SQL

Android devices include an SQL database using the SQLite product. SQLite is a self-contained, transactional SQL database engine. RFO-BASIC! provides an interface to SQLite.

Each database is a file, which can contain tables of information. Databases reside in a subdirectory of the RFO-BASIC! installation directory,. Within a database, the SQL.NEW_TABLE and SQL.DROP_TABLE statements create and delete tables.

A table comprises rows and columns. A single row of a database might describe a single person, song in a music collection, or item in a store's inventory. If the rows of a database described persons, each row's columns might give first name, last name, address, and phone number. In any table row, any column might not contain information. These columns have null values.

The BASIC program gains access to the database by forming a query specifying how to select rows from the database. The result is a view into the table, containing some or all of its rows. The view may also contain data derived from information in the table. Rows of the view may be grouped or sorted as the programmer specifies.

The SQL.QUERY statement submits a query to SQLite, which returns a cursor into that view of the database. The BASIC program can use this cursor to step through the rows that satisfy its query. After a single pass through the view, the cursor becomes unusable, though the program can make the same query again.

Most of the SQL. statements of RFO-BASIC take the values of their parameters and assemble a command to SQLite. The descriptions of some statements, such as SQL.NEW_TABLE, show the exact form of the resulting command to SQLite. The SQL. unit is designed to be simple, but it gives you access to a small number of the features of SQLite.
 * Regular SQL. statements versus the raw interface

It is always an option to compose your own command to SQLite and submit it through the raw interface discussed below. For example, to give a table column a constraint (such as to never be null, or to always have its value validated), or to add or remove columns from a table, you would use the raw interface.

Most of the SQL. statements provide a string-only interface to SQLite. Any columns you specify when creating a table use the SQLite data type. SQLite returns any null values to the BASIC program as the empty string,. To convert numeric values to strings to store in a database, BASIC provides STR$. In addition, FORMAT$ and USING$ perform formatting, including locale-specific formatting. To get the numeric value of a string read from a table, BASIC provides VAL.
 * A string-only interface

SQLite has the tools to apply powerful numeric expressions. For example, there are SQLite functions to obtain the maximum value of a column in any row of the table, or the average of a column across all rows. You can use the raw interface to create numeric columns, then to use these expressions to reduce your view of a table to the rows that are exactly relevant. Performing computations on each row of a table inside SQLite is usually more efficient than having a BASIC program loop through all rows of the table because there is no repeated interpretation of BASIC statements.

Use the raw interface to create numeric columns especially when their values will be compared. String representations of numeric values do not automatically sort as you expect (but see the programming note below for a work-around).

SQLite is the most widely deployed SQL database engine in the world. Resources include the following:
 * For more information
 * Full details about SQLite are on the SQLite Home Page.
 * An online tutorial on SQL is at www.w3schools.com.
 * A tutorial on the SQLite dialect of SQL is at SQLiteTutorial.net.

Database management
An RFO-BASIC! program must use SQL.OPEN to open a database before it can use any tables in that database. It refers to tables by name (a string expression), and can use SQL.CREATE_TABLE to create new tables, and use SQL.DROP_TABLE to destroy tables. It should use SQL.CLOSE to close any database it has opened, after completing its operations on the tables in that database.

SQL.OPEN
Opens a database

SQL.OPEN , 
 * Synopsis

The SQL.OPEN statement opens a database for access.  is the name of the database. If there is no database by that name, SQL.OPEN creates it.
 * Description

 can contain the string value. In this case, SQL.OPEN creates a temporary database in main memory. Temporary databases go away when the program stops executing.

Like files, which the BASIC program operates on with pointers, the SQL.OPEN statement returns a pointer, in , which the program must use in subsequent commands to refer to the database it opened. If a BASIC program opens several databases at the same time, these pointers will be different and will specify the database to which any subsequent SQL. statement applies.

SQL.CLOSE
Closes a database

SQL.CLOSE 
 * Synopsis

A BASIC program should always execute SQL.CLOSE on all databases it has opened. Failure to do so can reduce the amount of memory available on your Android device.  The SQL.CLOSE statement closes a database previously opened with SQL.OPEN. The variable  specifies which database to close; SQL.CLOSE sets this variable to 0. It no longer refers to any database, but can be reused, such as in another call to SQL.OPEN.
 * Description

SQL.NEW_TABLE
Creates a new table SQL.NEW_TABLE ,. There is no upper limit on the number of columns, but there must be at least one column named.
 * Synopsis

If  directly to SQLite.
 * To determine if a table already exists, see the programming note below.

SQLite also creates a unique column in each new table, named. Usually, it is the tree's primary key. RFO-BASIC! programs can use it, if desired.  RFO-BASIC! always adds a column named  to every new table. This is called a row index column and is unique for each row in that table. For example, the row index column of the fifteenth row created would have the value. The row index counter can be used to connect information in one table to another table. For example, the  in a customer table could appear in a database of orders to uniquely identify a customer, in place of name or phone number or other information from the customer table that might not always be unique.

A syntactically valid SQL.NEW_TABLE statement sends the following command to SQLite (using the statement's parameters to formulate the items shown in gray): CREATE TABLE table_name ( _id INTEGER PRIMARY KEY AUTOINCREMENT,  c1 TEXT,  c2 TEXT,   ...</SPAN>  cn</SPAN> TEXT  )
 * Translation

Row index columns, over time, can become very large; they do not just go up to the number of rows in the table, but up to the number of rows that have ever been in the table. </DIV> This shows that all the columns the BASIC program specified are created as SQL data type TEXT, and the row index column is created as INTEGER. PRIMARY KEY means the row index column contains a value that uniquely identifies the row. AUTOINCREMENT means that, every time you insert a new row, its row index column gets a value one higher than the highest row in the table.

SQL.DROP_TABLE
Drops the specified table from the database

SQL.DROP_TABLE <DB_pointer_nvar>,, which can be submitted using SQL.EXEC.
 * Synopsis

SQL.DELETE
Delete an existing row from a table

SQL.DELETE <DB_pointer_nvar>,. The string value must not start with the word WHERE; RFO-BASIC! generates it. The simplest syntax is of the form. Text literals must use single-quotes, to keep BASIC from interpreting them as the start or end of a string literal in BASIC. (To include a single-quote character in a text literal, type two consecutive single-quotes. Do not use the backslash as an escape character.)  Here is an example of a query:
 * Synopsis

First_name = 'John'

The most precise way to retrieve a single row from a table is to use the unique row index column  automatically added by RFO-BASIC!, or the comparable   column assigned by SQLite. For example:

_id = 5

A program might execute SQL.QUERY on everyone named John, as in the first example, but ask for the return of relevant columns and the row index column, whose name is always. The program analyzes the returned data to identify the one row it is interested in, and executes SQL.UPDATE with a query limited to that value of.

A query that is an empty string always makes the request apply to all the table's rows.
 * Null query

A query such as the following matches only rows where the specified column has no data: Graduate_degrees IS NULL
 * Presence or absence of data

Similarly,  matches only rows where the specified column has data.

SQLite provides many tools to formulate more complex queries. A program can obtain from the table rows that satisfy any of a list of conditions, or only those rows that satisfy all of them. This example searches for two specific persons in the table:
 * For more information

First_name = 'John' AND Last_name = 'Smith' OR First_name = 'Mary' AND Last_name = 'Doe'

SQL.QUERY
Selects certain rows from a table

SQL.QUERY <cursor_nvar>, <DB_pointer_nvar>,  and. For example: <BR />If <columns_sexp> contains , then each row of the result set contains an entire row of the table, except for the hidden columns.
 * Synopsis
 * Which rows satisfy the query. The <query_sexp> parameter is any valid SQLite query, as described above.  If SQL.QUERY omits <query_sexp> or if it is an empty string, then all the table's rows satisfy the query, and the sort option described below is not available.
 * Which order to sort the rows. If the program provided a query expression in <query_sexp>, then it may also specify a sort order with <order_sexp>.  This string contains the name of a column, which may be followed by modifiers such as these:
 * to sort the rows in ascending order (low-to-high), or
 * to sort the rows in descending order (high-to-low).
 * to perform an ascending sort and treat lowercase letters and the corresponding capital letter as the same. In place of , you can use   to specify a descending sort.
 * to perform an ascending sort and use a sort order appropriate for the device's locale. For example, this ensures that international characters such as é are treated as e instead of being sorted according to their code order (in which they are beyond the traditional letters).  In place of , you can use   to specify a descending sort.

The contents of <query_sexp> and <order_sexp> are passed directly to SQLite. This description is a small fraction of SQLite's capabilities and omits many of the things you can put in these parameters. For example, <order_sexp> can contain one sort specification or a list of sort specifications separated by commas, perhaps to sort by first name those rows where last name is identical. SQLite contains many numeric operators, such as AVG, which can be applied to numeric columns of a database table and avoid conversion problems that would occur if the column data are brought out to BASIC first.

A syntactically valid SQL.QUERY statement sends the following command to SQLite: SELECT columns</SPAN> FROM table_name</SPAN> WHERE query</SPAN> ORDER BY order</SPAN>
 * Translation

Query the PERSONNEL table for a list of employees living in Hudson, highest salaries first. In the case of several employees with the same salary, sort by last name, then first name.
 * Example

SQL.OPEN DBptr, "ACME_CORP" Query1$ = "Hometown = 'Hudson'" DesiredColumns$ = "First_name, Last_name, Salary, Phone_no" SortOrder$ = "Salary DESC, Last_name COLLATE NOCASE ASC, First_name COLLATE NOCASE ASC" SQL.QUERY cur1, DBptr, "PERSONNEL", DesiredColumns$, Query1$, SortOrder$

SQL.QUERY.LENGTH
Get the number of rows that satisfied a query

SQL.QUERY.LENGTH <length_nvar>, <cursor_nvar>
 * Synopsis

If the program has successfully made a query and <cursor_nvar> is the cursor that was returned, then SQL.QUERY.LENGTH sets <length_nvar> equal to the number of rows that satisfied the query.
 * Description

A program cannot use SQL.QUERY.LENGTH after it uses SQL.NEXT to step through the entire list of rows that satisfied the query, because the call to SQL.NEXT that reports the end of the list also invalidates the cursor.

SQL.QUERY.POSITION
Get the position of a cursor

SQL.QUERY.POSITION <position_nvar>, <cursor_nvar>
 * Synopsis

If the program has successfully made a query and <cursor_nvar> is the cursor that was returned, then SQL.QUERY.POSITION sets <position_nvar> to the position of that cursor in the rows that satisfied the query. Immediately after the query is made, SQL.QUERY.POSITION reports 0. After each SQL.NEXT command, the value that SQL.QUERY.POSITION would report is incremented by 1.
 * Description

A program cannot use SQL.QUERY.POSITION after it uses SQL.NEXT to step through the entire list of rows that satisfied the query, because the call to SQL.NEXT that reports the end of the list also invalidates the cursor.

SQL.NEXT
Step to the next row included in a query

SQL.NEXT <done_lvar>, <cursor_nvar>{, }
 * Synopsis

A query (performed by either SQL.QUERY or SQL.RAW_QUERY) identifies rows from a named table and provides a cursor with which to step through those rows. If the BASIC program has opened a database and done a query on one of its tables, which returned <cursor_nvar>, then it steps through the rows by repeatedly executing the SQL.NEXT statement.
 * Description

The first execution returns data from the first row that satisfied the query. Any execution of SQL.NEXT that returns row data (including from the final row of the query) sets <done_lvar> to FALSE (0). Executing SQL.NEXT after it has returned all the rows of the query sets <done_lvar> to TRUE (1). It also sets <cursor_nvar> to 0, preventing further use of this cursor, though the variable itself can be reused, such as to be the cursor of a subsequent query. A template for processing the result set of a query is as follows:

DO SQL.NEXT <done_lvar>, <cursor_nvar>, IF <done_lvar> THEN D_U.BREAK  % Exit loop when data is exhausted % Process the UNTIL 0

Following the first two parameters may be variables into which SQL.NEXT deposits values from the row it retrieved. If there are no variables, then SQL.NEXT does nothing but step the cursor one row through the result set. The parameter shown as in the synopsis above can take several forms.
 * Results

If some columns of the table are designed to represent numeric values, SQL.NEXT must provide string variables to hold their string representations. The program can then use VAL to obtain the numeric values. See the note at the start of this article. </DIV> The <columns_sexp> parameter provided to the prior call to SQL.QUERY specified which columns each SQL.NEXT should return, and in which order. If SQL.NEXT provides a comma-separated list of string variables, each one receives the data of one column. If there are more variables than columns, the excess variables are unchanged. If there are more columns than variables, the excess columns are not returned to the program.

In its simplest form, is a comma-separated list of string variable names. Each variable receives the data of one column. A synopsis of this form of SQL.NEXT is as follows:

SQL.NEXT <done_lvar>, <cursor_nvar>, <columndata1_svar>{, <columndata2_svar> ...}

Following — or instead of — any string variables may be a string array. SQL.NEXT creates, or increases the dimension of, the array if necessary to hold all the columns the query calls for. In this case, the synopsis is:

SQL.NEXT <done_lvar>, <cursor_nvar>, {<columndata1_svar>, {<columndata2_svar> ...,}} <others_sarr>{, <ncol_nvar>}

SQL.NEXT writes the values of any column(s) that were not written to the preceding string variables into the array. Therefore, no additional string variables are allowed after the string array. If no column data are written to the array, the array has one element, an empty string "". If the variable names an array that already exists, it is overwritten.

In the case an array is used, it can be followed by another comma and a numeric variable, shown above as <ncol_nvar>. SQL.NEXT sets this to the total number of columns retrieved. This is not necessarily the same as the size of the array because:
 * The count includes the column data returned in any string scalars that preceded the array in the parameter list; and
 * If no columns were left to assign to the string array, the count will include 1 to reflect the empty string written to the first element of the array.

SQL.NEXT done, cursor, firstname$, lastname$, cv$[], nCols
 * Examples

Assuming a previous query that selected rows from the table and specified to return first name, last name, and other columns from each row, this SQL.NEXT uses the query's cursor, puts the first name in firstname$, the last name in lastname$, and as many other columns that were specified in the query into the array cv$[]. It sets nCols to the number of columns that cursor obtained, and sets done to 0.

If the program had already retrieved the final row satisfying the query, SQL.NEXT sets done to 1 and sets cursor to 0.

SQL.UPDATE
Change the contents of one or more rows of a table

SQL.UPDATE <DB_pointer_nvar>,  character, which BASIC also uses to separate statements. Do not use  to append another BASIC statement on the same line, after SQL.UPDATE. The preprocessor assumes that the text after  is part of the SQL.UPDATE statement. </DIV> The SQL.UPDATE statement makes changes to one or more rows of the table named  character followed by a query in the format given above. If the query is empty or omitted, then the specified changes are made to every row of the table.
 * Synopsis
 * Description

A syntactically valid SQL.UPDATE statement sends the following command to SQLite: UPDATE table_name</SPAN> SET c1</SPAN>=v1</SPAN>, c2</SPAN>=v2</SPAN>, ... </SPAN>cn</SPAN>=vn</SPAN> WHERE query</SPAN>
 * Translation

Raw interface
Using the raw interface and the ordinary BASIC interface could produce undesired side-effects. For example, if cursors were open using SQL.QUERY and the program submitted a raw "INSERT" command to SQLite, it could create columns that would satisfy the queries but would not be in the result set. A BASIC program could use the raw interface to drop a table into which it had cursors. Using those cursors would produce a run-time error. </DIV> In some cases, it is more convenient not to use the RFO-BASIC! statements that present a BASIC-style interface to SQLite, but to submit "raw" commands directly to SQLite. The following two statements enable this in BASIC programs.

SQL.EXEC
Submit a raw database command directly to SQLite

SQL.EXEC <DB_pointer_nvar>, <command_sexp>
 * Synopsis

The SQL.EXEC statement lets a BASIC program execute any non-query SQLite command on a previously opened database, specified by <DB_pointer_nvar>.
 * Description

SQLite has many features that are not represented in the RFO-BASIC! interface described in this article. For example, to evade the strings-only limitation of RFO-BASIC! and create a table with numeric columns, a BASIC program could submit a raw command:
 * Examples

CommandText$ = "CREATE TABLE " + tbname$ + " ( " + ~ "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + ~   % Optional, but imitates RFO-BASIC! behavior  colname1$ + " TEXT, " + ~  colname2$ + " TEXT, " + ~  colname3$ + " INTEGER, " + ~  colname4$ + " INTEGER) " SQL.EXEC dbptr, CommandText$

As another example, SQLite has a transaction interface that lets a program specify changes to several tables with the guarantee that either all changes will occur, or none will. Access to this feature requires that the program issue raw commands such as. A BASIC program can also execute SQLite  commands to set the general behavior of the database.

SQL.RAW_QUERY
Submit a raw query command directly to SQLite

SQL.RAW_QUERY <cursor_nvar>, <DB_pointer_nvar>, <query_sexp>
 * Synopsis

The SQL.RAW_QUERY statement lets a BASIC program execute any SQLite query command on a previously opened database, specified by <DB_pointer_nvar>. SQL.RAW_QUERY returns a cursor with which to gain access to the table rows that satisfy the query.
 * Description

The SQL.RAW_QUERY statement does not have the parameters that SQL.QUERY has that specify desired columns and sort order. This is achieved in the raw query in the SELECT clause included in <query_sexp>.

Existence of tables
An RFO-BASIC! program may want to determine whether a table exists. A pertinent example is if the program uses a database table to store persistent settings (information it wants to survive from one running of the program to the next).

The first time the program is ever run, the table does not exist. The program uses SQL.NEW_TABLE to create it, then writes to it the default values for those settings. On subsequent runs, the table does exist. The program must not create it again, but must read the current values for each setting from the table. It is also possible that the user deletes the database, such as with the Android File Manager. The user's intention might be exactly to return the BASIC program to its default settings. In this case, the program must use SQL.NEW_TABLE again.

In summary, the program should follow two courses of action depending on whether a table exists. There are two ways to detect this:

Your SQLite database contains not just the tables you created, but a table called, whose rows describe each object in the database, including your tables. Therefore, you can query  to see if a specified table exists.
 * Query the database metadata

SQL.OPEN MyDatabase, "ABC-DATABASE" QueryText$ = "SELECT * FROM sqlite_master WHERE type='table' AND tbl_name='SETTINGS';" SQL.RAW_QUERY MyCursor, MyDatabase, QueryText$

The program doesn't even need to use SQL.NEXT to walk through the result set. was so specific that the result set either contains 0 or 1 row, the number of tables named SETTINGS that exist. We can obtain the number of rows as follows:

SQL.QUERY.LENGTH TableAlreadyExists, MyCursor IF TableAlreadyExists THEN  % 1 is "TRUE" and the following statements are executed ... ENDIF

The program can try to create a table and write default settings to it. SQL.NEW_TABLE fails if the table already exists. This programming error can be trapped using the ONERROR: handler. The initialization code might take this form: SQL.OPEN MyDatabase, "ABC-DATABASE" AmTestingForTable = 1   % Prepare for the next statement to fail! SQL.NEW_TABLE MyDatabase, "SETTINGS", "SettingName", "SettingValue" AmTestingForTable = 0 ! At this point, we create rows in ! "SETTINGS" for each of the desired ! persistent settings TableAlreadyExists: ! Here we have either created the table ! or found that it already exists. Read ! the settings from the table into ! program variables.
 * Let SQL.NEW_TABLE fail

The error handler may have many additional jobs, but we use the variable  to indicate that the error occurred trying to create a table. The error handler cannot return to the previous spot, as interrupt handlers can, but it can execute a suitable GOTO:

ONERROR: IF AmTestingForTable THEN GOTO TableAlreadyExists ...

Strings representing numbers
For RFO-BASIC! programs that do not use the raw interface, every column specified for an SQL table is of type. The text may have a numeric value, such as "2".

Comparing two such columns does not always give the result you expect. For example, "10" is a smaller value than "2" because of its smaller first character. One work-around is to use one of RFO-BASIC!'s formatting functions to prepend leading zeros (so that "0010" is greater than "0002"). However, this forces you to decide what the largest-ever value will be, and numbers with minus signs still do not sort in the intuitive way.

SQLite lets you use these columns in numeric expressions, and it converts them to their numeric value before doing further processing. The simplest way to do this is to add zero to the column data. For example, as <order_sexp> in an SQL.QUERY statement, you might specify. This would make SQLite sort the table not by the value of the  columns but by the value of the numbers they represent.

Conserving row index columns
Row index columns, such as the  column that RFO-BASIC! adds when a program executes SQL.NEW_TABLE, or the  column that SQLite adds to every new table, can get large. A new row index is assigned whenever a new row is inserted, but no row index changes when a row is deleted. For example, the magnitude of a row index for a customer table would reflect not the number of current customers but the number of customers there have ever been.

To make such tables more manageable, without techniques like a batch program to copy information to a new customer table with "packed" row indexes, a user-defined function can use SQL to find the best row index to assign to a new row:

FN.DEF useNum(dbPtr, ffile$, ffield$) ! ! Find the smallest unused row index number ! dbPtr  = Pointer to your database ! ffile$ = your table file ! ffield$ = the field serving as the row index ! !Formulate a query of places where successive row indexes do not differ by 1 query$ = "SELECT a."+ffield$+" AS aa, c."+ffield$+" AS cc,a."+ffield$+" - 1 AS useNum "~ "FROM "+ffile$+" AS a "~ "LEFT JOIN "+ffile$+" as c "~ "ON   c."+ffield$+" = a."+ffield$+" - 1 "~ "WHERE c."+ffield$+" is null "~ "AND  a."+ffield$+" > 1 "~ "ORDER BY a."+ffield$+" LIMIT 1" SQL.RAW_QUERY cur, dbPtr, query$ SQL.QUERY.LENGTH rows, cur !If the above exercise produced rows, return the first "hole" IF rows > 0 THEN SQL.NEXT xdone, cur, aNum$, cNum$, useNum$ FN.RTN val(useNum$) !Otherwise, query the largest value anywhere and return one greater ELSE query$ = "SELECT max("+ffield$+") as useNum FROM "+ffile$ SQL.RAW_QUERY cur, dbPtr, query$ SQL.QUERY.LENGTH rows, cur IF rows > 0 THEN SQL.NEXT xdone, cur,useNum$ FN.RTN val(useNum$) + 1 ENDIF !If there are no values at all, it's safe to use 1 ENDIF FN.RTN 1 FN.END

Memory leak on creation of cursors
RFO-BASIC! closes a cursor for an SQL statement at the end of the execution of that statement. It closes a cursor for a query when the program executes SQL.NEXT that reports the end of the result set.

However, creating a cursor consumes memory of the Android device, and closing it does not release the memory. There is currently no way (such as an SQL.CLC statement) to reuse that memory. Therefore, a program that created cursors continually would eventually run out of memory and fail.

Editor's queries

 * SQL.CLOSE sidebar: BASIC does not automatically close databases on exit?  "Failure to do so can reduce the amount of memory available on your Android device."  This sounds like damage will be done to the device.
 * Are the column names case-sensitive? Does the use of underscore in our examples mean that column names cannot include spaces?