X-Git-Url: https://harishankar.org/repos/?p=biaweb_qt.git;a=blobdiff_plain;f=biaweb_db.py;h=c1d5e60db222804c0eac91012849a2982f8a0dc2;hp=1946ad7dafbe9f64a517105c8dbeec5b18585fd9;hb=df54af4f6bf7afa6b89f5fa1f07177313aa5cc1f;hpb=5f4cc1e3f6bf2ffbf624dfc19d24c1b4eb2e15db diff --git a/biaweb_db.py b/biaweb_db.py index 1946ad7..c1d5e60 100644 --- a/biaweb_db.py +++ b/biaweb_db.py @@ -6,6 +6,61 @@ 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 @@ -23,6 +78,22 @@ def create_article (dbname, title, summary, keywords, content, catid, stub, rati 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: @@ -136,6 +207,52 @@ def get_configuration (dbname): except sqlite3.Error: return False +# Function to get articles from a category or all articles (inner join with categories to get category stub) +def site_articles (dbname, catid = None): + try: + conn = sqlite3.connect (dbname) + c = conn.cursor () + if catid is None: + c.execute ("SELECT * FROM articles INNER JOIN categories ON articles.cid=categories.cid \ + ORDER BY cdate DESC;") + else: + c.execute ("SELECT * FROM articles INNER JOIN categories ON articles.cid=categories.cid \ + WHERE articles.cid=? ORDER BY cdate DESC;", (catid,)) + conn.commit () + rows = c.fetchall () + conn.close () + return rows + except sqlite3.Error: + return False + +# Function to get the latest articles (inner join with categories to get category stub) +def site_latest_articles (dbname, num_arts): + try: + conn = sqlite3.connect (dbname) + c = conn.cursor () + c.execute ("SELECT * FROM articles INNER JOIN categories ON articles.cid=categories.cid \ + ORDER BY cdate DESC LIMIT ?;", (num_arts,)) + conn.commit () + rows = c.fetchall () + conn.close () + return rows + except sqlite3.Error: + return False + +# Function to get the best rated articles (inner join with categories to get category stub) +def site_get_bestrated (dbname): + try: + conn = sqlite3.connect (dbname) + c = conn.cursor () + c.execute ("SELECT * FROM articles INNER JOIN categories ON articles.cid=categories.cid \ + ORDER BY rating DESC LIMIT 5;") + conn.commit () + rows = c.fetchall () + conn.close () + return rows + 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: @@ -177,6 +294,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, \ @@ -192,203 +311,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 | -