tstack / sqlitejdbcng Goto Github PK
View Code? Open in Web Editor NEWA new SQLite JDBC driver
sqlitejdbcng's People
Contributors
Stargazers
Watchers
Forkers
aaimesqlitejdbcng's Issues
Switch a to a two clause based BSD license
Hi,
sorry to contact you via a ticket, found no other way to do so. I see the license is a classic BSD three clauses one, which makes it incompatible with the LGPL/GPL work.
I'm looking at it for a potential integration with GeoTools/GeoServer, the license is kind of blocking me before I even start since I know I won't be able to use it.
Would you consider switching the license to the 2 clause version, that is known to be compatible with LGPL/GPL instead? http://en.wikipedia.org/wiki/BSD_licenses
Support Java 6
Hi there!
Ok, I know, I know, the home page says only support for java 7, and the code makes quite some use of "try with resources", porting it back to java 6 would make the code uglier to look at.
Now, let me propose some positives for the switch. Both GeoTools and GeoServer are established projects with a large user base, I'm trying to make them use your driver instead of the Xerial one (for entirely selfish reasons, it allows to use several versions of Spatialite, it scales up nicely, and I find the code well written which makes me conformtable contributing to it, and it never crashed a JVM in my face, while the Xerial one is a serial killer...)
The first step would be to provide better support for spatialiate in GeoServer, a web application with many concurrent users that definitely needs the extra scalability.
Then we are looking into supporting mbtiles, a way to store tiled maps in a sqlite database, another topic in which we definitely need the extra scalablity.
Finally, the OpenGeoSpatial consortium is pushing the GeoPackage specification, which is a container for spatial data based on, guess what, sqlite.
Long story short, being able to work with GeoTools and GeoServer would increase the user base of your driver.
Now, I can definitely consider trying to setup a java 6 compatible fork, but before going to those measures, I'd like to check with you if there is any chance to get java 6 support in the mainline driver.
To avoid repeating the same maintenance code an approach like Spring JDBCTemplate could be taken (of course, it would have to be hand-rolled to avoid a dependency on Spring).
Add a java.nio.file.spi.FileTypeDetector that detects sqlite database files
This might be a good end-to-end thing to do so that someone can check the file type before opening it up.
Database metadata not working
Trying to access database metadata results in:
Caused by: java.sql.SQLSyntaxErrorException: no such table: .sqlite_master
at org.sqlitejdbcng.bridj.Sqlite3.checkOk(Sqlite3.java:674)
at org.sqlitejdbcng.SqliteConnection.prepareStatement(SqliteConnection.java:507)
at org.sqlitejdbcng.SqliteConnection.prepareStatement(SqliteConnection.java:235)
at org.sqlitejdbcng.SqliteDatabaseMetadata.getColumns(SqliteDatabaseMetadata.java:802)
at org.apache.commons.dbcp.DelegatingDatabaseMetaData.getColumns(DelegatingDatabaseMetaData.java:218)
at org.apache.commons.dbcp.DelegatingDatabaseMetaData.getColumns(DelegatingDatabaseMetaData.java:218)
at org.geotools.jdbc.JDBCFeatureSource.getColumnMetadata(JDBCFeatureSource.java:708)
at org.geotools.jdbc.JDBCFeatureSource.buildFeatureType(JDBCFeatureSource.java:204)
... 17 more
The invocation in question is:
ResultSet columns = metaData.getColumns(cx.getCatalog(), databaseSchema, tableName, "%");
Database metadata getTypeInfo() returns null
The specification states it should return a non null resultset instead. It would be nice if it was filled with values, but having a non null one is a starter :)
Cannot load extensions
The current driver apparently does not allow loading extension (in my case, spatialite).
I've tried with the following simple patch:
diff --git a/src/main/java/org/sqlitejdbcng/SqliteConnection.java b/src/main/java/org/sqlitejdbcng/SqliteConnection.java
index e7e6bff..edadb2b 100644
--- a/src/main/java/org/sqlitejdbcng/SqliteConnection.java
+++ b/src/main/java/org/sqlitejdbcng/SqliteConnection.java
@@ -109,7 +109,7 @@ public class SqliteConnection extends SqliteCommon implements Connection {
Sqlite3.OpenFlag.SQLITE_OPEN_CREATE.intValue() |
Sqlite3.OpenFlag.SQLITE_OPEN_URI.intValue(),
null);
-
+ Sqlite3.sqlite3_enable_load_extension(db_out.get(), 1);
this.url = url;
this.db = Sqlite3.withDbReleaser(db_out.get());
this.properties = properties;
diff --git a/src/main/java/org/sqlitejdbcng/bridj/Sqlite3.java b/src/main/java/org/sqlitejdbcng/bridj/Sqlite3.java
index 9c63f0f..a5a003c 100644
--- a/src/main/java/org/sqlitejdbcng/bridj/Sqlite3.java
+++ b/src/main/java/org/sqlitejdbcng/bridj/Sqlite3.java
@@ -163,6 +163,8 @@ public class Sqlite3 {
public static native Pointer<Byte> sqlite3_mprintf(Pointer<Byte> fmt, Object... varargs);
public static native void sqlite3_free(Pointer<Byte> mem);
+
+ public static native int sqlite3_enable_load_extension(Pointer<Sqlite3Db> db, int onoff);
public static native int sqlite3_changes(Pointer<Sqlite3Db> db);
public static native int sqlite3_total_changes(Pointer<Sqlite3Db> db);
While I'm getting no error, calling from a statement
SELECT load_extension('/usr/lib/libspatialite.so.3')
results in no errors, but the extension is not loaded either.
If it is of any help, the spatialite site also has an example program based on the Xerial drivers (which I'm considering avoiding as I need to use the system libraries, not the ones packed in the jar): http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/java.html
Performance issues
While testing a program running 300 thousands insert statements I've noticed the performance of the driver can apparently be optimized quite a bit: with the Xerial driver the program runs in around 18s, the same with sqlitejdbcng runs in 42s instead.
The program requires using the spatialite extensions, if you do, I'd recommend not to use 4.1.1 but something from the 3.x series, as in 4.1 it seems the call to InitSpatialMetadata can take minutes.
With spatialite 2.4 or 3.1.0 it's quick, and the time is actually spent doing inserts.
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class NGSpatialiteSample
{
public static void main(String[] args) throws ClassNotFoundException
{
long start = System.currentTimeMillis();
File file = new File("./spatialite.sample");
file.delete();
// load the sqlite-JDBC driver using the current class loader
Class.forName("org.sqlitejdbcng.SqliteDriver");
Connection conn = null;
try
{
// create a database connection
conn = DriverManager.getConnection("jdbc:sqlite:spatialite.sample", null);
Statement stmt = conn.createStatement();
stmt.setQueryTimeout(30); // set timeout to 30 sec.
// loading SpatiaLite
executeProcedure(stmt, "SELECT load_extension('libspatialite.so.2')");
// enabling Spatial Metadata
// using v.2.4.0 this automatically initializes SPATIAL_REF_SYS and GEOMETRY_COLUMNS
String sql = "SELECT InitSpatialMetadata()";
executeProcedure(stmt, sql);
// creating a POINT table
sql = "CREATE TABLE test_pt (";
sql += "id INTEGER NOT NULL PRIMARY KEY,";
sql += "name TEXT NOT NULL)";
stmt.execute(sql);
// creating a POINT Geometry column
sql = "SELECT AddGeometryColumn('test_pt', ";
sql += "'geom', 4326, 'POINT', 'XY')";
executeProcedure(stmt, sql);
// creating a LINESTRING table
sql = "CREATE TABLE test_ln (";
sql += "id INTEGER NOT NULL PRIMARY KEY,";
sql += "name TEXT NOT NULL)";
stmt.execute(sql);
// creating a LINESTRING Geometry column
sql = "SELECT AddGeometryColumn('test_ln', ";
sql += "'geom', 4326, 'LINESTRING', 'XY')";
executeProcedure(stmt, sql);
// creating a POLYGON table
sql = "CREATE TABLE test_pg (";
sql += "id INTEGER NOT NULL PRIMARY KEY,";
sql += "name TEXT NOT NULL)";
stmt.execute(sql);
// creating a POLYGON Geometry column
sql = "SELECT AddGeometryColumn('test_pg', ";
sql += "'geom', 4326, 'POLYGON', 'XY')";
executeProcedure(stmt, sql);
// inserting some POINTs
// please note well: SQLite is ACID and Transactional,
// so (to get best performance) the whole insert cycle
// will be handled as a single TRANSACTION
conn.setAutoCommit(false);
int i;
for (i = 0; i < 100000; i++)
{
// for POINTs we'll use full text sql statements
sql = "INSERT INTO test_pt (id, name, geom) VALUES (";
sql += i + 1;
sql += ", 'test POINT #";
sql += i + 1;
sql += "', GeomFromText('POINT(";
sql += i / 1000.0;
sql += " ";
sql += i / 1000.0;
sql += ")', 4326))";
stmt.executeUpdate(sql);
}
conn.commit();
// checking POINTs
sql = "SELECT DISTINCT Count(*), ST_GeometryType(geom), ";
sql += "ST_Srid(geom) FROM test_pt";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next())
{
// read the result set
String msg = "> Inserted ";
msg += rs.getInt(1);
msg += " entities of type ";
msg += rs.getString(2);
msg += " SRID=";
msg += rs.getInt(3);
System.out.println(msg);
}
rs.close();
// inserting some LINESTRINGs
// this time we'll use a Prepared Statement
sql = "INSERT INTO test_ln (id, name, geom) ";
sql += "VALUES (?, ?, GeomFromText(?, 4326))";
PreparedStatement ins_stmt = conn.prepareStatement(sql);
conn.setAutoCommit(false);
for (i = 0; i < 100000; i++)
{
// setting up values / binding
String name = "test LINESTRING #";
name += i + 1;
String geom = "LINESTRING (";
if ((i%2) == 1)
{
// odd row: five points
geom += "-180.0 -90.0, ";
geom += -10.0 - (i / 1000.0);
geom += " ";
geom += -10.0 - (i / 1000.0);
geom += ", ";
geom += -10.0 - (i / 1000.0);
geom += " ";
geom += 10.0 + (i / 1000.0);
geom += ", ";
geom += 10.0 + (i / 1000.0);
geom += " ";
geom += 10.0 + (i / 1000.0);
geom += ", 180.0 90.0";
}
else
{
// even row: two points
geom += -10.0 - (i / 1000.0);
geom += " ";
geom += -10.0 - (i / 1000.0);
geom += ", ";
geom += 10.0 + (i / 1000.0);
geom += " ";
geom += 10.0 + (i / 1000.0);
}
geom += ")";
ins_stmt.setInt(1, i+1);
ins_stmt.setString(2, name);
ins_stmt.setString(3, geom);
ins_stmt.executeUpdate();
}
conn.commit();
ins_stmt.close();
// checking LINESTRINGs
sql = "SELECT DISTINCT Count(*), ST_GeometryType(geom), ";
sql += "ST_Srid(geom) FROM test_ln";
rs = stmt.executeQuery(sql);
while(rs.next())
{
// read the result set
String msg = "> Inserted ";
msg += rs.getInt(1);
msg += " entities of type ";
msg += rs.getString(2);
msg += " SRID=";
msg += rs.getInt(3);
System.out.println(msg);
}
rs.close();
// inserting some POLYGONs
// this time too we'll use a Prepared Statement
sql = "INSERT INTO test_pg (id, name, geom) ";
sql += "VALUES (?, ?, GeomFromText(?, 4326))";
ins_stmt = conn.prepareStatement(sql);
conn.setAutoCommit(false);
for (i = 0; i < 100000; i++)
{
// setting up values / binding
String name = "test POLYGON #";
name += i + 1;
ins_stmt.setInt(1, i+1);
ins_stmt.setString(2, name);
String geom = "POLYGON((";
geom += -10.0 - (i / 1000.0);
geom += " ";
geom += -10.0 - (i / 1000.0);
geom += ", ";
geom += 10.0 + (i / 1000.0);
geom += " ";
geom += -10.0 - (i / 1000.0);
geom += ", ";
geom += 10.0 + (i / 1000.0);
geom += " ";
geom += 10.0 + (i / 1000.0);
geom += ", ";
geom += -10.0 - (i / 1000.0);
geom += " ";
geom += 10.0 + (i / 1000.0);
geom += ", ";
geom += -10.0 - (i / 1000.0);
geom += " ";
geom += -10.0 - (i / 1000.0);
geom += "))";
ins_stmt.setInt(1, i+1);
ins_stmt.setString(2, name);
ins_stmt.setString(3, geom);
ins_stmt.executeUpdate();
}
conn.commit();
ins_stmt.close();
// checking POLYGONs
sql = "SELECT DISTINCT Count(*), ST_GeometryType(geom), ";
sql += "ST_Srid(geom) FROM test_pg";
rs = stmt.executeQuery(sql);
while(rs.next())
{
// read the result set
String msg = "> Inserted ";
msg += rs.getInt(1);
msg += " entities of type ";
msg += rs.getString(2);
msg += " SRID=";
msg += rs.getInt(3);
System.out.println(msg);
}
rs.close();
stmt.close();
}
catch(SQLException e)
{
// if the error message is "out of memory",
// it probably means no database file is found
System.out.println(e.getMessage());
}
finally
{
try
{
if(conn != null)
conn.close();
}
catch(SQLException e)
{
// connection close failed.
System.err.println(e);
}
}
long end = System.currentTimeMillis();
System.out.println((end - start) / 1000.0);
}
private static void executeProcedure(Statement stmt, String sql) throws SQLException {
try (ResultSet rsl = stmt.executeQuery(sql)) {
rsl.next();
}
}
}
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.