This is my new blog CMS https://jlelse.blog
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

151 lines
6.2 KiB

4 months ago
3 months ago
3 months ago
3 months ago
3 months ago
3 months ago
2 months ago
1 month ago
4 months ago
  1. package main
  2. import (
  3. "database/sql"
  4. "github.com/lopezator/migrator"
  5. )
  6. func migrateDb() error {
  7. startWritingToDb()
  8. defer finishWritingToDb()
  9. m, err := migrator.New(
  10. migrator.Migrations(
  11. &migrator.Migration{
  12. Name: "00001",
  13. Func: func(tx *sql.Tx) error {
  14. _, err := tx.Exec(`
  15. create table posts (path text not null primary key, content text, published text, updated text, blog text not null, section text);
  16. create table post_parameters (id integer primary key autoincrement, path text not null, parameter text not null, value text);
  17. create index index_pp_path on post_parameters (path);
  18. create trigger after delete on posts begin delete from post_parameters where path = old.path; end;
  19. create table indieauthauth (time text not null, code text not null, me text not null, client text not null, redirect text not null, scope text not null);
  20. create table indieauthtoken (time text not null, token text not null, me text not null, client text not null, scope text not null);
  21. create index index_iat_token on indieauthtoken (token);
  22. create table autocert (key text not null primary key, data blob not null, created text not null);
  23. create table activitypub_followers (blog text not null, follower text not null, inbox text not null, primary key (blog, follower));
  24. create table webmentions (id integer primary key autoincrement, source text not null, target text not null, created integer not null, status text not null default "new", title text, content text, author text, type text, unique(source, target));
  25. create index index_wm_target on webmentions (target);
  26. `)
  27. return err
  28. },
  29. },
  30. &migrator.Migration{
  31. Name: "00002",
  32. Func: func(tx *sql.Tx) error {
  33. _, err := tx.Exec(`
  34. drop table autocert;
  35. `)
  36. return err
  37. },
  38. },
  39. &migrator.Migration{
  40. Name: "00003",
  41. Func: func(tx *sql.Tx) error {
  42. _, err := tx.Exec(`
  43. drop trigger AFTER;
  44. create trigger trigger_posts_delete_pp after delete on posts begin delete from post_parameters where path = old.path; end;
  45. `)
  46. return err
  47. },
  48. },
  49. &migrator.Migration{
  50. Name: "00004",
  51. Func: func(tx *sql.Tx) error {
  52. _, err := tx.Exec(`
  53. create view view_posts_with_title as select id, path, title, content, published, updated, blog, section from (select p.rowid as id, p.path as path, pp.value as title, content, published, updated, blog, section from posts p left outer join post_parameters pp on p.path = pp.path where pp.parameter = 'title');
  54. create virtual table posts_fts using fts5(path unindexed, title, content, published unindexed, updated unindexed, blog unindexed, section unindexed, content=view_posts_with_title, content_rowid=id);
  55. insert into posts_fts(posts_fts) values ('rebuild');
  56. `)
  57. return err
  58. },
  59. },
  60. &migrator.Migration{
  61. Name: "00005",
  62. Func: func(tx *sql.Tx) error {
  63. _, err := tx.Exec(`
  64. drop view view_posts_with_title;
  65. create view view_posts_with_title as select id, path, title, content, published, updated, blog, section from (select p.rowid as id, p.path as path, pp.value as title, content, published, updated, blog, section from posts p left outer join (select * from post_parameters where parameter = 'title') pp on p.path = pp.path);
  66. insert into posts_fts(posts_fts) values ('rebuild');
  67. `)
  68. return err
  69. },
  70. },
  71. &migrator.Migration{
  72. Name: "00006",
  73. Func: func(tx *sql.Tx) error {
  74. _, err := tx.Exec(`
  75. create table indieauthauthnew (time text not null, code text not null, client text not null, redirect text not null, scope text not null);
  76. insert into indieauthauthnew (time, code, client, redirect, scope) select time, code, client, redirect, scope from indieauthauth;
  77. drop table indieauthauth;
  78. alter table indieauthauthnew rename to indieauthauth;
  79. create table indieauthtokennew (time text not null, token text not null, client text not null, scope text not null);
  80. insert into indieauthtokennew (time, token, client, scope) select time, token, client, scope from indieauthtoken;
  81. drop table indieauthtoken;
  82. alter table indieauthtokennew rename to indieauthtoken;
  83. `)
  84. return err
  85. },
  86. },
  87. &migrator.Migration{
  88. Name: "00007",
  89. Func: func(tx *sql.Tx) error {
  90. // Change all dates to local
  91. _, err := tx.Exec(`
  92. update posts set published = tolocal(published), updated = tolocal(updated);
  93. `)
  94. return err
  95. },
  96. },
  97. &migrator.Migration{
  98. Name: "00008",
  99. Func: func(tx *sql.Tx) error {
  100. _, err := tx.Exec(`
  101. create table shortpath (id integer primary key autoincrement, path text not null, unique(path));
  102. `)
  103. return err
  104. },
  105. },
  106. &migrator.Migration{
  107. Name: "00009",
  108. Func: func(tx *sql.Tx) error {
  109. _, err := tx.Exec(`
  110. alter table posts add status text;
  111. update posts set status = 'published';
  112. drop view view_posts_with_title;
  113. drop table posts_fts;
  114. create view view_posts_with_title as select id, path, title, content, published, updated, blog, section, status from (select p.rowid as id, p.path as path, pp.value as title, content, published, updated, blog, section, status from posts p left outer join post_parameters pp on p.path = pp.path where pp.parameter = 'title');
  115. create virtual table posts_fts using fts5(path unindexed, title, content, published unindexed, updated unindexed, blog unindexed, section unindexed, status unindexed, content=view_posts_with_title, content_rowid=id);
  116. insert into posts_fts(posts_fts) values ('rebuild');
  117. `)
  118. return err
  119. },
  120. },
  121. &migrator.Migration{
  122. Name: "00010",
  123. Func: func(tx *sql.Tx) error {
  124. _, err := tx.Exec(`
  125. create table comments (id integer primary key autoincrement, target text not null, name text not null, website text not null, comment text not null);
  126. `)
  127. return err
  128. },
  129. },
  130. &migrator.Migration{
  131. Name: "00011",
  132. Func: func(tx *sql.Tx) error {
  133. _, err := tx.Exec(`
  134. create table notifications (id integer primary key autoincrement, time integer not null, text text not null);
  135. `)
  136. return err
  137. },
  138. },
  139. ),
  140. )
  141. if err != nil {
  142. return err
  143. }
  144. if err := m.Migrate(appDb); err != nil {
  145. return err
  146. }
  147. return nil
  148. }