pragma



Executes a PRAGMA statement. A PRAGMA statement is a SQLite specific SQL extension, probably unknown to any other database engine. The pragma method implements the functionality of the SQLite PRAGMA statement. The SQLite documentation should be read to understand how PRAGMAs work

The pragma method is intended to be a convenience method for the Rexx programmer. For any specific PRAGMA, the pragma method could be replaced by using a prepared The ooSQLiteStmt Class , or by using the exec method. When a PRAGMA is executed using a prepared statement or the exec method, a result set is returned. However, executing the vast majority of PRAGMAs returns either a single value or no value. As a convenience, the pragma method returns a single value for those cases. A result set is only returned when necessary.

In addition, some PRAGMAs require an argument, some only work with no argument, and SQLite ignores unknown PRAGMA keywords. In all these cases of mistaken usage, no error messages or error codes are generated by the SQLite engine. This means if there is mistaken usage of a PRAGMA statement SQLite does not inform the user of the fact. However, the ooSQLite framework attempts to detect any errors when using the pragma method and raises an exception when it does. Because of this, the Rexx programmer is informed of incorrect usage the first time he tests his program.

Pragmas can have an optional database name before the pragma name. The form is: the database name, a dot characeter, and the pragma name. The database name is the name of an attached database or it can be main or temp for the main and the TEMP databases. If the optional database name is omitted, main is assumed. In some pragmas, the database name is meaningless and is simply ignored. An example for the secure_delete pragma could be: main.secure_delete

Arguments: The arguments are:
name [required] The name of the pragma to be executed. Case is not significant for the name. Each recognized pragma is listed in the remarks section, along with details on what to expect for that pragma.
value [optional] Most PRAGMAs accept, or use, an argument. The secondvalueargument to the pragmamethod is used as the argument to a PRAGMA. When a pragma is used to set a value, thevalueargument is the value to set. For these pragmas, the existence of the argument determines if a query or a set is done. When the argument is omitted, a query is done. When the argument is used, a set is done. In some cases the PRAGMA argument is a qualifier and thevalueargument is passed on to the PRAGMA as that qualifier. In some cases, a PRAGMA does not accept an argument, and in others an arugment is required. See the remarks section for the details on each individual PRAGMA.
Return value: The return is dependent on the pragma in use. Some pragmas return result sets, some return a single value, and others return a SQLite result Result Code Constants . In all cases a value is returned. See the remarks section for the type of return to expect for each individual PRAGMA. Although an error is unlikely when using thepragmamethod, errors are always possible. In many cases, an error return is not distiguishable from a valid return. For instance, thepage_countpragma could return 21 pages. 21 is also the value of the MISUSE result code. In all cases, the programmer should check the value of the last error lastErrCode (Attribute) attribute to detemine if an error occurred. The value of the attribute willalwaysbe .ooSQLiteConstants~OKif no error happended. The value will never be OK if there was an error.
Remarks: PRAGMA statement can be used to set / configure database values, to trigger an action, and to query most of the configurable database values. SQLitedeprecatedanddebug onlyPRAGMAs are not recognized in ooSQLite. The following lists all PRAGMAs that are recognized, with details for each PRAGMA following. Return values are categorized as single values (the most common,) result codes, or result sets. When a result set is returned, it could be an empty result set. That is it could contain no rows. Both single value returns and empty result sets could also be an error return. See the Return value section above for a discussion of error retuns. For each pragma the return category is listed for the prama with novalueargument and with avalueargument.
  • application_id
  • auto_vacuum
  • automatic_index
  • busy_timeout
  • cache_size
  • cache_spill
  • case_sensitive_like
  • checkpoint_fullfsync
  • collation_list
  • compile_options
  • database_list
  • defer_foreign_keys
  • encoding
  • foreign_key_check
  • foreign_key_list
  • foreign_keys
  • freelist_count
  • fullfsync
  • ignore_check_constraints
  • incremental_vacuum
  • index_info
  • index_list
  • integrity_check
  • journal_mode
  • journal_size_limit
  • legacy_file_format
  • locking_mode
  • max_page_count
  • mmap_size
  • page_count
  • page_size
  • query_only
  • quick_check
  • read_uncommitted
  • recursive_triggers
  • reverse_unordered_selects
  • schema_version
  • secure_delete
  • shrink_memory
  • soft_heap_limit
  • synchronous
  • table_info
  • temp_store
  • user_version
  • wal_autocheckpoint
  • wal_checkpoint
  • writable_schema
application_id Queries or sets the 32-bit unsigned big-endianApplication IDinteger. Consult the SQLite application_id documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:integer
auto_vacum Queries or sets the auto-vacuum status in the database. Consult the SQLite auto_vacum documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:0 ¦ NONE ¦ 1 ¦ FULL ¦ 2 ¦ INCREMENTA
automatic_index Queries, sets, or clears the automatic indexing capability. Consult the SQLite automatic_index documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:true ¦ false
busy_timeout Queries or changes the setting of the busyTimeOut timeout. This pragma is an alternative to thebusyTimeOutmethod. SQLite makes the pragma available as a pragma for use with language bindings that do not provide direct access to the sqlite3_busy_timeout() API. ooSQLite of course does provide direct access through both thebusyTimeOut method and the oosqlBusyTimeOut routine. The pragma is included here for completeness. Consult the SQLite busy_timeout documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: single value.
  • value:milliseconds
cache_size Queries or changes the suggested maximum number of database disk pages that SQLite will hold in memory at once per open database file. Consult the SQLite cache_size documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:pages ¦ -kibibytes
cache_spill Enables or disables the ability of the pager to spill dirty cache pages to the database file in the middle of a transaction. Cache_spill is enabled by default and most applications should leave it that way as cache spilling is usually advantageous. Consult the SQLite cache_spill documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:true or false
case_sensitive_like Installs a new application-defined LIKE function that is either case sensitive or insensitive depending on the value of the case_sensitive_like pragma. By default LIKE is case-insensitive. Consult the SQLite case_sensitive_like documentation for complete details.
  • Novalueargument: invalid, condition raised.
  • Withvalueargument: single value
  • value:true or false
checkpoint_fullfsync Query or change the fullfsync flag for checkpoint operations. Consult the SQLite checkpoint_fullfsync documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:true or false
collation_list Returns a list of the collating sequences defined for the current database connection. Consult the SQLite collation_list documentation for complete details.
  • Novalueargument returns: result set.
  • Withvalueargument: invalid, condition raised
  • value:invalid
compile_options Returns the names of compile-time options used when building SQLite, one option per row. TheSQLITE_prefix is omitted from the returned option names. Consult the SQLite compile_options documentation for complete details.
  • Novalueargument returns: result set.
  • Withvalueargument: invalid, condition raised
  • value:invalid
database_list Works like a query to return one row for each database attached to the current database connection. Consult the SQLite database_list documentation for complete details.
  • Novalueargument returns: result set.
  • Withvalueargument: invalid, condition raised
  • value:invalid
defer_foreign_keys When the defer_foreign_keys PRAGMA is on, enforcement of all foreign key constraints is delayed until the outermost transaction is committed. The defer_foreign_keys pragma defaults to OFF so that foreign key constraints are only deferred if they are created as "DEFERRABLE INITIALLY DEFERRED". This pragma is only meaningful if foreign key constraints are enabled, of course. Consult the SQLite database_list documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:true or false
encoding When used as a query, if the main database has already been created, then this pragma returns the text encoding used by the main database, one of "UTF-8", "UTF-16le" (little-endian UTF-16 encoding) or "UTF-16be" (big-endian UTF-16 encoding). If the main database has not already been created, then the value returned is the text encoding that will be used to create the main database, if it is created by this session. Consult the SQLite encoding documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:UTF-8 ¦ UTF-16 ¦ UTF-16le ¦ UTF-16be
foreign_key_check Checks the database, or the table specified, for foreign key constraints that are violated and returns one row of output for each violation. Consult the SQLite foreign_key_check documentation for complete details.
  • Novalueargument returns: result set.
  • Withvalueargument returns: result set.
  • value:table_name, not required
foreign_key_list Returns one row for each foreign key that references a column in the argumenttable. Consult the SQLite foreign_key_list documentation for complete details.
  • Novalueargument: invalid, condition raised.
  • Withvalueargument returns: result set.
  • value:table_name
foreign_keys Query, set, or clear the enforcement of foreign key constraints. Consult the SQLite foreign_keys documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:true or false
freelist_count Returns the number of unused pages in the database file. Consult the SQLite freelist_count documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument: invalid, condition raised.
  • value:invalid
fullfsync Query or change the fullfsync flag. Consult the SQLite fullfsync documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:true or false
ignore_check_constraints Enables or disables the enforcement of CHECK constraints. The default setting is off, meaning that CHECK constraints are enforced by default. Consult the SQLite ignore_check_constraints documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:true or false
incremental_vacuum Causes up to N pages to be removed from the freelist. The database file is truncated by the same amount. If there are fewer than N pages on the freelist, N is omitted, or N is less than 1, all pages are removed. Consult the SQLite incremental_vacuum documentation for complete details.
  • Novalueargument returns: result code.
  • Withvalueargument returns: result code.
  • value:N, not required
index_info Returns one row each column in the named index. The first column of the result is the rank of the column within the index. The second column of the result is the rank of the column within the table. The third column of output is the name of the column being indexed. Consult the SQLite index_info documentation for complete details.
  • Novalueargument: invalid, condition raised.
  • Withvalueargument returns: result set
  • value:index_name, required
index_list Returns one row for each index associated with the given table. Columns of the result set include the index name and a flag to indicate whether or not the index is UNIQUE. Consult the SQLite index_list documentation for complete details.
  • Novalueargument: invalid, condition raised.
  • Withvalueargument returns: result set
  • value:table_name, required
integrity_check Does an integrity check of the entire database. It looks for out-of-order records, missing pages, malformed records, and corrupt indices. If any problems are found, then strings are returned (as multiple rows with a single column per row) which describe the problems. At most N errors will be reported before the analysis quits. The default value for N is 100. If no errors are found, a single row with the valueokis returned. Consult the SQLite integrity_check documentation for complete details.
  • Novalueargument returns: result set.
  • Withvalueargument returns: result set.
  • value:N, not required
journal_mode Queries or sets the journal mode for databases associated with the current database connection. Consult the SQLite journal_mode documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: single value
  • value:DELETE ¦ TRUNCATE ¦ PERSIST ¦ MEMORY ¦ WAL ¦ OFF
journal_size_limit This pragma can be used to limit the size (N bytes) of rollback-journal and WAL files left in the file-system after transactions or checkpoints. Consult the SQLite journal_size_limit documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: single value.
  • value:N or -1, no limit
legacy_file_format Sets or queries the value of the legacy_file_format flag. When this flag is on, new SQLite databases are created in a file format that is readable and writable by all versions of SQLite going back to 3.0.0. When the flag is off, new databases are created using the latest file format which might not be readable or writable by versions of SQLite prior to 3.3.0. Consult the SQLite legacy_file_format documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:true or false
locking_mode Sets or queries the database connection locking-mode. The locking-mode is either NORMAL or EXCLUSIVE. Consult the SQLite locking_mode documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:NORMAL ¦ EXCLUSIVE
max_page_count Queries or set the maximum number of pages (N) in the database file. Both forms of the pragma return the maximum page count. The set form attempts to modify the maximum page count. The maximum page count cannot be reduced below the current database size. Consult the SQLite max_page_count documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: single value.
  • value:N
mmap_size Query or change the maximum number of bytes (N) that are set aside for memory-mapped I/O on a single database. Consult the SQLite page_count documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:N
page_count Returns the total number of pages in the database file. Consult the SQLite page_count documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: invalid, condition raised.
  • value:invalid
page_size Queries or sets the page size (N) of the database. The page size must be a power of two between 512 and 65536 inclusive. Consult the SQLite page_size documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:N
query_only Prevents all changes to database files when enabled. Consult the SQLite query_only documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:true or false
quick_check Performs an integrity check, like the integrity_check pragma, except that it does not verify that index content matches table content. By skipping the verification of index content, quick_check is able to run much faster than integrity_check. At most N errors will be reported before the analysis quits. The default value for N is 100. If no errors are found, a single row with the valueokis returned. Otherwise the two pragmas are the same. Consult the SQLite quick_check documentation for complete details.
  • Novalueargument returns: result set.
  • Withvalueargument returns: result set.
  • value:N, not required
read_uncommitted Query, set, or clear READ UNCOMMITTED isolation. Consult the SQLite read_uncommitted documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:true or false
recursive_triggers Query, set, or clear the recursive trigger capability. Consult the SQLite recursive_triggers documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:true or false
reverse_unordered_selects When enabled, this pragma causes SELECT statements without an ORDER BY clause to emit their results in the reverse order of what they normally would. This can help debug applications that are making invalid assumptions about the result order. Consult the SQLite reverse_unordered_selects documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:true or false
schema_version Used to set or get the value of the schema-version. The pragmas schema_version and user_version are used to set or get the value of the schema-version and user-version, respectively. The schema-version and the user-version are big-endian 32-bit signed integers stored in the database header. The schema-version is usually only manipulated internally by SQLite. Using the schema_version pragma to modify the schema-version is potentially dangerous and may lead to program crashes or database corruption. Use with caution. The user-version is not used internally by SQLite. It may be used by applications for any purpose. Consult the SQLite schema_version documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:32-bit integer
secure_delete Queries or changes the secure-delete setting. When secure-delete on, SQLite overwrites deleted content with zeros. Consult the SQLite secure_delete documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: single value.
  • value:true or false
shrink_memory Causes the database connection on which it is invoked to free up as much memory as it can. Consult the SQLite shrink_memory documentation for complete details.
  • Novalueargument returns: result code.
  • Withvalueargument: invalid, condition raised
  • value:invalid
soft_heap_limit This pragma invokes the softHeapLimit64 method with the argument N, if N is specified and is a non-negative integer. The soft_heap_limit pragma always returns the same integer that would be returned by thesoftHeapLimit64method with an argument of -1. C-language function. Consult the SQLite shrink_memory documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: single value
  • value:N
synchronous Queries or changes the setting of the "synchronous" flag. Consult the SQLite synchronous documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:0 ¦ OFF ¦ 1 ¦ NORMAL ¦ 2 ¦ FULL
table_info Returns a row for each column in the named table. Columns in the result set include the column name, data type, whether or not the column can be NULL, and the default value for the column. Consult the SQLite table_info documentation for complete details.
  • Novalueargument: invalid, condtion raised
  • Withvalueargument returns: result set.
  • value:table_name, required
temp_store Queries or changes the setting of thetemp_storeparameter. Consult the SQLite temp_store documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:0 ¦ DEFAULT ¦ 1 ¦ FILE ¦ 2 ¦ MEMORY
user_version Used to set or get the value of the user-version. The pragmas schema_version and user_version are used to set or get the value of the schema-version and user-version, respectively. The schema-version and the user-version are big-endian 32-bit signed integers stored in the database header. The schema-version is usually only manipulated internally by SQLite. Using the schema_version pragma to modify the schema-version is potentially dangerous and may lead to program crashes or database corruption. Use with caution. The user-version is not used internally by SQLite. It may be used by applications for any purpose. Consult the SQLite schema_version documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: result code.
  • value:32-bit integer
wal_autocheckpoint Queries or sets the write-ahead log auto-checkpoint interval. When the write-ahead log is enabled (via the journal_mode pragma) a checkpoint will be run automatically whenever the write-ahead log equals or exceeds N pages in length. Setting the auto-checkpoint size (N) to zero or a negative value turns auto-checkpointing off.Consult the SQLite wal_autocheckpoint documentation for complete details.
  • Novalueargument returns: single value.
  • Withvalueargument returns: single value.
  • value:N
wal_checkpoint If the write-ahead log is enabled (via the journal_mode pragma), this pragma causes a checkpoint operation to run on the nameddatabasedatabase, or on all attached databases ifdatabaseis omitted. If write-ahead log mode is disabled, this pragma is a harmless no-op. Consult the SQLite wal_checkpoint documentation for complete details.
  • Novalueargument returns: result set.
  • Withvalueargument returns: result set.
  • value:PASSIVE ¦ FULL ¦ RESTART
writable_schema When this pragma is on, the SQLITE_MASTER tables in the database can be changed using ordinary UPDATE, INSERT, and DELETE statements. Warning: misuse of this pragma can easily result in a corrupt database file. Consult the SQLite writable_schema documentation for complete details.
  • Novalueargument returns: singel value.
  • Withvalueargument returns: result code.
  • value:true or false
Example: This example uses the table_info pragma to get information on the food_types table and then print it to the screen:
dbName = 'ooFoods.rdbx'
tablename = 'food_types'
dbConn = .ooSQLiteConnection~new(dbName, .ooSQLite~OPEN_READWRITE)
resultSet = dbConn~pragma('table_info', tablename)
z = printResultSet(resultSet)
/* Output might be for example:
cid name type notnull dflt_value pk
============================================================
0 id integer 0 NULL 1
1 name text 0 NULL 0
*/