I have a pre packaged SQL database. Which has cou...

# room

[pollux-]

I have a pre packaged SQL database. Which has couple of Boolean fields. When I import that DB using Room it's throwing an error staying
Pre packaged database has invalid schema

I'm guessing problem is that, DB has boolen field type but Room table we can't have Boolean its being used and Integer field.

How can I solve this??

Thanks

[Mark Murphy]

Step #1: Create your Room entities, DAOs, and RoomDatabase

Step #2: Look at the schema that Room generates

Step #3: Adjust your packaged SQLite database to use that schema

[pollux-]

Thanks @Mark Murphy.. I don't have the control over the schema of prepackaged database, which I'm downloading from a remote location and writing to a file 🙄

Is there anyway I can migrate it like, copying the data and dropping the table. I tried that, but it didn't work though

Issue here is that schema has Boolean type and same table in Room use INTEGER type to represent the Boolean statuses.

[pollux-]

_`Caused by: java.lang.IllegalStateException: Pre-packaged database has an invalid schema: facility(org.kp.m.dashboard.home.data.geofencing.model.Facility).
09-14 184003.805  979  979 E AndroidRuntime: Expected:
09-14 184003.805  979  979 E AndroidRuntime: TableInfo{name='facility', columns={zip=Column{name='zip', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, afterHoursInd=Column{name='afterHoursInd', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, isPreferedFacility=Column{name='isPreferedFacility', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, hasEmergencyServices=Column{name='hasEmergencyServices', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, city=Column{name='city', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, timezone=Column{name='timezone', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, latitude=Column{name='latitude', type='REAL', affinity='4', notNull=false, primaryKeyPosition=0, defaultValue='null'}, hasByAppointment=Column{name='hasByAppointment', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, cpmFacilityId=Column{name='cpmFacilityId', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, hospitalInd=Column{name='hospitalInd', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, facilityDescription=Column{name='facilityDescription', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, street=Column{name='street', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, name=Column{name='name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, ncalInd=Column{name='ncalInd', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, affiliateInd=Column{name='affiliateInd', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, isHospital=Column{name='isHospital', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, hasWalkInCare=Column{name='hasWalkInCare', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, _id=Column{name='_id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, state=Column{name='state', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, region=Column{name='region', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, hasAfterHoursCare=Column{name='hasAfterHoursCare', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, spotlightKey=Column{name='spotlightKey', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, formattedHours=Column{name='formattedHours', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, longitude=Column{name='longitude', type='REAL', affinity='4', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
09-14 184003.805  979  979 E AndroidRuntime: Found:
09-14 184003.806  979  979 E AndroidRuntime: TableInfo{name='facility', columns={afterHoursInd=Column{name='afterHoursInd', type='BOOLEAN', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='null'}, isPreferedFacility=Column{name='isPreferedFacility', type='BOOLEAN', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='null'}, city=Column{name='city', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, timezone=Column{name='timezone', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, latitude=Column{name='latitude', type='REAL', affinity='4', notNull=false, primaryKeyPosition=0, defaultValue='null'}, hasByAppointment=Column{name='hasByAppointment', type='BOOLEAN', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='null'}, cpmFacilityId=Column{name='cpmFacilityId', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, street=Column{name='street', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, affiliateInd=Column{name='affiliateInd', type='BOOLEAN', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='null'}, isHospital=Column{name='isHospital', type='BOOLEAN', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='null'}, hasWalkInCare=Column{name='hasWalkInCare', type='BOOLEAN', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='null'}, state=Column{name='state', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, hasAfterHoursCare=Column{name='hasAfterHoursCare', type='BOOLEAN', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='null'}, formattedHours=Column{name='formattedHours', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, longitude=Column{name='longitude', type='REAL', affinity='4', notNull=false, primaryKeyPosition=0, defaultValue='null'}, zip=Column{name='zip', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, hasUrgentCare=Column{name='hasUrgentCare', type='BOOLEAN', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='null'}, hasEmergencyServices=Column{name='hasEmergencyServices', type='BOOLEAN', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='null'}, hospitalInd=Column{name='hospitalInd', type='BOOLEAN', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='null'}, facilityDescription=Column{name='facilityDescription', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, name=Column{name='name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, ncalInd=Column{name='ncalInd', type='BOOLEAN', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='null'}, _id=Column{name='_id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, region=Column{name='region', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, spotlightKey=Column{name='spotlightKey', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}`_

[Mark Murphy]

I don't have the control over the schema of prepackaged database,

You may wish to consider alternatives to Room, such as SQLDelight.

Is there anyway I can migrate it like, copying the data and dropping the table

No, not via Room. If this database is packaged as an asset, fix it on your development machine. If this database is downloaded from a server... either change the server or perhaps use SQLiteDatabase to fix the database before using it with Room's createFromFile(). Or, consider alternatives to Room.

[pollux-]

@Mark Murphy I had requested for schema change and that worked for me .. thanks for all the help