-- xtra SQLite
-- SQLite Xtra v2.0 (c) 2011 Valentin Schmidt
-- contact: fluxus@freenet.de
-- SQLite version: 3.7.2

SQLite Xtra is free SQL-capable database xtra. It's based on the open source database engine "SQLite", 
which is also used by commercial xtras like Arca Xtra and MelixDB Xtra (so database files are 
exchangeable!). SQLite implements most of the SQL92 standard. (for details and documentation see 
http://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.


LAST CHANGES
============

v2.0
- added strong database encryption (AES-256) via SQLCipher

v1.9-beta:
- ported latest win version to mac


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 http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions

- full-text indexing/full-text search capabilities (using the loadable fts1 or fts2 extensions)
  see
  http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex
  http://www.sqlite.org/cvstrac/wiki?p=FtsTwo


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

-- xtra SQLite
-- SQLite Xtra v2.0 (c) 2011 Valentin Schmidt
-- contact: fluxus@freenet.de
-- SQLite version: 3.7.2

-- CONSTRUCTOR:
new object me, *

-- BASIC COMMANDS:
sqlite_open object me, string db_filename, *error_list
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_fetch_single object me, string sql, *error_list, as_integer
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
http://www.sqlite.org/
http://www.sqlite.org/lang.html


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

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

--> use the "VACUUM" sql command

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

see http://www.sqlite.org/lang_vacuum.html


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

--> check out the (hidden) table "sqlite_master":

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
=======================================

--> use sqlite_open(":memory:")

-- 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 http://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) [NOT TESTED YET ON TH MAC!!!]
============================

--> http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions

ok = sx.sqlite_exec("SELECT load_extension('"&the moviepath&"extensions\fts2.dll')")
ok = sx.sqlite_exec("SELECT load_extension(#)", [the moviepath&"file.dll"])


6) FULLTEXT SEARCH (FTS1/FTS2) [NOT TESTED YET ON TH MAC!!!]
==============================

see code in demo, and additional info at
--> http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex
--> http://www.sqlite.org/cvstrac/wiki?p=FtsTwo


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

--> http://www.sqlite.org/lang_attach.html
--> http://www.sqlite.org/lang_detach.html

example:

sx = xtra("SQLite").new()  
sx.sqlite_open(the moviepath&"adressbook.db")
sx.sqlite_exec("ATTACH DATABASE '"&the moviepath&"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
========

--> http://www.sqlite.org/lang_createview.html
--> http://www.sqlite.org/lang_dropview.html

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

SELECT * FROM v_europe


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

--> http://www.sqlite.org/lang_createtrigger.html
--> http://www.sqlite.org/lang_droptrigger.html

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
===========

--> http://www.sqlite.org/lang_createindex.html
--> http://www.sqlite.org/lang_dropindex.html

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(the moviepath & "test.db")
sx.sqlite_exec("PRAGMA key = 'your_passphrase'") --> call this right after opening the db
...