Prepopulated SQLite Databases in Ionic 2


#1

Hi, could someone guide me how to copy a pre-filled ( prepopulated ) sqlite database in Ionic 2?


#2

Not ionic 2 specific, but it’s the same idea. You would take the file you want use as the DB data, and using the file plugin, pass this along

https://pouchdb.com/2016/04/28/prebuilt-databases-with-pouchdb.html


#3

Hi, a couple of days ago I had the same issue - no clear solution found on this forum, however mine is:

npm install -g sqlite3 
sqlite3 -csv -header database_name.db "SELECT * FROM table_name" > data.csv

npm install -g csvtojson
csvtojson data.csv > data.json

then you import freshly created json data through data service:

this.http.get('data/data.json')...

Hope this helps…


#4

There must be a way to access resources without going through all the needless overhead of Http. It’s trivial using require with webpack.


#5

I do agree with you rapropos, but the thing is - everybody talks about it, but nobody found it (the solution how to load prepopulated db I mean…) It is sad, because the topic is super clear and forum seems to help us with the knowledge and solutions :frowning:


#6

Actually, it works just like it does in webpack. I just tested it.

let data = require('data/data.json');

#7

Ok for JSON file source, but I have talked about the famous code:

  this._db = win.sqlitePlugin.openDatabase(util_1.assign({
                name: dbOptions.name,
                location: location_1,
                createFromLocation: dbOptions.existingDatabase ? 1 : 0
            }, dbOptions));`

Worked as a charm in Ionic 1 and then in Ionic 2 does nothing - prefilled database is simply not imported…

Thanks anyway for the require solution that I is of course a valid one.


#8

You can open an issue against ionic-native if you want, I suppose. Looks like that feature isn’t included in the stock cordova sqlite plugin that ionic-native uses, just the ext version. Personally, I find the entire concept of magic prepopulation rather fragile and prefer to just use SQL to initialize databases.


#9

How do you then load initial data?


#10

SQL INSERT statements.


#11

I’ve tried to use File to copy the prepopulated sqlite file from www/ but I cannot figure out how to use File also.
In Ionic 1, all things are fine, but in Ionic 2, cordova.file.dataDirectory does not work. If you have experience about this case, pleas share with us :slight_smile:


#12

SQL INSERT statements.

Would it be possible to have an exapmle of your load service/function?
The insert statement suppose to be run once - only if DB doesn’t exists and traight after table creation. How to then detect it - I mean avoid:

 SELECT * FROM XXX and if(data.res.rows.length === 0) 

statements for each table load.


#13

I use a special version table to decide whether the database has been initialized; this also allows for upgrades to the app to modify databases created by older versions.

  private _initializeDb(): void {
    if (win.sqlitePlugin) {
      let openparms = {
        name: _SQLITE_DB_NAME,
        location: 2, // local backup
        createFromLocation: 0
      };
      this._db = win.sqlitePlugin.openDatabase(openparms);
    } else {
      console.warn('Storage: SQLite plugin not installed, falling back to WebSQL. Make sure to install cordova-sqlite-storage in production!');
      this._db = win.openDatabase(_SQLITE_DB_NAME, '1.0', 'database', 5 * 1024 * 1024);
    }

    this._query("SELECT version FROM version")
      .then((dbrv) => {
          // dbrv.rows.item(0).version is the version
          // database has been initialized
        },
        (dbrv) => {
          // database needs initialization
          this._batch([
            "CREATE TABLE version (version INT NOT NULL PRIMARY KEY)",
            ["INSERT INTO version (version) VALUES ($1)", [1]],
            // more CREATE TABLEs and INSERTs
          ])
          .then(() => {
              console.log("completed database initialization");
            }, (dbrv) => {
              console.log(JSON.stringify(dbrv.err));
          });
      });
  }

If you’re confident that you’ll only ever be running on device, you don’t need the special case initialization logic for WebSQL, and you can replace _batch() with the plugin’s executeSqlBatch(). I prefer to maintain WebSQL compatibility by rolling a version of batch that works with transactions:

 private _batch(stmts: any[]): Promise<any> {
    let rv: Promise<any> = new Promise((resolve, reject) => {
      let wincb = (dbrv: any) => {
        resolve(dbrv);
      };

      let losecb = (tx: any, err: Error) => {
        reject(err);
        return false;
      };

      this._db.transaction((tx: any) => {
        let nstmts = stmts.length;
        for (let ix = 0; ix < nstmts; ix++) {
          let sql:string;
          let phs:any[];
          if (Array.isArray(stmts[ix])) {
            sql = stmts[ix][0];
            phs = stmts[ix][1];
          } else {
            sql = stmts[ix];
            phs = [];
          }
          tx.executeSql(sql, phs, undefined, losecb);
        }
      }, losecb, wincb);
    });
    return rv;
  }

#14

Great, many thanks…


#15

There is a section (using Angular) that may be helpful. He seems to indicate the pre-populated database can be created and shipped with the app, and if that is so (unless it needs to be
pre-populated at app run-time) Im not sure I see why you even need to use ionic/angular. HTH

using pre-populated sqllite


#16

Hi rapropos, could you please give me a clue how do you prepopulate the DB table with about 500 entries using your logic… It gonna be a huge array:

> this._batch([...............]


#17

Did you solve this? I’m trying to solve a similar issue for a week with no luck. My DB table has 20K entries :confused:


#18

Hi, don’t know if you still need help but if you still need help i figured it out.

Basically you need to use the plugin cordova-sqlite-ext.

  1. To install use ionic plugin add cordova-sqlite-ext

  2. Copy your Database file to the /www folder

  3. In one of your classes (i tried it on MyApp class contructor after the platfor.ready(…) do this:
    this.storage = new Storage(SqlStorage,
    {
    name: ‘WUT.db’, <- Name of your database .db
    location: 1,<- Don’t know if this makes any difference
    backupFlag: SqlStorage.BACKUP_LOCAL,
    existingDatabase: true <-This way you’re looking for an existing DB
    });

  4. Now do something like
    storage.query(‘SELECT * from table’).then
    (
    (data) => { console.log(data): },
    (error) => { console.log(error); }
    });


How to make SQLite in ionic-native use cordova-sqlite-ext?
#19

Hi ruialves_programming, sorry for the late reply. Well in fact I have sopped to develop the Ionic part of the project fo a while. To reply: No no solution found till now, but I will check yours asap and give you a feedback. Thanks.


#20

Hi,
Please report back when you do.