Tips & Tricks

@@Sqlite_logo@@

SQLite

Date and time

• Date and time
  Date and time functions
• Date_to_epoch
  Date to EPOCH and vice versa

Images

• Handle images
• Raw images or Base64 encoded images

Indexes

• glob vs. like - wildcard on index.
• List unique indexes

Insert/update/replace

• Default value on SELECT: coalesce(x,y,..)
• Delete rows with duplicate values
  How to delete all rows with duplicate values in 2 columns but keep the last inserted row? (Based on )
• Copy one column at a time
  Copy from one table to another - one column at a time
• Insert UUID as ID (Based on )
• UUID as default value for id
  SQLite - Generate GUID/UUID on SELECT INTO statement (Based on )

Profiling, debug and cleanup

• Database analytics
  • get the first 10 tables using the most disk space
  • see how efficiently the content of a table is stored on disk
  • average fan-out for a table
  • fraction of the pages in a database are sequential
• Profiler - Simple profiler for SQLite
• Performance tuning - Read, write

Select

• Get the latest X rows of each action Tail for SQLite
• Diff on two tables
• Queue system

Strings

• Handle newlines.
  Newlines (and other special characters) can be escaped using the CHAR() function or using the hex X’0A’.
• Extract nested JSON from SQLite (Based on )

Various

• sqlean
  All the Missing SQLite Functions (From nalgeon / sqlean)
• Paradox
  Convert Paradox data to SQLite