tables.go

package main

import "database/sql"

func makeTables(db *sql.DB) error {
	// Create the tables if they don't exist
	_, err := db.Exec(`CREATE TABLE IF NOT EXISTS files (
		id SERIAL PRIMARY KEY,
		user_id TEXT NOT NULL,
		file_name TEXT NOT NULL,
		size BIGINT,
		cid TEXT NOT NULL,
		root TEXT NOT NULL,
		active BOOLEAN DEFAULT TRUE,
		created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
	);`)
	if err != nil {
		panic(err)
	}

	_, err = db.Exec(`CREATE TABLE IF NOT EXISTS keys (
		name TEXT NOT NULL,
		user_id TEXT NOT NULL,
		created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY (user_id, name)
	);`)
	if err != nil {
		panic(err)
	}

	_, err = db.Exec(`CREATE TABLE IF NOT EXISTS customers (
		stripe_id TEXT NOT NULL,
		user_id TEXT NOT NULL,
		PRIMARY KEY (user_id)
	);`)
	if err != nil {
		panic(err)
	}

	_, err = db.Exec(`CREATE TABLE IF NOT EXISTS subscriptions (
		sub_id TEXT NOT NULL PRIMARY KEY,
    	quantity BIGINT,
		stripe_id TEXT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
	);`)
	if err != nil {
		panic(err)
	}

	_, err = db.Exec(`CREATE TABLE IF NOT EXISTS usermap (
		user_id TEXT NOT NULL PRIMARY KEY,
    	id TEXT NOT NULL
	);`)
	if err != nil {
		panic(err)
	}

	_, err = db.Exec(`CREATE TABLE IF NOT EXISTS collections (
		id SERIAL PRIMARY KEY,
    	collection_name TEXT NOT NULL UNIQUE,
    	cid TEXT,
		user_id TEXT NOT NULL
	);`)
	if err != nil {
		panic(err)
	}

	_, err = db.Exec(`CREATE TABLE IF NOT EXISTS collection_files (
    	collection_id INTEGER NOT NULL,
		file_id INTEGER NOT NULL,
		PRIMARY KEY (collection_id, file_id),
        FOREIGN KEY (collection_id) REFERENCES collections (id) ON DELETE CASCADE
	);`)
	if err != nil {
		panic(err)
	}

	_, err = db.Exec(`CREATE TABLE IF NOT EXISTS collection_refs (
    	collection_id INTEGER NOT NULL,
		ref_id INTEGER NOT NULL,
		PRIMARY KEY (collection_id, ref_id),
        FOREIGN KEY (collection_id) REFERENCES collections (id) ON DELETE CASCADE
	);`)
	if err != nil {
		panic(err)
	}

	return nil
}