SQLite support?

[meltzow]

Hi,

is there any SQLite support available here?
If no, is it possible to describe how it must implemented?

thx.
Mario

[stephenh]

Hi Mario,

Joist currently doesn't have SQLite support. A friend of mine spiked adding H2 support (another embedded database) a few years ago, but we never finished it up as, for us anyway, using an embedded database was mostly about testing speed, and I've found that regular MySQL/Postgresql with the fsync settings turned off has been fast enough.

That said, it'd be great to have SQLite support.

This probably sounds janky, but the way to implement it would be to add a Db.SQLITE to the Db enum, then find all references to Db.MYSQL and also handle Db.SQLITE. Or just run the tests (in the features project, which is a test schema that exercises all the boundary conditions) and see what fails. :-)

Awhile ago, I tried to do the more sensible approach of having a "DbAdapter" type interface, that then would have an implementation for each supported database. But the refactoring was not as clean as I wanted it to be...not sure why, so I stayed with the enum approach for now.

If you start poking around at implementing SQLITE support, feel free to ask any questions/comments, and I'll help out.

[meltzow]

Hi stephen,

thx.I have tried it. But now I get error while getting information about the schema. In mysql or pg this statement is working: "SELECT" + " c.table_name, c.column_name, c.data_type, c.character_maximum_length, c.is_nullable, c.column_default as default_value" + " FROM information_schema.columns c" + " INNER JOIN information_schema.tables t on c.table_name = t.table_name" + " WHERE t.table_schema = '" + this.schemaName + "' AND c.table_schema = '" + this.schemaName + "' but not on sqlite.
in sqlite there is something like "sqlite_master" but I don't understand how to handle this. Any ideas?

[meltzow]

my changes are commited here: https://github.com/wuendsch/joist

[stephenh]

Hi Mario,

Hm. Well. The situation does not look good.

Joist makes a fundamental assumption that:

1. Migrations are used to create/update the schema.
2. Codegen that scans the schema to determine tables/columns/foreign keys

With a combination of that sqlite_master and "pragma table_info(tableName)", I can see how to get the list of tables and then columns from SQLite (granted, the table_info is only per-table, so we'd need to execute 1 SQL query per table instead of using just one information_schema query like we can do for MySQL/PG).

But I'm not seeing anything for getting foreign key information, which is pretty important for correctly setting up "parent.getChild()", "parent.getChildren()" type relationships.

Short of calling "select * from sqlite_master" and then parsing the "CREATE TABLE" strings to look for foreign keys. Which is technically doable, but would be "fun" to say the least.

You could probably reuse an existing SQL grammar/parser, like from H2, which is a Java-based database, so should have a SQL parsing engine in it somewhere that understands how to parse the CREATE TABLE statements.

Oh, here's something:

> pragma foreign_key_list(bar);
0|0|foo|foo_id|id|NO ACTION|NO ACTION|NONE

(I made a test table called "bar" that has a "foo_id" column that points to the "foo.id" column.)

So, this looks more doable-ish...

So, in Joist right now the InformationSchemaWrapper uses basically the same code for both MySQL and PG to populate a List, which then the rest of codegen uses.

SQLite is going to be different enough that I don't think you'll be able to reuse any of the "information_schema" code.

So, maybe something like a SchemaProvider interface, and then two implementations: one called something like StandardSchemaProvider (uses the information_schema table and is used for MySQL/PG) and then a SQLiteSchemaProvider that: 1) calls "select * from sqlite_master" to get all of the table names, 2) for each table name call "pragma table_info(tableName)" and make a new InformationSchemaColumn for each result, 3) for each table name call "pragma foreign_key_list(tableName)" to go back and update the InformationSchemaColumns created in step 2 with the FK information. ...4) for each table name call "pragma index_list(tableName)" to go back and update the InformationSchemaColumns increated in step 2 with index information.

Then in InformationSchemaWrapper, add some "if/else" logic to pick either the StandardSchemaProvider or the SQLiteSchemaProvider based on the database type.

I can't guarantee that the foreign_key_list/index_list pragmas will provide all the information you need to fully populate the List that will then drive the rest of codegen, but it seems promising.

What do you think?