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