Commits

Kirill Simonov committed c0c4f77

Added an introspector for Oracle.

Comments (0)

Files changed (7)

src/htsql_mssql/domain.py

 
     def __eq__(self, other):
         # The generic domain comparison checks if the types of the domains
-        # and all their attributes are equal.  Since MySQL domains grow
-        # an extra attribute `name`, we need to update the comparison
-        # implementation.
+        # and all their attributes are equal.  Since we added extra attributes,
+        # we need to update the implementation.
         return (super(MSSQLDomain, self).__eq__(other) and
                 self.schema_name == other.schema_name and
                 self.name == other.name)

src/htsql_oracle/connect.py

 from htsql.connect import Connect, Normalize, DBError
 from htsql.adapter import adapts
 from htsql.context import context
-from htsql.domain import BooleanDomain, StringDomain, DateDomain
+from htsql.domain import BooleanDomain, DecimalDomain, StringDomain, DateDomain
+import datetime
+import decimal
 import cx_Oracle
 
 
         return (value != 0)
 
 
+class NormalizeOracleDecimal(Normalize):
+
+    adapts(DecimalDomain)
+
+    def __call__(self, value):
+        if value is None:
+            return None
+        return decimal.Decimal(value)
+
+
 class NormalizeOracleString(Normalize):
 
     adapts(StringDomain)

src/htsql_oracle/domain.py

+#
+# Copyright (c) 2006-2011, Prometheus Research, LLC
+# Authors: Clark C. Evans <cce@clarkevans.com>,
+#          Kirill Simonov <xi@resolvent.net>
+#
+
+
+"""
+:mod:`htsql_oracle.domain`
+==========================
+
+This module defines Oracle-specific data types.
+"""
+
+
+from htsql.domain import (Domain, BooleanDomain, IntegerDomain, DecimalDomain,
+                          FloatDomain, StringDomain, DateDomain, OpaqueDomain)
+
+
+class OracleDomain(Domain):
+    """
+    Represents an Oracle data type.
+
+    This is an abstract mixin class; see subclasses for concrete data types.
+
+    `name` (a string)
+        The name of the type.
+    """
+
+    def __init__(self, name, **attributes):
+        # Sanity check on the arguments.
+        assert isinstance(name, str)
+
+        # Pass the attributes to the concrete domain constructor.
+        super(OracleDomain, self).__init__(**attributes)
+        self.name = name
+
+    def __str__(self):
+        return self.name
+
+    def __eq__(self, other):
+        # The generic domain comparison checks if the types of the domains
+        # and all their attributes are equal.  Since Oracle domains grow
+        # an extra attribute `name`, we need to update the comparison
+        # implementation.
+        return (super(OracleDomain, self).__eq__(other) and
+                self.name == other.name)
+
+
+class OracleBooleanDomain(OracleDomain, BooleanDomain):
+    """
+    Represents a pseudo-Boolean type for Oracle.
+
+    In Oracle, we express Boolean values as integers; ``0`` is the FALSE
+    value, any non-zero integer is a TRUE value.
+    """
+
+
+class OracleIntegerDomain(OracleDomain, IntegerDomain):
+    """
+    Represents the Oracle ``INTEGER`` (actually, ``NUMBER(38)``) data type.
+    """
+
+
+class OracleDecimalDomain(OracleDomain, DecimalDomain):
+    """
+    Represents an Oracle ``NUMBER`` data type.
+    """
+
+
+class OracleFloatDomain(OracleDomain, FloatDomain):
+    """
+    Represents Oracle ``BINARY_FLOAT`` and ``BINARY_DOUBLE`` data types.
+    """
+
+
+class OracleStringDomain(OracleDomain, StringDomain):
+    """
+    Represents Oracle ``CHAR``, ``NCHAR``, ``VARCHAR2``, ``NVARCHAR2``,
+    ``CLOB``, ``NCLOB``, and ``LONG`` data types.
+    """
+
+
+class OracleDateDomain(OracleDomain, DateDomain):
+    """
+    Represents an Oracle ``DATE`` data type.
+    """
+
+
+class OracleOpaqueDomain(OracleDomain, OpaqueDomain):
+    """
+    Represents an unsupported Oracle data type.
+    """
+
+

src/htsql_oracle/introspect.py

+#
+# Copyright (c) 2006-2011, Prometheus Research, LLC
+# Authors: Clark C. Evans <cce@clarkevans.com>,
+#          Kirill Simonov <xi@resolvent.net>
+#
+
+
+"""
+:mod:`htsql_oracle.introspect`
+==============================
+
+This module implements the introspection adapter for Oracle.
+"""
+
+
+from htsql.introspect import Introspect
+from htsql.entity import (CatalogEntity, SchemaEntity, TableEntity,
+                          ColumnEntity, UniqueKeyEntity, PrimaryKeyEntity,
+                          ForeignKeyEntity)
+from .domain import (OracleBooleanDomain, OracleIntegerDomain,
+                     OracleDecimalDomain, OracleFloatDomain,
+                     OracleStringDomain, OracleDateDomain,
+                     OracleOpaqueDomain)
+from htsql.connect import Connect
+from htsql.util import Record
+import re
+
+
+class Meta(object):
+    """
+    Loads raw meta-data from `information_schema`.
+    """
+
+    def __init__(self):
+        connect = Connect()
+        connection = connect()
+        cursor = connection.cursor()
+        self.users = self.fetch(cursor, 'all_users', ['username'])
+        self.tables = self.fetch(cursor, 'all_catalog',
+                                 ['owner', 'table_name'])
+        self.columns = self.fetch(cursor, 'all_tab_columns',
+                                  ['owner', 'table_name', 'column_id'])
+        self.constraints = self.fetch(cursor, 'all_constraints',
+                                      ['owner', 'constraint_name'])
+        self.key_columns = self.fetch(cursor, 'all_cons_columns',
+                                      ['owner', 'constraint_name',
+                                       'position'])
+        self.tables_by_user = self.group(self.tables, self.users,
+                                         ['owner'])
+        self.columns_by_table = self.group(self.columns, self.tables,
+                                           ['owner', 'table_name'])
+        self.constraints_by_table = self.group(self.constraints, self.tables,
+                                               ['owner', 'table_name'])
+        self.key_columns_by_constraint = self.group(self.key_columns,
+                        self.constraints, ['owner', 'constraint_name'])
+
+    def fetch(self, cursor, table_name, id_names):
+        rows = {}
+        cursor.execute("SELECT * FROM %s" % table_name)
+        for items in cursor.fetchall():
+            attributes = {}
+            for kind, item in zip(cursor.description, items):
+                name = kind[0].lower()
+                if isinstance(item, unicode):
+                    item = item.encode('utf-8')
+                attributes[name] = item
+            key = tuple(attributes[name] for name in id_names)
+            record = Record(**attributes)
+            rows[key] = record
+        return rows
+
+    def group(self, targets, bases, id_names):
+        groups = {}
+        if not targets or not bases:
+            return groups
+        for key in bases:
+            groups[key] = []
+        for key in sorted(targets):
+            record = targets[key]
+            base_key = tuple(getattr(record, name) for name in id_names)
+            if base_key not in groups:
+                continue
+            groups[base_key].append(key)
+        return groups
+
+
+class IntrospectOracle(Introspect):
+    """
+    Implements the introspection adapter for Oracle.
+    """
+
+    def __init__(self):
+        super(IntrospectOracle, self).__init__()
+        self.meta = Meta()
+
+    def __call__(self):
+        return self.introspect_catalog()
+
+    def introspect_catalog(self):
+        schemas = self.introspect_schemas()
+        return CatalogEntity(schemas)
+
+    def permit_schema(self, schema_name):
+        if schema_name in ['SYS', 'SYSTEM', 'OUTLN', 'DIP', 'TSMSYS', 'DBSNMP',
+                           'CTXSYS', 'XDB', 'ANONYMOUS', 'MDSYS', 'HR',
+                           'FLOWS_FILES', 'FLOWS_020100']:
+            return False
+        return True
+
+    def permit_table(self, schema_name, table_name):
+        return True
+
+    def permit_column(self, schema_name, table_name, column_name):
+        return True
+
+    def introspect_schemas(self):
+        schemas = []
+        for key in sorted(self.meta.users):
+            record = self.meta.users[key]
+            name = record.username
+            if not self.permit_schema(name):
+                continue
+            tables = self.introspect_tables(key)
+            schema = SchemaEntity(name, tables)
+            schemas.append(schema)
+        schemas.sort(key=(lambda s: s.name))
+        return schemas
+
+    def introspect_tables(self, schema_key):
+        tables = []
+        for key in self.meta.tables_by_user[schema_key]:
+            record = self.meta.tables[key]
+            if record.table_type not in ['TABLE', 'VIEW']:
+                continue
+            schema_name = record.owner
+            name = record.table_name
+            if not self.permit_table(schema_name, name):
+                continue
+            columns = self.introspect_columns(key)
+            unique_keys = self.introspect_unique_keys(key)
+            foreign_keys = self.introspect_foreign_keys(key)
+            table = TableEntity(schema_name, name,
+                                columns, unique_keys, foreign_keys)
+            tables.append(table)
+        tables.sort(key=(lambda t: t.name))
+        return tables
+
+    def introspect_columns(self, table_key):
+        columns = []
+        for key in self.meta.columns_by_table[table_key]:
+            record = self.meta.columns[key]
+            schema_name = record.owner
+            table_name = record.table_name
+            name = record.column_name
+            if not self.permit_column(schema_name, table_name, name):
+                continue
+            domain = self.introspect_domain(key)
+            is_nullable = (record.nullable == 'Y')
+            has_default = (record.data_default is not None)
+            column = ColumnEntity(schema_name, table_name, name, domain,
+                                  is_nullable, has_default)
+            columns.append(column)
+        return columns
+
+    def introspect_unique_keys(self, table_key):
+        unique_keys = []
+        for key in self.meta.constraints_by_table[table_key]:
+            record = self.meta.constraints[key]
+            if record.constraint_type not in ['P', 'U']:
+                continue
+            if record.status != 'ENABLED' or record.validated != 'VALIDATED':
+                continue
+            schema_name = record.owner
+            table_name = record.table_name
+            column_names = []
+            for column_key in self.meta.key_columns_by_constraint[key]:
+                column_record = self.meta.key_columns[column_key]
+                column_names.append(column_record.column_name)
+            if not all(self.permit_column(schema_name, table_name, column_name)
+                       for column_name in column_names):
+                continue
+            if record.constraint_type == 'P':
+                unique_key = PrimaryKeyEntity(schema_name, table_name,
+                                              column_names)
+            else:
+                unique_key = UniqueKeyEntity(schema_name, table_name,
+                                             column_names)
+            unique_keys.append(unique_key)
+        return unique_keys
+
+    def introspect_foreign_keys(self, table_key):
+        foreign_keys = []
+        for key in self.meta.constraints_by_table[table_key]:
+            record = self.meta.constraints[key]
+            if record.constraint_type != 'R':
+                continue
+            if record.status != 'ENABLED' or record.validated != 'VALIDATED':
+                continue
+            target_key = (record.r_owner, record.r_constraint_name)
+            target_record = self.meta.constraints[target_key]
+            schema_name = record.owner
+            target_schema_name = target_record.owner
+            table_name = record.table_name
+            target_table_name = target_record.table_name
+            column_names = []
+            target_column_names = []
+            for column_key in self.meta.key_columns_by_constraint[key]:
+                column_record = self.meta.key_columns[column_key]
+                column_names.append(column_record.column_name)
+            for column_key in self.meta.key_columns_by_constraint[target_key]:
+                column_record = self.meta.key_columns[column_key]
+                target_column_names.append(column_record.column_name)
+            if not self.permit_schema(target_schema_name):
+                continue
+            if not self.permit_table(target_schema_name, target_table_name):
+                continue
+            if not all(self.permit_column(schema_name, table_name, column_name)
+                       for column_name in column_names):
+                continue
+            if not all(self.permit_column(target_schema_name,
+                                          target_table_name,
+                                          target_column_name)
+                       for target_column_name in target_column_names):
+                continue
+            foreign_key = ForeignKeyEntity(schema_name, table_name,
+                                           column_names,
+                                           target_schema_name,
+                                           target_table_name,
+                                           target_column_names)
+            foreign_keys.append(foreign_key)
+        return foreign_keys
+
+    boolean_pattern = r"""
+        ^ %s \s+ IN \s+ \( (?: 0 \s* , \s* 1 | 1 \s* , \s* 0 ) \) $
+    """
+
+    def introspect_domain(self, key):
+        record = self.meta.columns[key]
+        table_key = (record.owner, record.table_name)
+        data_type = record.data_type
+        data_length = record.data_length
+        data_precision = record.data_precision
+        data_scale = record.data_scale
+        if data_type in ['CHAR', 'NCHAR']:
+            return OracleStringDomain(data_type,
+                                      length=data_length,
+                                      is_varying=False)
+        elif data_type in ['VARCHAR2', 'NVARCHAR2', 'CLOB', 'NCLOB', 'LONG']:
+            return OracleStringDomain(data_type,
+                                      length=data_length,
+                                      is_varying=True)
+        elif data_type == 'NUMBER':
+            if (data_precision, data_scale) == (1, 0):
+                for constraint_key in self.meta.constraints_by_table[table_key]:
+                    constraint_record = self.meta.constraints[constraint_key]
+                    if (constraint_record.constraint_type == 'C' and
+                        re.match(self.boolean_pattern
+                                 % re.escape(record.column_name),
+                                 constraint_record.search_condition,
+                                 re.X|re.I)):
+                        return OracleBooleanDomain(data_type)
+            if (data_precision, data_scale) == (38, 0):
+                return OracleIntegerDomain(data_type)
+            return OracleDecimalDomain(data_type,
+                                       precision=data_precision,
+                                       scale=data_scale)
+        elif data_type in ['BINARY_FLOAT', 'BINARY_DOUBLE']:
+            return OracleFloatDomain(data_type)
+        elif data_type == 'DATE':
+            return OracleDateDomain(data_type)
+        return OracleOpaqueDomain(data_type)
+
+

src/htsql_oracle/tr/__init__.py

+#
+# Copyright (c) 2006-2011, Prometheus Research, LLC
+# Authors: Clark C. Evans <cce@clarkevans.com>,
+#          Kirill Simonov <xi@resolvent.net>
+#
+
+
+"""
+:mod:`htsql_oracle.tr`
+======================
+
+This package adapts the HTSQL-to-SQL translator for Oracle.
+"""
+
+

src/htsql_oracle/tr/dump.py

+#
+# Copyright (c) 2006-2011, Prometheus Research, LLC
+# Authors: Clark C. Evans <cce@clarkevans.com>,
+#          Kirill Simonov <xi@resolvent.net>
+#
+
+
+"""
+:mod:`htsql_oracle.tr.dump`
+===========================
+
+This module adapts the SQL serializer for Oracle.
+"""
+
+
+from htsql.tr.dump import DumpAnchor, DumpLeadingAnchor
+
+
+class OracleDumpLeadingAnchor(DumpLeadingAnchor):
+
+    def __call__(self):
+        alias = self.state.frame_alias_by_tag[self.clause.frame.tag]
+        self.state.push_hook(with_aliases=True)
+        self.format("{frame} {alias:name}",
+                    frame=self.clause.frame, alias=alias)
+        self.state.pop_hook()
+
+
+class OracleDumpAnchor(DumpAnchor):
+
+    def __call__(self):
+        alias = self.state.frame_alias_by_tag[self.clause.frame.tag]
+        self.newline()
+        if self.clause.is_cross:
+            self.write("CROSS JOIN ")
+        elif self.clause.is_inner:
+            self.write("INNER JOIN ")
+        elif self.clause.is_left and not self.clause.is_right:
+            self.write("LEFT OUTER JOIN ")
+        elif self.clause.is_right and not self.clause.is_left:
+            self.write("RIGHT OUTER JOIN ")
+        else:
+            self.write("FULL OUTER JOIN ")
+        self.indent()
+        self.state.push_hook(with_aliases=True)
+        self.format("{frame} {alias:name}",
+                    frame=self.clause.frame, alias=alias)
+        self.state.pop_hook()
+        if self.clause.condition is not None:
+            self.newline()
+            self.format("ON {condition}",
+                        condition=self.clause.condition)
+        self.dedent()
+
+

src/htsql_oracle/tr/lookup.py

+#
+# Copyright (c) 2006-2011, Prometheus Research, LLC
+# Authors: Clark C. Evans <cce@clarkevans.com>,
+#          Kirill Simonov <xi@resolvent.net>
+#
+
+
+from htsql.tr.lookup import ItemizeTable
+from htsql.tr.syntax import IdentifierSyntax
+from htsql.tr.binding import ColumnBinding
+
+
+class OracleItemizeTable(ItemizeTable):
+
+    def itemize_columns(self):
+        for column in self.binding.table.columns:
+            name = column.name
+            if name.isupper():
+                name = name.lower()
+            identifier = IdentifierSyntax(name, self.syntax.mark)
+            link = self.find_link(column, identifier)
+            yield ColumnBinding(self.binding, column, link, identifier)
+
+
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.