Commits

Kirill Simonov committed 016293f

htsql-ctl shell: improved describe command.

Comments (0)

Files changed (6)

src/htsql/core/entity.py

 
     def __unicode__(self):
         # Generate a string of the form:
-        #   schema.table -> schema.table
-        return u"%s -> %s" % (self.origin, self.target)
+        #   schema.table(column,...) -> schema.table(column,...)
+        return u"%s(%s) -> %s(%s)" % \
+                (self.origin,
+                 u",".join(column.name for column in self.origin_columns),
+                 self.target,
+                 u",".join(column.name for column in self.target_columns))
 
     def __str__(self):
         return unicode(self).encode('utf-8')

src/htsql/core/util.py

                 raise ValueError("options must be a dictionary with"
                                  " string keys and values; got %r" % options)
 
+        # Permit capitalized engine name.
+        engine = engine.lower()
+
         # We are done, produce an instance.
         return cls(engine, database, username, password, host, port, options)
 

src/htsql/ctl/shell.py

 
 from .error import ScriptError
 from .request import Request, DBRoutine
-from ..core.util import listof, trim_doc
+from ..core.util import listof, trim_doc, to_name
 from ..core.model import (HomeNode, InvalidNode, InvalidArc, TableArc,
-        ColumnArc, ChainArc, AmbiguousArc)
-from ..core.classify import classify, normalize
+        ColumnArc, ChainArc, SyntaxArc, AmbiguousArc)
+from ..core.classify import classify, normalize, relabel, localize
+from ..core.entity import UniqueKeyEntity, ForeignKeyEntity
 import traceback
 import StringIO
 import mimetypes
     """
 
     name = 'describe'
-    signature = """describe [table]"""
-    hint = """list tables, or slots for a given table"""
+    signature = """describe [name]"""
+    hint = """describe a database entity"""
     help = """
-    Type `describe` to list all tables or `describe <table>` to list
-    all columns and links for a given table.
+    Type `describe` to list the content of the database.
+
+    Type `describe <table>` to describe a table and list table
+    attributes.
+
+    Typ `describe <table>.<column>` or `describe <table>.<link>`
+    to describe a table attribute.
     """
 
     @classmethod
     def complete(cls, routine, argument):
-        if argument:
-            return None
+        path = [name.strip().lower() for name in argument.split('.')]
+        if path:
+            if path[-1]:
+                return None
+            path.pop()
+        node = HomeNode()
         with routine.state.app:
-             return [label.name for label in classify(HomeNode())]
+            labels = [label for label in classify(node)
+                      if label.arity is None and
+                         not isinstance(label.arc, InvalidArc)]
+        for name in path:
+            node_by_name = dict((label.name.encode('utf-8'), label.target)
+                                for label in labels)
+            if name not in node_by_name:
+                return None
+            node = node_by_name[name]
+            with routine.state.app:
+                labels = [label for label in classify(node)
+                          if label.arity is None and
+                             not isinstance(label.arc, InvalidArc)]
+        return [label.name.encode('utf-8') for label in labels]
 
     def execute(self):
+        path = []
+        if self.argument:
+            path = [name.strip() for name in self.argument.split('.')]
+        arc = None
+        for name in path:
+            if to_name(name).encode('utf-8') != name.lower():
+                self.ctl.out("** invalid identifier %r" % name)
+                return
+            if arc is None:
+                node = HomeNode()
+            else:
+                node = arc.target
+            with self.state.app:
+                labels = [label for label in classify(node)
+                          if label.arity is None and
+                             not isinstance(label.arc, InvalidArc)]
+            arc_by_name = dict((label.name.encode('utf-8'), label.arc)
+                                for label in labels)
+            if name.lower() not in arc_by_name:
+                self.ctl.out("** unknown identifier %r" % name)
+                return
+            arc = arc_by_name[name.lower()]
+
+        if arc is None:
+            node = HomeNode()
+        else:
+            node = arc.target
         with self.state.app:
-             root_labels = classify(HomeNode())
+            labels = [label for label in classify(node)
+                      if not isinstance(label.arc, InvalidArc)]
 
-        if not self.argument:
-            #
-            # Enumerate introspected tables if one isn't provided
-            #
-            if not(root_labels):
-                 self.ctl.out("No tables introspected or configured.")
-                 return
-            self.ctl.out("Tables introspected for this database are:")
-            for label in root_labels:
-                if not isinstance(label.arc, TableArc):
-                     continue
-                if label.name == label.arc.target.table.name:
-                    self.ctl.out("\t%s" % label.name)
+        if arc is None:
+            db = self.state.app.htsql.db
+            sanitized_db = self.state.app.htsql.db.clone(
+                    engine=db.engine.upper(), password=None)
+            name = str(sanitized_db)
+            kind = "HTSQL database"
+        else:
+            name = ".".join(to_name(name) for name in path).upper()
+            kind = self.get_arc_kind(arc)
+        self.ctl.out("%s - %s" % (name, kind))
+        for line in self.get_arc_description(arc):
+            self.ctl.out(line)
+        if labels:
+            self.ctl.out()
+            self.ctl.out("Labels:")
+            for label in labels:
+                signature = self.get_label_signature(label)
+                hint = self.get_arc_kind(label.arc)
+                self.ctl.out("  ", end="")
+                if hint is not None:
+                    self.ctl.out("%-24s : %s" % (signature, hint))
                 else:
-                    self.ctl.out("\t%s (%s)" % (label.name,
-                                                label.arc.target.table.name))
-            self.ctl.out()
-            return
-        #
-        # Dump table attributes if a specific table is requested.
-        #
-        slots = None
-        with self.state.app:
-            for label in root_labels:
-                if label.name == self.argument:
-                    slots = classify(label.arc.target)
-        if slots is None:
-            self.ctl.out("Unable to find table: %s" % self.argument)
-            self.ctl.out()
-            return
+                    self.ctl.out(signature)
+        self.ctl.out()
 
-        max_width = 0
-        for slot in slots:
-            if isinstance(slot.arc, AmbiguousArc):
-                continue
-            if len(slot.name) > max_width:
-                max_width = len(slot.name)
-        if not max_width:
-            self.ctl.out("Table `%s` has no slots." % self.argument)
-            self.ctl.out()
-            return
+    def get_arc_kind(self, arc):
+        if isinstance(arc, TableArc):
+            return "table"
+        elif isinstance(arc, ColumnArc):
+            return "%s column" % arc.column.domain
+        elif isinstance(arc, ChainArc):
+            with self.state.app:
+                target_labels = relabel(TableArc(arc.target.table))
+            target_name = None
+            if target_labels:
+                target_name = target_labels[0].name.encode('utf-8')
+            if arc.is_contracting:
+                kind = "link"
+            else:
+                kind = "plural link"
+            if target_name:
+                kind = "%s to %s" % (kind, target_name)
+            return kind
+        elif isinstance(arc, SyntaxArc):
+            return "calculated attribute"
+        else:
+            return "attribute"
 
-        self.ctl.out("Slots for `%s` are:" % self.argument)
-        for slot in slots:
-            name = slot.name.ljust(max_width)
-            post = str(slot.arc.target)
-            if isinstance(slot.arc, ChainArc):
-                if slot.arc.is_contracting:
-                    post = "SINGULAR(%s)" % post
+    def get_arc_description(self, arc):
+        if isinstance(arc, TableArc):
+            table = arc.table
+            yield ""
+            yield "SQL name:"
+            yield "  %s" % table
+            if table.unique_keys:
+                yield ""
+                yield "Unique keys:"
+                for unique_key in table.unique_keys:
+                    yield "  %s" % self.get_key_description(unique_key)
+            if table.foreign_keys or table.referring_foreign_keys:
+                yield ""
+                yield "Foreign keys:"
+                for foreign_key in table.foreign_keys:
+                    yield "  %s" % self.get_key_description(foreign_key)
+                for foreign_key in table.referring_foreign_keys:
+                    if foreign_key.origin is table:
+                        continue
+                    yield "  %s" % self.get_key_description(foreign_key)
+            with self.state.app:
+                identity = localize(arc.target)
+            if identity:
+                identity_names = []
+                for identity_arc in identity:
+                    with self.state.app:
+                        identity_labels = relabel(identity_arc)
+                    if not identity_labels:
+                        break
+                    identity_names.append(
+                            identity_labels[0].name.encode('utf-8'))
                 else:
-                    post = "PLURAL(%s)" % post
-            if isinstance(slot.arc, AmbiguousArc):
-                continue
-            self.ctl.out("\t %s %s" % (name, post))
-        self.ctl.out()
+                    yield ""
+                    yield "Identity:"
+                    yield "  %s" % ", ".join(identity_names)
+        elif isinstance(arc, ColumnArc):
+            column = arc.column
+            yield ""
+            yield "SQL name:"
+            yield "  %s" % column
+            yield ""
+            yield "Domain:"
+            yield "  %s" % column.domain
+            yield ""
+            yield "Nullable?"
+            if column.is_nullable:
+                yield "  yes"
+            else:
+                yield "  no"
+            if column.unique_keys:
+                yield ""
+                yield "Unique keys:"
+                for unique_key in column.unique_keys:
+                    yield "  %s" % self.get_key_description(unique_key)
+            if column.foreign_keys or column.referring_foreign_keys:
+                yield ""
+                yield "Foreign keys:"
+                for foreign_key in column.foreign_keys:
+                    yield "  %s" % self.get_key_description(foreign_key)
+                for foreign_key in column.referring_foreign_keys:
+                    if column in foreign_key.origin_columns:
+                        continue
+                    yield "  %s" % self.get_key_description(foreign_key)
+            if arc.link is not None and not isinstance(arc.link, InvalidArc):
+                yield ""
+                yield "Link:"
+                yield "  %s" % self.get_arc_kind(arc.link)
+        elif isinstance(arc, ChainArc):
+            yield ""
+            yield "Joins:"
+            for join in arc.joins:
+                yield "  %s" % join
+        elif isinstance(arc, SyntaxArc):
+            yield ""
+            yield "Definition:"
+            yield "  %s" % arc.syntax
+
+    def get_key_description(self, key):
+        description = str(key)
+        flags = []
+        if isinstance(key, UniqueKeyEntity):
+            if key.is_primary:
+                flags.append("primary")
+            if any(column.is_nullable for column in key.origin_columns):
+                flags.append("nullable")
+            if key.is_partial:
+                flags.append("partial")
+        elif isinstance(key, ForeignKeyEntity):
+            if any(column.is_nullable for column in key.origin_columns):
+                flags.append("nullable")
+            if key.is_partial:
+                flags.append("partial")
+        if flags:
+            return "%s {%s}" % (description, ", ".join(flags))
+        else:
+            return description
+
+    def get_label_signature(self, label):
+        if label.arity is None:
+            return label.name.encode('utf-8')
+        elif label.arity == 0:
+            return "%s()" % label.name.encode('utf-8')
+        else:
+            if isinstance(label.arc, SyntaxArc):
+                parameters = []
+                for name, is_reference in label.arc.parameters:
+                    name = name.encode('utf-8')
+                    if is_reference:
+                        name = "$%s" % name
+                    parameters.append(name)
+            else:
+                parameters = ["?"]*len(label.arity)
+            return "%s(%s)" % (label.name.encode('utf-8'),
+                               ",".join(parameters))
 
 
 class ShellRoutine(DBRoutine):
                 else:
                     name = prefix.split()[0]
                     if self.command_name_pattern.match(name):
-                        prefix = prefix[len(name):].strip()
+                        prefix = prefix[len(name):].lstrip()
                     else:
                         name = ''
                     if name in self.command_by_name:

src/htsql_sqlite/core/connect.py

         # Check if the database file exists.
         if not ((db.database.startswith(":") and db.database.endswith(":")) or
                 os.path.exists(db.database)):
-            raise Error("file does not exist: %s" % db.path)
+            raise Error("file does not exist: %s" % db.database)
         # Generate and return the DBAPI connection.
         connection = sqlite3.connect(db.database)
         self.create_functions(connection)

test/input/routine.yaml

     stdin: |
       help describe
       describe
-      describe school
-      describe school.code
+      describe department
+      describe department.school_code
+      describe department.course
       describe error
   # Headers
   - ctl: [shell, *db]

test/output/routine.yaml

         version                  : print version and license information
         exit                     : quit the shell
         user [remote_user]       : set the remote user for HTTP requests
-        describe [table]         : list tables, or slots for a given table
+        describe [name]          : describe a database entity
         headers on|off           : display HTTP status line and headers
         pager on|off             : pipe long output to a pager
         [get] /query             : execute an HTSQL query
         version                  : print version and license information
         exit                     : quit the shell
         user [remote_user]       : set the remote user for HTTP requests
-        describe [table]         : list tables, or slots for a given table
+        describe [name]          : describe a database entity
         headers on|off           : display HTTP status line and headers
         pager on|off             : pipe long output to a pager
         [get] /query             : execute an HTSQL query
       ** remote user is set to 'htsql_demo'
     exit: 0
   - ctl: [shell, 'sqlite:build/regress/sqlite/htsql_demo.sqlite']
-    stdout: "help describe\ndescribe\ndescribe school\ndescribe school.code\ndescribe
-      error\nDESCRIBE - list tables, or slots for a given table\nUsage: describe [table]\n\nType
-      `describe` to list all tables or `describe <table>` to list\nall columns and
-      links for a given table.\n\nTables introspected for this database are:\n\tappointment\n\tclass\n\tclassification\n\tconfidential\n\tcourse\n\tcourse_classification\n\tdepartment\n\tenrollment\n\tinstructor\n\tprerequisite\n\tprogram\n\tprogram_requirement\n\tschool\n\tsemester\n\tstudent\n\nSlots
-      for `school` are:\n\t code       text\n\t name       text\n\t campus     text\n\t
-      department PLURAL(department)\n\t program    PLURAL(program)\n\nUnable to find
-      table: school.code\n\nUnable to find table: error\n\n"
+    stdout: |
+      help describe
+      describe
+      describe department
+      describe department.school_code
+      describe department.course
+      describe error
+      DESCRIBE - describe a database entity
+      Usage: describe [name]
+
+      Type `describe` to list the content of the database.
+
+      Type `describe <table>` to describe a table and list table
+      attributes.
+
+      Typ `describe <table>.<column>` or `describe <table>.<link>`
+      to describe a table attribute.
+
+      SQLITE:///build/regress/sqlite/htsql_demo.sqlite - HTSQL database
+
+      Labels:
+        appointment              : table
+        class                    : table
+        classification           : table
+        confidential             : table
+        course                   : table
+        course_classification    : table
+        department               : table
+        enrollment               : table
+        instructor               : table
+        prerequisite             : table
+        program                  : table
+        program_requirement      : table
+        school                   : table
+        semester                 : table
+        student                  : table
+
+      DEPARTMENT - table
+
+      SQL name:
+        department
+
+      Unique keys:
+        department(code) {primary}
+        department(name)
+
+      Foreign keys:
+        department(school_code) -> school(code) {nullable}
+        appointment(department_code) -> department(code)
+        course(department_code) -> department(code)
+
+      Identity:
+        code
+
+      Labels:
+        code                     : text column
+        name                     : text column
+        school_code              : text column
+        school                   : link to school
+        appointment              : plural link to appointment
+        course                   : plural link to course
+
+      DEPARTMENT.SCHOOL_CODE - text column
+
+      SQL name:
+        department.school_code
+
+      Domain:
+        text
+
+      Nullable?
+        yes
+
+      Foreign keys:
+        department(school_code) -> school(code) {nullable}
+
+      Link:
+        link to school
+
+      DEPARTMENT.COURSE - plural link to course
+
+      Joins:
+        department(code) -> course(department_code)
+
+      Labels:
+        department_code          : text column
+        no                       : integer column
+        title                    : text column
+        credits                  : integer column
+        description              : text column
+        department               : link to department
+        class                    : plural link to class
+        course_classification    : plural link to course_classification
+        prerequisite_via_of_course : plural link to prerequisite
+        prerequisite_via_on_course : plural link to prerequisite
+
+      ** unknown identifier 'error'
     exit: 0
   - ctl: [shell, 'sqlite:build/regress/sqlite/htsql_demo.sqlite']
     stdout: "help headers\nheaders\nheaders on\n/count(school)\nheaders off\n/count(school)\nHEADERS