Database schema results in issue when trying to migrate

[ymmijbao]

Context: Prior to introducing this dependency into my app, I have an existing db already set up and working in my application; this was previously using Room.databaseBuilder(). I just replaced this with SpatiaRoom.databaseBuilder() after adding the dependency. I am using version 0.2.4 of this library.

Everything seemed to have compiled and ran just fine; I was even able to write a query using the PtDistWithin() SQL function and got results back (dynamically generated Points from lat, lng coordinates that I had). However, after trying to create a new brand new table with a Geometry Column (tried both AddGeometryColumn as well as having a field already in my entity and calling RecoverGeometryColumn) + Spatial Index, the db always shows as "closed" in DB Inspector, db gets wiped (due to my preference to include .fallbackToDestructiveMigration() to allow destructive state when explicit migration fails), and when I try to save the .db file out, there's nothing in there. I noticed a bunch of errors when printing out the schema (attaching here: db_dump.txt).

I am following the code snippets in the demo project + README. In my migration function, I am essentially doing:

1. calling database.query("SELECT InitSpatialMetaData();").moveToNext()
2. calling database.execSQL() to create my new tables, set up custom indexes, etc
3. calling database.query("SELECT RecoverGeometryColumn(...);").moveToNext()
4. calling database.query("SELECT CreateSpatialIndex(...);").moveToNext()

My questions:

1. What's going on here and what's causing the db schema to get all these errors? For example, my column names do not contain single or double quotes, so not sure why it's complaining about that. My column names are also lower case.
2. Is there any known issue of just replacing Room.databaseBuilder() with SpatiaRoom.databaseBuilder like that? I saw that internally, the latter does essentially the asme thing, but difference is that it is created from templateDb asset and sets an openHelperFactory. Can I do this or not?
3. Why does the db show up as "closed" in the Android Studio Database Inspector? I saw this question was also asked in 2021.
4. Why does the text file dump that I included not have any reference or inclusion of my local database table names? It looks like perhaps it errored out with all the initialization before it can ever get there?

[dalgarins]

@ymmijbao demo project use the latest version 0.2.9, please update the lib version you are using, the template db was an old implementation and had some errors, maybe this issue is produced by that.

About db inspector in android studio, this is an issue, you can't see your db, I haven't had time to fix that or to create a plugin for android studio to see spatialite dbs, PR's are welcome if you want to help with the project.

about your migration scripts could you share them? also fyi the library has some classes in this package: co.anbora.labs.spatia.geometry, you don't need to create a migration if you want to use some of geometry, only use the types and add the converters to room, that have to work for you.

[ymmijbao]

@dalgarins Thanks for your prompt response. Let me see if I am migrate my project to using your latest 0.2.9 version. I had to use 0.2.4 because Room dependency I was using was 2.4.3. I'll see if my project can easily upgrade to this.

Regarding the db inspector, is the only way to pull the .db file and manually inspect it with sqlite3 command or something?

I am creating a migration (androidx.room.migration) and adding this to my DB since I read in the documentation online that even adding a new brand table requires a migration. My project is not set up with auto-migrations. I cannot share the exact migration code, but I've essentially outlined what I am doing in the migrate() function that I have to override. I know that my SQL for creating the table and creating indexes are correct because if I comment out the InitSpatialMetaData, RecoverGeometryColumn, and CreateSpatialIndex spatialite SQL functions + restore back to using Room.databaseBuilder() instead of SpatiaRoom.databaseBuilder(), my app will correctly migrate and add the new tables. I am only running into this issue when using SpatiaRoom.databaseBuilder() and using the spatialite SQL functions.

Did you have a chance to look at the txt file I included to see all the issues going on with the db creation schema? Have you seen that before? I'd like to know why I don't see any of my table names and stuff like that. Only see spatialite stuff that seems to have a ton of errors.

Also, as previously inquired - do you know if there is any known issue for replacing an existing Room.databaseBuilder() (app previously had db up and running already) to using SpatiaRoom.databaseBuilder()? Could there be any complications in doing that? Please answer this.

[dalgarins]

@ymmijbao the reason you don't see your tables is by the version old version used a template db and copy that db and replace the db of your application it wasn't the best way to deal that, but worked, the latest version don't do that, don't copy a db and replace your db, the new execute the commands to initialize spatialite DBs.

[ymmijbao]

Also, as previously inquired - do you know if there is any known issue for replacing an existing Room.databaseBuilder() (app previously had db up and running already) to using SpatiaRoom.databaseBuilder()? Could there be any complications in doing that? Please answer this.

[ymmijbao]

Hey @dalgarins , so it seems like upgrading to the latest 0.2.9 is having some positive effects. I am able to successfully observe the following correct behaviors:

1. my migration code is running and my data from old tables are retained and not dumped
2. my new tables and columns are created successfully

However, I had a question to confirm whether all the spatialite stuff is being initialized correctly. I'm still seeing this when I save out the db and use the sqlite3 command. An example snippet is:

SELECT RAISE(ABORT,'insert on geometry_columns violates constraint: f_table_name value must not contain a single quote')
WHERE NEW.f_table_name LIKE ('%''%');
SELECT RAISE(ABORT,'insert on geometry_columns violates constraint: f_table_name value must not contain a double quote')
WHERE NEW.f_table_name LIKE ('%"%');
SELECT RAISE(ABORT,'insert on geometry_columns violates constraint: 
f_table_name value must be lower case')
WHERE NEW.f_table_name <> lower(NEW.f_table_name);
END;
CREATE TRIGGER geometry_columns_f_table_name_update
BEFORE UPDATE OF 'f_table_name' ON 'geometry_columns'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT,'update on geometry_columns violates constraint: f_table_name value must not contain a single quote')
WHERE NEW.f_table_name LIKE ('%''%');
SELECT RAISE(ABORT,'update on geometry_columns violates constraint: f_table_name value must not contain a double quote')
WHERE NEW.f_table_name LIKE ('%"%');
SELECT RAISE(ABORT,'update on geometry_columns violates constraint: f_table_name value must be lower case')
WHERE NEW.f_table_name <> lower(NEW.f_table_name);
END;
CREATE TRIGGER geometry_columns_f_geometry_column_insert
BEFORE INSERT ON 'geometry_columns'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT,'insert on geometry_columns violates constraint: f_geometry_column value must not contain a single quote')
WHERE NEW.f_geometry_column LIKE ('%''%');
SELECT RAISE(ABORT,'insert on geometry_columns violates constraint: 
f_geometry_column value must not contain a double quote')
WHERE NEW.f_geometry_column LIKE ('%"%');
SELECT RAISE(ABORT,'insert on geometry_columns violates constraint: f_geometry_column value must be lower case')
WHERE NEW.f_geometry_column <> lower(NEW.f_geometry_column);
END;
CREATE TRIGGER geometry_columns_f_geometry_column_update
BEFORE UPDATE OF 'f_geometry_column' ON 'geometry_columns'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT,'update on geometry_columns violates constraint: f_geometry_column value must not contain a single quote')
WHERE NEW.f_geometry_column LIKE ('%''%');
SELECT RAISE(ABORT,'update on geometry_columns violates constraint: f_geometry_column value must not contain a double quote')
WHERE NEW.f_geometry_column LIKE ('%"%');
SELECT RAISE(ABORT,'update on geometry_columns violates constraint: f_geometry_column value must be lower case')
WHERE NEW.f_geometry_column <> lower(NEW.f_geometry_column);
END;

I don't have any upper case column name values for my new table which is adding a GeometryColumn. Can you tell me what's going on here? And can you tell me what I can look for when using the sqlite3 command to make sure things were initialized correctly?

[dalgarins]

@ymmijbao don't save out with sqlite3 command, use adb pull to extract your db in your machine, open the db using the spatialite tool gui, and to validate if everything it's ok go to the demo project and copy the DAO, there are some internal functions to get version, use one of them if works you know.

[ymmijbao]

@dalgarins Can you link the spatialite tool gui that you are talking about to view DB?

[dalgarins]

@ymmijbao this is the tool: https://www.gaia-gis.it/fossil/spatialite_gui/index