Commits

Yang Zhang committed df1dd88

change schema to prepare for message system

Comments (0)

Files changed (2)

 
 --user
 CREATE TABLE user(
-    id                      INTEGER PRIMARY KEY AUTOINCREMENT,
-    nickname                VARCHAR,
-    email                   VARCHAR,
-    password                VARCHAR,
-    avatar                  BLOB,-------- need to reduce
-    star_counter            INTEGER DEFAULT 0,
-    created_topic_counter   INTEGER DEFAULT 0,
-    comment_counter         INTEGER DEFAULT 0,
-    create_date             TIMESTAMP DEFAULT (datetime('now','localtime')),
-    reputation              INTEGER DEFAULT 0,
+    id                              INTEGER PRIMARY KEY AUTOINCREMENT,
+    nickname                        VARCHAR,
+    email                           VARCHAR,
+    password                        VARCHAR,
+    avatar                          BLOB,-------- need to reduce
+    star_counter                    INTEGER DEFAULT 0,
+    created_topic_counter           INTEGER DEFAULT 0,
+    comment_counter                 INTEGER DEFAULT 0,
+    notify_message_counter          INTEGER DEFAULT 0,
+    unreaded_counter                INTEGER DEFAULT 0,
+    create_date                     TIMESTAMP DEFAULT (datetime('now','localtime')),
+    reputation                      INTEGER DEFAULT 0,
     UNIQUE(nickname),
     UNIQUE(email)
 );
 FROM comment c, user u WHERE u.id = c.userid;
 
 
+-- notify message view
+CREATE VIEW notify_message_view AS
+SELECT tv.title AS title, tv.poster_name AS poster_name, 
+       tv.poster_email AS poster_email,tv.posterid AS posterid, 
+       c.content AS content, c.create_date AS create_date,
+       c.userid AS userid, c.nickname AS nickname, 
+       c.email AS email
+FROM notify_message nm, comment_view c, topic_view tv
+WHERE nm.commentid = c.id AND c.topicid = tv.id;
+
+
 --create trigger
 --update after user comment
 CREATE TRIGGER update_topic_comment_counter AFTER INSERT on comment
   BEGIN
-    UPDATE topic SET comments_number = comments_number +1, score =score +1, last_comment_posterid = new.userid, last_comment_date = new.create_date
+    UPDATE topic SET comments_number = comments_number +1, score =score +1, 
+                    last_comment_posterid = new.userid, 
+                    last_comment_date = new.create_date 
     WHERE new.topicid = id;
 
     UPDATE counter SET comment_number = comment_number +1;
         self.star_counter = 0
         self.created_topic_counter = 0
         self.comment_counter = 0
+        self.notify_message_counter = 0
+        self.unreaded_counter = 0
 
 
     @staticmethod
         tempuser.star_counter = result['star_counter']
         tempuser.created_topic_counter = result['created_topic_counter']
         tempuser.comment_counter = result['comment_counter']
+        tempuser.notify_message_counter = result['notify_message_counter']
+        tempuser.unreaded_counter = result['unreaded_counter']
         tempuser.id = id
         return tempuser
 
         c.execute("UPDATE user SET avatar = :avatar WHERE id=:id",\
                                 {'id':id,'avatar':avatar})
         g.db.commit()
-        
-        
+
+
     def update_email_nickname(self):
         c = g.db.cursor()
         c.execute("UPDATE user SET email=:email, nickname=:nickname WHERE id=:id",\
             {'userid':userid,'password':changed_pw})
         g.db.commit()
         return True
-        
+
     @staticmethod
     def password_generate(size=8, chars=string.ascii_letters + string.digits):
         return ''.join(random.choice(chars) for x in range(size))
 
 
 class NotifyMessage:
-    def __init__(self):
+    def __init__(self, userid, commentid):
+        self.userid = userid
+        self.commentid = commentid
+        self.readed = False
+        self.create_date = None
+    @staticmethod
+    def get_notify_messages_byuserid(userid, limit = 5, offset = 0):
         pass
 
+    def save(self):
+        c = g.db.cursor()
+        self.create_date = datetime.datetime.now()
+        c.execute("INSERT INTO \
+                    notify_message(userid, commentid, readed, create_date)\
+                    VALUES (:userid, :commentid, :readed, :create_date)",\
+                    {'userid':self.userid,'commentid':self.commentid,\
+                    'readed':self.readed, 'create_date':create_date})
+        self.id = c.lastrowid
+        g.db.commit()
 
 
 
 
 
 
+