root/galaxy-central/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.6.egg/sqlalchemy/databases/information_schema.py

リビジョン 3, 7.5 KB (コミッタ: kohda, 14 年 前)

Install Unix tools  http://hannonlab.cshl.edu/galaxy_unix_tools/galaxy.html

行番号 
1"""
2information schema implementation.
3
4This module is deprecated and will not be present in this form in SQLAlchemy 0.6.
5
6"""
7from sqlalchemy import util
8
9util.warn_deprecated("the information_schema module is deprecated.")
10
11import sqlalchemy.sql as sql
12import sqlalchemy.exc as exc
13from sqlalchemy import select, MetaData, Table, Column, String, Integer
14from sqlalchemy.schema import DefaultClause, ForeignKeyConstraint
15
16ischema = MetaData()
17
18schemata = Table("schemata", ischema,
19    Column("catalog_name", String),
20    Column("schema_name", String),
21    Column("schema_owner", String),
22    schema="information_schema")
23
24tables = Table("tables", ischema,
25    Column("table_catalog", String),
26    Column("table_schema", String),
27    Column("table_name", String),
28    Column("table_type", String),
29    schema="information_schema")
30
31columns = Table("columns", ischema,
32    Column("table_schema", String),
33    Column("table_name", String),
34    Column("column_name", String),
35    Column("is_nullable", Integer),
36    Column("data_type", String),
37    Column("ordinal_position", Integer),
38    Column("character_maximum_length", Integer),
39    Column("numeric_precision", Integer),
40    Column("numeric_scale", Integer),
41    Column("column_default", Integer),
42    Column("collation_name", String),
43    schema="information_schema")
44
45constraints = Table("table_constraints", ischema,
46    Column("table_schema", String),
47    Column("table_name", String),
48    Column("constraint_name", String),
49    Column("constraint_type", String),
50    schema="information_schema")
51
52column_constraints = Table("constraint_column_usage", ischema,
53    Column("table_schema", String),
54    Column("table_name", String),
55    Column("column_name", String),
56    Column("constraint_name", String),
57    schema="information_schema")
58
59pg_key_constraints = Table("key_column_usage", ischema,
60    Column("table_schema", String),
61    Column("table_name", String),
62    Column("column_name", String),
63    Column("constraint_name", String),
64    Column("ordinal_position", Integer),
65    schema="information_schema")
66
67#mysql_key_constraints = Table("key_column_usage", ischema,
68#    Column("table_schema", String),
69#    Column("table_name", String),
70#    Column("column_name", String),
71#    Column("constraint_name", String),
72#    Column("referenced_table_schema", String),
73#    Column("referenced_table_name", String),
74#    Column("referenced_column_name", String),
75#    schema="information_schema")
76
77key_constraints = pg_key_constraints
78
79ref_constraints = Table("referential_constraints", ischema,
80    Column("constraint_catalog", String),
81    Column("constraint_schema", String),
82    Column("constraint_name", String),
83    Column("unique_constraint_catlog", String),
84    Column("unique_constraint_schema", String),
85    Column("unique_constraint_name", String),
86    Column("match_option", String),
87    Column("update_rule", String),
88    Column("delete_rule", String),
89    schema="information_schema")
90
91
92def table_names(connection, schema):
93    s = select([tables.c.table_name], tables.c.table_schema==schema)
94    return [row[0] for row in connection.execute(s)]
95
96
97def reflecttable(connection, table, include_columns, ischema_names):
98    key_constraints = pg_key_constraints
99
100    if table.schema is not None:
101        current_schema = table.schema
102    else:
103        current_schema = connection.default_schema_name()
104
105    s = select([columns],
106        sql.and_(columns.c.table_name==table.name,
107        columns.c.table_schema==current_schema),
108        order_by=[columns.c.ordinal_position])
109
110    c = connection.execute(s)
111    found_table = False
112    while True:
113        row = c.fetchone()
114        if row is None:
115            break
116        #print "row! " + repr(row)
117 #       continue
118        found_table = True
119        (name, type, nullable, charlen, numericprec, numericscale, default) = (
120            row[columns.c.column_name],
121            row[columns.c.data_type],
122            row[columns.c.is_nullable] == 'YES',
123            row[columns.c.character_maximum_length],
124            row[columns.c.numeric_precision],
125            row[columns.c.numeric_scale],
126            row[columns.c.column_default]
127            )
128        if include_columns and name not in include_columns:
129            continue
130
131        args = []
132        for a in (charlen, numericprec, numericscale):
133            if a is not None:
134                args.append(a)
135        coltype = ischema_names[type]
136        #print "coltype " + repr(coltype) + " args " +  repr(args)
137        coltype = coltype(*args)
138        colargs = []
139        if default is not None:
140            colargs.append(DefaultClause(sql.text(default)))
141        table.append_column(Column(name, coltype, nullable=nullable, *colargs))
142
143    if not found_table:
144        raise exc.NoSuchTableError(table.name)
145
146    # we are relying on the natural ordering of the constraint_column_usage table to return the referenced columns
147    # in an order that corresponds to the ordinal_position in the key_constraints table, otherwise composite foreign keys
148    # wont reflect properly.  dont see a way around this based on whats available from information_schema
149    s = select([constraints.c.constraint_name, constraints.c.constraint_type, constraints.c.table_name, key_constraints], use_labels=True, from_obj=[constraints.join(column_constraints, column_constraints.c.constraint_name==constraints.c.constraint_name).join(key_constraints, key_constraints.c.constraint_name==column_constraints.c.constraint_name)], order_by=[key_constraints.c.ordinal_position])
150    s.append_column(column_constraints)
151    s.append_whereclause(constraints.c.table_name==table.name)
152    s.append_whereclause(constraints.c.table_schema==current_schema)
153    colmap = [constraints.c.constraint_type, key_constraints.c.column_name, column_constraints.c.table_schema, column_constraints.c.table_name, column_constraints.c.column_name, constraints.c.constraint_name, key_constraints.c.ordinal_position]
154    c = connection.execute(s)
155
156    fks = {}
157    while True:
158        row = c.fetchone()
159        if row is None:
160            break
161        (type, constrained_column, referred_schema, referred_table, referred_column, constraint_name, ordinal_position) = (
162            row[colmap[0]],
163            row[colmap[1]],
164            row[colmap[2]],
165            row[colmap[3]],
166            row[colmap[4]],
167            row[colmap[5]],
168            row[colmap[6]]
169        )
170        #print "type %s on column %s to remote %s.%s.%s" % (type, constrained_column, referred_schema, referred_table, referred_column)
171        if type == 'PRIMARY KEY':
172            table.primary_key.add(table.c[constrained_column])
173        elif type == 'FOREIGN KEY':
174            try:
175                fk = fks[constraint_name]
176            except KeyError:
177                fk = ([], [])
178                fks[constraint_name] = fk
179            if current_schema == referred_schema:
180                referred_schema = table.schema
181            if referred_schema is not None:
182                Table(referred_table, table.metadata, autoload=True, schema=referred_schema, autoload_with=connection)
183                refspec = ".".join([referred_schema, referred_table, referred_column])
184            else:
185                Table(referred_table, table.metadata, autoload=True, autoload_with=connection)
186                refspec = ".".join([referred_table, referred_column])
187            if constrained_column not in fk[0]:
188                fk[0].append(constrained_column)
189            if refspec not in fk[1]:
190                fk[1].append(refspec)
191
192    for name, value in fks.iteritems():
193        table.append_constraint(ForeignKeyConstraint(value[0], value[1], name=name))
Note: リポジトリブラウザについてのヘルプは TracBrowser を参照してください。