SQLite: обобщенный пример

Я продемонстрирую простой пример - работу с некоторыми песнями,
которые нужно разделять по рейтингу - с низкой стоимостью -
помещать в одну таблицу, а с высокой - в другую, со средней -
в третью. В демонстрации использую и Виды (VIEW) и
Триггеры (TRIGGER). Мой файлик с командами такой:

/* some hacks */
.output /dev/null
PRAGMA synchronous=OFF;
PRAGMA journal_mode=MEMORY;
PRAGMA temp_store=MEMORY;
.output stdout

CREATE TABLE IF NOT EXISTS songs (
    song_id INTEGER PRIMARY KEY,
    song_created INTEGER NOT NULL DEFAULT (datetime('now','localtime')),
    song_name VARCHAR (50),
    song_rate NUMERIC
);

CREATE INDEX IF NOT EXISTS idx_rate ON songs (song_rate ASC);

/* low rating songs */
CREATE VIEW IF NOT EXISTS songs_lowrate AS
    SELECT * FROM songs WHERE song_rate<50 ORDER BY song_rate;


/* top rating songs */
CREATE TABLE IF NOT EXISTS songs_toprate (
    song_id INTEGER PRIMARY KEY,
    song_name VARCHAR(50),
    song_rate NUMERIC
);

/* selects top rating on insert */
CREATE TRIGGER IF NOT EXISTS trg_songs_ai
AFTER INSERT ON songs
WHEN new.song_rate>50
BEGIN
   INSERT INTO songs_toprate (song_name,song_rate)
	   VALUES (new.song_name,new.song_rate);
END;

INSERT INTO songs(song_name,song_rate) VALUES('Song 1',10);
INSERT INTO songs(song_name,song_rate) VALUES('Song 2',20);
INSERT INTO songs(song_name,song_rate) VALUES('Song 3',30);
INSERT INTO songs(song_name,song_rate) VALUES('Song 4',40);
INSERT INTO songs(song_name,song_rate) VALUES('Song 5',50);
INSERT INTO songs(song_name,song_rate) VALUES('Song 0',50);
INSERT INTO songs(song_name,song_rate) VALUES('Song 6',60);
INSERT INTO songs(song_name,song_rate) VALUES('Song 7',70);
INSERT INTO songs(song_name,song_rate) VALUES('Song 8',80);
INSERT INTO songs(song_name,song_rate) VALUES('Song 9',90);

/* middle rating songs */
CREATE TABLE songs_midrate AS
    SELECT song_id, song_name, song_rate FROM songs
        WHERE song_rate BETWEEN 30 AND 70;

ALTER TABLE songs_midrate ADD PRIMARY KEY (song_id) AUTOINCREMENT;

/* trigger on view */
CREATE TRIGGER IF NOT EXISTS songs_lowrate_ioi
INSTEAD OF INSERT ON songs_lowrate
BEGIN
   INSERT INTO songs_midrate (song_id,song_name,song_rate)
	   VALUES (new.song_id,new.song_name,new.song_rate);
END;

INSERT INTO songs_lowrate(song_id,song_name,song_rate) VALUES (100,'Song X',25);

.headers on
.mode column

SELECT 'ALL',* FROM songs;
SELECT 'TOP',* FROM songs_toprate;
SELECT 'LOW',* FROM songs_lowrate;
SELECT 'MID',* FROM songs_midrate;
SELECT song_rate, COUNT(*), GROUP_CONCAT(song_name) FROM songs GROUP BY 1;

WITH tab(w,name,rate) AS (SELECT 'WITH', song_name, song_rate
    FROM songs WHERE song_rate<=30)
SELECT * FROM tab;

WITH RECURSIVE
  cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<10)
SELECT x FROM cnt;

В данном пример - доступ к непопулярным песням выполняется через
вид songs_lowrate, а популярные песни - помещаются в отдельную таблицу
триггером, срабатывающим после реального добавления товара в таблицу songs.

Есть еще таблица songs_midrate которая формируется лишь однажды и содержит снимок
среднепопулярных песен. При попытке вставить строку в вид songs_lowrate -
новая песня на самом деле добавляется в таблицу songs_midrate. Это делается
также с помощью триггера.