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

Revision 2593, 41.2 kB (checked in by jerome, 18 years ago)

Added support for SQLite3 database backend.
NEEDS TESTERS !

  • Property svn:eol-style set to native
  • Property svn:keywords set to Author Date Id Revision
Line 
1# PyKota
2# -*- coding: ISO-8859-15 -*-
3#
4# PyKota : Print Quotas for CUPS and LPRng
5#
6# (c) 2003-2004 Jerome Alet <alet@librelogiciel.com>
7# This program is free software; you can redistribute it and/or modify
8# it under the terms of the GNU General Public License as published by
9# the Free Software Foundation; either version 2 of the License, or
10# (at your option) any later version.
11#
12# This program is distributed in the hope that it will be useful,
13# but WITHOUT ANY WARRANTY; without even the implied warranty of
14# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
15# GNU General Public License for more details.
16#
17# You should have received a copy of the GNU General Public License
18# along with this program; if not, write to the Free Software
19# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
20#
21# $Id$
22#
23#
24
25from types import StringType
26from pykota.storage import PyKotaStorageError, BaseStorage, StorageObject, \
27                           StorageUser, StorageGroup, StoragePrinter, \
28                           StorageJob, StorageLastJob, StorageUserPQuota, \
29                           StorageGroupPQuota, StorageBillingCode
30
31class SQLStorage :
32    def createFilter(self, only) :   
33        """Returns the appropriate SQL filter."""
34        if only :
35            expressions = []
36            for (k, v) in only.items() :
37                expressions.append("%s=%s" % (k, self.doQuote(self.userCharsetToDatabase(v))))
38            return " AND ".join(expressions)     
39        return ""       
40       
41    def extractPrinters(self, extractonly={}) :
42        """Extracts all printer records."""
43        thefilter = self.createFilter(extractonly)
44        if thefilter :
45            thefilter = "WHERE %s" % thefilter
46        result = self.doRawSearch("SELECT * FROM printers %s ORDER BY id ASC" % thefilter)
47        return self.prepareRawResult(result)
48       
49    def extractUsers(self, extractonly={}) :
50        """Extracts all user records."""
51        thefilter = self.createFilter(extractonly)
52        if thefilter :
53            thefilter = "WHERE %s" % thefilter
54        result = self.doRawSearch("SELECT * FROM users %s ORDER BY id ASC" % thefilter)
55        return self.prepareRawResult(result)
56       
57    def extractBillingcodes(self, extractonly={}) :
58        """Extracts all billing codes records."""
59        thefilter = self.createFilter(extractonly)
60        if thefilter :
61            thefilter = "WHERE %s" % thefilter
62        result = self.doRawSearch("SELECT * FROM billingcodes %s ORDER BY id ASC" % thefilter)
63        return self.prepareRawResult(result)
64       
65    def extractGroups(self, extractonly={}) :
66        """Extracts all group records."""
67        thefilter = self.createFilter(extractonly)
68        if thefilter :
69            thefilter = "WHERE %s" % thefilter
70        result = self.doRawSearch("SELECT groups.*,COALESCE(SUM(balance), 0) AS balance, COALESCE(SUM(lifetimepaid), 0) as lifetimepaid FROM groups LEFT OUTER JOIN users ON users.id IN (SELECT userid FROM groupsmembers WHERE groupid=groups.id) %s GROUP BY groups.id,groups.groupname,groups.limitby,groups.description ORDER BY groups.id ASC" % thefilter)
71        return self.prepareRawResult(result)
72       
73    def extractPayments(self, extractonly={}) :
74        """Extracts all payment records."""
75        thefilter = self.createFilter(extractonly)
76        if thefilter :
77            thefilter = "AND %s" % thefilter
78        result = self.doRawSearch("SELECT username,payments.* FROM users,payments WHERE users.id=payments.userid %s ORDER BY payments.id ASC" % thefilter)
79        return self.prepareRawResult(result)
80       
81    def extractUpquotas(self, extractonly={}) :
82        """Extracts all userpquota records."""
83        thefilter = self.createFilter(extractonly)
84        if thefilter :
85            thefilter = "AND %s" % thefilter
86        result = self.doRawSearch("SELECT users.username,printers.printername,userpquota.* FROM users,printers,userpquota WHERE users.id=userpquota.userid AND printers.id=userpquota.printerid %s ORDER BY userpquota.id ASC" % thefilter)
87        return self.prepareRawResult(result)
88       
89    def extractGpquotas(self, extractonly={}) :
90        """Extracts all grouppquota records."""
91        thefilter = self.createFilter(extractonly)
92        if thefilter :
93            thefilter = "AND %s" % thefilter
94        result = self.doRawSearch("SELECT groups.groupname,printers.printername,grouppquota.*,coalesce(sum(pagecounter), 0) AS pagecounter,coalesce(sum(lifepagecounter), 0) AS lifepagecounter FROM groups,printers,grouppquota,userpquota WHERE groups.id=grouppquota.groupid AND printers.id=grouppquota.printerid AND userpquota.printerid=grouppquota.printerid AND userpquota.userid IN (SELECT userid FROM groupsmembers WHERE groupsmembers.groupid=grouppquota.groupid) %s GROUP BY grouppquota.id,grouppquota.groupid,grouppquota.printerid,grouppquota.softlimit,grouppquota.hardlimit,grouppquota.datelimit,grouppquota.maxjobsize,groups.groupname,printers.printername ORDER BY grouppquota.id" % thefilter)
95        return self.prepareRawResult(result)
96       
97    def extractUmembers(self, extractonly={}) :
98        """Extracts all user groups members."""
99        thefilter = self.createFilter(extractonly)
100        if thefilter :
101            thefilter = "AND %s" % thefilter
102        result = self.doRawSearch("SELECT groups.groupname, users.username, groupsmembers.* FROM groups,users,groupsmembers WHERE users.id=groupsmembers.userid AND groups.id=groupsmembers.groupid %s ORDER BY groupsmembers.groupid, groupsmembers.userid ASC" % thefilter)
103        return self.prepareRawResult(result)
104       
105    def extractPmembers(self, extractonly={}) :
106        """Extracts all printer groups members."""
107        for (k, v) in extractonly.items() :
108            if k == "pgroupname" :
109                del extractonly[k]
110                extractonly["p1.printername"] = v
111            elif k == "printername" :
112                del extractonly[k]
113                extractonly["p2.printername"] = v
114        thefilter = self.createFilter(extractonly)
115        if thefilter :
116            thefilter = "AND %s" % thefilter
117        result = self.doRawSearch("SELECT p1.printername as pgroupname, p2.printername as printername, printergroupsmembers.* FROM printers p1, printers p2, printergroupsmembers WHERE p1.id=printergroupsmembers.groupid AND p2.id=printergroupsmembers.printerid %s ORDER BY printergroupsmembers.groupid, printergroupsmembers.printerid ASC" % thefilter)
118        return self.prepareRawResult(result)
119       
120    def extractHistory(self, extractonly={}) :
121        """Extracts all jobhistory records."""
122        startdate = extractonly.get("start")
123        enddate = extractonly.get("end")
124        for limit in ("start", "end") :
125            try :
126                del extractonly[limit]
127            except KeyError :   
128                pass
129        thefilter = self.createFilter(extractonly)
130        if thefilter :
131            thefilter = "AND %s" % thefilter
132        (startdate, enddate) = self.cleanDates(startdate, enddate)
133        if startdate and enddate : 
134            thefilter = "%s AND jobdate>=%s AND jobdate<=%s" % (thefilter, self.doQuote(startdate), self.doQuote(enddate))
135        result = self.doRawSearch("SELECT users.username,printers.printername,jobhistory.* FROM users,printers,jobhistory WHERE users.id=jobhistory.userid AND printers.id=jobhistory.printerid %s ORDER BY jobhistory.id ASC" % thefilter)
136        return self.prepareRawResult(result)
137           
138    def getAllUsersNames(self) :   
139        """Extracts all user names."""
140        usernames = []
141        result = self.doSearch("SELECT username FROM users")
142        if result :
143            usernames = [record["username"] for record in result]
144        return usernames
145       
146    def getAllGroupsNames(self) :   
147        """Extracts all group names."""
148        groupnames = []
149        result = self.doSearch("SELECT groupname FROM groups")
150        if result :
151            groupnames = [record["groupname"] for record in result]
152        return groupnames
153       
154    def getUserNbJobsFromHistory(self, user) :
155        """Returns the number of jobs the user has in history."""
156        result = self.doSearch("SELECT COUNT(*) FROM jobhistory WHERE userid=%s" % self.doQuote(user.ident))
157        if result :
158            return result[0]["count"]
159        return 0
160       
161    def getUserFromBackend(self, username) :   
162        """Extracts user information given its name."""
163        user = StorageUser(self, username)
164        result = self.doSearch("SELECT * FROM users WHERE username=%s LIMIT 1" % self.doQuote(username))
165        if result :
166            fields = result[0]
167            user.ident = fields.get("id")
168            user.Name = fields.get("username", username)
169            user.LimitBy = fields.get("limitby") or "quota"
170            user.AccountBalance = fields.get("balance")
171            user.LifeTimePaid = fields.get("lifetimepaid")
172            user.Email = fields.get("email")
173            user.OverCharge = fields.get("overcharge", 1.0)
174            user.Exists = 1
175        return user
176       
177    def getGroupFromBackend(self, groupname) :   
178        """Extracts group information given its name."""
179        group = StorageGroup(self, groupname)
180        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 LIMIT 1" % self.doQuote(groupname))
181        if result :
182            fields = result[0]
183            group.ident = fields.get("id")
184            group.Name = fields.get("groupname", groupname)
185            group.LimitBy = fields.get("limitby") or "quota"
186            group.AccountBalance = fields.get("balance")
187            group.LifeTimePaid = fields.get("lifetimepaid")
188            group.Exists = 1
189        return group
190       
191    def getPrinterFromBackend(self, printername) :       
192        """Extracts printer information given its name."""
193        printer = StoragePrinter(self, printername)
194        result = self.doSearch("SELECT * FROM printers WHERE printername=%s LIMIT 1" % self.doQuote(printername))
195        if result :
196            fields = result[0]
197            printer.ident = fields.get("id")
198            printer.Name = fields.get("printername", printername)
199            printer.PricePerJob = fields.get("priceperjob") or 0.0
200            printer.PricePerPage = fields.get("priceperpage") or 0.0
201            printer.MaxJobSize = fields.get("maxjobsize") or 0
202            printer.PassThrough = fields.get("passthrough") or 0
203            if printer.PassThrough in (1, "1", "t", "true", "TRUE", "True") :
204                printer.PassThrough = 1
205            else :
206                printer.PassThrough = 0
207            printer.Description = self.databaseToUserCharset(fields.get("description") or "")
208            printer.Exists = 1
209        return printer   
210       
211    def getBillingCodeFromBackend(self, label) :       
212        """Extracts a billing code information given its name."""
213        code = StorageBillingCode(self, label)
214        result = self.doSearch("SELECT * FROM billingcodes WHERE billingcode=%s LIMIT 1" % self.doQuote(self.userCharsetToDatabase(label)))
215        if result :
216            fields = result[0]
217            code.ident = fields.get("id")
218            code.BillingCode = self.databaseToUserCharset(fields.get("billingcode"))
219            code.Description = self.databaseToUserCharset(fields.get("description") or "")
220            code.Balance = fields.get("balance") or 0.0
221            code.PageCounter = fields.get("pagecounter") or 0
222            code.Exists = 1
223        return code   
224       
225    def getUserPQuotaFromBackend(self, user, printer) :       
226        """Extracts a user print quota."""
227        userpquota = StorageUserPQuota(self, user, printer)
228        if printer.Exists and user.Exists :
229            result = self.doSearch("SELECT * FROM userpquota WHERE userid=%s AND printerid=%s" % (self.doQuote(user.ident), self.doQuote(printer.ident)))
230            if result :
231                fields = result[0]
232                userpquota.ident = fields.get("id")
233                userpquota.PageCounter = fields.get("pagecounter")
234                userpquota.LifePageCounter = fields.get("lifepagecounter")
235                userpquota.SoftLimit = fields.get("softlimit")
236                userpquota.HardLimit = fields.get("hardlimit")
237                userpquota.DateLimit = fields.get("datelimit")
238                userpquota.WarnCount = fields.get("warncount")
239                userpquota.Exists = 1
240        return userpquota
241       
242    def getGroupPQuotaFromBackend(self, group, printer) :       
243        """Extracts a group print quota."""
244        grouppquota = StorageGroupPQuota(self, group, printer)
245        if group.Exists :
246            result = self.doSearch("SELECT * FROM grouppquota WHERE groupid=%s AND printerid=%s" % (self.doQuote(group.ident), self.doQuote(printer.ident)))
247            if result :
248                fields = result[0]
249                grouppquota.ident = fields.get("id")
250                grouppquota.SoftLimit = fields.get("softlimit")
251                grouppquota.HardLimit = fields.get("hardlimit")
252                grouppquota.DateLimit = fields.get("datelimit")
253                result = self.doSearch("SELECT SUM(lifepagecounter) AS lifepagecounter, SUM(pagecounter) AS pagecounter FROM userpquota WHERE printerid=%s AND userid IN (SELECT userid FROM groupsmembers WHERE groupid=%s)" % (self.doQuote(printer.ident), self.doQuote(group.ident)))
254                if result :
255                    fields = result[0]
256                    grouppquota.PageCounter = fields.get("pagecounter") or 0
257                    grouppquota.LifePageCounter = fields.get("lifepagecounter") or 0
258                grouppquota.Exists = 1
259        return grouppquota
260       
261    def getPrinterLastJobFromBackend(self, printer) :       
262        """Extracts a printer's last job information."""
263        lastjob = StorageLastJob(self, printer)
264        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 printerid=%s AND userid=users.id ORDER BY jobdate DESC LIMIT 1" % self.doQuote(printer.ident))
265        if result :
266            fields = result[0]
267            lastjob.ident = fields.get("id")
268            lastjob.JobId = fields.get("jobid")
269            lastjob.UserName = fields.get("username")
270            lastjob.PrinterPageCounter = fields.get("pagecounter")
271            lastjob.JobSize = fields.get("jobsize")
272            lastjob.JobPrice = fields.get("jobprice")
273            lastjob.JobAction = fields.get("action")
274            lastjob.JobFileName = self.databaseToUserCharset(fields.get("filename") or "") 
275            lastjob.JobTitle = self.databaseToUserCharset(fields.get("title") or "") 
276            lastjob.JobCopies = fields.get("copies")
277            lastjob.JobOptions = self.databaseToUserCharset(fields.get("options") or "") 
278            lastjob.JobDate = fields.get("jobdate")
279            lastjob.JobHostName = fields.get("hostname")
280            lastjob.JobSizeBytes = fields.get("jobsizebytes")
281            lastjob.JobMD5Sum = fields.get("md5sum")
282            lastjob.JobPages = fields.get("pages")
283            lastjob.JobBillingCode = self.databaseToUserCharset(fields.get("billingcode"))
284            lastjob.PrecomputedJobSize = fields.get("precomputedjobsize")
285            lastjob.PrecomputedJobPrice = fields.get("precomputedjobprice")
286            if lastjob.JobTitle == lastjob.JobFileName == lastjob.JobOptions == "hidden" :
287                (lastjob.JobTitle, lastjob.JobFileName, lastjob.JobOptions) = (_("Hidden because of privacy concerns"),) * 3
288            lastjob.Exists = 1
289        return lastjob
290           
291    def getGroupMembersFromBackend(self, group) :       
292        """Returns the group's members list."""
293        groupmembers = []
294        result = self.doSearch("SELECT * FROM groupsmembers JOIN users ON groupsmembers.userid=users.id WHERE groupid=%s" % self.doQuote(group.ident))
295        if result :
296            for record in result :
297                user = StorageUser(self, record.get("username"))
298                user.ident = record.get("userid")
299                user.LimitBy = record.get("limitby") or "quota"
300                user.AccountBalance = record.get("balance")
301                user.LifeTimePaid = record.get("lifetimepaid")
302                user.Email = record.get("email")
303                user.OverCharge = record.get("overcharge")
304                user.Exists = 1
305                groupmembers.append(user)
306                self.cacheEntry("USERS", user.Name, user)
307        return groupmembers       
308       
309    def getUserGroupsFromBackend(self, user) :       
310        """Returns the user's groups list."""
311        groups = []
312        result = self.doSearch("SELECT groupname FROM groupsmembers JOIN groups ON groupsmembers.groupid=groups.id WHERE userid=%s" % self.doQuote(user.ident))
313        if result :
314            for record in result :
315                groups.append(self.getGroup(record.get("groupname")))
316        return groups       
317       
318    def getParentPrintersFromBackend(self, printer) :   
319        """Get all the printer groups this printer is a member of."""
320        pgroups = []
321        result = self.doSearch("SELECT groupid,printername FROM printergroupsmembers JOIN printers ON groupid=id WHERE printerid=%s" % self.doQuote(printer.ident))
322        if result :
323            for record in result :
324                if record["groupid"] != printer.ident : # in case of integrity violation
325                    parentprinter = self.getPrinter(record.get("printername"))
326                    if parentprinter.Exists :
327                        pgroups.append(parentprinter)
328        return pgroups
329       
330    def getMatchingPrinters(self, printerpattern) :
331        """Returns the list of all printers for which name matches a certain pattern."""
332        printers = []
333        # We 'could' do a SELECT printername FROM printers WHERE printername LIKE ...
334        # but we don't because other storages semantics may be different, so every
335        # storage should use fnmatch to match patterns and be storage agnostic
336        result = self.doSearch("SELECT * FROM printers")
337        if result :
338            for record in result :
339                if self.tool.matchString(record["printername"], printerpattern.split(",")) :
340                    printer = StoragePrinter(self, record["printername"])
341                    printer.ident = record.get("id")
342                    printer.PricePerJob = record.get("priceperjob") or 0.0
343                    printer.PricePerPage = record.get("priceperpage") or 0.0
344                    printer.Description = self.databaseToUserCharset(record.get("description") or "") 
345                    printer.MaxJobSize = record.get("maxjobsize") or 0
346                    printer.PassThrough = record.get("passthrough") or 0
347                    if printer.PassThrough in (1, "1", "t", "true", "TRUE", "True") :
348                        printer.PassThrough = 1
349                    else :
350                        printer.PassThrough = 0
351                    printer.Exists = 1
352                    printers.append(printer)
353                    self.cacheEntry("PRINTERS", printer.Name, printer)
354        return printers       
355       
356    def getMatchingBillingCodes(self, billingcodepattern) :
357        """Returns the list of all billing codes for which the label matches a certain pattern."""
358        codes = []
359        result = self.doSearch("SELECT * FROM billingcodes")
360        if result :
361            for record in result :
362                bcode = self.databaseToUserCharset(record["billingcode"])
363                if self.tool.matchString(bcode, billingcodepattern.split(",")) :
364                    code = StorageBillingCode(self, bcode)
365                    code.ident = record.get("id")
366                    code.Balance = record.get("balance") or 0.0
367                    code.PageCounter = record.get("pagecounter") or 0
368                    code.Description = self.databaseToUserCharset(record.get("description") or "") 
369                    code.Exists = 1
370                    codes.append(code)
371                    self.cacheEntry("BILLINGCODES", code.BillingCode, code)
372        return codes       
373       
374    def getPrinterUsersAndQuotas(self, printer, names=["*"]) :       
375        """Returns the list of users who uses a given printer, along with their quotas."""
376        usersandquotas = []
377        result = self.doSearch("SELECT users.id as uid,username,balance,lifetimepaid,limitby,email,overcharge,userpquota.id,lifepagecounter,pagecounter,softlimit,hardlimit,datelimit,warncount FROM users JOIN userpquota ON users.id=userpquota.userid AND printerid=%s ORDER BY username ASC" % self.doQuote(printer.ident))
378        if result :
379            for record in result :
380                if self.tool.matchString(record.get("username"), names) :
381                    user = StorageUser(self, record.get("username"))
382                    user.ident = record.get("uid")
383                    user.LimitBy = record.get("limitby") or "quota"
384                    user.AccountBalance = record.get("balance")
385                    user.LifeTimePaid = record.get("lifetimepaid")
386                    user.Email = record.get("email") 
387                    user.OverCharge = record.get("overcharge")
388                    user.Exists = 1
389                    userpquota = StorageUserPQuota(self, user, printer)
390                    userpquota.ident = record.get("id")
391                    userpquota.PageCounter = record.get("pagecounter")
392                    userpquota.LifePageCounter = record.get("lifepagecounter")
393                    userpquota.SoftLimit = record.get("softlimit")
394                    userpquota.HardLimit = record.get("hardlimit")
395                    userpquota.DateLimit = record.get("datelimit")
396                    userpquota.WarnCount = record.get("warncount")
397                    userpquota.Exists = 1
398                    usersandquotas.append((user, userpquota))
399                    self.cacheEntry("USERS", user.Name, user)
400                    self.cacheEntry("USERPQUOTAS", "%s@%s" % (user.Name, printer.Name), userpquota)
401        return usersandquotas
402               
403    def getPrinterGroupsAndQuotas(self, printer, names=["*"]) :       
404        """Returns the list of groups which uses a given printer, along with their quotas."""
405        groupsandquotas = []
406        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))
407        if result :
408            for record in result :
409                if self.tool.matchString(record.get("groupname"), names) :
410                    group = self.getGroup(record.get("groupname"))
411                    grouppquota = self.getGroupPQuota(group, printer)
412                    groupsandquotas.append((group, grouppquota))
413        return groupsandquotas
414       
415    def addPrinter(self, printername) :       
416        """Adds a printer to the quota storage, returns it."""
417        self.doModify("INSERT INTO printers (printername) VALUES (%s)" % self.doQuote(printername))
418        return self.getPrinter(printername)
419       
420    def addBillingCode(self, label) :       
421        """Adds a billing code to the quota storage, returns it."""
422        self.doModify("INSERT INTO billingcodes (billingcode) VALUES (%s)" % self.doQuote(self.userCharsetToDatabase(label)))
423        return self.getBillingCode(label)
424       
425    def addUser(self, user) :       
426        """Adds a user to the quota storage, returns it."""
427        self.doModify("INSERT INTO users (username, limitby, balance, lifetimepaid, email, overcharge) VALUES (%s, %s, %s, %s, %s, %s)" % (self.doQuote(user.Name), self.doQuote(user.LimitBy or 'quota'), self.doQuote(user.AccountBalance or 0.0), self.doQuote(user.LifeTimePaid or 0.0), self.doQuote(user.Email), self.doQuote(user.OverCharge)))
428        return self.getUser(user.Name)
429       
430    def addGroup(self, group) :       
431        """Adds a group to the quota storage, returns it."""
432        self.doModify("INSERT INTO groups (groupname, limitby) VALUES (%s, %s)" % (self.doQuote(group.Name), self.doQuote(group.LimitBy or "quota")))
433        return self.getGroup(group.Name)
434
435    def addUserToGroup(self, user, group) :   
436        """Adds an user to a group."""
437        result = self.doSearch("SELECT COUNT(*) AS mexists FROM groupsmembers WHERE groupid=%s AND userid=%s" % (self.doQuote(group.ident), self.doQuote(user.ident)))
438        try :
439            mexists = int(result[0].get("mexists"))
440        except (IndexError, TypeError) :   
441            mexists = 0
442        if not mexists :   
443            self.doModify("INSERT INTO groupsmembers (groupid, userid) VALUES (%s, %s)" % (self.doQuote(group.ident), self.doQuote(user.ident)))
444           
445    def addUserPQuota(self, user, printer) :
446        """Initializes a user print quota on a printer."""
447        self.doModify("INSERT INTO userpquota (userid, printerid) VALUES (%s, %s)" % (self.doQuote(user.ident), self.doQuote(printer.ident)))
448        return self.getUserPQuota(user, printer)
449       
450    def addGroupPQuota(self, group, printer) :
451        """Initializes a group print quota on a printer."""
452        self.doModify("INSERT INTO grouppquota (groupid, printerid) VALUES (%s, %s)" % (self.doQuote(group.ident), self.doQuote(printer.ident)))
453        return self.getGroupPQuota(group, printer)
454       
455    def writePrinterPrices(self, printer) :   
456        """Write the printer's prices back into the storage."""
457        self.doModify("UPDATE printers SET priceperpage=%s, priceperjob=%s WHERE id=%s" % (self.doQuote(printer.PricePerPage), self.doQuote(printer.PricePerJob), self.doQuote(printer.ident)))
458       
459    def writePrinterDescription(self, printer) :   
460        """Write the printer's description back into the storage."""
461        description = self.userCharsetToDatabase(printer.Description)
462        self.doModify("UPDATE printers SET description=%s WHERE id=%s" % (self.doQuote(description), self.doQuote(printer.ident)))
463       
464    def setPrinterMaxJobSize(self, printer, maxjobsize) :     
465        """Write the printer's maxjobsize attribute."""
466        self.doModify("UPDATE printers SET maxjobsize=%s WHERE id=%s" % (self.doQuote(maxjobsize), self.doQuote(printer.ident)))
467       
468    def setPrinterPassThroughMode(self, printer, passthrough) :
469        """Write the printer's passthrough attribute."""
470        self.doModify("UPDATE printers SET passthrough=%s WHERE id=%s" % (self.doQuote((passthrough and "t") or "f"), self.doQuote(printer.ident)))
471       
472    def writeUserOverCharge(self, user, factor) :
473        """Sets the user's overcharging coefficient."""
474        self.doModify("UPDATE users SET overcharge=%s WHERE id=%s" % (self.doQuote(factor), self.doQuote(user.ident)))
475       
476    def writeUserLimitBy(self, user, limitby) :   
477        """Sets the user's limiting factor."""
478        self.doModify("UPDATE users SET limitby=%s WHERE id=%s" % (self.doQuote(limitby), self.doQuote(user.ident)))
479       
480    def writeGroupLimitBy(self, group, limitby) :   
481        """Sets the group's limiting factor."""
482        self.doModify("UPDATE groups SET limitby=%s WHERE id=%s" % (self.doQuote(limitby), self.doQuote(group.ident)))
483       
484    def writeUserPQuotaDateLimit(self, userpquota, datelimit) :   
485        """Sets the date limit permanently for a user print quota."""
486        self.doModify("UPDATE userpquota SET datelimit=%s WHERE id=%s" % (self.doQuote(datelimit), self.doQuote(userpquota.ident)))
487           
488    def writeGroupPQuotaDateLimit(self, grouppquota, datelimit) :   
489        """Sets the date limit permanently for a group print quota."""
490        self.doModify("UPDATE grouppquota SET datelimit=%s WHERE id=%s" % (self.doQuote(datelimit), self.doQuote(grouppquota.ident)))
491       
492    def increaseUserPQuotaPagesCounters(self, userpquota, nbpages) :   
493        """Increase page counters for a user print quota."""
494        self.doModify("UPDATE userpquota SET pagecounter=pagecounter + %s,lifepagecounter=lifepagecounter + %s WHERE id=%s" % (self.doQuote(nbpages), self.doQuote(nbpages), self.doQuote(userpquota.ident)))
495       
496    def writeUserPQuotaPagesCounters(self, userpquota, newpagecounter, newlifepagecounter) :   
497        """Sets the new page counters permanently for a user print quota."""
498        self.doModify("UPDATE userpquota SET pagecounter=%s, lifepagecounter=%s, warncount=0, datelimit=NULL WHERE id=%s" % (self.doQuote(newpagecounter), self.doQuote(newlifepagecounter), self.doQuote(userpquota.ident)))
499       
500    def writeBillingCodeDescription(self, code) :
501        """Sets the new description for a billing code."""
502        self.doModify("UPDATE billingcodes SET description=%s WHERE id=%s" % (self.doQuote(self.userCharsetToDatabase(code.Description or "")), self.doQuote(code.ident)))
503       
504    def setBillingCodeValues(self, code, newpagecounter, newbalance) :   
505        """Sets the new page counter and balance for a billing code."""
506        self.doModify("UPDATE billingcodes SET balance=%s, pagecounter=%s WHERE id=%s" % (self.doQuote(newbalance), self.doQuote(newpagecounter), self.doQuote(code.ident)))
507       
508    def consumeBillingCode(self, code, pagecounter, balance) :
509        """Consumes from a billing code."""
510        self.doModify("UPDATE billingcodes SET balance=balance + %s, pagecounter=pagecounter + %s WHERE id=%s" % (self.doQuote(balance), self.doQuote(pagecounter), self.doQuote(code.ident)))
511       
512    def decreaseUserAccountBalance(self, user, amount) :   
513        """Decreases user's account balance from an amount."""
514        self.doModify("UPDATE users SET balance=balance - %s WHERE id=%s" % (self.doQuote(amount), self.doQuote(user.ident)))
515       
516    def writeUserAccountBalance(self, user, newbalance, newlifetimepaid=None) :   
517        """Sets the new account balance and eventually new lifetime paid."""
518        if newlifetimepaid is not None :
519            self.doModify("UPDATE users SET balance=%s, lifetimepaid=%s WHERE id=%s" % (self.doQuote(newbalance), self.doQuote(newlifetimepaid), self.doQuote(user.ident)))
520        else :   
521            self.doModify("UPDATE users SET balance=%s WHERE id=%s" % (self.doQuote(newbalance), self.doQuote(user.ident)))
522           
523    def writeNewPayment(self, user, amount, comment="") :
524        """Adds a new payment to the payments history."""
525        self.doModify("INSERT INTO payments (userid, amount, description) VALUES (%s, %s, %s)" % (self.doQuote(user.ident), self.doQuote(amount), self.doQuote(comment)))
526       
527    def writeLastJobSize(self, lastjob, jobsize, jobprice) :       
528        """Sets the last job's size permanently."""
529        self.doModify("UPDATE jobhistory SET jobsize=%s, jobprice=%s WHERE id=%s" % (self.doQuote(jobsize), self.doQuote(jobprice), self.doQuote(lastjob.ident)))
530       
531    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) :
532        """Adds a job in a printer's history."""
533        if self.privacy :   
534            # For legal reasons, we want to hide the title, filename and options
535            title = filename = options = "hidden"
536        filename = self.userCharsetToDatabase(filename)
537        title = self.userCharsetToDatabase(title)
538        options = self.userCharsetToDatabase(options)
539        jobbilling = self.userCharsetToDatabase(jobbilling)
540        if (not self.disablehistory) or (not printer.LastJob.Exists) :
541            if jobsize is not None :
542                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)))
543            else :   
544                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)))
545        else :       
546            # here we explicitly want to reset jobsize to NULL if needed
547            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)))
548           
549    def writeUserPQuotaLimits(self, userpquota, softlimit, hardlimit) :
550        """Sets soft and hard limits for a user quota."""
551        self.doModify("UPDATE userpquota SET softlimit=%s, hardlimit=%s, warncount=0, datelimit=NULL WHERE id=%s" % (self.doQuote(softlimit), self.doQuote(hardlimit), self.doQuote(userpquota.ident)))
552       
553    def writeUserPQuotaWarnCount(self, userpquota, warncount) :
554        """Sets the warn counter value for a user quota."""
555        self.doModify("UPDATE userpquota SET warncount=%s WHERE id=%s" % (self.doQuote(warncount), self.doQuote(userpquota.ident)))
556       
557    def increaseUserPQuotaWarnCount(self, userpquota) :
558        """Increases the warn counter value for a user quota."""
559        self.doModify("UPDATE userpquota SET warncount=warncount+1 WHERE id=%s" % self.doQuote(userpquota.ident))
560       
561    def writeGroupPQuotaLimits(self, grouppquota, softlimit, hardlimit) :
562        """Sets soft and hard limits for a group quota on a specific printer."""
563        self.doModify("UPDATE grouppquota SET softlimit=%s, hardlimit=%s, datelimit=NULL WHERE id=%s" % (self.doQuote(softlimit), self.doQuote(hardlimit), self.doQuote(grouppquota.ident)))
564
565    def writePrinterToGroup(self, pgroup, printer) :
566        """Puts a printer into a printer group."""
567        children = []
568        result = self.doSearch("SELECT printerid FROM printergroupsmembers WHERE groupid=%s" % self.doQuote(pgroup.ident))
569        if result :
570            for record in result :
571                children.append(record.get("printerid")) # TODO : put this into the database integrity rules
572        if printer.ident not in children :       
573            self.doModify("INSERT INTO printergroupsmembers (groupid, printerid) VALUES (%s, %s)" % (self.doQuote(pgroup.ident), self.doQuote(printer.ident)))
574       
575    def removePrinterFromGroup(self, pgroup, printer) :
576        """Removes a printer from a printer group."""
577        self.doModify("DELETE FROM printergroupsmembers WHERE groupid=%s AND printerid=%s" % (self.doQuote(pgroup.ident), self.doQuote(printer.ident)))
578       
579    def retrieveHistory(self, user=None, printer=None, hostname=None, billingcode=None, limit=100, start=None, end=None) :
580        """Retrieves all print jobs for user on printer (or all) between start and end date, limited to first 100 results."""
581        query = "SELECT jobhistory.*,username,printername FROM jobhistory,users,printers WHERE users.id=userid AND printers.id=printerid"
582        where = []
583        if user is not None : # user.ident is None anyway if user doesn't exist
584            where.append("userid=%s" % self.doQuote(user.ident))
585        if printer is not None : # printer.ident is None anyway if printer doesn't exist
586            where.append("printerid=%s" % self.doQuote(printer.ident))
587        if hostname is not None :   
588            where.append("hostname=%s" % self.doQuote(hostname))
589        if billingcode is not None :   
590            where.append("billingcode=%s" % self.doQuote(self.userCharsetToDatabase(billingcode)))
591        if start is not None :   
592            where.append("jobdate>=%s" % self.doQuote(start))
593        if end is not None :   
594            where.append("jobdate<=%s" % self.doQuote(end))
595        if where :   
596            query += " AND %s" % " AND ".join(where)
597        query += " ORDER BY id DESC"
598        if limit :
599            query += " LIMIT %s" % self.doQuote(int(limit))
600        jobs = []   
601        result = self.doSearch(query)   
602        if result :
603            for fields in result :
604                job = StorageJob(self)
605                job.ident = fields.get("id")
606                job.JobId = fields.get("jobid")
607                job.PrinterPageCounter = fields.get("pagecounter")
608                job.JobSize = fields.get("jobsize")
609                job.JobPrice = fields.get("jobprice")
610                job.JobAction = fields.get("action")
611                job.JobFileName = self.databaseToUserCharset(fields.get("filename") or "") 
612                job.JobTitle = self.databaseToUserCharset(fields.get("title") or "") 
613                job.JobCopies = fields.get("copies")
614                job.JobOptions = self.databaseToUserCharset(fields.get("options") or "") 
615                job.JobDate = fields.get("jobdate")
616                job.JobHostName = fields.get("hostname")
617                job.JobSizeBytes = fields.get("jobsizebytes")
618                job.JobMD5Sum = fields.get("md5sum")
619                job.JobPages = fields.get("pages")
620                job.JobBillingCode = self.databaseToUserCharset(fields.get("billingcode") or "")
621                job.PrecomputedJobSize = fields.get("precomputedjobsize")
622                job.PrecomputedJobPrice = fields.get("precomputedjobprice")
623                job.UserName = fields.get("username")
624                job.PrinterName = fields.get("printername")
625                if job.JobTitle == job.JobFileName == job.JobOptions == "hidden" :
626                    (job.JobTitle, job.JobFileName, job.JobOptions) = (_("Hidden because of privacy concerns"),) * 3
627                job.Exists = 1
628                jobs.append(job)
629        return jobs
630       
631    def deleteUser(self, user) :   
632        """Completely deletes an user from the Quota Storage."""
633        # TODO : What should we do if we delete the last person who used a given printer ?
634        # TODO : we can't reassign the last job to the previous one, because next user would be
635        # TODO : incorrectly charged (overcharged).
636        for q in [ 
637                    "DELETE FROM payments WHERE userid=%s" % self.doQuote(user.ident),
638                    "DELETE FROM groupsmembers WHERE userid=%s" % self.doQuote(user.ident),
639                    "DELETE FROM jobhistory WHERE userid=%s" % self.doQuote(user.ident),
640                    "DELETE FROM userpquota WHERE userid=%s" % self.doQuote(user.ident),
641                    "DELETE FROM users WHERE id=%s" % self.doQuote(user.ident),
642                  ] :
643            self.doModify(q)
644       
645    def deleteGroup(self, group) :   
646        """Completely deletes a group from the Quota Storage."""
647        for q in [
648                   "DELETE FROM groupsmembers WHERE groupid=%s" % self.doQuote(group.ident),
649                   "DELETE FROM grouppquota WHERE groupid=%s" % self.doQuote(group.ident),
650                   "DELETE FROM groups WHERE id=%s" % self.doQuote(group.ident),
651                 ] : 
652            self.doModify(q)
653           
654    def deletePrinter(self, printer) :   
655        """Completely deletes a printer from the Quota Storage."""
656        for q in [ 
657                    "DELETE FROM printergroupsmembers WHERE groupid=%s OR printerid=%s" % (self.doQuote(printer.ident), self.doQuote(printer.ident)),
658                    "DELETE FROM jobhistory WHERE printerid=%s" % self.doQuote(printer.ident),
659                    "DELETE FROM grouppquota WHERE printerid=%s" % self.doQuote(printer.ident),
660                    "DELETE FROM userpquota WHERE printerid=%s" % self.doQuote(printer.ident),
661                    "DELETE FROM printers WHERE id=%s" % self.doQuote(printer.ident),
662                  ] :
663            self.doModify(q)
664           
665    def deleteBillingCode(self, code) :   
666        """Completely deletes a billing code from the Quota Storage."""
667        for q in [
668                   "DELETE FROM billingcodes WHERE id=%s" % self.doQuote(code.ident),
669                 ] : 
670            self.doModify(q)
671       
Note: See TracBrowser for help on using the browser.