summaryrefslogtreecommitdiff
path: root/sql/ddl/pgsql.sql
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--sql/ddl/pgsql.sql51
1 files changed, 51 insertions, 0 deletions
diff --git a/sql/ddl/pgsql.sql b/sql/ddl/pgsql.sql
new file mode 100644
index 0000000..18c0661
--- /dev/null
+++ b/sql/ddl/pgsql.sql
@@ -0,0 +1,51 @@
+create table users
+(
+ id serial primary key,
+ name text not null,
+ pwd text not null,
+ description text
+);
+
+create unique index users_name ON users(name);
+
+create table categories
+(
+ id serial primary key,
+ name text not null,
+ parent_id integer references categories,
+ user_id integer not null references users
+);
+
+create index parent_id
+ on categories (parent_id);
+
+create index user_id
+ on categories (user_id);
+
+
+create table const_expenses
+(
+ id serial primary key,
+ description text,
+ expense numeric(10, 2) not null,
+ months smallint not null,
+ start date not null,
+ "end" date not null,
+ prev_id integer references const_expenses,
+ category_id integer not null references categories,
+ user_id integer not null references users
+);
+
+create table single_expenses
+(
+ id bigserial primary key,
+ description text,
+ expense numeric(10, 2) not null,
+ year smallint not null,
+ month smallint not null,
+ day smallint not null,
+ category_id integer not null references categories,
+ user_id integer not null references users
+);
+
+CREATE INDEX idx_single_date ON single_expenses(user_id, year, month); \ No newline at end of file