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>
Comments (15)
-
repo owner -
repo owner - changed milestone to 0.6.xx
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. -
repo owner - changed component to schema
-
repo owner - changed milestone to blue sky
-
Account Deleted - changed watchers to David, Avsajanishvili, <avsd05@gmail.com>
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).
-
Hi!
Has there been any progress on this feature request? I am also interested in this :D
Best regards,
Pau.
-
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';
-
References
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4009.htm https://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_1036.htm https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2095.htm Note also alternative views (DBA_, USER_ rather than ALL_*).
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';
-
References
http://dev.mysql.com/doc/refman/5.7/en/create-table.html http://dev.mysql.com/doc/refman/5.7/en/tables-table.html http://dev.mysql.com/doc/refman/5.7/en/columns-table.html
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';
-
References
http://www.postgresql.org/docs/9.1/static/sql-createtable.html http://www.postgresql.org/docs/9.2/static/sql-comment.html http://stackoverflow.com/questions/343138/retrieving-comments-from-a-postgresql-db http://www.postgresql.org/docs/8.3/static/catalog-pg-description.html http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STATIO-ALL-TABLES-VIEW
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';
-
References
http://stackoverflow.com/questions/4586842/sql-comments-on-create-table-on-sql-server-2008 https://msdn.microsoft.com/en-us/library/ms180047.aspx https://mrsql.wordpress.com/tag/sp_addextendedproperty/
SQLite
-
Unsupported.
-
-
repo owner @RudolfCardinal thanks! that is very useful.
-
What's the current state of this?
-
I have started implementing at https://gerrit.sqlalchemy.org/#/c/111/
-
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?
-
repo owner - edited description
- changed milestone to 1.2
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.
-
Any news? this will be really useful for me too!
-
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.
-
repo owner - changed status to resolved
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:
#1546Change-Id: Ib90683850805a2b4ee198e420dc294f32f15d35d→ <<cset fadb8d61babb>>
- Log in to comment
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 aDeclarativeMeta
subclass. another big PITA is reflecting the comments back.