Mercurial > sqlpython
annotate sqlpython/connections.py @ 473:d710a48e6fb7
connect problem unfixed
| author | catherine@dellzilla |
|---|---|
| date | Fri, 05 Mar 2010 12:59:28 -0500 |
| parents | 0be350ab306c |
| children | f29923ac1119 |
| rev | line source |
|---|---|
| 427 | 1 import re |
| 2 import os | |
| 428 | 3 import getpass |
| 427 | 4 import gerald |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
5 import time |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
6 import threading |
| 436 | 7 import pickle |
| 440 | 8 import optparse |
| 9 import doctest | |
| 10 | |
| 11 try: | |
| 12 import cx_Oracle | |
| 13 except ImportError: | |
| 14 pass | |
| 15 | |
| 16 try: | |
| 17 import psycopg2 | |
| 18 except ImportError: | |
| 19 pass | |
| 20 | |
| 21 try: | |
| 22 import MySQLdb | |
| 23 except ImportError: | |
| 24 pass | |
| 427 | 25 |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
26 class ObjectDescriptor(object): |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
27 def __init__(self, name, dbobj): |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
28 self.fullname = name |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
29 self.dbobj = dbobj |
| 433 | 30 self.type = str(type(self.dbobj)).split('.')[-1].lower().strip("'>") |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
31 self.path = '%s/%s' % (self.type, self.fullname) |
|
437
6ba7087f1a34
allow qualified or unqualified object names in gerald
catherine@dellzilla
parents:
436
diff
changeset
|
32 if '.' in self.fullname: |
|
6ba7087f1a34
allow qualified or unqualified object names in gerald
catherine@dellzilla
parents:
436
diff
changeset
|
33 (self.owner, self.unqualified_name) = self.fullname.split('.') |
|
6ba7087f1a34
allow qualified or unqualified object names in gerald
catherine@dellzilla
parents:
436
diff
changeset
|
34 self.owner = self.owner.lower() |
|
6ba7087f1a34
allow qualified or unqualified object names in gerald
catherine@dellzilla
parents:
436
diff
changeset
|
35 else: |
|
6ba7087f1a34
allow qualified or unqualified object names in gerald
catherine@dellzilla
parents:
436
diff
changeset
|
36 (self.owner, self.unqualified_name) = (None, self.fullname) |
| 435 | 37 self.unqualified_path = '%s/%s' % (self.type, self.unqualified_name) |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
38 def match_pattern(self, pattern, specific_owner=None): |
|
437
6ba7087f1a34
allow qualified or unqualified object names in gerald
catherine@dellzilla
parents:
436
diff
changeset
|
39 right_owner = (not self.owner) or (not specific_owner) or (self.owner == specific_owner.lower()) |
| 435 | 40 if not pattern: |
| 41 return right_owner | |
| 42 compiled = re.compile(pattern, re.IGNORECASE) | |
| 43 if r'\.' in pattern: | |
| 44 return compiled.match(self.fullname) or compiled.match(self.path) | |
| 45 return right_owner and (compiled.match(self.type) or | |
| 465 | 46 compiled.match(self.type + r'/') or |
| 435 | 47 compiled.match(self.unqualified_name) or |
| 48 compiled.match(self.unqualified_path)) | |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
49 |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
50 class GeraldPlaceholder(object): |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
51 current = False |
| 440 | 52 complete = False |
| 53 | |
| 54 class OptionTestDummy(object): | |
| 55 mysql = None | |
| 56 postgres = None | |
| 57 username = None | |
| 58 password = None | |
| 59 hostname = None | |
| 60 port = None | |
| 61 database = None | |
| 62 mode = 0 | |
| 63 def __init__(self, *args, **kwargs): | |
| 64 self.__dict__.update(kwargs) | |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
65 |
| 440 | 66 class ConnectionData(object): |
| 67 username = None | |
| 68 password = None | |
| 69 hostname = None | |
| 70 port = None | |
| 71 database = None | |
| 72 mode = 0 | |
| 73 connection_uri_parser = re.compile('(postgres|oracle|mysql|sqlite|mssql):/(.*$)', re.IGNORECASE) | |
| 74 connection_parser = re.compile('((?P<database>\S+)(\s+(?P<username>\S+))?)?') | |
| 75 def __init__(self, arg, opts, default_rdbms = 'oracle'): | |
| 76 ''' | |
| 77 >>> opts = OptionTestDummy(postgres=True, password='password') | |
| 78 >>> ConnectionData('thedatabase theuser', opts).uri() | |
| 79 'postgres://theuser:password@localhost:5432/thedatabase' | |
| 80 >>> opts = OptionTestDummy(password='password') | |
| 81 >>> ConnectionData('oracle://user:password@db', opts).uri() | |
| 82 'oracle://user:password@db' | |
| 83 >>> ConnectionData('user/password@db', opts).uri() | |
| 84 'oracle://user:password@db' | |
| 85 >>> ConnectionData('user/password@db as sysdba', opts).uri() | |
| 86 'oracle://user:password@db?mode=2' | |
| 87 >>> ConnectionData('user/password@thehost/db', opts).uri() | |
| 88 'oracle://user:password@thehost:1521/db' | |
| 89 >>> opts = OptionTestDummy(postgres=True, hostname='thehost', password='password') | |
| 90 >>> ConnectionData('thedatabase theuser', opts).uri() | |
| 91 'postgres://theuser:password@thehost:5432/thedatabase' | |
| 92 >>> opts = OptionTestDummy(mysql=True, password='password') | |
| 93 >>> ConnectionData('thedatabase theuser', opts).uri() | |
| 94 'mysql://theuser:password@localhost:3306/thedatabase' | |
| 447 | 95 >>> opts = OptionTestDummy(mysql=True, password='password') |
| 96 >>> ConnectionData('thedatabase', opts).uri() | |
| 97 'mysql://catherine:password@localhost:3306/thedatabase' | |
| 440 | 98 ''' |
| 99 self.arg = arg | |
| 100 self.opts = opts | |
| 101 self.default_rdbms = default_rdbms | |
| 102 self.determine_rdbms() | |
| 103 if not self.parse_connect_uri(arg): | |
| 104 self.set_defaults() | |
| 105 connectargs = self.connection_parser.search(self.arg) | |
| 106 if connectargs: | |
| 107 for param in ('username', 'password', 'database', 'port', 'hostname', 'mode'): | |
| 108 if hasattr(opts, param) and getattr(opts, param): | |
| 109 setattr(self, param, getattr(opts, param)) | |
| 110 else: | |
| 111 try: | |
| 112 if connectargs.group(param): | |
| 113 setattr(self, param, connectargs.group(param)) | |
| 114 except IndexError: | |
| 115 pass | |
| 116 self.set_corrections() | |
| 117 if not self.password: | |
| 441 | 118 self.password = getpass.getpass() |
| 440 | 119 def parse_connect_uri(self, uri): |
| 120 results = self.connection_uri_parser.search(uri) | |
| 121 if results: | |
| 122 (self.username, self.password, self.hostname, self.port, self.database | |
| 123 ) = gerald.utilities.dburi.Connection().parse_uri(results.group(2)) | |
| 124 self.set_class_from_rdbms_name(results.group(1)) | |
| 125 self.port = self.port or self.default_port | |
| 126 return True | |
| 127 else: | |
| 128 return False | |
| 129 def set_class_from_rdbms_name(self, rdbms_name): | |
| 130 for cls in (OracleConnectionData, PostgresConnectionData, MySQLConnectionData): | |
| 131 if cls.rdbms == rdbms_name: | |
| 132 self.__class__ = cls | |
| 133 def uri(self): | |
| 134 return '%s://%s:%s@%s:%s/%s' % (self.rdbms, self.username, self.password, | |
| 135 self.hostname, self.port, self.database) | |
| 473 | 136 colon_between_username_passwd = re.compile(r':(?=[^/]+@)') |
| 440 | 137 def gerald_uri(self): |
| 473 | 138 result = self.uri().split('?mode=')[0] |
| 139 result = self.colon_between_username_passwd.sub('/', result) | |
| 140 result = result.replace('://', ':/') | |
| 141 return result | |
| 440 | 142 def determine_rdbms(self): |
| 143 if self.opts.mysql: | |
| 144 self.__class__ = MySQLConnectionData | |
| 145 elif self.opts.postgres: | |
| 146 self.__class__ = PostgresConnectionData | |
| 147 else: | |
| 148 self.set_class_from_rdbms_name(self.default_rdbms) | |
| 149 def set_defaults(self): | |
| 150 self.port = self.default_port | |
| 151 def set_corrections(self): | |
| 152 pass | |
| 153 | |
| 154 class MySQLConnectionData(ConnectionData): | |
| 155 rdbms = 'mysql' | |
| 156 default_port = 3306 | |
| 157 def set_defaults(self): | |
| 158 self.port = self.default_port | |
| 159 self.hostname = 'localhost' | |
| 447 | 160 self.username = os.getenv('USER') |
| 441 | 161 self.database = os.getenv('USER') |
| 440 | 162 def connection(self): |
| 163 return MySQLdb.connect(host = self.hostname, user = self.username, | |
| 164 passwd = self.password, db = self.database, | |
| 165 port = self.port, sql_mode = 'ANSI') | |
| 166 | |
| 167 class PostgresConnectionData(ConnectionData): | |
| 168 rdbms = 'postgres' | |
| 169 default_port = 5432 | |
| 170 def set_defaults(self): | |
| 171 self.port = os.getenv('PGPORT') or self.default_port | |
| 172 self.database = os.getenv('ORACLE_SID') | |
| 173 self.hostname = os.getenv('PGHOST') or 'localhost' | |
| 441 | 174 self.username = os.getenv('USER') |
| 440 | 175 def connection(self): |
| 176 return psycopg2.connect(host = self.hostname, user = self.username, | |
| 177 password = self.password, database = self.database, | |
| 178 port = self.port) | |
| 179 | |
| 180 class OracleConnectionData(ConnectionData): | |
| 181 rdbms = 'oracle' | |
| 182 default_port = 1521 | |
| 183 connection_parser = re.compile('(?P<username>[^/\s@]*)(/(?P<password>[^/\s@]*))?(@((?P<hostname>[^/\s:]*)(:(?P<port>\d{1,4}))?/)?(?P<database>[^/\s:]*))?(\s+as\s+(?P<mode>sys(dba|oper)))?', | |
| 184 re.IGNORECASE) | |
| 185 def uri(self): | |
| 186 if self.hostname: | |
| 187 uri = '%s://%s:%s@%s:%s/%s' % (self.rdbms, self.username, self.password, | |
| 188 self.hostname, self.port, self.database) | |
| 189 else: | |
| 190 uri = '%s://%s:%s@%s' % (self.rdbms, self.username, self.password, self.database) | |
| 191 if self.mode: | |
| 192 uri = '%s?mode=%d' % (uri, self.mode) | |
| 193 return uri | |
| 194 def set_defaults(self): | |
| 195 self.port = 1521 | |
| 196 self.database = os.getenv('ORACLE_SID') | |
| 197 def set_corrections(self): | |
| 198 if self.mode: | |
| 199 self.mode = getattr(cx_Oracle, self.mode.upper()) | |
| 200 if self.hostname: | |
| 201 self.dsn = cx_Oracle.makedsn(self.hostname, self.port, self.database) | |
| 202 else: | |
| 203 self.dsn = self.database | |
| 204 def parse_connect_uri(self, uri): | |
| 205 if ConnectionData.parse_connect_uri(self, uri): | |
| 206 if not self.database: | |
| 207 self.database = self.hostname | |
| 208 self.hostname = None | |
| 209 self.port = self.default_port | |
| 210 return True | |
| 211 return False | |
| 212 def connection(self): | |
| 213 return cx_Oracle.connect(user = self.username, password = self.password, | |
| 214 dsn = self.dsn, mode = self.mode) | |
| 215 | |
| 216 gerald_classes = {'oracle': gerald.oracle_schema.User, | |
| 217 'postgres': gerald.PostgresSchema, | |
| 218 'mysql': gerald.MySQLSchema } | |
| 219 | |
| 428 | 220 class DatabaseInstance(object): |
| 436 | 221 import_failure = None |
| 439 | 222 username = None |
| 427 | 223 password = None |
| 439 | 224 port = None |
| 427 | 225 uri = None |
| 436 | 226 pickledir = os.path.join(os.getenv('HOME'), '.sqlpython') |
| 427 | 227 connection_uri_parser = re.compile('(postgres|oracle|mysql|sqlite|mssql):/(.*$)', re.IGNORECASE) |
| 228 | |
| 229 def __init__(self, arg, opts, default_rdbms = 'oracle'): | |
| 440 | 230 #opts.username = opts.username or opts.user |
| 231 self.conn_data = ConnectionData(arg, opts, default_rdbms) | |
| 232 for v in ('username', 'database', 'rdbms'): | |
| 233 setattr(self, v, getattr(self.conn_data, v)) | |
| 234 self.connection = self.conn_data.connection() | |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
235 self.gerald = GeraldPlaceholder() |
| 436 | 236 self.discover_metadata() |
| 433 | 237 |
| 238 def discover_metadata(self): | |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
239 self.metadata_discovery_thread = MetadataDiscoveryThread(self) |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
240 self.metadata_discovery_thread.start() |
| 433 | 241 |
| 428 | 242 def set_instance_number(self, instance_number): |
| 243 self.instance_number = instance_number | |
| 440 | 244 self.prompt = "%d:%s@%s> " % (self.instance_number, self.username, self.database) |
| 436 | 245 def pickle(self): |
| 246 try: | |
| 247 os.mkdir(self.pickledir) | |
| 248 except OSError: | |
| 249 pass | |
| 250 picklefile = open(self.picklefile(), 'w') | |
| 251 pickle.dump(self.gerald.schema, picklefile) | |
| 252 picklefile.close() | |
| 253 def picklefile(self): | |
| 254 return os.path.join(self.pickledir, ('%s.%s.%s.%s.pickle' % | |
| 440 | 255 (self.rdbms, self.username, self.conn_data.hostname, self.database)).lower()) |
| 436 | 256 def retreive_pickled_gerald(self): |
| 448 | 257 picklefile = open(self.picklefile()) |
| 258 schema = pickle.load(picklefile) | |
| 259 picklefile.close() | |
| 440 | 260 newgerald = gerald_classes[self.rdbms](self.username, None) |
| 261 newgerald.connect(self.conn_data.gerald_uri()) | |
| 436 | 262 newgerald.schema = schema |
| 263 newgerald.current = False | |
| 264 newgerald.complete = True | |
| 265 newgerald.descriptions = {} | |
| 266 for (name, obj) in newgerald.schema.items(): | |
| 267 newgerald.descriptions[name] = ObjectDescriptor(name, obj) | |
| 268 self.gerald = newgerald | |
|
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
269 |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
270 class MetadataDiscoveryThread(threading.Thread): |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
271 def __init__(self, db_instance): |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
272 threading.Thread.__init__(self) |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
273 self.db_instance = db_instance |
|
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
274 def run(self): |
| 436 | 275 if not self.db_instance.gerald.complete: |
| 276 try: | |
| 277 self.db_instance.retreive_pickled_gerald() | |
| 278 except IOError: | |
| 279 pass | |
| 433 | 280 self.db_instance.gerald.current = False |
| 448 | 281 newgerald = gerald_classes[self.db_instance.rdbms](self.db_instance.username, self.db_instance.conn_data.gerald_uri()) |
| 433 | 282 newgerald.descriptions = {} |
| 448 | 283 for (name, obj) in newgerald.schema.items(): |
| 284 newgerald.descriptions[name] = ObjectDescriptor(name, obj) | |
| 433 | 285 newgerald.current = True |
| 286 newgerald.complete = True | |
| 287 self.db_instance.gerald = newgerald | |
| 436 | 288 self.db_instance.pickle() |
| 440 | 289 |
| 290 if __name__ == '__main__': | |
| 448 | 291 doctest.testmod() |