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.

sqlite> CREATE TABLE csvdata (id INTEGER, datatype_id INTEGER, level INTEGER, meaning text);
sqlite> .separator ","
sqlite> .import csvdata.csv test

To show the column headers when issuing a command issue:

.headers ON

In order to make sqlite always show the headers, add .headers ON to ~/.sqliterc.

As per the FAQ question on the 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).

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:

date('now')

will refer to the current date, and it is also possible to pass relative modifiers by using a second parameter. For example:

date('now', '+1 day')

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.

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:

sqlite3 -cmd ".timeout 60000" DATABASE_FILE

where:

• DATABASE_FILE is the path to a database file

will wait for for locks to expire before giving up and returning an error.

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:

sqlite3 -cmd ".timeout 60000" "file:///PATH_TO_DATABASE_FILE?mode=ro"

will attempt to open PATH_TO_DATABASE_FILE in read-only mode and also additionally wait for 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….