1. Michael Bayer
  2. sqlalchemy
  3. Issues

Issues

Issue #1546 resolved

Feature request: Commenting DB objects support

Anonymous created an issue

Many SQL engines support COMMENTS ON DB OBJECTS.

For example, in Oracle:

CREATE TABLE A (ID INTEGER);
COMMENT ON TABLE A IS 'The test table';
COMMENT ON COLUMN A.ID IS 'Primary key column';

I would be good to make this functionality supported in SQLAlchemy. It's a good idea to assotiate these Comments with Python objects DOCSTRINGS ( _doc _ variable). As I needed this urgently, I am currently using this workaround for sqlalchemy v.0.5.4p2:

# -----------------------------------
# Re-declare Table and Column Classes
# as well as MetaData/Base classes
# to support Comments creation
# NOTE: Works only for Oracle backend!

class Table(sqlalchemy.Table):
    def __init__(self, *args, **kwargs):
        doc = kwargs.pop('comment', None)
        super(Table,self).__init__(*args, **kwargs)
        self.__doc__ = doc

class Column(sqlalchemy.Column):
    def __init__(self, *args, **kwargs):
        doc = kwargs.pop('comment', None)
        super(Column,self).__init__(*args, **kwargs)
        self.__doc__ = doc

class MetaData(sqlalchemy.MetaData):
    def create_comments(self, bind):
        session = sessionmaker(bind)()
        for t in self.sorted_tables:
            if t.__doc__:
                session.execute("COMMENT ON TABLE %s IS '%s'"
                                % (t.name, t.__doc__.replace("'", "''").strip()))
            for c in t.columns:
                if c.__doc__:
                    session.execute("COMMENT ON COLUMN %s.%s IS '%s'"
                                % (t.name, c.name, c.__doc__.replace("'", "''").strip()))
        session.commit()

class DeclarativeMeta(declarative.DeclarativeMeta):
    def __init__(cls, classname, bases, dict_):
        if hasattr(cls, '__table__') and cls.__table__.__doc__:
            cls.__doc__ = cls.__table__.__doc__        
        ret = declarative.DeclarativeMeta.__init__(cls, classname, bases, dict_)
        if hasattr(cls, '__table__'):
            cls.__table__.__doc__ = cls.__doc__
        return ret

Base = declarative.declarative_base(metadata=MetaData(), metaclass=DeclarativeMeta)

.....

# --------------------------------
# DECLARE ORM CLASSES / DB OBJECTS

tb_person = Table('tb_person', Base.metadata,
        Column('id', Integer,
               Sequence('seq_tb_person'),
               primary_key = True,
               comment = 'PK Identifier of the Person record.'),
        Column('name', String(500),
               comment = 'First name of the Person.'),
        Column('surname', String(500),
               comment = 'Surname of the Person.'),
        comment = 'Table, represneting the Person.'
         )

.....

class Property(Base):
    """
    Property of the Person.
    """
    #NOTE: The string above goes to Table comment

    __tablename__ = 'tb_property'

    id = Column(Integer,
                Sequence('seq_tb_property'),
                primary_key=True,
                comment = 'Property identifier. Primay key with sequence.')
    name = Column(String(500),
                  nullable=False,
                  unique=True,
                  comment = 'Property name.')

    def __init__(self, name='Property'):
        self.name = name

    def __repr__(self):
        return "<Property('%s')>" % self.name

....

Then, in my case following commands create both DB Objects (tables/sequences) and comments to them:

    orm.Base.metadata.create_all(bind=engine)
    orm.Base.metadata.create_comments(bind=engine)

THE RESULT IS:

CREATE SEQUENCE seq_tb_person
CREATE TABLE tb_person (
    id INTEGER NOT NULL, 
    name VARCHAR(500), 
    surname VARCHAR(500), 
    PRIMARY KEY (id)
)

CREATE SEQUENCE seq_tb_property
CREATE TABLE tb_property (
    id INTEGER NOT NULL, 
    name VARCHAR(500), 
    PRIMARY KEY (id)
)

COMMENT ON TABLE tb_person IS 'Table, represneting the Person.'
COMMENT ON COLUMN tb_person.id IS 'PK Identifier of the Person record.'
COMMENT ON COLUMN tb_person.name IS 'First name of the Person.'
COMMENT ON COLUMN tb_person.surname IS 'Surname of the Person.'

COMMENT ON TABLE tb_property IS 'Property of the Person.'
COMMENT ON COLUMN tb_property.id IS 'Property identifier. Primay key with sequence.'
COMMENT ON COLUMN tb_property.name IS 'Property name.'

These comments then used by me to generate documentation out of DDL scripts (see Sql2AsciiDoc Project for more info).

My suggestion is to include this technique in upcoming versions of SQLAlchemy (storing docstrings of the objects as DB comments, where possible).

Sincerely,

David Avsajanishvili <avsd05 at gmail dot com>

Comments (15)

  1. Michael Bayer repo owner

    adding "comment" keyword arguments to Table and Column is no problem. But for declarative docstrings, you'd have to put the comment into __table_args__ or build it into a DeclarativeMeta subclass. another big PITA is reflecting the comments back.

  2. Michael Bayer repo owner

    the basic idea of how to do this, not including a change to the "on" signature that will happen as a result of #1538, is attached as a patch. It's not tested at all and I may have made mistakes. For this to go forward the tests need to be run and the DDL for comments has to be implemented correctly for each of Oracle, MySQL, Postgresql, MSSQL, SQLite, and possibly others, with either the appropriate DDL or setting the "supports_comments" flag appropriately on the dialect. then we can commit to trunk and the feature is in.

  3. Anonymous

    I've recently become interested in this feature. Not sure if I know enough about SQLAlchemy to finish the patch, but I've looked up the syntax for various back-end's COMMENT syntax.

    MySQL (http://dev.mysql.com/doc/refman/5.1/en/create-table.html):

    CREATE TABLE demo (
      c VARCHAR(20) COMMENT 'column comment'
    )
    COMMENT 'table comment';
    

    Postgres (http://www.postgresql.org/docs/9.2/static/sql-comment.html) and Oracle (http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4009.htm):

    CREATE TABLE demo (
      c TEXT
    );
    
    COMMENT ON TABLE demo IS 'table comment';
    COMMENT ON COLUMN demo.c IS 'column comment';
    

    SQLite does not support comments as SQL objects per se, but seems to store the entire CREATE TABLE command issued. Thus, comments could be added as -- regular sql comments and be retrieved in that manner as well. But there's no standard for where to place or associate them. They could (potentially) go above, below or after pretty much anything. Example:

    -- table comment
    CREATE TABLE demo (
      -- column comment
      c TEXT
    );
    

    MSSQL doesn't have an "obvious" way of doing this, but seems like using an "extended property" is a standard way (http://stackoverflow.com/questions/4586842/sql-comments-on-create-table-on-sql-server-2008).

  4. Rudolf Cardinal

    For table and column comments, I think the various DDLs are as follows. Tested on MySQL 5.7.12; PostgreSQL 9.3.12; SQL Server 2005 (but not on Oracle - I don't have a copy).

    Oracle

    • Adding during table creation:

      not possible?

    • Adding comments later:

        COMMENT ON TABLE sometable IS 'This is a table comment';
        COMMENT ON COLUMN sometable.somecol IS 'This is a column comment';
    
    • Retrieving:
        SELECT table_name, comments
        FROM all_tab_comments
        WHERE table_name = 'sometable';
    
        SELECT table_name, column_name, comments
        FROM all_col_comments
        WHERE table_name = 'sometable';
    

    MySQL

    • Adding during table creation:
        CREATE TABLE sometable (
            somecol INTEGER COMMENT 'this is a column comment'
        ) COMMENT 'this is a table comment';
    
    • Adding comments later:
        ALTER TABLE sometable COMMENT 'this is a table comment too';
        ALTER TABLE sometable CHANGE somecol somecol INTEGER COMMENT 'this is a column comment too';
    
    • Retrieving:
        SELECT table_schema, table_name, table_comment
        FROM information_schema.tables
        WHERE table_schema = DATABASE() AND table_name = 'sometable';
    
        SELECT table_schema, column_name, column_comment
        FROM information_schema.columns
        WHERE table_schema = DATABASE() AND table_name = 'sometable';
    

    PostgreSQL

    • Adding during table creation:

      not possible?

    • Adding comments later:

        COMMENT ON TABLE sometable IS 'This is a table comment';
        COMMENT ON COLUMN sometable.somecol IS 'This is a column comment';
    
    • Retrieving:

      (Uses internal OIDs to reference table number.)

        SELECT t.table_schema, t.table_name, pgd.description
        FROM pg_catalog.pg_statio_all_tables AS st
        INNER JOIN pg_catalog.pg_description pgd ON (pgd.objoid = st.relid)
        INNER JOIN information_schema.tables t ON (
            pgd.objsubid = 0 AND
            t.table_schema = st.schemaname AND
            t.table_name = st.relname)
        WHERE t.table_name = 'sometable';
    
        SELECT c.table_schema, c.table_name, c.column_name, pgd.description
        FROM pg_catalog.pg_statio_all_tables AS st
        INNER JOIN pg_catalog.pg_description pgd ON (pgd.objoid = st.relid)
        INNER JOIN information_schema.columns c ON (
            pgd.objsubid = c.ordinal_position AND
            c.table_schema = st.schemaname AND
            c.table_name = st.relname)
        WHERE c.table_name = 'sometable';
    

    MSSQL (SQL Server)

    • Unsupported in plain SQL. Possible using "extended properties". A bit nasty, but...

    • Adding during table creation:

      not possible?

    • Adding comments later:

        EXEC sys.sp_addextendedproperty
            @name=N'Description',
            @value=N'This is a table comment',
            @level0type=N'SCHEMA',
            @level0name=N'dbo',
            @level1type=N'TABLE',
            @level1name=N'sometable'
        GO
        EXEC sys.sp_addextendedproperty
            @name=N'Description',
            @value=N'This is a column comment',
            @level0type=N'SCHEMA',
            @level0name=N'dbo',
            @level1type=N'TABLE',
            @level1name=N'sometable',
            @level2type=N'COLUMN',
            @level2name=N'somecol'
        GO
    
    • Retrieving:
        SELECT
            s.name AS schema_name,
            t.name AS table_name,
            CONVERT(VARCHAR(1000), x.value) AS table_comment -- x.value is of type SQL_VARIANT
        FROM sys.tables t
        LEFT JOIN sys.extended_properties x ON t.object_id = x.major_id
        LEFT JOIN sys.schemas s on t.schema_id = s.schema_id
        WHERE x.minor_id = 0 AND t.name = 'sometable';
    
        SELECT
            s.name AS schema_name,
            t.name AS table_name,
            c.name AS column_name,
            CONVERT(VARCHAR(1000), x.value) AS column_comment -- x.value is of type SQL_VARIANT
        FROM sys.columns c
        LEFT JOIN sys.extended_properties x ON (
            c.object_id = x.major_id AND
            c.column_id = x.minor_id
        )
        LEFT JOIN sys.tables t ON c.object_id = t.object_id
        LEFT JOIN sys.schemas s on t.schema_id = s.schema_id
        WHERE t.name = 'sometable';
    

    SQLite

  5. AlSegno

    Hi! I see you have some patchsets in -- I don't know much about how the project is governed though -- is there an anticipated date that this feature might end up in a major release?

  6. Michael Bayer repo owner
    • changed milestone to 1.2
    • edited description

    there's not a date, though I should at least move this to a better milestone as we have a lot of votes here. If I have time, I can possibly review for mid- 1.1 inclusion but the changes here would need to be very "safe". 1.1 final is likely in the coming weeks. 1.2 I'd start working on sometime after 1.1 is out but it would normally be in 2017 if it stays in 1.2.

  7. Michael Bayer repo owner

    this is a leading candidate for 1.2, and the gerrit looks in good shape. however i am not planning on doing new SQLAlchemy 1.2 branch development until next year at the earliest.

  8. Michael Bayer repo owner

    Implement comments for tables, columns

    Added support for SQL comments on :class:.Table and :class:.Column objects, via the new :paramref:.Table.comment and :paramref:.Column.comment arguments. The comments are included as part of DDL on table creation, either inline or via an appropriate ALTER statement, and are also reflected back within table reflection, as well as via the :class:.Inspector. Supported backends currently include MySQL, Postgresql, and Oracle.

    Co-authored-by: Mike Bayer mike_mp@zzzcomputing.com Fixes: #1546 Change-Id: Ib90683850805a2b4ee198e420dc294f32f15d35d

    → <<cset fadb8d61babb>>

  9. Log in to comment