Changeset 3165 for pykota/trunk

Timestamp:

04/16/07 18:52:23 (18 years ago)

Author:

jerome

Message:

Added --orderby command line switch to dumpykota.
Doesn't work yet with the LDAP backend, since sorting will have
to be done by PyKota's code instead of relying on the database
backend itself to do the dirty work.

Location:

pykota/trunk

Files:

• bin/dumpykota (modified) (3 diffs)
• pykota/dumper.py (modified) (2 diffs)
• pykota/storages/ldapstorage.py (modified) (10 diffs)
• pykota/storages/sql.py (modified) (4 diffs)
• TODO (modified) (1 diff)

pykota/trunk/bin/dumpykota

@@ -78 +78 @@
                        WARNING : existing files are truncated !
 
+  -O | --orderby exp   Change the ordering or result. 'exp' is a comma
+                       separated list of ordering statements, for example
+                       '--orderby +username,-printername'. Not all expression
+                       values are meaningful, so using this command line 
+                       switch is not recommanded if you don't know the 
+                       exact layout of PyKota's database schema.
+                       
   -s | --sum           Summarize the selected datas.
                            ONLY AVAILABLE WITH --data history or payments
@@ -150 +157 @@
                      "output" : "-", \
                    }
-        short_options = "vhd:f:o:s"
-        long_options = ["help", "version", "data=", "format=", "output=", "sum"]
+        short_options = "vhd:f:o:sO:"
+        long_options = ["help", "version", "data=", "format=", "output=", "sum", "orderby="]
         
         # Initializes the command line tool
@@ -167 +174 @@
         options["output"] = options["o"] or options["output"] or defaults["output"]
         options["sum"] = options["s"] or options["sum"]
+        options["orderby"] = options["O"] or options["orderby"]
         
         if options["help"] :

pykota/trunk/pykota/dumper.py

@@ -80 +80 @@
             raise PyKotaCommandLineError, _("Invalid modifier [%s] for --data command line option, see help.") % datatype
                     
+        orderby = options["orderby"]             
+        if orderby :
+            fields = [f.strip() for f in orderby.split(",")]
+            orderby = []
+            for field in fields :
+                if field.isalpha() \
+                   or ((field[0] in ("+", "-")) and field[1:].isalpha()) :
+                    orderby.append(field)
+                else :    
+                    self.printInfo("Skipping invalid ordering statement '%(field)s'" % locals(), "error") 
+        else :
+            orderby = []
+            
         extractonly = {}
         if datatype == "all" :            
@@ -147 +160 @@
             retcode = self.dumpXml(allentries, neededdatatypes)
         else :    
-            entries = getattr(self.storage, "extract%s" % datatype.title())(extractonly)
+            entries = getattr(self.storage, "extract%s" % datatype.title())(extractonly, orderby)
             if entries :
                 nbentries = len(entries)

pykota/trunk/pykota/storages/ldapstorage.py

@@ -1574 +1574 @@
         self.doDelete(code.ident)
         
-    def extractPrinters(self, extractonly={}) :
+    def extractPrinters(self, extractonly={}, ordering=[]) :
         """Extracts all printer records."""
         pname = extractonly.get("printername")
@@ -1588 +1588 @@
             return result 
         
-    def extractUsers(self, extractonly={}) :
+    def extractUsers(self, extractonly={}, ordering=[]) :
         """Extracts all user records."""
         uname = extractonly.get("username")
@@ -1598 +1598 @@
             return result 
         
-    def extractBillingcodes(self, extractonly={}) :
+    def extractBillingcodes(self, extractonly={}, ordering=[]) :
         """Extracts all billing codes records."""
         billingcode = extractonly.get("billingcode")
@@ -1608 +1608 @@
             return result 
         
-    def extractGroups(self, extractonly={}) :
+    def extractGroups(self, extractonly={}, ordering=[]) :
         """Extracts all group records."""
         gname = extractonly.get("groupname")
@@ -1618 +1618 @@
             return result 
         
-    def extractPayments(self, extractonly={}) :
+    def extractPayments(self, extractonly={}, ordering=[]) :
         """Extracts all payment records."""
         startdate = extractonly.get("start")
@@ -1636 +1636 @@
             return result        
         
-    def extractUpquotas(self, extractonly={}) :
+    def extractUpquotas(self, extractonly={}, ordering=[]) :
         """Extracts all userpquota records."""
         pname = extractonly.get("printername")
@@ -1648 +1648 @@
             return result
         
-    def extractGpquotas(self, extractonly={}) :
+    def extractGpquotas(self, extractonly={}, ordering=[]) :
         """Extracts all grouppquota records."""
         pname = extractonly.get("printername")
@@ -1660 +1660 @@
             return result
         
-    def extractUmembers(self, extractonly={}) :
+    def extractUmembers(self, extractonly={}, ordering=[]) :
         """Extracts all user groups members."""
         gname = extractonly.get("groupname")
@@ -1673 +1673 @@
             return result        
                 
-    def extractPmembers(self, extractonly={}) :
+    def extractPmembers(self, extractonly={}, ordering=[]) :
         """Extracts all printer groups members."""
         pname = extractonly.get("printername")
@@ -1686 +1686 @@
             return result        
         
-    def extractHistory(self, extractonly={}) :
+    def extractHistory(self, extractonly={}, ordering=[]) :
         """Extracts all jobhistory records."""
         uname = extractonly.get("username")

pykota/trunk/pykota/storages/sql.py

@@ -153 +153 @@
         return ""        
         
-    def extractPrinters(self, extractonly={}) :
+    def createOrderBy(self, default, ordering) :    
+        """Creates a suitable ORDER BY statement based on a list of fieldnames prefixed with '+' (ASC) or '-' (DESC)."""
+        statements = []
+        if not ordering :
+            ordering = default
+        for field in ordering :    
+            if field.startswith("-") :    
+                statements.append("%s DESC" % field[1:])
+            elif field.startswith("+") :
+                statements.append("%s ASC" % field[1:])
+            else :    
+                statements.append("%s ASC" % field)
+        return ", ".join(statements)    
+        
+    def extractPrinters(self, extractonly={}, ordering=[]) :
         """Extracts all printer records."""
         thefilter = self.createFilter(extractonly)
         if thefilter :
             thefilter = "WHERE %s" % thefilter
-        result = self.doRawSearch("SELECT * FROM printers %s ORDER BY id ASC" % thefilter)
+        orderby = self.createOrderBy(["+id"], ordering)
+        result = self.doRawSearch("SELECT * FROM printers %(thefilter)s ORDER BY %(orderby)s" % locals())
         return self.prepareRawResult(result)
         
-    def extractUsers(self, extractonly={}) :
+    def extractUsers(self, extractonly={}, ordering=[]) :
         """Extracts all user records."""
         thefilter = self.createFilter(extractonly)
         if thefilter :
             thefilter = "WHERE %s" % thefilter
-        result = self.doRawSearch("SELECT * FROM users %s ORDER BY id ASC" % thefilter)
+        orderby = self.createOrderBy(["+id"], ordering)
+        result = self.doRawSearch("SELECT * FROM users %(thefilter)s ORDER BY %(orderby)s" % locals())
         return self.prepareRawResult(result)
         
-    def extractBillingcodes(self, extractonly={}) :
+    def extractBillingcodes(self, extractonly={}, ordering=[]) :
         """Extracts all billing codes records."""
         thefilter = self.createFilter(extractonly)
         if thefilter :
             thefilter = "WHERE %s" % thefilter
-        result = self.doRawSearch("SELECT * FROM billingcodes %s ORDER BY id ASC" % thefilter)
+        orderby = self.createOrderBy(["+id"], ordering)
+        result = self.doRawSearch("SELECT * FROM billingcodes %(thefilter)s ORDER BY %(orderby)s" % locals())
         return self.prepareRawResult(result)
         
-    def extractGroups(self, extractonly={}) :
+    def extractGroups(self, extractonly={}, ordering=[]) :
         """Extracts all group records."""
         thefilter = self.createFilter(extractonly)
         if thefilter :
             thefilter = "WHERE %s" % thefilter
-        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)
+        orderby = self.createOrderBy(["+groups.id"], ordering)
+        result = self.doRawSearch("SELECT groups.*,COALESCE(SUM(balance), 0) AS balance, COALESCE(SUM(lifetimepaid), 0) as lifetimepaid FROM groups LEFT OUTER JOIN users ON users.id IN (SELECT userid FROM groupsmembers WHERE groupid=groups.id) %(thefilter)s GROUP BY groups.id,groups.groupname,groups.limitby,groups.description ORDER BY %(orderby)s" % locals())
         return self.prepareRawResult(result)
         
-    def extractPayments(self, extractonly={}) :
+    def extractPayments(self, extractonly={}, ordering=[]) :
         """Extracts all payment records."""
         startdate = extractonly.get("start")
@@ -202 +220 @@
         if enddate : 
             thefilter = "%s AND date<=%s" % (thefilter, self.doQuote(enddate))
-        result = self.doRawSearch("SELECT username,payments.* FROM users,payments WHERE users.id=payments.userid %s ORDER BY payments.id ASC" % thefilter)
+        orderby = self.createOrderBy(["+payments.id"], ordering)
+        result = self.doRawSearch("SELECT username,payments.* FROM users,payments WHERE users.id=payments.userid %(thefilter)s ORDER BY %(orderby)s" % locals())
         return self.prepareRawResult(result)
         
-    def extractUpquotas(self, extractonly={}) :
+    def extractUpquotas(self, extractonly={}, ordering=[]) :
         """Extracts all userpquota records."""
         thefilter = self.createFilter(extractonly)
         if thefilter :
             thefilter = "AND %s" % thefilter
-        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)
+        orderby = self.createOrderBy(["+userpquota.id"], ordering)
+        result = self.doRawSearch("SELECT users.username,printers.printername,userpquota.* FROM users,printers,userpquota WHERE users.id=userpquota.userid AND printers.id=userpquota.printerid %(thefilter)s ORDER BY %(orderby)s" % locals())
         return self.prepareRawResult(result)
         
-    def extractGpquotas(self, extractonly={}) :
+    def extractGpquotas(self, extractonly={}, ordering=[]) :
         """Extracts all grouppquota records."""
         thefilter = self.createFilter(extractonly)
         if thefilter :
             thefilter = "AND %s" % thefilter
-        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 ASC" % thefilter)
+        orderby = self.createOrderBy(["+grouppquota.id"], ordering)
+        result = self.doRawSearch("SELECT groups.groupname,printers.printername,grouppquota.*,coalesce(sum(pagecounter), 0) AS pagecounter,coalesce(sum(lifepagecounter), 0) AS lifepagecounter FROM groups,printers,grouppquota,userpquota WHERE groups.id=grouppquota.groupid AND printers.id=grouppquota.printerid AND userpquota.printerid=grouppquota.printerid AND userpquota.userid IN (SELECT userid FROM groupsmembers WHERE groupsmembers.groupid=grouppquota.groupid) %(thefilter)s GROUP BY grouppquota.id,grouppquota.groupid,grouppquota.printerid,grouppquota.softlimit,grouppquota.hardlimit,grouppquota.datelimit,grouppquota.maxjobsize,groups.groupname,printers.printername ORDER BY %(orderby)s" % locals())
         return self.prepareRawResult(result)
         
-    def extractUmembers(self, extractonly={}) :
+    def extractUmembers(self, extractonly={}, ordering=[]) :
         """Extracts all user groups members."""
         thefilter = self.createFilter(extractonly)
         if thefilter :
             thefilter = "AND %s" % thefilter
-        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 ASC, groupsmembers.userid ASC" % thefilter)
+        orderby = self.createOrderBy(["+groupsmembers.groupid", "+groupsmembers.userid"], ordering)
+        result = self.doRawSearch("SELECT groups.groupname, users.username, groupsmembers.* FROM groups,users,groupsmembers WHERE users.id=groupsmembers.userid AND groups.id=groupsmembers.groupid %(thefilter)s ORDER BY %(orderby)s" % locals())
         return self.prepareRawResult(result)
         
-    def extractPmembers(self, extractonly={}) :
+    def extractPmembers(self, extractonly={}, ordering=[]) :
         """Extracts all printer groups members."""
         for (k, v) in extractonly.items() :
@@ -241 +263 @@
         if thefilter :
             thefilter = "AND %s" % thefilter
-        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 ASC, printergroupsmembers.printerid ASC" % thefilter)
+        orderby = self.createOrderBy(["+printergroupsmembers.groupid", "+printergroupsmembers.printerid"], ordering)
+        result = self.doRawSearch("SELECT p1.printername as pgroupname, p2.printername as printername, printergroupsmembers.* FROM printers p1, printers p2, printergroupsmembers WHERE p1.id=printergroupsmembers.groupid AND p2.id=printergroupsmembers.printerid %(thefilter)s ORDER BY %(orderby)s" % locals())
         return self.prepareRawResult(result)
         
-    def extractHistory(self, extractonly={}) :
+    def extractHistory(self, extractonly={}, ordering=[]) :
         """Extracts all jobhistory records."""
         startdate = extractonly.get("start")
@@ -261 +284 @@
         if enddate : 
             thefilter = "%s AND jobdate<=%s" % (thefilter, self.doQuote(enddate))
-        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)
+        orderby = self.createOrderBy(["+jobhistory.id"], ordering)
+        result = self.doRawSearch("SELECT users.username,printers.printername,jobhistory.* FROM users,printers,jobhistory WHERE users.id=jobhistory.userid AND printers.id=jobhistory.printerid %(thefilter)s ORDER BY %(orderby)s" % locals())
         return self.prepareRawResult(result)
             

pykota/trunk/TODO

@@ -30 +30 @@
           
         - --orderby and --order ASC|DESC for dumpykota (or --asc | --desc)
+          Now works for SQL backends, not with LDAP yet.
         
         - Document the PYKOTA_HOME environment variable.