Mercurial > sqlpython
annotate sqlpython/schemagroup.py @ 407:188c86d4a11e
struggling with option parsing
| author | catherine@DellZilla |
|---|---|
| date | Wed, 14 Oct 2009 13:57:29 -0400 |
| parents | 1e3179b31a1e |
| children | dd9aab264798 |
| rev | line source |
|---|---|
| 405 | 1 import gerald, re, datetime, threading, time, operator |
| 382 | 2 |
| 387 | 3 def gerald_connection_string(sqlalchemy_connection_string): |
| 4 return sqlalchemy_connection_string.split('/?mode=')[0].replace('//','/') | |
| 405 | 5 |
| 6 def build_column_list(schema): | |
| 7 schema.column_names = [[c for c in o.columns] for o in schema.schema.values() | |
| 8 if hasattr(o, 'columns') | |
| 9 and hasattr(o.columns, 'keys')] | |
| 10 schema.column_names = reduce(operator.add, schema.column_names, []) | |
| 11 schema.table_names = [] | |
| 12 schema.qual_table_names = [] | |
| 13 for t in schema.schema.values(): | |
| 14 if hasattr(t, 'columns') and isinstance(t.columns, dict): | |
| 15 schema.table_names.append(t.name) | |
| 16 schema.qual_table_names.append('%s.%s' % (schema.name, t.name)) | |
| 17 for c in t.columns: | |
| 18 schema.column_names.append('%s.%s' % (t.name, c)) | |
| 19 schema.qual_column_names = ['%s.%s' % (schema.name, c) for c in schema.column_names] | |
| 20 | |
| 386 | 21 class RefreshThread(threading.Thread): |
| 22 def __init__(self, schemagroup, owner): | |
| 23 threading.Thread.__init__(self) | |
| 24 self.schemagroup = schemagroup | |
| 25 self.owner = owner | |
| 26 self.schema = self.schemagroup.schemas.get(self.owner) | |
| 27 def run(self): | |
| 387 | 28 if (not self.schema) or (self.schema.is_stale()): |
| 29 self.schema = self.schemagroup.childtype(self.owner, self.schemagroup) | |
| 30 else: | |
| 386 | 31 self.schema.refreshed = self.schema.time() |
| 405 | 32 self.schemagroup.schemas[self.owner] = self.schema |
| 33 build_column_list(self.schema) | |
| 34 | |
| 387 | 35 class RefreshGroupThread(threading.Thread): |
| 405 | 36 def __init__(self, schemas): |
| 387 | 37 threading.Thread.__init__(self) |
| 390 | 38 self.parent = threading.current_thread() |
| 387 | 39 self.schemas = schemas |
| 40 self.daemon = True | |
| 41 def run(self): | |
| 405 | 42 self.schemas.refresh() |
| 43 s.column_names = reduce(operator.add, | |
| 44 [s.qual_column_names for s in self.schemas.values()], | |
| 45 []) | |
| 46 s.table_names = reduce(operator.add, | |
| 47 [s.qual_table_names for s in self.schemas.values()], | |
| 48 []) | |
| 387 | 49 |
| 407 | 50 class OracleSchemaAccess(object): |
| 51 child_type = gerald.OracleSchema | |
| 52 current_database_time_query = 'SELECT sysdate FROM dual' | |
| 53 def latest_ddl_timestamp_query(self, username, connection): | |
| 54 curs = connection.cursor() | |
| 55 curs.execute('''SELECT owner, MAX(last_ddl_time) | |
| 56 FROM all_objects | |
| 57 GROUP BY owner | |
| 58 -- sort :username to top | |
| 59 ORDER BY REPLACE(owner, :username, 'A'), owner''', | |
| 60 {'username': username.upper()}) | |
| 61 return curs | |
| 62 | |
| 63 class PostgresSchemaAccess(object): | |
| 64 child_type = gerald.PostgresSchema | |
| 65 current_database_time_query = 'SELECT current_time' | |
| 66 def latest_ddl_timestamp_query(self, username, connection): | |
| 67 curs = connection.cursor() | |
| 68 curs.execute("""SELECT '%s', current_time""" % username) | |
| 69 return curs | |
| 70 | |
| 71 class MySQLSchemaAccess(object): | |
| 72 child_type = gerald.MySQLSchema | |
| 73 current_database_time_query = 'SELECT sysdate FROM dual' | |
| 74 def latest_ddl_timestamp_query(self, username, connection): | |
| 75 curs = connection.cursor() | |
| 76 curs.execute("""SELECT '%s', current_time""" % username) | |
| 77 return curs | |
| 78 | |
| 387 | 79 class SchemaDict(dict): |
| 407 | 80 schema_types = {'oracle': OracleSchemaAccess, 'postgres': PostgresSchemaAccess, 'mysql': MySQLSchemaAccess} |
| 405 | 81 def __init__(self, dct, rdbms, user, connection, connection_string): |
| 387 | 82 dict.__init__(self, dct) |
| 407 | 83 self.schema_access = self.schema_types[rdbms]() |
| 387 | 84 self.user = user |
| 85 self.connection = connection | |
| 86 self.gerald_connection_string = gerald_connection_string(connection_string) | |
| 405 | 87 self.refresh_thread = RefreshGroupThread(self) |
| 396 | 88 self.complete = 0 |
|
389
53ee70e9417e
oops, one more change propagated from hg messup
catherine@DellZilla
parents:
387
diff
changeset
|
89 def refresh_asynch(self): |
|
53ee70e9417e
oops, one more change propagated from hg messup
catherine@DellZilla
parents:
387
diff
changeset
|
90 self.refresh_thread.start() |
| 407 | 91 current_database_time_sql = {gerald.OracleSchema: 'SELECT sysdate FROM dual', |
| 92 gerald.PostgresSchema: 'SELECT current_time'} | |
| 390 | 93 def get_current_database_time(self): |
| 387 | 94 curs = self.connection.cursor() |
| 407 | 95 curs.execute(self.schema_access.current_database_time_query) |
| 390 | 96 return curs.fetchone()[0] |
| 407 | 97 def refresh_times(self, target_schema): |
| 98 now = self.get_current_database_time() | |
| 99 result = [] | |
| 100 for (schema_name, schema) in self.items(): | |
| 101 if (not target_schema) or (target_schema.lower() == schema_name.lower()): | |
| 102 result.append('%s: %s (%s ago)' % (schema_name, schema.refreshed, now - schema.refreshed)) | |
| 103 result.sort() | |
| 104 return '\n'.join(result) | |
| 105 | |
| 390 | 106 def refresh(self): |
| 107 current_database_time = self.get_current_database_time() | |
| 407 | 108 curs = self.schema_access.latest_ddl_timestamp_query(self.user, self.connection) |
| 387 | 109 for (owner, last_ddl_time) in curs.fetchall(): |
| 110 if (owner not in self) or (self[owner].refreshed < last_ddl_time): | |
| 390 | 111 self.refresh_one(owner, current_database_time) |
| 387 | 112 # what if a user's last object is deleted? |
| 396 | 113 if isinstance(self.complete, int): |
| 114 self.complete += 1 | |
| 405 | 115 self.column_names = [s.column_names for s in self.values()] |
| 116 self.columns = reduce(operator.add, [s.column_names for s in self.values()]) | |
| 396 | 117 self.complete = 'all' |
| 390 | 118 def refresh_one(self, owner, current_database_time=None): |
| 407 | 119 #owner = owner.upper() |
| 120 owner = str(owner) | |
| 390 | 121 if not current_database_time: |
| 122 current_database_time = self.get_current_database_time() | |
| 407 | 123 self[owner] = self.schema_access.child_type(owner, self.gerald_connection_string) |
| 390 | 124 self[owner].refreshed = current_database_time |
| 405 | 125 build_column_list(self[owner]) |
|
394
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
126 |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
127 class PlainObject(object): |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
128 '''Simply a dumb container for attributes.''' |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
129 def __init__(self, **kwargs): |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
130 self.__dict__.update(kwargs) |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
131 def transform(self, transformation): |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
132 '''Attempts to apply a transformation function to all the |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
133 user-defined attributes; fails silently on errors''' |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
134 for (k, v) in self.__dict__.items(): |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
135 try: |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
136 self.__dict__[k] = transformation(v) |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
137 except: |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
138 pass |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
139 return self |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
140 def __repr__(self): |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
141 return '%s(%s)' % (self.__class__.__name__, |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
142 ','.join('%s=%s' % (k, |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
143 (isinstance(v, basestring) and "'%s'" % v) or v) |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
144 for (k, v) in sorted(self.__dict__.items()))) |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
145 |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
146 |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
147 class MetaData(PlainObject): |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
148 def __init__(self, object_name, schema_name, db_object): |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
149 self.object_name = object_name |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
150 self.schema_name = schema_name |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
151 self.db_object = db_object |
| 402 | 152 if isinstance(db_object, dict): |
| 153 self.db_type = db_object['db_type'] | |
| 154 elif hasattr(db_object, 'type'): | |
|
394
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
155 self.db_type = db_object.type |
|
403
dff2ac907331
genericizing _dir_indexes, _dir_constraints'
catherine@DellZilla
parents:
402
diff
changeset
|
156 else: |
|
394
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
157 self.db_type = str(type(db_object)).rstrip("'>").split('.')[-1] |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
158 def qualified_name(self): |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
159 return '%s.%s' % (self.schema_name, self.object_name) |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
160 def name(self, qualified=False): |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
161 if qualified: |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
162 return self.qualified_name() |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
163 else: |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
164 return self.object_name |
|
58e6d66794b0
refactoring _matching_database_objects to return MetaData
catherine@DellZilla
parents:
390
diff
changeset
|
165 def descriptor(self, qualified=False): |
| 396 | 166 return '%s/%s' % (self.db_type, self.name(qualified)) |
| 405 | 167 |
| 168 s = None | |
| 169 | |
| 170 if __name__ == '__main__': | |
| 171 connection_string = 'oracle://jrrt:password@orcl/?mode=0' | |
| 172 connection = None | |
| 173 dct = {} | |
| 174 user = 'jrrt' | |
| 175 rdbms = 'oracle' | |
| 176 s = SchemaDict(dct, rdbms, user, connection, connection_string, 100) | |
| 177 sch = s.child_type('jrrt',s.gerald_connection_string) | |
| 178 |