Tune PHP to the Max | PHP | Android | JavaScript | HTML5

In this post I will share my thoughts about SQLite database engine. You can search the net for tutorials and info and it seems lot of sites copy most of the documentation from the original site, so therefore in this post there will be my experiences with it.

You cannot output create statements with ".schema" command on attached database. It is only possible on the main and temp databases. So therefore you have to exit sqlite prompt and then enter it again using a line: "sqlite3 your-db-name.db" so that your database is now the main database.
You cannot use ALTER sql command to change a column in a table. It is only possible to rename a table and to add new column to an existing table. DROP COLUMN and ADD CONSTRAINT sql commands are also not supported.

RIGHT OUTER JOIN and FULL OUTER JOIN are not supported. Only LEFT OUTER JOIN is implemented.
SQLite database is CASE SENSITIVE. For example GLOB and glob have different meaning in SQLite statements.
The data type of a value is associated with the value itself, not with its container. Each value stored in SQlite database has its "Storage class": NULL, INTEGER, REAL, TEXT, BLOB.
SQLite, technically, has no data types, there are storage classes in a manifest typing system. Everything, internally, is stored as text.
SQLite supports the concept of type affinity on columns. Type affinities: TEXT, NUMERIC, INTEGER, REAL, NONE.
Comments can be added with double hyphen "--" in front of a line of text or using C-like comment blocks with /* some text here */
Dot commands should not be terminated with semicolon (;)
Autoincrement keyword can be applied to a column only if that column is of type INTEGER
Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class.
SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).
The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required.
Sorting of data types is achieved like this: (NULL < NULL or NULL < any other type), (INTEGER or REAL) < (TEXT or BLOB), (between INTEGER and REAL types a numerical comparison is used), (TEXT < BLOB), (between TEXT values a collating sequence is used), between two BLOBs a memcmp() function is used
Use this SQL construct to insert multiple rows into a table in one go: INSERT INTO 'tablename' ('column1', 'column2') SELECT 'data1' AS 'column1', 'data2' AS 'column2' UNION SELECT 'data3', 'data4' UNION SELECT 'data5', 'data6' UNION SELECT 'data7', 'data8';
Use ".header on", ".mode column" and ".width" dot commands to refine the output of the queries.
Use this command: select sql from sqlite_master where type='table' and tbl_name='table_name'; to select schema of the specified table. Much like ".schema" command shows create statements for the whole database, this shows only give table.
sqlite_master table contains 5 columns named in order: type, name, tbl_name, rootpage and sql
GLOB is a logical operator used to compare value to similar values using wild card operators. Also it is case sensitive unlike the LIKE comparison operator. GLOB uses "?" for one character and "*" from many, while LIKE uses "_" and "%" wild-cards correspondingly.
|| operator adds two different strings and make new one aka concatenate.
Bitwise binary operators are: & (AND), | (OR), ~ (complement), << (left shift) and >> (right shift)
Values NULL, 0.0, 0, 'english' and '0' are all considered to be false. Values 1, 1.0, 0.1, -0.1 and '1english' are considered to be true.
The IS and IS NOT operators work like = and != except when one or both of the operands are NULL.
To use a string in a query you should use single quotes as the SQL standard specifies. Check image 4, using text="text" is identical to saying select * from proba where text=text; which is always true, so all rows will be returned.
To escape a single quote in a string use another single quote, like this insert into table_name (my_string) values ('it is nine o''clock');
In the docs I couldn't find nor using .help command told me how to see current sqlite version. I however tried command .version which worked. Check image 5.