SQLite

• CREATE [TEMP] TABLE [IF NOT EXISTS] tname
• CREATE [UNIQUE] INDEX [IF NOT EXISTS] iname ON tname
• DROP [INDEX|TABLE|TRIGGER|VIEW] [IF EXISTS] name
• INSERT
  • conflict resolution https://www.sqlite.org/lang_conflict.html
    • rollback
    • abort
    • fail
    • ignore
    • replace
  • INSERT OR IGNORE - UNIQUE
  • INSERT ON CONFLICT IGNORE - UNIQUE+NULL

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

create table artist( -- parent table
  artistid   integer primary key, -- parent key
  artistname text
);
create table track( -- child table
  trackid     integer,
  trackname   text,
  trackartist integer, -- add NOT NULL? -- child key
  foreign key(trackartist) references artist(artistid)
);
create index trackindex ON track(trackartist); -- !!! not created automatically

• Disabled by default

• Enabled on each db connection

  > PRAGMA foreign_keys = ON; -- ON=1 OFF=0
  

• Used to enforce "exists" relationships between tables
• If foreign key is NULL, then no corresponding parent entry is required

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

create virtual table mytablename using fts5(
  linkid UNINDEXED, -- not added to the FTS index
  header, -- mostly ignores "column options", everything after the table name, but warns if type doesn't match
  title,
  columnsize=0 -- ?
);

• has an implicit rowid field (you can reference it, insert into it)
  • can be changed in vacuum
• functions
  • highlight(table,colidx,leftstr,rightstr) - returns a copy of the text, matchs wrapped by leftstr and rightstr
  • bm25(table) - returns the accuracy of the current match (lower is better match) "ORDER BY bm25(table)"
  • snippet() - like highlight() but returns smaller part of the column text
• 21 https://abdus.dev/posts/quick-full-text-search-using-sqlite/
• 23 https://darksi.de/13.sqlite-fts5-structure/
• 24 https://www.bytefish.de/blog/experimenting_with_sqlite_fts5.html

select * from tb1
where col1 match ? AND col2 match ?;