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