Re: [sqlite] Feature request: add support for COMMENT statement

[Simon Slavin]

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

Re: [sqlite] 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  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

[Klaas V]

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

[Alexey Pechnikov]

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

[Petite Abeille]

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

[Roman Fleysher]

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

[Alexey Pechnikov]

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

[Roger]

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

[Igor Tandetnik]

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

[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: 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

[Clemens Ladisch]

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

[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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request: add support for COMMENT statement

[Igor Tandetnik]

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

[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: 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

[Igor Tandetnik]

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