# BiaWeb Website content manager (c) 2010 V.Harishankar
# Database handling functions
import sqlite3
import os
import os.path
import pprint
# function to create a category
def create_category (dbname, category_name, category_desc, category_stub):
try:
conn = sqlite3.connect (dbname)
c = conn.cursor ()
c.execute ("INSERT INTO categories (name, desc, stub) VALUES (?, ?, ?);",
(category_name, category_desc, category_stub))
conn.commit ()
conn.close ()
return True
except sqlite3.Error:
return False
# Function to get list of articles (either full list or just in a category
def get_articles (dbname, category_id=None):
try:
conn = sqlite3.connect (dbname)
c = conn.cursor ()
if category_id == None:
c.execute ("SELECT * FROM articles;")
else:
c.execute ("SELECT * FROM articles WHERE cid=?", (category_id,))
conn.commit ()
rows = c.fetchall ()
return rows
except sqlite3.Error:
return False
# Function to get list of categories and return a (category_id, category_name) array
def get_categories (dbname):
try:
conn = sqlite3.connect (dbname)
c = conn.cursor ()
c.execute ("SELECT * FROM categories;")
conn.commit ()
recs = c.fetchall ()
conn.close ()
return recs
except sqlite3.Error:
return False
# function to create a new site database
def create_db (dbname, site_title, site_url, keywords, description, copyright,
num_rss, dest_path):
try:
if os.path.exists (dbname):
os.remove (dbname)
except OSError:
return False
try:
conn = sqlite3.connect (dbname)
c = conn.cursor ()
c.execute ("CREATE TABLE IF NOT EXISTS \
categories (cid INTEGER PRIMARY KEY, \
name TEXT, desc TEXT, \
stub TEXT);")
c.execute ("CREATE TABLE IF NOT EXISTS \
articles (aid INTEGER PRIMARY KEY, \
title TEXT, summary TEXT, keywords TEXT, \
content TEXT, cdate NUMERIC, mdate NUMERIC, cid NUMERIC, \
stub TEXT, rating NUMERIC);")
c.execute ("CREATE TABLE IF NOT EXISTS \
config (config_name TEXT, config_param TEXT);")
c.execute ("CREATE TABLE IF NOT EXISTS \
templates (template_name TEXT, template_content);")
template_main = """
${site_title}
${contents_bit}
"""
template_article_bit = """${article_title}
Created: ${article_cdate} | Last modified: ${article_mdate}
Rating: ${rating}
${article_contents}
"""
template_news_item_bit = """
${news_datetime}
${news_description}
"""
template_index_bit = """Welcome to ${site_name}
Welcome to my site, ${site_name}.
Latest Articles
${news_updates}
"""
template_table_bit = """${category_title}
${category_desc}
Title |
Created on |
Rated |
${table_rows}
"""
template_tablerow_bit = """
${title} |
${created} |
${rating} |
"""
template_style = """body {
font-family: "Bitstream Vera Sans", Verdana, Arial, Sans Serif;
font-size: 0.9em;
background-color: #ffffff;
color: #000000;
margin: auto
}
#head {
width: 98%;
background-color: #efefef;
padding: 1%;
text-align: center;
}
#main {
width: 73%;
padding: 1%;
float: left;
}
#sidebar {
width: 23%;
padding: 1%;
float: right;
}
#footer {
width: 100%;
padding-top: 5px;
padding-bottom: 5px;
font-size: 0.9em;
text-align: center;
float: left;
background-color: #efefef;
}
.headerdesc {
font-variant: small-caps;
font-size: 1.1em;
}
.content {
text-align: justify;
line-height: 1.1em;
}
.categorytable {
width: 100%;
}
.categorytable thead {
font-weight: bold;
}
.modified {
font-size: 0.8em;
color: #666666;
}
.rating {
font-size: 0.8em;
color: #666666;
}
h1, h2, h3 {
font-family: "Bitstream Vera Serif", Serif;
padding: 0;
margin: 0;
margin-top: 5px;
margin-bottom: 5px;
}
hr {
border: 0;
border-bottom: 1px solid;
border-color: #888888;
}
h1 {
font-size: 2.4em;
color: #000099;
}
h1 a, h1 a:hover, h1 a:visited, h2 a:active {
text-decoration: none;
}
h2 {
font-size: 1.4em;
background-color: #efefef;
}
h2 a, h2 a:hover, h2 a:visited, h2 a:active {
text-decoration: none;
}
h3 {
font-size: 1.2em;
a {
color: #0000dd;
}
a:visited {
color: #0000aa;
}
a:active, a:hover {
color: #0000ff;
}"""
c.executemany ("INSERT INTO templates (template_name, template_content) VALUES (?, ?);",
[["main_template", template_main],
["article_bit", template_article_bit],
["news_bit", template_news_item_bit],
["table_bit", template_table_bit],
["tablerow_bit", template_tablerow_bit],
["stylesheet", template_style],
["index_bit", template_index_bit]])
c.executemany ("INSERT INTO config (config_name, config_param) VALUES (?, ?); ",
[["Website URL", site_url],
["Website Title", site_title],
["Keywords", keywords],
["Description", description],
["No. of RSS items", num_rss],
["Destination path", dest_path],
["Copyright", copyright]])
conn.commit ()
conn.close ()
return True
except sqlite3.Error:
return False