Spaces:
Running
Running
File size: 2,688 Bytes
478dec6 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 | # CREATE TABLES
query_create_cv_raw = """
CREATE TABLE cv_raw (
filename TEXT NOT NULL UNIQUE,
profile_id SERIAL PRIMARY KEY,
file_content BYTEA NOT NULL,
is_extracted BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
"""
alter_table_cv_raw_add_is_extracted = """
ALTER TABLE cv_raw
ADD COLUMN is_extracted BOOLEAN DEFAULT FALSE;"""
query_create_cv_profile_extracted = """
CREATE TABLE cv_profile_extracted (
fullname VARCHAR(100) NOT NULL,
profile_id SERIAL PRIMARY KEY,
gpa_edu_1 DECIMAL(4,2),
gpa_edu_2 DECIMAL(4,2),
gpa_edu_3 DECIMAL(4,2),
univ_edu_1 varchar(100),
univ_edu_2 varchar(100),
univ_edu_3 varchar(100),
major_edu_1 varchar(100),
major_edu_2 varchar(100),
major_edu_3 varchar(100),
domicile TEXT,
yoe DECIMAL(3,1),
hardskills TEXT,
softskills TEXT,
certifications TEXT,
business_domain TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_profile_extracted
FOREIGN KEY (profile_id)
REFERENCES cv_raw (profile_id)
ON DELETE CASCADE
);
"""
query_create_cv_profile_norm = """
CREATE TABLE cv_profile_norm (
fullname VARCHAR(100) NOT NULL,
profile_id SERIAL PRIMARY KEY,
gpa_edu_1 DECIMAL(4,2),
gpa_edu_2 DECIMAL(4,2),
gpa_edu_3 DECIMAL(4,2),
univ_edu_1 varchar(100),
univ_edu_2 varchar(100),
univ_edu_3 varchar(100),
major_edu_1 varchar(100),
major_edu_2 varchar(100),
major_edu_3 varchar(100),
domicile TEXT,
yoe DECIMAL(3,1),
hardskills TEXT,
softskills TEXT,
certifications TEXT,
business_domain TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_profile_norm
FOREIGN KEY (profile_id)
REFERENCES cv_profile_extracted (profile_id)
ON DELETE CASCADE
);
"""
# DROP TABLES
query_drop_cv_raw = "drop table cv_raw cascade"
query_drop_cv_profile_extracted = "drop table cv_profile_extracted cascade"
query_drop_cv_profile_norm = "drop table cv_profile_norm cascade"
# OPERATIONS
q_creates = [query_create_cv_raw, query_create_cv_profile_extracted, query_create_cv_profile_norm]
q_drops = [
# query_drop_cv_raw,
query_drop_cv_profile_extracted, query_drop_cv_profile_norm]
from externals.databases._pgdb import execute_query
async def get_create_queries():
for q in q_creates:
await execute_query(q)
async def get_drop_queries():
for q in q_drops:
await execute_query(q)
if __name__ == "__main__":
import asyncio
# asyncio.run(get_drop_queries())
asyncio.run(get_create_queries()) |