SQLite

You asked for it, so here a guide to SQLite.
Livecode can work with any database. Livecode has integrated SQLite, but you can interface with any database that has an ODBC interface (almost all have it), here are the most popular:

• SQLite
• MySQL
• Oracle
• PostgreSQL
• Valentina DB

Databases are usually files outside our program that can be on the same PC on which our program runs or on a remote server. The databases contain lots of cataloged information. Thanks to the databases we can search for information, insert or delete data, all according to specific criteria that we set ourselves.
The language used by almost all databases is SQL. Generally the valid commands for a database also apply to all others.
Summarizing in a few words a database, we can say that it is a collection of tables, within each table the data are organized by rows and by columns.
Databases have advantages:

• quick to find the data you need among thousands of data
• take up little space
• they are optimized to do their job
• they are very fast (very, very, very fast)
• it is possible to cross the data of several tables easily

You could do the same things as a database with an array; but if you deal with a lot of data, I suggest you learn how to use databases.

Connection

A database can be an external running program, you talk with it and it answers you. Thisi is called client/server database. (MySQL, Oracle, PostgreSQL)
A database can be simply just a file (SQLite).
To use a database you must first connect to a database (server or file), using the revOpenDatabase () function and store the connection identifier. For example with SQLite we will write:

put revOpenDatabase("sqlite", "./myDB.sqlite", , , , ) into connID




If livecode doesn't find the file, Livecode will create it.
In this example the connID variable contains the connection identifier (it's just a number). You can establish multiple connections simultaneously.

Query

To retrieve information from the database, in jargon it is said to query, just use the command the revDataFromQuery function, specifying how to separate rows and columns:

put "SELECT * FROM users ; " into tSQL
put revDataFromQuery(tab,return,connID,tSQL) into tRecords




in this case we have that tRecords contains all the data where each column is separated from the other by the TAB character and each row by a line. You can change this form, but it is very convenient because it allows you to view the data in a text field like a real table (field, label).

Execution

To execute commands that change in the database, or make more complex actions, just use the revExecuteSQL command, for example:

put "INSERT into users (name,surname) VALUES ('Jack','Sparrow')" into tSQL
revExecuteSQL connID,tSQL

Speeding execution

Sometime you need to send many commands one after another, like inserting a lot of data, or updating a lot of data. In this case is much better to use transactions. Transaction is a list of task, the database reads them all and then decide the best strategy to do all task in the shortest time possible.
For example:

put "BEGIN TRANSACTION;" into myQuery
put "INSERT INTO myTable (name, surname) VALUES ('Mark', 'Red') ;" after myQuery
put "INSERT INTO myTable (name, surname) VALUES ('John', 'Green') ;" after myQuery
--..... a lot of data more
put "INSERT INTO myTable (name, surname) VALUES ('Elvis', 'Yellow') ;" after myQuery
put "COMMIT;" after myQuery
revExecuteSQL connID,myQuery

Check  column existence

SQLite is simple fast, portable and compact, but it misses some complex function like how to know if a table column exists. You can do it, but it needs to call PRAGMA.
Here the example code:

put "PRAGMA table_info(myTable);" into tSQL
put revDataFromQuery(comma,return,connID,tSQL) into tRecords
if "muyColumn" is not among the items of tRecords then   
   answer "Column myColumn doesn't exist"
else
   answer "Column myColumn exists!"
end if

Close the connection

When working on databases installed on client/server type, it is advisable to close the connection when you no longer work with the database, here is the code:

revCloseDatabase connID