biaweb_db.py

   1 # BiaWeb Website content manager (c) 2010 V.Harishankar
   2 # Database handling functions
   3
   4 import sqlite3
   5 import os
   6 import os.path
   7 import time
   8
   9 import biaweb_strings as bws
  10
  11 # function to get the template text for a particular template
  12 def get_template_text (dbname, template_name):
  13         try:
  14                 conn = sqlite3.connect (dbname)
  15                 c = conn.cursor ()
  16                 c.execute ("SELECT template_content FROM templates WHERE template_name=?",
  17                                                 (template_name,))
  18                 conn.commit ()
  19                 row = c.fetchone ()
  20                 conn.close ()
  21                 return row[0]
  22         except sqlite3.Error:
  23                 return False
  24
  25 # function to update the template
  26 def update_template (dbname, template_name, template_str):
  27         try:
  28                 conn = sqlite3.connect (dbname)
  29                 c = conn.cursor ()
  30                 c.execute ("UPDATE templates SET template_content=? WHERE template_name=?",
  31                                                 (template_str, template_name))
  32                 conn.commit ()
  33                 conn.close ()
  34                 return True
  35         except sqlite3.Error:
  36                 return False
  37
  38 # function to retrieve the templates from the database
  39 def get_templates (dbname):
  40         try:
  41                 conn = sqlite3.connect (dbname)
  42                 c = conn.cursor ()
  43                 c.execute ("SELECT * FROM templates;")
  44                 conn.commit ()
  45                 rows = c.fetchall ()
  46                 conn.close ()
  47                 return rows
  48         except sqlite3.Error:
  49                 return False
  50
  51 # function to get an article from the database
  52 def get_article (dbname, artid):
  53         try:
  54                 conn = sqlite3.connect (dbname)
  55                 c = conn.cursor ()
  56                 c.execute ("SELECT * FROM articles WHERE aid=?;", (artid,))
  57                 conn.commit ()
  58                 row = c.fetchone ()
  59                 conn.close ()
  60                 return row
  61         except sqlite3.Error:
  62                 return False
  63
  64 # function to create an article
  65 def create_article (dbname, title, summary, keywords, content, catid, stub, rating):
  66         # time of creation
  67         creattime = time.time ()
  68         modtime = creattime
  69         try:
  70                 conn = sqlite3.connect (dbname)
  71                 c = conn.cursor ()
  72                 c.execute ("INSERT INTO articles (title, summary, keywords, content, cdate, mdate, cid, stub, rating) \
  73                                         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);", (title, summary, keywords, content, creattime,
  74                                         modtime, catid, stub, rating))
  75                 conn.commit ()
  76                 conn.close ()
  77                 return True
  78         except sqlite3.Error:
  79                 return False
  80
  81 # function to update an article
  82 def update_article (dbname, aid, title, summary, keywords, content, catid, stub, rating):
  83         # modification time only to be updated
  84         modtime = time.time ()
  85         try:
  86                 conn = sqlite3.connect (dbname)
  87                 c = conn.cursor ()
  88                 c.execute ("UPDATE articles SET title=?, summary=?, keywords=?, content=?, mdate=?, cid=?, \
  89                                         stub=?, rating=? WHERE aid=?;",
  90                                         (title, summary, keywords, content, modtime, catid, stub, rating, aid))
  91                 conn.commit ()
  92                 conn.close ()
  93                 return True
  94         except sqlite3.Error:
  95                 return False
  96
  97 # function to delete an article
  98 def delete_article (dbname, artid):
  99         try:
 100                 conn = sqlite3.connect (dbname)
 101                 c = conn.cursor ()
 102                 c.execute ("DELETE FROM articles WHERE aid=?;", (artid,))
 103                 conn.commit ()
 104                 conn.close ()
 105                 return True
 106         except sqlite3.Error:
 107                 return False
 108
 109 # function to get a category from the database
 110 def get_category (dbname, catid):
 111         try:
 112                 conn = sqlite3.connect (dbname)
 113                 c = conn.cursor ()
 114                 c.execute ("SELECT * FROM categories WHERE cid=?", (catid,))
 115                 conn.commit ()
 116                 cat = c.fetchone ()
 117                 conn.close ()
 118                 return cat
 119         except sqlite3.Error:
 120                 return False
 121
 122 # function to update a category
 123 def update_category (dbname, catid, cat_name, cat_desc, cat_stub):
 124         try:
 125                 conn = sqlite3.connect (dbname)
 126                 c = conn.cursor ()
 127                 c.execute ("UPDATE categories SET name=?, desc=?, stub=? WHERE cid=?;",
 128                                         (cat_name, cat_desc, cat_stub, catid))
 129                 conn.commit ()
 130                 conn.close ()
 131                 return True
 132         except sqlite3.Error:
 133                 return False
 134
 135 # function to remove a category
 136 def remove_category (dbname, catid):
 137         try:
 138                 conn = sqlite3.connect (dbname)
 139                 c = conn.cursor ()
 140                 c.execute ("DELETE FROM categories WHERE cid=?;", (catid,))
 141                 c.execute ("DELETE FROM articles WHERE cid=?;", (catid,))
 142                 conn.commit ()
 143                 conn.close ()
 144                 return True
 145         except sqlite3.Error:
 146                 return False
 147
 148
 149 # function to create a category
 150 def create_category (dbname, category_name, category_desc, category_stub):
 151         try:
 152                 conn = sqlite3.connect (dbname)
 153                 c = conn.cursor ()
 154                 c.execute ("INSERT INTO categories (name, desc, stub) VALUES (?, ?, ?);",
 155                                                         (category_name, category_desc, category_stub))
 156                 conn.commit ()
 157                 conn.close ()
 158                 return True
 159         except sqlite3.Error:
 160                 return False
 161
 162 # function to set the configuration and update the database
 163 def set_configuration (dbname, site_title, site_url, keywords, description, copyright,
 164                                                 num_rss, dest_path):
 165         try:
 166                 conn = sqlite3.connect (dbname)
 167                 c = conn.cursor ()
 168                 c.executemany ("UPDATE config SET config_param=? WHERE config_name=?;",
 169                                                 [[site_url, "Website URL"],
 170                                                  [site_title, "Website Title"],
 171                                                  [keywords, "Keywords"],
 172                                                  [description, "Description"],
 173                                                  [num_rss, "No. of RSS items"],
 174                                                  [dest_path, "Destination path"],
 175                                                  [copyright, "Copyright"]])
 176                 conn.commit ()
 177                 conn.close ()
 178                 return True
 179         except sqlite3.Error:
 180                 return False
 181
 182 # function to get the existing site configuration and return it as a tuple
 183 def get_configuration (dbname):
 184         try:
 185                 conn = sqlite3.connect (dbname)
 186                 c = conn.cursor ()
 187                 c.execute ("SELECT * FROM config;")
 188                 conn.commit ()
 189                 recs = c.fetchall ()
 190                 conn.close ()
 191                 for name, param in recs:
 192                         if name == "Website URL":
 193                                 website_url = param
 194                         elif name == "Website Title":
 195                                 website_title = param
 196                         elif name == "Keywords":
 197                                 keywords = param
 198                         elif name == "Description":
 199                                 description = param
 200                         elif name == "No. of RSS items":
 201                                 num_rss = int (param)
 202                         elif name == "Destination path":
 203                                 destination = param
 204                         elif name == "Copyright":
 205                                 copyright = param
 206                 return (website_url, website_title, keywords, description, num_rss, destination, copyright)
 207         except sqlite3.Error:
 208                 return False
 209
 210 # Function to get the latest articles (inner join with categories to get category stub)
 211 def site_latest_articles (dbname, num_arts):
 212         try:
 213                 conn = sqlite3.connect (dbname)
 214                 c = conn.cursor ()
 215                 c.execute ("SELECT * FROM articles INNER JOIN categories ON articles.cid=categories.cid \
 216                                         ORDER BY cdate DESC LIMIT ?;", (num_arts,))
 217                 conn.commit ()
 218                 rows = c.fetchall ()
 219                 conn.close ()
 220                 return rows
 221         except sqlite3.Error:
 222                 return False
 223
 224 # Function to get the best rated articles (inner join with categories to get category stub)
 225 def site_get_bestrated (dbname):
 226         try:
 227                 conn = sqlite3.connect (dbname)
 228                 c = conn.cursor ()
 229                 c.execute ("SELECT * FROM articles INNER JOIN categories ON articles.cid=categories.cid \
 230                                         ORDER BY rating DESC LIMIT 5;")
 231                 conn.commit ()
 232                 rows = c.fetchall ()
 233                 conn.close ()
 234                 return rows
 235         except sqlite3.Error:
 236                 return False
 237
 238 # Function to get list of articles (either full list or just in a category
 239 def get_articles (dbname, category_id=None):
 240         try:
 241                 conn = sqlite3.connect (dbname)
 242                 c = conn.cursor ()
 243                 if category_id == None:
 244                         c.execute ("SELECT * FROM articles;")
 245                 else:
 246                         c.execute ("SELECT * FROM articles WHERE cid=?", (category_id,))
 247                 conn.commit ()
 248                 rows = c.fetchall ()
 249                 conn.close ()
 250                 return rows
 251         except sqlite3.Error:
 252                 return False
 253
 254 # Function to get list of categories and return a (category_id, category_name) array
 255 def get_categories (dbname):
 256         try:
 257                 conn = sqlite3.connect (dbname)
 258                 c = conn.cursor ()
 259                 c.execute ("SELECT * FROM categories;")
 260                 conn.commit ()
 261                 recs = c.fetchall ()
 262                 conn.close ()
 263                 return recs
 264         except sqlite3.Error:
 265                 return False
 266
 267 # function to create a new site database
 268 def create_db (dbname, site_title, site_url, keywords, description, copyright,
 269                                         num_rss, dest_path):
 270         try:
 271                 if os.path.exists (dbname):
 272                         os.remove (dbname)
 273         except OSError:
 274                 return False
 275
 276         try:
 277                 conn = sqlite3.connect (dbname)
 278                 c = conn.cursor ()
 279
 280                 # create the tables
 281                 c.execute ("CREATE TABLE IF NOT EXISTS \
 282                                         categories (cid INTEGER PRIMARY KEY, \
 283                                         name TEXT, desc TEXT, \
 284                                         stub TEXT);")
 285                 c.execute ("CREATE TABLE IF NOT EXISTS \
 286                                         articles (aid INTEGER PRIMARY KEY, \
 287                                         title TEXT, summary TEXT, keywords TEXT, \
 288                                         content TEXT, cdate NUMERIC, mdate NUMERIC, cid NUMERIC, \
 289                                         stub TEXT, rating NUMERIC);")
 290                 c.execute ("CREATE TABLE IF NOT EXISTS \
 291                                         config (config_name TEXT, config_param TEXT);")
 292
 293                 c.execute ("CREATE TABLE IF NOT EXISTS \
 294                                         templates (template_name TEXT, template_content);")
 295
 296                 # insert the templates into the newly created database
 297                 c.executemany ("INSERT INTO templates (template_name, template_content) VALUES (?, ?);",
 298                                                                         [["main_template", bws.template_main],
 299                                                                          ["article_bit", bws.template_article_bit],
 300                                                                          ["news_bit", bws.template_news_item_bit],
 301                                                                          ["table_bit", bws.template_table_bit],
 302                                                                          ["tablerow_bit", bws.template_tablerow_bit],
 303                                                                          ["stylesheet", bws.template_style],
 304                                                                          ["index_bit", bws.template_index_bit]])
 305
 306                 # inser the configuration
 307                 c.executemany ("INSERT INTO config (config_name, config_param) VALUES (?, ?); ",
 308                                                                         [["Website URL", site_url],
 309                                                                          ["Website Title", site_title],
 310                                                                          ["Keywords", keywords],
 311                                                                          ["Description", description],
 312                                                                          ["No. of RSS items", num_rss],
 313                                                                          ["Destination path", dest_path],
 314                                                                          ["Copyright", copyright]])
 315
 316                 conn.commit ()
 317                 conn.close ()
 318                 return True
 319         except sqlite3.Error:
 320                 return False