Pre-packaged database has an invalid schema:

[hitmacreed]

Hi @johan12345 @dalgarins , when trying go te data from a existing DB.sqlite i get this error and i tried a lot of solutions but no sucess.

Full error

java.lang.IllegalStateException: Pre-packaged database has an invalid schema: amenity_internet_cafe_lisboa(co.anbora.labs.spatiaroom.data.model.PtAmenity).
Expected:TableInfo{name='amenity_internet_cafe_lisboa', columns={osm_id=Column{name='osm_id', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, osm_type=Column{name='osm_type', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, wheelchair=Column{name='wheelchair', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, internet_access=Column{name='internet_access', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, amenity=Column{name='amenity', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, opening_hours=Column{name='opening_hours', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, name=Column{name='name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, ogc_fid=Column{name='ogc_fid', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='undefined'}, full_id=Column{name='full_id', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, GEOMETRY=Column{name='GEOMETRY', type='BLOB', 
                                                                                                    affinity='5', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}}, foreignKeys=[], indices=[]}
 Found: TableInfo{name='amenity_internet_cafe_lisboa', columns={ogc_fid=Column{name='ogc_fid', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='undefined'}, full_id=Column{name='full_id', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, osm_id=Column{name='osm_id', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, osm_type=Column{name='osm_type', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, amenity=Column{name='amenity', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, wheelchair=Column{name='wheelchair', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, opening_hours=Column{name='opening_hours', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, name=Column{name='name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, internet_access=Column{name='internet_access', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, GEOMETRY=Column{name='GEOMETRY', type='POINT', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}}, foreignKeys=[], indices=[]}

Main issue seems to be this

Expected:
GEOMETRY=Column{name='GEOMETRY', type='BLOB',

 Found:
GEOMETRY=Column{name='GEOMETRY', type='POINT'

My Code:

@Database(
    entities = [PtAmenity::class],
    version = 1,
    exportSchema = false
)
@TypeConverters(GeometryConverters::class)
abstract class AppDatabase : RoomDatabase() {

    /**
     * @return [AmenitiesDao] Foodium Posts Data Access Object.
     */
    abstract fun getAmenity(): AmenitiesDao

    companion object {
        const val DB_NAME = "cafesSpatiaLite.sqlite"; 
        const val ASSET_PATH = "database/" + DB_NAME; 

        @Volatile
        private var INSTANCE: AppDatabase? = null

        fun getInstance(context: Context): AppDatabase {
            val tempInstance = INSTANCE
            if (tempInstance != null) {
                return tempInstance
            }

            synchronized(this) {
                val instance = SpatiaRoom.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    DB_NAME,
                ).createFromAsset(ASSET_PATH)
                    .addCallback(object : Callback() {
                    override fun onCreate(db: SupportSQLiteDatabase) {
                        // Initialize Spatialite
                        db.query("SELECT InitSpatialMetaData(1);").moveToNext()
                        // RecoverGeometryColumn to correctly initialize Spatialite's metadata
                        db.query("SELECT RecoverGeometryColumn($TABLE_NAME, 'GEOMETRY', 4326, 'POINT', 'XY');")
                            .moveToNext()

                        db.query("SELECT CreateSpatialIndex($TABLE_NAME, 'GEOMETRY');")
                            .moveToNext()
                    }
                }).build()

                INSTANCE = instance
                return instance
            }
        }

    }
}

/**
 * Data class for Database entity and Serialization.
 */
@Entity(tableName = TABLE_NAME)
data class PtAmenity(

    @PrimaryKey
    @ColumnInfo(name = "ogc_fid")
    var id: Int? = 0,

    @ColumnInfo(name = "full_id")
    var fullId: String? = null,

    @ColumnInfo(name = "osm_id")
    var osmId: String?,

    @ColumnInfo(name = "osm_type")
    var osmType: String?,

    @ColumnInfo(name = "amenity")
    var amenity: String?,

    var wheelchair : String?,

    @ColumnInfo(name = "opening_hours")
    var openingHours: String?,

    var name: String?,

    @ColumnInfo(name = "internet_access")
    var internetAccess: String?,

    @ColumnInfo(name = "GEOMETRY")
    var geometry: Point?,
) {
    companion object {
        const val TABLE_NAME = "amenity_internet_cafe_lisboa"
    }
}

 @Query("SELECT * FROM ${PtAmenity.TABLE_NAME} WHERE ogc_fid = :id")
    @SkipQueryVerification
    fun getAmenityById(id: Int): PtAmenity

appDatabase = AppDatabase.getInstance(requireContext())
  val getAmenity = appDatabase.getAmenity().getAmenityById(1);
  Log.d("AmenityLog", "Fetched amenity: $getAmenity")

DB schema 

"amenity_internet_cafe_lisboa" (
    "ogc_fid"   INTEGER,
    "full_id"   TEXT,
    "osm_id"    TEXT,
    "osm_type"  TEXT,
    "amenity"   TEXT,
    "wheelchair"    TEXT,
    "opening_hours" TEXT,
    "name"  TEXT,
    "internet_access"   TEXT,
    "GEOMETRY"  POINT, // Could be POINT, LINE POLYGON ECT
    PRIMARY KEY("ogc_fid" AUTOINCREMENT)
)

Any help would be nice, thanks

[dalgarins]

@hitmacreed please create an example repository I will help you.

[dalgarins]

Hi @hitmacreed please create a repo I will make a PR to your repo when I found the issue.

[hitmacreed]

Hi @hitmacreed please create a repo I will make a PR to your repo when I found the issue.

Hi @dalgarins thanks this is the repo