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