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

Revision 722, 6.8 kB (checked in by jalet, 21 years ago)

added a method to set the limit date

  • Property svn:eol-style set to native
  • Property svn:keywords set to Author Date Id Revision
Line 
1# PyKota
2#
3# PyKota : Print Quotas for CUPS
4#
5# (c) 2003 Jerome Alet <alet@librelogiciel.com>
6# You're welcome to redistribute this software under the
7# terms of the GNU General Public Licence version 2.0
8# or, at your option, any higher version.
9#
10# You can read the complete GNU GPL in the file COPYING
11# which should come along with this software, or visit
12# the Free Software Foundation's WEB site http://www.fsf.org
13#
14# $Id$
15#
16# $Log$
17# Revision 1.10  2003/02/06 15:03:11  jalet
18# added a method to set the limit date
19#
20# Revision 1.9  2003/02/06 14:52:35  jalet
21# Forgotten import
22#
23# Revision 1.8  2003/02/06 14:49:04  jalet
24# edpykota should be ok now
25#
26# Revision 1.7  2003/02/06 14:28:59  jalet
27# edpykota should be ok, minus some typos
28#
29# Revision 1.6  2003/02/06 09:19:02  jalet
30# More robust behavior (hopefully) when the user or printer is not managed
31# correctly by the Quota System : e.g. cupsFilter added in ppd file, but
32# printer and/or user not 'yet?' in storage.
33#
34# Revision 1.5  2003/02/05 23:26:22  jalet
35# Incorrect handling of grace delay
36#
37# Revision 1.4  2003/02/05 23:02:10  jalet
38# Typo
39#
40# Revision 1.3  2003/02/05 23:00:12  jalet
41# Forgotten import
42# Bad datetime conversion
43#
44# Revision 1.2  2003/02/05 22:28:38  jalet
45# More robust storage
46#
47# Revision 1.1  2003/02/05 21:28:17  jalet
48# Initial import into CVS
49#
50#
51#
52
53import fnmatch
54
55class SQLStorage :   
56    def getMatchingPrinters(self, printerpattern) :
57        """Returns the list of all printer names which match a certain pattern."""
58        printerslist = []
59        # We 'could' do a SELECT printername FROM printers WHERE printername LIKE ...
60        # but we don't because other storages semantics may be different, so every
61        # storage should use fnmatch to match patterns and be storage agnostic
62        result = self.doQuery("SELECT printername FROM printers;")
63        result = self.doParseResult(result)
64        if result is not None :
65            for printer in result :
66                if fnmatch.fnmatchcase(printer["printername"], printerpattern) :
67                    printerslist.append(printer["printername"])
68        return printerslist       
69           
70    def getUserId(self, username) :
71        """Returns a userid given a username."""
72        result = self.doQuery("SELECT id FROM users WHERE username=%s;" % self.doQuote(username))
73        try :
74            return self.doParseResult(result)[0]["id"]
75        except TypeError :      # Not found
76            return
77           
78    def getPrinterId(self, printername) :       
79        """Returns a printerid given a printername."""
80        result = self.doQuery("SELECT id FROM printers WHERE printername=%s;" % self.doQuote(printername))
81        try :
82            return self.doParseResult(result)[0]["id"]
83        except TypeError :      # Not found   
84            return
85           
86    def getPrinterPageCounter(self, printername) :
87        """Returns the last page counter value for a printer given its name."""
88        result = self.doQuery("SELECT pagecounter, lastusername FROM printers WHERE printername=%s;" % self.doQuote(printername))
89        try :
90            return self.doParseResult(result)[0]
91        except TypeError :      # Not found
92            return
93       
94    def updatePrinterPageCounter(self, printername, username, pagecount) :
95        """Updates the last page counter information for a printer given its name, last username and pagecount."""
96        return self.doQuery("UPDATE printers SET pagecounter=%s, lastusername=%s WHERE printername=%s;" % (self.doQuote(pagecount), self.doQuote(username), self.doQuote(printername)))
97       
98    def addUserPQuota(self, username, printername) :
99        (userid, printerid) = self.getUPIds(username, printername)
100        if printerid is None :   
101            self.doQuery("INSERT INTO printers (printername) VALUES (%s);" % self.doQuote(printername))
102        if userid is None :   
103            self.doQuery("INSERT INTO users (username) VALUES (%s);" % self.doQuote(username))
104        (userid, printerid) = self.getUPIds(username, printername)
105        if (userid is not None) and (printerid is not None) :
106            return self.doQuery("INSERT INTO userpquota (userid, printerid) VALUES (%s, %s);" % (self.doQuote(userid), self.doQuote(printerid)))
107       
108    def getUPIds(self, username, printername) :   
109        """Returns a tuple (userid, printerid) given a username and a printername."""
110        return (self.getUserId(username), self.getPrinterId(printername))
111       
112    def getUserPQuota(self, username, printername) :
113        """Returns the Print Quota information for a given (username, printername)."""
114        (userid, printerid) = self.getUPIds(username, printername)
115        if (userid is not None) and (printerid is not None) :
116            result = self.doQuery("SELECT pagecounter, softlimit, hardlimit, datelimit FROM userpquota WHERE userid=%s AND printerid=%s;" % (self.doQuote(userid), self.doQuote(printerid)))
117            try :
118                return self.doParseResult(result)[0]
119            except TypeError :      # Not found   
120                pass
121       
122    def setUserPQuota(self, username, printername, softlimit, hardlimit) :
123        """Sets soft and hard limits for a user quota on a specific printer given (username, printername)."""
124        (userid, printerid) = self.getUPIds(username, printername)
125        if (userid is not None) and (printerid is not None) :
126            self.doQuery("UPDATE userpquota SET softlimit=%s, hardlimit=%s, datelimit=NULL WHERE userid=%s AND printerid=%s;" % (self.doQuote(softlimit), self.doQuote(hardlimit), self.doQuote(userid), self.doQuote(printerid)))
127       
128    def setDateLimit(username, printername, datelimit) :
129        """Sets the limit date for a soft limit to become an hard one given (username, printername)."""
130        (userid, printerid) = self.getUPIds(username, printername)
131        if (userid is not None) and (printerid is not None) :
132            self.doQuery("UPDATE userpquota SET datelimit=%s::DATETIME WHERE userid=%s AND printerid=%s;" % (self.doQuote("%04i-%02i-%02i %02i:%02i:%02i" % (datelimit.year, datelimit.month, datelimit.day, datelimit.hour, datelimit.minute, datelimit.second)), self.doQuote(userid), self.doQuote(printerid)))
133       
134    def updateUserPQuota(self, username, printername, pagecount) :
135        """Updates the used user Quota information given (username, printername) and a job size in pages."""
136        (userid, printerid) = self.getUPIds(username, printername)
137        if (userid is not None) and (printerid is not None) :
138            self.doQuery("UPDATE userpquota SET pagecounter=pagecounter+(%s) WHERE userid=%s AND printerid=%s;" % (self.doQuote(pagecount), self.doQuote(userid), self.doQuote(printerid)))
139       
140    def buyUserPQuota(self, username, printername, pagebought) :
141        """Buys pages for a given (username, printername)."""
142        self.updateUserPQuota(username, printername, -pagebought)
143       
Note: See TracBrowser for help on using the browser.