145 lines
3.0 KiB
Go
145 lines
3.0 KiB
Go
package database
|
|
|
|
import (
|
|
"context"
|
|
|
|
"github.com/aldy505/bob"
|
|
"github.com/jackc/pgx/v4/pgxpool"
|
|
)
|
|
|
|
// Setup the table connection, create table if not exists
|
|
func Populate(db *pgxpool.Pool, ctx context.Context) error {
|
|
err := setupAuthTable(db, ctx)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
err = setupJokesTable(db, ctx)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
err = setupSubmissionTable(db, ctx)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
func setupAuthTable(db *pgxpool.Pool, ctx context.Context) error {
|
|
conn, err := db.Acquire(ctx)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer conn.Release()
|
|
|
|
// Check if table exists
|
|
var tableAuthExists bool
|
|
err = conn.QueryRow(ctx, `SELECT EXISTS (
|
|
SELECT FROM information_schema.tables
|
|
WHERE table_schema = 'public'
|
|
AND table_name = 'administrators'
|
|
);`).Scan(&tableAuthExists)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
if !tableAuthExists {
|
|
sql, _, err := bob.
|
|
CreateTable("administrators").
|
|
AddColumn(bob.ColumnDef{Name: "id", Type: "SERIAL", Extras: []string{"PRIMARY KEY"}}).
|
|
StringColumn("key", "NOT NULL", "UNIQUE").
|
|
TextColumn("token").
|
|
StringColumn("last_used").
|
|
ToSql()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
_, err = conn.Exec(ctx, sql)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func setupJokesTable(db *pgxpool.Pool, ctx context.Context) error {
|
|
conn, err := db.Acquire(ctx)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer conn.Release()
|
|
|
|
// Check if table exists
|
|
var tableJokesExists bool
|
|
err = conn.QueryRow(ctx, `SELECT EXISTS (
|
|
SELECT FROM information_schema.tables
|
|
WHERE table_schema = 'public'
|
|
AND table_name = 'jokesbapak2'
|
|
);`).Scan(&tableJokesExists)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
if !tableJokesExists {
|
|
sql, _, err := bob.
|
|
CreateTable("jokesbapak2").
|
|
AddColumn(bob.ColumnDef{Name: "id", Type: "SERIAL", Extras: []string{"PRIMARY KEY"}}).
|
|
TextColumn("link", "UNIQUE").
|
|
AddColumn(bob.ColumnDef{Name: "creator", Type: "INT", Extras: []string{"NOT NULL", "REFERENCES \"administrators\" (\"id\")"}}).
|
|
ToSql()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
_, err = conn.Exec(ctx, sql)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
func setupSubmissionTable(db *pgxpool.Pool, ctx context.Context) error {
|
|
conn, err := db.Acquire(ctx)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer conn.Release()
|
|
|
|
//Check if table exists
|
|
var tableSubmissionExists bool
|
|
err = conn.QueryRow(ctx, `SELECT EXISTS (
|
|
SELECT FROM information_schema.tables
|
|
WHERE table_schema = 'public'
|
|
AND table_name = 'submission'
|
|
);`).Scan(&tableSubmissionExists)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
if !tableSubmissionExists {
|
|
sql, _, err := bob.
|
|
CreateTable("submission").
|
|
AddColumn(bob.ColumnDef{Name: "id", Type: "SERIAL", Extras: []string{"PRIMARY KEY"}}).
|
|
TextColumn("link", "UNIQUE", "NOT NULL").
|
|
StringColumn("created_at").
|
|
StringColumn("author", "NOT NULL").
|
|
AddColumn(bob.ColumnDef{Name: "status", Type: "SMALLINT", Extras: []string{"DEFAULT 0"}}).
|
|
ToSql()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
_, err = conn.Exec(ctx, sql)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
}
|
|
|
|
return nil
|
|
}
|