grim/devweb

Clean up the database schema a bit
draft
2020-11-13, Gary Kramlich
454462e98cfc
Parents bacc8aeab2fe
Children 55f3ba42d58d
Clean up the database schema a bit
7 files changed, 37 insertions(+), 32 deletions(-)
  • +1 -2
    Makefile
  • +8 -5
    access/create.go
  • +6 -5
    db/apikey.go
  • +7 -7
    db/book.go
  • +8 -0
    db/bookversion.go
  • +7 -6
    db/scheme/01-initial.sql
  • +0 -7
    db/types.go
    • --- a/Makefile Fri Nov 13 23:04:28 2020 -0600
    +++ b/Makefile Fri Nov 13 23:36:08 2020 -0600
    @@ -2,8 +2,7 @@
    CLEAN_FILES := \
    db/scheme/embedded.go \
    - devweb \
    - frontend/embedded.go
    + devweb
    CLEAN_DIRS := \
    frontend/dist
    --- a/access/create.go Fri Nov 13 23:04:28 2020 -0600
    +++ b/access/create.go Fri Nov 13 23:36:08 2020 -0600
    @@ -11,6 +11,8 @@
    type CreateCmd struct {
    db.Options
    +
    + Description string `kong:"flag,name='description',help='Descriptive text for the key'"`
    }
    func (c *CreateCmd) newToken() (string, error) {
    @@ -43,8 +45,9 @@
    }
    apiKey := db.APIKey{
    - AccessKey: accessKey,
    - SecretKey: secretKey,
    + AccessKey: accessKey,
    + SecretKey: secretKey,
    + Description: c.Description,
    }
    if err := apiKey.Create(); err != nil {
    @@ -52,9 +55,9 @@
    return err
    }
    - fmt.Printf("New API Key created:\n")
    - fmt.Printf("Access Key: %s\n", accessKey)
    - fmt.Printf("Secret Key: %s\n", secretKey)
    + fmt.Printf("Access Key: %s\n", accessKey)
    + fmt.Printf("Secret Key: %s\n", secretKey)
    + fmt.Printf("Description: %s\n", c.Description)
    return db.Teardown()
    }
    --- a/db/apikey.go Fri Nov 13 23:04:28 2020 -0600
    +++ b/db/apikey.go Fri Nov 13 23:36:08 2020 -0600
    @@ -1,8 +1,9 @@
    package db
    type APIKey struct {
    - AccessKey string `db:"access_key"`
    - SecretKey string `db:"secret_key"`
    + AccessKey string `db:"access_key"`
    + SecretKey string `db:"secret_key"`
    + Description string `db:"description"`
    }
    func FindAPIKey(accessKey string) (APIKey, error) {
    @@ -15,15 +16,15 @@
    }
    func (a APIKey) Create() error {
    - query := `INSERT INTO api_keys(access_key, secret_key) VALUES($1, $2);`
    + query := `INSERT INTO api_keys(access_key, secret_key, description) VALUES($1, $2, $3);`
    - _, err := _db.Exec(query, a.AccessKey, a.SecretKey)
    + _, err := _db.Exec(query, a.AccessKey, a.SecretKey, a.Description)
    return err
    }
    func (a APIKey) Delete() error {
    - query := `DELETE FROM api_keys WHERE access_key = $1;`
    + query := `DELETE FROM api_keys WHERE access_key=$1;`
    _, err := _db.Exec(query, a.AccessKey)
    --- a/db/book.go Fri Nov 13 23:04:28 2020 -0600
    +++ b/db/book.go Fri Nov 13 23:36:08 2020 -0600
    @@ -2,28 +2,28 @@
    type Book struct {
    ID string `db:"id" json:"id" form:"id" validate:"max='32',min='1'"`
    - Name string `db:"name" json:"name" form:"name" validate:"max='32',min='1'"`
    + DisplayName string `db:"display_name" json:"displayName" form:"displayName" validate:"max='32',min='1'"`
    DefaultVersion string `db:"default_version" json:"defaultVersion" form:"defaultVersion"`
    }
    func (b Book) Create() error {
    - query := `INSERT INTO BOOKS(id, name, default_version) VALUES($1, $2, $3);`
    + query := `INSERT INTO books(id, display_name, default_version) VALUES($1, $2, $3)`
    - _, err := _db.Exec(query, b.ID, b.Name, b.DefaultVersion)
    + _, err := _db.Exec(query, b.ID, b.DisplayName, b.DefaultVersion)
    return err
    }
    func (b Book) Update() error {
    - query := `UPDATE book SET name= $1, default_version= $2 WHERE id = $3;`
    + query := `UPDATE books SET display_name=$1, default_version=$2 WHERE id=$3`
    - _, err := _db.Exec(query, b.Name, b.DefaultVersion, b.ID)
    + _, err := _db.Exec(query, b.DisplayName, b.DefaultVersion, b.ID)
    return err
    }
    func (b Book) Delete() error {
    - query := `DELETE FROM book WHERE id = $1;`
    + query := `DELETE FROM books WHERE id=$1`
    _, err := _db.Exec(query, b.ID)
    @@ -33,7 +33,7 @@
    func ListBooks() ([]Book, error) {
    books := []Book{}
    - query := `SELECT * FROM books ORDER BY name DESC`
    + query := `SELECT * FROM books ORDER BY name ASC`
    if err := _db.Select(&books, query); err != nil {
    return nil, err
    }
    --- /dev/null Thu Jan 01 00:00:00 1970 +0000
    +++ b/db/bookversion.go Fri Nov 13 23:36:08 2020 -0600
    @@ -0,0 +1,8 @@
    +package db
    +
    +type BookVersion struct {
    + BookID string `db:"book_id" json:"book" form:"book" validate:"max='32',min='1'"`
    + Type string `db:"type" json:"type" form:"type" validate:"max='32',min='1'"`
    + Version string `db:"version" json:"version" form:"version" validate:"max='32',min='1'"`
    + CreatorID string `db:"creator_id" json:"creator" form:"creator"`
    +}
    --- a/db/scheme/01-initial.sql Fri Nov 13 23:04:28 2020 -0600
    +++ b/db/scheme/01-initial.sql Fri Nov 13 23:36:08 2020 -0600
    @@ -1,18 +1,19 @@
    CREATE TABLE api_keys (
    access_key varchar(64) UNIQUE PRIMARY KEY,
    - secret_key varchar(64) NOT NULL
    + secret_key varchar(64) NOT NULL,
    + description varchar(128)
    );
    CREATE TABLE books (
    id varchar(32) UNIQUE PRIMARY KEY,
    - name varchar(32) NOT NULL,
    + display_name varchar(32) NOT NULL,
    default_version varchar(32) NOT NULL
    );
    -CREATE TABLE book_version (
    - book varchar(32) NOT NULL,
    +CREATE TABLE book_versions (
    + book_id varchar(32) NOT NULL,
    type varchar(32) NOT NULL,
    version varchar(32) NOT NULL,
    - PRIMARY KEY (book, version),
    - UNIQUE (book, version)
    + api_key_access_key varchar(64),
    + PRIMARY KEY (book_id, version)
    );
    --- a/db/types.go Fri Nov 13 23:04:28 2020 -0600
    +++ /dev/null Thu Jan 01 00:00:00 1970 +0000
    @@ -1,7 +0,0 @@
    -package db
    -
    -type BookVersion struct {
    - Book string `db:"book" json:"book" form:"book" validate:"max='32',min='1'"`
    - Type string `db:"type" json:"type" form:"type"`
    - Version string `db:"version" json:"version" form:"version" validate:"max='32',min='1'"`
    -}