wiki:Notes/SQLite

SQLite

The only database you'll ever need.

http://www.sqlite.org/

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.

In the public domain, that is unlicenced, not GPL.

http://www.sqlite.org/docs.html

http://www.sqlite.org/lang.html

http://en.wikipedia.org/wiki/SQLite

... SQLite development stores revisions of its source code in Fossil, a distributed version control system that is itself built upon an SQLite database.

See RevisionControl#Fossil

Tutorials

http://www.w3resource.com/sqlite/index.php

Could be best tutorial.

http://www.sqlite.org/datatype3.html

Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:

NULL. The value is a NULL value.

INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

BLOB. The value is a blob of data, stored exactly as it was input.

http://zetcode.com/db/sqlite/

http://www.tutorialspoint.com/sqlite/

http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html

http://freecode.com/articles/sqlite-tutorial

http://www.techonthenet.com/sqlite/

http://www.w3resource.com/sqlite/index.php

http://www.thegeekstuff.com/2012/09/sqlite-command-examples/

Tools

Mozilla and SQLite

https://developer.mozilla.org/en-US/docs/Storage

https://developer.mozilla.org/en-US/search?q=sqlite

https://addons.mozilla.org/en-us/firefox/addon/sqlite-manager/

See Firefox plugin SQLite Manager - Firefox#SQLite

SQLite Browser

http://sqlitebrowser.org/

DB Browser for SQLite is a high quality, visual, open source tool to create, design, and edit database files compatible with SQLite.

https://github.com/sqlitebrowser/sqlitebrowser

DB Browser for SQLite project. Previously known as "SQLite Database Browser" and "Database Browser for SQLite".

https://github.com/sqlitebrowser/sqlitebrowser/blob/master/BUILDING.md

The GPL version of Qt is available in almost all Linux distributions as a default package.

Linux is version 2.0, versus current Windows version 3.5.1. Supposedly can build with cmake and QT3.

Windows version is 'bi-licensed'. Whoooa Baaaby !

Command Line Interface

http://www.sqlite.org/cli.html

  For a listing of the available dot commands, you can enter ".help" at any time. For example:

    sqlite> .help
    .backup ?DB? FILE      Backup DB (default "main") to FILE
    .bail on|off           Stop after hitting an error.  Default OFF
    .clone NEWDB           Clone data into NEWDB from the existing database
    .databases             List names and files of attached databases
    .dump ?TABLE? ...      Dump the database in an SQL text format
                             If TABLE specified, only dump tables matching
                             LIKE pattern TABLE.
    .echo on|off           Turn command echo on or off
    .eqp on|off            Enable or disable automatic EXPLAIN QUERY PLAN
    .exit                  Exit this program
    .explain ?on|off?      Turn output mode suitable for EXPLAIN on or off.
                             With no args, it turns EXPLAIN on.
    .fullschema            Show schema and the content of sqlite_stat tables
    .headers on|off        Turn display of headers on or off
    .help                  Show this message
    .import FILE TABLE     Import data from FILE into TABLE
    .indices ?TABLE?       Show names of all indices
                             If TABLE specified, only show indices for tables
                             matching LIKE pattern TABLE.
    .load FILE ?ENTRY?     Load an extension library
    .log FILE|off          Turn logging on or off.  FILE can be stderr/stdout
    .mode MODE ?TABLE?     Set output mode where MODE is one of:
                             csv      Comma-separated values
                             column   Left-aligned columns.  (See .width)
                             html     HTML <table> code
                             insert   SQL insert statements for TABLE
                             line     One value per line
                             list     Values delimited by .separator string
                             tabs     Tab-separated values
                             tcl      TCL list elements
    .nullvalue STRING      Use STRING in place of NULL values
    .once FILENAME         Output for the next SQL command only to FILENAME
    .open ?FILENAME?       Close existing database and reopen FILENAME
    .output ?FILENAME?     Send output to FILENAME or stdout
    .print STRING...       Print literal STRING
    .prompt MAIN CONTINUE  Replace the standard prompts
    .quit                  Exit this program
    .read FILENAME         Execute SQL in FILENAME
    .restore ?DB? FILE     Restore content of DB (default "main") from FILE
    .save FILE             Write in-memory database into FILE
    .schema ?TABLE?        Show the CREATE statements
                             If TABLE specified, only show tables matching
                             LIKE pattern TABLE.
    .separator STRING ?NL? Change separator used by output mode and .import
                             NL is the end-of-line mark for CSV
    .shell CMD ARGS...     Run CMD ARGS... in a system shell
    .show                  Show the current values for various settings
    .stats on|off          Turn stats on or off
    .system CMD ARGS...    Run CMD ARGS... in a system shell
    .tables ?TABLE?        List names of tables
                             If TABLE specified, only list tables matching
                             LIKE pattern TABLE.
    .timeout MS            Try opening locked tables for MS milliseconds
    .timer on|off          Turn SQL timer on or off
    .trace FILE|off        Output each SQL statement as it is run
    .vfsname ?AUX?         Print the name of the VFS stack
    .width NUM1 NUM2 ...   Set column widths for "column" mode
                             Negative values right-justify

Full Text Search

https://www.sqlite.org/fts3.html

The FTS3 and FTS4 extension modules allows users to create special tables with a built-in full-text index (hereafter "FTS tables"). The full-text index allows the user to efficiently query the database for all rows that contain one or more words (hereafter "tokens"), even if the table contains many lar[ge documents].

https://github.com/coolaj86/sqlite-fts-demo

Install the latest version of SQLite3 (>= 3.7.14) ... Don't use the one that comes with OS X, no matter the version ...

However, as you approach somewhere between 10,000 and 100,000 records, you might notice your queries starting to run slower (whole seconds, even minutes) ...

If you have a MacBook? Pro with 16GiB RAM and the 1.5GiB database is still in your disk cache then that query will take around 4.5 seconds ...

Issues and Answers

SQLite to the Max ...

Triggers Rather Than Foreign Key References

Using triggers instead of referencing foreign keys.

https://www.sqlite.org/lang_createview.html

You cannot DELETE, INSERT, or UPDATE a view. Views are read-only in SQLite. However, in many cases you can use an INSTEAD OF trigger on the view to accomplish the same thing.

https://www.sqlite.org/lang_createtrigger.html#instead_of_trigger

https://www.sqlite.org/omitted.html

https://www.sqlite.org/lang_createtrigger.html

Also See

PythonSQLite

Android#SQLite

Query wiki for 'sqlite'

Last modified 2 years ago Last modified on 05/27/2015 01:24:14 PM