This shows you the differences between two versions of the page.
Previous revision | |||
— | fuss:sqlite [2025/05/31 21:47] (current) – [Using Date and Time] office | ||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== Importing Delimited File ====== | ||
+ | |||
+ | To import a file called '' | ||
+ | |||
+ | <code sql> | ||
+ | sqlite> create table csvdata (id integer, datatype_id integer, level integer, meaning text); | ||
+ | sqlite> .separator "," | ||
+ | sqlite> .import csvdata.csv test | ||
+ | </ | ||
+ | |||
+ | ====== Showing Headers ====== | ||
+ | |||
+ | To show the column headers when issuing a command issue: | ||
+ | < | ||
+ | .headers ON | ||
+ | </ | ||
+ | |||
+ | In order to make sqlite always show the headers, add '' | ||
+ | |||
+ | ====== Avoiding Locking Issues for SQLite databases on NFS Mounts ====== | ||
+ | |||
+ | As per the FAQ question on the [[https:// | ||
+ | |||
+ | One workaround is to mount the NFS share with the '' | ||
+ | |||
+ | 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 '' | ||
+ | |||
+ | ====== Using Date and Time ====== | ||
+ | |||
+ | SQLite has some built-in functions that deal with date and time. | ||
+ | * '' | ||
+ | * '' | ||
+ | * '' | ||
+ | |||
+ | For example: | ||
+ | < | ||
+ | date(' | ||
+ | </ | ||
+ | will refer to the current date, and it is also possible to pass relative modifiers by using a second parameter. For example: | ||
+ | < | ||
+ | date(' | ||
+ | </ | ||
+ | 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 " | ||
+ | |||
+ | ====== Waiting for Database Locks on the Command-Line ====== | ||
+ | |||
+ | When accessing the database on the command-line, | ||
+ | <code bash> | ||
+ | sqlite3 -cmd " | ||
+ | </ | ||
+ | where: | ||
+ | * '' | ||
+ | |||
+ | 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, | ||
+ | <code bash> | ||
+ | sqlite3 -cmd " | ||
+ | </ | ||
+ | will attempt to open '' | ||
+ | |||
+ | 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: ''/ | ||
+ | |||
+ | |||
+ | |||
For the contact, copyright, license, warranty and privacy terms for the usage of this website please see the contact, license, privacy, copyright.