SQLite 3.7.9 notes

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.

1. sqlite_master table contents after some sql coding. Mode is column and header is on

2. Deleting a column from existing table using sqlite transation and temporary table to save the data

3. I tried round(X,Y) function with value 9.95 and without Y parameter it gave correct result

4. To use a string in a query you should use single quotes as the SQL standard specifies

5. Use .version dot command to check current sqlite version

Intro to document-oriented databases

In this post I will explain the basics and advantages of the document-oriented databases over relational dbs.

Document database is a database constructed of documents that has its own structure and can be independent from each other i.e there is no need of relations like in RDBMS.  There are no tables, rows, relations at all. So when I want to add new field in the database I simply select a document and add it without the need to update and affect other documents in the db.

DO databases are called semi-structured dbs because they don't have the need of a predefined schema like in RDBMS.

For example if I have one document that have these fields: name, surname, proffession, phone, I can create document by filling all fields except the phone field which can be empty and it will not be kept as null but it will simply do not exist. But I can also add new field which is not in the above list for example email and give it a value of my email.

Below is a list of the advantages over RDBMS:

-  Objects are stored as documents. So now we can use the serialize method in a language to convert object to a document and save it to the db.
- Documents can be of any complexity. Objects model can be read and written at once. NICEEEEEEE :)
In this case we are relieved of the need to think of complex sql statements and stored procs.
-  Documents are all the way independent. This is good for performance and the side effects of concurrency are decreased
- Formats for accessing and using DO dbs. The documents in the dbs are described with JSON, XML and derivatives like Binary JSON
- Schema free. This ability gives us flexibilty to sturcture the data inside without the need to restructure it again after some time.
-  Built-in verisoning. DO databases can support versioning of documents and it can be done with several clicks

The DO dbs can be sorted in these categories Document, Graph, Key/Value, and Tabular/Wide Column.
 All of them with its own advantages and drawbacks.

The most popular products that declare themselves as DO dbs are:

- CouchDB
- RavenDB
- MongoDB

So where these dbs would best fit in? Well DO dbs are good for CMS and CRM systems where the user usually wants to customize his data and this is easy with saving documents. Also DO dbs would be useful in creating and storing data from a shopping cart and user sessions.

In the next posts I will try to explain briefly the above given products and their properties.