Feature request: Commenting DB objects support

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>

Feature request: Commenting DB objects support

Comments (15)

Oracle

MySQL

PostgreSQL

MSSQL (SQL Server)

SQLite