biaweb_db.py

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