Skip to content

Commit d372770

Browse files
saskenubasaskenuba
committed
feat: added initial version of scripts and schema
Signed-off-by: Martin <martin@hotmail.com.br>
1 parent 093f87b commit d372770

File tree

3 files changed

+172
-0
lines changed

3 files changed

+172
-0
lines changed

‎migrations/initial_insert.sql‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
INSERT INTO account (id)
2+
VALUES (1), (2), (3), (4), (5);
3+
4+
INSERT INTO transaction (amount, description, account_id)
5+
VALUES (100000, 'Initial balance for account 1', 1)
6+
, (80000, 'Initial balance for account 2', 2)
7+
, (1000000, 'Initial balance for account 3', 3)
8+
, (10000000, 'Initial balance for account 4', 4)
9+
, (500000, 'Initial balance for account 5', 5)

‎migrations/initial_model.sql‎

Lines changed: 61 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,61 @@
1+
-- Database generated with pgModeler (PostgreSQL Database Modeler).
2+
-- pgModeler version: 1.0.6
3+
-- PostgreSQL version: 16.0
4+
-- Project Site: pgmodeler.io
5+
-- Model Author: ---
6+
-- Tablespaces creation must be performed outside a multi lined SQL file.
7+
-- These commands were put in this file only as a convenience.
8+
--
9+
-- object: "Account" | type: TABLESPACE --
10+
-- DROP TABLESPACE IF EXISTS "Account" CASCADE;
11+
CREATE TABLESPACE "Account"
12+
OWNER postgres
13+
LOCATION 'hehe';
14+
15+
-- ddl-end --
16+
17+
18+
19+
-- Database creation must be performed outside a multi lined SQL file.
20+
-- These commands were put in this file only as a convenience.
21+
--
22+
-- object: rinhabackend | type: DATABASE --
23+
-- DROP DATABASE IF EXISTS rinhabackend;
24+
CREATE DATABASE rinhabackend
25+
ENCODING = 'UTF8';
26+
-- ddl-end --
27+
28+
29+
-- object: public.account | type: TABLE --
30+
-- DROP TABLE IF EXISTS public.account CASCADE;
31+
CREATE UNLOGGED TABLE public.account (
32+
id integer NOT NULL,
33+
CONSTRAINT "Account_pk" PRIMARY KEY (id)
34+
);
35+
-- ddl-end --
36+
ALTER TABLE public.account OWNER TO postgres;
37+
-- ddl-end --
38+
39+
-- object: public.transaction | type: TABLE --
40+
-- DROP TABLE IF EXISTS public.transaction CASCADE;
41+
CREATE UNLOGGED TABLE public.transaction (
42+
amount integer NOT NULL,
43+
description text NOT NULL,
44+
created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
45+
account_id integer NOT NULL,
46+
CONSTRAINT transaction_pk PRIMARY KEY (account_id)
47+
);
48+
-- ddl-end --
49+
COMMENT ON COLUMN public.transaction.amount IS E'Transaction amount';
50+
-- ddl-end --
51+
ALTER TABLE public.transaction OWNER TO postgres;
52+
-- ddl-end --
53+
54+
-- object: account_fk | type: CONSTRAINT --
55+
-- ALTER TABLE public.transaction DROP CONSTRAINT IF EXISTS account_fk CASCADE;
56+
ALTER TABLE public.transaction ADD CONSTRAINT account_fk FOREIGN KEY (account_id)
57+
REFERENCES public.account (id) MATCH FULL
58+
ON DELETE CASCADE ON UPDATE CASCADE;
59+
-- ddl-end --
60+
61+

‎schema.dbm‎

Lines changed: 102 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,102 @@
1+
<?xml version="1.0" encoding="UTF-8"?>
2+
<!--
3+
CAUTION: Do not modify this file unless you know what you are doing.
4+
Unexpected results may occur if the code is changed deliberately.
5+
-->
6+
<dbmodel pgmodeler-ver="1.0.6" use-changelog="false" last-position="52,0" last-zoom="0.9" max-obj-count="4"
7+
default-schema="public" default-owner="postgres"
8+
layers="Default layer"
9+
active-layers="0"
10+
layer-name-colors="#000000"
11+
layer-rect-colors="#b4b4b4"
12+
show-layer-names="false" show-layer-rects="false">
13+
<database name="rinhabackend" encoding="UTF8" is-template="false" allow-conns="true">
14+
</database>
15+
16+
<schema name="public" layers="0" fill-color="#e1e1e1" sql-disabled="true">
17+
</schema>
18+
19+
<table name="account" layers="0" unlogged="true" collapse-mode="2" pagination="true" attribs-page="0" ext-attribs-page="0" max-obj-count="1" z-value="0">
20+
<schema name="public"/>
21+
<role name="postgres"/>
22+
<position x="340" y="160"/>
23+
<column name="id" not-null="true">
24+
<type name="integer" length="0"/>
25+
</column>
26+
<constraint name="Account_pk" type="pk-constr" table="public.account">
27+
<columns names="id" ref-type="src-columns"/>
28+
</constraint>
29+
</table>
30+
31+
<table name="transaction" layers="0" unlogged="true" collapse-mode="2" max-obj-count="4" z-value="0">
32+
<schema name="public"/>
33+
<role name="postgres"/>
34+
<position x="160" y="400"/>
35+
<column name="amount" not-null="true">
36+
<type name="integer" length="0"/>
37+
<comment> <![CDATA[Transaction amount]]> </comment>
38+
</column>
39+
<column name="description" not-null="true">
40+
<type name="text" length="0"/>
41+
</column>
42+
<column name="created_on" not-null="true" default-value="CURRENT_TIMESTAMP">
43+
<type name="timestamp" length="0"/>
44+
</column>
45+
46+
<customidxs object-type="column">
47+
<object name="account_id" index="3"/>
48+
</customidxs>
49+
50+
<customidxs object-type="constraint">
51+
<object name="account_fk" index="0"/>
52+
</customidxs>
53+
</table>
54+
55+
<relationship name="account_has_many_transaction" type="rel1n" layers="0"
56+
src-col-pattern="{st}_{sc}"
57+
pk-pattern="{dt}_pk" uq-pattern="{dt}_uq"
58+
src-fk-pattern="{st}_fk"
59+
custom-color="#8d5216"
60+
src-table="public.account"
61+
dst-table="public.transaction"
62+
src-required="false" dst-required="false"
63+
upd-action="CASCADE"
64+
del-action="CASCADE"/>
65+
66+
<index name="idx_t_user" table="public.transaction"
67+
concurrent="false" unique="false" fast-update="false" buffering="false"
68+
index-type="hash" factor="0">
69+
<idxelement use-sorting="false">
70+
<column name="account_id"/>
71+
</idxelement>
72+
</index>
73+
74+
<index name="idx_t_date" table="public.transaction"
75+
concurrent="false" unique="false" fast-update="false" buffering="false"
76+
index-type="btree" factor="0">
77+
<idxelement use-sorting="false">
78+
<column name="created_on"/>
79+
</idxelement>
80+
</index>
81+
82+
<view name="running_balance" layers="0" collapse-mode="2" max-obj-count="3" z-value="0">
83+
<schema name="public"/>
84+
<role name="postgres"/>
85+
<position x="594.444" y="357.778"/>
86+
<reference>
87+
<expression> <![CDATA[t.account_id AS account_id
88+
, sum(t.amount) AS balance]]> </expression>
89+
</reference>
90+
<reference>
91+
<expression> <![CDATA[transaction t
92+
INNER JOIN public.account acc ON acc.id = t.account_id]]> </expression>
93+
</reference>
94+
<reference>
95+
<expression> <![CDATA[account_id]]> </expression>
96+
</reference>
97+
<expression type="select-exp">0</expression>
98+
<expression type="from-exp">1</expression>
99+
<expression type="end-exp">2</expression>
100+
</view>
101+
102+
</dbmodel>

0 commit comments

Comments
 (0)