c1d5e60db222804c0eac91012849a2982f8a0dc2
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 articles from a category or all articles (inner join with categories to get category stub)
211 def site_articles (dbname
, catid
= None):
213 conn
= sqlite3
.connect (dbname
)
216 c
.execute ("SELECT * FROM articles INNER JOIN categories ON articles.cid=categories.cid \
217 ORDER BY cdate DESC;")
219 c
.execute ("SELECT * FROM articles INNER JOIN categories ON articles.cid=categories.cid \
220 WHERE articles.cid=? ORDER BY cdate DESC;", (catid
,))
225 except sqlite3
.Error
:
228 # Function to get the latest articles (inner join with categories to get category stub)
229 def site_latest_articles (dbname
, num_arts
):
231 conn
= sqlite3
.connect (dbname
)
233 c
.execute ("SELECT * FROM articles INNER JOIN categories ON articles.cid=categories.cid \
234 ORDER BY cdate DESC LIMIT ?;", (num_arts
,))
239 except sqlite3
.Error
:
242 # Function to get the best rated articles (inner join with categories to get category stub)
243 def site_get_bestrated (dbname
):
245 conn
= sqlite3
.connect (dbname
)
247 c
.execute ("SELECT * FROM articles INNER JOIN categories ON articles.cid=categories.cid \
248 ORDER BY rating DESC LIMIT 5;")
253 except sqlite3
.Error
:
256 # Function to get list of articles (either full list or just in a category
257 def get_articles (dbname
, category_id
=None):
259 conn
= sqlite3
.connect (dbname
)
261 if category_id
== None:
262 c
.execute ("SELECT * FROM articles;")
264 c
.execute ("SELECT * FROM articles WHERE cid=?", (category_id
,))
269 except sqlite3
.Error
:
272 # Function to get list of categories and return a (category_id, category_name) array
273 def get_categories (dbname
):
275 conn
= sqlite3
.connect (dbname
)
277 c
.execute ("SELECT * FROM categories;")
282 except sqlite3
.Error
:
285 # function to create a new site database
286 def create_db (dbname
, site_title
, site_url
, keywords
, description
, copyright
,
289 if os
.path
.exists (dbname
):
295 conn
= sqlite3
.connect (dbname
)
299 c
.execute ("CREATE TABLE IF NOT EXISTS \
300 categories (cid INTEGER PRIMARY KEY, \
301 name TEXT, desc TEXT, \
303 c
.execute ("CREATE TABLE IF NOT EXISTS \
304 articles (aid INTEGER PRIMARY KEY, \
305 title TEXT, summary TEXT, keywords TEXT, \
306 content TEXT, cdate NUMERIC, mdate NUMERIC, cid NUMERIC, \
307 stub TEXT, rating NUMERIC);")
308 c
.execute ("CREATE TABLE IF NOT EXISTS \
309 config (config_name TEXT, config_param TEXT);")
311 c
.execute ("CREATE TABLE IF NOT EXISTS \
312 templates (template_name TEXT, template_content);")
314 # insert the templates into the newly created database
315 c
.executemany ("INSERT INTO templates (template_name, template_content) VALUES (?, ?);",
316 [["main_template", bws
.template_main
],
317 ["article_bit", bws
.template_article_bit
],
318 ["news_bit", bws
.template_news_item_bit
],
319 ["table_bit", bws
.template_table_bit
],
320 ["tablerow_bit", bws
.template_tablerow_bit
],
321 ["stylesheet", bws
.template_style
],
322 ["index_bit", bws
.template_index_bit
]])
324 # inser the configuration
325 c
.executemany ("INSERT INTO config (config_name, config_param) VALUES (?, ?); ",
326 [["Website URL", site_url
],
327 ["Website Title", site_title
],
328 ["Keywords", keywords
],
329 ["Description", description
],
330 ["No. of RSS items", num_rss
],
331 ["Destination path", dest_path
],
332 ["Copyright", copyright
]])
337 except sqlite3
.Error
: