Populating large tables Of SQLite DataBases with LC

[danielrr2]

Is it possible to use LiveCode to populate a table in an SQLite database? If so, what are some best practices or tips for writing the script when the table needs to handle more than 20 million records?

Best,
Daniel

[dunbarx]

Danielrr2, are you a bot?

Happy to reply if you are not.

Craig

[danielrr2]

Hi Craig,

As Marina and the Diamonds would say, I am not a robot. However, I’ve been having some trouble posting in this forum (it has happened to me before). The server either didn't recognize my username, my email, or my password — or it seemed to recognize them but then complained that my account had been ‘inactive.’ The only solution it suggested was to contact an administrator, but it doesn’t tell me how. But now I think I'm OK.

So, the bottomline: I am not a robot. I'd appreciate your help on this (as I did in the past)

[stam]

The Marina and the Diamonds reference is good
However Craig’s point is that this is a common topic and should be no issue googling it. This was pretty much the first result I found: https://lessons.livecode.com/m/4071/c/16767

However you will also find a lot if info in the built in API documentation (the “dictionary”), just search for “database”. There is native support for SQLite, MySQL, Postgres and ODBC. The only difference between these is how you connect.

[stam]

As for retrieving data I wrote up something short when experimenting with Reddit (I gave it up due to lack of interest). This is the text from that particular post:

Retrieve SQL data in LiveCode
When you connect to the database, you receive a connection ID (an integer - if not a number then contains error information) that you can use to read data from the database or manipulate the data stored therein.

There are two functions for getting information out of the database:

1. The first function is revDataFromQuery, which returns a single variable, with the selected fields ofthe records that fit the criteria in your SQL query in TSV text format.

Example:

Code: Select all

put "SELECT cust_id,cust_name FROM Customers" into tQuery
put revDataFromQuery(return,tab,tConnectionID,tQuery) into tData
put tData into field "Table Field"

You would have a return-and-tab-delimited list like this:

000001<tab>Jane Doe<return>
000002<tab>Jeff Doe<return>
000003<tab>John Doe<return>

This function is great if you're looking to display some data easily and quickly. But it would be hard to parse out individual fields, and that's where the next function comes to play.

2) The second function is revQueryDatabase, which executes the query and returns a cursor ID - this doesn't contain the data itself, it's an integer that points to a 'database cursor' which itself is a collection of pointers to the records in the database that fulfil the SQL query's requirements.

You can think of this as navigating cards in a stack where the stack is identified as the cursor ID.

To determine how many records there are and which is the current one
* revNumberOfRecords(<cursor id>)
* revCurrentRecord(<cursor id>)

To navigate the records in the result set, you use the commands:
* revMoveToFirstRecord <cursor id>
* revMoveToPreviousRecord <cursor id>
* revMoveToNextRecord <cursor id>
* revMoveToLastRecord <cursor id>

To determine what fields are in those records, you use:
* revDatabaseColumnCount(<cursor id>)
* revDatabaseColumnNames(<cursor id>)

To fetch the individual fields of the current record, you use:
* revDatabaseColumnNumbered(<cursor id>,<column number>)
* revDatabaseColumnNamed(<cursor id>,<column name>)

To release the result set from memory, you use:
* revCloseCursor <cursor id>

[danielrr2]

Thanks so much for all the info Stam. I'm sorry I missed the obvious places to look for the info. Thanks for your patience as well

[LCMark]

@danielrr: One particularly important thing when using SQLite is to remember to explicitly begin and end a transaction around each 'batch' of SQL commands you are running to insert/modify data.

If you don't then an implicit one will be created around each individual SQL command.

This is relatively expensive (particularly on Windows!) as SQLite has to acquire locks on files whenever a transaction starts - so you only want it to do that when necessary.

Note: You don't have to do this if just using 'SELECT' - as SQLite will only take a lock when it needs to modify or add data/records.

[stam]

@danielrr: One particularly important thing when using SQLite is to remember to explicitly begin and end a transaction around each 'batch' of SQL commands you are running to insert/modify data.

Hi Mark - a couple of years ago I needed to do a large number of SQL inserts. I had these batched in a begin transaction statement and it was very slow. I think either you or Ali recommended I just do these in a loop and with individual transactions and it was magically faster - something about overheads.

Is that not the case anymore?

[LCMark]

@stam: That would be this thread - viewtopic.php?f=18&t=37824...

Originally you were doing:

Code: Select all

BEGIN
<single insert>
COMMIT

(Which is no different to just doing <single insert> - as that creates an implicit transaction}.

My advice was to do:

Code: Select all

BEGIN
repeat for each insert
    <single insert>
end repeat
COMMIT

The difference being that the lock is taken once before all the inserts and dropped afterwards; rather than being taken before each insert and dropped after each insert.

We also discovered that single insert statements was generally a bit quicker than a large single INSERT statement with lots of records of values.

[stam]

Thanks Mark