There are some functions / subroutines in the executable’s System macros Panel which are intended to provide functionality that I have regularly needed, and that does not fit to a single SQLite command. There are also some built-in commands that send queries to the SQLite database.
This page describes some of the built-in commands and embedded functions / subroutines that are available, and that focus on getting information about the various components of the database.
SQLite Version
The SQLite database engine is often updated by the good people in the SQLite project. Each version is clearly identified. The command sqlVersion will return a text string containing the SQLite version (currently 3.23.03).
The following are built-in commands of the executable. They still have a (internal) functional specification.
Number of child objects
This command identifies the number of child objects within a specified database object e.g. how many columns does a given table have.
format: dbObjectChildren ( OPTIONAL <database schema specifier> , OPTIONAL <table specifier> , OPTIONAL <column specifier> )
——————-
- how many databases are currently open
message = dbObjectChildren ()
——————-
- how many tables a specific databaseschema has
message = dbObjectChildren ( "main")
——————-
- how many columns a table has
message = dbObjectChildren ( "main" , "family")
——————-
- how many rows a table has
message = dbObjectChildren ( "main" , "family", " any not empty value" )
Weird and wonderful complication.
The dbObjectChildren command accepts a 1 based index instead of the database name, and instead of the table name.
Index of a database object
This command identifies the index number of a specified database object.
dbObjectIndex( OPTIONAL <database schema specifier> , OPTIONAL <table specifier> , OPTIONAL <column specifier> )
This command identifies the index number of a specified database object.
——————-
- is there an open database
message = dbObjectIndex()
——————-
- index number of a database. Assume you have attached a database with the schema name fred
message = dbObjectIndex("fred")
——————-
- index number of a table (good old “family”) within the database “main”.
message = dbObjectIndex("main", "family")
——————-
- index number of a the column “age” in the table (good old “family”) within the database “main”.
message = dbObjectIndex("main", "family", "age")
Text name of a database component
This command identifies the text name of a specified database object. So it is in effect the reverse of dbObjectIndex.
format: dbObjectName ( OPTIONAL <database schema specifier> , OPTIONAL <table specifier> , OPTIONAL <column specifier> )
Weird and wonderful complication: dbObjectName will accept as parameters either the index number or the original text name!