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