Context Navigation

sql.py @ 2654

Revision 2654, 43.5 kB (checked in by jerome, 18 years ago)
Now non-ascii characters are accepted as part of usernames, groupnames or printernames. Contrarily to with LDAP, this works fine with relationnal databases.
Property svn:eol-style set to `native` Property svn:keywords set to `Author Date Id Revision`

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

Note: See TracBrowser for help on using the browser.

Context Navigation

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

Download in other formats: