89 lines
2.4 KiB
MySQL
89 lines
2.4 KiB
MySQL
|
DROP TABLE IF EXISTS notes;
|
||
|
|
DROP TABLE IF EXISTS note_relationships;
|
||
|
|
DROP TABLE IF EXISTS pages;
|
||
|
|
DROP TABLE IF EXISTS page_relationships;
|
||
|
|
DROP TABLE IF EXISTS favorites;
|
||
|
|
|
||
|
|
CREATE TABLE notes (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
uuid TEXT NOT NULL UNIQUE,
|
||
|
|
notetype TEXT
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE pages (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
slug text NOT NULL UNIQUE,
|
||
|
|
note_id INTEGER,
|
||
|
|
FOREIGN KEY (note_id) REFERENCES notes (id) ON DELETE NO ACTION ON UPDATE NO ACTION
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE note_relationships (
|
||
|
|
note_id INTEGER NOT NULL,
|
||
|
|
parent_id INTEGER NOT NULL,
|
||
|
|
position INTEGER NOT NULL,
|
||
|
|
nature TEXT NOT NULL,
|
||
|
|
FOREIGN KEY (note_id) REFERENCES notes (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
||
|
|
FOREIGN KEY (parent_id) REFERENCES notes (id) ON DELETE NO ACTION ON UPDATE NO ACTION
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE page_relationships (
|
||
|
|
note_id INTEGER NOT NULL,
|
||
|
|
page_id INTEGER NOT NULL,
|
||
|
|
FOREIGN KEY (note_id) references notes (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
||
|
|
FOREIGN KEY (page_id) references pages (id) ON DELETE NO ACTION ON UPDATE NO ACTION
|
||
|
|
);
|
||
|
|
|
||
|
|
INSERT INTO notes (id, uuid, notetype) VALUES
|
||
|
|
(1, "U1", "root"),
|
||
|
|
(2, "U2", "note"),
|
||
|
|
(3, "U3", "note"),
|
||
|
|
(4, "U4", "note"),
|
||
|
|
(5, "U5", "note"),
|
||
|
|
(6, "U6", "note"),
|
||
|
|
(7, "U7", "note"),
|
||
|
|
(8, "U8", "note");
|
||
|
|
|
||
|
|
INSERT INTO note_relationships (note_id, parent_id, position, nature) VALUES
|
||
|
|
(2, 1, 1, "note"),
|
||
|
|
(3, 1, 2, "note"),
|
||
|
|
(4, 3, 1, "note"),
|
||
|
|
(5, 3, 2, "note"),
|
||
|
|
(6, 5, 1, "note"),
|
||
|
|
(7, 4, 1, "note"),
|
||
|
|
(8, 7, 1, "note");
|
||
|
|
|
||
|
|
SELECT id, uuid, parent_id, parent_uuid, position, notetype
|
||
|
|
FROM (
|
||
|
|
WITH RECURSIVE notetree (
|
||
|
|
id, uuid, parent_id, parent_uuid, position, notetype, cycle
|
||
|
|
)
|
||
|
|
|
||
|
|
AS (
|
||
|
|
SELECT notes.id, notes.uuid,
|
||
|
|
notes.id AS parent_id,
|
||
|
|
notes.uuid AS parent_uuid,
|
||
|
|
0, notes.notetype, ','||notes.id||','
|
||
|
|
FROM notes
|
||
|
|
WHERE notes.id = 1 AND notes.notetype = "root"
|
||
|
|
|
||
|
|
UNION
|
||
|
|
SELECT notes.id, notes.uuid,
|
||
|
|
notetree.id AS parent_id,
|
||
|
|
notetree.uuid AS parent_uuid,
|
||
|
|
note_relationships.position,
|
||
|
|
notes.notetype,
|
||
|
|
notetree.cycle||notes.id||','
|
||
|
|
FROM notes
|
||
|
|
INNER JOIN note_relationships
|
||
|
|
ON notes.id = note_relationships.note_id
|
||
|
|
INNER JOIN notetree
|
||
|
|
ON note_relationships.parent_id = notetree.id
|
||
|
|
WHERE notetree.cycle NOT LIKE '%,'||notes.id||',%'
|
||
|
|
ORDER BY note_relationships.position
|
||
|
|
)
|
||
|
|
|
||
|
|
SELECT * from notetree);
|
||
|
|
|
||
|
|
|
||
|
|
|