Import external DB

[stefanosisto]

Hello! I'm asking if with SharkORM I can import an external DB. What I want to do is:

• Put the database file (assetDatabase.db) as asset in my project;
• Create a new db when launching the app (inappDatabase.db). At this moment I have an asset that is just a file and a Database that is generated by SharkORM;
• Do something like " INSERT OR UPDATE" in inappDatabase using assetDatabase (giving me the possibility to specify which table need this insert).

I hope you understand! Thanks, Stefano

[editfmah]

Hi,

Yes you could SharkORM.rawQuery("ATTACH DATABASE 'assetDatabase.db' as Assets"), then do a SELECT INTO ............

Here is an example we did for a data fix.

[SharkORM executeSQL:[NSString stringWithFormat:@"ATTACH DATABASE '%@' as DataFix", [[NSBundle mainBundle] pathForResource:@"DataFix" ofType:@".db"]] inDatabase:nil];
SRKRawResults* spoiledResources = [SharkORM rawQuery:@"SELECT * FROM DataFix.ResourceGuidDataFix WHERE ResourceGuidDataFix.resourceId IN (SELECT Resource.resourceId FROM Resource)"];

if (spoiledResources.rowCount) {
    for (int i=0; i<spoiledResources.rowCount; i++) {

        NSNumber* resourceId = [spoiledResources valueForColumn:@"resourceId" atRow:i];
        NSString* guid = [spoiledResources valueForColumn:@"guid" atRow:i];

        Resource* r = [Resource objectWithId:resourceId.intValue];

        for (Favourite* f in [[[Favourite query] whereWithFormat:@"resourceGuid = %@", r.guid] fetch]) {
            f.resourceGuid = guid;
            [f commit];
        }

        r.guid = guid;
        [r commit];

    }
}
[Settings setJun18DataFixRun];
[SharkORM executeSQL:@"DETACH DATABASE DataFix" inDatabase:nil];

[stefanosisto]

Thanks!!!

[stefanosisto]

Just another question. Can I also insert a .db as asset and use it in the app without creating a new one?

[editfmah]

That can be troublesome, because the bundle is signed and is therefore readonly. So things can get pretty weird and unreliable. You normally have to copy it out to something like the documents folder and the use that name in the open method.

[stefanosisto]

Another question! Can i check if exist a DB in the app and if not exist, attacch 'assetDb' ?

[editfmah]

So you could do that with standard SQLite. execute a raw query with something like, "PRAGMA table_info(myTableName)".