Differences

This shows you the differences between two versions of the page.

@@ Line 1: / Line 1: @@
+====== Importing Delimited File ======
+To import a file called ''data.csv'', first we create a table to hold that data, choose the delimiter (in this case ",") and then import the data.
+<code sql>
+sqlite> create table csvdata (id integer, datatype_id integer, level integer, meaning text);
+sqlite> .separator ","
+sqlite> .import csvdata.csv test
+</code>
+====== Showing Headers ======
+To show the column headers when issuing a command issue:
+<code>
+.headers ON
+</code>
+In order to make sqlite always show the headers, add ''.headers ON'' to ''~/.sqliterc''.
+====== Avoiding Locking Issues for SQLite databases on NFS Mounts ======
+As per the FAQ question on the [[https://sqlite.org/faq.html#q5|SQLite FAQ]], SQLite does not work well if databases are hosted on NFS filesystems. The problem is that the locks requested by SQLite do not synchronize by default with the filesystem locks on share exported by the NFS server.
+One workaround is to mount the NFS share with the ''nolock'' (Linux) or ''nolockd'' (FreeBSD). This will enable locking on the client, such that locks will work on the client where the filesystem is mounted but they will not transfer over to the server.
+A different option would be to just switch over to NFSv4 (with or without Kerberos) that takes care of this issue and synchronizes locks. NFSv4 is supported in recent versions of FreeBSD by passing ''nfsv4'' as a mount option (can also be added to ''/etc/fstab'').
+====== Using Date and Time ======
+SQLite has some built-in functions that deal with date and time.
+  * ''date()'' refers to dates in the format ''YYYY-MM-DD'',
+  * ''datetime()'' refers to dates in format ''YYYY-MM-DD HH:MM:SS'' and
+  * ''time()'' refers to the current time in format ''YYYY-MM-DD''
+For example:
+<code>
+date('now')
+</code>
+will refer to the current date, and it is also possible to pass relative modifiers by using a second parameter. For example:
+<code>
+date('now', '+1 day')
+</code>
+refers to he current date plus one day.
+It is important to realize that there is no underlying datatype for either date or time such that these functions will in fact "generate" strings following their formatting definitions. When the functions ''time()'', ''date()'' or ''datetime()'' are used in SQL statements involving table columns, the strings that are generated are then compared //as strings// as the SQL statement is executed.
+====== Waiting for Database Locks on the Command-Line ======
+When accessing the database on the command-line, in case the database it locks, the command just returns an error. A timeout can be passed to the command-line in order to wait for any locks to expire. For example, the command:
+<code bash>
+sqlite3 -cmd ".timeout 60000" DATABASE_FILE
+</code>
+where:
+  * ''DATABASE_FILE'' is the path to a database file
+will wait for $60s$ for locks to expire before giving up and returning an error.
+====== Accessing Databases in Read-Only Mode on the Command Line ======
+For most operations where the purpose is to only read the database, the database should be opened in read-only mode in order to optimize concurrent accesses. On the command-line, in order to open an SQLite database in read-only mode, the trick is to reference the database by URL rather than a path to file, such that parameters can be added. For example, the following command:
+<code bash>
+sqlite3 -cmd ".timeout 60000" "file:///PATH_TO_DATABASE_FILE?mode=ro"
+</code>
+will attempt to open ''PATH_TO_DATABASE_FILE'' in read-only mode and also additionally wait for $60s$ for any database locks to expire.
+Note that the URL has triple-slashes but that the last forward-slash is just diluted in meaning because it stands for the root of the filesystem, ie: ''/usr/local...''.