Changeset 1991

Show
Ignore:
Timestamp:
12/21/04 16:50:00 (20 years ago)
Author:
jalet
Message:

The dumpykota command now supports extended filtering capabilities with
the PostgreSQL backend. LDAP doesn't yet support such possibilities.

Location:
pykota/trunk
Files:
4 modified

Legend:

Unmodified
Added
Removed
  • pykota/trunk/bin/dumpykota

    r1990 r1991  
    2424# 
    2525# $Log$ 
     26# Revision 1.17  2004/12/21 15:50:00  jalet 
     27# The dumpykota command now supports extended filtering capabilities with 
     28# the PostgreSQL backend. LDAP doesn't yet support such possibilities. 
     29# 
    2630# Revision 1.16  2004/12/21 14:45:31  jalet 
    2731# Prepared dumpykota to accept the new --filter command line option. Some 
     
    106110command line usage : 
    107111 
    108   dumpykota [options]  
     112  dumpykota [options] [filterexpr] 
    109113 
    110114options : 
     
    138142                         - xml : dump data as XML 
    139143                          
    140   -F - | --filter exp  Uses the filter to extract only parts of the                           
    141                        datas. Allowed filters are of the form : 
    142                         
    143                          key=value 
    144                           
    145                        Allowed keys for now are :   
    146                         
    147                          username       User's name 
    148                          groupname      Users group's name 
    149                          printername    Printer's name 
    150                          pgroupname     Printers group's name 
    151                           
    152                        Contrary to other PyKota management tools, wildcard 
    153                        characters are not expanded, so you can't use them. 
    154                           
    155                        NB : not all keys are allowed for each data type, 
    156                        so the result may be empty if you use a key not 
    157                        available for a particular data type. 
    158                           
    159144  -o | --output fname  All datas will be dumped to the file instead of 
    160145                       to the standard output. The special '-' filename 
    161146                       is the default value and means stdout. 
    162147                       WARNING : existing files are truncated ! 
     148                        
     149  Use the filter expressions to extract only parts of the  
     150  datas. Allowed filters are of the form : 
     151                 
     152         key=value 
     153                          
     154  Allowed keys for now are :   
     155                        
     156         username       User's name 
     157         groupname      Users group's name 
     158         printername    Printer's name 
     159         pgroupname     Printers group's name 
     160          
     161  Contrary to other PyKota management tools, wildcard characters are not  
     162  expanded, so you can't use them. 
     163   
     164  NB : not all keys are allowed for each data type, so the result may be  
     165  empty if you use a key not available for a particular data type. 
    163166   
    164167Examples : 
     
    173176  Dumps all users datas to the users.xml file. 
    174177   
    175   $ dumpykota --data history --filter printername=HP2100 
    176    
    177   Dumps the job history for printer HP2100 only. 
     178  $ dumpykota --data history printername=HP2100 username=jerome 
     179   
     180  Dumps the job history for user jerome on printer HP2100 only. 
    178181   
    179182This program is free software; you can redistribute it and/or modify 
     
    201204             
    202205        extractonly = {} 
    203         filterexp = options["filter"]     
    204         if filterexp : 
    205             try : 
    206                 (filterkey, filtervalue) = [part.strip() for part in filterexp.split("=")] 
    207                 if filterkey not in [ "username", 
    208                                       "groupname", 
    209                                       "printername", 
    210                                       "pgroupname", 
    211                                     ] : 
    212                     raise ValueError                 
    213             except ValueError :     
    214                 raise PyKotaToolError, _("Invalid value [%s] for --filter command line option, see help.") % filterexp 
    215             else :     
    216                 extractonly = { filterkey : filtervalue } 
     206        for filterexp in arguments : 
     207            if filterexp.strip() : 
     208                try : 
     209                    (filterkey, filtervalue) = [part.strip() for part in filterexp.split("=")] 
     210                    if filterkey not in [ "username", 
     211                                          "groupname", 
     212                                          "printername", 
     213                                          "pgroupname", 
     214                                        ] : 
     215                        raise ValueError                 
     216                except ValueError :     
     217                    raise PyKotaToolError, _("Invalid value [%s] for --filter command line option, see help.") % filterexp 
     218                else :     
     219                    extractonly.update({ filterkey : filtervalue }) 
    217220             
    218221        datatype = options["data"] 
     
    306309                     "output" : "-", \ 
    307310                   } 
    308         short_options = "vhd:f:o:F:" 
    309         long_options = ["help", "version", "data=", "format=", "output=", "filter="] 
     311        short_options = "vhd:f:o:" 
     312        long_options = ["help", "version", "data=", "format=", "output="] 
    310313         
    311314        # Initializes the command line tool 
     
    321324        options["format"] = options["f"] or options["format"] or defaults["format"] 
    322325        options["output"] = options["o"] or options["output"] or defaults["output"] 
    323         options["filter"] = options["F"] or options["filter"] 
    324326         
    325327        if options["help"] : 
     
    330332            raise PyKotaToolError, _("The -d | --data command line option is mandatory, see help.") 
    331333        else : 
    332             if args : 
    333                 raise PyKotaToolError, _("Too many arguments, see help.") 
    334334            retcode = dumper.main(args, options) 
    335335    except SystemExit :         
  • pykota/trunk/NEWS

    r1980 r1991  
    2222PyKota NEWS : 
    2323 
     24    - 1.21alpha14 : 
     25     
     26        - dumpykota now supports extended filtering possibilities 
     27          with the PostgreSQL backend (LDAP support will come soon). 
     28           
    2429    - 1.21alpha13 : 
    2530     
  • pykota/trunk/pykota/storages/sql.py

    r1990 r1991  
    2222# 
    2323# $Log$ 
     24# Revision 1.61  2004/12/21 15:49:59  jalet 
     25# The dumpykota command now supports extended filtering capabilities with 
     26# the PostgreSQL backend. LDAP doesn't yet support such possibilities. 
     27# 
    2428# Revision 1.60  2004/12/21 14:45:31  jalet 
    2529# Prepared dumpykota to accept the new --filter command line option. Some 
     
    136140            return entries 
    137141         
     142    def createFilter(self, only) :     
     143        """Returns the appropriate SQL filter.""" 
     144        if only : 
     145            expressions = [] 
     146            for (k, v) in only.items() : 
     147                expressions.append("%s=%s" % (k, self.doQuote(v))) 
     148            return " AND ".join(expressions)      
     149        return ""         
     150         
    138151    def extractPrinters(self, extractonly={}) : 
    139152        """Extracts all printer records.""" 
    140         result = self.doRawSearch("SELECT * FROM printers ORDER BY id ASC") 
     153        thefilter = self.createFilter(extractonly) 
     154        if thefilter : 
     155            thefilter = "WHERE %s" % thefilter 
     156        result = self.doRawSearch("SELECT * FROM printers %s ORDER BY id ASC" % thefilter) 
    141157        return self.prepareRawResult(result) 
    142158         
    143159    def extractUsers(self, extractonly={}) : 
    144160        """Extracts all user records.""" 
    145         result = self.doRawSearch("SELECT * FROM users ORDER BY id ASC") 
     161        thefilter = self.createFilter(extractonly) 
     162        if thefilter : 
     163            thefilter = "WHERE %s" % thefilter 
     164        result = self.doRawSearch("SELECT * FROM users %s ORDER BY id ASC" % thefilter) 
    146165        return self.prepareRawResult(result) 
    147166         
    148167    def extractGroups(self, extractonly={}) : 
    149168        """Extracts all group records.""" 
    150         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") 
     169        thefilter = self.createFilter(extractonly) 
     170        if thefilter : 
     171            thefilter = "WHERE %s" % thefilter 
     172        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 ORDER BY groups.id ASC" % thefilter) 
    151173        return self.prepareRawResult(result) 
    152174         
    153175    def extractPayments(self, extractonly={}) : 
    154176        """Extracts all payment records.""" 
    155         result = self.doRawSearch("SELECT username,payments.* FROM users,payments WHERE users.id=payments.userid ORDER BY payments.id ASC") 
     177        thefilter = self.createFilter(extractonly) 
     178        if thefilter : 
     179            thefilter = "AND %s" % thefilter 
     180        result = self.doRawSearch("SELECT username,payments.* FROM users,payments WHERE users.id=payments.userid %s ORDER BY payments.id ASC" % thefilter) 
    156181        return self.prepareRawResult(result) 
    157182         
    158183    def extractUpquotas(self, extractonly={}) : 
    159184        """Extracts all userpquota records.""" 
    160         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") 
     185        thefilter = self.createFilter(extractonly) 
     186        if thefilter : 
     187            thefilter = "AND %s" % thefilter 
     188        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) 
    161189        return self.prepareRawResult(result) 
    162190         
    163191    def extractGpquotas(self, extractonly={}) : 
    164192        """Extracts all grouppquota records.""" 
    165         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") 
     193        thefilter = self.createFilter(extractonly) 
     194        if thefilter : 
     195            thefilter = "AND %s" % thefilter 
     196        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,groups.groupname,printers.printername ORDER BY grouppquota.id" % thefilter) 
    166197        return self.prepareRawResult(result) 
    167198         
    168199    def extractUmembers(self, extractonly={}) : 
    169200        """Extracts all user groups members.""" 
    170         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") 
     201        thefilter = self.createFilter(extractonly) 
     202        if thefilter : 
     203            thefilter = "AND %s" % thefilter 
     204        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) 
    171205        return self.prepareRawResult(result) 
    172206         
    173207    def extractPmembers(self, extractonly={}) : 
    174208        """Extracts all printer groups members.""" 
    175         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") 
     209        thefilter = self.createFilter(extractonly) 
     210        if thefilter : 
     211            thefilter = "AND %s" % thefilter 
     212        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) 
    176213        return self.prepareRawResult(result) 
    177214         
    178215    def extractHistory(self, extractonly={}) : 
    179216        """Extracts all jobhistory records.""" 
    180         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") 
     217        thefilter = self.createFilter(extractonly) 
     218        if thefilter : 
     219            thefilter = "AND %s" % thefilter 
     220        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) 
    181221        return self.prepareRawResult(result) 
    182222         
  • pykota/trunk/pykota/version.py

    r1980 r1991  
    2222# 
    2323 
    24 __version__ = "1.21alpha13_unofficial" 
     24__version__ = "1.21alpha14_unofficial" 
    2525 
    2626__doc__ = """PyKota : a complete Printing Quota Solution for CUPS and LPRng."""