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

Revision 1806, 34.0 kB (checked in by jalet, 20 years ago)

Now warnpykota only warns users who have already printed, to not confuse
users who have just opened their account.

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