Mercurial > sqlpython
annotate sqlpython/connections.py @ 436:f441f2eb52e0
pickling gerald data
| author | catherine@dellzilla |
|---|---|
| date | Thu, 28 Jan 2010 17:05:50 -0500 |
| parents | 5443c5d5ed8c |
| children | 6ba7087f1a34 |
| rev | line source |
|---|---|
| 427 | 1 import re |
| 2 import os | |
| 428 | 3 import getpass |
| 427 | 4 import gerald |
| 5 import schemagroup | |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
6 import time |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
7 import threading |
| 436 | 8 import pickle |
| 427 | 9 |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
10 class ObjectDescriptor(object): |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
11 def __init__(self, name, dbobj): |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
12 self.fullname = name |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
13 self.dbobj = dbobj |
| 433 | 14 self.type = str(type(self.dbobj)).split('.')[-1].lower().strip("'>") |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
15 self.path = '%s/%s' % (self.type, self.fullname) |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
16 (self.owner, self.unqualified_name) = self.fullname.split('.') |
| 435 | 17 self.unqualified_path = '%s/%s' % (self.type, self.unqualified_name) |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
18 self.owner = self.owner.lower() |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
19 def match_pattern(self, pattern, specific_owner=None): |
| 435 | 20 right_owner = (not specific_owner) or (self.owner == specific_owner.lower()) |
| 21 if not pattern: | |
| 22 return right_owner | |
| 23 compiled = re.compile(pattern, re.IGNORECASE) | |
| 24 if r'\.' in pattern: | |
| 25 return compiled.match(self.fullname) or compiled.match(self.path) | |
| 26 return right_owner and (compiled.match(self.type) or | |
| 27 compiled.match(self.unqualified_name) or | |
| 28 compiled.match(self.unqualified_path)) | |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
29 |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
30 class GeraldPlaceholder(object): |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
31 current = False |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
32 complete = False |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
33 |
| 428 | 34 class DatabaseInstance(object): |
| 436 | 35 import_failure = None |
| 427 | 36 password = None |
| 37 uri = None | |
| 436 | 38 pickledir = os.path.join(os.getenv('HOME'), '.sqlpython') |
| 427 | 39 connection_uri_parser = re.compile('(postgres|oracle|mysql|sqlite|mssql):/(.*$)', re.IGNORECASE) |
| 40 | |
| 41 def __init__(self, arg, opts, default_rdbms = 'oracle'): | |
| 42 self.default_rdbms = default_rdbms | |
| 43 if not self.parse_connect_uri(arg): | |
| 44 self.parse_connect_arg(arg, opts) | |
| 428 | 45 self.connection = self.new_connection() |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
46 self.gerald = GeraldPlaceholder() |
| 436 | 47 self.discover_metadata() |
| 433 | 48 |
| 49 def discover_metadata(self): | |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
50 self.metadata_discovery_thread = MetadataDiscoveryThread(self) |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
51 self.metadata_discovery_thread.start() |
| 427 | 52 |
| 53 def parse_connect_uri(self, uri): | |
| 54 results = self.connection_uri_parser.search(uri) | |
| 55 if results: | |
| 56 (self.username, self.password, self.host, self.port, self.db_name | |
| 57 ) = gerald.utilities.dburi.Connection().parse_uri(results.group(2)) | |
| 58 self.__class__ = rdbms_types.get(results.group(1)) | |
| 59 self.uri = uri | |
| 60 self.port = self.port or self.default_port | |
| 61 return True | |
| 62 else: | |
| 63 return False | |
| 64 | |
| 65 def parse_connect_arg(self, arg, opts): | |
| 66 self.host = opts.hostname | |
| 67 self.oracle_connect_mode = 0 | |
| 68 if opts.postgres: | |
| 428 | 69 self.__class__ = PostgresDatabaseInstance |
| 427 | 70 elif opts.mysql: |
| 428 | 71 self.__class__ = MySQLDatabaseInstance |
| 427 | 72 elif opts.oracle: |
| 428 | 73 self.__class__ = OracleDatabaseInstance |
| 427 | 74 else: |
| 75 self.__class__ = rdbms_types.get(self.default_rdbms) | |
| 76 self.assign_args(arg, opts) | |
| 77 self.db_name = opts.database or self.db_name | |
| 78 self.port = self.port or self.default_port | |
|
430
aa1a603740fe
making progress, but there is something awful about threaded calls to sysdate
catherine@dellzilla
parents:
428
diff
changeset
|
79 self.password = self.password or opts.password or getpass.getpass('Password: ') |
| 436 | 80 self.uri = self.uri or self.calculated_uri() |
| 81 def calculated_uri(self): | |
| 82 return '%s://%s:%s@%s:%s/%s' % (self.rdbms, self.username, self.password, | |
| 83 self.host, self.port, self.db_name) | |
| 427 | 84 def gerald_uri(self): |
| 85 return self.uri.split('?mode=')[0] | |
| 433 | 86 |
| 428 | 87 def set_instance_number(self, instance_number): |
| 88 self.instance_number = instance_number | |
| 436 | 89 self.prompt = "%d:%s@%s> " % (self.instance_number, self.username, self.db_name) |
| 90 def pickle(self): | |
| 91 try: | |
| 92 os.mkdir(self.pickledir) | |
| 93 except OSError: | |
| 94 pass | |
| 95 picklefile = open(self.picklefile(), 'w') | |
| 96 pickle.dump(self.gerald.schema, picklefile) | |
| 97 picklefile.close() | |
| 98 def picklefile(self): | |
| 99 return os.path.join(self.pickledir, ('%s.%s.%s.%s.pickle' % | |
| 100 (self.rdbms, self.username, self.host, self.db_name)).lower()) | |
| 101 def retreive_pickled_gerald(self): | |
| 102 picklefile = open(self.picklefile()) | |
| 103 schema = pickle.load(picklefile) | |
| 104 picklefile.close() | |
| 105 newgerald = rdbms_types[self.rdbms].gerald_class(self.username, None) | |
| 106 newgerald.connect(self.gerald_uri()) | |
| 107 newgerald.schema = schema | |
| 108 newgerald.current = False | |
| 109 newgerald.complete = True | |
| 110 newgerald.descriptions = {} | |
| 111 for (name, obj) in newgerald.schema.items(): | |
| 112 newgerald.descriptions[name] = ObjectDescriptor(name, obj) | |
| 113 self.gerald = newgerald | |
| 427 | 114 |
| 428 | 115 class OpenSourceDatabaseInstance(DatabaseInstance): |
| 116 def assign_args(self, arg, opts): | |
| 117 self.assign_details(arg, opts) | |
| 118 self.username = self.username or os.environ['USER'] | |
| 427 | 119 self.db_name = self.db_name or self.username |
| 428 | 120 self.host = opts.hostname or self.host or 'localhost' |
| 427 | 121 |
| 436 | 122 class ImportFailure(DatabaseInstance): |
| 123 def fail(self, *arg, **kwargs): | |
| 124 raise ImportError, 'Python DB-API2 module (MySQLdb/psycopg2/cx_Oracle) was not successfully imported' | |
| 125 assign_args = fail | |
| 126 new_connection = fail | |
| 127 | |
| 427 | 128 try: |
| 129 import psycopg2 | |
| 428 | 130 class PostgresDatabaseInstance(OpenSourceDatabaseInstance): |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
131 gerald_class = gerald.PostgresSchema |
| 427 | 132 rdbms = 'postgres' |
| 133 default_port = 5432 | |
| 134 def assign_details(self, arg, opts): | |
| 428 | 135 self.port = opts.port or os.getenv('PGPORT') or self.default_port |
| 427 | 136 self.host = self.host or os.getenv('PGHOST') |
| 137 args = arg.split() | |
| 138 if len(args) > 1: | |
| 139 self.username = args[1] | |
| 140 if len(args) > 0: | |
| 141 self.db_name = args[0] | |
| 142 def new_connection(self): | |
| 143 return psycopg2.connect(host = self.host, user = self.username, | |
| 144 password = self.password, database = self.db_name, | |
| 145 port = self.port) | |
| 146 except ImportError: | |
| 436 | 147 PostgresDatabaseInstance = ImportFailure |
| 427 | 148 |
| 149 try: | |
| 150 import MySQLdb | |
| 428 | 151 class MySQLDatabaseInstance(OpenSourceDatabaseInstance): |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
152 gerald_class = gerald.MySQLSchema |
| 427 | 153 rdbms = 'mysql' |
| 154 default_port = 3306 | |
| 155 def assign_details(self, arg, opts): | |
| 156 self.db_name = arg | |
| 157 def new_connection(self): | |
| 158 return MySQLdb.connect(host = self.host, user = self.username, | |
| 159 passwd = self.password, db = self.db_name, | |
| 160 port = self.port, sql_mode = 'ANSI') | |
| 161 except ImportError: | |
| 436 | 162 MySQLDatabaseInstance = ImportFailure |
| 427 | 163 try: |
| 164 import cx_Oracle | |
| 165 | |
| 428 | 166 class OracleDatabaseInstance(DatabaseInstance): |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
167 gerald_class = gerald.oracle_schema.User |
| 427 | 168 rdbms = 'oracle' |
| 434 | 169 connection_parser = re.compile('(?P<username>[^/\s@]*)(/(?P<password>[^/\s@]*))?(@((?P<host>[^/\s:]*)(:(?P<port>\d{1,4}))?/)?(?P<db_name>[^/\s:]*))?(\s+as\s+(?P<mode>sys(dba|oper)))?', |
| 427 | 170 re.IGNORECASE) |
| 171 connection_modes = {'SYSDBA': cx_Oracle.SYSDBA, 'SYSOPER': cx_Oracle.SYSOPER} | |
| 172 oracle_connect_mode = 0 | |
| 173 default_port = 1521 | |
| 174 def assign_args(self, arg, opts): | |
| 175 connectargs = self.connection_parser.search(arg) | |
| 176 self.username = connectargs.group('username') | |
| 177 self.password = connectargs.group('password') | |
| 434 | 178 self.db_name = connectargs.group('db_name') or os.getenv('ORACLE_SID') |
| 427 | 179 self.port = connectargs.group('port') or self.default_port |
| 180 self.host = connectargs.group('host') | |
| 181 if self.host: | |
| 182 self.dsn = cx_Oracle.makedsn(self.host, self.port, self.db_name) | |
| 183 else: | |
| 184 self.dsn = self.db_name | |
| 185 if connectargs.group('mode'): | |
| 186 self.oracle_connect_mode = self.connection_modes.get(connectargs.group('mode').upper()) | |
| 436 | 187 def calculated_uri(self): |
| 188 if self.host: | |
| 189 result = DatabaseInstance.calculated_uri(self) | |
| 190 else: | |
| 191 result = '%s://%s:%s@%s' % (self.rdbms, self.username, self.password, | |
| 192 self.db_name) | |
| 193 if self.oracle_connect_mode: | |
| 194 result = "%s?mode=%d" % (result, self.oracle_connect_mode) | |
| 195 return result | |
| 427 | 196 def new_connection(self): |
| 197 return cx_Oracle.connect(user = self.username, | |
| 198 password = self.password, | |
| 199 dsn = self.dsn, | |
| 200 mode = self.oracle_connect_mode) | |
| 201 | |
| 202 | |
| 203 except ImportError: | |
| 436 | 204 OracleDatabaseInstance = ImportFailure |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
205 |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
206 class MetadataDiscoveryThread(threading.Thread): |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
207 def __init__(self, db_instance): |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
208 threading.Thread.__init__(self) |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
209 self.db_instance = db_instance |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
210 def run(self): |
| 436 | 211 if not self.db_instance.gerald.complete: |
| 212 try: | |
| 213 self.db_instance.retreive_pickled_gerald() | |
| 214 except IOError: | |
| 215 pass | |
| 433 | 216 self.db_instance.gerald.current = False |
| 217 newgerald = self.db_instance.gerald_class(self.db_instance.username, self.db_instance.gerald_uri()) | |
| 218 newgerald.descriptions = {} | |
| 219 for (name, obj) in newgerald.schema.items(): | |
| 220 newgerald.descriptions[name] = ObjectDescriptor(name, obj) | |
| 221 newgerald.current = True | |
| 222 newgerald.complete = True | |
| 223 self.db_instance.gerald = newgerald | |
| 436 | 224 self.db_instance.pickle() |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
225 |
| 428 | 226 rdbms_types = {'oracle': OracleDatabaseInstance, 'mysql': MySQLDatabaseInstance, 'postgres': PostgresDatabaseInstance} |
| 427 | 227 |
| 228 |