CODEWITHSUNDEEP
androidsqliteandroid-sqlitesql-insertsqliteopenhelper
User
User

Reputation: 722

Is there any tool/method to generate database scripts for inserting data in SQLite?

I am working on an Android application that maintains a SQLite database with around 40+ tables.The tables are supposed to have predefined data, so i need to insert bulk data in all tables. Is there any way using which i can create a data insertion script and run it just like they do in SQL Server and SQLyog instead of writing insert data function for insertion of data in each table.

I have researched about this but was unable to find any help. Can somebody please let me know if this is possible with SQLite Android or i have to see an alternative?

Any help will be appreciated.

Upvotes: 0

Views: 148

Answers (1)

MikeT
MikeT

Reputation: 56948

Typically you would ship a pre-populated database as an asset populating the database using your favourtie SQLite Management tool (NaviCat, DBeaver, DBBrowser for SQlite and more).

The file (as all an SQLite Database is, is a single file) is then copied into the assets (assets/databases if using SQLiteAssethelper) folder, when developing the App.

  • other support rollbak files may exist, but shouldn't when copying a database.
  • the assets folder will likely have to be created.

When the App is first run the database is copied from the assets folder to it's final location, which is normally (recommended) in the data/data/the_package/databases/. See link above for SQLiteAssetHelper that does this on your behalf.

The above is perhaps the most efficient way startup wise, although the App will have a little bit of bloat as two copies of the database will exist.

You can build data from files. These could be JSON/GSON, CSV's are even SQL e.g. exported from your SQLite manage tool (or perhaps from other database tools).

You could, if the data required permits build the data using SQL to generate the data on the fly e.g.

WITH cte(cnt,ts,sid,t,p) AS 
    (
        SELECT 
          1,
            '1971-01-01 00:00:00',
            1,  
            (ABS(random()) % 3000) / 99.35
            , ABS(random()) % 25 / 99.35
            AS b
        UNION ALL
        SELECT 
            cnt + 1,
            datetime('1971-01-01 00:00:00','+'||(cnt / 96)||' days','+'||((cnt / 4) % 24)||' hours'), 
            ((cnt % 4) +1), 
            (ABS(random()) % 3000) / 99.35
            , ABS(random()) % 25 / 99.35
            FROM cte AS d
            LIMIT 8760 * 4 * 10
    )
-- SELECT * FROM cte;
INSERT INTO log SELECT ts,sid,t,p FROM cte;
;

This builds data for every hour of the day of the year for 10 years with 4 rows per hour. i.e. 350,400 rows.

Upvotes: 1

Related Questions