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