-- xtra SQLite
-- SQLite Xtra v0.27 (c) 2017 Valentin Schmidt
-- SQLite version: 3.8.0.2

SQLite Xtra is free SQL-capable database xtra. It's based on the open source
database engine "SQLite". SQLite implements most of the SQL92 standard (for
details and documentation see https://www.sqlite.org/)

SQLite Xtra uses a naming convention for its basic database functions that is
similar to the PHP implementation of SQLite. In addition, it has a couple of
utilitiy functions for embedding/retrieving binary data (binary strings,
external files, arbitrary cast members) into/from a database.


FEATURES
========

- direct support for storing and retrieving members (media/compressedmedia), images and arbitrary binary
  strings.

- support of using arbitrary lingo functions (so called User Defined Functions, UDFs, either
  non-aggregate or aggregate) inside SQL statements

- support of registering callback functions for queries.

- loadable extensions: Beginning after version 3.3.6, SQLite has the ability to load new SQL functions
  and collating sequences from shared libraries and DLLs. This means that you no longer have to recompile
  SQLite in order to add new functions and collations.
  see https://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions

- full-text indexing/full-text search capabilities built-in (based on FTS4)
  see https://www.sqlite.org/fts3.html


INTERFACE:
==========

-- CONSTRUCTOR:
new object me, *

-- BASIC COMMANDS:
sqlite_open object me, string db_filename, *error_list, read_only_flag
sqlite_close object me
sqlite_exec object me, string sql, *var_list, error_list, callback
sqlite_fetch object me, string sql, *result_type, error_list, auto_decode_flag
sqlite_last_insert_rowid object me, *error_list
sqlite_changes object me, *error_list
sqlite_create_function object me, string function_name, integer num_args
sqlite_create_aggregate object me, string function_name, integer num_args, *finalizeFuncName
sqlite_busy_timeout object me, integer milliseconds
sqlite_last_error object me
sqlite_load_extension object me, string filename, *entry_point

-- UTILITIES:
*sqlite_version
*sqlite_error_string integer error
*sqlite_escape_string string input
*sqlite_encode_binary_string string binary_string
*sqlite_decode_binary_string string encoded_string
*sqlite_encode_binary_file string filename
*sqlite_decode_binary_file string encoded_string, string filename
*sqlite_encode_member any member_ref, *useCompressedMedia
*sqlite_decode_member string encoded_string, any member_ref
*sqlite_member_to_binary_string any member_ref, *useCompressedMedia
*sqlite_member_to_binary_file any member_ref, string filename, *useCompressedMedia
*sqlite_binary_string_to_member string binary_string, any member_ref
*sqlite_binary_file_to_member string filename, any member_ref

-- BYTEARRAY CONVERSION (D11.5+ only):
*sqlite_binary_string_to_bytearray string binary_string
*sqlite_bytearray_to_binary_string any bytearray, *offset, *size


DOCUMENTATION ABOUT SQLITE AND SQL:
===================================

See infos at
--> https://www.sqlite.org/
--> https://www.sqlite.org/lang.html


SOME HINTS:
===========

1) COMPACT A DATABASE
=====================

See infos at
--> https://www.sqlite.org/lang_vacuum.html

Examples:

ok = sx.sqlite_exec("VACUUM", 0, err)
ok = sx.sqlite_exec("VACUUM someTableName", 0, err)


2) DATABASE SCHEMA (information about database, tables, indexes etc.)
==================

See infos at
--> https://sqlite.org/faq.html#q7

Examples:

put sx.sqlite_fetch("SELECT * FROM sqlite_master")
put sx.sqlite_fetch("SELECT * FROM sqlite_master WHERE type='table'")
put sx.sqlite_fetch("SELECT * FROM sqlite_master WHERE type='index'")


3) USE SQL WITHOUT FILE, IN MEMORY ONLY
=======================================

See infos at:
--> https://www.sqlite.org/inmemorydb.html

Example:

sx = xtra("SQLite").new()
ok = sx.sqlite_open(":memory:")
if (ok=0) then
  put "ERROR: Error opening Database!"
else
  put sx.sqlite_fetch_single("SELECT MAX(1,2)", 0, 1)
  sx.sqlite_close()
end if
sx = 0


4) PRAGMA STATEMENTS
====================

See infos at
--> https://www.sqlite.org/pragma.html

Examples:

put db.sqlite_fetch_single("PRAGMA cache_size;", err)
db.sqlite_exec("PRAGMA cache_size=4000;", 0, err)

put db.sqlite_fetch_single("PRAGMA encoding;", err)

db.sqlite_exec("PRAGMA temp_store=1;", 0, err)
put db.sqlite_fetch_single("PRAGMA temp_store;", err)

put db.sqlite_fetch("PRAGMA database_list;", err)
put db.sqlite_fetch("PRAGMA table_info('random_numbers');", err)


5) LOADING EXTENSIONS (DLLs)
============================

See infos at
--> https://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions

Examples:

ok = sx.sqlite_exec("SELECT load_extension(#)", [_movie.path&"MBS.dll"])
  or
ok = sx.sqlite_load_extension(_movie.path&"MBS.dll"])


6) FULLTEXT SEARCH (FTS4)
=========================

See infos at
--> https://www.sqlite.org/fts3.html


7) OPEN SEVERAL DATABASE FILES AT THE SAME TIME (and even use them together in the same query)
==============================================================================================

See infos at
--> https://sqlite.org/lang_attach.html
--> https://sqlite.org/lang_detach.html

Example:

sx = xtra("SQLite").new()
sx.sqlite_open(_movie.path&"adressbook.db")
sx.sqlite_exec("ATTACH DATABASE '"&_movie.path&"customers.db' AS customers")
put db.sqlite_fetch("INSERT INTO adresses(name, fon) SELECT name,fon FROM customers.adresses")
sx.sqlite_exec("DETACH DATABASE customers")


8) VIEWS
========

See infos at
--> https://www.sqlite.org/lang_createview.html
--> https://www.sqlite.org/lang_dropview.html

Example:

CREATE VIEW v_europe AS
 SELECT name,
        population AS pop
  FROM world
  WHERE region='Europe';

SELECT * FROM v_europe


9) TRIGGERS
===========

See infos at
--> https://www.sqlite.org/lang_createtrigger.html
--> https://www.sqlite.org/lang_droptrigger.html

Example:

CREATE TRIGGER update_customer_address UPDATE OF address ON customers
  BEGIN
    UPDATE orders SET address = new.address WHERE customer_name = old.name;
  END;


10) INDEXES
===========

See infos at
--> https://www.sqlite.org/lang_createindex.html
--> https://www.sqlite.org/lang_dropindex.html

Example:

CREATE TABLE author (
   author_id      INTEGER PRIMARY KEY,
   name           CHAR(100)
);
CREATE INDEX au_name_idx ON author (name);


11) CREATE/USE AN ENCRYPTED DATABASE
====================================

sx.sqlite_open(_movie.path & "test.db")
sx.sqlite_exec("PRAGMA key = 'your_passphrase'") --> call this right after opening the db
...