X-Git-Url: https://harishankar.org/repos/?p=biaweb_qt.git;a=blobdiff_plain;f=biaweb_db.py;h=b310972f9d8cf70d48976760988d6a512879c1d1;hp=2c8e6cdf3fae529c6ff91b6272f5c284a82ebde4;hb=54389d5adbbb1830a7e85545b2bdde816713a272;hpb=3632ca174c91a33db13178bc19ad2f93473b52c0 diff --git a/biaweb_db.py b/biaweb_db.py index 2c8e6cd..b310972 100644 --- a/biaweb_db.py +++ b/biaweb_db.py @@ -4,6 +4,160 @@ import sqlite3 import os import os.path +import time + +import biaweb_strings as bws + +# function to get the template text for a particular template +def get_template_text (dbname, template_name): + try: + conn = sqlite3.connect (dbname) + c = conn.cursor () + c.execute ("SELECT template_content FROM templates WHERE template_name=?", + (template_name,)) + conn.commit () + row = c.fetchone () + conn.close () + return row[0] + except sqlite3.Error: + return False + +# function to update the template +def update_template (dbname, template_name, template_str): + try: + conn = sqlite3.connect (dbname) + c = conn.cursor () + c.execute ("UPDATE templates SET template_content=? WHERE template_name=?", + (template_str, template_name)) + conn.commit () + conn.close () + return True + except sqlite3.Error: + return False + +# function to retrieve the templates from the database +def get_templates (dbname): + try: + conn = sqlite3.connect (dbname) + c = conn.cursor () + c.execute ("SELECT * FROM templates;") + conn.commit () + rows = c.fetchall () + conn.close () + return rows + except sqlite3.Error: + return False + +# function to get an article from the database +def get_article (dbname, artid): + try: + conn = sqlite3.connect (dbname) + c = conn.cursor () + c.execute ("SELECT * FROM articles WHERE aid=?;", (artid,)) + conn.commit () + row = c.fetchone () + conn.close () + return row + except sqlite3.Error: + return False + +# function to create an article +def create_article (dbname, title, summary, keywords, content, catid, stub, rating): + # time of creation + creattime = time.time () + modtime = creattime + try: + conn = sqlite3.connect (dbname) + c = conn.cursor () + c.execute ("INSERT INTO articles (title, summary, keywords, content, cdate, mdate, cid, stub, rating) \ + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);", (title, summary, keywords, content, creattime, + modtime, catid, stub, rating)) + conn.commit () + conn.close () + return True + except sqlite3.Error: + return False + +# function to update an article +def update_article (dbname, aid, title, summary, keywords, content, catid, stub, rating): + # modification time only to be updated + modtime = time.time () + try: + conn = sqlite3.connect (dbname) + c = conn.cursor () + c.execute ("UPDATE articles SET title=?, summary=?, keywords=?, content=?, mdate=?, cid=?, \ + stub=?, rating=? WHERE aid=?;", + (title, summary, keywords, content, modtime, catid, stub, rating, aid)) + conn.commit () + conn.close () + return True + except sqlite3.Error: + return False + +# function to delete an article +def delete_article (dbname, artid): + try: + conn = sqlite3.connect (dbname) + c = conn.cursor () + c.execute ("DELETE FROM articles WHERE aid=?;", (artid,)) + conn.commit () + conn.close () + return True + except sqlite3.Error: + return False + +# function to get a category from the database +def get_category (dbname, catid): + try: + conn = sqlite3.connect (dbname) + c = conn.cursor () + c.execute ("SELECT * FROM categories WHERE cid=?", (catid,)) + conn.commit () + cat = c.fetchone () + conn.close () + return cat + except sqlite3.Error: + return False + +# function to update a category +def update_category (dbname, catid, cat_name, cat_desc, cat_stub): + try: + conn = sqlite3.connect (dbname) + c = conn.cursor () + c.execute ("UPDATE categories SET name=?, desc=?, stub=? WHERE cid=?;", + (cat_name, cat_desc, cat_stub, catid)) + conn.commit () + conn.close () + return True + except sqlite3.Error: + return False + +# function to remove a category +def remove_category (dbname, catid): + try: + conn = sqlite3.connect (dbname) + c = conn.cursor () + c.execute ("DELETE FROM categories WHERE cid=?;", (catid,)) + c.execute ("DELETE FROM articles WHERE cid=?;", (catid,)) + conn.commit () + conn.close () + return True + except sqlite3.Error: + return False + + +# function to create a category +def create_category (dbname, category_name, category_desc, category_stub): + try: + conn = sqlite3.connect (dbname) + c = conn.cursor () + c.execute ("INSERT INTO categories (name, desc, stub) VALUES (?, ?, ?);", + (category_name, category_desc, category_stub)) + conn.commit () + conn.close () + return True + except sqlite3.Error: + return False # function to set the configuration and update the database def set_configuration (dbname, site_title, site_url, keywords, description, copyright, @@ -53,20 +207,6 @@ def get_configuration (dbname): except sqlite3.Error: return False - -# function to create a category -def create_category (dbname, category_name, category_desc, category_stub): - try: - conn = sqlite3.connect (dbname) - c = conn.cursor () - c.execute ("INSERT INTO categories (name, desc, stub) VALUES (?, ?, ?);", - (category_name, category_desc, category_stub)) - conn.commit () - conn.close () - return True - except sqlite3.Error: - return False - # Function to get list of articles (either full list or just in a category def get_articles (dbname, category_id=None): try: @@ -108,6 +248,8 @@ def create_db (dbname, site_title, site_url, keywords, description, copyright, try: conn = sqlite3.connect (dbname) c = conn.cursor () + + # create the tables c.execute ("CREATE TABLE IF NOT EXISTS \ categories (cid INTEGER PRIMARY KEY, \ name TEXT, desc TEXT, \ @@ -123,203 +265,17 @@ def create_db (dbname, site_title, site_url, keywords, description, copyright, c.execute ("CREATE TABLE IF NOT EXISTS \ templates (template_name TEXT, template_content);") - template_main = """ - - -
-${category_desc}
-Title | -Created on | -Rated | -