From b9200623b0be3c8b663c1000b63c3ab581b7d34b Mon Sep 17 00:00:00 2001 From: Bryan Newbold Date: Thu, 10 May 2018 10:34:47 -0700 Subject: basic database schema reload --- golang/Gopkg.lock | 8 +++++- golang/cmd/fatcatd/reinit.go | 42 +++++++++++++++++++++++++++ golang/cmd/fatcatd/root.go | 2 +- golang/fatcat-schema.sql | 68 -------------------------------------------- golang/fatcatd.toml | 1 + golang/sql/fatcat-schema.sql | 68 ++++++++++++++++++++++++++++++++++++++++++++ 6 files changed, 119 insertions(+), 70 deletions(-) create mode 100644 golang/cmd/fatcatd/reinit.go delete mode 100644 golang/fatcat-schema.sql create mode 100644 golang/sql/fatcat-schema.sql (limited to 'golang') diff --git a/golang/Gopkg.lock b/golang/Gopkg.lock index 624973cf..eedeab82 100644 --- a/golang/Gopkg.lock +++ b/golang/Gopkg.lock @@ -158,6 +158,12 @@ revision = "5b73ce88484575f3480edf393237f6bf79d5f166" version = "v6.11.2" +[[projects]] + name = "github.com/gobuffalo/packr" + packages = ["."] + revision = "7f4074995d431987caaa35088199f13c44b24440" + version = "v1.11.0" + [[projects]] branch = "master" name = "github.com/hashicorp/hcl" @@ -366,6 +372,6 @@ [solve-meta] analyzer-name = "dep" analyzer-version = 1 - inputs-digest = "6f07904be5d2765e24cd25378e28c8c1c34f6e4205100dc6f8364d0be5eafbf9" + inputs-digest = "551e8d9bd6a848cc1a77d0706bb2275d4ff4052d83c7e2a82494ee2ee8821805" solver-name = "gps-cdcl" solver-version = 1 diff --git a/golang/cmd/fatcatd/reinit.go b/golang/cmd/fatcatd/reinit.go new file mode 100644 index 00000000..b7cfa468 --- /dev/null +++ b/golang/cmd/fatcatd/reinit.go @@ -0,0 +1,42 @@ + +package main + +import ( + "github.com/spf13/viper" + log "github.com/sirupsen/logrus" + "github.com/go-pg/pg" + "github.com/spf13/cobra" + "github.com/gobuffalo/packr" +) + +var reinitCmd = &cobra.Command{ + Use: "reinit [options]", + Short: "Reset database schema", + Run: func(cmd *cobra.Command, args[] string) { + main_reinit() + }, +} + +func main_reinit() { + + box := packr.NewBox("../../sql") + sql_schema, err := box.MustString("fatcat-schema.sql") + if err != nil { + log.Panicf("finding SQL file: {}", err) + } + + db_options, err := pg.ParseURL(viper.GetString("db_url")) + if err != nil { + log.Panicf("parsing DB string: {}", err) + } + db := pg.Connect(db_options) + defer db.Close() + + log.Info("Starting load...") + _, err = db.Exec(sql_schema) + if err != nil { + log.Fatalf("Error loading SQL: {}", err) + } + log.Info("Success!") + +} diff --git a/golang/cmd/fatcatd/root.go b/golang/cmd/fatcatd/root.go index b5263b06..12be35b3 100644 --- a/golang/cmd/fatcatd/root.go +++ b/golang/cmd/fatcatd/root.go @@ -29,12 +29,12 @@ func init() { serveCmd.Flags().String("db-url", "postgres://", "database connecion string") rootCmd.AddCommand(serveCmd) + rootCmd.AddCommand(reinitCmd) } func initConfig() { viper.SetDefault("port", 9411) - viper.SetDefault("db_url", "postgres://bnewbold@localhost/fatcat") viper.SetEnvPrefix("FATCAT") viper.AutomaticEnv() diff --git a/golang/fatcat-schema.sql b/golang/fatcat-schema.sql deleted file mode 100644 index f5983371..00000000 --- a/golang/fatcat-schema.sql +++ /dev/null @@ -1,68 +0,0 @@ - --- written for Postgres 9.6 with OSSP extension for UUIDs - -CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; - -DROP TABLE IF EXISTS editor CASCADE; -DROP TABLE IF EXISTS editgroup CASCADE; -DROP TABLE IF EXISTS changelog CASCADE; -DROP TABLE IF EXISTS creator_rev CASCADE; -DROP TABLE IF EXISTS creator_ident CASCADE; -DROP TABLE IF EXISTS creator_edit CASCADE; - --- uuid_generate_v1mc: timestamp ordered, random MAC address --- uuid_generate_v4: totally random - --- NB: could use LIKE clause, or "composite types" - -CREATE TABLE editor ( - id BIGSERIAL PRIMARY KEY, - username TEXT NOT NULL, - is_admin BOOLEAN NOT NULL DEFAULT false, - active_editgroup_id BIGINT -- REFERENCES( editgroup(id) via ALTER below -); - -CREATE TABLE editgroup ( - id BIGSERIAL PRIMARY KEY, - extra_json JSON, - editor_id BIGSERIAL REFERENCES editor(id) NOT NULL, - description TEXT -); - -ALTER TABLE editor - ADD CONSTRAINT editor_editgroupid_fkey FOREIGN KEY (active_editgroup_id) - REFERENCES editgroup(id); - -CREATE TABLE changelog ( - id BIGSERIAL PRIMARY KEY, - editgroup_id BIGINT REFERENCES editgroup(id) NOT NULL, - timestamp TIMESTAMP WITHOUT TIME ZONE DEFAULT now() -); - -CREATE TABLE creator_rev ( - id BIGSERIAL PRIMARY KEY, - extra_json JSON, - - name TEXT, - orcid TEXT -); - --- Could denormalize a "is_live" flag into revision tables, to make indices --- more efficient -CREATE INDEX creator_rev_orcid_idx ON creator_rev(orcid) WHERE orcid IS NOT NULL; - -CREATE TABLE creator_ident ( - id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), - is_live BOOL NOT NULL, - rev_id BIGINT REFERENCES creator_rev(id), - redirect_id UUID REFERENCES creator_ident(id) -); - -CREATE TABLE creator_edit ( - id BIGSERIAL PRIMARY KEY, - extra_json JSON, - ident_id UUID REFERENCES creator_ident(id) NOT NULL, - rev_id BIGINT REFERENCES creator_rev(id), - redirect_id UUID REFERENCES creator_ident(id), - editgroup_id BIGINT REFERENCES editgroup(id) NOT NULL -); diff --git a/golang/fatcatd.toml b/golang/fatcatd.toml index bd839b19..56316061 100644 --- a/golang/fatcatd.toml +++ b/golang/fatcatd.toml @@ -1 +1,2 @@ verbose = false +db_url = "postgres://fatcat:tactaf@localhost/fatcat" diff --git a/golang/sql/fatcat-schema.sql b/golang/sql/fatcat-schema.sql new file mode 100644 index 00000000..f5983371 --- /dev/null +++ b/golang/sql/fatcat-schema.sql @@ -0,0 +1,68 @@ + +-- written for Postgres 9.6 with OSSP extension for UUIDs + +CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; + +DROP TABLE IF EXISTS editor CASCADE; +DROP TABLE IF EXISTS editgroup CASCADE; +DROP TABLE IF EXISTS changelog CASCADE; +DROP TABLE IF EXISTS creator_rev CASCADE; +DROP TABLE IF EXISTS creator_ident CASCADE; +DROP TABLE IF EXISTS creator_edit CASCADE; + +-- uuid_generate_v1mc: timestamp ordered, random MAC address +-- uuid_generate_v4: totally random + +-- NB: could use LIKE clause, or "composite types" + +CREATE TABLE editor ( + id BIGSERIAL PRIMARY KEY, + username TEXT NOT NULL, + is_admin BOOLEAN NOT NULL DEFAULT false, + active_editgroup_id BIGINT -- REFERENCES( editgroup(id) via ALTER below +); + +CREATE TABLE editgroup ( + id BIGSERIAL PRIMARY KEY, + extra_json JSON, + editor_id BIGSERIAL REFERENCES editor(id) NOT NULL, + description TEXT +); + +ALTER TABLE editor + ADD CONSTRAINT editor_editgroupid_fkey FOREIGN KEY (active_editgroup_id) + REFERENCES editgroup(id); + +CREATE TABLE changelog ( + id BIGSERIAL PRIMARY KEY, + editgroup_id BIGINT REFERENCES editgroup(id) NOT NULL, + timestamp TIMESTAMP WITHOUT TIME ZONE DEFAULT now() +); + +CREATE TABLE creator_rev ( + id BIGSERIAL PRIMARY KEY, + extra_json JSON, + + name TEXT, + orcid TEXT +); + +-- Could denormalize a "is_live" flag into revision tables, to make indices +-- more efficient +CREATE INDEX creator_rev_orcid_idx ON creator_rev(orcid) WHERE orcid IS NOT NULL; + +CREATE TABLE creator_ident ( + id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), + is_live BOOL NOT NULL, + rev_id BIGINT REFERENCES creator_rev(id), + redirect_id UUID REFERENCES creator_ident(id) +); + +CREATE TABLE creator_edit ( + id BIGSERIAL PRIMARY KEY, + extra_json JSON, + ident_id UUID REFERENCES creator_ident(id) NOT NULL, + rev_id BIGINT REFERENCES creator_rev(id), + redirect_id UUID REFERENCES creator_ident(id), + editgroup_id BIGINT REFERENCES editgroup(id) NOT NULL +); -- cgit v1.2.3