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