# Database handling functions
import sqlite3
+import os
+import os.path
+import time
+import biaweb_strings as bws
+
+# function to get the template text for a particular template
+def get_template_text (dbname, template_name):
+ try:
+ conn = sqlite3.connect (dbname)
+ c = conn.cursor ()
+ c.execute ("SELECT template_content FROM templates WHERE template_name=?",
+ (template_name,))
+ conn.commit ()
+ row = c.fetchone ()
+ conn.close ()
+ return row[0]
+ except sqlite3.Error:
+ return False
+
+# function to update the template
+def update_template (dbname, template_name, template_str):
+ try:
+ conn = sqlite3.connect (dbname)
+ c = conn.cursor ()
+ c.execute ("UPDATE templates SET template_content=? WHERE template_name=?",
+ (template_str, template_name))
+ conn.commit ()
+ conn.close ()
+ return True
+ except sqlite3.Error:
+ return False
+
+# function to retrieve the templates from the database
+def get_templates (dbname):
+ try:
+ conn = sqlite3.connect (dbname)
+ c = conn.cursor ()
+ c.execute ("SELECT * FROM templates;")
+ conn.commit ()
+ rows = c.fetchall ()
+ conn.close ()
+ return rows
+ except sqlite3.Error:
+ return False
+
+# function to get an article from the database
+def get_article (dbname, artid):
+ try:
+ conn = sqlite3.connect (dbname)
+ c = conn.cursor ()
+ c.execute ("SELECT * FROM articles WHERE aid=?;", (artid,))
+ conn.commit ()
+ row = c.fetchone ()
+ conn.close ()
+ return row
+ except sqlite3.Error:
+ return False
+
+# function to create an article
+def create_article (dbname, title, summary, keywords, content, catid, stub, rating):
+ # time of creation
+ creattime = time.time ()
+ modtime = creattime
+ try:
+ conn = sqlite3.connect (dbname)
+ c = conn.cursor ()
+ c.execute ("INSERT INTO articles (title, summary, keywords, content, cdate, mdate, cid, stub, rating) \
+ VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);", (title, summary, keywords, content, creattime,
+ modtime, catid, stub, rating))
+ conn.commit ()
+ conn.close ()
+ return True
+ except sqlite3.Error:
+ return False
+
+# function to update an article
+def update_article (dbname, aid, title, summary, keywords, content, catid, stub, rating):
+ # modification time only to be updated
+ modtime = time.time ()
+ try:
+ conn = sqlite3.connect (dbname)
+ c = conn.cursor ()
+ c.execute ("UPDATE articles SET title=?, summary=?, keywords=?, content=?, mdate=?, cid=?, \
+ stub=?, rating=? WHERE aid=?;",
+ (title, summary, keywords, content, modtime, catid, stub, rating, aid))
+ conn.commit ()
+ conn.close ()
+ return True
+ except sqlite3.Error:
+ return False
+
+# function to delete an article
+def delete_article (dbname, artid):
+ try:
+ conn = sqlite3.connect (dbname)
+ c = conn.cursor ()
+ c.execute ("DELETE FROM articles WHERE aid=?;", (artid,))
+ conn.commit ()
+ conn.close ()
+ return True
+ except sqlite3.Error:
+ return False
+
+# function to get a category from the database
+def get_category (dbname, catid):
+ try:
+ conn = sqlite3.connect (dbname)
+ c = conn.cursor ()
+ c.execute ("SELECT * FROM categories WHERE cid=?", (catid,))
+ conn.commit ()
+ cat = c.fetchone ()
+ conn.close ()
+ return cat
+ except sqlite3.Error:
+ return False
+
+# function to update a category
+def update_category (dbname, catid, cat_name, cat_desc, cat_stub):
+ try:
+ conn = sqlite3.connect (dbname)
+ c = conn.cursor ()
+ c.execute ("UPDATE categories SET name=?, desc=?, stub=? WHERE cid=?;",
+ (cat_name, cat_desc, cat_stub, catid))
+ conn.commit ()
+ conn.close ()
+ return True
+ except sqlite3.Error:
+ return False
+
+# function to remove a category
+def remove_category (dbname, catid):
+ try:
+ conn = sqlite3.connect (dbname)
+ c = conn.cursor ()
+ c.execute ("DELETE FROM categories WHERE cid=?;", (catid,))
+ c.execute ("DELETE FROM articles WHERE cid=?;", (catid,))
+ conn.commit ()
+ conn.close ()
+ return True
+ except sqlite3.Error:
+ return False
+
+
+# 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 set the configuration and update the database
+def set_configuration (dbname, site_title, site_url, keywords, description, copyright,
+ num_rss, dest_path):
+ try:
+ conn = sqlite3.connect (dbname)
+ c = conn.cursor ()
+ c.executemany ("UPDATE config SET config_param=? WHERE config_name=?;",
+ [[site_url, "Website URL"],
+ [site_title, "Website Title"],
+ [keywords, "Keywords"],
+ [description, "Description"],
+ [num_rss, "No. of RSS items"],
+ [dest_path, "Destination path"],
+ [copyright, "Copyright"]])
+ conn.commit ()
+ conn.close ()
+ return True
+ except sqlite3.Error:
+ return False
+
+# function to get the existing site configuration and return it as a tuple
+def get_configuration (dbname):
+ try:
+ conn = sqlite3.connect (dbname)
+ c = conn.cursor ()
+ c.execute ("SELECT * FROM config;")
+ conn.commit ()
+ recs = c.fetchall ()
+ conn.close ()
+ for name, param in recs:
+ if name == "Website URL":
+ website_url = param
+ elif name == "Website Title":
+ website_title = param
+ elif name == "Keywords":
+ keywords = param
+ elif name == "Description":
+ description = param
+ elif name == "No. of RSS items":
+ num_rss = int (param)
+ elif name == "Destination path":
+ destination = param
+ elif name == "Copyright":
+ copyright = param
+ return (website_url, website_title, keywords, description, num_rss, destination, copyright)
+ 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 ()
+ conn.close ()
+ 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 ()
+
+ # create the tables
c.execute ("CREATE TABLE IF NOT EXISTS \
categories (cid INTEGER PRIMARY KEY, \
name TEXT, desc TEXT, \
c.execute ("CREATE TABLE IF NOT EXISTS \
templates (template_name TEXT, template_content);")
- template_main = """<?xml version="1.0" encoding="UTF-8"?>
- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
- <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
- <head>
- <title>${site_title}</title>
- <base href="${site_url}" />
- <meta name="generator" content="BiaWeb"/>
- <meta name="keywords" content="${meta_keywords}"/>
- <meta name="description" content="${meta_description}"/>
- <link rel="StyleSheet" type="text/css" href="style.css" />
- <link rel="alternate" type="application/rss+xml" title="Entries RSS 2.0" href="subscribe.xml" />
- </head>
- <body>
- <div id="head">
- <h1><a href="${site_url}">${page_title}</a></h1>
- <div class="headerdesc">${page_desc}</div>
- </div>
- <div id="main">
- ${contents_bit}
- </div>
- <div id="sidebar">
- <h2>Categories</h2>
- ${list_of_categories}
- <h2>Best rated</h2>
- ${list_best_rated}
- <h2>Subscribe</h2>
- <a href="subscribe.xml">Latest articles (RSS)</a>
- <h2>Search</h2>
- <form action="cgi-bin/search.py" method="post" enctype="multipart/form-data">
- <p><input type="text" name="query" maxlength="255" style="width:142px;border: 1px inset #5A5A5A; color:#5A5A5A; background-color:#FFFFFF;" value="" /><br />
- <input type="submit" value="Search" /><br />
- <input type="hidden" name="fromsearch" value="fromsearch" />
- <input type="radio" name="criteria" value="1" checked="checked" />All words<br />
- <input type="radio" name="criteria" value="2" />Any word</p>
- </form>
- </div>
- <div id="footer">${copyright}<br />Site generated by
- <a href=\"http://harishankar.org/software/biaweb.php\">BiaWeb</a> created by V. Harishankar</div>
- </body>
- </html>"""
-
- template_article_bit = """<h2>${article_title}</h2>
- <div class="modified">Created: ${article_cdate} | Last modified: ${article_mdate}</div>
- <div class="rating">Rating: ${rating}</div>
- <div class="content">${article_contents}</div>
- """
-
- template_news_item_bit = """<h3><a href="${news_link}">${news_title}</a></h3>
- <div class="modified">${news_datetime}</div>
- <div class="content">${news_description}</div>
- """
-
- template_index_bit = """<h2>Welcome to ${site_name}</h2>
- <div class="content">
- Welcome to my site, ${site_name}.
- </div>
- <h2>Latest Articles</h2>
- ${news_updates}
- """
-
- template_table_bit = """<h2>${category_title}</h2>
- <p>${category_desc}</p>
- <table class="categorytable">
- <thead>
- <tr>
- <td style="width:50%">Title</td>
- <td>Created on</td>
- <td>Rated</td>
- </tr>
- </thead>
- <tbody>
- ${table_rows}
- </tbody>
- </table>
- """
-
- template_tablerow_bit = """<tr>
- <td style="width:50%"><a href="${article_url}">${title}</a></td>
- <td>${created}</td>
- <td>${rating}</td>
- </tr>
- """
-
- 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;
- }"""
-
+ # insert the templates into the newly created database
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]])
+ [["main_template", bws.template_main],
+ ["article_bit", bws.template_article_bit],
+ ["news_bit", bws.template_news_item_bit],
+ ["table_bit", bws.template_table_bit],
+ ["tablerow_bit", bws.template_tablerow_bit],
+ ["stylesheet", bws.template_style],
+ ["index_bit", bws.template_index_bit]])
+ # inser the configuration
c.executemany ("INSERT INTO config (config_name, config_param) VALUES (?, ?); ",
[["Website URL", site_url],
["Website Title", site_title],
projects / biaweb_qt.git / blobdiff