76 lines
1.7 KiB
Go
76 lines
1.7 KiB
Go
package api
|
|
|
|
import (
|
|
"log"
|
|
"strings"
|
|
)
|
|
|
|
var initSQLString string = `create table users(
|
|
id serial primary key,
|
|
username varchar(30) not null unique,
|
|
password varchar(64) not null,
|
|
balance money not null default 0,
|
|
role integer not null default 1,
|
|
location point not null default '(0, 0)',
|
|
registerd_time timestamp not null default now()
|
|
);
|
|
|
|
create table market (
|
|
id serial primary key,
|
|
name varchar(100) not null,
|
|
description text not null,
|
|
location point not null
|
|
)
|
|
|
|
create table tag (
|
|
id serial primary key,
|
|
name varchar(30) not null
|
|
);
|
|
|
|
create table goods (
|
|
id serial primary key,
|
|
name varchar(100) not null,
|
|
description text not null,
|
|
create_time timestamp not null default now(),
|
|
supplier_id integer not null references users(id),
|
|
market_id integer not null references market(id),
|
|
quantity numeric not null check (quantity >= 0),
|
|
price money not null,
|
|
data jsonb
|
|
);
|
|
|
|
create table tags_on_goods(
|
|
tag_id integer not null references tag(id),
|
|
goods_id integer not null references goods(id),
|
|
primary key (tag_id, goods_id)
|
|
);
|
|
|
|
create table purchase (
|
|
id serial primary key,
|
|
user_id integer not null references users(id),
|
|
goods_id integer not null references goods(id),
|
|
quantity numeric not null default 1,
|
|
purchased_time timestamp not null default now()
|
|
);
|
|
|
|
insert into users (username, password) values ('a', 'a');
|
|
`
|
|
|
|
func install() {
|
|
sqls := strings.Split(initSQLString, "\n\n")
|
|
tx, err := db.Begin()
|
|
if err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
for _, sql := range sqls {
|
|
log.Println("Installing table with SQL", sql)
|
|
_, err = tx.Exec(sql)
|
|
if err != nil {
|
|
tx.Rollback()
|
|
log.Fatal(err)
|
|
}
|
|
}
|
|
tx.Commit()
|
|
log.Println("Successfully install all tables")
|
|
}
|