tpm/pick.sql at main · notlibrary/tpm · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE IF NOT EXISTS toothpastes (
    id INTEGER,
    brand_string TEXT,
	tube_mass_g INTEGER,
	rating INTEGER
);

INSERT INTO toothpastes (id, brand_string, tube_mass_g,rating) VALUES (0,'Toothpaste 1', 100, 100);
INSERT INTO toothpastes (id, brand_string, tube_mass_g,rating) VALUES (1,'Toothpaste 2', 100, 100);
INSERT INTO toothpastes (id, brand_string, tube_mass_g,rating) VALUES (2,'Toothpaste 3', 100, 100);
INSERT INTO toothpastes (id, brand_string, tube_mass_g,rating) VALUES (3,'Toothpaste 4', 100, 100);
INSERT INTO toothpastes (id, brand_string, tube_mass_g,rating) VALUES (4,'Nothing', 0, 0);

SELECT * FROM toothpastes WHERE id=mod((SELECT CAST(unixepoch('now') / 86400 AS INTEGER)), (SELECT COUNT(*) FROM toothpastes)) LIMIT 1;
SELECT * FROM toothpastes ORDER BY rating DESC LIMIT 1;
SELECT * FROM toothpastes ORDER BY tube_mass_g DESC LIMIT 1;
SELECT * FROM toothpastes ORDER BY rating ASC LIMIT 1;
SELECT * FROM toothpastes ORDER BY tube_mass_g ASC LIMIT 1;
SELECT * FROM toothpastes ORDER BY RANDOM() LIMIT 1;
SELECT * FROM toothpastes WHERE id = ? LIMIT 1;
SELECT * FROM toothpastes WHERE brand_string = ? LIMIT 1;