Add students blueprint using psycopg

1 files changed, 134 insertions, 7 deletions

diff --git a/ustayml/schema.sql b/ustayml/schema.sql
index be76d7e..8062be8 100644
--- a/ustayml/schema.sql
+++ b/ustayml/schema.sql
@@ -1,17 +1,144 @@
-DROP TABLE IF EXISTS user;
-DROP TABLE IF EXISTS post;
+-- System
 
-CREATE TABLE user (
-  id INTEGER PRIMARY KEY AUTOINCREMENT,
+CREATE TABLE user_role (
+	id SERIAL PRIMARY KEY,
+	name TEXT
+);
+
+CREATE TABLE "user" (
+  id SERIAL PRIMARY KEY,
   username TEXT UNIQUE NOT NULL,
-  password TEXT NOT NULL
+  password TEXT NOT NULL,
+  role_id INTEGER NOT NULL,
+  FOREIGN KEY (role_id) REFERENCES user_role (id)
 );
 
 CREATE TABLE post (
-  id INTEGER PRIMARY KEY AUTOINCREMENT,
+  id SERIAL PRIMARY KEY,
   author_id INTEGER NOT NULL,
   created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   title TEXT NOT NULL,
   body TEXT NOT NULL,
-  FOREIGN KEY (author_id) REFERENCES user (id)
+  FOREIGN KEY (author_id) REFERENCES "user" (id)
+);
+
+-- Students
+
+create table pucp_unit (
+  id serial primary key,
+  "name" text
+);
+
+create table administrative_division (
+  id serial primary key,
+  "name" text,
+  country text,
+  subdivision1 text,
+  subdivision2 text,
+  subdivision3 text,
+  subdivision10 text,
+  ubigeo text,
+  "population" integer,
+  poverty real
+);
+
+CREATE TABLE student (
+	id SERIAL PRIMARY KEY,
+	pucp_code INTEGER,
+	first_name TEXT,
+	last_name TEXT,
+	email TEXT,
+	-- district TEXT,
+  	administrative_division_id integer,
+	pucp_unit_id integer,
+	tuition_bracket TEXT,
+	current_semester integer,
+	current_attendance REAL,
+	current_craest REAL,
+	current_ppne3 REAL,
+	current_merit REAL,
+	est_study_length INTEGER,
+	est_dessertion_risk TEXT,
+	FOREIGN KEY (administrative_division_id) REFERENCES administrative_division (id),
+	FOREIGN KEY (pucp_unit_id) REFERENCES pucp_unit (id)
 );
+
+-- alter table student 
+-- add constraint fk_administrative_division
+-- FOREIGN KEY (administrative_division_id) 
+-- REFERENCES administrative_division (id)
+-- ;
+
+-- Sample data  (later move to tests)
+
+INSERT INTO user_role ("id", "name") VALUES
+	 (1, 'admin'),
+	 (2, 'user')
+;
+
+INSERT INTO "user" ("username","password","role_id") VALUES
+	 ('mitsuo','scrypt:32768:8:1$DNNf1Ah1SwUcFHIU$c0e40293c9ecff498b74e7727a8d9a348371262b85cc880e8c653438b1afa84849adb7230f4fcd995c82de543a7836a5cda2a411a45c17dc653e7036fe3de482',1)
+;
+
+INSERT INTO pucp_unit ("name") VALUES
+	 ('Facultad de Ciencias e Ingenieria'),
+	 ('Estudios Generales Ciencias');
+
+-- sample 2019 data
+INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES
+	 ('Peru','','','','','',29009326,28.9,'country'),
+	 ('Peru','Lima','','','','150000',8981440,15.4,'region'),
+	 ('Peru','Lima','Lima','','','150100',8095747,17.5,'province'),
+	 ('Peru','Lima','Lima','LIMA                     ','Lima Centro','150101',302056,12.3,'district'),
+	 ('Peru','Lima','Lima','ANCÓN                    ','Lima Norte','150102',36401,19.6,'district'),
+	 ('Peru','Lima','Lima','ATE                      ','Lima Este','150103',521692,18.9,'district'),
+	 ('Peru','Lima','Lima','BARRANCO                 ','Lima Centro','150104',33996,5.3,'district'),
+	 ('Peru','Lima','Lima','BREÑA                    ','Lima Centro','150105',82987,8.5,'district'),
+	 ('Peru','Lima','Lima','CARABAYLLO               ','Lima Norte','150106',237269,26.3,'district'),
+	 ('Peru','Lima','Lima','CHACLACAYO               ','Lima Este','150107',42884,10.1,'district');
+INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES
+	 ('Peru','Lima','Lima','CHORRILLOS               ','Lima Centro','150108',303913,17.2,'district'),
+	 ('Peru','Lima','Lima','CIENEGUILLA              ','Lima Este','150109',31160,24.8,'district'),
+	 ('Peru','Lima','Lima','COMAS                    ','Lima Norte','150110',509976,22.3,'district'),
+	 ('Peru','Lima','Lima','EL AGUSTINO              ','Lima Este','150111',188138,22.1,'district'),
+	 ('Peru','Lima','Lima','INDEPENDENCIA            ','Lima Norte','150112',215941,21.3,'district'),
+	 ('Peru','Lima','Lima','JESÚS MARÍA              ','Lima Centro','150113',71139,1.7,'district'),
+	 ('Peru','Lima','Lima','LA MOLINA                ','Lima Este','150114',144491,0.7,'district'),
+	 ('Peru','Lima','Lima','LA VICTORIA              ','Lima Centro','150115',193592,14.9,'district'),
+	 ('Peru','Lima','Lima','LINCE                    ','Lima Centro','150116',55733,4.0,'district'),
+	 ('Peru','Lima','Lima','LOS OLIVOS               ','Lima Norte','150117',339028,13.4,'district');
+INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES
+	 ('Peru','Lima','Lima','LURIGANCHO               ','Lima Este','150118',184593,24.4,'district'),
+	 ('Peru','Lima','Lima','LURÍN                    ','Lima Sur','150119',69282,30.2,'district'),
+	 ('Peru','Lima','Lima','MAGDALENA DEL MAR        ','Lima Centro','150120',54116,2.3,'district'),
+	 ('Peru','Lima','Lima','PUEBLO LIBRE','Lima Centro','150121',77892,2.0,'district'),
+	 ('Peru','Lima','Lima','MIRAFLORES               ','Lima Centro','150122',86920,0.8,'district'),
+	 ('Peru','Lima','Lima','PACHACAMAC               ','Lima Este','150123',81145,32.9,'district'),
+	 ('Peru','Lima','Lima','PUCUSANA                 ','Lima Sur','150124',12148,26.7,'district'),
+	 ('Peru','Lima','Lima','PUENTE PIEDRA            ','Lima Norte','150125',263594,35.6,'district'),
+	 ('Peru','Lima','Lima','PUNTA HERMOSA            ','Lima Sur','150126',6309,7.4,'district'),
+	 ('Peru','Lima','Lima','PUNTA NEGRA              ','Lima Sur','150127',5951,9.5,'district');
+INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES
+	 ('Peru','Lima','Lima','RIMAC                    ','Lima Centro','150128',178869,16.5,'district'),
+	 ('Peru','Lima','Lima','SAN BARTOLO              ','Lima Sur','150129',6368,9.7,'district'),
+	 ('Peru','Lima','Lima','SAN BORJA                ','Lima Centro','150130',111208,0.8,'district'),
+	 ('Peru','Lima','Lima','SAN ISIDRO               ','Lima Centro','150131',58920,0.6,'district'),
+	 ('Peru','Lima','Lima','SAN JUAN DE LURIGANCHO   ','Lima Este','150132',962554,27.0,'district'),
+	 ('Peru','Lima','Lima','SAN JUAN DE MIRAFLORES   ','Lima Sur','150133',382531,19.7,'district'),
+	 ('Peru','Lima','Lima','SAN LUIS                 ','Lima Este','150134',57080,7.1,'district'),
+	 ('Peru','Lima','Lima','SAN MARTÍN DE PORRES     ','Lima Norte','150135',620193,10.9,'district'),
+	 ('Peru','Lima','Lima','SAN MIGUEL               ','Lima Centro','150136',134666,2.3,'district'),
+	 ('Peru','Lima','Lima','SANTA ANITA              ','Lima Este','150137',199282,12.0,'district');
+INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES
+	 ('Peru','Lima','Lima','SANTA MARÍA DEL MAR      ','Lima Sur','150138',924,4.0,'district'),
+	 ('Peru','Lima','Lima','SANTA ROSA               ','Lima Norte','150139',12641,12.0,'district'),
+	 ('Peru','Lima','Lima','SANTIAGO DE SURCO        ','Lima Centro','150140',309889,3.3,'district'),
+	 ('Peru','Lima','Lima','SURQUILLO                ','Lima Centro','150141',93271,5.2,'district'),
+	 ('Peru','Lima','Lima','VILLA EL SALVADOR        ','Lima Sur','150142',410313,25.9,'district'),
+	 ('Peru','Lima','Lima','VILLA MARÍA DEL TRIUNFO  ','Lima Sur','150143',404692,21.1,'district');
+
+
+INSERT INTO student (pucp_code,first_name,last_name,email,administrative_division_id,pucp_unit_id,tuition_bracket,current_semester,current_attendance,current_craest,current_ppne3,current_merit,est_study_length,est_dessertion_risk) VALUES
+	 (20171234,'Alberto','Alvarez','aalvarez@pucp.edu.pe',40,1,'G5',5,0.8,58.35,55.23,0.1532,10,'L'),
+	 (20171235,'Benito','Bueno','bbueno@pucp.edu.pe',41,2,'G5',5,0.8,58.35,55.23,0.1532,10,'H'),
+	 (20171236,'Carlos','Canto','ccanto@pucp.edu.pe',42,1,'G5',5,0.8,58.35,55.23,0.1532,10,'L');
\ No newline at end of file