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

Revision 3524, 53.7 kB (checked in by jerome, 12 years ago)

Improved the retrival of printers and billingcodes as well as
users. References #52. For users groups it will be harder to do, next
time :-)

  • 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 getMatchingStuff(self, pattern, tablename, entrytype, keyname) :
450        """Returns the list of all entries for which the name matches a certain pattern."""
451        entries = []
452        # We 'could' do a SELECT xxxxname FROM xxxx WHERE xxxxname LIKE ...
453        # but we don't because other storages semantics may be different, so every
454        # storage should use fnmatch to match patterns and be storage agnostic
455        #
456        # This doesn't prevent us from being smarter, thanks to bse@chalmers.se
457        pattern = pattern or "*"
458        patterns = pattern.split(",")
459        patdict = {}.fromkeys(patterns)
460        patterns = patdict.keys() # Ensures the uniqueness of each pattern, but we lose the cmd line ordering
461        # BEWARE : if a single pattern contains wild cards, we'll still use the slow route.
462        storageEntryFromRecord = getattr(self, "storage%sFromRecord" % entrytype)
463        cachename = tablename.upper()
464        if self.hasWildCards(pattern) :
465            # Slow route
466            result = self.doSearch("SELECT * FROM %s" % tablename)
467            if result :
468                for record in result :
469                    name = databaseToUnicode(record[keyname])
470                    if patdict.has_key(name) or self.tool.matchString(name, patterns) :
471                        entry = storageEntryFromRecord(name, record)
472                        entries.append(entry)
473                        self.cacheEntry(cachename, entry.Name, entry)
474        else :
475            # Fast route (probably not faster with a few entries)
476            while patterns :
477                subset = patterns[:MAXINNAMES]
478                nbpatterns = len(subset)
479                if nbpatterns == 1 :
480                    wherestmt = "%s=%s" % (keyname, self.doQuote(unicodeToDatabase(subset[0])))
481                else :
482                    wherestmt = "%s IN (%s)" % (keyname,
483                                                ",".join([self.doQuote(unicodeToDatabase(p)) for p in subset]))
484                result = self.doSearch("SELECT * FROM %s WHERE %s" % (tablename,
485                                                                      wherestmt))
486                if result :
487                    for record in result :
488                        name = databaseToUnicode(record[keyname])
489                        entry = storageEntryFromRecord(name, record)
490                        entries.append(entry)
491                        self.cacheEntry(cachename, entry.Name, entry)
492                patterns = patterns[MAXINNAMES:]
493        entries.sort(key=lambda e : e.Name) # Adds some ordering, we've already lost the cmd line one anyway.
494        return entries
495
496    def getMatchingPrinters(self, pattern) :
497        """Returns the list of all printers for which name matches a certain pattern."""
498        return self.getMatchingStuff(pattern, "printers", "Printer", "printername")
499
500    def getMatchingUsers(self, pattern) :
501        """Returns the list of all users for which name matches a certain pattern."""
502        return self.getMatchingStuff(pattern, "users", "User", "username")
503
504    def getMatchingBillingCodes(self, pattern) :
505        """Returns the list of all billing codes for which the label matches a certain pattern."""
506        return self.getMatchingStuff(pattern, "billingcodes", "BillingCode", "billingcode")
507
508    def getMatchingGroups(self, grouppattern) :
509        """Returns the list of all groups for which name matches a certain pattern."""
510        groups = []
511        # We 'could' do a SELECT groupname FROM groups WHERE groupname LIKE ...
512        # but we don't because other storages semantics may be different, so every
513        # storage should use fnmatch to match patterns and be storage agnostic
514        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")
515        if result :
516            patterns = grouppattern.split(",")
517            patdict = {}.fromkeys(patterns)
518            for record in result :
519                gname = databaseToUnicode(record["groupname"])
520                if patdict.has_key(gname) or self.tool.matchString(gname, patterns) :
521                    group = self.storageGroupFromRecord(gname, record)
522                    groups.append(group)
523                    self.cacheEntry("GROUPS", group.Name, group)
524        return groups
525
526    def getPrinterUsersAndQuotas(self, printer, names=["*"]) :
527        """Returns the list of users who uses a given printer, along with their quotas."""
528        usersandquotas = []
529        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))
530        if result :
531            for record in result :
532                uname = databaseToUnicode(record.get("username"))
533                if self.tool.matchString(uname, names) :
534                    user = self.storageUserFromRecord(uname, record)
535                    userpquota = self.storageUserPQuotaFromRecord(user, printer, record)
536                    usersandquotas.append((user, userpquota))
537                    self.cacheEntry("USERS", user.Name, user)
538                    self.cacheEntry("USERPQUOTAS", "%s@%s" % (user.Name, printer.Name), userpquota)
539        return usersandquotas
540
541    def getPrinterGroupsAndQuotas(self, printer, names=["*"]) :
542        """Returns the list of groups which uses a given printer, along with their quotas."""
543        groupsandquotas = []
544        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))
545        if result :
546            for record in result :
547                gname = databaseToUnicode(record.get("groupname"))
548                if self.tool.matchString(gname, names) :
549                    group = self.getGroup(gname)
550                    grouppquota = self.getGroupPQuota(group, printer)
551                    groupsandquotas.append((group, grouppquota))
552        return groupsandquotas
553
554    def addPrinter(self, printer) :
555        """Adds a printer to the quota storage, returns the old value if it already exists."""
556        oldentry = self.getPrinter(printer.Name)
557        if oldentry.Exists :
558            return oldentry
559        self.doModify("INSERT INTO printers (printername, passthrough, maxjobsize, description, priceperpage, priceperjob) VALUES (%s, %s, %s, %s, %s, %s)" \
560                          % (self.doQuote(unicodeToDatabase(printer.Name)), \
561                             self.doQuote((printer.PassThrough and "t") or "f"), \
562                             self.doQuote(printer.MaxJobSize or 0), \
563                             self.doQuote(unicodeToDatabase(printer.Description)), \
564                             self.doQuote(printer.PricePerPage or 0.0), \
565                             self.doQuote(printer.PricePerJob or 0.0)))
566        printer.isDirty = False
567        return None # the entry created doesn't need further modification
568
569    def addBillingCode(self, bcode) :
570        """Adds a billing code to the quota storage, returns the old value if it already exists."""
571        oldentry = self.getBillingCode(bcode.BillingCode)
572        if oldentry.Exists :
573            return oldentry
574        self.doModify("INSERT INTO billingcodes (billingcode, balance, pagecounter, description) VALUES (%s, %s, %s, %s)" \
575                           % (self.doQuote(unicodeToDatabase(bcode.BillingCode)),
576                              self.doQuote(bcode.Balance or 0.0), \
577                              self.doQuote(bcode.PageCounter or 0), \
578                              self.doQuote(unicodeToDatabase(bcode.Description))))
579        bcode.isDirty = False
580        return None # the entry created doesn't need further modification
581
582    def addUser(self, user) :
583        """Adds a user to the quota storage, returns the old value if it already exists."""
584        oldentry = self.getUser(user.Name)
585        if oldentry.Exists :
586            return oldentry
587        self.doModify("INSERT INTO users (username, limitby, balance, lifetimepaid, email, overcharge, description) VALUES (%s, %s, %s, %s, %s, %s, %s)" % \
588                                     (self.doQuote(unicodeToDatabase(user.Name)), \
589                                      self.doQuote(user.LimitBy or 'quota'), \
590                                      self.doQuote(user.AccountBalance or 0.0), \
591                                      self.doQuote(user.LifeTimePaid or 0.0), \
592                                      self.doQuote(user.Email), \
593                                      self.doQuote(user.OverCharge), \
594                                      self.doQuote(unicodeToDatabase(user.Description))))
595        if user.PaymentsBacklog :
596            for (value, comment) in user.PaymentsBacklog :
597                self.writeNewPayment(user, value, comment)
598            user.PaymentsBacklog = []
599        user.isDirty = False
600        return None # the entry created doesn't need further modification
601
602    def addGroup(self, group) :
603        """Adds a group to the quota storage, returns the old value if it already exists."""
604        oldentry = self.getGroup(group.Name)
605        if oldentry.Exists :
606            return oldentry
607        self.doModify("INSERT INTO groups (groupname, limitby, description) VALUES (%s, %s, %s)" % \
608                              (self.doQuote(unicodeToDatabase(group.Name)), \
609                               self.doQuote(group.LimitBy or "quota"), \
610                               self.doQuote(unicodeToDatabase(group.Description))))
611        group.isDirty = False
612        return None # the entry created doesn't need further modification
613
614    def addUserToGroup(self, user, group) :
615        """Adds an user to a group."""
616        result = self.doSearch("SELECT COUNT(*) AS mexists FROM groupsmembers WHERE groupid=%s AND userid=%s" % (self.doQuote(group.ident), self.doQuote(user.ident)))
617        try :
618            mexists = int(result[0].get("mexists"))
619        except (IndexError, TypeError) :
620            mexists = 0
621        if not mexists :
622            self.doModify("INSERT INTO groupsmembers (groupid, userid) VALUES (%s, %s)" % (self.doQuote(group.ident), self.doQuote(user.ident)))
623
624    def delUserFromGroup(self, user, group) :
625        """Removes an user from a group."""
626        self.doModify("DELETE FROM groupsmembers WHERE groupid=%s AND userid=%s" % \
627                       (self.doQuote(group.ident), self.doQuote(user.ident)))
628
629    def addUserPQuota(self, upq) :
630        """Initializes a user print quota on a printer."""
631        oldentry = self.getUserPQuota(upq.User, upq.Printer)
632        if oldentry.Exists :
633            return oldentry
634        self.doModify("INSERT INTO userpquota (userid, printerid, softlimit, hardlimit, warncount, datelimit, pagecounter, lifepagecounter, maxjobsize) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)" \
635                          % (self.doQuote(upq.User.ident), \
636                             self.doQuote(upq.Printer.ident), \
637                             self.doQuote(upq.SoftLimit), \
638                             self.doQuote(upq.HardLimit), \
639                             self.doQuote(upq.WarnCount or 0), \
640                             self.doQuote(upq.DateLimit), \
641                             self.doQuote(upq.PageCounter or 0), \
642                             self.doQuote(upq.LifePageCounter or 0), \
643                             self.doQuote(upq.MaxJobSize)))
644        upq.isDirty = False
645        return None # the entry created doesn't need further modification
646
647    def addGroupPQuota(self, gpq) :
648        """Initializes a group print quota on a printer."""
649        oldentry = self.getGroupPQuota(gpq.Group, gpq.Printer)
650        if oldentry.Exists :
651            return oldentry
652        self.doModify("INSERT INTO grouppquota (groupid, printerid, softlimit, hardlimit, datelimit, maxjobsize) VALUES (%s, %s, %s, %s, %s, %s)" \
653                          % (self.doQuote(gpq.Group.ident), \
654                             self.doQuote(gpq.Printer.ident), \
655                             self.doQuote(gpq.SoftLimit), \
656                             self.doQuote(gpq.HardLimit), \
657                             self.doQuote(gpq.DateLimit), \
658                             self.doQuote(gpq.MaxJobSize)))
659        gpq.isDirty = False
660        return None # the entry created doesn't need further modification
661
662    def savePrinter(self, printer) :
663        """Saves the printer to the database in a single operation."""
664        self.doModify("UPDATE printers SET passthrough=%s, maxjobsize=%s, description=%s, priceperpage=%s, priceperjob=%s WHERE id=%s" \
665                              % (self.doQuote((printer.PassThrough and "t") or "f"), \
666                                 self.doQuote(printer.MaxJobSize or 0), \
667                                 self.doQuote(unicodeToDatabase(printer.Description)), \
668                                 self.doQuote(printer.PricePerPage or 0.0), \
669                                 self.doQuote(printer.PricePerJob or 0.0), \
670                                 self.doQuote(printer.ident)))
671
672    def saveUser(self, user) :
673        """Saves the user to the database in a single operation."""
674        self.doModify("UPDATE users SET limitby=%s, balance=%s, lifetimepaid=%s, email=%s, overcharge=%s, description=%s WHERE id=%s" \
675                               % (self.doQuote(user.LimitBy or 'quota'), \
676                                  self.doQuote(user.AccountBalance or 0.0), \
677                                  self.doQuote(user.LifeTimePaid or 0.0), \
678                                  self.doQuote(user.Email), \
679                                  self.doQuote(user.OverCharge), \
680                                  self.doQuote(unicodeToDatabase(user.Description)), \
681                                  self.doQuote(user.ident)))
682
683    def saveGroup(self, group) :
684        """Saves the group to the database in a single operation."""
685        self.doModify("UPDATE groups SET limitby=%s, description=%s WHERE id=%s" \
686                               % (self.doQuote(group.LimitBy or 'quota'), \
687                                  self.doQuote(unicodeToDatabase(group.Description)), \
688                                  self.doQuote(group.ident)))
689
690    def writeUserPQuotaDateLimit(self, userpquota, datelimit) :
691        """Sets the date limit permanently for a user print quota."""
692        self.doModify("UPDATE userpquota SET datelimit=%s WHERE id=%s" % (self.doQuote(datelimit), self.doQuote(userpquota.ident)))
693
694    def writeGroupPQuotaDateLimit(self, grouppquota, datelimit) :
695        """Sets the date limit permanently for a group print quota."""
696        self.doModify("UPDATE grouppquota SET datelimit=%s WHERE id=%s" % (self.doQuote(datelimit), self.doQuote(grouppquota.ident)))
697
698    def increaseUserPQuotaPagesCounters(self, userpquota, nbpages) :
699        """Increase page counters for a user print quota."""
700        self.doModify("UPDATE userpquota SET pagecounter=pagecounter + %s,lifepagecounter=lifepagecounter + %s WHERE id=%s" % (self.doQuote(nbpages), self.doQuote(nbpages), self.doQuote(userpquota.ident)))
701
702    def saveBillingCode(self, bcode) :
703        """Saves the billing code to the database."""
704        self.doModify("UPDATE billingcodes SET balance=%s, pagecounter=%s, description=%s WHERE id=%s" \
705                            % (self.doQuote(bcode.Balance or 0.0), \
706                               self.doQuote(bcode.PageCounter or 0), \
707                               self.doQuote(unicodeToDatabase(bcode.Description)), \
708                               self.doQuote(bcode.ident)))
709
710    def consumeBillingCode(self, bcode, pagecounter, balance) :
711        """Consumes from a billing code."""
712        self.doModify("UPDATE billingcodes SET balance=balance + %s, pagecounter=pagecounter + %s WHERE id=%s" % (self.doQuote(balance), self.doQuote(pagecounter), self.doQuote(bcode.ident)))
713
714    def refundJob(self, jobident) :
715        """Marks a job as refunded in the history."""
716        self.doModify("UPDATE jobhistory SET action='REFUND' WHERE id=%s;" % self.doQuote(jobident))
717
718    def decreaseUserAccountBalance(self, user, amount) :
719        """Decreases user's account balance from an amount."""
720        self.doModify("UPDATE users SET balance=balance - %s WHERE id=%s" % (self.doQuote(amount), self.doQuote(user.ident)))
721
722    def writeNewPayment(self, user, amount, comment="") :
723        """Adds a new payment to the payments history."""
724        if user.ident is not None :
725            self.doModify("INSERT INTO payments (userid, amount, description) VALUES (%s, %s, %s)" % (self.doQuote(user.ident), self.doQuote(amount), self.doQuote(unicodeToDatabase(comment))))
726        else :
727            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))))
728
729    def writeLastJobSize(self, lastjob, jobsize, jobprice) :
730        """Sets the last job's size permanently."""
731        self.doModify("UPDATE jobhistory SET jobsize=%s, jobprice=%s WHERE id=%s" % (self.doQuote(jobsize), self.doQuote(jobprice), self.doQuote(lastjob.ident)))
732
733    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) :
734        """Adds a job in a printer's history."""
735        if self.privacy :
736            # For legal reasons, we want to hide the title, filename and options
737            title = filename = options = "hidden"
738        filename = unicodeToDatabase(filename)
739        title = unicodeToDatabase(title)
740        options = unicodeToDatabase(options)
741        jobbilling = unicodeToDatabase(jobbilling)
742        if (not self.disablehistory) or (not printer.LastJob.Exists) :
743            if jobsize is not None :
744                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)))
745            else :
746                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)))
747        else :
748            # here we explicitly want to reset jobsize to NULL if needed
749            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)))
750
751    def saveUserPQuota(self, userpquota) :
752        """Saves an user print quota entry."""
753        self.doModify("UPDATE userpquota SET softlimit=%s, hardlimit=%s, warncount=%s, datelimit=%s, pagecounter=%s, lifepagecounter=%s, maxjobsize=%s WHERE id=%s" \
754                              % (self.doQuote(userpquota.SoftLimit), \
755                                 self.doQuote(userpquota.HardLimit), \
756                                 self.doQuote(userpquota.WarnCount or 0), \
757                                 self.doQuote(userpquota.DateLimit), \
758                                 self.doQuote(userpquota.PageCounter or 0), \
759                                 self.doQuote(userpquota.LifePageCounter or 0), \
760                                 self.doQuote(userpquota.MaxJobSize), \
761                                 self.doQuote(userpquota.ident)))
762
763    def writeUserPQuotaWarnCount(self, userpquota, warncount) :
764        """Sets the warn counter value for a user quota."""
765        self.doModify("UPDATE userpquota SET warncount=%s WHERE id=%s" % (self.doQuote(warncount), self.doQuote(userpquota.ident)))
766
767    def increaseUserPQuotaWarnCount(self, userpquota) :
768        """Increases the warn counter value for a user quota."""
769        self.doModify("UPDATE userpquota SET warncount=warncount+1 WHERE id=%s" % self.doQuote(userpquota.ident))
770
771    def saveGroupPQuota(self, grouppquota) :
772        """Saves a group print quota entry."""
773        self.doModify("UPDATE grouppquota SET softlimit=%s, hardlimit=%s, datelimit=%s WHERE id=%s" \
774                              % (self.doQuote(grouppquota.SoftLimit), \
775                                 self.doQuote(grouppquota.HardLimit), \
776                                 self.doQuote(grouppquota.DateLimit), \
777                                 self.doQuote(grouppquota.ident)))
778
779    def writePrinterToGroup(self, pgroup, printer) :
780        """Puts a printer into a printer group."""
781        children = []
782        result = self.doSearch("SELECT printerid FROM printergroupsmembers WHERE groupid=%s" % self.doQuote(pgroup.ident))
783        if result :
784            for record in result :
785                children.append(record.get("printerid")) # TODO : put this into the database integrity rules
786        if printer.ident not in children :
787            self.doModify("INSERT INTO printergroupsmembers (groupid, printerid) VALUES (%s, %s)" % (self.doQuote(pgroup.ident), self.doQuote(printer.ident)))
788
789    def removePrinterFromGroup(self, pgroup, printer) :
790        """Removes a printer from a printer group."""
791        self.doModify("DELETE FROM printergroupsmembers WHERE groupid=%s AND printerid=%s" % (self.doQuote(pgroup.ident), self.doQuote(printer.ident)))
792
793    def retrieveHistory(self, user=None, printer=None, hostname=None, billingcode=None, jobid=None, limit=100, start=None, end=None) :
794        """Retrieves all print jobs for user on printer (or all) between start and end date, limited to first 100 results."""
795        query = "SELECT jobhistory.*,username,printername FROM jobhistory,users,printers WHERE users.id=userid AND printers.id=printerid"
796        where = []
797        if user is not None : # user.ident is None anyway if user doesn't exist
798            where.append("userid=%s" % self.doQuote(user.ident))
799        if printer is not None : # printer.ident is None anyway if printer doesn't exist
800            where.append("printerid=%s" % self.doQuote(printer.ident))
801        if hostname is not None :
802            where.append("hostname=%s" % self.doQuote(hostname))
803        if billingcode is not None :
804            where.append("billingcode=%s" % self.doQuote(unicodeToDatabase(billingcode)))
805        if jobid is not None :
806            where.append("jobid=%s" % self.doQuote(jobid)) # TODO : jobid is text, so unicodeToDatabase(jobid) but do all of them as well.
807        if start is not None :
808            where.append("jobdate>=%s" % self.doQuote(start))
809        if end is not None :
810            where.append("jobdate<=%s" % self.doQuote(end))
811        if where :
812            query += " AND %s" % " AND ".join(where)
813        query += " ORDER BY jobhistory.id DESC"
814        if limit :
815            # TODO : LIMIT is not supported under DB2.
816            # TODO : so we must use something like " FETCH FIRST %s ROWS ONLY" % self.doQuote(int(limit))
817            query += " LIMIT %s" % self.doQuote(int(limit))
818        jobs = []
819        result = self.doSearch(query)
820        if result :
821            for fields in result :
822                job = self.storageJobFromRecord(fields)
823                jobs.append(job)
824        return jobs
825
826    def deleteUser(self, user) :
827        """Completely deletes an user from the database."""
828        # TODO : What should we do if we delete the last person who used a given printer ?
829        # TODO : we can't reassign the last job to the previous one, because next user would be
830        # TODO : incorrectly charged (overcharged).
831        for q in [
832                    "DELETE FROM payments WHERE userid=%s" % self.doQuote(user.ident),
833                    "DELETE FROM groupsmembers WHERE userid=%s" % self.doQuote(user.ident),
834                    "DELETE FROM jobhistory WHERE userid=%s" % self.doQuote(user.ident),
835                    "DELETE FROM userpquota WHERE userid=%s" % self.doQuote(user.ident),
836                    "DELETE FROM users WHERE id=%s" % self.doQuote(user.ident),
837                  ] :
838            self.doModify(q)
839
840    def multipleQueriesInTransaction(self, queries) :
841        """Does many modifications in a single transaction."""
842        self.beginTransaction()
843        try :
844            for q in queries :
845                self.doModify(q)
846        except :
847            self.rollbackTransaction()
848            raise
849        else :
850            self.commitTransaction()
851
852    def deleteManyBillingCodes(self, billingcodes) :
853        """Deletes many billing codes."""
854        codeids = ", ".join(["%s" % self.doQuote(b.ident) for b in billingcodes])
855        if codeids :
856            self.multipleQueriesInTransaction([
857                    "DELETE FROM billingcodes WHERE id IN (%s)" % codeids,])
858
859    def deleteManyUsers(self, users) :
860        """Deletes many users."""
861        userids = ", ".join(["%s" % self.doQuote(u.ident) for u in users])
862        if userids :
863            self.multipleQueriesInTransaction([
864                    "DELETE FROM payments WHERE userid IN (%s)" % userids,
865                    "DELETE FROM groupsmembers WHERE userid IN (%s)" % userids,
866                    "DELETE FROM jobhistory WHERE userid IN (%s)" % userids,
867                    "DELETE FROM userpquota WHERE userid IN (%s)" % userids,
868                    "DELETE FROM users WHERE id IN (%s)" % userids,])
869
870    def deleteManyGroups(self, groups) :
871        """Deletes many groups."""
872        groupids = ", ".join(["%s" % self.doQuote(g.ident) for g in groups])
873        if groupids :
874            self.multipleQueriesInTransaction([
875                    "DELETE FROM groupsmembers WHERE groupid IN (%s)" % groupids,
876                    "DELETE FROM grouppquota WHERE groupid IN (%s)" % groupids,
877                    "DELETE FROM groups WHERE id IN (%s)" % groupids,])
878
879    def deleteManyPrinters(self, printers) :
880        """Deletes many printers."""
881        printerids = ", ".join(["%s" % self.doQuote(p.ident) for p in printers])
882        if printerids :
883            self.multipleQueriesInTransaction([
884                    "DELETE FROM printergroupsmembers WHERE groupid IN (%s) OR printerid IN (%s)" % (printerids, printerids),
885                    "DELETE FROM jobhistory WHERE printerid IN (%s)" % printerids,
886                    "DELETE FROM grouppquota WHERE printerid IN (%s)" % printerids,
887                    "DELETE FROM userpquota WHERE printerid IN (%s)" % printerids,
888                    "DELETE FROM printers WHERE id IN (%s)" % printerids,])
889
890    def deleteManyUserPQuotas(self, printers, users) :
891        """Deletes many user print quota entries."""
892        printerids = ", ".join(["%s" % self.doQuote(p.ident) for p in printers])
893        userids = ", ".join(["%s" % self.doQuote(u.ident) for u in users])
894        if userids and printerids :
895            self.multipleQueriesInTransaction([
896                    "DELETE FROM jobhistory WHERE userid IN (%s) AND printerid IN (%s)" \
897                                 % (userids, printerids),
898                    "DELETE FROM userpquota WHERE userid IN (%s) AND printerid IN (%s)" \
899                                 % (userids, printerids),])
900
901    def deleteManyGroupPQuotas(self, printers, groups) :
902        """Deletes many group print quota entries."""
903        printerids = ", ".join(["%s" % self.doQuote(p.ident) for p in printers])
904        groupids = ", ".join(["%s" % self.doQuote(g.ident) for g in groups])
905        if groupids and printerids :
906            self.multipleQueriesInTransaction([
907                    "DELETE FROM grouppquota WHERE groupid IN (%s) AND printerid IN (%s)" \
908                                 % (groupids, printerids),])
909
910    def deleteUserPQuota(self, upquota) :
911        """Completely deletes an user print quota entry from the database."""
912        for q in [
913                    "DELETE FROM jobhistory WHERE userid=%s AND printerid=%s" \
914                                 % (self.doQuote(upquota.User.ident), self.doQuote(upquota.Printer.ident)),
915                    "DELETE FROM userpquota WHERE id=%s" % self.doQuote(upquota.ident),
916                  ] :
917            self.doModify(q)
918
919    def deleteGroupPQuota(self, gpquota) :
920        """Completely deletes a group print quota entry from the database."""
921        for q in [
922                    "DELETE FROM grouppquota WHERE id=%s" % self.doQuote(gpquota.ident),
923                  ] :
924            self.doModify(q)
925
926    def deleteGroup(self, group) :
927        """Completely deletes a group from the database."""
928        for q in [
929                   "DELETE FROM groupsmembers WHERE groupid=%s" % self.doQuote(group.ident),
930                   "DELETE FROM grouppquota WHERE groupid=%s" % self.doQuote(group.ident),
931                   "DELETE FROM groups WHERE id=%s" % self.doQuote(group.ident),
932                 ] :
933            self.doModify(q)
934
935    def deletePrinter(self, printer) :
936        """Completely deletes a printer from the database."""
937        for q in [
938                    "DELETE FROM printergroupsmembers WHERE groupid=%s OR printerid=%s" % (self.doQuote(printer.ident), self.doQuote(printer.ident)),
939                    "DELETE FROM jobhistory WHERE printerid=%s" % self.doQuote(printer.ident),
940                    "DELETE FROM grouppquota WHERE printerid=%s" % self.doQuote(printer.ident),
941                    "DELETE FROM userpquota WHERE printerid=%s" % self.doQuote(printer.ident),
942                    "DELETE FROM printers WHERE id=%s" % self.doQuote(printer.ident),
943                  ] :
944            self.doModify(q)
945
946    def deleteBillingCode(self, code) :
947        """Completely deletes a billing code from the database."""
948        for q in [
949                   "DELETE FROM billingcodes WHERE id=%s" % self.doQuote(code.ident),
950                 ] :
951            self.doModify(q)
Note: See TracBrowser for help on using the browser.