root / pykota / trunk / pykota / storages / sql.py @ 2054

Revision 2054, 38.8 kB (checked in by jalet, 19 years ago)

Big database structure changes. Upgrade script is now included as well as
the new LDAP schema.
Introduction of the -o | --overcharge command line option to edpykota.
The output of repykota is more complete, but doesn't fit in 80 columns anymore.
Introduction of the new 'maxdenybanners' directive.

  • Property svn:eol-style set to native
  • Property svn:keywords set to Author Date Id Revision
Line 
1# PyKota
2# -*- coding: ISO-8859-15 -*-
3#
4# PyKota : Print Quotas for CUPS and LPRng
5#
6# (c) 2003-2004 Jerome Alet <alet@librelogiciel.com>
7# This program is free software; you can redistribute it and/or modify
8# it under the terms of the GNU General Public License as published by
9# the Free Software Foundation; either version 2 of the License, or
10# (at your option) any later version.
11#
12# This program is distributed in the hope that it will be useful,
13# but WITHOUT ANY WARRANTY; without even the implied warranty of
14# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
15# GNU General Public License for more details.
16#
17# You should have received a copy of the GNU General Public License
18# along with this program; if not, write to the Free Software
19# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307, USA.
20#
21# $Id$
22#
23# $Log$
24# Revision 1.65  2005/02/13 22:02:29  jalet
25# Big database structure changes. Upgrade script is now included as well as
26# the new LDAP schema.
27# Introduction of the -o | --overcharge command line option to edpykota.
28# The output of repykota is more complete, but doesn't fit in 80 columns anymore.
29# Introduction of the new 'maxdenybanners' directive.
30#
31# Revision 1.64  2005/01/18 19:47:50  jalet
32# Big bug fix wrt the datelimit attribute
33#
34# Revision 1.63  2005/01/01 08:16:17  jalet
35# Fixed a problem which occured when 'limitby' was unset in the PostgreSQL
36# database
37#
38# Revision 1.62  2004/12/21 16:19:44  jalet
39# Fixed the problem with dumpykota's filtering of printers groups membership
40#
41# Revision 1.61  2004/12/21 15:49:59  jalet
42# The dumpykota command now supports extended filtering capabilities with
43# the PostgreSQL backend. LDAP doesn't yet support such possibilities.
44#
45# Revision 1.60  2004/12/21 14:45:31  jalet
46# Prepared dumpykota to accept the new --filter command line option. Some
47# additionnal work needs to be done in the backends though.
48#
49# Revision 1.59  2004/10/25 14:12:25  jalet
50# For URGENT legal reasons (Italy), a new "privacy" directive was added to pykota.conf
51# to hide print jobs' title, filename, and options.
52#
53# Revision 1.58  2004/10/12 08:58:53  jalet
54# Now warnpykota only warns users who have already printed, to not confuse
55# users who have just opened their account.
56#
57# Revision 1.57  2004/10/10 10:12:21  jalet
58# Improved SQL queries for groups. Same work has to be done for groups print quotas.
59#
60# Revision 1.56  2004/10/07 21:14:28  jalet
61# Hopefully final fix for data encoding to and from the database
62#
63# Revision 1.55  2004/10/07 09:37:53  jalet
64# Fixes recently introduced bug wrt users groups (was it three days ago ?)
65#
66# Revision 1.54  2004/10/05 10:05:04  jalet
67# UnicodeEncodeError isn't defined in Python2.1
68#
69# Revision 1.53  2004/10/05 09:59:20  jalet
70# Restore compatibility with Python 2.1
71#
72# Revision 1.52  2004/10/04 22:23:54  jalet
73# Charset conversions for dumps from the PostgreSQL backend
74#
75# Revision 1.51  2004/10/04 16:11:38  jalet
76# Now outputs page counters when dumping user groups quotas
77#
78# Revision 1.50  2004/10/04 16:01:15  jalet
79# More complete dumps for groups and groups quotas
80#
81# Revision 1.49  2004/10/02 13:33:13  jalet
82# Some work done of user's charset handling in database dumps.
83#
84# Revision 1.48  2004/10/02 05:48:56  jalet
85# Should now correctly deal with charsets both when storing into databases and when
86# retrieving datas. Works with both PostgreSQL and LDAP.
87#
88# Revision 1.47  2004/09/15 07:26:20  jalet
89# Data dumps are now ordered by entry creation date if applicable.
90# Now dumpykota exits with a message when there's a broken pipe like
91# in dumpykota --data history | head -3
92#
93# Revision 1.46  2004/09/15 06:58:25  jalet
94# User groups membership and printer groups membership can now be dumped too
95#
96# Revision 1.45  2004/09/14 22:29:13  jalet
97# First version of dumpykota. Works fine but only with PostgreSQL backend
98# for now.
99#
100# Revision 1.44  2004/09/10 21:32:54  jalet
101# Small fixes for incomplete entry intialization
102#
103# Revision 1.43  2004/07/01 17:45:49  jalet
104# Added code to handle the description field for printers
105#
106# Revision 1.42  2004/06/08 17:44:43  jalet
107# Payment now gets deleted when the user is deleted
108#
109# Revision 1.41  2004/06/05 22:03:50  jalet
110# Payments history is now stored in database
111#
112# Revision 1.40  2004/06/03 23:14:11  jalet
113# Now stores the job's size in bytes in the database.
114# Preliminary work on payments storage : database schemas are OK now,
115# but no code to store payments yet.
116# Removed schema picture, not relevant anymore.
117#
118# Revision 1.39  2004/05/26 14:50:12  jalet
119# First try at saving the job-originating-hostname in the database
120#
121# Revision 1.38  2004/05/06 12:37:47  jalet
122# pkpgcounter : comments
123# pkprinters : when --add is used, existing printers are now skipped.
124#
125# Revision 1.37  2004/02/23 22:53:21  jalet
126# Don't retrieve data when it's not needed, to avoid database queries
127#
128# Revision 1.36  2004/02/04 13:24:41  jalet
129# pkprinters can now remove printers from printers groups.
130#
131# Revision 1.35  2004/02/04 11:17:00  jalet
132# pkprinters command line tool added.
133#
134# Revision 1.34  2004/02/02 22:44:16  jalet
135# Preliminary work on Relationnal Database Independance via DB-API 2.0
136#
137#
138#
139
140from types import StringType
141from pykota.storage import PyKotaStorageError,BaseStorage,StorageObject,StorageUser,StorageGroup,StoragePrinter,StorageJob,StorageLastJob,StorageUserPQuota,StorageGroupPQuota
142
143class SQLStorage :
144    def prepareRawResult(self, result) :
145        """Prepares a raw result by including the headers."""
146        if result.ntuples() > 0 :
147            entries = [result.listfields()]
148            entries.extend(result.getresult())
149            nbfields = len(entries[0])
150            for i in range(1, len(entries)) :
151                fields = list(entries[i])
152                for j in range(nbfields) :
153                    field = fields[j]
154                    if type(field) == StringType :
155                        fields[j] = self.databaseToUserCharset(field) 
156                entries[i] = tuple(fields)   
157            return entries
158       
159    def createFilter(self, only) :   
160        """Returns the appropriate SQL filter."""
161        if only :
162            expressions = []
163            for (k, v) in only.items() :
164                expressions.append("%s=%s" % (k, self.doQuote(v)))
165            return " AND ".join(expressions)     
166        return ""       
167       
168    def extractPrinters(self, extractonly={}) :
169        """Extracts all printer records."""
170        thefilter = self.createFilter(extractonly)
171        if thefilter :
172            thefilter = "WHERE %s" % thefilter
173        result = self.doRawSearch("SELECT * FROM printers %s ORDER BY id ASC" % thefilter)
174        return self.prepareRawResult(result)
175       
176    def extractUsers(self, extractonly={}) :
177        """Extracts all user records."""
178        thefilter = self.createFilter(extractonly)
179        if thefilter :
180            thefilter = "WHERE %s" % thefilter
181        result = self.doRawSearch("SELECT * FROM users %s ORDER BY id ASC" % thefilter)
182        return self.prepareRawResult(result)
183       
184    def extractGroups(self, extractonly={}) :
185        """Extracts all group records."""
186        thefilter = self.createFilter(extractonly)
187        if thefilter :
188            thefilter = "WHERE %s" % thefilter
189        result = self.doRawSearch("SELECT groups.*,COALESCE(SUM(balance), 0) AS balance, COALESCE(SUM(lifetimepaid), 0) as lifetimepaid FROM groups LEFT OUTER JOIN users ON users.id IN (SELECT userid FROM groupsmembers WHERE groupid=groups.id) %s GROUP BY groups.id,groups.groupname,groups.limitby ORDER BY groups.id ASC" % thefilter)
190        return self.prepareRawResult(result)
191       
192    def extractPayments(self, extractonly={}) :
193        """Extracts all payment records."""
194        thefilter = self.createFilter(extractonly)
195        if thefilter :
196            thefilter = "AND %s" % thefilter
197        result = self.doRawSearch("SELECT username,payments.* FROM users,payments WHERE users.id=payments.userid %s ORDER BY payments.id ASC" % thefilter)
198        return self.prepareRawResult(result)
199       
200    def extractUpquotas(self, extractonly={}) :
201        """Extracts all userpquota records."""
202        thefilter = self.createFilter(extractonly)
203        if thefilter :
204            thefilter = "AND %s" % thefilter
205        result = self.doRawSearch("SELECT users.username,printers.printername,userpquota.* FROM users,printers,userpquota WHERE users.id=userpquota.userid AND printers.id=userpquota.printerid %s ORDER BY userpquota.id ASC" % thefilter)
206        return self.prepareRawResult(result)
207       
208    def extractGpquotas(self, extractonly={}) :
209        """Extracts all grouppquota records."""
210        thefilter = self.createFilter(extractonly)
211        if thefilter :
212            thefilter = "AND %s" % thefilter
213        result = self.doRawSearch("SELECT groups.groupname,printers.printername,grouppquota.*,coalesce(sum(pagecounter), 0) AS pagecounter,coalesce(sum(lifepagecounter), 0) AS lifepagecounter FROM groups,printers,grouppquota,userpquota WHERE groups.id=grouppquota.groupid AND printers.id=grouppquota.printerid AND userpquota.printerid=grouppquota.printerid AND userpquota.userid IN (SELECT userid FROM groupsmembers WHERE groupsmembers.groupid=grouppquota.groupid) %s GROUP BY grouppquota.id,grouppquota.groupid,grouppquota.printerid,grouppquota.softlimit,grouppquota.hardlimit,grouppquota.datelimit,groups.groupname,printers.printername ORDER BY grouppquota.id" % thefilter)
214        return self.prepareRawResult(result)
215       
216    def extractUmembers(self, extractonly={}) :
217        """Extracts all user groups members."""
218        thefilter = self.createFilter(extractonly)
219        if thefilter :
220            thefilter = "AND %s" % thefilter
221        result = self.doRawSearch("SELECT groups.groupname, users.username, groupsmembers.* FROM groups,users,groupsmembers WHERE users.id=groupsmembers.userid AND groups.id=groupsmembers.groupid %s ORDER BY groupsmembers.groupid, groupsmembers.userid ASC" % thefilter)
222        return self.prepareRawResult(result)
223       
224    def extractPmembers(self, extractonly={}) :
225        """Extracts all printer groups members."""
226        for (k, v) in extractonly.items() :
227            if k == "pgroupname" :
228                del extractonly[k]
229                extractonly["p1.printername"] = v
230            elif k == "printername" :
231                del extractonly[k]
232                extractonly["p2.printername"] = v
233        thefilter = self.createFilter(extractonly)
234        if thefilter :
235            thefilter = "AND %s" % thefilter
236        result = self.doRawSearch("SELECT p1.printername as pgroupname, p2.printername as printername, printergroupsmembers.* FROM printers p1, printers p2, printergroupsmembers WHERE p1.id=printergroupsmembers.groupid AND p2.id=printergroupsmembers.printerid %s ORDER BY printergroupsmembers.groupid, printergroupsmembers.printerid ASC" % thefilter)
237        return self.prepareRawResult(result)
238       
239    def extractHistory(self, extractonly={}) :
240        """Extracts all jobhistory records."""
241        thefilter = self.createFilter(extractonly)
242        if thefilter :
243            thefilter = "AND %s" % thefilter
244        result = self.doRawSearch("SELECT users.username,printers.printername,jobhistory.* FROM users,printers,jobhistory WHERE users.id=jobhistory.userid AND printers.id=jobhistory.printerid %s ORDER BY jobhistory.id ASC" % thefilter)
245        return self.prepareRawResult(result)
246       
247    def getAllUsersNames(self) :   
248        """Extracts all user names."""
249        usernames = []
250        result = self.doSearch("SELECT username FROM users")
251        if result :
252            usernames = [record["username"] for record in result]
253        return usernames
254       
255    def getAllGroupsNames(self) :   
256        """Extracts all group names."""
257        groupnames = []
258        result = self.doSearch("SELECT groupname FROM groups")
259        if result :
260            groupnames = [record["groupname"] for record in result]
261        return groupnames
262       
263    def getUserNbJobsFromHistory(self, user) :
264        """Returns the number of jobs the user has in history."""
265        result = self.doSearch("SELECT COUNT(*) FROM jobhistory WHERE userid=%s" % self.doQuote(user.ident))
266        if result :
267            return result[0]["count"]
268        return 0
269       
270    def getUserFromBackend(self, username) :   
271        """Extracts user information given its name."""
272        user = StorageUser(self, username)
273        result = self.doSearch("SELECT * FROM users WHERE username=%s LIMIT 1" % self.doQuote(username))
274        if result :
275            fields = result[0]
276            user.ident = fields.get("id")
277            user.Name = fields.get("username", username)
278            user.LimitBy = fields.get("limitby") or "quota"
279            user.AccountBalance = fields.get("balance")
280            user.LifeTimePaid = fields.get("lifetimepaid")
281            user.Email = fields.get("email")
282            user.OverCharge = fields.get("overcharge", 1.0)
283            user.Exists = 1
284        return user
285       
286    def getGroupFromBackend(self, groupname) :   
287        """Extracts group information given its name."""
288        group = StorageGroup(self, groupname)
289        result = self.doSearch("SELECT groups.*,COALESCE(SUM(balance), 0.0) AS balance, COALESCE(SUM(lifetimepaid), 0.0) AS lifetimepaid FROM groups LEFT OUTER JOIN users ON users.id IN (SELECT userid FROM groupsmembers WHERE groupid=groups.id) WHERE groupname=%s GROUP BY groups.id,groups.groupname,groups.limitby LIMIT 1" % self.doQuote(groupname))
290        if result :
291            fields = result[0]
292            group.ident = fields.get("id")
293            group.Name = fields.get("groupname", groupname)
294            group.LimitBy = fields.get("limitby") or "quota"
295            group.AccountBalance = fields.get("balance")
296            group.LifeTimePaid = fields.get("lifetimepaid")
297            group.Exists = 1
298        return group
299       
300    def getPrinterFromBackend(self, printername) :       
301        """Extracts printer information given its name."""
302        printer = StoragePrinter(self, printername)
303        result = self.doSearch("SELECT * FROM printers WHERE printername=%s LIMIT 1" % self.doQuote(printername))
304        if result :
305            fields = result[0]
306            printer.ident = fields.get("id")
307            printer.Name = fields.get("printername", printername)
308            printer.PricePerJob = fields.get("priceperjob") or 0.0
309            printer.PricePerPage = fields.get("priceperpage") or 0.0
310            printer.Description = self.databaseToUserCharset(fields.get("description") or "")
311            printer.Exists = 1
312        return printer   
313       
314    def getUserPQuotaFromBackend(self, user, printer) :       
315        """Extracts a user print quota."""
316        userpquota = StorageUserPQuota(self, user, printer)
317        if printer.Exists and user.Exists :
318            result = self.doSearch("SELECT id, lifepagecounter, pagecounter, softlimit, hardlimit, datelimit FROM userpquota WHERE userid=%s AND printerid=%s" % (self.doQuote(user.ident), self.doQuote(printer.ident)))
319            if result :
320                fields = result[0]
321                userpquota.ident = fields.get("id")
322                userpquota.PageCounter = fields.get("pagecounter")
323                userpquota.LifePageCounter = fields.get("lifepagecounter")
324                userpquota.SoftLimit = fields.get("softlimit")
325                userpquota.HardLimit = fields.get("hardlimit")
326                userpquota.DateLimit = fields.get("datelimit")
327                userpquota.WarnCount = fields.get("warncount")
328                userpquota.Exists = 1
329        return userpquota
330       
331    def getGroupPQuotaFromBackend(self, group, printer) :       
332        """Extracts a group print quota."""
333        grouppquota = StorageGroupPQuota(self, group, printer)
334        if group.Exists :
335            result = self.doSearch("SELECT id, softlimit, hardlimit, datelimit FROM grouppquota WHERE groupid=%s AND printerid=%s" % (self.doQuote(group.ident), self.doQuote(printer.ident)))
336            if result :
337                fields = result[0]
338                grouppquota.ident = fields.get("id")
339                grouppquota.SoftLimit = fields.get("softlimit")
340                grouppquota.HardLimit = fields.get("hardlimit")
341                grouppquota.DateLimit = fields.get("datelimit")
342                result = self.doSearch("SELECT SUM(lifepagecounter) AS lifepagecounter, SUM(pagecounter) AS pagecounter FROM userpquota WHERE printerid=%s AND userid IN (SELECT userid FROM groupsmembers WHERE groupid=%s)" % (self.doQuote(printer.ident), self.doQuote(group.ident)))
343                if result :
344                    fields = result[0]
345                    grouppquota.PageCounter = fields.get("pagecounter") or 0
346                    grouppquota.LifePageCounter = fields.get("lifepagecounter") or 0
347                grouppquota.Exists = 1
348        return grouppquota
349       
350    def getPrinterLastJobFromBackend(self, printer) :       
351        """Extracts a printer's last job information."""
352        lastjob = StorageLastJob(self, printer)
353        result = self.doSearch("SELECT jobhistory.id, jobid, userid, username, pagecounter, jobsize, jobprice, filename, title, copies, options, hostname, jobdate FROM jobhistory, users WHERE printerid=%s AND userid=users.id ORDER BY jobdate DESC LIMIT 1" % self.doQuote(printer.ident))
354        if result :
355            fields = result[0]
356            lastjob.ident = fields.get("id")
357            lastjob.JobId = fields.get("jobid")
358            lastjob.UserName = fields.get("username")
359            lastjob.PrinterPageCounter = fields.get("pagecounter")
360            lastjob.JobSize = fields.get("jobsize")
361            lastjob.JobPrice = fields.get("jobprice")
362            lastjob.JobAction = fields.get("action")
363            lastjob.JobFileName = self.databaseToUserCharset(fields.get("filename") or "") 
364            lastjob.JobTitle = self.databaseToUserCharset(fields.get("title") or "") 
365            lastjob.JobCopies = fields.get("copies")
366            lastjob.JobOptions = self.databaseToUserCharset(fields.get("options") or "") 
367            lastjob.JobDate = fields.get("jobdate")
368            lastjob.JobHostName = fields.get("hostname")
369            lastjob.JobSizeBytes = fields.get("jobsizebytes")
370            lastjob.JobMD5Sum = fields.get("md5sum")
371            lastjob.JobPages = fields.get("pages")
372            lastjob.JobBillingCode = fields.get("billingcode")
373            lastjob.Exists = 1
374        return lastjob
375           
376    def getGroupMembersFromBackend(self, group) :       
377        """Returns the group's members list."""
378        groupmembers = []
379        result = self.doSearch("SELECT * FROM groupsmembers JOIN users ON groupsmembers.userid=users.id WHERE groupid=%s" % self.doQuote(group.ident))
380        if result :
381            for record in result :
382                user = StorageUser(self, record.get("username"))
383                user.ident = record.get("userid")
384                user.LimitBy = record.get("limitby") or "quota"
385                user.AccountBalance = record.get("balance")
386                user.LifeTimePaid = record.get("lifetimepaid")
387                user.Email = record.get("email")
388                user.OverCharge = record.get("overcharge")
389                user.Exists = 1
390                groupmembers.append(user)
391                self.cacheEntry("USERS", user.Name, user)
392        return groupmembers       
393       
394    def getUserGroupsFromBackend(self, user) :       
395        """Returns the user's groups list."""
396        groups = []
397        result = self.doSearch("SELECT groupname FROM groupsmembers JOIN groups ON groupsmembers.groupid=groups.id WHERE userid=%s" % self.doQuote(user.ident))
398        if result :
399            for record in result :
400                groups.append(self.getGroup(record.get("groupname")))
401        return groups       
402       
403    def getParentPrintersFromBackend(self, printer) :   
404        """Get all the printer groups this printer is a member of."""
405        pgroups = []
406        result = self.doSearch("SELECT groupid,printername FROM printergroupsmembers JOIN printers ON groupid=id WHERE printerid=%s" % self.doQuote(printer.ident))
407        if result :
408            for record in result :
409                if record["groupid"] != printer.ident : # in case of integrity violation
410                    parentprinter = self.getPrinter(record.get("printername"))
411                    if parentprinter.Exists :
412                        pgroups.append(parentprinter)
413        return pgroups
414       
415    def getMatchingPrinters(self, printerpattern) :
416        """Returns the list of all printers for which name matches a certain pattern."""
417        printers = []
418        # We 'could' do a SELECT printername FROM printers WHERE printername LIKE ...
419        # but we don't because other storages semantics may be different, so every
420        # storage should use fnmatch to match patterns and be storage agnostic
421        result = self.doSearch("SELECT * FROM printers")
422        if result :
423            for record in result :
424                if self.tool.matchString(record["printername"], printerpattern.split(",")) :
425                    printer = StoragePrinter(self, record["printername"])
426                    printer.ident = record.get("id")
427                    printer.PricePerJob = record.get("priceperjob") or 0.0
428                    printer.PricePerPage = record.get("priceperpage") or 0.0
429                    printer.Description = self.databaseToUserCharset(record.get("description") or "") 
430                    printer.Exists = 1
431                    printers.append(printer)
432                    self.cacheEntry("PRINTERS", printer.Name, printer)
433        return printers       
434       
435    def getPrinterUsersAndQuotas(self, printer, names=["*"]) :       
436        """Returns the list of users who uses a given printer, along with their quotas."""
437        usersandquotas = []
438        result = self.doSearch("SELECT users.id as uid,username,balance,lifetimepaid,limitby,email,overcharge,userpquota.id,lifepagecounter,pagecounter,softlimit,hardlimit,datelimit,warncount FROM users JOIN userpquota ON users.id=userpquota.userid AND printerid=%s ORDER BY username ASC" % self.doQuote(printer.ident))
439        if result :
440            for record in result :
441                if self.tool.matchString(record.get("username"), names) :
442                    user = StorageUser(self, record.get("username"))
443                    user.ident = record.get("uid")
444                    user.LimitBy = record.get("limitby") or "quota"
445                    user.AccountBalance = record.get("balance")
446                    user.LifeTimePaid = record.get("lifetimepaid")
447                    user.Email = record.get("email") 
448                    user.OverCharge = record.get("overcharge")
449                    user.Exists = 1
450                    userpquota = StorageUserPQuota(self, user, printer)
451                    userpquota.ident = record.get("id")
452                    userpquota.PageCounter = record.get("pagecounter")
453                    userpquota.LifePageCounter = record.get("lifepagecounter")
454                    userpquota.SoftLimit = record.get("softlimit")
455                    userpquota.HardLimit = record.get("hardlimit")
456                    userpquota.DateLimit = record.get("datelimit")
457                    userpquota.WarnCount = record.get("warncount")
458                    userpquota.Exists = 1
459                    usersandquotas.append((user, userpquota))
460                    self.cacheEntry("USERS", user.Name, user)
461                    self.cacheEntry("USERPQUOTAS", "%s@%s" % (user.Name, printer.Name), userpquota)
462        return usersandquotas
463               
464    def getPrinterGroupsAndQuotas(self, printer, names=["*"]) :       
465        """Returns the list of groups which uses a given printer, along with their quotas."""
466        groupsandquotas = []
467        result = self.doSearch("SELECT groupname FROM groups JOIN grouppquota ON groups.id=grouppquota.groupid AND printerid=%s ORDER BY groupname ASC" % self.doQuote(printer.ident))
468        if result :
469            for record in result :
470                if self.tool.matchString(record.get("groupname"), names) :
471                    group = self.getGroup(record.get("groupname"))
472                    grouppquota = self.getGroupPQuota(group, printer)
473                    groupsandquotas.append((group, grouppquota))
474        return groupsandquotas
475       
476    def addPrinter(self, printername) :       
477        """Adds a printer to the quota storage, returns it."""
478        self.doModify("INSERT INTO printers (printername) VALUES (%s)" % self.doQuote(printername))
479        return self.getPrinter(printername)
480       
481    def addUser(self, user) :       
482        """Adds a user to the quota storage, returns its id."""
483        self.doModify("INSERT INTO users (username, limitby, balance, lifetimepaid, email, overcharge) VALUES (%s, %s, %s, %s, %s, %s)" % (self.doQuote(user.Name), self.doQuote(user.LimitBy or 'quota'), self.doQuote(user.AccountBalance or 0.0), self.doQuote(user.LifeTimePaid or 0.0), self.doQuote(user.Email), self.doQuote(user.OverCharge)))
484        return self.getUser(user.Name)
485       
486    def addGroup(self, group) :       
487        """Adds a group to the quota storage, returns its id."""
488        self.doModify("INSERT INTO groups (groupname, limitby) VALUES (%s, %s)" % (self.doQuote(group.Name), self.doQuote(group.LimitBy or "quota")))
489        return self.getGroup(group.Name)
490
491    def addUserToGroup(self, user, group) :   
492        """Adds an user to a group."""
493        result = self.doSearch("SELECT COUNT(*) AS mexists FROM groupsmembers WHERE groupid=%s AND userid=%s" % (self.doQuote(group.ident), self.doQuote(user.ident)))
494        try :
495            mexists = int(result[0].get("mexists"))
496        except (IndexError, TypeError) :   
497            mexists = 0
498        if not mexists :   
499            self.doModify("INSERT INTO groupsmembers (groupid, userid) VALUES (%s, %s)" % (self.doQuote(group.ident), self.doQuote(user.ident)))
500           
501    def addUserPQuota(self, user, printer) :
502        """Initializes a user print quota on a printer."""
503        self.doModify("INSERT INTO userpquota (userid, printerid) VALUES (%s, %s)" % (self.doQuote(user.ident), self.doQuote(printer.ident)))
504        return self.getUserPQuota(user, printer)
505       
506    def addGroupPQuota(self, group, printer) :
507        """Initializes a group print quota on a printer."""
508        self.doModify("INSERT INTO grouppquota (groupid, printerid) VALUES (%s, %s)" % (self.doQuote(group.ident), self.doQuote(printer.ident)))
509        return self.getGroupPQuota(group, printer)
510       
511    def writePrinterPrices(self, printer) :   
512        """Write the printer's prices back into the storage."""
513        self.doModify("UPDATE printers SET priceperpage=%s, priceperjob=%s WHERE id=%s" % (self.doQuote(printer.PricePerPage), self.doQuote(printer.PricePerJob), self.doQuote(printer.ident)))
514       
515    def writePrinterDescription(self, printer) :   
516        """Write the printer's description back into the storage."""
517        description = self.userCharsetToDatabase(printer.Description)
518        self.doModify("UPDATE printers SET description=%s WHERE id=%s" % (self.doQuote(description), self.doQuote(printer.ident)))
519       
520    def writeUserOverCharge(self, user, factor) :
521        """Sets the user's overcharging coefficient."""
522        self.doModify("UPDATE users SET overcharge=%s WHERE id=%s" % (self.doQuote(factor), self.doQuote(user.ident)))
523       
524    def writeUserLimitBy(self, user, limitby) :   
525        """Sets the user's limiting factor."""
526        self.doModify("UPDATE users SET limitby=%s WHERE id=%s" % (self.doQuote(limitby), self.doQuote(user.ident)))
527       
528    def writeGroupLimitBy(self, group, limitby) :   
529        """Sets the group's limiting factor."""
530        self.doModify("UPDATE groups SET limitby=%s WHERE id=%s" % (self.doQuote(limitby), self.doQuote(group.ident)))
531       
532    def writeUserPQuotaDateLimit(self, userpquota, datelimit) :   
533        """Sets the date limit permanently for a user print quota."""
534        self.doModify("UPDATE userpquota SET datelimit=%s WHERE id=%s" % (self.doQuote(datelimit), self.doQuote(userpquota.ident)))
535           
536    def writeGroupPQuotaDateLimit(self, grouppquota, datelimit) :   
537        """Sets the date limit permanently for a group print quota."""
538        self.doModify("UPDATE grouppquota SET datelimit=%s WHERE id=%s" % (self.doQuote(datelimit), self.doQuote(grouppquota.ident)))
539       
540    def increaseUserPQuotaPagesCounters(self, userpquota, nbpages) :   
541        """Increase page counters for a user print quota."""
542        self.doModify("UPDATE userpquota SET pagecounter=pagecounter + %s,lifepagecounter=lifepagecounter + %s WHERE id=%s" % (self.doQuote(nbpages), self.doQuote(nbpages), self.doQuote(userpquota.ident)))
543       
544    def writeUserPQuotaPagesCounters(self, userpquota, newpagecounter, newlifepagecounter) :   
545        """Sets the new page counters permanently for a user print quota."""
546        self.doModify("UPDATE userpquota SET pagecounter=%s, lifepagecounter=%s, warncount=0, datelimit=NULL WHERE id=%s" % (self.doQuote(newpagecounter), self.doQuote(newlifepagecounter), self.doQuote(userpquota.ident)))
547       
548    def decreaseUserAccountBalance(self, user, amount) :   
549        """Decreases user's account balance from an amount."""
550        self.doModify("UPDATE users SET balance=balance - %s WHERE id=%s" % (self.doQuote(amount), self.doQuote(user.ident)))
551       
552    def writeUserAccountBalance(self, user, newbalance, newlifetimepaid=None) :   
553        """Sets the new account balance and eventually new lifetime paid."""
554        if newlifetimepaid is not None :
555            self.doModify("UPDATE users SET balance=%s, lifetimepaid=%s WHERE id=%s" % (self.doQuote(newbalance), self.doQuote(newlifetimepaid), self.doQuote(user.ident)))
556        else :   
557            self.doModify("UPDATE users SET balance=%s WHERE id=%s" % (self.doQuote(newbalance), self.doQuote(user.ident)))
558           
559    def writeNewPayment(self, user, amount) :       
560        """Adds a new payment to the payments history."""
561        self.doModify("INSERT INTO payments (userid, amount) VALUES (%s, %s)" % (self.doQuote(user.ident), self.doQuote(amount)))
562       
563    def writeLastJobSize(self, lastjob, jobsize, jobprice) :       
564        """Sets the last job's size permanently."""
565        self.doModify("UPDATE jobhistory SET jobsize=%s, jobprice=%s WHERE id=%s" % (self.doQuote(jobsize), self.doQuote(jobprice), self.doQuote(lastjob.ident)))
566       
567    def writeJobNew(self, printer, user, jobid, pagecounter, action, jobsize=None, jobprice=None, filename=None, title=None, copies=None, options=None, clienthost=None, jobsizebytes=None) :
568        """Adds a job in a printer's history."""
569        if self.privacy :   
570            # For legal reasons, we want to hide the title, filename and options
571            title = filename = options = "Hidden because of privacy concerns"
572        filename = self.userCharsetToDatabase(filename)
573        title = self.userCharsetToDatabase(title)
574        options = self.userCharsetToDatabase(options)
575        if (not self.disablehistory) or (not printer.LastJob.Exists) :
576            if jobsize is not None :
577                self.doModify("INSERT INTO jobhistory (userid, printerid, jobid, pagecounter, action, jobsize, jobprice, filename, title, copies, options, hostname, jobsizebytes) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" % (self.doQuote(user.ident), self.doQuote(printer.ident), self.doQuote(jobid), self.doQuote(pagecounter), self.doQuote(action), self.doQuote(jobsize), self.doQuote(jobprice), self.doQuote(filename), self.doQuote(title), self.doQuote(copies), self.doQuote(options), self.doQuote(clienthost), self.doQuote(jobsizebytes)))
578            else :   
579                self.doModify("INSERT INTO jobhistory (userid, printerid, jobid, pagecounter, action, filename, title, copies, options, hostname, jobsizebytes) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" % (self.doQuote(user.ident), self.doQuote(printer.ident), self.doQuote(jobid), self.doQuote(pagecounter), self.doQuote(action), self.doQuote(filename), self.doQuote(title), self.doQuote(copies), self.doQuote(options), self.doQuote(clienthost), self.doQuote(jobsizebytes)))
580        else :       
581            # here we explicitly want to reset jobsize to NULL if needed
582            self.doModify("UPDATE jobhistory SET userid=%s, jobid=%s, pagecounter=%s, action=%s, jobsize=%s, jobprice=%s, filename=%s, title=%s, copies=%s, options=%s, hostname=%s, jobsizebytes=%s, jobdate=now() WHERE id=%s" % (self.doQuote(user.ident), self.doQuote(jobid), self.doQuote(pagecounter), self.doQuote(action), self.doQuote(jobsize), self.doQuote(jobprice), self.doQuote(filename), self.doQuote(title), self.doQuote(copies), self.doQuote(options), self.doQuote(clienthost), self.doQuote(jobsizebytes), self.doQuote(printer.LastJob.ident)))
583           
584    def writeUserPQuotaLimits(self, userpquota, softlimit, hardlimit) :
585        """Sets soft and hard limits for a user quota."""
586        self.doModify("UPDATE userpquota SET softlimit=%s, hardlimit=%s, warncount=0, datelimit=NULL WHERE id=%s" % (self.doQuote(softlimit), self.doQuote(hardlimit), self.doQuote(userpquota.ident)))
587       
588    def writeUserPQuotaWarnCount(self, userpquota, warncount) :
589        """Sets the warn counter value for a user quota."""
590        self.doModify("UPDATE userpquota SET warncount=%s WHERE id=%s" % (self.doQuote(warncount), self.doQuote(userpquota.ident)))
591       
592    def increaseUserPQuotaWarnCount(self, userpquota) :
593        """Increases the warn counter value for a user quota."""
594        self.doModify("UPDATE userpquota SET warncount=warncount+1 WHERE id=%s" % self.doQuote(userpquota.ident))
595       
596    def writeGroupPQuotaLimits(self, grouppquota, softlimit, hardlimit) :
597        """Sets soft and hard limits for a group quota on a specific printer."""
598        self.doModify("UPDATE grouppquota SET softlimit=%s, hardlimit=%s, datelimit=NULL WHERE id=%s" % (self.doQuote(softlimit), self.doQuote(hardlimit), self.doQuote(grouppquota.ident)))
599
600    def writePrinterToGroup(self, pgroup, printer) :
601        """Puts a printer into a printer group."""
602        children = []
603        result = self.doSearch("SELECT printerid FROM printergroupsmembers WHERE groupid=%s" % self.doQuote(pgroup.ident))
604        if result :
605            for record in result :
606                children.append(record.get("printerid")) # TODO : put this into the database integrity rules
607        if printer.ident not in children :       
608            self.doModify("INSERT INTO printergroupsmembers (groupid, printerid) VALUES (%s, %s)" % (self.doQuote(pgroup.ident), self.doQuote(printer.ident)))
609       
610    def removePrinterFromGroup(self, pgroup, printer) :
611        """Removes a printer from a printer group."""
612        self.doModify("DELETE FROM printergroupsmembers WHERE groupid=%s AND printerid=%s" % (self.doQuote(pgroup.ident), self.doQuote(printer.ident)))
613       
614    def retrieveHistory(self, user=None, printer=None, datelimit=None, hostname=None, limit=100) :   
615        """Retrieves all print jobs for user on printer (or all) before date, limited to first 100 results."""
616        query = "SELECT jobhistory.*,username,printername FROM jobhistory,users,printers WHERE users.id=userid AND printers.id=printerid"
617        where = []
618        if (user is not None) and user.Exists :
619            where.append("userid=%s" % self.doQuote(user.ident))
620        if (printer is not None) and printer.Exists :
621            where.append("printerid=%s" % self.doQuote(printer.ident))
622        if hostname is not None :   
623            where.append("hostname=%s" % self.doQuote(hostname))
624        if datelimit is not None :   
625            where.append("jobdate<=%s" % self.doQuote(datelimit))
626        if where :   
627            query += " AND %s" % " AND ".join(where)
628        query += " ORDER BY id DESC"
629        if limit :
630            query += " LIMIT %s" % self.doQuote(int(limit))
631        jobs = []   
632        result = self.doSearch(query)   
633        if result :
634            for fields in result :
635                job = StorageJob(self)
636                job.ident = fields.get("id")
637                job.JobId = fields.get("jobid")
638                job.PrinterPageCounter = fields.get("pagecounter")
639                job.JobSize = fields.get("jobsize")
640                job.JobPrice = fields.get("jobprice")
641                job.JobAction = fields.get("action")
642                job.JobFileName = self.databaseToUserCharset(fields.get("filename") or "") 
643                job.JobTitle = self.databaseToUserCharset(fields.get("title") or "") 
644                job.JobCopies = fields.get("copies")
645                job.JobOptions = self.databaseToUserCharset(fields.get("options") or "") 
646                job.JobDate = fields.get("jobdate")
647                job.JobHostName = fields.get("hostname")
648                job.JobSizeBytes = fields.get("jobsizebytes")
649                job.JobMD5Sum = fields.get("md5sum")
650                job.JobPages = fields.get("pages")
651                job.JobBillingCode = fields.get("billingcode")
652                job.UserName = fields.get("username")
653                job.PrinterName = fields.get("printername")
654                job.Exists = 1
655                jobs.append(job)
656        return jobs
657       
658    def deleteUser(self, user) :   
659        """Completely deletes an user from the Quota Storage."""
660        # TODO : What should we do if we delete the last person who used a given printer ?
661        # TODO : we can't reassign the last job to the previous one, because next user would be
662        # TODO : incorrectly charged (overcharged).
663        for q in [ 
664                    "DELETE FROM payments WHERE userid=%s" % self.doQuote(user.ident),
665                    "DELETE FROM groupsmembers WHERE userid=%s" % self.doQuote(user.ident),
666                    "DELETE FROM jobhistory WHERE userid=%s" % self.doQuote(user.ident),
667                    "DELETE FROM userpquota WHERE userid=%s" % self.doQuote(user.ident),
668                    "DELETE FROM users WHERE id=%s" % self.doQuote(user.ident),
669                  ] :
670            self.doModify(q)
671       
672    def deleteGroup(self, group) :   
673        """Completely deletes a group from the Quota Storage."""
674        for q in [
675                   "DELETE FROM groupsmembers WHERE groupid=%s" % self.doQuote(group.ident),
676                   "DELETE FROM grouppquota WHERE groupid=%s" % self.doQuote(group.ident),
677                   "DELETE FROM groups WHERE id=%s" % self.doQuote(group.ident),
678                 ] : 
679            self.doModify(q)
680           
681    def deletePrinter(self, printer) :   
682        """Completely deletes a printer from the Quota Storage."""
683        for q in [ 
684                    "DELETE FROM printergroupsmembers WHERE groupid=%s OR printerid=%s" % (self.doQuote(printer.ident), self.doQuote(printer.ident)),
685                    "DELETE FROM jobhistory WHERE printerid=%s" % self.doQuote(printer.ident),
686                    "DELETE FROM grouppquota WHERE printerid=%s" % self.doQuote(printer.ident),
687                    "DELETE FROM userpquota WHERE printerid=%s" % self.doQuote(printer.ident),
688                    "DELETE FROM printers WHERE id=%s" % self.doQuote(printer.ident),
689                  ] :
690            self.doModify(q)
691       
Note: See TracBrowser for help on using the browser.