202407151352_autoincrement.go raw

   1  package migrations
   2  
   3  import (
   4  	_ "embed"
   5  	"text/template"
   6  
   7  	"github.com/go-gormigrate/gormigrate/v2"
   8  	"gorm.io/gorm"
   9  )
  10  
  11  const dropMigration = `
  12  DROP TABLE request_events {{ .DropTableCascade }};
  13  DROP TABLE response_events;
  14  `
  15  
  16  var dropMigrationTmpl = template.Must(template.New("dropMigration").Parse(dropMigration))
  17  
  18  const appsMigration = `
  19  DELETE FROM app_permissions WHERE app_id NOT IN (SELECT id FROM apps);
  20  CREATE TABLE apps_2 (id {{ .AutoincrementPrimaryKey }},name text,description text,nostr_pubkey text UNIQUE,created_at {{ .Timestamp }},updated_at {{ .Timestamp }}, isolated boolean);
  21  INSERT INTO apps_2 (id, name, description, nostr_pubkey, created_at, updated_at, isolated) SELECT id, name text, description, nostr_pubkey, created_at, updated_at, isolated FROM apps;
  22  CREATE TABLE app_permissions_2 (id {{ .AutoincrementPrimaryKey }},app_id integer,"scope" text,"max_amount_sat" integer,budget_renewal text,expires_at {{ .Timestamp }},created_at {{ .Timestamp }},updated_at {{ .Timestamp }},CONSTRAINT fk_app_permissions_app FOREIGN KEY (app_id) REFERENCES apps_2(id) ON DELETE CASCADE);
  23  INSERT INTO app_permissions_2 (id, app_id, scope, max_amount_sat, budget_renewal, expires_at, created_at, updated_at) SELECT id, app_id, scope, max_amount_sat, budget_renewal, expires_at, created_at, updated_at FROM app_permissions;
  24  
  25  DROP TABLE apps {{ .DropTableCascade }};
  26  ALTER TABLE apps_2 RENAME TO apps;
  27  DROP TABLE app_permissions;
  28  ALTER TABLE app_permissions_2 RENAME TO app_permissions;
  29  
  30  CREATE INDEX idx_app_permissions_scope ON app_permissions("scope");
  31  CREATE INDEX idx_app_permissions_app_id ON app_permissions(app_id);
  32  `
  33  
  34  var appsMigrationTmpl = template.Must(template.New("appsMigration").Parse(appsMigration))
  35  
  36  const reqRespMigration = `
  37  CREATE TABLE "request_events" (id {{ .AutoincrementPrimaryKey }},app_id integer,nostr_id text UNIQUE,state text,created_at {{ .Timestamp }},updated_at {{ .Timestamp }}, method TEXT, content_data TEXT,CONSTRAINT fk_request_events_app FOREIGN KEY (app_id) REFERENCES apps(id) ON DELETE CASCADE);
  38  CREATE INDEX idx_request_events_app_id ON request_events(app_id);
  39  CREATE INDEX idx_request_events_app_id_and_id ON request_events(app_id, id);
  40  CREATE INDEX idx_request_events_method ON request_events(method);
  41  CREATE TABLE "response_events" (id {{ .AutoincrementPrimaryKey }},nostr_id text UNIQUE,request_id integer,state text,replied_at {{ .Timestamp }},created_at {{ .Timestamp }},updated_at {{ .Timestamp }},CONSTRAINT fk_response_events_request_event FOREIGN KEY (request_id) REFERENCES request_events(id) ON DELETE CASCADE);
  42  `
  43  
  44  var reqRespMigrationTmpl = template.Must(template.New("reqRespMigration").Parse(reqRespMigration))
  45  
  46  // This migration (inside a DB transaction),
  47  // - Adds AUTOINCREMENT to the primary key of:
  48  // - apps, app_permissions, request_events, response_events
  49  //
  50  // user_configs is not migrated as it has no relations with other tables, therefore hopefully no issue with reusing IDs
  51  //
  52  // request_events and response_events are not critical (and also payments are dropped in the same release)
  53  // so we just drop those tables and re-create them.
  54  var _202407151352_autoincrement = &gormigrate.Migration{
  55  	ID: "202407151352_autoincrement",
  56  	Migrate: func(db *gorm.DB) error {
  57  
  58  		if err := db.Transaction(func(tx *gorm.DB) error {
  59  
  60  			// drop old request and response event tables
  61  			if err := exec(tx, dropMigrationTmpl); err != nil {
  62  				return err
  63  			}
  64  
  65  			// Apps & app permissions (interdependent)
  66  			// create new tables, copy old values, delete old tables, rename new tables, create new indexes
  67  			// also deletes broken app permissions no longer linked to apps (from reused app IDs)
  68  			if err := exec(tx, appsMigrationTmpl); err != nil {
  69  				return err
  70  			}
  71  
  72  			// create fresh request and response event tables
  73  			if err := exec(tx, reqRespMigrationTmpl); err != nil {
  74  				return err
  75  			}
  76  
  77  			return nil
  78  		}); err != nil {
  79  			return err
  80  		}
  81  
  82  		return nil
  83  	},
  84  	Rollback: func(tx *gorm.DB) error {
  85  		return nil
  86  	},
  87  }
  88