Modifying SQL SCHEMA to generate timestamps by itself.

3 files changed, 45 insertions, 53 deletions

diff --git a/SCHEMA.sql b/SCHEMA.sql
index 50a8b2c..4177ecc 100644
--- a/SCHEMA.sql
+++ b/SCHEMA.sql
@@ -2,56 +2,56 @@ DROP TABLE users, questions, answers, questionaires, quiz_links;
 CREATE TABLE users (
 	id SERIAL PRIMARY KEY,
 	nick TEXT,
-	created INT
+	created TIMESTAMP DEFAULT NOW()
 );
 CREATE TABLE questions (
 	id SERIAL PRIMARY KEY,
 	question TEXT,
 	neg_answer TEXT,
 	pos_answer TEXT,
-	created INT
+	created TIMESTAMP DEFAULT NOW()
 );
 CREATE TABLE answers (
 	id SERIAL PRIMARY KEY,
 	user_id INT,
 	question_id INT,
 	answer INT,
-	created INT
+	created TIMESTAMP DEFAULT NOW()
 );
 CREATE TABLE questionaires (
 	id SERIAL PRIMARY KEY,
 	questionset INT[] DEFAULT '{}',
 	creator_id INT,
-	created INT
+	created TIMESTAMP DEFAULT NOW()
 );
 CREATE TABLE quiz_links (
 	id SERIAL PRIMARY KEY,
 	quiz_id INT,
 	link TEXT,
-	created INT
+	created TIMESTAMP DEFAULT NOW()
 );
-INSERT INTO users (nick, created) VALUES ('Raili Niemi', 57 );
-INSERT INTO users (nick, created) VALUES ('Tea Jalava', 36 );
-INSERT INTO users (nick, created) VALUES ('Eero Metsäranta', 47 );
-INSERT INTO questions (question, neg_answer, pos_answer, created)
-	VALUES ('Mikä ikä?', '0', '100', 99 ),
-		('Mikä vointi?', 'huono', 'hyvä', 99 ),
-		('Ulkoiletko?', 'viime vuonna', 'joka päivä', 99 ),
-		('kys?', 'ei vielä', 'ei koskaan', 99 );
-INSERT INTO answers (user_id, question_id, answer, created)
-	VALUES (1, 1, 570, 99 ),
-		(1, 2, 670, 99 ),
-		(1, 3, 888, 99 ),
-		(1, 4, 999, 99 ),
-		(2, 1, 360, 99 ),
-		(2, 2, 230, 99 ),
-		(2, 3, 120, 99 ),
-		(2, 4, 123, 99 ),
-		(3, 1, 470, 99 ),
-		(3, 2, 570, 99 ),
-		(3, 3, 321, 99 ),
-		(3, 4, 785, 99 );
-INSERT INTO questionaires (questionset,	creator_id, created)
-	VALUES ('{1,2,3,4}', 1, 666);
-INSERT INTO quiz_links (quiz_id, link, created)
-	VALUES (1, 'kysdemo', 666);
+INSERT INTO users (nick) VALUES ('Raili Niemi');
+INSERT INTO users (nick) VALUES ('Tea Jalava');
+INSERT INTO users (nick) VALUES ('Eero Metsäranta');
+INSERT INTO questions (question, neg_answer, pos_answer)
+	VALUES ('Mikä ikä?', '0', '100'),
+		('Mikä vointi?', 'huono', 'hyvä'),
+		('Ulkoiletko?', 'viime vuonna', 'joka päivä'),
+		('kys?', 'ei vielä', 'ei koskaan');
+INSERT INTO answers (user_id, question_id, answer)
+	VALUES (1, 1, 570),
+		(1, 2, 670),
+		(1, 3, 888),
+		(1, 4, 999),
+		(2, 1, 360),
+		(2, 2, 230),
+		(2, 3, 120),
+		(2, 4, 123),
+		(3, 1, 470),
+		(3, 2, 570),
+		(3, 3, 321),
+		(3, 4, 785);
+INSERT INTO questionaires (questionset,	creator_id)
+	VALUES ('{1,2,3,4}', 1);
+INSERT INTO quiz_links (quiz_id, link)
+	VALUES (1, 'kysdemo');
diff --git a/db/db.py b/db/db.py
index 8261cf5..fd2e8c6 100644
--- a/db/db.py
+++ b/db/db.py
@@ -1,5 +1,3 @@
-from time import time
-
 from flask import Flask
 from flask_sqlalchemy import SQLAlchemy
 from sqlalchemy.sql import text
@@ -16,26 +14,25 @@ class DB:
         
     def question_new(self, question, neg_ans, pos_ans ):
         sql = "INSERT \
-                INTO questions (question, neg_answer, pos_answer, created) \
-                VALUES (:question, :neg_answer, :pos_answer, :created) \
+                INTO questions (question, neg_answer, pos_answer) \
+                VALUES (:question, :neg_answer, :pos_answer) \
                 RETURNING id ;"
         result = self.db.session.execute(
                 text(sql), { 
                         "question":question, 
                         "neg_answer":neg_ans,
-                        "pos_answer":pos_ans,
-                        "created":int(time()) }
+                        "pos_answer":pos_ans }
             )
         self.db.session.commit()
         return result.fetchone()[0]
 
     def quiz_new(self, user_id):
         sql = "INSERT \
-                INTO questionaires (creator_id, created) \
-                VALUES (:creator_id, :created) \
+                INTO questionaires (creator_id) \
+                VALUES (:creator_id) \
                 RETURNING id ;"
         result = self.db.session.execute( text(sql),
-            { "creator_id":user_id, "created":int(time()) } )
+            { "creator_id":user_id } )
         self.db.session.commit()
         return result.fetchone()[0]
 
@@ -52,10 +49,10 @@ class DB:
 
     def set_quiz_link( self, quiz_id, link ):
         sql = "INSERT \
-                INTO quiz_links (quiz_id, link, created) \
-                VALUES (:quiz_id, :link, :created)"
+                INTO quiz_links (quiz_id, link) \
+                VALUES (:quiz_id, :link)"
         result = self.db.session.execute( text(sql),
-            {  "quiz_id":quiz_id, "link":link, "created":int(time()) } )
+            {  "quiz_id":quiz_id, "link":link } )
         self.db.session.commit()
 
 
@@ -77,13 +74,12 @@ class DB:
     
     def answer_new(self, user_id, question_id, answer):
         sql = "INSERT \
-                INTO answers (user_id, question_id,	answer,	created) \
-                VALUES (:user_id, :question_id, :answer, :created);"
+                INTO answers (user_id, question_id, answer ) \
+                VALUES (:user_id, :question_id, :answer);"
         self.db.session.execute( text(sql), {
                 "user_id":user_id,
                 "question_id":question_id, 
-                "answer":answer,
-                "created":int(time())
+                "answer":answer
             } )
         self.db.session.commit()
 
diff --git a/db/user.py b/db/user.py
index a129a13..7b61fab 100644
--- a/db/user.py
+++ b/db/user.py
@@ -1,5 +1,3 @@
-from time import time
-
 from sqlalchemy.sql import text
 
 class DBUser:
@@ -15,12 +13,10 @@ class DBUser:
 
     def new(self, nick):
         sql = "INSERT \
-                INTO users (nick, created) \
-                VALUES (:nick, :created) \
+                INTO users (nick) \
+                VALUES (:nick) \
                 RETURNING id ;"
-        result = self.db.session.execute(
-                text(sql), { "nick":nick, "created":int(time()) }
-            )
+        result = self.db.session.execute( text(sql), { "nick":nick } )
         self.db.session.commit()
         return result.fetchone()[0]