1 # BiaWeb Website content manager (c) 2010 V.Harishankar
2 # Database handling functions
9 import biaweb_strings
as bws
11 # function to get the template text for a particular template
12 def get_template_text (dbname
, template_name
):
14 conn
= sqlite3
.connect (dbname
)
16 c
.execute ("SELECT template_content FROM templates WHERE template_name=?",
25 # function to update the template
26 def update_template (dbname
, template_name
, template_str
):
28 conn
= sqlite3
.connect (dbname
)
30 c
.execute ("UPDATE templates SET template_content=? WHERE template_name=?",
31 (template_str
, template_name
))
38 # function to retrieve the templates from the database
39 def get_templates (dbname
):
41 conn
= sqlite3
.connect (dbname
)
43 c
.execute ("SELECT * FROM templates;")
51 # function to get an article from the database
52 def get_article (dbname
, artid
):
54 conn
= sqlite3
.connect (dbname
)
56 c
.execute ("SELECT * FROM articles WHERE aid=?;", (artid
,))
64 # function to create an article
65 def create_article (dbname
, title
, summary
, keywords
, content
, catid
, stub
, rating
):
67 creattime
= time
.time ()
70 conn
= sqlite3
.connect (dbname
)
72 c
.execute ("INSERT INTO articles (title, summary, keywords, content, cdate, mdate, cid, stub, rating) \
73 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);", (title
, summary
, keywords
, content
, creattime
,
74 modtime
, catid
, stub
, rating
))
81 # function to update an article
82 def update_article (dbname
, aid
, title
, summary
, keywords
, content
, catid
, stub
, rating
):
83 # modification time only to be updated
84 modtime
= time
.time ()
86 conn
= sqlite3
.connect (dbname
)
88 c
.execute ("UPDATE articles SET title=?, summary=?, keywords=?, content=?, mdate=?, cid=?, \
89 stub=?, rating=? WHERE aid=?;",
90 (title
, summary
, keywords
, content
, modtime
, catid
, stub
, rating
, aid
))
97 # function to delete an article
98 def delete_article (dbname
, artid
):
100 conn
= sqlite3
.connect (dbname
)
102 c
.execute ("DELETE FROM articles WHERE aid=?;", (artid
,))
106 except sqlite3
.Error
:
109 # function to get a category from the database
110 def get_category (dbname
, catid
):
112 conn
= sqlite3
.connect (dbname
)
114 c
.execute ("SELECT * FROM categories WHERE cid=?", (catid
,))
119 except sqlite3
.Error
:
122 # function to update a category
123 def update_category (dbname
, catid
, cat_name
, cat_desc
, cat_stub
):
125 conn
= sqlite3
.connect (dbname
)
127 c
.execute ("UPDATE categories SET name=?, desc=?, stub=? WHERE cid=?;",
128 (cat_name
, cat_desc
, cat_stub
, catid
))
132 except sqlite3
.Error
:
135 # function to remove a category
136 def remove_category (dbname
, catid
):
138 conn
= sqlite3
.connect (dbname
)
140 c
.execute ("DELETE FROM categories WHERE cid=?;", (catid
,))
141 c
.execute ("DELETE FROM articles WHERE cid=?;", (catid
,))
145 except sqlite3
.Error
:
149 # function to create a category
150 def create_category (dbname
, category_name
, category_desc
, category_stub
):
152 conn
= sqlite3
.connect (dbname
)
154 c
.execute ("INSERT INTO categories (name, desc, stub) VALUES (?, ?, ?);",
155 (category_name
, category_desc
, category_stub
))
159 except sqlite3
.Error
:
162 # function to set the configuration and update the database
163 def set_configuration (dbname
, site_title
, site_url
, keywords
, description
, copyright
,
166 conn
= sqlite3
.connect (dbname
)
168 c
.executemany ("UPDATE config SET config_param=? WHERE config_name=?;",
169 [[site_url
, "Website URL"],
170 [site_title
, "Website Title"],
171 [keywords
, "Keywords"],
172 [description
, "Description"],
173 [num_rss
, "No. of RSS items"],
174 [dest_path
, "Destination path"],
175 [copyright
, "Copyright"]])
179 except sqlite3
.Error
:
182 # function to get the existing site configuration and return it as a tuple
183 def get_configuration (dbname
):
185 conn
= sqlite3
.connect (dbname
)
187 c
.execute ("SELECT * FROM config;")
191 for name
, param
in recs
:
192 if name
== "Website URL":
194 elif name
== "Website Title":
195 website_title
= param
196 elif name
== "Keywords":
198 elif name
== "Description":
200 elif name
== "No. of RSS items":
201 num_rss
= int (param
)
202 elif name
== "Destination path":
204 elif name
== "Copyright":
206 return (website_url
, website_title
, keywords
, description
, num_rss
, destination
, copyright
)
207 except sqlite3
.Error
:
210 # Function to get the latest articles (inner join with categories to get category stub)
211 def site_latest_articles (dbname
, num_arts
):
213 conn
= sqlite3
.connect (dbname
)
215 c
.execute ("SELECT * FROM articles INNER JOIN categories ON articles.cid=categories.cid \
216 ORDER BY cdate DESC LIMIT ?;", (num_arts
,))
221 except sqlite3
.Error
:
224 # Function to get the best rated articles (inner join with categories to get category stub)
225 def site_get_bestrated (dbname
):
227 conn
= sqlite3
.connect (dbname
)
229 c
.execute ("SELECT * FROM articles INNER JOIN categories ON articles.cid=categories.cid \
230 ORDER BY rating DESC LIMIT 5;")
235 except sqlite3
.Error
:
238 # Function to get list of articles (either full list or just in a category
239 def get_articles (dbname
, category_id
=None):
241 conn
= sqlite3
.connect (dbname
)
243 if category_id
== None:
244 c
.execute ("SELECT * FROM articles;")
246 c
.execute ("SELECT * FROM articles WHERE cid=?", (category_id
,))
251 except sqlite3
.Error
:
254 # Function to get list of categories and return a (category_id, category_name) array
255 def get_categories (dbname
):
257 conn
= sqlite3
.connect (dbname
)
259 c
.execute ("SELECT * FROM categories;")
264 except sqlite3
.Error
:
267 # function to create a new site database
268 def create_db (dbname
, site_title
, site_url
, keywords
, description
, copyright
,
271 if os
.path
.exists (dbname
):
277 conn
= sqlite3
.connect (dbname
)
281 c
.execute ("CREATE TABLE IF NOT EXISTS \
282 categories (cid INTEGER PRIMARY KEY, \
283 name TEXT, desc TEXT, \
285 c
.execute ("CREATE TABLE IF NOT EXISTS \
286 articles (aid INTEGER PRIMARY KEY, \
287 title TEXT, summary TEXT, keywords TEXT, \
288 content TEXT, cdate NUMERIC, mdate NUMERIC, cid NUMERIC, \
289 stub TEXT, rating NUMERIC);")
290 c
.execute ("CREATE TABLE IF NOT EXISTS \
291 config (config_name TEXT, config_param TEXT);")
293 c
.execute ("CREATE TABLE IF NOT EXISTS \
294 templates (template_name TEXT, template_content);")
296 # insert the templates into the newly created database
297 c
.executemany ("INSERT INTO templates (template_name, template_content) VALUES (?, ?);",
298 [["main_template", bws
.template_main
],
299 ["article_bit", bws
.template_article_bit
],
300 ["news_bit", bws
.template_news_item_bit
],
301 ["table_bit", bws
.template_table_bit
],
302 ["tablerow_bit", bws
.template_tablerow_bit
],
303 ["stylesheet", bws
.template_style
],
304 ["index_bit", bws
.template_index_bit
]])
306 # inser the configuration
307 c
.executemany ("INSERT INTO config (config_name, config_param) VALUES (?, ?); ",
308 [["Website URL", site_url
],
309 ["Website Title", site_title
],
310 ["Keywords", keywords
],
311 ["Description", description
],
312 ["No. of RSS items", num_rss
],
313 ["Destination path", dest_path
],
314 ["Copyright", copyright
]])
319 except sqlite3
.Error
: