codekingpro/portable-devtools

1# dialects/mssql/information_schema.py
2# Copyright (C) 2005-2026 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7# mypy: ignore-errors
8
9from ... import cast
10from ... import Column
11from ... import MetaData
12from ... import Table
13from ...ext.compiler import compiles
14from ...sql import expression
15from ...types import Boolean
16from ...types import Integer
17from ...types import Numeric
18from ...types import NVARCHAR
19from ...types import String
20from ...types import TypeDecorator
21from ...types import Unicode
22
23
24ischema = MetaData()
25
26
27class CoerceUnicode(TypeDecorator):
28    impl = Unicode
29    cache_ok = True
30
31    def bind_expression(self, bindvalue):
32        return _cast_on_2005(bindvalue)
33
34
35class _cast_on_2005(expression.ColumnElement):
36    def __init__(self, bindvalue):
37        self.bindvalue = bindvalue
38
39
40@compiles(_cast_on_2005)
41def _compile(element, compiler, **kw):
42    from . import base
43
44    if (
45        compiler.dialect.server_version_info is None
46        or compiler.dialect.server_version_info < base.MS_2005_VERSION
47    ):
48        return compiler.process(element.bindvalue, **kw)
49    else:
50        return compiler.process(cast(element.bindvalue, Unicode), **kw)
51
52
53schemata = Table(
54    "SCHEMATA",
55    ischema,
56    Column("CATALOG_NAME", CoerceUnicode, key="catalog_name"),
57    Column("SCHEMA_NAME", CoerceUnicode, key="schema_name"),
58    Column("SCHEMA_OWNER", CoerceUnicode, key="schema_owner"),
59    schema="INFORMATION_SCHEMA",
60)
61
62tables = Table(
63    "TABLES",
64    ischema,
65    Column("TABLE_CATALOG", CoerceUnicode, key="table_catalog"),
66    Column("TABLE_SCHEMA", CoerceUnicode, key="table_schema"),
67    Column("TABLE_NAME", CoerceUnicode, key="table_name"),
68    Column("TABLE_TYPE", CoerceUnicode, key="table_type"),
69    schema="INFORMATION_SCHEMA",
70)
71
72columns = Table(
73    "COLUMNS",
74    ischema,
75    Column("TABLE_SCHEMA", CoerceUnicode, key="table_schema"),
76    Column("TABLE_NAME", CoerceUnicode, key="table_name"),
77    Column("COLUMN_NAME", CoerceUnicode, key="column_name"),
78    Column("IS_NULLABLE", Integer, key="is_nullable"),
79    Column("DATA_TYPE", String, key="data_type"),
80    Column("ORDINAL_POSITION", Integer, key="ordinal_position"),
81    Column(
82        "CHARACTER_MAXIMUM_LENGTH", Integer, key="character_maximum_length"
83    ),
84    Column("NUMERIC_PRECISION", Integer, key="numeric_precision"),
85    Column("NUMERIC_SCALE", Integer, key="numeric_scale"),
86    Column("COLUMN_DEFAULT", Integer, key="column_default"),
87    Column("COLLATION_NAME", String, key="collation_name"),
88    schema="INFORMATION_SCHEMA",
89)
90
91sys_columns = Table(
92    "columns",
93    ischema,
94    Column("object_id", Integer),
95    Column("name", CoerceUnicode),
96    Column("column_id", Integer),
97    Column("default_object_id", Integer),
98    Column("user_type_id", Integer),
99    Column("is_nullable", Integer),
100    Column("ordinal_position", Integer),
101    Column("max_length", Integer),
102    Column("precision", Integer),
103    Column("scale", Integer),
104    Column("collation_name", String),
105    schema="sys",
106)
107
108sys_types = Table(
109    "types",
110    ischema,
111    Column("name", CoerceUnicode, key="name"),
112    Column("system_type_id", Integer, key="system_type_id"),
113    Column("user_type_id", Integer, key="user_type_id"),
114    Column("schema_id", Integer, key="schema_id"),
115    Column("max_length", Integer, key="max_length"),
116    Column("precision", Integer, key="precision"),
117    Column("scale", Integer, key="scale"),
118    Column("collation_name", CoerceUnicode, key="collation_name"),
119    Column("is_nullable", Boolean, key="is_nullable"),
120    Column("is_user_defined", Boolean, key="is_user_defined"),
121    Column("is_assembly_type", Boolean, key="is_assembly_type"),
122    Column("default_object_id", Integer, key="default_object_id"),
123    Column("rule_object_id", Integer, key="rule_object_id"),
124    Column("is_table_type", Boolean, key="is_table_type"),
125    schema="sys",
126)
127
128constraints = Table(
129    "TABLE_CONSTRAINTS",
130    ischema,
131    Column("TABLE_SCHEMA", CoerceUnicode, key="table_schema"),
132    Column("TABLE_NAME", CoerceUnicode, key="table_name"),
133    Column("CONSTRAINT_NAME", CoerceUnicode, key="constraint_name"),
134    Column("CONSTRAINT_TYPE", CoerceUnicode, key="constraint_type"),
135    schema="INFORMATION_SCHEMA",
136)
137
138sys_default_constraints = Table(
139    "default_constraints",
140    ischema,
141    Column("object_id", Integer),
142    Column("name", CoerceUnicode),
143    Column("schema_id", Integer),
144    Column("parent_column_id", Integer),
145    Column("definition", CoerceUnicode),
146    schema="sys",
147)
148
149column_constraints = Table(
150    "CONSTRAINT_COLUMN_USAGE",
151    ischema,
152    Column("TABLE_SCHEMA", CoerceUnicode, key="table_schema"),
153    Column("TABLE_NAME", CoerceUnicode, key="table_name"),
154    Column("COLUMN_NAME", CoerceUnicode, key="column_name"),
155    Column("CONSTRAINT_NAME", CoerceUnicode, key="constraint_name"),
156    schema="INFORMATION_SCHEMA",
157)
158
159key_constraints = Table(
160    "KEY_COLUMN_USAGE",
161    ischema,
162    Column("TABLE_SCHEMA", CoerceUnicode, key="table_schema"),
163    Column("TABLE_NAME", CoerceUnicode, key="table_name"),
164    Column("COLUMN_NAME", CoerceUnicode, key="column_name"),
165    Column("CONSTRAINT_NAME", CoerceUnicode, key="constraint_name"),
166    Column("CONSTRAINT_SCHEMA", CoerceUnicode, key="constraint_schema"),
167    Column("ORDINAL_POSITION", Integer, key="ordinal_position"),
168    schema="INFORMATION_SCHEMA",
169)
170
171ref_constraints = Table(
172    "REFERENTIAL_CONSTRAINTS",
173    ischema,
174    Column("CONSTRAINT_CATALOG", CoerceUnicode, key="constraint_catalog"),
175    Column("CONSTRAINT_SCHEMA", CoerceUnicode, key="constraint_schema"),
176    Column("CONSTRAINT_NAME", CoerceUnicode, key="constraint_name"),
177    # TODO: is CATLOG misspelled ?
178    Column(
179        "UNIQUE_CONSTRAINT_CATLOG",
180        CoerceUnicode,
181        key="unique_constraint_catalog",
182    ),
183    Column(
184        "UNIQUE_CONSTRAINT_SCHEMA",
185        CoerceUnicode,
186        key="unique_constraint_schema",
187    ),
188    Column(
189        "UNIQUE_CONSTRAINT_NAME", CoerceUnicode, key="unique_constraint_name"
190    ),
191    Column("MATCH_OPTION", String, key="match_option"),
192    Column("UPDATE_RULE", String, key="update_rule"),
193    Column("DELETE_RULE", String, key="delete_rule"),
194    schema="INFORMATION_SCHEMA",
195)
196
197views = Table(
198    "VIEWS",
199    ischema,
200    Column("TABLE_CATALOG", CoerceUnicode, key="table_catalog"),
201    Column("TABLE_SCHEMA", CoerceUnicode, key="table_schema"),
202    Column("TABLE_NAME", CoerceUnicode, key="table_name"),
203    Column("VIEW_DEFINITION", CoerceUnicode, key="view_definition"),
204    Column("CHECK_OPTION", String, key="check_option"),
205    Column("IS_UPDATABLE", String, key="is_updatable"),
206    schema="INFORMATION_SCHEMA",
207)
208
209computed_columns = Table(
210    "computed_columns",
211    ischema,
212    Column("object_id", Integer),
213    Column("name", CoerceUnicode),
214    Column("column_id", Integer),
215    Column("is_computed", Boolean),
216    Column("is_persisted", Boolean),
217    Column("definition", CoerceUnicode),
218    schema="sys",
219)
220
221sequences = Table(
222    "SEQUENCES",
223    ischema,
224    Column("SEQUENCE_CATALOG", CoerceUnicode, key="sequence_catalog"),
225    Column("SEQUENCE_SCHEMA", CoerceUnicode, key="sequence_schema"),
226    Column("SEQUENCE_NAME", CoerceUnicode, key="sequence_name"),
227    schema="INFORMATION_SCHEMA",
228)
229
230
231class NumericSqlVariant(TypeDecorator):
232    r"""This type casts sql_variant columns in the identity_columns view
233    to numeric. This is required because:
234
235    * pyodbc does not support sql_variant
236    * pymssql under python 2 return the byte representation of the number,
237      int 1 is returned as "\x01\x00\x00\x00". On python 3 it returns the
238      correct value as string.
239    """
240
241    impl = Unicode
242    cache_ok = True
243
244    def column_expression(self, colexpr):
245        return cast(colexpr, Numeric(38, 0))
246
247
248identity_columns = Table(
249    "identity_columns",
250    ischema,
251    Column("object_id", Integer),
252    Column("name", CoerceUnicode),
253    Column("column_id", Integer),
254    Column("is_identity", Boolean),
255    Column("seed_value", NumericSqlVariant),
256    Column("increment_value", NumericSqlVariant),
257    Column("last_value", NumericSqlVariant),
258    Column("is_not_for_replication", Boolean),
259    schema="sys",
260)
261
262
263class NVarcharSqlVariant(TypeDecorator):
264    """This type casts sql_variant columns in the extended_properties view
265    to nvarchar. This is required because pyodbc does not support sql_variant
266    """
267
268    impl = Unicode
269    cache_ok = True
270
271    def column_expression(self, colexpr):
272        return cast(colexpr, NVARCHAR)
273
274
275extended_properties = Table(
276    "extended_properties",
277    ischema,
278    Column("class", Integer),  # TINYINT
279    Column("class_desc", CoerceUnicode),
280    Column("major_id", Integer),
281    Column("minor_id", Integer),
282    Column("name", CoerceUnicode),
283    Column("value", NVarcharSqlVariant),
284    schema="sys",
285)
286