#!/usr/bin/env python # $Id: forgetsql-generate,v 1.5 2004/03/08 11:04:28 stain Exp $ ## Distributed under LGPL ## (c) Stian Søiland 2002-2004 ## stian@soiland.no ## http://forgetsql.sourceforge.net/ # __version__ should really come from setup.py.. hmm __version__ = "0.5.1" import exceptions, time, re, types, pprint, sys import forgetSQL # backwards compatibility try: True,False except NameError: (True, False) = (1==1, 1==0) # Taken from http://www.python.org/doc/current/lib/built-in-funcs.html def my_import(name): mod = __import__(name) components = name.split('.') # Takes care of things like pyPgSQL.PgSQL for comp in components[1:]: mod = getattr(mod, comp) return mod def generateFromTables(tables, cursor, getLinks=1, code=0): """Generates python code (or class objects if code is false) based on SQL queries on the table names given in the list tables. code - if given - should be an dictionary containing these keys to be inserted into generated code: 'database': database name 'module': database module name 'connect': string to be inserted into module.connect() """ curs = cursor() forgetters = {} class _Wrapper(forgetSQL.Forgetter): pass _Wrapper.cursor = cursor for table in tables: # capitalize the table name to make it look like a class name = table.capitalize() # Define the class by instanciating the meta class to # the given name (requires Forgetter to be new style) forgetter = _Wrapper.__class__(name, (_Wrapper,), {}) # Register it forgetters[name] = forgetter forgetter._sqlTable = table forgetter._sqlLinks = {} forgetter._sqlFields = {} forgetter._shortView = () forgetter._descriptions = {} forgetter._userClasses = {} # Get columns curs.execute("SELECT * FROM %s LIMIT 1" % table) columns = [column[0] for column in curs.description] # convert to dictionary and register in forgetter for column in columns: forgetter._sqlFields[column] = column if getLinks: # Try to find links between tables (!) # Note the big O factor with this ... for (tableName, forgetter) in forgetters.items(): for (key, column) in forgetter._sqlFields.items(): # A column refering to another table would most likely # be called otherColumnID or just otherColumn. We'll # lowercase below when performing the test. possTable = re.sub(r'_?id$', '', column) # all tables (ie. one of the forgetters) are candidates foundLink = False for candidate in forgetters.keys(): if candidate.lower() == possTable.lower(): if possTable.lower() == tableName.lower(): # It's our own primary key! forgetter._sqlPrimary = (column,) break # Woooh! First - let's replace 'blapp_id' with 'blapp' # as the attribute name to indicate that it would # contain the Blapp instance, not just # some ID. del forgetter._sqlFields[key] forgetter._sqlFields[possTable] = column # And.. we'll need to know which class we refer to forgetter._userClasses[possTable] = candidate break # we've found our candidate if code: if code['module'] == "MySQLdb": code['class'] = 'forgetSQL.MysqlForgetter' else: code['class'] = 'forgetSQL.Forgetter' code['date'] = time.strftime('%Y-%m-%d') print ''' """Database wrappers %(database)s Autogenerated by forgetsql-generate %(date)s. """ import forgetSQL #import %(module)s class _Wrapper(%(class)s): """Just a simple wrapper class so that you may easily change stuff for all forgetters. Typically this involves subclassing MysqlForgetter instead.""" # Example database connection (might miss password) #_dbModule = %(module)s #_dbConnection = %(module)s.connect(%(connect)s) #def cursor(self): # return self._dbConnection.cursor() ''' % code items = forgetters.items() items.sort() for (name, forgetter) in items: print "class %s(_Wrapper):" % name for (key, value) in forgetter.__dict__.items(): if key.find('__') == 0: continue nice = pprint.pformat(value) # Get some indention nice = nice.replace('\n', '\n ' + ' '*len(key)) print ' %s = ' % key, nice print "" print ''' # Prepare them all. We need to send in our local # namespace. forgetSQL.prepareClasses(locals()) ''' else: forgetSQL.prepareClasses(forgetters) return forgetters def main(): try: # Should from optparse import OptionParser except ImportError: print >>sys.stderr, "optik 1.4.1 or Python 2.3 or later needed for command line usage" print >>sys.stderr, "Download optik from http://optik.sourceforge.net/" print >>sys.stderr, "or upgrade Python." sys.exit(1) usage = """usage: %prog [options] Generates Python code for using forgetSQL to access database tables. You need to include a line-seperated list of table names to either stdin or as a file using option --tables.""" parser = OptionParser(version="%prog " + __version__, usage=usage) parser.add_option("-t", "--tables", dest="tables", help="read list of tables from FILE instead of stdin", metavar="FILE") parser.add_option("-o", "--output", dest="output", help="write generated code to OUTPUT instead of stdout") parser.add_option("-m", "--dbmodule", dest="dbmodule", help="database module to use") parser.add_option("-H", "--host", dest="host", help="hostname of database server") parser.add_option("-d", "--database", dest="database", help="database to connect to") parser.add_option("-u", "--username", dest="username", help="database username") parser.add_option("-p", "--password", dest="password", help="database password") parser.add_option("-c", "--connect", dest="connect", help="database connect string (instead of host/database/user/password") (options, args) = parser.parse_args() if options.tables: try: file = open(options.tables) except IOError, e: print >>sys.stderr, "%s: %s" % (e.strerror, e.filename) sys.exit(2) else: file = sys.stdin if options.output: try: # Override print.. dirty. sys.stdout = open(output, "w") except IOError, e: print >>sys.stderr, "%s: %s" % (e.strerror, e.filename) sys.exit(3) if not options.dbmodule: print >>sys.stderr, "Missing required option --dbmodule" parser.print_help(file=sys.stderr) sys.exit(4) try: dbmodule = my_import(options.dbmodule) except ImportError: print >>sys.stderr, "Unknown database module", options.dbmodule sys.exit(5) if options.connect: connectstring = options.connect try: connection = dbmodule.connect(options.connect) except Exception, e: print >>sys.stderr, "Could not connect to database using", \ options.connect sys.exit(6) else: params = {} if options.database: params['database'] = options.database else: print >>sys.stderr, "Missing required option --database or --connect" sys.exit(7) if options.host: params['host'] = options.host if options.username: params['user'] = options.username if options.password: params['password'] = options.password connectstring = ", ".join(["%s=%r" % (key, value) for (key,value) in params.items() # filter out password for 'security reasons' if key != "password"]) try: connection = dbmodule.connect(**params) except Exception, e: print >>sys.stderr, "Could not connect to database using", \ connectstring print >>sys.stderr, e sys.exit(8) cursor = connection.cursor tables = file.read().split() if not tables: print >>sys.stderr, "No table names supplied" sys.exit(9) # collect useful strings for generated code code = {} code['connect'] = connectstring code['module'] = options.dbmodule code['database'] = options.database or '(unknown)' generateFromTables(tables, cursor, code=code) if __name__=='__main__': main()