queries.ini

[tables]
users_table = CREATE TABLE IF NOT EXISTS users (
    id       SERIAL,
    userid   bigint,
    username varchar(64),
    lang     char(2) DEFAULT 'en',
    UNIQUE   (userid));
roles_table = CREATE TABLE IF NOT EXISTS roles (
    id            SERIAL,
    account_role  varchar(30),
    userid        bigint);
market_table = CREATE TABLE IF NOT EXISTS market (
    id SERIAL, dish_name varchar(50) NOT NULL UNIQUE, price int,
    quantity int DEFAULT 0);
orders_table = CREATE TABLE IF NOT EXISTS orders (
    id SERIAL, username varchar(64), dish varchar(50), quantity int);

[critical]
# ATTENTION!
#   -- EXECUTING THIS SQL SCRIPT MAY CAUSE ALL DATA LOSS!
#   -- USE ONLY FOR TEST AND DEBUGGING PURPOSES!
manual_drop_all = DROP TABLE IF EXISTS users, market, roles, orders;
drop_all = SELECT 'drop table if exists "' || tables.table_name || '" cascade;'
    FROM information_schema.tables WHERE  table_schema = 'public';

[users]
add_user = INSERT INTO users (userid, username, lang)
    VALUES (%%s, %%s, %%s);
select_user = SELECT username FROM users WHERE userid = %%(userid)s;
update_user_lang = UPDATE users SET lang = %%(lang)s WHERE userid = %%(userid)s;
select_user_lang = SELECT (lang) FROM users WHERE userid = %%s;

[roles]
add_role = INSERT INTO roles (account_role, userid)
    VALUES (%%s, %%s);
select_roles = SELECT DISTINCT userid FROM roles;
check_if_admin = SELECT id FROM roles WHERE userid = %%s AND
    account_role = 'admin';
check_if_role = SELECT id FROM roles WHERE userid = %%s and account_role = %%s;
select_userid_by_role = SELECT userid FROM roles WHERE account_role = %%s;

[market]
add_dish = INSERT INTO market (dish_name, price, quantity)
    VALUES (%%s, %%s, %%s);
select_dishes = SELECT dish_name, price, quantity FROM market
    ORDER BY quantity DESC;
select_dish_quantity = SELECT quantity FROM market WHERE dish_name = %%s;
decrease_quantity = UPDATE market AS m
   SET quantity = m.quantity - o.quantity
   FROM orders AS o
   WHERE o.username = %%s AND o.dish = m.dish_name;

[orders]
order_dish = INSERT INTO orders (username, dish, quantity)
    VALUES (%%s, %%s, %%s);
delete_order = DELETE FROM orders WHERE username = %%s;