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 # function to retrieve the templates from the database
  10 def get_templates (dbname):
  11         try:
  12                 conn = sqlite3.connect (dbname)
  13                 c = conn.cursor ()
  14                 c.execute ("SELECT * FROM templates;")
  15                 conn.commit ()
  16                 rows = c.fetchall ()
  17                 conn.close ()
  18                 return rows
  19         except sqlite3.Error:
  20                 return False
  21
  22 # function to get an article from the database
  23 def get_article (dbname, artid):
  24         try:
  25                 conn = sqlite3.connect (dbname)
  26                 c = conn.cursor ()
  27                 c.execute ("SELECT * FROM articles WHERE aid=?;", (artid,))
  28                 conn.commit ()
  29                 row = c.fetchone ()
  30                 conn.close ()
  31                 return row
  32         except sqlite3.Error:
  33                 return False
  34
  35 # function to create an article
  36 def create_article (dbname, title, summary, keywords, content, catid, stub, rating):
  37         # time of creation
  38         creattime = time.time ()
  39         modtime = creattime
  40         try:
  41                 conn = sqlite3.connect (dbname)
  42                 c = conn.cursor ()
  43                 c.execute ("INSERT INTO articles (title, summary, keywords, content, cdate, mdate, cid, stub, rating) \
  44                                         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);", (title, summary, keywords, content, creattime,
  45                                         modtime, catid, stub, rating))
  46                 conn.commit ()
  47                 conn.close ()
  48                 return True
  49         except sqlite3.Error:
  50                 return False
  51
  52 # function to update an article
  53 def update_article (dbname, aid, title, summary, keywords, content, catid, stub, rating):
  54         # modification time only to be updated
  55         modtime = time.time ()
  56         try:
  57                 conn = sqlite3.connect (dbname)
  58                 c = conn.cursor ()
  59                 c.execute ("UPDATE articles SET title=?, summary=?, keywords=?, content=?, mdate=?, cid=?, \
  60                                         stub=?, rating=? WHERE aid=?;",
  61                                         (title, summary, keywords, content, modtime, catid, stub, rating, aid))
  62                 conn.commit ()
  63                 conn.close ()
  64                 return True
  65         except sqlite3.Error:
  66                 return False
  67
  68 # function to delete an article
  69 def delete_article (dbname, artid):
  70         try:
  71                 conn = sqlite3.connect (dbname)
  72                 c = conn.cursor ()
  73                 c.execute ("DELETE FROM articles WHERE aid=?;", (artid,))
  74                 conn.commit ()
  75                 conn.close ()
  76                 return True
  77         except sqlite3.Error:
  78                 return False
  79
  80 # function to get a category from the database
  81 def get_category (dbname, catid):
  82         try:
  83                 conn = sqlite3.connect (dbname)
  84                 c = conn.cursor ()
  85                 c.execute ("SELECT * FROM categories WHERE cid=?", (catid,))
  86                 conn.commit ()
  87                 cat = c.fetchone ()
  88                 conn.close ()
  89                 return cat
  90         except sqlite3.Error:
  91                 return False
  92
  93 # function to update a category
  94 def update_category (dbname, catid, cat_name, cat_desc, cat_stub):
  95         try:
  96                 conn = sqlite3.connect (dbname)
  97                 c = conn.cursor ()
  98                 c.execute ("UPDATE categories SET name=?, desc=?, stub=? WHERE cid=?;",
  99                                         (cat_name, cat_desc, cat_stub, catid))
 100                 conn.commit ()
 101                 conn.close ()
 102                 return True
 103         except sqlite3.Error:
 104                 return False
 105
 106 # function to remove a category
 107 def remove_category (dbname, catid):
 108         try:
 109                 conn = sqlite3.connect (dbname)
 110                 c = conn.cursor ()
 111                 c.execute ("DELETE FROM categories WHERE cid=?;", (catid,))
 112                 c.execute ("DELETE FROM articles WHERE cid=?;", (catid,))
 113                 conn.commit ()
 114                 conn.close ()
 115                 return True
 116         except sqlite3.Error:
 117                 return False
 118
 119
 120 # function to create a category
 121 def create_category (dbname, category_name, category_desc, category_stub):
 122         try:
 123                 conn = sqlite3.connect (dbname)
 124                 c = conn.cursor ()
 125                 c.execute ("INSERT INTO categories (name, desc, stub) VALUES (?, ?, ?);",
 126                                                         (category_name, category_desc, category_stub))
 127                 conn.commit ()
 128                 conn.close ()
 129                 return True
 130         except sqlite3.Error:
 131                 return False
 132
 133 # function to set the configuration and update the database
 134 def set_configuration (dbname, site_title, site_url, keywords, description, copyright,
 135                                                 num_rss, dest_path):
 136         try:
 137                 conn = sqlite3.connect (dbname)
 138                 c = conn.cursor ()
 139                 c.executemany ("UPDATE config SET config_param=? WHERE config_name=?;",
 140                                                 [[site_url, "Website URL"],
 141                                                  [site_title, "Website Title"],
 142                                                  [keywords, "Keywords"],
 143                                                  [description, "Description"],
 144                                                  [num_rss, "No. of RSS items"],
 145                                                  [dest_path, "Destination path"],
 146                                                  [copyright, "Copyright"]])
 147                 conn.commit ()
 148                 conn.close ()
 149                 return True
 150         except sqlite3.Error:
 151                 return False
 152
 153 # function to get the existing site configuration and return it as a tuple
 154 def get_configuration (dbname):
 155         try:
 156                 conn = sqlite3.connect (dbname)
 157                 c = conn.cursor ()
 158                 c.execute ("SELECT * FROM config;")
 159                 conn.commit ()
 160                 recs = c.fetchall ()
 161                 conn.close ()
 162                 for name, param in recs:
 163                         if name == "Website URL":
 164                                 website_url = param
 165                         elif name == "Website Title":
 166                                 website_title = param
 167                         elif name == "Keywords":
 168                                 keywords = param
 169                         elif name == "Description":
 170                                 description = param
 171                         elif name == "No. of RSS items":
 172                                 num_rss = int (param)
 173                         elif name == "Destination path":
 174                                 destination = param
 175                         elif name == "Copyright":
 176                                 copyright = param
 177                 return (website_url, website_title, keywords, description, num_rss, destination, copyright)
 178         except sqlite3.Error:
 179                 return False
 180
 181 # Function to get list of articles (either full list or just in a category
 182 def get_articles (dbname, category_id=None):
 183         try:
 184                 conn = sqlite3.connect (dbname)
 185                 c = conn.cursor ()
 186                 if category_id == None:
 187                         c.execute ("SELECT * FROM articles;")
 188                 else:
 189                         c.execute ("SELECT * FROM articles WHERE cid=?", (category_id,))
 190                 conn.commit ()
 191                 rows = c.fetchall ()
 192                 conn.close ()
 193                 return rows
 194         except sqlite3.Error:
 195                 return False
 196
 197 # Function to get list of categories and return a (category_id, category_name) array
 198 def get_categories (dbname):
 199         try:
 200                 conn = sqlite3.connect (dbname)
 201                 c = conn.cursor ()
 202                 c.execute ("SELECT * FROM categories;")
 203                 conn.commit ()
 204                 recs = c.fetchall ()
 205                 conn.close ()
 206                 return recs
 207         except sqlite3.Error:
 208                 return False
 209
 210 # function to create a new site database
 211 def create_db (dbname, site_title, site_url, keywords, description, copyright,
 212                                         num_rss, dest_path):
 213         try:
 214                 if os.path.exists (dbname):
 215                         os.remove (dbname)
 216         except OSError:
 217                 return False
 218
 219         try:
 220                 conn = sqlite3.connect (dbname)
 221                 c = conn.cursor ()
 222                 c.execute ("CREATE TABLE IF NOT EXISTS \
 223                                         categories (cid INTEGER PRIMARY KEY, \
 224                                         name TEXT, desc TEXT, \
 225                                         stub TEXT);")
 226                 c.execute ("CREATE TABLE IF NOT EXISTS \
 227                                         articles (aid INTEGER PRIMARY KEY, \
 228                                         title TEXT, summary TEXT, keywords TEXT, \
 229                                         content TEXT, cdate NUMERIC, mdate NUMERIC, cid NUMERIC, \
 230                                         stub TEXT, rating NUMERIC);")
 231                 c.execute ("CREATE TABLE IF NOT EXISTS \
 232                                         config (config_name TEXT, config_param TEXT);")
 233
 234                 c.execute ("CREATE TABLE IF NOT EXISTS \
 235                                         templates (template_name TEXT, template_content);")
 236
 237                 template_main = """<?xml version="1.0" encoding="UTF-8"?>
 238 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
 239 <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
 240 <head>
 241 <title>${site_title}</title>
 242 <base href="${site_url}" />
 243 <meta name="generator" content="BiaWeb"/>
 244 <meta name="keywords" content="${meta_keywords}"/>
 245 <meta name="description" content="${meta_description}"/>
 246 <link rel="StyleSheet" type="text/css" href="style.css" />
 247 <link rel="alternate" type="application/rss+xml" title="Entries RSS 2.0" href="subscribe.xml" />
 248 </head>
 249 <body>
 250         <div id="head">
 251                 <h1><a href="${site_url}">${page_title}</a></h1>
 252                 <div class="headerdesc">${page_desc}</div>
 253         </div>
 254         <div id="main">
 255                 ${contents_bit}
 256         </div>
 257         <div id="sidebar">
 258                 <h2>Categories</h2>
 259                 ${list_of_categories}
 260                 <h2>Best rated</h2>
 261                 ${list_best_rated}
 262                 <h2>Subscribe</h2>
 263                 <a href="subscribe.xml">Latest articles (RSS)</a>
 264                 <h2>Search</h2>
 265                 <form action="cgi-bin/search.py" method="post" enctype="multipart/form-data">
 266                                         <p><input type="text" name="query" maxlength="255" style="width:142px;border: 1px inset #5A5A5A; color:#5A5A5A; background-color:#FFFFFF;" value="" /><br />
 267                                         <input type="submit" value="Search" /><br />
 268                                         <input type="hidden" name="fromsearch" value="fromsearch" />
 269                                         <input type="radio" name="criteria" value="1" checked="checked" />All words<br />
 270                                         <input type="radio" name="criteria" value="2" />Any word</p>
 271                 </form>
 272         </div>
 273         <div id="footer">${copyright}<br />Site generated by
 274         <a href=\"http://harishankar.org/software/biaweb.php\">BiaWeb</a> created by V. Harishankar</div>
 275 </body>
 276 </html>"""
 277
 278                 template_article_bit = """<h2>${article_title}</h2>
 279 <div class="modified">Created: ${article_cdate} | Last modified: ${article_mdate}</div>
 280 <div class="rating">Rating: ${rating}</div>
 281 <div class="content">${article_contents}</div>
 282 """
 283
 284                 template_news_item_bit = """<h3><a href="${news_link}">${news_title}</a></h3>
 285 <div class="modified">${news_datetime}</div>
 286 <div class="content">${news_description}</div>
 287 """
 288
 289                 template_index_bit = """<h2>Welcome to ${site_name}</h2>
 290 <div class="content">
 291 Welcome to my site, ${site_name}.
 292 </div>
 293 <h2>Latest Articles</h2>
 294 ${news_updates}
 295 """
 296
 297                 template_table_bit = """<h2>${category_title}</h2>
 298         <p>${category_desc}</p>
 299 <table class="categorytable">
 300 <thead>
 301         <tr>
 302                 <td style="width:50%">Title</td>
 303                 <td>Created on</td>
 304                 <td>Rated</td>
 305         </tr>
 306 </thead>
 307 <tbody>
 308         ${table_rows}
 309 </tbody>
 310 </table>
 311 """
 312
 313                 template_tablerow_bit = """<tr>
 314         <td style="width:50%"><a href="${article_url}">${title}</a></td>
 315         <td>${created}</td>
 316         <td>${rating}</td>
 317 </tr>
 318 """
 319
 320                 template_style = """body {
 321         font-family: "Bitstream Vera Sans", Verdana, Arial, Sans Serif;
 322         font-size: 0.9em;
 323         background-color: #ffffff;
 324         color: #000000;
 325         margin: auto
 326 }
 327 #head {
 328         width: 98%;
 329         background-color: #efefef;
 330         padding: 1%;
 331         text-align: center;
 332 }
 333
 334 #main {
 335         width: 73%;
 336         padding: 1%;
 337         float: left;
 338 }
 339
 340 #sidebar {
 341         width: 23%;
 342         padding: 1%;
 343         float: right;
 344 }
 345
 346 #footer {
 347         width: 100%;
 348         padding-top: 5px;
 349         padding-bottom: 5px;
 350         font-size: 0.9em;
 351         text-align: center;
 352         float: left;
 353         background-color: #efefef;
 354 }
 355
 356 .headerdesc {
 357         font-variant: small-caps;
 358         font-size: 1.1em;
 359 }
 360
 361 .content {
 362         text-align: justify;
 363         line-height: 1.1em;
 364 }
 365
 366 .categorytable {
 367         width: 100%;
 368 }
 369
 370 .categorytable thead {
 371         font-weight: bold;
 372 }
 373
 374 .modified {
 375         font-size: 0.8em;
 376         color: #666666;
 377 }
 378
 379 .rating {
 380         font-size: 0.8em;
 381         color: #666666;
 382 }
 383
 384 h1, h2, h3 {
 385         font-family: "Bitstream Vera Serif", Serif;
 386         padding: 0;
 387         margin: 0;
 388         margin-top: 5px;
 389         margin-bottom: 5px;
 390 }
 391
 392 hr {
 393         border: 0;
 394         border-bottom: 1px solid;
 395         border-color: #888888;
 396 }
 397
 398 h1 {
 399         font-size: 2.4em;
 400         color: #000099;
 401
 402 }
 403 h1 a, h1 a:hover, h1 a:visited, h2 a:active {
 404         text-decoration: none;
 405 }
 406 h2 {
 407         font-size: 1.4em;
 408         background-color: #efefef;
 409 }
 410 h2 a, h2 a:hover, h2 a:visited, h2 a:active {
 411         text-decoration: none;
 412 }
 413 h3 {
 414         font-size: 1.2em;
 415 a  {
 416         color: #0000dd;
 417 }
 418 a:visited {
 419         color: #0000aa;
 420 }
 421 a:active, a:hover {
 422         color: #0000ff;
 423 }"""
 424
 425                 c.executemany ("INSERT INTO templates (template_name, template_content) VALUES (?, ?);",
 426                                                                         [["main_template", template_main],
 427                                                                          ["article_bit", template_article_bit],
 428                                                                          ["news_bit", template_news_item_bit],
 429                                                                          ["table_bit", template_table_bit],
 430                                                                          ["tablerow_bit", template_tablerow_bit],
 431                                                                          ["stylesheet", template_style],
 432                                                                          ["index_bit", template_index_bit]])
 433
 434                 c.executemany ("INSERT INTO config (config_name, config_param) VALUES (?, ?); ",
 435                                                                         [["Website URL", site_url],
 436                                                                          ["Website Title", site_title],
 437                                                                          ["Keywords", keywords],
 438                                                                          ["Description", description],
 439                                                                          ["No. of RSS items", num_rss],
 440                                                                          ["Destination path", dest_path],
 441                                                                          ["Copyright", copyright]])
 442
 443                 conn.commit ()
 444                 conn.close ()
 445                 return True
 446         except sqlite3.Error:
 447                 return False