[sqlite] Feature request: add support for COMMENT statement
Hello! It's very important to have place to store table metainformation. In all common DBMSs we can use TABLE/COLUMN COMMENT as meta description of table but SQLite doesn't support it. COMMENTs are supported from very old to new PostgreSQL: http://www.postgresql.org/docs/7.1/static/sql-comment.html http://www.postgresql.org/docs/9.2/static/sql-comment.html MySQL: http://dev.mysql.com/doc/refman/5.1/en/create-table.html ORACLE: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_4009.htm IBM DB2: http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_addandretrievecommentsfromcatalog.htm Sybase: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00801.1510/html/iqrefso/X315695.htm P.S. Field type is bad storage for additional information because there are a lot of conversion problems from some bindings (including official tclsqlite) when column types are non-standard. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: add support for COMMENT statement
On 6/13/2013 10:23 AM, Alexey Pechnikov wrote: It's very important to have place to store table metainformation. You are the first person in years who asked for it, so it's probably not *that* important. In all common DBMSs we can use TABLE/COLUMN COMMENT as meta description of table but SQLite doesn't support it. Suppose you have it. What would you do with it? What's the use case? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: add support for COMMENT statement
I'd just like to add my penny's worth to this discussion. We use a COMMENT to store information about the version of our tables that are in place on the customer system. Assume that we start with 'v1' of our tables that have 3 columns. For a variety of reasons we might add a 4th column in 'v2'. Most of the time we do not want to force our customers to upgrade their table definitions to 'v2' just so they can run the new version of our products. This means that our new code has to cater for both 'v1' and 'v2' table definitions. A simple way of doing this is to return the COMMENT which contain our 'version number'. Yes, there are other ways of doing this. But with a myriad of changes possible (indexes, column definitions, triggers etc) we've found that the use of a single place to store our version number makes the checking much easier. Regards, Dave Ward Analytics Ltd - information in motion Tel: +44 (0) 118 9740191 Fax: +44 (0) 118 9740192 www: http://www.ward-analytics.com Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United Kingdom, GU1 3SR Registered company number: 3917021 Registered in England and Wales. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: 14 June 2013 14:37 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Feature request: add support for COMMENT statement On 6/13/2013 10:23 AM, Alexey Pechnikov wrote: > It's very important to have place to store table metainformation. You are the first person in years who asked for it, so it's probably not *that* important. > In all > common DBMSs we can use TABLE/COLUMN COMMENT as meta description of > table but SQLite doesn't support it. Suppose you have it. What would you do with it? What's the use case? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: add support for COMMENT statement
On 6/14/2013 9:59 AM, Dave Wellman wrote: We use a COMMENT to store information about the version of our tables that are in place on the customer system. PRAGMA user_version is intended for this very purpose. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: add support for COMMENT statement
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 14/06/2013 15:03, Igor Tandetnik wrote: > On 6/14/2013 9:59 AM, Dave Wellman wrote: >> We use a COMMENT to store information about the version of our >> tables that are in place on the customer system. > > PRAGMA user_version is intended for this very purpose. Yes but it is defined once-per-file instead of once-per-table. -BEGIN PGP SIGNATURE- Version: GnuPG/MacGPG2 v2.0.18 (Darwin) Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQEcBAEBAgAGBQJRuyYWAAoJEHp3pOlF38H/nQIH/1mfVa/3AMGhYbXhBLaGtxX3 ElWz8lRdPAygQNB3XvhU7Xh9z6u5ZqnMv9ovsI18b/l6w7XzlezP7EoMuwQETzgY ynBiGO2i4bb5Z1Ew9SMJIMYBfFmoCDF01achQllwnhBDDnjb0Q4pAyqPX4bMKqxf 9qLmymCFeXzghyw/LCYCfkxY+oo3AeY7dzDPGhFy6s7q6viV9sDis4JdI2AxAOf1 UTrta1Z5GvtKefD/iX0u0bYMylMd1LkQWuXqHJpSv0QS1yQaV5yNkq29ssV5ea6u RAse/bEOAj+5+/joYm6Aa5v2w1Z6rP3wb1FeV407hVUjMtcx/44j+/SEQrC5GJQ= =8CoS -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: add support for COMMENT statement
Alexey Pechnikov wrote: > It's very important to have place to store table metainformation. In all > common DBMSs we can use TABLE/COLUMN COMMENT as meta description of table > but SQLite doesn't support it. SQLite saves comments in table/view/index/trigger definitions: sqlite> create table t(x /* :-) */); sqlite> .schema CREATE TABLE t(x /* :-) */); Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: add support for COMMENT statement
Thanks Clemens, that is probably a workable option (at least for me). As someone else noted, the PRAGMA user_version will not work for us as it is one value per db file and we want to set this per table. Cheers, Dave Ward Analytics Ltd - information in motion Tel: +44 (0) 118 9740191 Fax: +44 (0) 118 9740192 www: http://www.ward-analytics.com Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United Kingdom, GU1 3SR Registered company number: 3917021 Registered in England and Wales. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Clemens Ladisch Sent: 14 June 2013 15:21 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Feature request: add support for COMMENT statement Alexey Pechnikov wrote: > It's very important to have place to store table metainformation. In > all common DBMSs we can use TABLE/COLUMN COMMENT as meta description > of table but SQLite doesn't support it. SQLite saves comments in table/view/index/trigger definitions: sqlite> create table t(x /* :-) */); sqlite> .schema CREATE TABLE t(x /* :-) */); Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: add support for COMMENT statement
On 6/14/2013 10:17 AM, Finn Wilcox wrote: Yes but it is defined once-per-file instead of once-per-table. "we've found that the use of a single place to store our version number makes the checking much easier." -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: add support for COMMENT statement
Would this not work for you? http://stackoverflow.com/questions/7426205/sqlite-adding-comments-to-tables-and-columns .schema On 13-06-14 11:00 AM, Dave Wellman wrote: Thanks Clemens, that is probably a workable option (at least for me). As someone else noted, the PRAGMA user_version will not work for us as it is one value per db file and we want to set this per table. Cheers, Dave Ward Analytics Ltd - information in motion Tel: +44 (0) 118 9740191 Fax: +44 (0) 118 9740192 www: http://www.ward-analytics.com Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United Kingdom, GU1 3SR Registered company number: 3917021 Registered in England and Wales. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Clemens Ladisch Sent: 14 June 2013 15:21 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Feature request: add support for COMMENT statement Alexey Pechnikov wrote: It's very important to have place to store table metainformation. In all common DBMSs we can use TABLE/COLUMN COMMENT as meta description of table but SQLite doesn't support it. SQLite saves comments in table/view/index/trigger definitions: sqlite> create table t(x /* :-) */); sqlite> .schema CREATE TABLE t(x /* :-) */); Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: add support for COMMENT statement
Hello! > Suppose you have it. What would you do with it? > What's the use case? > --> Igor Tandetnik The COMMENTs can be used to store any information for interchanging with application and external systems. As example, ORM (object relational mappers) may need some additional metainfo for all mapped columns. My web-apps use metainfo like to: user { username TITLE loginKEY password PASSWORD rolesLIST:role note HIDDEN modified TIMESTAMP author ID:user isactive ACTUAL } Where user - table name username, login,... - table fields TITLE, KEY, PASSWORD - definition for rules to process tables fields in web application and in console (import/export utils). The database schema (including tables, indicies, FTS indicies tables, etc.) is generated by this metainfo too. So I have the high-level domain specific data definition language and low-level application file format as SQLite database. Of course, it's usefull to have auto generated schema with records versioning for all tables, fast search for all key/title fields using FTS extension (my patches add snowball tokenizers support), fast search for lists of identifiers (using FTS extension too), import/export utilities, JSON routes, etc. Unfortunately, now we need additional external [plain-text] file with metainfo or additional table with non-trivial and non-standard mapping between database objects and own metainfo table records. > SQLite saves comments in table/view/index/trigger definitions: > > sqlite> create table t(x /* :-) */); > sqlite> .schema > CREATE TABLE t(x /* :-) */); > > Regards, > Clemens Bad idea. The schema definition can't be modified! -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: add support for COMMENT statement
Dear SQLiters, I can not add solutions, since I am a physicist designing database for the first time, but I would like to add questions... Object-relational mapping (ORM) is a new and interesting concept for me that I learned. I will read about it more. However, I do not understand why new functionality of SQLite is needed. Why can't the mapper use a special table of three columns (I will use example from Alexey): tableName | columnName | Rule user | username | TITLE user | login | KEY user | roles | LIST:role (This table describes all classes, "user" being one of them. Perhaps another table is needed to keep track of instances of the classes.) My question is (an it intersects with another topic discussed recently: security) how safe it is to store SQL instructions to be executed in such tables, say in the rule column? Name of some scripts to be executed? Class is a collection of data and methods to operate them. Database is clearly designed to store data, and relations. What about operations? Is it, in general, a good idea to store SQL statements and script names to be called even if security is not an issue? Can someone recommend an ORM? What are the pros and cons of using them? If this list is inappropriate for such discussion, please also let me know and I will refrain. Thank you, Roman From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Alexey Pechnikov [pechni...@mobigroup.ru] Sent: Friday, June 14, 2013 12:47 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Feature request: add support for COMMENT statement Hello! > Suppose you have it. What would you do with it? > What's the use case? > --> Igor Tandetnik The COMMENTs can be used to store any information for interchanging with application and external systems. As example, ORM (object relational mappers) may need some additional metainfo for all mapped columns: My web-apps use metainfo like to: user { username TITLE loginKEY password PASSWORD rolesLIST:role note HIDDEN modified TIMESTAMP author ID:user isactive ACTUAL } Where user - table name username, login,... - table fields TITLE, KEY, PASSWORD - definition for rules to process tables fields in web application and in console (import/export utils). The database schema (including tables, indicies, FTS indicies tables, etc.) is generated by this metainfo too. So I have the high-level domain specific data definition language and low-level application file format as SQLite database. Of course, it's usefull to have auto generated schema with records versioning for all tables, fast search for all key/title fields using FTS extension (my patches add snowball tokenizers support), fast search for lists of identifiers (using FTS extension too), import/export utilities, JSON routes, etc. Unfortunately, now we need additional external [plain-text] file with metainfo or additional table with non-trivial and non-standard mapping between database objects and own metainfo table records. > SQLite saves comments in table/view/index/trigger definitions: > > sqlite> create table t(x /* :-) */); > sqlite> .schema > CREATE TABLE t(x /* :-) */); > > Regards, > Clemens Bad idea. The schema definition can't be modified! -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: add support for COMMENT statement
On Jun 17, 2013, at 6:14 PM, Roman Fleysher wrote: > Dear SQLiters, First thing first… don't hijack a thread… instead start a new one, with a new subject. > Can someone recommend an ORM? No. > What are the pros and cons of using them? http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx > If this list is inappropriate for such discussion, please also let me know > and I will refrain. Inappropriate forum. Try perhaps your favorite programming language discussion list and take it from there. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: add support for COMMENT statement
Hello! > Roman Fleysher Mon, 17 Jun 2013 09:14:59 -0700 > ... However, I do not understand why new > functionality of SQLite is needed. Why can't the mapper use a special table of > three columns (I will use example from Alexey): We can replace the "drop column" functionality by easy SQL script but we can't replace the COMMENT statements functionality without uncontrolled grow of database schema complexity and the loss of independance of single table. Main questions: Can you dump only single table from your database and load it into second database? Can you use Fossil SCM or other for easy versioning schemas of your tables independently? And optionally: Can you easy transform your database dump by shell utilities (sed, awk,etc)? Can you search by grep dump of your database? The really simple and useful ideology: sqlite3 1.db '.dump'|sed ... | sqlite3 2.db or sqlite3 1.db '.dump' > dump.sql fossil diff dump.sql With SQLite we can do many things very simple. Why not? -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: add support for COMMENT statement
Dear fellow users and managers of SQLite, Alexey Pechnikov wrote: >Bad idea. The schema definition can't be modified! In fact it's possible if you use the writable schema pragma http://www.sqlite.org/pragma.html#pragma_writable_schema An application could drop all applicable `ls -l` and `id -P`-results in a database, fire triggers execute one or more smart functions and "presto". Can be done, me seems. This way not such a bad idea as Alexey thinks. As said in the link you have to know well what you're doing, but this is a good advice always. Kind regards, Klaas `Z4us` V ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: add support for COMMENT statement
Just out of curiosity, either by including it as another SQLite built in table, or, a user added table after the fact, couldn't a relationship be built between the new table and a comment, or a field in a table and comment be made? So for example (A very VERY loose example) create table sqlite_comment (identifier char, comment char) or create table sqlite_comment (identifier integer, comment char) Identifier could either be a fully qualified name (IE: tMyTable or tMyTable.UserDetails) and comment could be the required meta data? On Tue, Jun 18, 2013 at 1:05 PM, Klaas V wrote: > Dear fellow users and managers of SQLite, > > Alexey Pechnikov wrote: > > >Bad idea. The schema definition can't be modified! > > In fact it's possible if you use the writable schema pragma > http://www.sqlite.org/pragma.html#pragma_writable_schema > > An application could drop all applicable `ls -l` and `id -P`-results in a > database, fire triggers execute one or more smart functions and "presto". > Can be done, me seems. This way not such a bad idea as Alexey thinks. > > As said in the link you have to know well what you're doing, but this is > a good advice always. > > Kind regards, > Klaas `Z4us` V > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: add support for COMMENT statement
On 18 Jun 2013, at 9:39pm, Stephen Chrzanowski wrote: > Just out of curiosity, either by including it as another SQLite built in > table, or, a user added table after the fact, couldn't a relationship be > built between the new table and a comment, or a field in a table and > comment be made? So for example (A very VERY loose example) > > create table sqlite_comment (identifier char, comment char) > or > create table sqlite_comment (identifier integer, comment char) This idea, acceptable to many users, was referred to upthread. The OP is working with an existing solution which makes the creation of new tables undesirable. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users