veryroundbird.house
1-- Simplified SQLite Database Migration Script for Issues and Comments
2
3-- Migration for issues table
4CREATE TABLE issues_new (
5 id integer primary key autoincrement,
6 owner_did text not null,
7 repo_at text not null,
8 issue_id integer not null,
9 title text not null,
10 body text not null,
11 open integer not null default 1,
12 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
13 issue_at text,
14 unique(repo_at, issue_id),
15 foreign key (repo_at) references repos(at_uri) on delete cascade
16);
17
18-- Migrate data to new issues table
19INSERT INTO issues_new (
20 id, owner_did, repo_at, issue_id,
21 title, body, open, created, issue_at
22)
23SELECT
24 id, owner_did, repo_at, issue_id,
25 title, body, open, created, issue_at
26FROM issues;
27
28-- Drop old issues table
29DROP TABLE issues;
30
31-- Rename new issues table
32ALTER TABLE issues_new RENAME TO issues;
33
34-- Migration for comments table
35CREATE TABLE comments_new (
36 id integer primary key autoincrement,
37 owner_did text not null,
38 issue_id integer not null,
39 repo_at text not null,
40 comment_id integer not null,
41 comment_at text not null,
42 body text not null,
43 created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
44 unique(issue_id, comment_id),
45 foreign key (repo_at, issue_id) references issues(repo_at, issue_id) on delete cascade
46);
47
48-- Migrate data to new comments table
49INSERT INTO comments_new (
50 id, owner_did, issue_id, repo_at,
51 comment_id, comment_at, body, created
52)
53SELECT
54 id, owner_did, issue_id, repo_at,
55 comment_id, comment_at, body, created
56FROM comments;
57
58-- Drop old comments table
59DROP TABLE comments;
60
61-- Rename new comments table
62ALTER TABLE comments_new RENAME TO comments;