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

Revision 3521, 54.8 kB (checked in by jerome, 12 years ago)

Removed some code specific to Python v2.2 and earlier.
Improved the work done by bse@… to fix #52.
IMPORTANT : the same optimisation is currently done for users only, not for
users groups, printers or billing codes. And more importantly the code
was not ported to the LDAP backend. I need more time to do all this.

  • Property svn:eol-style set to native
  • Property svn:keywords set to Author Date Id Revision
Line 
1# -*- coding: utf-8 -*-
2#
3# PyKota : Print Quotas for CUPS
4#
5# (c) 2003-2009 Jerome Alet <alet@librelogiciel.com>
6# This program is free software: you can redistribute it and/or modify
7# it under the terms of the GNU General Public License as published by
8# the Free Software Foundation, either version 3 of the License, or
9# (at your option) any later version.
10#
11# This program is distributed in the hope that it will be useful,
12# but WITHOUT ANY WARRANTY; without even the implied warranty of
13# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
14# GNU General Public License for more details.
15#
16# You should have received a copy of the GNU General Public License
17# along with this program.  If not, see <http://www.gnu.org/licenses/>.
18#
19# $Id$
20#
21#
22
23"""This module defines methods common to all relational backends."""
24
25from pykota.storage import StorageUser, StorageGroup, StoragePrinter, \
26                           StorageJob, StorageLastJob, StorageUserPQuota, \
27                           StorageGroupPQuota, StorageBillingCode
28
29from pykota.utils import *
30
31MAXINNAMES = 500 # Maximum number of non-patterns names to use in a single IN statement
32
33class SQLStorage :
34    def storageUserFromRecord(self, username, record) :
35        """Returns a StorageUser instance from a database record."""
36        user = StorageUser(self, username)
37        user.ident = record.get("uid", record.get("userid", record.get("id")))
38        user.LimitBy = record.get("limitby") or "quota"
39        user.AccountBalance = record.get("balance")
40        user.LifeTimePaid = record.get("lifetimepaid")
41        user.Email = record.get("email")
42        user.Description = databaseToUnicode(record.get("description"))
43        user.OverCharge = record.get("overcharge", 1.0)
44        user.Exists = True
45        return user
46
47    def storageGroupFromRecord(self, groupname, record) :
48        """Returns a StorageGroup instance from a database record."""
49        group = StorageGroup(self, groupname)
50        group.ident = record.get("id")
51        group.LimitBy = record.get("limitby") or "quota"
52        group.AccountBalance = record.get("balance")
53        group.LifeTimePaid = record.get("lifetimepaid")
54        group.Description = databaseToUnicode(record.get("description"))
55        group.Exists = True
56        return group
57
58    def storagePrinterFromRecord(self, printername, record) :
59        """Returns a StoragePrinter instance from a database record."""
60        printer = StoragePrinter(self, printername)
61        printer.ident = record.get("id")
62        printer.PricePerJob = record.get("priceperjob") or 0.0
63        printer.PricePerPage = record.get("priceperpage") or 0.0
64        printer.MaxJobSize = record.get("maxjobsize") or 0
65        printer.PassThrough = record.get("passthrough") or 0
66        if printer.PassThrough in (1, "1", "t", "true", "TRUE", "True") :
67            printer.PassThrough = True
68        else :
69            printer.PassThrough = False
70        printer.Description = databaseToUnicode(record.get("description") or "") # TODO : is 'or ""' still needed ?
71        printer.Exists = True
72        return printer
73
74    def setJobAttributesFromRecord(self, job, record) :
75        """Sets the attributes of a job from a database record."""
76        job.ident = record.get("id")
77        job.JobId = record.get("jobid")
78        job.PrinterPageCounter = record.get("pagecounter")
79        job.JobSize = record.get("jobsize")
80        job.JobPrice = record.get("jobprice")
81        job.JobAction = record.get("action")
82        job.JobFileName = databaseToUnicode(record.get("filename") or "")
83        job.JobTitle = databaseToUnicode(record.get("title") or "")
84        job.JobCopies = record.get("copies")
85        job.JobOptions = databaseToUnicode(record.get("options") or "")
86        job.JobDate = record.get("jobdate")
87        job.JobHostName = record.get("hostname")
88        job.JobSizeBytes = record.get("jobsizebytes")
89        job.JobMD5Sum = record.get("md5sum")
90        job.JobPages = record.get("pages")
91        job.JobBillingCode = databaseToUnicode(record.get("billingcode") or "")
92        job.PrecomputedJobSize = record.get("precomputedjobsize")
93        job.PrecomputedJobPrice = record.get("precomputedjobprice")
94        job.UserName = databaseToUnicode(record.get("username"))
95        job.PrinterName = databaseToUnicode(record.get("printername"))
96        if job.JobTitle == job.JobFileName == job.JobOptions == "hidden" :
97            (job.JobTitle, job.JobFileName, job.JobOptions) = (_("Hidden because of privacy concerns"),) * 3
98        job.Exists = True
99
100    def storageJobFromRecord(self, record) :
101        """Returns a StorageJob instance from a database record."""
102        job = StorageJob(self)
103        self.setJobAttributesFromRecord(job, record)
104        return job
105
106    def storageLastJobFromRecord(self, printer, record) :
107        """Returns a StorageLastJob instance from a database record."""
108        lastjob = StorageLastJob(self, printer)
109        self.setJobAttributesFromRecord(lastjob, record)
110        return lastjob
111
112    def storageUserPQuotaFromRecord(self, user, printer, record) :
113        """Returns a StorageUserPQuota instance from a database record."""
114        userpquota = StorageUserPQuota(self, user, printer)
115        userpquota.ident = record.get("id")
116        userpquota.PageCounter = record.get("pagecounter")
117        userpquota.LifePageCounter = record.get("lifepagecounter")
118        userpquota.SoftLimit = record.get("softlimit")
119        userpquota.HardLimit = record.get("hardlimit")
120        userpquota.DateLimit = record.get("datelimit")
121        userpquota.WarnCount = record.get("warncount") or 0
122        userpquota.Exists = True
123        return userpquota
124
125    def storageGroupPQuotaFromRecord(self, group, printer, record) :
126        """Returns a StorageGroupPQuota instance from a database record."""
127        grouppquota = StorageGroupPQuota(self, group, printer)
128        grouppquota.ident = record.get("id")
129        grouppquota.SoftLimit = record.get("softlimit")
130        grouppquota.HardLimit = record.get("hardlimit")
131        grouppquota.DateLimit = record.get("datelimit")
132        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)" \
133                      % (self.doQuote(printer.ident), self.doQuote(group.ident)))
134        if result :
135            grouppquota.PageCounter = result[0].get("pagecounter") or 0
136            grouppquota.LifePageCounter = result[0].get("lifepagecounter") or 0
137        grouppquota.Exists = True
138        return grouppquota
139
140    def storageBillingCodeFromRecord(self, billingcode, record) :
141        """Returns a StorageBillingCode instance from a database record."""
142        code = StorageBillingCode(self, billingcode)
143        code.ident = record.get("id")
144        code.Description = databaseToUnicode(record.get("description") or "") # TODO : is 'or ""' still needed ?
145        code.Balance = record.get("balance") or 0.0
146        code.PageCounter = record.get("pagecounter") or 0
147        code.Exists = True
148        return code
149
150    def createFilter(self, only) :
151        """Returns the appropriate SQL filter."""
152        if only :
153            expressions = []
154            for (k, v) in only.items() :
155                expressions.append("%s=%s" % (k, self.doQuote(unicodeToDatabase(v))))
156            return " AND ".join(expressions)
157        return ""
158
159    def createOrderBy(self, default, ordering) :
160        """Creates a suitable ORDER BY statement based on a list of fieldnames prefixed with '+' (ASC) or '-' (DESC)."""
161        statements = []
162        if not ordering :
163            ordering = default
164        for field in ordering :
165            if field.startswith("-") :
166                statements.append("%s DESC" % field[1:])
167            elif field.startswith("+") :
168                statements.append("%s ASC" % field[1:])
169            else :
170                statements.append("%s ASC" % field)
171        return ", ".join(statements)
172
173    def extractPrinters(self, extractonly={}, ordering=[]) :
174        """Extracts all printer records."""
175        thefilter = self.createFilter(extractonly)
176        if thefilter :
177            thefilter = "WHERE %s" % thefilter
178        orderby = self.createOrderBy(["+id"], ordering)
179        result = self.doRawSearch("SELECT * FROM printers %(thefilter)s ORDER BY %(orderby)s" % locals())
180        return self.prepareRawResult(result)
181
182    def extractUsers(self, extractonly={}, ordering=[]) :
183        """Extracts all user records."""
184        thefilter = self.createFilter(extractonly)
185        if thefilter :
186            thefilter = "WHERE %s" % thefilter
187        orderby = self.createOrderBy(["+id"], ordering)
188        result = self.doRawSearch("SELECT * FROM users %(thefilter)s ORDER BY %(orderby)s" % locals())
189        return self.prepareRawResult(result)
190
191    def extractBillingcodes(self, extractonly={}, ordering=[]) :
192        """Extracts all billing codes records."""
193        thefilter = self.createFilter(extractonly)
194        if thefilter :
195            thefilter = "WHERE %s" % thefilter
196        orderby = self.createOrderBy(["+id"], ordering)
197        result = self.doRawSearch("SELECT * FROM billingcodes %(thefilter)s ORDER BY %(orderby)s" % locals())
198        return self.prepareRawResult(result)
199
200    def extractGroups(self, extractonly={}, ordering=[]) :
201        """Extracts all group records."""
202        thefilter = self.createFilter(extractonly)
203        if thefilter :
204            thefilter = "WHERE %s" % thefilter
205        orderby = self.createOrderBy(["+groups.id"], ordering)
206        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) %(thefilter)s GROUP BY groups.id,groups.groupname,groups.limitby,groups.description ORDER BY %(orderby)s" % locals())
207        return self.prepareRawResult(result)
208
209    def extractPayments(self, extractonly={}, ordering=[]) :
210        """Extracts all payment records."""
211        startdate = extractonly.get("start")
212        enddate = extractonly.get("end")
213        for limit in ("start", "end") :
214            try :
215                del extractonly[limit]
216            except KeyError :
217                pass
218        thefilter = self.createFilter(extractonly)
219        if thefilter :
220            thefilter = "AND %s" % thefilter
221        (startdate, enddate) = self.cleanDates(startdate, enddate)
222        if startdate :
223            thefilter = "%s AND date>=%s" % (thefilter, self.doQuote(startdate))
224        if enddate :
225            thefilter = "%s AND date<=%s" % (thefilter, self.doQuote(enddate))
226        orderby = self.createOrderBy(["+payments.id"], ordering)
227        result = self.doRawSearch("SELECT username,payments.* FROM users,payments WHERE users.id=payments.userid %(thefilter)s ORDER BY %(orderby)s" % locals())
228        return self.prepareRawResult(result)
229
230    def extractUpquotas(self, extractonly={}, ordering=[]) :
231        """Extracts all userpquota records."""
232        thefilter = self.createFilter(extractonly)
233        if thefilter :
234            thefilter = "AND %s" % thefilter
235        orderby = self.createOrderBy(["+userpquota.id"], ordering)
236        result = self.doRawSearch("SELECT users.username,printers.printername,userpquota.* FROM users,printers,userpquota WHERE users.id=userpquota.userid AND printers.id=userpquota.printerid %(thefilter)s ORDER BY %(orderby)s" % locals())
237        return self.prepareRawResult(result)
238
239    def extractGpquotas(self, extractonly={}, ordering=[]) :
240        """Extracts all grouppquota records."""
241        thefilter = self.createFilter(extractonly)
242        if thefilter :
243            thefilter = "AND %s" % thefilter
244        orderby = self.createOrderBy(["+grouppquota.id"], ordering)
245        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) %(thefilter)s GROUP BY grouppquota.id,grouppquota.groupid,grouppquota.printerid,grouppquota.softlimit,grouppquota.hardlimit,grouppquota.datelimit,grouppquota.maxjobsize,groups.groupname,printers.printername ORDER BY %(orderby)s" % locals())
246        return self.prepareRawResult(result)
247
248    def extractUmembers(self, extractonly={}, ordering=[]) :
249        """Extracts all user groups members."""
250        thefilter = self.createFilter(extractonly)
251        if thefilter :
252            thefilter = "AND %s" % thefilter
253        orderby = self.createOrderBy(["+groupsmembers.groupid", "+groupsmembers.userid"], ordering)
254        result = self.doRawSearch("SELECT groups.groupname, users.username, groupsmembers.* FROM groups,users,groupsmembers WHERE users.id=groupsmembers.userid AND groups.id=groupsmembers.groupid %(thefilter)s ORDER BY %(orderby)s" % locals())
255        return self.prepareRawResult(result)
256
257    def extractPmembers(self, extractonly={}, ordering=[]) :
258        """Extracts all printer groups members."""
259        for (k, v) in extractonly.items() :
260            if k == "pgroupname" :
261                del extractonly[k]
262                extractonly["p1.printername"] = v
263            elif k == "printername" :
264                del extractonly[k]
265                extractonly["p2.printername"] = v
266        thefilter = self.createFilter(extractonly)
267        if thefilter :
268            thefilter = "AND %s" % thefilter
269        orderby = self.createOrderBy(["+printergroupsmembers.groupid", "+printergroupsmembers.printerid"], ordering)
270        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 %(thefilter)s ORDER BY %(orderby)s" % locals())
271        return self.prepareRawResult(result)
272
273    def extractHistory(self, extractonly={}, ordering=[]) :
274        """Extracts all jobhistory records."""
275        startdate = extractonly.get("start")
276        enddate = extractonly.get("end")
277        for limit in ("start", "end") :
278            try :
279                del extractonly[limit]
280            except KeyError :
281                pass
282        thefilter = self.createFilter(extractonly)
283        if thefilter :
284            thefilter = "AND %s" % thefilter
285        (startdate, enddate) = self.cleanDates(startdate, enddate)
286        if startdate :
287            thefilter = "%s AND jobdate>=%s" % (thefilter, self.doQuote(startdate))
288        if enddate :
289            thefilter = "%s AND jobdate<=%s" % (thefilter, self.doQuote(enddate))
290        orderby = self.createOrderBy(["+jobhistory.id"], ordering)
291        result = self.doRawSearch("SELECT users.username,printers.printername,jobhistory.* FROM users,printers,jobhistory WHERE users.id=jobhistory.userid AND printers.id=jobhistory.printerid %(thefilter)s ORDER BY %(orderby)s" % locals())
292        return self.prepareRawResult(result)
293
294    def filterNames(self, records, attribute, patterns=None) :
295        """Returns a list of 'attribute' from a list of records.
296
297           Logs any missing attribute.
298        """
299        result = []
300        for record in records :
301            attrval = record.get(attribute, [None])
302            if attrval is None :
303                self.tool.printInfo("Object %s has no %s attribute !" % (repr(record), attribute), "error")
304            else :
305                attrval = databaseToUnicode(attrval)
306                if patterns :
307                    if (not isinstance(patterns, type([]))) and (not isinstance(patterns, type(()))) :
308                        patterns = [ patterns ]
309                    if self.tool.matchString(attrval, patterns) :
310                        result.append(attrval)
311                else :
312                    result.append(attrval)
313        return result
314
315    def getAllBillingCodes(self, billingcode=None) :
316        """Extracts all billing codes or only the billing codes matching the optional parameter."""
317        result = self.doSearch("SELECT billingcode FROM billingcodes")
318        if result :
319            return self.filterNames(result, "billingcode", billingcode)
320        else :
321            return []
322
323    def getAllPrintersNames(self, printername=None) :
324        """Extracts all printer names or only the printers' names matching the optional parameter."""
325        result = self.doSearch("SELECT printername FROM printers")
326        if result :
327            return self.filterNames(result, "printername", printername)
328        else :
329            return []
330
331    def getAllUsersNames(self, username=None) :
332        """Extracts all user names."""
333        result = self.doSearch("SELECT username FROM users")
334        if result :
335            return self.filterNames(result, "username", username)
336        else :
337            return []
338
339    def getAllGroupsNames(self, groupname=None) :
340        """Extracts all group names."""
341        result = self.doSearch("SELECT groupname FROM groups")
342        if result :
343            return self.filterNames(result, "groupname", groupname)
344        else :
345            return []
346
347    def getUserNbJobsFromHistory(self, user) :
348        """Returns the number of jobs the user has in history."""
349        result = self.doSearch("SELECT COUNT(*) AS count FROM jobhistory WHERE userid=%s" % self.doQuote(user.ident))
350        if result :
351            return result[0]["count"]
352        return 0
353
354    def getUserFromBackend(self, username) :
355        """Extracts user information given its name."""
356        result = self.doSearch("SELECT * FROM users WHERE username=%s"\
357                      % self.doQuote(unicodeToDatabase(username)))
358        if result :
359            return self.storageUserFromRecord(username, result[0])
360        else :
361            return StorageUser(self, username)
362
363    def getGroupFromBackend(self, groupname) :
364        """Extracts group information given its name."""
365        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,groups.description" \
366                      % self.doQuote(unicodeToDatabase(groupname)))
367        if result :
368            return self.storageGroupFromRecord(groupname, result[0])
369        else :
370            return StorageGroup(self, groupname)
371
372    def getPrinterFromBackend(self, printername) :
373        """Extracts printer information given its name."""
374        result = self.doSearch("SELECT * FROM printers WHERE printername=%s" \
375                      % self.doQuote(unicodeToDatabase(printername)))
376        if result :
377            return self.storagePrinterFromRecord(printername, result[0])
378        else :
379            return StoragePrinter(self, printername)
380
381    def getBillingCodeFromBackend(self, label) :
382        """Extracts a billing code information given its name."""
383        result = self.doSearch("SELECT * FROM billingcodes WHERE billingcode=%s" \
384                      % self.doQuote(unicodeToDatabase(label)))
385        if result :
386            return self.storageBillingCodeFromRecord(label, result[0])
387        else :
388            return StorageBillingCode(self, label)
389
390    def getUserPQuotaFromBackend(self, user, printer) :
391        """Extracts a user print quota."""
392        if printer.Exists and user.Exists :
393            result = self.doSearch("SELECT * FROM userpquota WHERE userid=%s AND printerid=%s;" \
394                          % (self.doQuote(user.ident), self.doQuote(printer.ident)))
395            if result :
396                return self.storageUserPQuotaFromRecord(user, printer, result[0])
397        return StorageUserPQuota(self, user, printer)
398
399    def getGroupPQuotaFromBackend(self, group, printer) :
400        """Extracts a group print quota."""
401        if printer.Exists and group.Exists :
402            result = self.doSearch("SELECT * FROM grouppquota WHERE groupid=%s AND printerid=%s" \
403                          % (self.doQuote(group.ident), self.doQuote(printer.ident)))
404            if result :
405                return self.storageGroupPQuotaFromRecord(group, printer, result[0])
406        return StorageGroupPQuota(self, group, printer)
407
408    def getPrinterLastJobFromBackend(self, printer) :
409        """Extracts a printer's last job information."""
410        result = self.doSearch("SELECT jobhistory.id, jobid, userid, username, pagecounter, jobsize, jobprice, filename, title, copies, options, hostname, jobdate, md5sum, pages, billingcode, precomputedjobsize, precomputedjobprice FROM jobhistory, users WHERE userid=users.id AND jobhistory.id IN (SELECT max(id) FROM jobhistory WHERE printerid=%s)" % self.doQuote(printer.ident))
411        if result :
412            return self.storageLastJobFromRecord(printer, result[0])
413        else :
414            return StorageLastJob(self, printer)
415
416    def getGroupMembersFromBackend(self, group) :
417        """Returns the group's members list."""
418        groupmembers = []
419        result = self.doSearch("SELECT * FROM groupsmembers JOIN users ON groupsmembers.userid=users.id WHERE groupid=%s" % self.doQuote(group.ident))
420        if result :
421            for record in result :
422                user = self.storageUserFromRecord(databaseToUnicode(record.get("username")), \
423                                                  record)
424                groupmembers.append(user)
425                self.cacheEntry("USERS", user.Name, user)
426        return groupmembers
427
428    def getUserGroupsFromBackend(self, user) :
429        """Returns the user's groups list."""
430        groups = []
431        result = self.doSearch("SELECT groupname FROM groupsmembers JOIN groups ON groupsmembers.groupid=groups.id WHERE userid=%s" % self.doQuote(user.ident))
432        if result :
433            for record in result :
434                groups.append(self.getGroup(databaseToUnicode(record.get("groupname"))))
435        return groups
436
437    def getParentPrintersFromBackend(self, printer) :
438        """Get all the printer groups this printer is a member of."""
439        pgroups = []
440        result = self.doSearch("SELECT groupid,printername FROM printergroupsmembers JOIN printers ON groupid=id WHERE printerid=%s" % self.doQuote(printer.ident))
441        if result :
442            for record in result :
443                if record["groupid"] != printer.ident : # in case of integrity violation
444                    parentprinter = self.getPrinter(databaseToUnicode(record.get("printername")))
445                    if parentprinter.Exists :
446                        pgroups.append(parentprinter)
447        return pgroups
448
449    def hasWildCards(self, pattern) :
450        """Returns True if the pattern contains wildcards, else False."""
451        specialchars = "*?[!" # no need to check for ] since [ would be there first
452        for specialchar in specialchars :
453            if specialchar in pattern :
454                return True
455        return False
456
457    def getMatchingPrinters(self, printerpattern) :
458        """Returns the list of all printers for which name matches a certain pattern."""
459        printers = []
460        # We 'could' do a SELECT printername FROM printers WHERE printername LIKE ...
461        # but we don't because other storages semantics may be different, so every
462        # storage should use fnmatch to match patterns and be storage agnostic
463        result = self.doSearch("SELECT * FROM printers")
464        if result :
465            patterns = printerpattern.split(",")
466            patdict = {}.fromkeys(patterns)
467            for record in result :
468                pname = databaseToUnicode(record["printername"])
469                if patdict.has_key(pname) or self.tool.matchString(pname, patterns) :
470                    printer = self.storagePrinterFromRecord(pname, record)
471                    printers.append(printer)
472                    self.cacheEntry("PRINTERS", printer.Name, printer)
473        return printers
474
475    def getMatchingUsers(self, userpattern) :
476        """Returns the list of all users for which name matches a certain pattern."""
477        users = []
478        # We 'could' do a SELECT username FROM users WHERE username LIKE ...
479        # but we don't because other storages semantics may be different, so every
480        # storage should use fnmatch to match patterns and be storage agnostic
481        #
482        # This doesn't prevent us from being smarter, thanks to bse@chalmers.se
483        userpattern = userpattern or "*"
484        patterns = userpattern.split(",")
485        patdict = {}.fromkeys(patterns)
486        patterns = patdict.keys() # Ensures the uniqueness of each pattern, but we lose the cmd line ordering
487        # BEWARE : if a single pattern contains wild cards, we'll still use the slow route.
488        if self.hasWildCards(userpattern) :
489            # Slow route
490            result = self.doSearch("SELECT * FROM users")
491            if result :
492                for record in result :
493                    uname = databaseToUnicode(record["username"])
494                    if patdict.has_key(uname) or self.tool.matchString(uname, patterns) :
495                        user = self.storageUserFromRecord(uname, record)
496                        users.append(user)
497                        self.cacheEntry("USERS", user.Name, user)
498        else :
499            # Fast route (probably not faster with a few users)
500            while patterns :
501                subset = patterns[:MAXINNAMES]
502                nbpatterns = len(subset)
503                if nbpatterns == 1 :
504                    wherestmt = "username=%s" % self.doQuote(unicodeToDatabase(subset[0]))
505                else :
506                    wherestmt = "username IN (%s)" % ",".join([self.doQuote(unicodeToDatabase(p)) for p in subset])
507                result = self.doSearch("SELECT * FROM users WHERE %s" % wherestmt)
508                if result :
509                    for record in result :
510                        uname = databaseToUnicode(record["username"])
511                        user = self.storageUserFromRecord(uname, record)
512                        users.append(user)
513                        self.cacheEntry("USERS", user.Name, user)
514                patterns = patterns[MAXINNAMES:]
515        users.sort(key=lambda u : u.Name) # Adds some ordering, we've already lost the cmd line one anyway.
516        return users
517
518    def getMatchingGroups(self, grouppattern) :
519        """Returns the list of all groups for which name matches a certain pattern."""
520        groups = []
521        # We 'could' do a SELECT groupname FROM groups WHERE groupname LIKE ...
522        # but we don't because other storages semantics may be different, so every
523        # storage should use fnmatch to match patterns and be storage agnostic
524        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) GROUP BY groups.id,groups.groupname,groups.limitby,groups.description")
525        if result :
526            patterns = grouppattern.split(",")
527            patdict = {}.fromkeys(patterns)
528            for record in result :
529                gname = databaseToUnicode(record["groupname"])
530                if patdict.has_key(gname) or self.tool.matchString(gname, patterns) :
531                    group = self.storageGroupFromRecord(gname, record)
532                    groups.append(group)
533                    self.cacheEntry("GROUPS", group.Name, group)
534        return groups
535
536    def getMatchingBillingCodes(self, billingcodepattern) :
537        """Returns the list of all billing codes for which the label matches a certain pattern."""
538        codes = []
539        result = self.doSearch("SELECT * FROM billingcodes")
540        if result :
541            patterns = billingcodepattern.split(",")
542            patdict = {}.fromkeys(patterns)
543            for record in result :
544                codename = databaseToUnicode(record["billingcode"])
545                if patdict.has_key(codename) or self.tool.matchString(codename, patterns) :
546                    code = self.storageBillingCodeFromRecord(codename, record)
547                    codes.append(code)
548                    self.cacheEntry("BILLINGCODES", code.BillingCode, code)
549        return codes
550
551    def getPrinterUsersAndQuotas(self, printer, names=["*"]) :
552        """Returns the list of users who uses a given printer, along with their quotas."""
553        usersandquotas = []
554        result = self.doSearch("SELECT users.id as uid,username,description,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))
555        if result :
556            for record in result :
557                uname = databaseToUnicode(record.get("username"))
558                if self.tool.matchString(uname, names) :
559                    user = self.storageUserFromRecord(uname, record)
560                    userpquota = self.storageUserPQuotaFromRecord(user, printer, record)
561                    usersandquotas.append((user, userpquota))
562                    self.cacheEntry("USERS", user.Name, user)
563                    self.cacheEntry("USERPQUOTAS", "%s@%s" % (user.Name, printer.Name), userpquota)
564        return usersandquotas
565
566    def getPrinterGroupsAndQuotas(self, printer, names=["*"]) :
567        """Returns the list of groups which uses a given printer, along with their quotas."""
568        groupsandquotas = []
569        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))
570        if result :
571            for record in result :
572                gname = databaseToUnicode(record.get("groupname"))
573                if self.tool.matchString(gname, names) :
574                    group = self.getGroup(gname)
575                    grouppquota = self.getGroupPQuota(group, printer)
576                    groupsandquotas.append((group, grouppquota))
577        return groupsandquotas
578
579    def addPrinter(self, printer) :
580        """Adds a printer to the quota storage, returns the old value if it already exists."""
581        oldentry = self.getPrinter(printer.Name)
582        if oldentry.Exists :
583            return oldentry
584        self.doModify("INSERT INTO printers (printername, passthrough, maxjobsize, description, priceperpage, priceperjob) VALUES (%s, %s, %s, %s, %s, %s)" \
585                          % (self.doQuote(unicodeToDatabase(printer.Name)), \
586                             self.doQuote((printer.PassThrough and "t") or "f"), \
587                             self.doQuote(printer.MaxJobSize or 0), \
588                             self.doQuote(unicodeToDatabase(printer.Description)), \
589                             self.doQuote(printer.PricePerPage or 0.0), \
590                             self.doQuote(printer.PricePerJob or 0.0)))
591        printer.isDirty = False
592        return None # the entry created doesn't need further modification
593
594    def addBillingCode(self, bcode) :
595        """Adds a billing code to the quota storage, returns the old value if it already exists."""
596        oldentry = self.getBillingCode(bcode.BillingCode)
597        if oldentry.Exists :
598            return oldentry
599        self.doModify("INSERT INTO billingcodes (billingcode, balance, pagecounter, description) VALUES (%s, %s, %s, %s)" \
600                           % (self.doQuote(unicodeToDatabase(bcode.BillingCode)),
601                              self.doQuote(bcode.Balance or 0.0), \
602                              self.doQuote(bcode.PageCounter or 0), \
603                              self.doQuote(unicodeToDatabase(bcode.Description))))
604        bcode.isDirty = False
605        return None # the entry created doesn't need further modification
606
607    def addUser(self, user) :
608        """Adds a user to the quota storage, returns the old value if it already exists."""
609        oldentry = self.getUser(user.Name)
610        if oldentry.Exists :
611            return oldentry
612        self.doModify("INSERT INTO users (username, limitby, balance, lifetimepaid, email, overcharge, description) VALUES (%s, %s, %s, %s, %s, %s, %s)" % \
613                                     (self.doQuote(unicodeToDatabase(user.Name)), \
614                                      self.doQuote(user.LimitBy or 'quota'), \
615                                      self.doQuote(user.AccountBalance or 0.0), \
616                                      self.doQuote(user.LifeTimePaid or 0.0), \
617                                      self.doQuote(user.Email), \
618                                      self.doQuote(user.OverCharge), \
619                                      self.doQuote(unicodeToDatabase(user.Description))))
620        if user.PaymentsBacklog :
621            for (value, comment) in user.PaymentsBacklog :
622                self.writeNewPayment(user, value, comment)
623            user.PaymentsBacklog = []
624        user.isDirty = False
625        return None # the entry created doesn't need further modification
626
627    def addGroup(self, group) :
628        """Adds a group to the quota storage, returns the old value if it already exists."""
629        oldentry = self.getGroup(group.Name)
630        if oldentry.Exists :
631            return oldentry
632        self.doModify("INSERT INTO groups (groupname, limitby, description) VALUES (%s, %s, %s)" % \
633                              (self.doQuote(unicodeToDatabase(group.Name)), \
634                               self.doQuote(group.LimitBy or "quota"), \
635                               self.doQuote(unicodeToDatabase(group.Description))))
636        group.isDirty = False
637        return None # the entry created doesn't need further modification
638
639    def addUserToGroup(self, user, group) :
640        """Adds an user to a group."""
641        result = self.doSearch("SELECT COUNT(*) AS mexists FROM groupsmembers WHERE groupid=%s AND userid=%s" % (self.doQuote(group.ident), self.doQuote(user.ident)))
642        try :
643            mexists = int(result[0].get("mexists"))
644        except (IndexError, TypeError) :
645            mexists = 0
646        if not mexists :
647            self.doModify("INSERT INTO groupsmembers (groupid, userid) VALUES (%s, %s)" % (self.doQuote(group.ident), self.doQuote(user.ident)))
648
649    def delUserFromGroup(self, user, group) :
650        """Removes an user from a group."""
651        self.doModify("DELETE FROM groupsmembers WHERE groupid=%s AND userid=%s" % \
652                       (self.doQuote(group.ident), self.doQuote(user.ident)))
653
654    def addUserPQuota(self, upq) :
655        """Initializes a user print quota on a printer."""
656        oldentry = self.getUserPQuota(upq.User, upq.Printer)
657        if oldentry.Exists :
658            return oldentry
659        self.doModify("INSERT INTO userpquota (userid, printerid, softlimit, hardlimit, warncount, datelimit, pagecounter, lifepagecounter, maxjobsize) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)" \
660                          % (self.doQuote(upq.User.ident), \
661                             self.doQuote(upq.Printer.ident), \
662                             self.doQuote(upq.SoftLimit), \
663                             self.doQuote(upq.HardLimit), \
664                             self.doQuote(upq.WarnCount or 0), \
665                             self.doQuote(upq.DateLimit), \
666                             self.doQuote(upq.PageCounter or 0), \
667                             self.doQuote(upq.LifePageCounter or 0), \
668                             self.doQuote(upq.MaxJobSize)))
669        upq.isDirty = False
670        return None # the entry created doesn't need further modification
671
672    def addGroupPQuota(self, gpq) :
673        """Initializes a group print quota on a printer."""
674        oldentry = self.getGroupPQuota(gpq.Group, gpq.Printer)
675        if oldentry.Exists :
676            return oldentry
677        self.doModify("INSERT INTO grouppquota (groupid, printerid, softlimit, hardlimit, datelimit, maxjobsize) VALUES (%s, %s, %s, %s, %s, %s)" \
678                          % (self.doQuote(gpq.Group.ident), \
679                             self.doQuote(gpq.Printer.ident), \
680                             self.doQuote(gpq.SoftLimit), \
681                             self.doQuote(gpq.HardLimit), \
682                             self.doQuote(gpq.DateLimit), \
683                             self.doQuote(gpq.MaxJobSize)))
684        gpq.isDirty = False
685        return None # the entry created doesn't need further modification
686
687    def savePrinter(self, printer) :
688        """Saves the printer to the database in a single operation."""
689        self.doModify("UPDATE printers SET passthrough=%s, maxjobsize=%s, description=%s, priceperpage=%s, priceperjob=%s WHERE id=%s" \
690                              % (self.doQuote((printer.PassThrough and "t") or "f"), \
691                                 self.doQuote(printer.MaxJobSize or 0), \
692                                 self.doQuote(unicodeToDatabase(printer.Description)), \
693                                 self.doQuote(printer.PricePerPage or 0.0), \
694                                 self.doQuote(printer.PricePerJob or 0.0), \
695                                 self.doQuote(printer.ident)))
696
697    def saveUser(self, user) :
698        """Saves the user to the database in a single operation."""
699        self.doModify("UPDATE users SET limitby=%s, balance=%s, lifetimepaid=%s, email=%s, overcharge=%s, description=%s WHERE id=%s" \
700                               % (self.doQuote(user.LimitBy or 'quota'), \
701                                  self.doQuote(user.AccountBalance or 0.0), \
702                                  self.doQuote(user.LifeTimePaid or 0.0), \
703                                  self.doQuote(user.Email), \
704                                  self.doQuote(user.OverCharge), \
705                                  self.doQuote(unicodeToDatabase(user.Description)), \
706                                  self.doQuote(user.ident)))
707
708    def saveGroup(self, group) :
709        """Saves the group to the database in a single operation."""
710        self.doModify("UPDATE groups SET limitby=%s, description=%s WHERE id=%s" \
711                               % (self.doQuote(group.LimitBy or 'quota'), \
712                                  self.doQuote(unicodeToDatabase(group.Description)), \
713                                  self.doQuote(group.ident)))
714
715    def writeUserPQuotaDateLimit(self, userpquota, datelimit) :
716        """Sets the date limit permanently for a user print quota."""
717        self.doModify("UPDATE userpquota SET datelimit=%s WHERE id=%s" % (self.doQuote(datelimit), self.doQuote(userpquota.ident)))
718
719    def writeGroupPQuotaDateLimit(self, grouppquota, datelimit) :
720        """Sets the date limit permanently for a group print quota."""
721        self.doModify("UPDATE grouppquota SET datelimit=%s WHERE id=%s" % (self.doQuote(datelimit), self.doQuote(grouppquota.ident)))
722
723    def increaseUserPQuotaPagesCounters(self, userpquota, nbpages) :
724        """Increase page counters for a user print quota."""
725        self.doModify("UPDATE userpquota SET pagecounter=pagecounter + %s,lifepagecounter=lifepagecounter + %s WHERE id=%s" % (self.doQuote(nbpages), self.doQuote(nbpages), self.doQuote(userpquota.ident)))
726
727    def saveBillingCode(self, bcode) :
728        """Saves the billing code to the database."""
729        self.doModify("UPDATE billingcodes SET balance=%s, pagecounter=%s, description=%s WHERE id=%s" \
730                            % (self.doQuote(bcode.Balance or 0.0), \
731                               self.doQuote(bcode.PageCounter or 0), \
732                               self.doQuote(unicodeToDatabase(bcode.Description)), \
733                               self.doQuote(bcode.ident)))
734
735    def consumeBillingCode(self, bcode, pagecounter, balance) :
736        """Consumes from a billing code."""
737        self.doModify("UPDATE billingcodes SET balance=balance + %s, pagecounter=pagecounter + %s WHERE id=%s" % (self.doQuote(balance), self.doQuote(pagecounter), self.doQuote(bcode.ident)))
738
739    def refundJob(self, jobident) :
740        """Marks a job as refunded in the history."""
741        self.doModify("UPDATE jobhistory SET action='REFUND' WHERE id=%s;" % self.doQuote(jobident))
742
743    def decreaseUserAccountBalance(self, user, amount) :
744        """Decreases user's account balance from an amount."""
745        self.doModify("UPDATE users SET balance=balance - %s WHERE id=%s" % (self.doQuote(amount), self.doQuote(user.ident)))
746
747    def writeNewPayment(self, user, amount, comment="") :
748        """Adds a new payment to the payments history."""
749        if user.ident is not None :
750            self.doModify("INSERT INTO payments (userid, amount, description) VALUES (%s, %s, %s)" % (self.doQuote(user.ident), self.doQuote(amount), self.doQuote(unicodeToDatabase(comment))))
751        else :
752            self.doModify("INSERT INTO payments (userid, amount, description) VALUES ((SELECT id FROM users WHERE username=%s), %s, %s)" % (self.doQuote(unicodeToDatabase(user.Name)), self.doQuote(amount), self.doQuote(unicodeToDatabase(comment))))
753
754    def writeLastJobSize(self, lastjob, jobsize, jobprice) :
755        """Sets the last job's size permanently."""
756        self.doModify("UPDATE jobhistory SET jobsize=%s, jobprice=%s WHERE id=%s" % (self.doQuote(jobsize), self.doQuote(jobprice), self.doQuote(lastjob.ident)))
757
758    def writeJobNew(self, printer, user, jobid, pagecounter, action, jobsize=None, jobprice=None, filename=None, title=None, copies=None, options=None, clienthost=None, jobsizebytes=None, jobmd5sum=None, jobpages=None, jobbilling=None, precomputedsize=None, precomputedprice=None) :
759        """Adds a job in a printer's history."""
760        if self.privacy :
761            # For legal reasons, we want to hide the title, filename and options
762            title = filename = options = "hidden"
763        filename = unicodeToDatabase(filename)
764        title = unicodeToDatabase(title)
765        options = unicodeToDatabase(options)
766        jobbilling = unicodeToDatabase(jobbilling)
767        if (not self.disablehistory) or (not printer.LastJob.Exists) :
768            if jobsize is not None :
769                self.doModify("INSERT INTO jobhistory (userid, printerid, jobid, pagecounter, action, jobsize, jobprice, filename, title, copies, options, hostname, jobsizebytes, md5sum, pages, billingcode, precomputedjobsize, precomputedjobprice) VALUES (%s, %s, %s, %s, %s, %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), self.doQuote(jobmd5sum), self.doQuote(jobpages), self.doQuote(jobbilling), self.doQuote(precomputedsize), self.doQuote(precomputedprice)))
770            else :
771                self.doModify("INSERT INTO jobhistory (userid, printerid, jobid, pagecounter, action, filename, title, copies, options, hostname, jobsizebytes, md5sum, pages, billingcode, precomputedjobsize, precomputedjobprice) VALUES (%s, %s, %s, %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(filename), self.doQuote(title), self.doQuote(copies), self.doQuote(options), self.doQuote(clienthost), self.doQuote(jobsizebytes), self.doQuote(jobmd5sum), self.doQuote(jobpages), self.doQuote(jobbilling), self.doQuote(precomputedsize), self.doQuote(precomputedprice)))
772        else :
773            # here we explicitly want to reset jobsize to NULL if needed
774            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, md5sum=%s, pages=%s, billingcode=%s, precomputedjobsize=%s, precomputedjobprice=%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(jobmd5sum), self.doQuote(jobpages), self.doQuote(jobbilling), self.doQuote(precomputedsize), self.doQuote(precomputedprice), self.doQuote(printer.LastJob.ident)))
775
776    def saveUserPQuota(self, userpquota) :
777        """Saves an user print quota entry."""
778        self.doModify("UPDATE userpquota SET softlimit=%s, hardlimit=%s, warncount=%s, datelimit=%s, pagecounter=%s, lifepagecounter=%s, maxjobsize=%s WHERE id=%s" \
779                              % (self.doQuote(userpquota.SoftLimit), \
780                                 self.doQuote(userpquota.HardLimit), \
781                                 self.doQuote(userpquota.WarnCount or 0), \
782                                 self.doQuote(userpquota.DateLimit), \
783                                 self.doQuote(userpquota.PageCounter or 0), \
784                                 self.doQuote(userpquota.LifePageCounter or 0), \
785                                 self.doQuote(userpquota.MaxJobSize), \
786                                 self.doQuote(userpquota.ident)))
787
788    def writeUserPQuotaWarnCount(self, userpquota, warncount) :
789        """Sets the warn counter value for a user quota."""
790        self.doModify("UPDATE userpquota SET warncount=%s WHERE id=%s" % (self.doQuote(warncount), self.doQuote(userpquota.ident)))
791
792    def increaseUserPQuotaWarnCount(self, userpquota) :
793        """Increases the warn counter value for a user quota."""
794        self.doModify("UPDATE userpquota SET warncount=warncount+1 WHERE id=%s" % self.doQuote(userpquota.ident))
795
796    def saveGroupPQuota(self, grouppquota) :
797        """Saves a group print quota entry."""
798        self.doModify("UPDATE grouppquota SET softlimit=%s, hardlimit=%s, datelimit=%s WHERE id=%s" \
799                              % (self.doQuote(grouppquota.SoftLimit), \
800                                 self.doQuote(grouppquota.HardLimit), \
801                                 self.doQuote(grouppquota.DateLimit), \
802                                 self.doQuote(grouppquota.ident)))
803
804    def writePrinterToGroup(self, pgroup, printer) :
805        """Puts a printer into a printer group."""
806        children = []
807        result = self.doSearch("SELECT printerid FROM printergroupsmembers WHERE groupid=%s" % self.doQuote(pgroup.ident))
808        if result :
809            for record in result :
810                children.append(record.get("printerid")) # TODO : put this into the database integrity rules
811        if printer.ident not in children :
812            self.doModify("INSERT INTO printergroupsmembers (groupid, printerid) VALUES (%s, %s)" % (self.doQuote(pgroup.ident), self.doQuote(printer.ident)))
813
814    def removePrinterFromGroup(self, pgroup, printer) :
815        """Removes a printer from a printer group."""
816        self.doModify("DELETE FROM printergroupsmembers WHERE groupid=%s AND printerid=%s" % (self.doQuote(pgroup.ident), self.doQuote(printer.ident)))
817
818    def retrieveHistory(self, user=None, printer=None, hostname=None, billingcode=None, jobid=None, limit=100, start=None, end=None) :
819        """Retrieves all print jobs for user on printer (or all) between start and end date, limited to first 100 results."""
820        query = "SELECT jobhistory.*,username,printername FROM jobhistory,users,printers WHERE users.id=userid AND printers.id=printerid"
821        where = []
822        if user is not None : # user.ident is None anyway if user doesn't exist
823            where.append("userid=%s" % self.doQuote(user.ident))
824        if printer is not None : # printer.ident is None anyway if printer doesn't exist
825            where.append("printerid=%s" % self.doQuote(printer.ident))
826        if hostname is not None :
827            where.append("hostname=%s" % self.doQuote(hostname))
828        if billingcode is not None :
829            where.append("billingcode=%s" % self.doQuote(unicodeToDatabase(billingcode)))
830        if jobid is not None :
831            where.append("jobid=%s" % self.doQuote(jobid)) # TODO : jobid is text, so unicodeToDatabase(jobid) but do all of them as well.
832        if start is not None :
833            where.append("jobdate>=%s" % self.doQuote(start))
834        if end is not None :
835            where.append("jobdate<=%s" % self.doQuote(end))
836        if where :
837            query += " AND %s" % " AND ".join(where)
838        query += " ORDER BY jobhistory.id DESC"
839        if limit :
840            # TODO : LIMIT is not supported under DB2.
841            # TODO : so we must use something like " FETCH FIRST %s ROWS ONLY" % self.doQuote(int(limit))
842            query += " LIMIT %s" % self.doQuote(int(limit))
843        jobs = []
844        result = self.doSearch(query)
845        if result :
846            for fields in result :
847                job = self.storageJobFromRecord(fields)
848                jobs.append(job)
849        return jobs
850
851    def deleteUser(self, user) :
852        """Completely deletes an user from the database."""
853        # TODO : What should we do if we delete the last person who used a given printer ?
854        # TODO : we can't reassign the last job to the previous one, because next user would be
855        # TODO : incorrectly charged (overcharged).
856        for q in [
857                    "DELETE FROM payments WHERE userid=%s" % self.doQuote(user.ident),
858                    "DELETE FROM groupsmembers WHERE userid=%s" % self.doQuote(user.ident),
859                    "DELETE FROM jobhistory WHERE userid=%s" % self.doQuote(user.ident),
860                    "DELETE FROM userpquota WHERE userid=%s" % self.doQuote(user.ident),
861                    "DELETE FROM users WHERE id=%s" % self.doQuote(user.ident),
862                  ] :
863            self.doModify(q)
864
865    def multipleQueriesInTransaction(self, queries) :
866        """Does many modifications in a single transaction."""
867        self.beginTransaction()
868        try :
869            for q in queries :
870                self.doModify(q)
871        except :
872            self.rollbackTransaction()
873            raise
874        else :
875            self.commitTransaction()
876
877    def deleteManyBillingCodes(self, billingcodes) :
878        """Deletes many billing codes."""
879        codeids = ", ".join(["%s" % self.doQuote(b.ident) for b in billingcodes])
880        if codeids :
881            self.multipleQueriesInTransaction([
882                    "DELETE FROM billingcodes WHERE id IN (%s)" % codeids,])
883
884    def deleteManyUsers(self, users) :
885        """Deletes many users."""
886        userids = ", ".join(["%s" % self.doQuote(u.ident) for u in users])
887        if userids :
888            self.multipleQueriesInTransaction([
889                    "DELETE FROM payments WHERE userid IN (%s)" % userids,
890                    "DELETE FROM groupsmembers WHERE userid IN (%s)" % userids,
891                    "DELETE FROM jobhistory WHERE userid IN (%s)" % userids,
892                    "DELETE FROM userpquota WHERE userid IN (%s)" % userids,
893                    "DELETE FROM users WHERE id IN (%s)" % userids,])
894
895    def deleteManyGroups(self, groups) :
896        """Deletes many groups."""
897        groupids = ", ".join(["%s" % self.doQuote(g.ident) for g in groups])
898        if groupids :
899            self.multipleQueriesInTransaction([
900                    "DELETE FROM groupsmembers WHERE groupid IN (%s)" % groupids,
901                    "DELETE FROM grouppquota WHERE groupid IN (%s)" % groupids,
902                    "DELETE FROM groups WHERE id IN (%s)" % groupids,])
903
904    def deleteManyPrinters(self, printers) :
905        """Deletes many printers."""
906        printerids = ", ".join(["%s" % self.doQuote(p.ident) for p in printers])
907        if printerids :
908            self.multipleQueriesInTransaction([
909                    "DELETE FROM printergroupsmembers WHERE groupid IN (%s) OR printerid IN (%s)" % (printerids, printerids),
910                    "DELETE FROM jobhistory WHERE printerid IN (%s)" % printerids,
911                    "DELETE FROM grouppquota WHERE printerid IN (%s)" % printerids,
912                    "DELETE FROM userpquota WHERE printerid IN (%s)" % printerids,
913                    "DELETE FROM printers WHERE id IN (%s)" % printerids,])
914
915    def deleteManyUserPQuotas(self, printers, users) :
916        """Deletes many user print quota entries."""
917        printerids = ", ".join(["%s" % self.doQuote(p.ident) for p in printers])
918        userids = ", ".join(["%s" % self.doQuote(u.ident) for u in users])
919        if userids and printerids :
920            self.multipleQueriesInTransaction([
921                    "DELETE FROM jobhistory WHERE userid IN (%s) AND printerid IN (%s)" \
922                                 % (userids, printerids),
923                    "DELETE FROM userpquota WHERE userid IN (%s) AND printerid IN (%s)" \
924                                 % (userids, printerids),])
925
926    def deleteManyGroupPQuotas(self, printers, groups) :
927        """Deletes many group print quota entries."""
928        printerids = ", ".join(["%s" % self.doQuote(p.ident) for p in printers])
929        groupids = ", ".join(["%s" % self.doQuote(g.ident) for g in groups])
930        if groupids and printerids :
931            self.multipleQueriesInTransaction([
932                    "DELETE FROM grouppquota WHERE groupid IN (%s) AND printerid IN (%s)" \
933                                 % (groupids, printerids),])
934
935    def deleteUserPQuota(self, upquota) :
936        """Completely deletes an user print quota entry from the database."""
937        for q in [
938                    "DELETE FROM jobhistory WHERE userid=%s AND printerid=%s" \
939                                 % (self.doQuote(upquota.User.ident), self.doQuote(upquota.Printer.ident)),
940                    "DELETE FROM userpquota WHERE id=%s" % self.doQuote(upquota.ident),
941                  ] :
942            self.doModify(q)
943
944    def deleteGroupPQuota(self, gpquota) :
945        """Completely deletes a group print quota entry from the database."""
946        for q in [
947                    "DELETE FROM grouppquota WHERE id=%s" % self.doQuote(gpquota.ident),
948                  ] :
949            self.doModify(q)
950
951    def deleteGroup(self, group) :
952        """Completely deletes a group from the database."""
953        for q in [
954                   "DELETE FROM groupsmembers WHERE groupid=%s" % self.doQuote(group.ident),
955                   "DELETE FROM grouppquota WHERE groupid=%s" % self.doQuote(group.ident),
956                   "DELETE FROM groups WHERE id=%s" % self.doQuote(group.ident),
957                 ] :
958            self.doModify(q)
959
960    def deletePrinter(self, printer) :
961        """Completely deletes a printer from the database."""
962        for q in [
963                    "DELETE FROM printergroupsmembers WHERE groupid=%s OR printerid=%s" % (self.doQuote(printer.ident), self.doQuote(printer.ident)),
964                    "DELETE FROM jobhistory WHERE printerid=%s" % self.doQuote(printer.ident),
965                    "DELETE FROM grouppquota WHERE printerid=%s" % self.doQuote(printer.ident),
966                    "DELETE FROM userpquota WHERE printerid=%s" % self.doQuote(printer.ident),
967                    "DELETE FROM printers WHERE id=%s" % self.doQuote(printer.ident),
968                  ] :
969            self.doModify(q)
970
971    def deleteBillingCode(self, code) :
972        """Completely deletes a billing code from the database."""
973        for q in [
974                   "DELETE FROM billingcodes WHERE id=%s" % self.doQuote(code.ident),
975                 ] :
976            self.doModify(q)
Note: See TracBrowser for help on using the browser.