tstack / sqlitejdbcng Goto Github PK
View Code? Open in Web Editor NEWA new SQLite JDBC driver
A new SQLite JDBC driver
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
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).
This might be a good end-to-end thing to do so that someone can check the file type before opening it up.
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, "%");
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 :)
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
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();
}
}
}
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.