def create_table_task():
connect, current_connect = db_connect()
sql = """CREATE TABLE task (
id_task SERIAL NOT NULL,
Task_category varchar(30) NOT NULL,
Task_name varchar(255) NOT NULL UNIQUE,
Task_flag varchar(255) NOT NULL,
Task_description varchar(2048) NOT NULL,
Task_point integer NOT NULL,
Task_hint varchar(1024) NOT NULL,
Task_solve varchar(1024),
Task_link varchar(512),
Status integer,
Public_status integer,
id_event integer,
PRIMARY KEY (id_task)
)"""
print(sql)
try:
current_connect.execute(sql)
current_connect.close()
except:
return
def create_table_event():
connect, current_connect = db_connect()
sql = """-- Table: public.event
DROP TABLE event;
CREATE TABLE event
(
id_event SERIAL NOT NULL,
name text COLLATE pg_catalog."default" NOT NULL,
description text COLLATE pg_catalog."default" NOT NULL,
logo text COLLATE pg_catalog."default" NOT NULL,
date_start timestamp(6) without time zone NOT NULL,
date_end timestamp(6) without time zone NOT NULL,
date_stop timestamp(6) without time zone NOT NULL,
date_continue timestamp(6) without time zone NOT NULL,
status integer,
CONSTRAINT event_pkey PRIMARY KEY (id_event)
)
-- DROP INDEX public.event_id_event_close;
CREATE INDEX event_id_event_close
ON event USING btree
(id_event DESC, status)
TABLESPACE pg_default
WHERE status = 1
;"""
print(sql)
try:
current_connect.execute(sql)
current_connect.close()
except:
return
def create_table_session():
connect, current_connect = db_connect()
sql = """CREATE TABLE Session (
Session SERIAL NOT NULL,
id_user integer NOT NULL,
UUID char(256) NOT NULL UNIQUE,
PRIMARY KEY (Session)
) """
print(sql)
try:
current_connect.execute(sql)
current_connect.close()
except:
return
def create_users():
sql = [
"INSERT INTO Auth(User, Login, Password) VALUES(1, 'Anton1', 'qwerty');",
"INSERT INTO Auth(User, Login, Password) VALUES(2, 'Anton2', 'qwerty');",
"INSERT INTO Auth(User, Login, Password) VALUES(3, 'Anton3', 'qwerty');"
"INSERT INTO Auth(User, Login, Password) VALUES(4, 'Anton4', 'qwerty');"
]
for execute in sql:
connect, current_connect = db_connect()
try:
data = current_connect.execute(execute)
finally:
current_connect.close()
def create_table_access():
connect, current_connect = db_connect()
sql = """--DROP TABLE access;
CREATE TABLE access
(
id_user integer NOT NULL,
access integer NOT NULL DEFAULT 0,
CONSTRAINT access_pkey PRIMARY KEY (id_user)
) """
print(sql)
try:
current_connect.execute(sql)
current_connect.close()
except:
return
def create_table_auth():
connect, current_connect = db_connect()
sql = """CREATE TABLE auth
(
id_user SERIAL NOT NULL,
login text COLLATE pg_catalog."default" NOT NULL,
password text COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT auth_pkey PRIMARY KEY (id_user),
CONSTRAINT auth_login_key UNIQUE (login)
) """
print(sql)
try:
current_connect.execute(sql)
current_connect.close()
except:
return
def create_table_task_acc():
connect, current_connect = db_connect()
sql = """CREATE TABLE task_acc (
id SERIAL NOT NULL,
id_task integer NOT NULL,
id_user integer NOT NULL,
point integer NOT NULL,
UNIQUE(id_task, id_user),
PRIMARY KEY (id)
);"""
print(sql)
try:
current_connect.execute(sql)
current_connect.close()
except:
return
def create_table_news():
connect, current_connect = db_connect()
sql = """CREATE TABLE news (
id_news SERIAL NOT NULL,
News_text text NOT NULL,
id_user integer NOT NULL,
Likes_count integer NOT NULL,
Dislikes_count integer NOT NULL,
Data timestamp NOT NULL,
PRIMARY KEY (id_news)
);"""
print(sql)
try:
current_connect.execute(sql)
current_connect.close()
except:
return
def create_table_user():
connect, current_connect = db_connect()
sql = """--drop table users;
CREATE TABLE public.users
(
id_user SERIAL NOT NULL,
name text COLLATE pg_catalog."default",
surname text COLLATE pg_catalog."default",
email text COLLATE pg_catalog."default",
sex text COLLATE pg_catalog."default",
city text COLLATE pg_catalog."default",
educational text COLLATE pg_catalog."default",
logo text COLLATE pg_catalog."default",
CONSTRAINT users_pkey PRIMARY KEY (id_user)
)"""
print(sql)
try:
current_connect.execute(sql)
current_connect.close()
except:
return
def Query(sql):
connect, current_connect = cd.db_connect()
print(connect)
current_connect.execute(sql)
result = current_connect.fetchall()
return result