Feature request: add support for COMMENT statement

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

Feature request: add support for COMMENT statement

Alexey Pechnikov-2
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: add support for COMMENT statement

Igor Tandetnik-2
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: add support for COMMENT statement

Dave Wellman
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: [hidden email]
[mailto:[hidden email]] On Behalf Of Igor Tandetnik
Sent: 14 June 2013 14:37
To: [hidden email]
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: add support for COMMENT statement

Igor Tandetnik-2
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: add support for COMMENT statement

Finn Wilcox
-----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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: add support for COMMENT statement

Clemens Ladisch
In reply to this post by Alexey Pechnikov-2
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: add support for COMMENT statement

Dave Wellman
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: [hidden email]
[mailto:[hidden email]] On Behalf Of Clemens Ladisch
Sent: 14 June 2013 15:21
To: [hidden email]
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: add support for COMMENT statement

Alexey Pechnikov-2
In reply to this post by Alexey Pechnikov-2
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
  login        KEY
  password     PASSWORD
  roles        LIST: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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: add support for COMMENT statement

Igor Tandetnik-2
In reply to this post by Finn Wilcox
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: add support for COMMENT statement

rmisk
In reply to this post by Dave Wellman
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: [hidden email]
> [mailto:[hidden email]] On Behalf Of Clemens Ladisch
> Sent: 14 June 2013 15:21
> To: [hidden email]
> 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: add support for COMMENT statement

Roman Fleysher
In reply to this post by Alexey Pechnikov-2
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: [hidden email] [[hidden email]] on behalf of Alexey Pechnikov [[hidden email]]
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
  login        KEY
  password     PASSWORD
  roles        LIST: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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: add support for COMMENT statement

Petite Abeille-2

On Jun 17, 2013, at 6:14 PM, Roman Fleysher <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: add support for COMMENT statement

Alexey Pechnikov-2
In reply to this post by Alexey Pechnikov-2
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: add support for COMMENT statement

KlaasV
In reply to this post by Alexey Pechnikov-2
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Klaas "Z4us" V, MetaDBA at InnocentIsArt.EU
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: add support for COMMENT statement

Stephen Chrzanowski
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 <[hidden email]> 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: add support for COMMENT statement

Simon Slavin-3

On 18 Jun 2013, at 9:39pm, Stephen Chrzanowski <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users