Mercurial > sqlpython
annotate sqlpython/connections.py @ 477:920a45f244e8
setuptools optional
author | catherine@dellzilla |
---|---|
date | Fri, 12 Mar 2010 19:42:11 -0500 |
parents | 8937b1476c42 |
children | 99734f5dded5 |
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 = result.replace('://', ':/') | |
140 return result | |
440 | 141 def determine_rdbms(self): |
142 if self.opts.mysql: | |
143 self.__class__ = MySQLConnectionData | |
144 elif self.opts.postgres: | |
145 self.__class__ = PostgresConnectionData | |
146 else: | |
147 self.set_class_from_rdbms_name(self.default_rdbms) | |
148 def set_defaults(self): | |
149 self.port = self.default_port | |
150 def set_corrections(self): | |
151 pass | |
152 | |
153 class MySQLConnectionData(ConnectionData): | |
154 rdbms = 'mysql' | |
155 default_port = 3306 | |
156 def set_defaults(self): | |
157 self.port = self.default_port | |
158 self.hostname = 'localhost' | |
447 | 159 self.username = os.getenv('USER') |
441 | 160 self.database = os.getenv('USER') |
440 | 161 def connection(self): |
162 return MySQLdb.connect(host = self.hostname, user = self.username, | |
163 passwd = self.password, db = self.database, | |
164 port = self.port, sql_mode = 'ANSI') | |
165 | |
166 class PostgresConnectionData(ConnectionData): | |
167 rdbms = 'postgres' | |
168 default_port = 5432 | |
169 def set_defaults(self): | |
170 self.port = os.getenv('PGPORT') or self.default_port | |
171 self.database = os.getenv('ORACLE_SID') | |
172 self.hostname = os.getenv('PGHOST') or 'localhost' | |
441 | 173 self.username = os.getenv('USER') |
440 | 174 def connection(self): |
175 return psycopg2.connect(host = self.hostname, user = self.username, | |
176 password = self.password, database = self.database, | |
177 port = self.port) | |
178 | |
179 class OracleConnectionData(ConnectionData): | |
180 rdbms = 'oracle' | |
181 default_port = 1521 | |
182 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)))?', | |
183 re.IGNORECASE) | |
184 def uri(self): | |
185 if self.hostname: | |
186 uri = '%s://%s:%s@%s:%s/%s' % (self.rdbms, self.username, self.password, | |
187 self.hostname, self.port, self.database) | |
188 else: | |
189 uri = '%s://%s:%s@%s' % (self.rdbms, self.username, self.password, self.database) | |
190 if self.mode: | |
191 uri = '%s?mode=%d' % (uri, self.mode) | |
192 return uri | |
193 def set_defaults(self): | |
194 self.port = 1521 | |
195 self.database = os.getenv('ORACLE_SID') | |
196 def set_corrections(self): | |
197 if self.mode: | |
198 self.mode = getattr(cx_Oracle, self.mode.upper()) | |
199 if self.hostname: | |
200 self.dsn = cx_Oracle.makedsn(self.hostname, self.port, self.database) | |
201 else: | |
202 self.dsn = self.database | |
203 def parse_connect_uri(self, uri): | |
204 if ConnectionData.parse_connect_uri(self, uri): | |
205 if not self.database: | |
206 self.database = self.hostname | |
207 self.hostname = None | |
208 self.port = self.default_port | |
209 return True | |
210 return False | |
211 def connection(self): | |
212 return cx_Oracle.connect(user = self.username, password = self.password, | |
213 dsn = self.dsn, mode = self.mode) | |
214 | |
215 gerald_classes = {'oracle': gerald.oracle_schema.User, | |
216 'postgres': gerald.PostgresSchema, | |
217 'mysql': gerald.MySQLSchema } | |
218 | |
428 | 219 class DatabaseInstance(object): |
436 | 220 import_failure = None |
439 | 221 username = None |
427 | 222 password = None |
439 | 223 port = None |
427 | 224 uri = None |
474 | 225 pickledir = os.path.join(os.path.expanduser('~'), '.sqlpython') |
427 | 226 connection_uri_parser = re.compile('(postgres|oracle|mysql|sqlite|mssql):/(.*$)', re.IGNORECASE) |
227 | |
228 def __init__(self, arg, opts, default_rdbms = 'oracle'): | |
440 | 229 #opts.username = opts.username or opts.user |
230 self.conn_data = ConnectionData(arg, opts, default_rdbms) | |
231 for v in ('username', 'database', 'rdbms'): | |
232 setattr(self, v, getattr(self.conn_data, v)) | |
233 self.connection = self.conn_data.connection() | |
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
234 self.gerald = GeraldPlaceholder() |
436 | 235 self.discover_metadata() |
433 | 236 |
237 def discover_metadata(self): | |
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
238 self.metadata_discovery_thread = MetadataDiscoveryThread(self) |
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
239 self.metadata_discovery_thread.start() |
433 | 240 |
428 | 241 def set_instance_number(self, instance_number): |
242 self.instance_number = instance_number | |
440 | 243 self.prompt = "%d:%s@%s> " % (self.instance_number, self.username, self.database) |
436 | 244 def pickle(self): |
245 try: | |
246 os.mkdir(self.pickledir) | |
247 except OSError: | |
248 pass | |
249 picklefile = open(self.picklefile(), 'w') | |
250 pickle.dump(self.gerald.schema, picklefile) | |
251 picklefile.close() | |
252 def picklefile(self): | |
253 return os.path.join(self.pickledir, ('%s.%s.%s.%s.pickle' % | |
440 | 254 (self.rdbms, self.username, self.conn_data.hostname, self.database)).lower()) |
436 | 255 def retreive_pickled_gerald(self): |
448 | 256 picklefile = open(self.picklefile()) |
257 schema = pickle.load(picklefile) | |
258 picklefile.close() | |
477 | 259 newgerald = gerald_classes[self.rdbms](self.username, None, omit_error_objects=True) |
440 | 260 newgerald.connect(self.conn_data.gerald_uri()) |
436 | 261 newgerald.schema = schema |
262 newgerald.current = False | |
263 newgerald.complete = True | |
264 newgerald.descriptions = {} | |
265 for (name, obj) in newgerald.schema.items(): | |
266 newgerald.descriptions[name] = ObjectDescriptor(name, obj) | |
267 self.gerald = newgerald | |
432
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
268 |
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
269 class MetadataDiscoveryThread(threading.Thread): |
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
270 def __init__(self, db_instance): |
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
271 threading.Thread.__init__(self) |
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
272 self.db_instance = db_instance |
26b09e1481e7
beginning to reimplement threaded metadata discovery
catherine@dellzilla
parents:
431
diff
changeset
|
273 def run(self): |
436 | 274 if not self.db_instance.gerald.complete: |
275 try: | |
276 self.db_instance.retreive_pickled_gerald() | |
277 except IOError: | |
278 pass | |
433 | 279 self.db_instance.gerald.current = False |
477 | 280 newgerald = gerald_classes[self.db_instance.rdbms](self.db_instance.username, self.db_instance.conn_data.gerald_uri(), omit_error_objects=True) |
433 | 281 newgerald.descriptions = {} |
448 | 282 for (name, obj) in newgerald.schema.items(): |
283 newgerald.descriptions[name] = ObjectDescriptor(name, obj) | |
433 | 284 newgerald.current = True |
285 newgerald.complete = True | |
286 self.db_instance.gerald = newgerald | |
436 | 287 self.db_instance.pickle() |
440 | 288 |
289 if __name__ == '__main__': | |
448 | 290 doctest.testmod() |