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