X-Git-Url: https://harishankar.org/repos/?p=biaweb_qt.git;a=blobdiff_plain;f=biaweb_db.py;h=c2761f5885fc74a0d2c6ab6e601dd7454f111dfe;hp=576f2846b66d6b47c242b295a23c36de9078b2cb;hb=b517d55320f54925d862aca9dedb363b6d3b156d;hpb=7db1961b4f7513f9ad5b6b357a655e16903e7f83
diff --git a/biaweb_db.py b/biaweb_db.py
index 576f284..c2761f5 100644
--- a/biaweb_db.py
+++ b/biaweb_db.py
@@ -4,7 +4,132 @@
import sqlite3
import os
import os.path
-import pprint
+import time
+
+# 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 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):
@@ -19,6 +144,54 @@ def create_category (dbname, category_name, category_desc, category_stub):
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:
@@ -30,6 +203,7 @@ def get_articles (dbname, category_id=None):
c.execute ("SELECT * FROM articles WHERE cid=?", (category_id,))
conn.commit ()
rows = c.fetchall ()
+ conn.close ()
return rows
except sqlite3.Error:
return False
@@ -75,192 +249,192 @@ def create_db (dbname, site_title, site_url, keywords, description, copyright,
templates (template_name TEXT, template_content);")
template_main = """
-
-
-
- ${site_title}
-
-
-
-
-
-
-
-
-
-
- ${contents_bit}
-
-
-
-
- """
+
+
+
+${site_title}
+
+
+
+
+
+
+
+
+
+
+ ${contents_bit}
+
+
+
+
+"""
template_article_bit = """${article_title}
- Created: ${article_cdate} | Last modified: ${article_mdate}
- Rating: ${rating}
- ${article_contents}
- """
+Created: ${article_cdate} | Last modified: ${article_mdate}
+Rating: ${rating}
+${article_contents}
+"""
template_news_item_bit = """
- ${news_datetime}
- ${news_description}
- """
+${news_datetime}
+${news_description}
+"""
template_index_bit = """Welcome to ${site_name}
-
- Welcome to my site, ${site_name}.
-
- Latest Articles
- ${news_updates}
- """
+
+Welcome to my site, ${site_name}.
+
+Latest Articles
+${news_updates}
+"""
template_table_bit = """${category_title}
- ${category_desc}
-
-
-
- | Title |
- Created on |
- Rated |
-
-
-
- ${table_rows}
-
-
- """
+ ${category_desc}
+
+
+
+ | Title |
+ Created on |
+ Rated |
+
+
+
+ ${table_rows}
+
+
+"""
template_tablerow_bit = """
- | ${title} |
- ${created} |
- ${rating} |
-
- """
+ ${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;
- }"""
+ 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],