Database Queries

These are the queries to replicate the creation of the SQLiggyBank database. Examples are included.

"users" CREATE TABLE Query

CREATE TABLE public.users
(
  uuid uuid DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
  username text NOT NULL,
  password text NOT NULL,
  first_name text DEFAULT NULL,
  last_name text DEFAULT NULL,
  email text DEFAULT NULL
);
CREATE UNIQUE INDEX users_uuid_uindex ON public.users (uuid);
CREATE UNIQUE INDEX users_username_uindex ON public.users (username);
CREATE UNIQUE INDEX users_lower_email_uindex ON public.users (lower(email)) WHERE email IS NOT NULL;
COMMENT ON TABLE public.users IS 'This is the table that contains all the users for SQLiggyBank.';

---

"users" INSERT Query

INSERT INTO users(username, password) VALUES (
'IonGlanetasu',
crypt('AmParolaMare', <function that generates salt>)
);

INSERT INTO users(username, password, first_name, last_name, email) VALUES (
'IonGlanetasu',
crpyt('AmParolaMare', <function that generates salt>)
'Ion',
'Glanetasu',
'ion@glanetasu.org');

---

"groups" CREATE TABLE Query

CREATE TABLE public.groups
(
    uuid uuid DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
    name text NOT NULL,
    description text DEFAULT NULL,
    owner_uid uuid NOT NULL,
    CONSTRAINT admin_key FOREIGN KEY (owner_uid) REFERENCES public.users (uuid)
);
CREATE UNIQUE INDEX groups_uuid_uindex ON public.groups (uuid);
CREATE UNIQUE INDEX groups_name_uindex ON public.groups (name);
COMMENT ON TABLE public.groups IS 'This is the table that contains all groups for SQLiggyBank. A group contains multiple users and can hold multiple banks.';

---

"groups" INSERT Query

INSERT INTO groups(name, owner_uid) VALUES (
'Test Group',
'OWNER_UUID'
);

INSERT INTO groups(name, description, owner_uid) VALUES (
'Test Group',
'This is the test group. No one is here, we're all alone',
'OWNER_UUID'
);

---

"group_lists" CREATE TABLE Query

CREATE TABLE public.group_lists
(
    id serial PRIMARY KEY NOT NULL,
    user_uuid uuid NOT NULL,
    group_uuid uuid NOT NULL,
    CONSTRAINT users_uuid FOREIGN KEY (user_uuid) REFERENCES public.users (uuid),
    CONSTRAINT groups_uuid FOREIGN KEY (group_uuid) REFERENCES public.groups (uuid)
);
CREATE UNIQUE INDEX group_lists_id_uindex ON public.group_lists (id);
COMMENT ON TABLE public.group_lists IS 'This is the table that contains all the group lists for SQLiggyBank. A group list contains a list of all members of a group.';

---

"group_lists" INSERT Query

INSERT into group_lists(user_uuid, group_uuid) VALUES (
'USER_UUID',
'GROUP_UUID'
);

---

"banks" CREATE TABLE Query

CREATE TABLE public.banks
(
    uuid uuid DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
    name text NOT NULL,
    description text DEFAULT NULL ,
    amount int DEFAULT 0 NOT NULL,
    group_uuid uuid NOT NULL,
    CONSTRAINT group_key FOREIGN KEY (group_uuid) REFERENCES public.groups (uuid)
);
CREATE UNIQUE INDEX banks_uuid_uindex ON public.banks (uuid);
CREATE UNIQUE INDEX banks_name_uindex ON public.banks (name);
COMMENT ON TABLE public.banks IS 'This is the table that contains all the banks for SQLiggyBank. A bank is a place where members of a group can gather money for whatever purpose. This money is accounted for by transactions(either deposits or withdrawals).';

---

"banks" INSERT Query

INSERT INTO banks(name, group_uuid) VALUES(
'Test Piggy Bank',
'GROUP_UUID'
);

INSERT INTO banks(name, description, amount, group_uuid) VALUES(
'Test Piggy Bank',
'This is a description for the piggy bank. Get some money in here!',
178,
'GROUP_UUID'
);

---

"deposits" CREATE TABLE Query

CREATE TABLE public.deposits
(
  uuid uuid DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
  amount int DEFAULT 0 NOT NULL,
  bank_uuid uuid NOT NULL,
  payee_uuid uuid NOT NULL,
  timestamp TIMESTAMP DEFAULT now(),
  description text DEFAULT NULL,
  tags text[] DEFAULT NULL,
  CONSTRAINT bank_key FOREIGN KEY (bank_uuid) REFERENCES public.banks (uuid),
  CONSTRAINT payee_key FOREIGN KEY (payee_uuid) REFERENCES public.users (uuid)
);
CREATE UNIQUE INDEX deposits_uuid_uindex ON public.deposits (uuid);
COMMENT ON TABLE public.deposits IS 'This is the table that contains all the deposits for SQLiggyBank. A deposit is a transaction to insert money in a bank by anyone.';

---

"deposits" INSERT Query

INSERT INTO deposits(bank_uuid, payee_uuid, amount) VALUES(
'BANK_UUID',
'PAYEE_UUID',
12
)

INSERT INTO deposits(bank_uuid, payee_uuid, amount, description, tags) VALUES (
'BANK_UUID',
'PAYEE_UUID',
12,
'This is a transaction description, enjoy!',
'fun, transaction, test, useless, ihavenolife'
);

---

"withdrawals" CREATE TABLE Query

CREATE TABLE public.withdrawals
(
  uuid uuid DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
  amount int DEFAULT 0 NOT NULL,
  bank_uuid uuid NOT NULL,
  timestamp TIMESTAMP DEFAULT now(),
  description text DEFAULT NULL,
  tags text[] DEFAULT NULL,
  CONSTRAINT bank_key FOREIGN KEY (bank_uuid) REFERENCES public.banks (uuid)
);
CREATE UNIQUE INDEX withdrawals_uuid_uindex ON public.withdrawals (uuid);
COMMENT ON TABLE public.withdrawals IS 'This is the table that contains all the withdrawals for SQLiggyBank. A withdrawal is a transaction to remove money from a bank by the group administrator.';

---

"withdrawals" INSERT Query

INSERT INTO withdrawals(bank_uuid, amount, description, tags) VALUES (
'BANK_UUID',
12,
'This is a transaction description, enjoy!',
'fun, transaction, test, useless, ihavenolife'
);

---

"goals" CREATE TABLE Query

CREATE TABLE public.goals
(
    uuid uuid DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
    name text NOT NULL,
    description text DEFAULT NULL,
    target_amount int DEFAULT 0 NOT NULL,
    deadline timestamp DEFAULT now() + interval '1 week' NOT NULL,
    bank_uuid uuid NOT NULL,
    CONSTRAINT bank_key FOREIGN KEY (bank_uuid) REFERENCES public.banks (uuid)
);
CREATE UNIQUE INDEX goals_uuid_index ON public.goals (uuid);
COMMENT ON TABLE public.goals IS 'This is the table that contains all the goals for SQLiggyBank. A goal is an amount of money designated for a bank to have by some deadline.';

---

"goals" INSERT Query

INSERT INTO goals(name, description, target_amount, bank_uuid) VALUES(
'Test Goal',
'This is a test goal. Let us try to get 100 bucks in the bank!',
100,
'BANK_UUID'
);