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 articles from a category or all articles (inner join with categories to get category stub)
 211 def site_articles (dbname, catid = None):
 212         try:
 213                 conn = sqlite3.connect (dbname)
 214                 c = conn.cursor ()
 215                 if catid is None:
 216                         c.execute ("SELECT * FROM articles INNER JOIN categories ON articles.cid=categories.cid \
 217                                                 ORDER BY cdate DESC;")
 218                 else:
 219                         c.execute ("SELECT * FROM articles INNER JOIN categories ON articles.cid=categories.cid \
 220                                                 WHERE articles.cid=? ORDER BY cdate DESC;", (catid,))
 221                 conn.commit ()
 222                 rows = c.fetchall ()
 223                 conn.close ()
 224                 return rows
 225         except sqlite3.Error:
 226                 return False
 227
 228 # Function to get the latest articles (inner join with categories to get category stub)
 229 def site_latest_articles (dbname, num_arts):
 230         try:
 231                 conn = sqlite3.connect (dbname)
 232                 c = conn.cursor ()
 233                 c.execute ("SELECT * FROM articles INNER JOIN categories ON articles.cid=categories.cid \
 234                                         ORDER BY cdate DESC LIMIT ?;", (num_arts,))
 235                 conn.commit ()
 236                 rows = c.fetchall ()
 237                 conn.close ()
 238                 return rows
 239         except sqlite3.Error:
 240                 return False
 241
 242 # Function to get the best rated articles (inner join with categories to get category stub)
 243 def site_get_bestrated (dbname):
 244         try:
 245                 conn = sqlite3.connect (dbname)
 246                 c = conn.cursor ()
 247                 c.execute ("SELECT * FROM articles INNER JOIN categories ON articles.cid=categories.cid \
 248                                         ORDER BY rating DESC LIMIT 5;")
 249                 conn.commit ()
 250                 rows = c.fetchall ()
 251                 conn.close ()
 252                 return rows
 253         except sqlite3.Error:
 254                 return False
 255
 256 # Function to get list of articles (either full list or just in a category
 257 def get_articles (dbname, category_id=None):
 258         try:
 259                 conn = sqlite3.connect (dbname)
 260                 c = conn.cursor ()
 261                 if category_id == None:
 262                         c.execute ("SELECT * FROM articles;")
 263                 else:
 264                         c.execute ("SELECT * FROM articles WHERE cid=?", (category_id,))
 265                 conn.commit ()
 266                 rows = c.fetchall ()
 267                 conn.close ()
 268                 return rows
 269         except sqlite3.Error:
 270                 return False
 271
 272 # Function to get list of categories and return a (category_id, category_name) array
 273 def get_categories (dbname):
 274         try:
 275                 conn = sqlite3.connect (dbname)
 276                 c = conn.cursor ()
 277                 c.execute ("SELECT * FROM categories;")
 278                 conn.commit ()
 279                 recs = c.fetchall ()
 280                 conn.close ()
 281                 return recs
 282         except sqlite3.Error:
 283                 return False
 284
 285 # function to create a new site database
 286 def create_db (dbname, site_title, site_url, keywords, description, copyright,
 287                                         num_rss, dest_path):
 288         try:
 289                 if os.path.exists (dbname):
 290                         os.remove (dbname)
 291         except OSError:
 292                 return False
 293
 294         try:
 295                 conn = sqlite3.connect (dbname)
 296                 c = conn.cursor ()
 297
 298                 # create the tables
 299                 c.execute ("CREATE TABLE IF NOT EXISTS \
 300                                         categories (cid INTEGER PRIMARY KEY, \
 301                                         name TEXT, desc TEXT, \
 302                                         stub TEXT);")
 303                 c.execute ("CREATE TABLE IF NOT EXISTS \
 304                                         articles (aid INTEGER PRIMARY KEY, \
 305                                         title TEXT, summary TEXT, keywords TEXT, \
 306                                         content TEXT, cdate NUMERIC, mdate NUMERIC, cid NUMERIC, \
 307                                         stub TEXT, rating NUMERIC);")
 308                 c.execute ("CREATE TABLE IF NOT EXISTS \
 309                                         config (config_name TEXT, config_param TEXT);")
 310
 311                 c.execute ("CREATE TABLE IF NOT EXISTS \
 312                                         templates (template_name TEXT, template_content);")
 313
 314                 # insert the templates into the newly created database
 315                 c.executemany ("INSERT INTO templates (template_name, template_content) VALUES (?, ?);",
 316                                                                         [["main_template", bws.template_main],
 317                                                                          ["article_bit", bws.template_article_bit],
 318                                                                          ["news_bit", bws.template_news_item_bit],
 319                                                                          ["table_bit", bws.template_table_bit],
 320                                                                          ["tablerow_bit", bws.template_tablerow_bit],
 321                                                                          ["stylesheet", bws.template_style],
 322                                                                          ["index_bit", bws.template_index_bit]])
 323
 324                 # inser the configuration
 325                 c.executemany ("INSERT INTO config (config_name, config_param) VALUES (?, ?); ",
 326                                                                         [["Website URL", site_url],
 327                                                                          ["Website Title", site_title],
 328                                                                          ["Keywords", keywords],
 329                                                                          ["Description", description],
 330                                                                          ["No. of RSS items", num_rss],
 331                                                                          ["Destination path", dest_path],
 332                                                                          ["Copyright", copyright]])
 333
 334                 conn.commit ()
 335                 conn.close ()
 336                 return True
 337         except sqlite3.Error:
 338                 return False