Home - DolpFin Admin

🖥️ Run script by Sequence Number

🖥️♾️ Run all scripts

1 administrator__database_update table

1.table.administrator__database_update.sql

CREATE SCHEMA IF NOT EXISTS administrator;

CREATE TABLE IF NOT EXISTS administrator.database_update
(
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  sequence_number integer NOT NULL,
  category varchar NOT NULL,
  object_name varchar NOT NULL,
  created_date timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_administrator_database_update_sequence_number ON administrator.database_update
(
  sequence_number DESC
);

10 auth__credential_get_by_id function

10.function.auth__credential_get_by_id.sql

CREATE OR REPLACE FUNCTION auth.credential_get_by_id
(
  input_id uuid,
  OUT salt bytea,
  OUT password_hash bytea
) AS
$$
BEGIN

  SELECT
    c.salt,
    c.password_hash
  INTO
    salt,
    password_hash
  FROM
    auth.credential c
  WHERE
    c.id = input_id
  LIMIT
    1;

END
$$ LANGUAGE plpgsql;

11 auth__credential_replace procedure

11.procedure.auth__credential_replace.sql

CREATE OR REPLACE PROCEDURE auth.credential_replace
(
  id uuid,
  salt bytea,
  password_hash bytea
) AS
$$
BEGIN

  UPDATE
    auth.credential c
  SET
    c.salt = salt,
    c.password_hash = password_hash,
    c.last_update = CURRENT_TIMESTAMP
  WHERE
    c.id = id;

END
$$ LANGUAGE plpgsql;

12 app__user_add function

12.function.app__user_add.sql

CREATE OR REPLACE FUNCTION app.user_add
(
  email varchar,
  credential_id uuid,

  OUT user_id uuid
) AS
$$
BEGIN

  INSERT INTO app.user
  (
    email,
    credential_id
  )
  VALUES
  (
    email,
    credential_id
  )
  RETURNING id INTO user_id;

END
$$ LANGUAGE plpgsql;

13 app__user_get_by_id function

13.function.app__user_get_by_id.sql

CREATE OR REPLACE FUNCTION app.user_get_by_id
(
  input_id uuid,

  OUT user_id uuid,
  OUT email varchar,
  OUT credential_id uuid,
  OUT created_date timestamp with time zone
) AS
$$
BEGIN

  SELECT
    u.id,
    u.email,
    u.credential_id,
    u.created_date
  INTO
    user_id,
    email,
    credential_id,
    created_date
  FROM
    app.user u
  WHERE
    u.id = input_id
  LIMIT
    1;

END
$$ LANGUAGE plpgsql;

14 app__user_get_by_email function

14.function.app__user_get_by_email.sql

CREATE OR REPLACE FUNCTION app.user_get_by_email
(
  input_email varchar,

  OUT user_id uuid,
  OUT email varchar,
  OUT credential_id uuid,
  OUT created_date timestamp with time zone
) AS
$$
BEGIN

  SELECT
    u.id,
    u.email,
    u.credential_id,
    u.created_date
  INTO
    user_id,
    email,
    credential_id,
    created_date
  FROM
    app.user u
  WHERE
    u.email = input_email
  LIMIT
    1;

END
$$ LANGUAGE plpgsql;

15 auth__session_add function

15.function.auth__session_add.sql

CREATE OR REPLACE FUNCTION auth.session_add
(
  user_id uuid,
  ip_address bytea,

  OUT session_id uuid
) AS
$$
BEGIN

  INSERT INTO auth.session
  (
    user_id,
    ip_address
  )
  VALUES
  (
    user_id,
    ip_address
  )
  RETURNING
    id
  INTO
    session_id;

END
$$ LANGUAGE plpgsql;

16 auth__session_get_user_by_id function

16.function.auth__session_get_user_by_id.sql

CREATE OR REPLACE FUNCTION auth.session_get_user_by_id
(
  session_id uuid,
  OUT user_id uuid
) AS
$$
BEGIN

  SELECT
    s.user_id
  INTO
    user_id
  FROM
    auth.session s
  WHERE
    s.id = session_id
  LIMIT
    1;

END
$$ LANGUAGE plpgsql;

17 auth__session_delete procedure

17.procedure.auth__session_delete.sql

CREATE OR REPLACE PROCEDURE auth.session_delete
(
  session_id uuid
) AS
$$
BEGIN

  DELETE  
  FROM
    auth.session s
  WHERE
    s.id = session_id;

END
$$ LANGUAGE plpgsql;

18 auth__session_delete_by_user_id procedure

18.procedure.auth__session_delete_by_user_id.sql

CREATE OR REPLACE PROCEDURE auth.session_delete_by_user_id
(
  user_id uuid
) AS
$$
BEGIN

  DELETE  
  FROM
    auth.session s
  WHERE
    s.user_id = user_id;

END
$$ LANGUAGE plpgsql;

19 app__goal table

19.table.app__goal.sql

CREATE TABLE IF NOT EXISTS app.goal
(
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  token uuid DEFAULT gen_random_uuid() NOT NULL,
  user_id uuid NOT NULL,
  goal_name varchar NOT NULL,
  goal_image varchar NOT NULL,
  amount int NOT NULL,
  created_date timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_date timestamp with time zone NULL DEFAULT NULL,

  CONSTRAINT fk_app_goal__app_user FOREIGN KEY (user_id) REFERENCES app.user (id),
  CONSTRAINT uq_app_goal_user_id__goal_name UNIQUE (user_id, goal_name)
);

2 administrator__database_update_add procedure

2.procedure.administrator__database_update_add.sql

CREATE OR REPLACE PROCEDURE administrator.database_update_add
(
  sequence_number integer,
  category varchar,
  object_name varchar
)
LANGUAGE SQL AS
$$
  INSERT INTO administrator.database_update
  (
    sequence_number,
    category,
    object_name
  )
  VALUES
  (
    sequence_number,
    category,
    object_name
  );
$$;

20 app__goal_add function

20.function.app__goal_add.sql

CREATE OR REPLACE FUNCTION app.goal_add
(
  user_id uuid,
  goal_name varchar,
  goal_image varchar,
  amount int,

  OUT goal_id uuid,
  OUT goal_token uuid
) AS
$$
BEGIN

  INSERT INTO app.goal
  (
    user_id,
    goal_name,
    goal_image,
    amount
  )
  VALUES
  (
    user_id,
    goal_name,
    goal_image,
    amount
  )
  RETURNING
    id,
    token
  INTO
    goal_id,
    goal_token;

END
$$ LANGUAGE plpgsql;

21 app__goals_get_by_user_id function

21.function.app__goals_get_by_user_id.sql

CREATE OR REPLACE FUNCTION app.goals_get_by_user_id
(
  input_user_id uuid
) RETURNS TABLE (goal_id uuid, goal_token uuid, goal_name varchar, goal_image varchar, amount int, totalAllocatedAmount int, created_date timestamp with time zone) AS
$$
BEGIN

  RETURN
    QUERY
  SELECT
    g.id,
    g.token,
    g.goal_name,
    g.goal_image,
    g.amount,
    COALESCE(totalAllocated.amount, 0),
    g.created_date
  FROM
    app.goal g
  LEFT JOIN
    (
      SELECT
        a.user_id,
        a.allocation_name,
        CAST(SUM(a.amount) AS int) AS amount
      FROM
        app.allocation a
      WHERE
        a.category = 'Goals'
      GROUP BY
        a.user_id,
        a.allocation_name
    ) AS totalAllocated
  ON
    totalAllocated.user_id = g.user_id
  AND
    totalAllocated.allocation_name = g.goal_name
  WHERE
    g.user_id = input_user_id
  AND
    g.deleted_date IS NULL;

END
$$ LANGUAGE plpgsql;

22 allocation table

22.table.allocation.sql

CREATE TABLE IF NOT EXISTS app.allocation
(
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  token uuid DEFAULT gen_random_uuid() NOT NULL,
  user_id uuid NOT NULL,
  allocation_name varchar NOT NULL,
  category varchar NOT NULL,
  amount int NOT NULL,
  created_date timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_date timestamp with time zone NULL DEFAULT NULL,

  CONSTRAINT fk_app_allocation__app_user FOREIGN KEY (user_id) REFERENCES app.user (id)
);

23 app__allocation_add function

23.function.app__allocation_add.sql

CREATE OR REPLACE FUNCTION app.allocation_add
(
  user_id uuid,
  allocation_name varchar,
  category varchar,
  amount int,

  OUT allocation_id uuid,
  OUT allocation_token uuid
) AS
$$
BEGIN

  INSERT INTO app.allocation
  (
    user_id,
    allocation_name,
    category,
    amount
  )
  VALUES
  (
    user_id,
    allocation_name,
    category,
    amount
  )
  RETURNING
    id,
    token
  INTO
    allocation_id,
    allocation_token;

END
$$ LANGUAGE plpgsql;

24 app__allocations_get_by_user_id function

24.function.app__allocations_get_by_user_id.sql

CREATE OR REPLACE FUNCTION app.allocations_get_by_user_id
(
  input_user_id uuid,
  input_current_datetime timestamp with time zone
) RETURNS TABLE (allocation_id uuid, allocation_token uuid, allocation_name varchar, category varchar, amount int, created_date timestamp with time zone) AS
$$
BEGIN

  RETURN
    QUERY
  SELECT
    a.id,
    a.token,
    a.allocation_name,
    a.category,
    a.amount,
    a.created_date
  FROM
    app.allocation a
  WHERE
    a.user_id = input_user_id
  AND
    a.deleted_date IS NULL;

END
$$ LANGUAGE plpgsql;

25 auth__session_get_by_user_id_and_ip function

25.function.auth__session_get_by_user_id_and_ip.sql

CREATE OR REPLACE FUNCTION auth.session_get_by_user_id_and_ip
(
  usr_id uuid,
  user_ip bytea,
  OUT session_id uuid
) AS
$$
BEGIN

  SELECT
    s.id
  INTO
    session_id
  FROM
    auth.session s
  WHERE
    s.user_id = usr_id
  AND
    s.ip_address = user_ip
  LIMIT
    1;

END
$$ LANGUAGE plpgsql;

26 app__allocations_get_all_for_goals function

26.function.app__allocations_get_all_for_goals.sql

CREATE OR REPLACE FUNCTION app.allocations_get_all_for_goals
(
  input_user_id uuid
) RETURNS TABLE (allocation_id uuid, allocation_token uuid, allocation_name varchar, category varchar, amount int, created_date timestamp with time zone) AS
$$
BEGIN

  RETURN
    QUERY
  SELECT
    a.id,
    a.token,
    a.allocation_name,
    a.category,
    a.amount,
    a.created_date
  FROM
    app.allocation a
  WHERE
    a.user_id = input_user_id
  AND
    a.deleted_date IS NULL
  AND
    a.category = 'Goals';

END
$$ LANGUAGE plpgsql;

27 app__allocations_get_by_user_id_between function

27.function.app__allocations_get_by_user_id_between.sql

CREATE OR REPLACE FUNCTION app.allocations_get_by_user_id_between
(
  input_user_id uuid,
  input_from_datetime timestamp with time zone,
  input_to_datetime timestamp with time zone
) RETURNS TABLE (allocation_id uuid, allocation_token uuid, allocation_name varchar, category varchar, amount int, created_date timestamp with time zone) AS
$$
BEGIN

  RETURN
    QUERY
  SELECT
    a.id,
    a.token,
    a.allocation_name,
    a.category,
    a.amount,
    a.created_date
  FROM
    app.allocation a
  WHERE
    a.user_id = input_user_id
  AND
    a.deleted_date IS NULL
  AND
    a.created_date >= input_from_datetime
  AND
    a.created_date <= input_to_datetime;

END
$$ LANGUAGE plpgsql;

28 app__goal_update procedure

28.procedure.app__goal_update.sql

CREATE OR REPLACE PROCEDURE app.goal_update
(
  in_user_id uuid,
  in_token uuid,
  in_goal_name varchar,
  in_goal_image varchar,
  in_amount int
) AS
$$

DECLARE old_goal_name varchar;

BEGIN

  SELECT
    app.goal.goal_name
  INTO
    old_goal_name
  FROM
    app.goal
  WHERE
    user_id = in_user_id
  AND
    token = in_token;

  raise exception 'old_goal_name Value: %', old_goal_name;

  UPDATE
    app.allocation
  SET
    allocation_name = in_goal_name
  WHERE
    user_id = in_user_id
  AND
    allocation_name = old_goal_name
  AND
    category = 'Goals';

  UPDATE
    app.goal
  SET
    goal_name = in_goal_name,
    goal_image = in_goal_image,
    amount = in_amount
  WHERE
    user_id = in_user_id
  AND
    token = in_token;

END
$$ LANGUAGE plpgsql;

29 app__allocation_update procedure

29.procedure.app__allocation_update.sql

CREATE OR REPLACE PROCEDURE app.allocation_update
(
  in_user_id uuid,
  in_token uuid,
  in_allocation_name varchar,
  in_category varchar,
  in_amount int
) AS
$$
BEGIN

  UPDATE
    app.allocation
  SET
    allocation_name = in_allocation_name,
    category = in_category,
    amount = in_amount
  WHERE
    user_id = in_user_id
  AND
    token = in_token;
    
END
$$ LANGUAGE plpgsql;

3 administrator__database_update_get_last_update_sequence function

3.function.administrator__database_update_get_last_update_sequence.sql

CREATE OR REPLACE FUNCTION administrator.database_update_get_last_update_sequence ()
RETURNS integer
LANGUAGE SQL AS
$$
  SELECT MAX(sequence_number) FROM administrator.database_update;
$$;

30 app__goal_delete procedure

30.procedure.app__goal_delete.sql

CREATE OR REPLACE PROCEDURE app.goal_delete
(
  in_user_id uuid,
  in_token uuid
) AS
$$
BEGIN

  UPDATE
    app.goal
  SET
    deleted_date = CURRENT_TIMESTAMP
  WHERE
    user_id = in_user_id
  AND
    token = in_token;
    
END
$$ LANGUAGE plpgsql;

31 app__allocation_delete procedure

31.procedure.app__allocation_delete.sql

CREATE OR REPLACE PROCEDURE app.allocation_delete
(
  in_user_id uuid,
  in_token uuid
) AS
$$
BEGIN

  UPDATE
    app.allocation
  SET
    deleted_date = CURRENT_TIMESTAMP
  WHERE
    user_id = in_user_id
  AND
    token = in_token;
    
END
$$ LANGUAGE plpgsql;

4 auth schema

4.schema.auth.sql

CREATE SCHEMA IF NOT EXISTS auth;

5 app schema

5.schema.app.sql

CREATE SCHEMA IF NOT EXISTS app;

6 auth__credential table

6.table.auth__credential.sql

CREATE TABLE IF NOT EXISTS auth.credential
(
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  salt bytea NOT NULL,
  password_hash bytea NOT NULL,
  created_date timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_update timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

7 app__user table

7.table.app__user.sql

CREATE TABLE IF NOT EXISTS app.user
(
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  email varchar NOT NULL,
  credential_id uuid NOT NULL,
  created_date timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_app_user__auth_credential FOREIGN KEY (credential_id) REFERENCES auth.credential (id),
  CONSTRAINT uq_app_user_email UNIQUE (email)
);

8 auth__session table

8.table.auth__session.sql

CREATE TABLE IF NOT EXISTS auth.session
(
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id uuid NOT NULL,
  ip_address bytea NOT NULL,
  created_date timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_app_session__app_user FOREIGN KEY (user_id) REFERENCES app.user (id),
  CONSTRAINT uq_app_session_user_id__ip_address UNIQUE (user_id, ip_address)
);

9 auth__credential_add function

9.function.auth__credential_add.sql

CREATE OR REPLACE FUNCTION auth.credential_add
(
  salt bytea,
  password_hash bytea,
  OUT cred_id uuid
) AS
$$
BEGIN

  INSERT INTO auth.credential AS c
  (
    salt,
    password_hash
  )
  VALUES
  (
    salt,
    password_hash
  )
  RETURNING
    c.id INTO cred_id;

END
$$ LANGUAGE plpgsql;