REAL SQL Database Language Reference

From Real Software Documentation

Contents 1 Real SQL Database Language Reference 2 SQL in Real Studio 3 Character Functions 4 SQL Functions 5 Aggregate Functions 6 Calling SQL Commands 7 SQL Command Reference 8 ALTER TABLE Statement 8.1 Syntax 8.2 Notes 8.3 Example 8.4 See Also 9 ANALYZE 9.1 Syntax 9.2 Notes 9.3 See Also 10 ATTACH DATABASE 10.1 Syntax 10.2 Notes 10.3 See Also 11 BEGIN Statement 11.1 Syntax 11.2 Notes 11.3 See Also 12 comment Keyword 12.1 Syntax 12.2 Notes 13 COMMIT Statement 13.1 Syntax 13.2 Notes 13.3 Example 13.4 See Also 14 CREATE INDEX Statement 14.1 Syntax 14.2 Notes 14.3 Example 14.4 See Also 15 CREATE TABLE Statement 15.1 Syntax 15.2 Notes 15.3 Example 15.4 See Also 16 CREATE TRIGGER Statement 16.1 Syntax 16.2 Notes 16.3 Example 16.4 See Also 17 CREATE VIEW Statement 17.1 Syntax 17.2 Notes 17.3 See Also 18 CREATE VIRTUAL TABLE Statement 18.1 Syntax 18.2 Notes 18.3 See Also 19 DELETE Statement 19.1 Syntax 19.2 Notes 19.3 Example 19.4 See Also 20 DETACH DATABASE Statement 20.1 Syntax 20.2 Notes 20.3 See Also 21 DROP INDEX Statement 21.1 Syntax 21.2 Notes 21.3 Example 21.4 See Also 22 DROP TABLE Statement 22.1 Syntax 22.2 Notes 22.3 Example 22.4 See Also 23 DROP TRIGGER Statement 23.1 Syntax 23.2 Notes 23.3 See Also 24 DROP VIEW Statement 24.1 Syntax 24.2 Notes 24.3 See Also 25 END TRANSACTION Statement 25.1 Syntax 25.2 Notes 25.3 See Also 26 EXPLAIN Modifier 26.1 Syntax 26.2 Notes 27 INSERT Statement 27.1 Syntax 27.2 Notes 27.3 Example 27.4 See Also 28 ON CONFLICT Clause 28.1 Syntax 28.2 Notes 29 PRAGMA Statement 29.1 Syntax 30 REINDEX Statement 30.1 Syntax 30.2 Notes 30.3 See Also 31 REPLACE Statement 31.1 Syntax 31.2 See Also 32 ROLLBACK Statement 32.1 Syntax 32.2 Notes 32.3 See Also 33 SELECT Statement 33.1 Syntax 33.2 Notes 33.2.1 Wildcard Searches 33.3 SELECT Examples 33.3.1 Simple Selection 33.3.2 Relational Operations 33.3.3 Wildcard Search 33.3.4 Aggregate Functions 34 UPDATE Statement 34.1 Syntax 34.2 Notes 35 VACUUM Statement 35.1 Syntax 35.2 Notes 36 SQL Functions 37 ABS Function 37.1 Syntax 37.2 Example 38 Coalesce Function 38.1 Syntax 38.2 See Also 39 Glob Function 40 IfNull Function 40.1 Syntax 40.2 See Also 41 Last_Insert_rowid Function 41.1 Syntax 41.2 Example 42 Length Function 42.1 Syntax 43 Like Function 43.1 Syntax 43.2 Notes 44 LOWER Function 44.1 Syntax 44.2 Example 44.3 See Also 45 Max Function 45.1 Syntax 45.2 Example 45.3 See Also 46 Min Function 46.1 Syntax 46.2 Example 46.3 See Also 47 NullIf Function 47.1 Syntax 47.2 Example 48 POSITION Function 48.1 Syntax 48.2 Example 48.3 See Also 49 Quote Function 49.1 Syntax 50 Random Function 50.1 Syntax 50.2 Example 51 Round Function 51.1 Syntax 51.2 Examples 52 SQLite_Version Function 52.1 Syntax 52.2 Example 53 SUBSTR Function 53.1 Syntax 53.2 Notes 53.3 Example 53.4 See Also 54 TRIM Function 54.1 Syntax 54.2 Notes 54.3 See Also 55 TYPEOF Function 55.1 Syntax 56 UPPER Function 56.1 Syntax 56.2 Example 56.3 See Also 57 CURRENT_DATE Function 57.1 Syntax 57.2 Example 58 CURRENT_TIME Function 58.1 Syntax 58.2 Example 58.3 See Also 59 CURRENT_TIMESTAMP Function 59.1 Syntax 59.2 Example 59.3 See Also 60 Aggregate Functions 61 AVG Function 61.1 Syntax 61.2 Note 61.3 Example 61.4 See Also 62 COUNT Function 62.1 Syntax 62.2 Notes 62.3 Example 62.4 See Also 63 MAX Function 63.1 Syntax 63.2 Example 63.3 See Also 64 MIN Function 64.1 Syntax 64.2 Example 64.3 See Also 65 SUM Function 65.1 Syntax 65.2 Notes 65.3 Example 65.4 See Also

Real SQL Database Language Reference

A Real Studio database front-end uses the Structured Query Language (SQL) to communicate with its data sources. The plug-in for your data source receives a SQL statement from Real Studio, translates the statement into a form that the data source understands (if necessary), and sends it to the data source. All versions of Real Studio include a single-user SQL-based data source. The other data sources are fully supported only in the Professional and Enterprise versions of Real Studio.

The Real SQLdatabase data source supports the subset of SQL described in this chapter. The plug-in for this data source is shipped as a integral part of the Real Studio application so there is no need to install a separate file in your plugins directory. The other supported data sources are native SQL back-ends. When you are working with any native SQL back-end, its Real Studio plug-in passes your SQL to the data source. Therefore any valid SQL for that data source will work in a Real Studio front-end for that source. Please refer to the documentation for your selected data source for further information on supported SQL and specifics on the data types supported by that data source. In the case of REALSQLdatabase, it is based on SQLite; it is described at http://www.sqlite.org. The SQLite documentation is at http://www.sqlite.org/docs.html.

If you are unfamiliar with SQL, you will need to learn its basics before implementing your Real Studio front-end. This manual does not attempt to teach you SQL; rather, it describes the subset of SQL that is currently supported for the REAL SQL Database engine. Please consult one of the many good SQL references, such as SQL for Dummies by Allen G. Taylor (ISBN: 0-7645-0105-4), The Practical SQL Handbook, by Bowman, Emerson, and Darnovsky (ISBN: 0-2014-4787-8). You can also find several good online SQL references by searching for “SQL command syntax” or for a specific SQL command using http://www.google.com or a comparable search engine.

SQL in Real Studio

This section provides an overview of SQL for the Real SQL Database. Here are the supported SQL statements.

Statement	Description
ALTER TABLE	Renames a table or adds a column to an existing table.
ANALYZE	Gathers statistics about indexes in the database and stores them in special tables in the database for the query optimizer.
ATTACH DATABASE	Attaches another database to the main database.
BEGIN TRANSACTION	Manually begins a transaction.
comment	Denotes text as nonexecutable comments, treated as whitespace by the SQL parser.
COMMIT	Commits changes (inserts, deletes, and updates) to the data and changes to the database schema (CREATE TABLE, CREATE INDEX, and so forth).
CREATE INDEX	Creates an index in a table.
CREATE TABLE	Creates a table and specifies the fields and their attributes.
CREATE TRIGGER	Adds database operations that are performed automatically when an event occurs.
CREATE VIEW	Creates a prepackaged SELECT statement that can be used in the FROM clause of another SELECT statement in place of a table name.
CREATE VIRTUAL TABLE	Creates an interface to an external storage or computation engine that appears to be a table but does not actually store information in the database file.
DELETE	Deletes a specified group of records.
DETACH DATABASE	Detaches a database that was attached with an ATTACH DATABASE statment.
DROP INDEX	Removes the specified index from a table.
DROP TABLE	Removes a table from the database.
DROP TRIGGER	Removes a trigger that was added with a CREATE TRIGGER statement.
DROP VIEW	Removes a view that was added with a CREATE VIEW statement.
END TRANSACTION	Ends a transaction that was initiated by BEGIN TRANSACTION.
EXPLAIN	Non-standard modifier that requests the virtual machine instructions that would have been used to execute a command.
INSERT	Inserts a record into the specified table.
ON CONFLICT	Non-standard clause that specifies what algorithm to use to resolve constraint conflicts.
PRAGMA	Directive to modify the operation of the library or to query the library for internal (non-table) data.
REINDEX	Deletes and then recreates indexes.
REPLACE	An alias for the Insert or Replace variant of the INSERT statement.
ROLLBACK	Cancels changes (inserts, deletes, updates) to the data and changes to the database schema (CREATE TABLE, CREATE INDEX, and so forth).
SELECT	Returns a group of rows, known in Real Studio as a recordset. In the language of SQL, this is also called a cursor. You can specify the columns (fields), the table(s), search conditions, grouping, and sort columns.
UPDATE	Updates existing records in a table.
VACUUM	Cleans up empty space left in the database from other operations.

Character Functions

The following functions compute values from the character or bit string passed to it.

Function	Example	Description
BIT_LENGTH	BIT_LENGTH('zz') returns 32	Takes either a character string or a bit string and returns the number of bits. BIT_LENGTH (string)
CHAR_LENGTH	CHAR_LENGTH ('480') returns 3.	Returns the number of characters in the string passed. CHAR_LENGTH (string)
LOWER	LOWER('GREENHORNET') returns “greenhornet”.	Takes a character string and returns the string in all lowercase. LOWER (string)
OCTET_LENGTH	OCTET_LENGTH ('FF') returns 2.	Returns the number of bytes in a character string or bit string expression. OCTET_LENGTH (string)
POSITION	POSITION ('G' IN 'Fred Gorman') returns 6.	Searches for the specified target string within the source string and returns as an integer the character position where the target string starts. If it doesn’t find the target string, it returns zero. POSITION (targetString IN sourceString)
SUBSTRING	SUBSTRING ('Samantha' FROM 1 For 3) returns ‘Sam’.	Returns the specified substring of characters from the sting passed. SUBSTRING (string FROM start [FOR length]) Start is the starting position; if the optional FOR clause is omitted, SUBSTRING returns the characters from start to the end of the string. If FOR is included, SUBSTRING returns the length characters beginning at start.
TRIM	TRIM BOTH from ' The Matrix ' returns 'The Matrix'. Same as TRIM BOTH ' ' from ' The Matrix '.	Trims leading and/or trailing characters from the string passed. The default character to trim is the blank. TRIM (LEADING|TRAILING|BOTH [trimstring] FROM targetString)
UPPER	UPPER('Grand blanc') returns “GRAND BLANC”.	Takes a character string and returns the string in all uppercase. UPPER (string)

SQL Functions

The following date, time, and ID functions are available:

Function	Example	Description
CURRENT_DATE	CURRENT_DATE	Returns the current date in SQL Date format, YYYY-MM-DD, as a SQL Date data type. CURRENT DATE
CURRENT_TIME	CURRENT_TIME	Returns the current time in SQL Time, HH:MM:SS, as a SQL Time data type. CURRENT TIME.
CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	Returns the current date-time in SQL TimeStamp format, YYYY-MM-DD HH:MM:SS, as a SQL TimeStamp data type.

Aggregate Functions

The following functions are aggregate functions, sometimes known as set functions. They apply to sets of rows in a table and return the results of an arithmetic calculation. You determine the number of rows on which the calculation is based using a standard WHERE clause and include the Set function in a SELECT statement. You can use several Set functions in a SELECT statement and each calculates its value on the rows specified by the WHERE clause. However, you cannot use the DISTINCT keyword more than once. The SELECT statement returns a RecordSet. If the WHERE clause is omitted, the function is computed on all the rows in the table.

If you use the GROUP BY clause with a aggregate functions, the result will contain one row per group, as defined by the GROUP BY clause. The aggregate functions compute the summary statistics per group. With no GROUP BY clause, one row will be returned, with summary statistics for the all the records that were queried.

Function	Description
AVG	Returns the numeric average of an expression. The expression must evaluate to a numeric data type. The expression can include one or more columns. The DISTINCT keyword can be used to limit the computation to distinct (unique) occurrences of each value. Example: Finds the average of the Price column in the Products table, for Database products only. SELECT AVG (Price) from Products WHERE Product='Database'
COUNT	Returns the total number of rows accessed by the expression. The expression may include one or more columns. The DISTINCT keyword can be used to limit the evaluation to distinct (unique) occurrences of each value. Example: Counts the number of Customer rows for the Zip Code ‘48070’ only. SELECT COUNT (*) from Customers WHERE Zip='48070'
MAX	Returns the maximum value of the specified field or fields. The Max function operates on numeric, date, time, and character data types. The expression may include one or more columns. Example: Finds the largest population in the Cities table. SELECT MAX (Population) from Cities
MIN	Returns the minimum value of the specified field or fields. The Min function operates on numeric, date, time, and character data types. The expression may include one or more columns. Example: Finds the minimum Price in the Products table. SELECT MIN (Price) from Products
SUM	Returns the total of the specified fields. That is, the expression must evaluate to a numeric data type. The expression may include one or more columns. The DISTINCT keyword can be used to limit the evaluation to distinct (unique) occurrences of each value. Example: Finds the total population from the States table for States in the NE region only. SELECT SUM (Population) from States WHERE Region='NE'

Calling SQL Commands

You use the SELECT statement by passing it as a string to the Database class’s SQLSelect method. It returns a RecordSet object that contains the records that were requested. You use the Database class’s SQLExecute statement to execute all the other SQL statements. None of the other SQL statements return data.

The examples in this chapter assume that you’ve created an instance of the database class. The SQL example is passed as a string parameter to either the SQLExecute or SQLSelect methods.

The Syntax section gives the syntax of the SQL string, omitting the surrounding Real Studio code that’s needed to pass the SQL to the data source.

SQL Command Reference

This section is a language reference for the subset of SQL that is supported by the REALSQLdatabase data source.

ALTER TABLE Statement

Renames a table or adds a new column to an existing table. Removing a column is not supported by REALSQLdatabase.

Syntax

ALTER TABLE [database-name.]table-name ADD [Column]field-name field-type
OR
ALTER TABLE[database-name.] tablename RENAME TO [new-table-name]

Part	Description
database-name	Optional name of the database containing table-name.
table-name	Name of the existing table.
field-name	Name of the field to add.
field-type	Data type. The Real Studio database supports the following data types: integer, varchar, double, boolean, date, time, timestamp, binary (blob), and string. If you are using another vendor’s data source, see their documentation on supported data types.
new-table-name	The new name to be assigned to table-name.

The keyword "Column" is assumed and can be omitted.

Notes

Use the RENAME TO syntax to rename the table specified by DatabaseName.TableName to NewTableName. The command cannot be used to move a table between attached databases. It only renames a table in the specified database. Any triggers or indexes in the table remain after the renaming. However, any view definitions or statements executed by triggers that refer to the table being renamed are not modified to refer to the new tablename. The triggers or view definitions must be dropped and recreated.

The ADD syntax adds a new column to the specified table. The new column is added to the end of the list of existing columns. The column definition may take any of the forms that are allowed in the CREATE TABLE statement, with the following restrictions:

The new column may not have a Primary Key or Unique attribute,
The column may not have a default value of CURRENT_TIME, CURRENT_DATE, or CURRENT_TIMESTAMP.
If NOT NULL is specified, then the column must have a default value other than Null.

Example

The following example adds the column “VacationDays” to the table “Employees”.

See Also

CREATE TABLE, DROP TABLE

ANALYZE

ANALYZE gathers statistics about indexes and stores them in special tables.

Syntax

ANALYZE
or
ANALYZE database-name
or
ANALYZE [database-name.] table-name

Part	Description
database-name	The name of the database to analyze.
table-name	The name of the table in a database to analyze.

Notes

ANALYZE gathers statistics about indexes and stores them in special tables in the database where the query optimizer can use them to help make better index choices.

If no arguments are given, all indexes in all attached databases are analyzed. If a database name is passed, all indexes in that database are analyzed. If the argument is a table name, then only indexes associated with that table are analyzed.

The initial implementation stores all statistics in a single table named sqlite_stat1. Future enhancements may create additional tables with the same name pattern except with the “1” changed to a different digit. The sqlite_stat1 table cannot be dropped, but all of its content can be deleted. This has the same effect.

See Also

CREATE INDEX.

ATTACH DATABASE

Adds another database file to the current database connection. Attached databases are removed with the DETACH DATABASE statement.

Syntax

ATTACH [DATABASE] database-filename AS database-name

Part	Description
database-filename	The name of the database to be attached.
database-name	The name to refer to the attached database in the current database connection.

Notes

Tables in an attached database can be referred to using the syntax database-name.table-name. If an attached table doesn’t have a duplicate table name in the main database, it doesn’t require a database name prefix. When a database is attached, all of its tables which don’t have duplicate names become the default table of that name. Any tables of that name attached afterwards require the database name prefix. If the default table of a given name is detached, then the last table of that name attached becomes the new default.

If the filename contains punctuation characters it must be quoted. The names “main” and “temp” refer to the main database and the database used for temporary tables, respectively.

You cannot create a new table with the same name as a table in an attached database, but you can attach a database which contains tables whose names are duplicates of tables in the main database. It is also permissible to attach the same database file multiple times.

You can read from and write to an attached database and you can modify the schema of the attached database.

Transactions involving multiple attached databases are atomic, assuming that the main database is not “:memory:”. If the main database is “:memory:” then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.

There is a compile-time limit of 10 attached database files.

See Also

DETACH DATABASE

BEGIN Statement

Begins a transaction manually. The transaction usually persists until the COMMIT or ROLLBACK statement is encountered.

Syntax

BEGIN [DEFERRED | IMMEDIATE | EXCLUSIVE] [TRANSACTION [name]]
OR
END [TRANSACTION[NAME]]
OR
COMMIT [TRANSACTION[NAME]]
OR
ROLLBACK [TRANSACTION[NAME]]

Notes

Transactions can be started manually using the BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command. But a transaction will also ROLLBACK if the database is closed or if an error occurs and the ROLLBACK conflict resolution algorithm is specified. See the documentation on the ON CONFLICT clause for additional information about the ROLLBACK conflict resolution algorithm.

No changes can be made to a database except within a transaction. Any statement that changes the database will automatically start a transaction if one is not already in effect. Automatically started transactions are committed at the conclusion of the command unless the ROLLBACK command is issued.

Transactions can be deferred, immediate, or exclusive. Deferred means that no locks are acquired on the database until the database is first accessed. With a deferred transaction, the BEGIN statement does nothing, as the locks are not obtained until the first read or write operation The first read operation creates a shared lock and the first write operation creates a reserved lock.

Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN command on the current thread has executed. If the transaction is immediate, then reserved locks are acquired on all databases as soon as the BEGIN command is executed, without waiting for the database to be used. After a BEGIN IMMEDIATE command, you are guaranteed that no other thread or process will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database, however. An exclusive transaction causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN EXCLUSIVE, you are guaranteed that no other thread or process will be able to read or write the database until the transaction is complete.

The default behavior is a Deferred transaction.

The COMMIT command does not actually do the commit until all pending SQL commands finish. That is, if two or more SELECT statements are in the middle of processing when a COMMIT is issued, the commit will not actually take place until all of the SELECT statements finish.

See Also

COMMIT, END, ROLLBACK, SELECT.

comment Keyword

Signifies a comment within SQL.

Syntax

-- single line comment
OR
/* multiple line comment [*/]

Notes

Comments aren’t SQL commands, but can occur in SQL queries. They are treated as whitespace by the parser. They can begin anywhere whitespace can be found, including inside expressions that span multiple lines.

Using the first syntax, the comment can extend only to the end of the line. With the second syntax the comment can extend onto multiple lines. If the terminating delimiter is omitted, the rest of the SQL is treated as a comment.

COMMIT Statement

Commits (saves) changes to a database.

Syntax

Commit [TRANSACTION [name]]

Part	Description
name	Optional name of the transaction. Ignored by the parser.

Notes

The REALSQLdatabase supports transactions. A transaction is either a set of changes to the database schema (i.e., tables, fields, and field types) or a set of changes to the contents of the database—record additions, deletions, or modifications. A transaction begins automatically by the first such change and is saved to the database by calling COMMIT. You can instead abort the transaction by calling ROLLBACK. This restores the database to its state when the transaction was started.
You can also commit changes to the database by calling the Database class’s Commit method.

Example

The following example uses COMMIT to save a change to a table. Some examples in this reference use the Commit method of the Database class to commit changes.

See Also

BEGIN, END, ROLLBACK

CREATE INDEX Statement

Creates an index in a table.

Syntax

CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database-name.]index-name ON table-name (fieldlist)
[ON CONFLICT conflict-algorithm]

Argument	Description
database-name	Optional name of the database that contains table-name.
index-name	The name of the new index.
table-name	The name of the table that contains the fields being indexed.
fieldlist	The names of one or more fields on which to build the index. FieldList consists of one or more fields/data types, separated by commas. A FieldName can be of the form: FieldName [COLLATE collation-name] [ASC | DESC] (The DESC specification is currently ignored.)

Notes

The CREATE INDEX command consists of the CREATE INDEX keyword, followed by the name of the new index, the ON keyword, the name of a previously created table that is to be indexed, and a parenthesized list of names of columns in the table that are used for the index key. Each column name can be followed by either the ASC or DESC keywords to indicate sort order, but the sort order is ignored in the current implementation. Sorting is always done in ascending order.

The COLLATE clause following each column name defines a collating sequence used for text entries in that column. The default collating sequence is the collating sequence defined for that column in the CREATE TABLE statement. Or if no collating sequence is otherwise defined, the built-in BINARY collating sequence is used.

There are no arbitrary limits on the number of indices that can be attached to a single table, nor on the number of columns in an index.

If the UNIQUE keyword appears between CREATE and INDEX, then duplicate index entries are not allowed. Any attempt to insert a duplicate entry will result in an error. The optional CONFLICT clause allows you to specify an alternative default constraint conflict resolution algorithm for the index. This only makes sense if the UNIQUE keyword is used since that is the only supported constraint. The default algorithm is ABORT. If an INSERT or UPDATE statement specifies a particular conflict resolution algorithm, that algorithm is used in place of the default algorithm specified here. See the ON CONFLICT section for additional information.

If the optional IF NOT EXISTS clause is present and another index with the same name already exists, then this command does nothing.

The exact text of each CREATE INDEX statement is stored in the “sqlite_master” or “sqlite_temp_master” table, depending on whether the table being indexed is temporary. Every time the database is opened, all CREATE INDEX statements are read from the sqlite_master table and used to regenerate SQLite’s internal representation of the index layout.

Example

The following example creates an index called “NameIndex” in the table “Employees” using the “Name” field.

See Also

DROP INDEX

CREATE TABLE Statement

Adds a table to the database.

Syntax

CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS] [database-name.]table-name (column-def [,column-def] [,constraint])
OR
CREATE [TEMP TEMPORARY] TABLE [databasename.]tablename AS select-statement

Part	Description
TableName	Name of the new table.
Column-def	Name [Type] [[CONSTRAINT name] Column-constraint.
Type	TypeName TypeName (number) TypeName (number, number)
Column-Constraint	Optional. The following constrains may be used: NOT NULL [conflict clause] | PRIMARY KEY [sort order] [conflict-clause] [AUTOINCREMENT] | UNIQUE [conflict-clause] | CHECK (expr) [conflict-clause] | DEFAULT value | COLLATE collation-name If Not Null is used, the database will require a value for that field in every row, i.e., the field may not be missing. CHECK constraints are currently ignored.
Constraint	PRIMARY KEY (column-list) [conflict-clause] | UNIQUE (column-list) [conflict-clause] | CHECK (expr) [conflict-clause] CHECK constraints are currently ignored.
Conflict-clause	ON CONFLICT conflict-algorithm

Notes

The CREATE TABLE statement creates a table structure on the current data source. You use the Database class’s SQLExecute method to pass a CREATE TABLE statement to Real Studio.

Although there are a lot of options for special cases, the CREATE TABLE statement is usually the CREATE TABLE keyword, followed by the list of columns, their data types, and one or more optional constraints.Table names that begin with “sqlite_” are reserved for use by the engine. There is no limit on the amount of data in a row.

Each column definition is the name of the column followed by the data type for that column. It is optionally followed by one or more column constraints. The data type does not restrict the type of data may be inserted into that column. It only determines how values whose data type is ambiguous may be converted. The actual storage type is a property of the value, not the column in which it is inserted.

If the TEMP or TEMPORARY keyword is issued, then the table is visible only within the database connection and is automatically deleted when the database connection is closed. Any indices created on a temporary table are also temporary. Temporary tables and indices are stored in a separate file distinct from the main database file.

The UNIQUE constraint causes an index to be created on the specified columns. This index must contain unique keys. The COLLATE clause specifies what text collating function to use when comparing text entries for the column. The built-in BINARY collating function is used by default.

The DEFAULT constraint specifies a default value to use when doing an INSERT. The value may be NULL, a string constant, or a number. The default value may also be one of the keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. If the value is NULL, a string constant, or number, it is literally inserted into the column whenever an INSERT statement that does not specify a value for the column is executed. If the default value is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the current UTC date and/or time is inserted into the columns. For CURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, YYYY-MM-DD. The format for CURRENT_TIMESTAMP is YYYY-MM-DD HH:MM:SS.

Specifying a PRIMARY KEY normally creates a UNIQUE index on the corresponding columns. However, if primary key is on a single column that has the data affinity INTEGER, then that column is used internally as the actual key of the B-Tree for the table. This means that the column may only hold unique integer values. This is the one case in which the REALSQLdatabase enforces strong data typing for the column. An integer column with the UNIQUE PRIMARY KEY. constraints must only contain unique integer values.

If a table does not have an INTEGER PRIMARY KEY column, then the B-Tree key will be a automatically generated integer. The B-Tree key for a row can always be accessed using one of the special names ROWID, OID, or _ROWID_. This is true regardless of whether or not there is an INTEGER PRIMARY KEY. An INTEGER PRIMARY KEY column may also include the keyword AUTOINCREMENT.

If a database-name is specified, then the table is created in the named database. It is an error to specify both a database-name and the TEMP keyword, unless the database-name is “temp.” If no database name is specified, and the TEMP keyword is not present, the table is created in the main database.

The optional conflict-clause following each constraint allows the specification of an alternative default constraint conflict resolution algorithm for that constraint. The default is abort ABORT. Different constraints within the same table may have different default conflict resolution algorithms. If an INSERT or UPDATE command specifies a different conflict resolution algorithm, then that algorithm is used in place of the default algorithm specified in the CREATE TABLE statement. See the section titled ON CONFLICT for additional information.

CHECK constraints are supported. Prior to version 3.3.0 of the SQLite library, CHECK constraints were parsed but not enforced.

There are no arbitrary limits on the number of columns or on the number of constraints in a table. The total amount of data in a single row is limited to about 1 megabytes in version 2.8. In version 3.0 there is no arbitrary limit on the amount of data in a row. The CREATE TABLE AS form defines the table to be the result set of a query. The names of the table columns are the names of the columns in the result.

The exact text of each CREATE TABLE statement is stored in the sqlite_master table. Every time the database is opened, all CREATE TABLE statements are read from the sqlite_master table and used to regenerate SQLite’s internal representation of the table layout. If the original command was a CREATE TABLE AS then an equivalent CREATE TABLE statement is synthesized and stored in the sqlite_master table in place of the original command. The text of CREATE TEMPORARY TABLE statements are stored in the sqlite_temp_master table.

If the optional IF NOT EXISTS clause is present and another table with the same name already exists, then this command does nothing.

Example

The following code opens an existing database, “myDVDs”, and adds a table with three fields. The underscore keyword is used to break the string passed to SQLExecute into two lines in the Code Editor. It is treated by Real Studio’s compiler as one logical line.

See Also

ALTER TABLE, DROP TABLE.

CREATE TRIGGER Statement

Adds triggers to the database schema. Triggers are database actions that are performed automatically when a specified database event occurs.

Syntax

CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] trigger-name [BEFORE | AFTER] database-event ON [database-name.]table-name trigger-action
OR
CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] trigger-name INSTEAD OF database-event ON [database-name.]view-name trigger-action

Part	Description
database-event	DELETE | INSERT | UPDATE | UPDATE OF column-list
trigger-action	[FOR EACH ROW | FOR EACH STATEMENT] | [WHEN expression] BEGIN trigger-step;[trigger-step;] END
trigger-step	update-statement | insert-statement | delete-statement | select-statement

Notes

Use the CREATE TRIGGER statement to add triggers to the database schema. Triggers are database operations (the trigger-action) that are automatically performed when a specified database event (the database-event) occurs.
You can fire a trigger whenever a DELETE, INSERT or UPDATE of a particular table occurs or whenever an UPDATE of one or more specified columns of a table are updated.

REALSQLdatabase supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional. FOR EACH ROW implies that the SQL statements specified as trigger-steps may be executed (depending on the WHEN clause) for each database row being inserted, updated, or deleted by the statement causing the trigger to fire.

Both the WHEN clause and the trigger-steps may access elements of the row being inserted, deleted or updated using references of the form NEW.column-name and OLD.column-name, where column-name is the name of a column from the table that the trigger is associated with. OLD and NEW references may only be used in triggers on trigger-events for which they are relevant, as follows:

Keyword	Description
INSERT	NEW references are valid
UPDATE	NEW and OLD references are valid
DELETE	OLD references are valid

If a WHEN clause is used, the SQL statements specified as trigger-steps are only executed for rows for which the WHEN clause is True. If no WHEN clause is supplied, the SQL statements are executed for all rows.

The specified trigger-time determines when the trigger-steps will be executed relative to the insertion, modification or removal of the associated row.

An ON CONFLICT clause may be specified as part of an UPDATE or INSERT trigger-step. If an ON CONFLICT clause is specified as part of the statement causing the trigger to fire, then this conflict handling policy is used instead.

Triggers are automatically dropped when the table that they are associated with is dropped.

Triggers may be created on views as well as ordinary tables by specifying INSTEAD OF in the CREATE TRIGGER statement. If one or more ON INSERT, ON DELETE or ON UPDATE triggers are defined on a view, then it is not an error to execute an INSERT, DELETE or UPDATE statement on the view, respectively. Thereafter, executing an INSERT, DELETE, or UPDATE on the view causes the associated triggers to fire. The real tables underlying the view are not modified (except possibly explicitly, by a trigger program).

Example

Assuming that Customer records are stored in the Customers table, and that Order records are stored in the Orders table, the following trigger ensures that all associated orders are redirected when a customer changes his or her address:

With this trigger installed, executing the statement:

causes the following to be automatically executed:

Triggers may behave oddly when created on tables with INTEGER PRIMARY KEY fields. If a BEFORE trigger program modifies the INTEGER PRIMARY KEY field of a row that will be subsequently updated by the statement that causes the trigger to fire, then the update may not occur. The workaround is to declare the table with a PRIMARY KEY column instead of an INTEGER PRIMARY KEY column.

A special SQL function RAISE() may be used within a trigger-program, with the following syntax:

RAISE (ABORT, error-message) |
RAISE (FAIL, error-message) |
RAISE (ROLLBACK, error-message) |
RAISE (IGNORE)

When one of the first three forms is called during trigger-program execution, the specified ON CONFLICT processing is performed (either ABORT, FAIL or ROLLBACK) and the current query terminates. An error code of SQLITE_CONSTRAINT is returned to the user, along with the specified error message.

When RAISE(IGNORE) is called, the remainder of the current trigger program, the statement that caused the trigger program to execute and any subsequent trigger programs that would of been executed are abandoned. No database changes are rolled back. If the statement that caused the trigger program to execute is itself part of a trigger program, then that trigger program resumes execution at the beginning of the next step.

Triggers are removed using the DROP TRIGGER statement. Non-temporary triggers cannot be added on a table in an attached database.

See Also

DROP TRIGGER

CREATE VIEW Statement

Assigns a name to a prepackaged SELECT statement. Once the view is created, it can be used in the FROM clause of another SELECT statement in place of a table name.

Syntax

CREATE [TEMP | TEMPORARY] VIEW [IF NOT EXISTS] [database-name.]view-name AS select-statement

Part	Description
database-name	The name of the database containing view-name.
view-name	The name of the view that you are creating.
module-name	The name of an object that implements the virtual table.

Notes

If the TEMP or TEMPORARY keyword is used, the table that is created is only visible to the process that opened the database and is automatically deleted when the database is closed.

If database-name s specified, then the view is created in the named database. It is an error to specify both database-name and the TEMP keyword, unless database-name is “Temp.” If no database name is specified, and the TEMP keyword is not present, the table is created in the main database.

You cannot DELETE, INSERT or UPDATE a view. Views are read-only. However, in many cases you can use a TRIGGER on the view to accomplish the same thing. Views are removed with the DROP VIEW command. Non-temporary views cannot be created on tables in an attached database.

See Also

DROP VIEW, SELECT.

CREATE VIRTUAL TABLE Statement

Creates an interface to an external storage or computation engine that appears to be a table but does not actually store information in the database file.

Syntax

CREATE VIRTUAL TABLE [database-name.]table-name USING module-name[(arguments)]

Part	Description
database-name	The name of the database containing table-name.
table-name	The name of the table that you are virtualizing.
module-name	The name of an object that implements the vitual table.

Notes

A virtual table is an interface to an external storage or computation engine that appears to be a table but does not actually store information in the database file.

In general, you can do anything with a virtual table that can be done with an ordinary table, except that you cannot create triggers on a virtual table. Some virtual table implementations might impose additional restrictions. For example, many virtual tables are read-only.
The module-name argument is the name of an object that implements the virtual table. Module-name must be registered with the SQLite database connection using sqlite3_create_module prior to issuing the CREATE VIRTUAL TABLE statement.

The module takes zero or more comma-separated arguments. The arguments can be just about any text as long as it has balanced parentheses. The argument syntax is sufficiently general that the arguments can be made to appear as column definitions in a traditional CREATE TABLE statement. SQLite passes the module arguments directly to the module without any interpretation. It is the responsibility of the module implementation to parse and interpret its own arguments.
A virtual table is destroyed using the ordinary DROP TABLE statement. There is no drop virtual table statement.

See Also

CREATE TABLE, DROP TABLE.

DELETE Statement

Deletes the records specified in its WHERE clause.

Syntax

DELETE FROM [database-name.]table-name WHERE search-condition

Part	Description
database-name	Optional. The name of the database containing the table from which you are deleting records.
table-name	Name of the table from which you are removing records.
search-condition	Boolean expression that identifies the row or rows to be updated.

Notes

The DELETE command removes the records that satisfy the search-condition. If no WHERE clause is included, all the rows in the specified table are removed.

Example

The following example deletes the record in the “Movies” table that has the title “Star Wars”.

See Also

INSERT, UPDATE.

DETACH DATABASE Statement

Detaches an attached database that was previously attached using the ATTACH DATABASE statement.

Syntax

DETACH [DATABASE] database-name

Part	Description
database-name	The name of the database to be detached.

Notes

It is possible to attach the same database multiple times using different names. Detaching one connection will leave the others intact.

See Also

ATTACH DATABASE statement.

DROP INDEX Statement

Removes an index from a database.

Syntax

DROP INDEX [IF EXISTS] [database-name.]index-name

Part	Description
database-name	The name of database that contains the index to be dropped.
index-name	The name of the index to be dropped.

Notes

DROP INDEX removes an index that was added via the CREATE INDEX statement. The index is completely removed and can only be restored by recreating it with another CREATE INDEX statement.
DROP INDEX does not reduce the size of the database file. Empty space is retained for later INSERTs. To remove free space, use the VACUUM statement. The optional IF EXISTS clause suppresses the error that would normally result if the table does not exist.

Example

The following example drops the index called “NameIndex” in the table “Employees” using the “Name” field.

See Also

CREATE INDEX

DROP TABLE Statement

Removes a table from the database.

Syntax

DROP TABLE [IF EXISTS] [database-name.]table-name

Part	Description
database-name	Optional. Database containing the table to be dropped.
table-name	Name of table to be dropped from the database.

Notes

DROP TABLE removes a table that was added via the CREATE TABLE statement. It is completely removed from the database file and cannot be recovered. All indexes are also removed.
The DROP TABLE statement does not reduce the size of the database file. Free space is retained for later INSERTs. To remove the free space in the database, use the VACUUM statement.
The optional IF EXISTS clause suppresses the error that would normally result if the table does not exist.

Example

The following example drops the table named “Employees” from the database.

See Also

CREATE TABLE, ALTER TABLE.

DROP TRIGGER Statement

Drops a trigger that was created with the CREATE TRIGGER statement.

Syntax

DROP TRIGGER [IF EXISTS] [database-name.]trigger-name

Part	Description
database-name	Optional. Name of the database that contains the trigger.
trigger-name	Name of the trigger to be dropped.

Notes

DROP TRIGGER deletes the specified trigger from the database schema. Triggers are also automatically dropped if the associated table is dropped.
The optional IF EXISTS clause suppresses the error that would normally result if the table does not exist.

See Also

CREATE TRIGGER statement.

DROP VIEW Statement

Drops a view that was created with the CREATE VIEW statement.

Syntax

DROP VIEW [IF EXISTS] view-name

Part	Description
view-name	Name of the view to be dropped.

Notes

The specified view is removed from the database schema, but this does not affect the data in the tables. Non-temporary views in attached databases cannot be dropped. The optional IF EXISTS clause suppresses the error that would normally result if the table does not exist.

See Also

CREATE VIEW statement.

END TRANSACTION Statement

Ends a transaction that was initiated with BEGIN TRANSACTION.

Syntax

END [TRANSACTION [name]]

Part	Description
name	Name of the transaction to be ended. The optional name is ignored.

Notes

Transactions can be started manually using the BEGIN TRANSACTION command. Such transactions usually persist until the next COMMIT or ROLLBACK command. But a transaction will also ROLLBACK if the database is closed or if an error occurs and the ROLLBACK conflict resolution algorithm is specified. See the documentation on the ON CONFLICT clause for additional information about the ROLLBACK conflict resolution algorithm.

See Also

BEGIN TRANSACTION, COMMIT, ROLLBACK.

EXPLAIN Modifier

Non-standard command modifier used to request information about a SQL statement.

Syntax

EXPLAIN SQL-statement

Part	Description
SQL-statement	The SQL statement about which information is being requested.

Notes

If the EXPLAIN keyword appears before any SQL statement then REALSQLdatabase will report back the sequence of virtual machine instructions it would have used to execute the command if the EXPLAIN keyword had not been present.

INSERT Statement

Inserts a record into a table.

Syntax

INSERT [OR conflict-algorithm] INTO [database-name.]table-name [(column- list)] VALUES (value-list)
OR
INSERT [OR conflict-algorithm] INTO [database-name.]table-name [(column-list)] select-statement)

Part	Description
conflict-algorithm	Optional. Name of the database containing the table to be inserted.
database-name	Optional. Name of the database containing the table to be inserted.
table-name	Name of the table into which the data will be inserted.
column-list	Optional. Names of the columns in tablename that will contain the values being inserted. The number of columns must match the number of values being inserted (first format) or the number of columns returned by the SELECT statement. If the field list is omitted, values for all fields should be provided inorder or default or NULL values will be inserted. If you supply the field list, then the list of values match the specified fields, i.e., value1 is the value for field1, value2 is for field2, and so forth.
value-list	Values for each field in the row being added. Columns of the table that do not appear in the column list are filled with the default value for the column or Null if there is no default value.
select-statement	SELECT statement that returns the values that will be inserted. It must return the same number of columns as are specified by the column-list. A new INSERT will be done for every row returned by the SELECT statement. The SELECT statement can be simple or compound. If an ORDER BY clause is used, it is ignored.

Notes

By default, auto-commit is off in REALSQLdatabase, so you need to call COMMIT to commit your inserts.

The INSERT statement comes in two basic forms. The first form (with the VALUES keyword) creates a single new row in an existing table. If no column-list is specified then the number of values must be the same as the number of columns in the table. If a column-list is specified, then the number of values must match the number of specified columns. Columns of the table that do not appear in the column list are filled with the default value, or with NULL if no default value is specified.

The second form of the INSERT statement takes its data from a SELECT statement. The number of columns in the result of the SELECT must exactly match the number of columns in the table if no column list is specified, or it must match the number of columns named in the column list. A new entry is made in the table for every row of the SELECT result. The SELECT may be simple or compound.

The optional conflict-clause allows the specification of an alternative constraint conflict resolution algorithm to use during this one command. See the section titled ON CONFLICT for additional information.

Example

The following example inserts four records into the Employees table. The underscore character is used to break up long logical lines into separate lines as displayed in the Code Editor.

See Also

DELETE, COMMIT, ROLLBACK.

ON CONFLICT Clause

A non-standard SQL command that specifies an alternate algorithm that is used to resolve constraint conflicts. The OR clause is a synonym for ON CONFLICT.

Syntax

ON CONFLICT conflict-algorithm

Part	Description
conflict-algorithm	Algorithm for resolving constraint conflicts. The choices are: ROLLBACK ABORT FAIL IGNORE REPLACE The default is ABORT.

Notes

The ON CONFLICT clause is not a separate SQL command. It is a non-standard clause that can appear in many other SQL commands. It is given its own section in this document because it is not part of standard SQL and therefore might not be familiar.

The syntax for the ON CONFLICT clause is as shown in the Syntax section for the CREATE TABLE command. For the INSERT and UPDATE commands, the ON CONFLICT' keyword is replaced by OR, to make the syntax seem more natural. For example, instead of “INSERT ON CONFLICT IGNORE” you use “INSERT OR IGNORE”. The keywords change but the meaning of the clause is the same either way.
The ON CONFLICT clause specifies an algorithm used to resolve constraint conflicts. There are five choices: ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE. The default algorithm is ABORT. The five alternate algorithms have the following meanings.

Algorithm	Description
ABORT	The command backs out any prior changes it might have made and aborts with a return code of SQLITE_CONSTRAINT. But no ROLLBACK is executed, so changes from prior commands within the same transaction are preserved. This is the default behavior.
FAIL	The command aborts with a return code SQLITE_CONSTRAINT. Any changes to the database that the command made prior to encountering the constraint violation are preserved and are not backed out. For example, if an UPDATE statement encountered a constraint violation on the 100th row that it attempts to update, then the first 99 row changes are preserved but changes to rows 100 and beyond never occur.
IGNORE	The one row that contains the constraint violation is not inserted or changed. However, the command continues executing normally. Other rows before and after the row that contained the constraint violation continue to be inserted or updated normally. No error is returned.
REPLACE	When a UNIQUE constraint violation occurs, the pre-existing rows that are causing the constraint violation are removed prior to inserting or updating the current row. Thus the insert or update always occurs. The command continues executing normally. No error is returned. If a NOT NULL constraint violation occurs, the NULL value is replaced by the default value for that column. If the column has no default value, then the ABORT algorithm is used.
ROLLBACK	An immediate ROLLBACK occurs, thus ending the current transaction, and the command aborts with a return code of SQLITE_CONSTRAINT. If no transaction is active (other than the implied transaction that is created on every command) then this algorithm is the same as ABORT.

PRAGMA Statement

Used to modify the operation of the REALSQLdatabase library or query the library.

Syntax

PRAGMA name [=value]
OR
PRAGMA function(arg)

Part	Description
name	Name of the pragma being called.
value	Value to be assigned to the pragma. Pragmas that take an integer value also take pragma constants. The constants “On”, “True”, and “Yes” are equivalent to 1. The constants “Off”, “False”, and “No” are equivalent to 0. The strings are not case-sensitive and do not require quotes.
function	The name of a function.
arg	The value of the argument passed to the function.

Here are the pragmas that are used to modify library operations:

Name	Syntax	Description
auto-vacuum	auto_vacuum auto_vacuum=0 | 1	Queries or sets the auto-vacuum flag in the database. When the auto_vacuum flag is set, the database file shrinks when a transaction that deletes data is committed. It is only possible to modify the value of the auto-vacuum flag before any tables have been created in the database. No error message is returned if an attempt to set the auto-vacuum flag is made after a table has been created.
cache-size	cache_size cache_size=number of pages	Queries or changes the maximum number of database disk pages that REALSQLdatabase will hold in memory at once. Each page uses about 1.5K of memory. The default cache size is 2000. If you are doing UPDATEs or DELETEs that change many rows, you may find that increasing this value may improve performance.
count-changes	count_changes count_changes=0 | 1	Queries or changes the count-changes flag. Normally, when the count-changes flag is not set, INSERT, UPDATE and DELETE statements return no data. When count-changes is set, each of these commands returns a single row of data consisting of one integer value - the number of rows inserted, modified or deleted by the command. The returned change count does not include any insertions, modifications or deletions performed by triggers.
default-cache-size	default_cache_size default_cache_size = number of pages	Queries or changes the maximum number of database disk pages that REALSQLdatabase will hold in memory at once. Each page uses 1K on disk and about 1.5K in memory. This pragma works like the cache_size pragma with the additional feature that it changes the cache size persistently. With this pragma, you can set the cache size once and that setting is retained and reused every time you reopen the database.
empty_result_callbacks	empty_result_callbacks empty_result_callbacks=0 | 1	Queries or changes the empty-result- callbacks flag. Normally, when the empty-result-callbacks flag is cleared, the callback function supplied to the sqlite3_exec() call is not invoked for commands that return zero rows of data. When empty-result-callbacks is set in this situation, the callback function is invoked exactly once, with the third parameter set to 0 (NULL). This is to enable programs that use the sqlite3_exec() API to retrieve column-names even when a query returns no data.
encoding	encoding encoding=value	Gets or sets the encoding. The default encoding is UTF-8. Using the second form, you can set the value to UTF-8, UTF-16, UTF-16le (little endian), UTF-16be (big endian). The second form is useful only if the main database has not already been created. When you use it, it specifies the encoding that will be used if the database is created in the same session. Attached databases use the same encoding as the main database.
full-column-names	full_column_names full_column_names=0 | 1	Queries or changes the full-column-names flag. This flag affects the way REALSQLdatabase names columns returned by SELECT statements when the expression for the column is a table column name or the * wildcard. Normally, such result columns are named table-name/alias.column-name if the SELECT statement joins two or more tables together, or simply column-name if the SELECT statement queries a single table. When the full-column-names flag is set, such columns are always named table-name/alias.column-name regardless of whether or not a join is performed.
page-size	page_size page_size=bytes	Queries or sets the page-size of the database. This can be set only before the database is created. The page size must be a power of 2 greater than or equal to 512 and less than or equal to 8192.
short-column-names	short_column_names short_column_names=0 | 1	Queries or sets the short-column-names flag. This flag affects the way REALSQLdatabase names columns of data returned by SELECT statements when the expression for the column is a table- column name or the * wildcard. Normally, such result columns are named table-name/alias.column-name if the SELECT statement joins two or more tables together, or simply column-name if the SELECT statement queries a single table. When the short-column-names flag is set, such columns are always named column-name regardless of whether or not a join is performed.
synchronous	synchronous synchronous=value	Queries or sets the synchronous flag. Value can be assigned OFF (0), NORMAL (1), or FULL (2). When synchronous is FULL (2), the REALSQLdatabase database engine will pause at critical moments to make sure that data has actually been written to the disk surface before continuing. This ensures that if the operating system crashes or if there is a power failure, the database will be uncorrupted after rebooting. FULL synchronous is very safe, but it is also slow. When synchronous is NORMAL (1, the default), the REALSQLdatabase database engine will pause at the most critical moments, but less often than in FULL mode. There is a very small chance that a power failure at just the wrong time could corrupt the database in NORMAL mode. But in practice, you are more likely to suffer a catastrophic disk failure or some other unrecoverable hardware fault. With synchronous OFF (0), REALSQLdatabase continues without pausing as soon as it has handed data off to the operating system. If the application crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk. On the other hand, some operations are as much as 50 or more times faster with synchronous OFF.
temp-store	temp_store temp_store=value	Queries or sets the temp-store parameter. Value can be DEFAULT (0), FILE (1), or MEMORY (2). When temp_store is DEFAULT (0), the compile-time C preprocessor macro TEMP_STORE is used to determine where temporary tables and indices are stored. When temp_store is MEMORY (2) temporary tables and indices are kept in memory. When temp_store is FILE (1) temporary tables and indices are stored in a file. The temp_store_directory pragma can be used to specify the directory containing this file. FILE is specified. When the temp_store setting is changed, all existing temporary tables, indices, triggers, and are immediately deleted.
temp-store-directory	temp_store_directory temp_store_directory=directoryName	Queries or sets the temp-store-directory. This is the directory where files used for storing temporary tables and indexes are located. The new setting lasts only for the current connection and resets to its default value for each new connection opened. When this directory is changed, all existing temporary tables, indexes, triggers, and viewers are immediately deleted. If you set this pragma, it should be done just after the database is opened.

Pragmas to query the database schema:

Name	Syntax	Description
database-list	database_list	Invoke the callback function once with information about the database. Arguments include the index and the name the database was attached with. The first row will be for the main database. The second row will be for the database used to store temporary tables.
foreign-key-list	foreign_key_list(table-name)	For each foreign key that references a column in the argument table, invoke the callback function with information about that foreign key. The callback function will be invoked once for each column in each foreign key. The callback function will be invoked once for each column in each foreign key.
freelist_count	[database.]freelist_count	Returns the number of unused pages in the database file. Running a “PRAGMA incremental_vaccum(N)” command with a large value of N will shrink the database file by this number of pages.
index-info	index_info(index-name)	For each column that the named index references, invoke the callback function once with information about that column, including the column name, and the column number.
index-list	index_list(table-name)	For each index on the named table, invoke the callback function once with information about that index. Arguments include the index name and a flag to indicate whether or not the index must be unique.
table-info	table_info(table-name)	For each column in the named table, invoke the callback function once with information about that column, including the column name, data type, whether or not the column can be NULL, and the default value for the column.

Pragmas to query or modify version values:

Name	Syntax	Description
schema-version	schema_version schema_version=integer	Gets or sets the value of the schema-version. It is a 32-bit signed integer stored in the database header. This is usually manipulated only by REALSQLdatabase. It is incremented whenever the database schema is modified. Interfering with this process is not recommended.
user-version	user_version user_version=integer	Gets or sets the use-version. It is also a 32-bit signed integer stored in the database header. This is not used internally, so it may be used for any purpose.

Pragmas to debug the library:

Name	Syntax	Description
integrity-check	integrity_check integrity_check(integer)	Does an integrity check of the entire database. It looks for out-of-order records, missing pages, malformed records, and corrupt indexes. If any problems are found, then a string is returned which is a description of all problems. If everything is in order a single row with the value “ok” isreturned.
parser-trace	parser_trace= 0 | 1	Turns tracing of the SQL parser on or off. Thisis used for debugging. The constants ON andOFF can be used instead of 0 and 1.
vdbe-trace	vdbe_trace= 0 | 1	Turns tracing of the virtual database on or off. This is used for debugging. The constants ON and OFF can be used instead of 0 and 1.
vdbe-listing	vdbe_listing= 0 | 1	Turns listing of the virtual machine programs on or off. When listing is on, the entire content of a program is printed just prior to beginning execution. This is likeautomatically executing an EXPLAIN prior to each statement. The statement executes normally after the listing is printed. This is used for debugging. The constants ON and OFF can be used instead of 0 and 1.

REINDEX Statement

Deletes and recreates indexes from scratch.

Syntax

REINDEX collation-name OR REINDEX [database-name.]table/index-name

Part	Description
collation-name	Name of a collation sequence.
database-name	The database in which the reindexing will be done.
table/index-name	Name of the table or index to be recreated.

Notes

The REINDEX command is used to delete and recreate indices from scratch. The REINDEX command is used primarily when the definition of a collation sequence had changed and the current index is no longer in sync.
In the first form, all indexes in all attached databases that use the named collation sequence are recreated. In the second form, if [database-name.]table/index-name identifies a table, then all indices associated with the table are rebuilt. If an index is identified, then only this specific index is deleted and recreated.
If no database-name is specified and there exists both a table or index and a collation sequence of the specified name, then indices associated with the collation sequence only are reconstructed. This ambiguity may be dispelled by always specifying a database-name when reindexing a specific table or index.

See Also

CREATE INDEX

REPLACE Statement

An alias for the INSERT OR REPLACE version of the INSERT command. See the INSERT statement for additional information.

Syntax

REPLACE INTO [database-name.]table-name [(column-list)] VALUES value-list
OR
REPLACE INTO [database-name.]table-name [(column-list)] select-statement

Class	Description
database-name	Optional. Name of the database containing the table to be inserted.
table-name	Name of the table into which the data will be inserted.
column-list	Optional. Names of the columns in tablename that will contain the values being inserted. The number of columns must match the number of values being inserted (first format) or the number of columns returned by the SELECT statement. If the field list is omitted, values for all fields should be provided in order or default or NULL values will be inserted. If you supply the field list, then the list of values match the specified fields, i.e., value1 is the value for field1, value2 is for field2, and so forth.
value-list	Values for each field in the row being added. Columns of the table that do not appear in the column list are filled with the default value for the column or Null if there is no default value.
select-statement	SELECT statement that returns the values that will be inserted. It must return the same number of columns as are specified by the column-list. A new INSERT will be done for every row returned by the SELECT statement. The SELECT statement can be simple or compound. If an ORDER BY clause is used, it is ignored.

See Also

INSERT Statement.

ROLLBACK Statement

Restores the database to its original state prior to the start of a transaction.

Syntax

ROLLBACK [TRANSACTION [name]]

Part	Description
name	Optional name of the transaction. If passed, it is ignored.

Notes

The REALSQLdatabase supports transactions. A transaction is either a set of changes to the database schema (i.e., tables, fields, and field types) or a set of changes to the contents of the database—record additions, deletions, or modifications performed by the INSERT, DELETE, or UPDATE statements. A transaction begins automatically with the first such change and is saved to the database by calling COMMIT. You can instead abort the transaction by calling ROLLBACK. This restores the database to the state it was when the transaction was started.

Transactions can be started manually using the BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command. But a transaction will also ROLLBACK if the database is closed or if an error occurs and the ROLLBACK conflict resolution algorithm is specified. See the documentation on the ON CONFLICT clause for additional information about the ROLLBACK conflict resolution algorithm.

In SQLite version 3.0.8 and later, transactions can be deferred, immediate, or exclusive. Deferred means that no locks are acquired on the database until the database is first accessed. Thus with a deferred transaction, the BEGIN statement itself does nothing. Locks are not acquired until the first read or write operation. The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock. Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed. If the transaction is immediate, then RESERVED locks are acquired on all databases as soon as the BEGIN command is executed, without waiting for the database to be used. After a BEGIN IMMEDIATE, you are guaranteed that no other thread or process will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database, however. An exclusive transaction causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN EXCLUSIVE, you are guaranteed that no other thread or process will be able to read or write the database until the transaction is complete.

A description of the meaning of SHARED, RESERVED, and EXCLUSIVE locks is available separately.
The default behavior for SQLite version 3.0.8 is a deferred transaction. For SQLite version 3.0.0 through 3.0.7, deferred is the only kind of transaction available. For SQLite version 2.8 and earlier, all transactions are exclusive. The COMMIT command does not actually perform a commit until all pending SQL commands finish. Thus if two or more SELECT statements are in the middle of processing and a COMMIT is executed, the commit will not actually occur until all SELECT statements finish.
You can also rollback changes to the database by calling the Database class’s Rollback method.

See Also

COMMIT.

SELECT Statement

Use the SQL Select statement to query the database. You pass a valid SQL Select statement as a string to the SQLSelect method of the Database class. It selects and optionally groups and sorts records in one or more tables.

Syntax

result=SELECT [ALL | DISTINCT] result FROM table-list
WHERE expr
GROUP BY expr-list
HAVING expr
[compound-op select]
ORDER BY sort-expr-list
[LIMIT integer [(OFFSET |, integer)]

Part	Description
ALL | DISTINCT	Optional keyword. Distinct retrieves only unique values, i.e., it retrieves only unique rows. For example ‘DISTINCT Cities’ retrieves only one record per city even if there are several records per city. The ALL keyword retrieves all records, even if the values are not unique. ‘ALL’ is assumed unless DISTINCT is used.
result	One or more result-columns that specify the columns to be returned. The resulting group of records is returned as a REALbasic RecordSet object. The SQL SELECT statement must be passed to the Database class’s SQLSelect method rather than SQLExecute.
result-column	Specifies a column of data to be returned of the form, * | tablename.* | expr [{AS] string] If result-column is equal to “*” then all columns of all tables are returned. If the expression is the name of a table followed by “.*” then all of the columns in that table are returned. Otherwise it is the list of columns to be returned.
table	table-name [AS alias] | (select) [AS alias] The syntax for a table allows you to specify an alias for the tablename that is used later in the select statement, i.e., “ACTORS AS a”
table-list	table [join-op table join-args] List of tables separated by commas. If a tablename has spaces in it, it must be surrounded by brackets, e.g., [Product Groups].
join-op	The permissible expressions for the join operator are: , | [NATURAL] [LEFT | RIGHT] | FULL] [OUTER | INNER | CROSS] JOIN
join-args	[ON expr] [USING (id-list)]
sort-expr-list	expr [sort-order] [,expr[sort-order]]
sort-order	[COLLATE collation-name] [ASC | DESC]
compound_op	UNION | UNION ALL | INTERSECT | EXCEPT

Notes

The SELECT statement is used to query the database. The result of a SELECT is zero or more rows of data where each row has a fixed number of columns. The number of columns in the result is specified by the expression list in between the SELECT and FROM keywords. Any arbitrary expression can be used as a result. If a result expression is * then all columns of all tables are substituted for that one expression. If the expression is the name of a table followed by * then the result is all columns in that one table.
The DISTINCT keyword causes a subset of result rows to be returned, in which each result row is different. NULL values are not treated as distinct from each other. The default behavior is that all result rows be returned, which can be made explicit with the keyword ALL.

The query is executed against one or more tables specified after the FROM keyword. If multiple tables names are separated by commas, then the query is against the cross join of the various tables. The full SQL-92 join syntax can also be used to specify joins. A subquery in parentheses may be substituted for any table name in the FROM clause. The entire FROM clause may be omitted, in which case the result is a single row consisting of the values of the expression list.
The WHERE clause can be used to limit the number of rows over which the query operates.

The GROUP BY clauses causes one or more rows of the result to be combined into a single row of output. This is especially useful when the result contains aggregate functions. The expressions in the GROUP BY clause do not have to be expressions that appear in the result. The HAVING clause is similar to WHERE except that HAVING applies after grouping has occurred. The HAVING expression may refer to values, even aggregate functions, that are not in the result.

The ORDER BY clause causes the output rows to be sorted. The argument to ORDER BY is a list of expressions that are used as the key for the sort. The expressions do not have to be part of the result for a simple SELECT, but in a compound SELECT each sort expression must exactly match one of the result columns. Each sort expression may be optionally followed by a COLLATE keyword and the name of a collating function used for ordering text and/or keywords ASC or DESC to specify the sort order.

The LIMIT clause places an upper bound on the number of rows returned in the result. A negative LIMIT indicates no upper bound. The optional OFFSET following LIMIT specifies how many rows to skip at the beginning of the result set. In a compound query, the LIMIT clause may only appear on the final SELECT statement. The limit is applied to the entire query not to the individual SELECT statement to which it is attached. Note that if the OFFSET keyword is used in the LIMIT clause, then the limit is the first number and the offset is the second number. If a comma is used instead of the OFFSET keyword, then the offset is the first number and the limit is the second number. This seeming contradiction is intentional - it maximizes compatibility with legacy SQL database systems.

A compound SELECT is formed from two or more simple SELECTs connected by one of the operators UNION, UNION ALL, INTERSECT, or EXCEPT. In a compound SELECT, all the constituent SELECTs must specify the same number of result columns. There may be only a single ORDER BY clause at the end of the compound SELECT. The UNION and UNION ALL operators combine the results of the SELECTs to the right and left into a single big table. The difference is that in UNION all result rows are distinct where in UNION ALL there may be duplicates. The INTERSECT operator takes the intersection of the results of the left and right SELECTs. EXCEPT takes the result of left SELECT after removing the results of the right SELECT. When three or more SELECTs are connected into a compound, they group from left to right.

Wildcard Searches

The Real database supports the two SQL wildcard characters, percent, ‘%’, and the underscore character, ‘_’. The percent wildcard stands for any number of characters and the underscore stands for any single character. Placing the % at the beginning or the end of the search string does ‘ends with’ and ‘begins with’ searches, respectively. Using the % on both sides of the search string specifies a ‘contains’ search. For example,

SELECT Statement	Search Type
SELECT * from authors where au_lname LIKE "Jon%"	Begins with
SELECT * from authors where au_lname LIKE "%son"	Ends with
SELECT * from authors where au_lname LIKE "%man%"	Contains

The search criterion is case-sensitive.

The underscore character is the wildcard character for any single character. For example,
SELECT * from authors where au_lname LIKE "B_r"
Returns “Bar”, “Bor”, etc.

SELECT Examples

You do relational operations (e.g., “joins”) by specifying the tables to be joined in the SELECT statement’s table-list and indicating in the WHERE clause how the tables are to be joined, i.e., rows in the "many" table whose foreign key matches the primary key in the "one" table. Please refer to a SQL reference book for detailed information on relational operations.

A join retrieves data from two or more tables. A join usually uses a WHERE clause that specifies the rows to be included in the recordset. The WHERE clause usually refers to primary and/or foreign key fields in the tables being joined. The most common type of join retrieves one or more rows in one table that are logically linked to each row in a different table.

Consider the following simple tables, Movies and Actors.

Movies

RowID	Title	Director	Year
1	Star Wars	George Lucas	1977
2	Raiders of the Lost Ark	George Lucas	1981
3	American Graffiti	George Lucas	1973
4	Apollo 13	Ron Howard	1995
5	Cast Away	Robert Zemeckis	2000

Actors

RowID	Name	Movie
1	Harrison Ford	1
2	Mark Hamill	1
3	Carrie Fisher	1
4	Harrison Ford	2
5	Ron Howard	3
6	Richard Dreyfuss	3
7	Tom Hanks	4
8	Tom Hanks	5

The third column in the Actors table identifies a movie in which the action appeared.

With this setup, the following SELECT statements can be used to query the database.

Simple Selection

These statements select all records and all columns in a table:

Each statement returns the Movies and Actors tables shown above.
The following statement uses the Distinct lists all the actors only once:
Select distinct Name from Actors
It returns the following RecordSet:

Name
Harrison Ford
Mark Hamill
Carrie Fisher
Ron Howard
Richard Dreyfuss
Tom Hanks

Relational Operations

Here are some examples of queries that use the WHERE and GROUP clauses to extract data from both tables.

Find all actors in ‘Star Wars’:

Each table is denoted by a letter and the WHERE clause specifies the records to be retrieved. It specifies that the Title column must equal ‘Star Wars’ and the rows in the Actors table must have the value of the Movie field equal to the RowID column in Movies that has the ‘Star Wars’ record. This is RowID=1. That means the matching rows in Actors are the first three rows.

Select a.name from Actors A, Movies M where a.movie=m._rowid andm.title='Star Wars'

The Select statement specifies that only the Actors.Name column be retrieved, so the result is:

Name
Harrison Ford
Mark Hamill
Carrie Fisher

Find all movies directed by Lucas and report the actor's name, the movie's name, and the year.
This example differs from the previous only in that the columns to be returned are from both the Actors and Movies tables:
Select A.Name,M.Title,M.Year from Actors A, Movies M where A.movie=M._rowid and M.Director='George Lucas

The WHERE clause works the same way as the previous example. It selects actors whose Movie column matches the Movie table’s RowID for Lucas-directed flicks. In this example, the column list is from both tables. The result is this:

Name	Movie	Year
Ron Howard	American Graffiti	1973
Richard Dreyfuss	American Graffiti	1973
Harrison Ford	Raiders of the Lost Ark	1981
Harrison Ford	Star Wars	1977
Mark Hamill	Star Wars	1977
Carrie Fisher	Star Wars	1977

Find all actors who have done a movie with Harrison Ford:
Select A.name from Actors A, Actors B where A.movie = B.movie and B.name='Harrison Ford' and A.name<>'Harrison Ford'
The result is:

Name
Mark Hamill
Carrie Fisher

Find anyone who is both an actor and a director:
Select A.Name from Actors A, Movies M where A.Name = M.Director
The result is:

Name
Ron Howard

Wildcard Search

Find any movies with the word “War” in the title (ignoring case):
Select title, year from Movies where Upper(title) like '%WAR%'

The result is:

Title	Year
Star Wars	1977

Aggregate Functions

Find out how many actors we have for each movie (by Title):

Select A.Title, Count(B.Name) from Movies A, Actors B where B.Movie = A._rowID group by B.Movie

The result is:

Title	Count(Name)
Star Wars	3
Raiders of the Lost Ark	1
American Graffiti	2
Apollo 13	1
Cast Away	1

Find when each director made his first and last movie (in this database).

Select Director, Min(Year), Max(Year) from Movies Group by Director

The result is:

Director	Min(Year)	Max(Year)
George Lucas	1973	1981
Robert Zemeckis	2000	2000
Ron Howard	1995	1995

Find out how many moview each actor has done, but display only those who have done more than one.

Select Name, Count(*) from Actors group by Name having Count(*) > 1
The result is:

Name	Count
Harrison Ford	2
Tom Hanks	2

UPDATE Statement

Modifies existing data in table.

Syntax

UPDATE [OR conflict-algorithm] [database-name.]table-name SET assignment [,assignment] [WHERE expr]

Argument	Description
conflict-algorithm	Optional conflict algorithm used to resolve constraint issues. See ON CONFLICT for the list of algorithms.
database-name	Optional name of the database containing the table to be updated.
table-name	Name of the table to be updated.
assignment	Specifies a column to the left of an equals sign and an arbitrary expression to the right.
expr	Expression that restricts the rows in table-name to be updated.

Notes

Use the UPDATE statement to change the value of specified columns in selected rows of a table. Each assignment in an UPDATE statement specifies a column name to the left of the equals sign and an arbitrary expression to the right. The expressions may use the values of other columns. All expressions are evaluated before any assignments are made. You can include the optional WHERE clause to restrict which rows are updated.

The optional OR (a.ka. ON CONFLICT) clause allows the specification of an alternative constraint conflict resolution algorithm to use during this command. Call the COMMIT command or the Database class’s Commit method to save your updates or call Rollback to reset the database to its state prior to the update.
Example
The following example changes the value of the Year field to 1976 for the movie with the title “Rabid.”

VACUUM Statement

Cleans up an index or table.

Syntax

VACUUM (index or table-name)

Part	Description
index	An index previously created via CREATE INDEX. Currently ignored.
table-name	The name of an existing table. Currently ignored.

Notes

VACUUM cleans up a database by removing empty space.

When an object (table, index, or trigger) is dropped from the database, it leaves behind empty space. This makes the database file larger than it needs to be, but the presence of empty space can speed up inserts. In time, inserts and deletes can leave the database file structure fragmented, which slows down disk access to the database contents. The VACUUM command cleans the main database by copying its contents to a temporary database file and reloading the original database file from the copy. This eliminates free pages, aligns table data to be contiguous, and otherwise cleans up the database file structure. It is not possible to perform this process on an attached database file.
This command will fail if there is an active transaction. This command has no effect on an in-memory database.

SQL Functions

ABS Function

Returns the absolute value of the passed value.

Syntax

result=ABS(x)

Part	Type	Description
result	Numeric	Absolute value of x.
x	Numeric	The number whose absolute value is to be returned.

Example

Coalesce Function

Returns a copy of the first non-Null argument. If all arguments are Null, then Null is returned. There must be at least two arguments. This is identical to IsNull except it can evaluate more than two arguments.

Syntax

result=Coalesce(X,Y,...)

Part	Type	Description
result	Any	A copy of the first non-Null argument. If all arguments are Null, then Null is returned.
x	Any	The first parameter to be evaluated.
y	Any	The second parameter to be evaluated.

See Also

IfNull function.

Glob Function

The Glob operator is similar to LIKE but uses the Unix file globbing syntax for its wildcards. Also, GLOB is case sensitive, unlike LIKE. Both GLOB and LIKE may be preceded by the NOT keyword to invert the sense of the test. The infix GLOB operator is implemented by calling the user function glob(X,Y) and can be modified by overriding that function.

IfNull Function

Returns a copy of the first non-Null argument. If both arguments are Null then Null is returned. This function behaves the same as Coalesce.

Syntax

result=IfNull(X,Y)

Part	Type	Description
result	Any	A copy of the first non-Null argument. If all arguments are Null, then Null is returned.
x	Any	The first parameter to be evaluated.
y	Any	The second parameter to be evaluated.

See Also

Coalesce function.

Last_Insert_rowid Function

Returns the RowID of the last row insert from this connection to the database.

Syntax

result=Last_Insert_rowID()

Part	Type	Description
result	Integer	The RowID of the last insert to the database.

Example

Length Function

Returns the length of the string passed.

Syntax

result=Length(str)

Part	Type	Description
result	Integer	The length of the string passed.
str	String	Character string to be evaluated.

Like Function

Implements the “X LIKE Y” syntax of SQL.

Syntax

result = LIKE(X, Y [,Z])

Part	Type	Description
result	Boolean	The result of the X LIKE Y comparison.
X	String	The string to be evaluated.
Y	String	The pattern to which X is compared.
Z	String	Optional Escape clause. See Notes.

Notes

The LIKE operator does a pattern matching comparison. The Y parameter contains the pattern, the X parameter contains the string to match against the pattern. A percent symbol % in the pattern matches any sequence of zero or more characters in the string.

An underscore _ in the pattern matches any single character in the string. Any other character matches itself or it's lower/upper case equivalent (i.e. case-insensitive matching). REALSQLdatabase only understands upper/lower case for 7-bit Latin characters. This means that the LIKE operator is case sensitive for 8-bit iso8859 characters or UTF-8 characters. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.).

If the optional ESCAPE clause is present, then the expression following the ESCAPE keyword must evaluate to a string consisting of a single character. This character may be used in the LIKE pattern to include literal percent or underscore characters. The escape character followed by a percent symbol, underscore or itself matches a literal percent symbol, underscore or escape character in the string, respectively.

LOWER Function

Returns the string passed in all lowercase text.

Syntax

result=LOWER(String)

Part	Type	Description
result	String	The passed string in all lowercase.
String	String	Character string to be evaluated.

Example

See Also

UPPER.

Max Function

Returns the maximum of the passed values. If passed one value it becomes an aggregate function.

Syntax

result=Max(X,Y[,...])

Part	Type	Description
result	Any	The maximum value of all the passed arguments.
X	Any	The first item to be compared. Items may be strings or numbers.
Y	Any	The second item to be compared. Items may be strings or numbers.

Example

See Also

Max (aggregate function), Min function.

Min Function

Returns the minimum of the passed values. If passed one value, it becomes an aggregate function.

Syntax

result=Min(X,Y[,...])

Part	Type	Description
result	Any	The maximum value of all the passed arguments.
X	Any	The first item to be compared. Items may be strings or numbers.
Y	Any	The second item to be compared. Items may be strings or numbers.

Example

See Also

Min (aggregate function), Max function.

NullIf Function

Returns the first parameter if they are different, otherwise it returns Null.

Syntax

result= NullIf(X,Y)

Part	Type	Description
result	Any	X or Y if they are different or Null.
X	Any	The first item to be compared. Items may be strings or numbers.
Y	Any	The second item to be compared. Items may be strings or numbers.

Example

POSITION Function

Searches for the specified target string within the source string and returns the character position where the target string starts.

Syntax

result=POSITION (targetString IN sourceString)

Example

See Also

SUBSTRING, TRIM.

Quote Function

Quotes the value passed to it for inclusion in another SQL statement.

Syntax

result= Quote (X)

Part	Type	Description
result	String	Value of X suitable for inclusion into another SQL statement.
X	Any	The value being quoted.

Random Function

Returns a random integer.

Syntax

result = Random()

Part	Type	Description
result	Integer	A random number between -9223372036854775808 and +92233720368547758072147483647.

Example

Round Function

Rounds off the passed number.

Syntax

result = Round(X[,Y])

Part	Type	Description
result	Number	X rounded to the number of decimal places specified by Y.
X	Number	The number to be rounded.
Y	Integer	The number of digits to the right of the decimal point that X is to be rounded to. If Y is omitted, it is set to zero.

Examples

SQLite_Version Function

Returns the version string of the SQLite library that is running.

Syntax

result=SQLite_Version()

Part	Type	Description
result	String	The version of SQLite that is running.

Example

SUBSTR Function

Returns the specified substring of characters from the string passed.

Syntax

result=SUBSTR(targetString,Start,Length)

Part	Type	Description
result	String	The substring of characters extracted from targetString by position.
targetString	String	Character string from which to extract a substring.
Start	Integer	Starting character position in targetString from which to extract the substring. The first position is numbered 1. If Start is negative, the starting position is determined by starting at the right and counting toward the left.
Length	Integer	The number of characters following Start to extract.

Notes

Returns a substring of input string targetString that begins with the start character and which is length characters long. The left-most character of targetString is number 1. If start is negative then the first character of the substring is found by counting from the right rather than the left. If targetString is a string then characters indices refer to actual UTF-8 characters. If targetString is a BLOB then start and length refer to bytes.

Example

See Also

POSITION, TRIM.

TRIM Function

Trims leading and/or trailing characters from the string passed.

Syntax

result=TRIM ([LEADING|TRAILING|BOTH! [trimstring] FROM targetString)

Part	Type	Description
result	String	The string of characters after trimming targetString' by removing leading and/or trailing instances of trimstring.
trimString	String	Optional. Character to remove from start or end of targetString. If omitted, the space is assumed.
targetString	String	String from which to trim characters.

Notes

TRIM requires that you specify that you want to trim Leading or Trailing instances of the trimString (or both) and allows you to specify the string to be trimmed. The From keyword is required if either the trimstring or the Leading/Trailing/Both options are specified.
Examples

See Also

POSITION, SUBSTRING.

TYPEOF Function

Returns the data type of the passed expression.

Syntax

result = TYPEOF(X)

Part	Type	Description
result	String	The data type of the expression passed. The valid values are: Null, Integer, Real, Text, and Blob.
X	Any	Value whose data type is to be determined.

UPPER Function

Returns the string passed in all uppercase text.

Syntax

result=UPPER (String)

Part	Type	Description
result	String	The passed string in all uppercase.
String	String	Character string to be evaluated.

Example

See Also

LOWER.

CURRENT_DATE Function

Returns the current date in the SQL Date format, YYYY-MM-DD.

Syntax

result=CURRENT_DATE

Part	Type	Description
result	SQLDate	The current date in the YYYY-MM-DD format.

Example

CURRENT_TIME Function

Returns the current time in the SQL Time format, HH:MM:SS.

Syntax

result=CURRENT_TIME

Part	Type	Description
result	SQLTime	The current time in the HH:MM:SS SQL Time format.

Example

See Also

CURRENT_TIMESTAMP.

CURRENT_TIMESTAMP Function

Returns the current date-time in the SQLTimestamp format, YYYY-MM-DD HH:MM:SS.

Syntax

result=CURRENT_TIMESTAMP

Part	Type	Description
result	SQLTimestamp	The current date/ time in the YYYY-MM-DD HH:MM:SS SQL Timestamp format.

Example

See Also

CURRENT_TIME.

Aggregate Functions

AVG Function

Computes the average value of an expression across rows.

Syntax

result=AVG [DISTINCT | ALL] (expression)

Note

If you use the DISTINCT keyword, only unique values of expression will be included in the calculation. If you use ALL, all values of expression will be included, including duplicate ones. The default is ALL.

Example

See Also

COUNT, SUM.

COUNT Function

Computes the number of rows in an expression.

Syntax

result=COUNT [DISTINCT] expression FROM searchExpression

Part	Type	Description
result	Integer	The total of expression across the rows specified by searchExpression.
expression	Any	Column or function of one or more columns.
SearchExpression		SQL WHERE clause that selects the rows that are evaluated.

Notes

If you use the DISTINCT keyword, only unique values of expression will be included in the calculation. If you use * as the expression, all rows will be counted.
When expression is specified, COUNT returns the number of rows for the columns in expression.

Example

This example returns the number of rows in the Movies table with non-missing values in the Title and Director fields. The second example counts the rows with non-missing values for Title in which the Director field equals ‘Lucas’.

See Also

AVG, SUM.

MAX Function

Returns the maximum value of an expression.

Syntax

result=MAX expression FROM searchExpression

Part	Type	Description
result	Double	The maximum value of expression across the rows specified by searchExpression.
expression	Numeric or String	Column or function of one or more columns that evaluates to a numeric or string expression
SearchExpression		SQL WHERE clause that selects the rows that are evaluated.

Example

This example finds the largest population in the Cities table.

See Also

MIN.

MIN Function

Returns the minimum value of the specified column or columns.

Syntax

result=MIN expression FROM searchExpression

Part	Type	Description
result	Double	The minimum value of expression across the rows specified by searchExpression.
expression	Numeric or String	Column or function of one or more columns thatevaluates to a numeric or string expression.
SearchExpression		SQL WHERE clause that selects the rows that are evaluated.

Example

This example finds the minimum Price in the Products table.

See Also

MAX.

SUM Function

Returns the total of the specified numeric fields.

Syntax

result=SUM [DISTINCT|ALL] expression FROM searchExpression

Part	Type	Description
result	Double	The maximum value of expression across the rows specified by searchExpression.
expression	Numeric or String	Column or function of one or more columns that evaluates to a numeric or string expression.
SearchExpression		SQL WHERE clause that selects the rows that are evaluated.

If you use the DISTINCT keyword, only unique values of expression will be included in the calculation. If you use ALL, all values of expression will be included, including duplicate ones. The default is ALL.

Notes

Expression must evaluate to a numeric data type. It can include one or more columns. The DISTINCT keyword can be used to limit the evaluation to distinct (unique) occurrences of each value.

Example

This example finds the total population from the States table for States in the NE region only.

See Also

AVG, COUNT.