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