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 list of articles (either full list or just in a category
211 def get_articles (dbname
, category_id
=None):
213 conn
= sqlite3
.connect (dbname
)
215 if category_id
== None:
216 c
.execute ("SELECT * FROM articles;")
218 c
.execute ("SELECT * FROM articles WHERE cid=?", (category_id
,))
223 except sqlite3
.Error
:
226 # Function to get list of categories and return a (category_id, category_name) array
227 def get_categories (dbname
):
229 conn
= sqlite3
.connect (dbname
)
231 c
.execute ("SELECT * FROM categories;")
236 except sqlite3
.Error
:
239 # function to create a new site database
240 def create_db (dbname
, site_title
, site_url
, keywords
, description
, copyright
,
243 if os
.path
.exists (dbname
):
249 conn
= sqlite3
.connect (dbname
)
253 c
.execute ("CREATE TABLE IF NOT EXISTS \
254 categories (cid INTEGER PRIMARY KEY, \
255 name TEXT, desc TEXT, \
257 c
.execute ("CREATE TABLE IF NOT EXISTS \
258 articles (aid INTEGER PRIMARY KEY, \
259 title TEXT, summary TEXT, keywords TEXT, \
260 content TEXT, cdate NUMERIC, mdate NUMERIC, cid NUMERIC, \
261 stub TEXT, rating NUMERIC);")
262 c
.execute ("CREATE TABLE IF NOT EXISTS \
263 config (config_name TEXT, config_param TEXT);")
265 c
.execute ("CREATE TABLE IF NOT EXISTS \
266 templates (template_name TEXT, template_content);")
268 # insert the templates into the newly created database
269 c
.executemany ("INSERT INTO templates (template_name, template_content) VALUES (?, ?);",
270 [["main_template", bws
.template_main
],
271 ["article_bit", bws
.template_article_bit
],
272 ["news_bit", bws
.template_news_item_bit
],
273 ["table_bit", bws
.template_table_bit
],
274 ["tablerow_bit", bws
.template_tablerow_bit
],
275 ["stylesheet", bws
.template_style
],
276 ["index_bit", bws
.template_index_bit
]])
278 # inser the configuration
279 c
.executemany ("INSERT INTO config (config_name, config_param) VALUES (?, ?); ",
280 [["Website URL", site_url
],
281 ["Website Title", site_title
],
282 ["Keywords", keywords
],
283 ["Description", description
],
284 ["No. of RSS items", num_rss
],
285 ["Destination path", dest_path
],
286 ["Copyright", copyright
]])
291 except sqlite3
.Error
: