Sqlite

[[dev-notes]]

Basic SQL #

1
2
3
4
5
6
7
8


SELECT * 
FROM testing 
WHERE id > 1
ORDER BY name
LIMIT 1
OFFSET 10
;

1
2

INSERT INTO Artist (name) VALUES ('Radiohead');
SELECT * from Artist WHERE name = 'Radiohead';

1
2

UPDATE Artist SET name = 'Daft Punk' WHERE name = 'Radiohead';
SELECT * from Artist WHERE name = 'Daft Punk';

1

UPDATE Artist SET name = 'Justice' WHERE name = 'Daft Punk' RETURNING *;

1
2

ALTER TABLE table ADD COLUMN image TEXT;
ALTER TABLE table DROP COLUMN image TEXT;

1

ALTER TABLE table ADD COLUMN name TEXT NOT NULL DEFAULT 'john';

must declare foreign keys on each connection now sqlite will respect foreign keys

1

PRAGMA foreign_keys=on;

aggregation functions aggregation happens after the where clause

1
2
3
4
5

SELECT COUNT(*) FROM table;
SELECT COUNT(DISTINCT GenreId) FROM Track;

SELECT MAX(*) FROM table;
SELECT MIN(*) FROM table;

aggregation happens after the where clause cannot use agg functions in the where clause, use them in HAVING

1
2
3
4
5

SELECT trackid, count(genreid) 
FROM tracks 
WHERE something > 1
GROUP BY genreid
HAVING COUNT(genreid) > 300;

create view to reuse sql queries easily this can be dangerous on performance and usage if the view query is big

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

CREATE VIEW
easy_tracks
AS
SELECT
  t.TrackId as id,
  ar.Name as artist,
  al.Title as album,
  t.Name as track
FROM
  Track t

JOIN
  Album al
ON
  t.AlbumId = al.AlbumId

JOIN
  Artist ar
ON
  ar.ArtistId = al.ArtistId;

-- now you can do this
SELECT * FROM easy_tracks LIMIT 15;

1
2
3

EXPLAIN <query statement>
EXPLAIN QUERY PLAN<query statement>
.eqp on

shows the indexed column sqlite automatically indexed foreign keys

1
2

PRAGMA index_list(table);
CREATE INDEX idx_track_name ON TrackTable (Name);

1

DROP VIEW IF EXISTS easy_tracks;

FTS5 full text search research bm25() for nearest match / fuzzy find search

1
2

-- incomplete
CREATE VIRTUAL TABLE track_search USING FTS5(content="easy_tracks", content_rowid='id')

SQLITE #

1

sudo apt install sqlite3

Types

  • INTEGER
  • TEXT
  • BLOB
  • REAL
  • NUMERIC

creates a new db from command line

sqlite3 test.db
  • stores booleans as integers 0 and 1
  • does not enforce types
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

.mode csv

.import

.ouput ./outputFile.csv
- queries will append to this file by default

.headers on
-- Bash Commands 
-- apt install sqlite3
-- sqlite3 FILENAME
-- sqlite3 sample.sqlite3 

.quit

.tables
--shows tables

.schema
-- shows the past table declaration details

.mode csv 
-- changes import/output method to csv

.import FILE table
-- imports file into table

.mode column
-- outputs normal columns in the terminal
----------------------------------------------------------------

CREATE TABLE modelData2;

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);