SQLite User Forum

Add Comment is Must
Login

Add Comment is Must

(1) By Sunny Saini (SunnySaini_com) on 2022-08-17 12:16:49 [link] [source]

Comment too, should be allowed to be added along with a new column using "Alter table t_name Add Column ... ".

Consider the following table:

Create table Loan
( id integer primary key
, Lender text -- Lending Company
, Principal Real
, Date text
, Period text
, Maturity_value Real -- The Amount to be returned
, Details text
);

The amount is compounded bimonthly instead of quarterly. After filling large amount of data over a period of time, the company decides to add Bimonthly rate of interest column and tries the following unsuccessful query:

Alter table Loan add column Interest Real -- bimonthly rate of interest;

Conclusion, many times comments in DDL are unavoidable hence, add column query should also allow adding Comment.

(2) By Gunter Hick (gunter_hick) on 2022-08-17 12:27:51 in reply to 1 [link] [source]

> create temp table t ( c text, v integer );
> alter table t add column x blob /* new */;
> .schema t
CREATE TABLE temp.t ( c text, v integer , x blob /* new */);

(3.1) By Sunny Saini (SunnySaini_com) on 2022-08-17 12:58:39 edited from 3.0 in reply to 2 [link] [source]

Thanks, but why not single line double hyphen comments?

Actually, purpose of adding comment in DDL is solved but I think both types of comments should be allowed. Such simple inabilities might give bad impression to SQLite.

(4) By Aask (AAsk1902) on 2022-08-17 12:53:01 in reply to 2 [link] [source]

Very useful tip.

I am sure I tried ths before and encountered errors; glad to have your example.

(5.1) By Sunny Saini (SunnySaini_com) on 2022-08-17 13:03:45 edited from 5.0 in reply to 4 [link] [source]

Deleted

(6) By Gunter Hick (gunter_hick) on 2022-08-17 13:02:19 in reply to 3.1 [link] [source]

I have no idea why the parser balks with SQL style comments. I was only pointing out that c style comments work.

(7) By Sunny Saini (SunnySaini_com) on 2022-08-17 13:06:21 in reply to 6 [link] [source]

I am new to this forum, do Documentation authors and SQLite developers read posts on this forum?

(8) By jose isaias cabrera (jicman) on 2022-08-17 13:17:07 in reply to 3.1 [link] [source]

I tried adding a new line to see if it would acknowledge the new line,

sqlite> create temp table t ( c text, v integer );
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite> alter table t add column
   ...>   x blob -- comment
   ...> ;
Run Time: real 0.000 user 0.000000 sys 0.000000
Runtime error: error in table t after add column: incomplete input

apparently, the -- breaks the command. Interestingly enough, this works,

sqlite> create temp table t0
   ...> (
   ...>  c text, -- comment 0
   ...>  v integer -- comment 1
   ...> );
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite>

Just thinking out-loud.

(9) By jose isaias cabrera (jicman) on 2022-08-17 13:17:45 in reply to 7 [link] [source]

Yes, but not every second of the day...

(10) By Sunny Saini (SunnySaini_com) on 2022-08-17 13:28:20 in reply to 9 [link] [source]

OK, then they might fix this short coming.

(11) By Sunny Saini (SunnySaini_com) on 2022-08-17 13:32:06 in reply to 8 [link] [source]

While creating DDL, double hyphen comments always work but, not later on with new column addition.

(12) By Stephan Beal (stephan) on 2022-08-17 14:49:40 in reply to 10 [link] [source]

OK, then they might fix this short coming.

Until you can demonstrate that it is a genuine shortcoming, it seems unlikely that this corner-case "must have" will be treated as a "must have." Given that /*...*/ works, there is a sensible workaround for your request which doesn't require any code changes. That option, combined with the fact that (to the best of my fallible recollection) nobody else has ever said that such comments in an ALTER statement are a "must", there would seem to be little justification/motivation for adding additional code for that specific capability for the benefit of a single user.

(13) By Keith Medcalf (kmedcalf) on 2022-08-17 15:33:53 in reply to 8 [link] [source]

The root of the problem is that the it is apparently wanted to "add a comment" by embedding the comment in a command to ALTER TABLE.

Clearly this cannot work. The comment would be stripped BEFORE the command is processed and therefore cannot be treated as a "text string" payload.

However, the handling is inconsistent. This should not be an error:

alter table t add column
  x blob -- comment
;

The comment should be stripped (it applies to the command ALTER TABLE and is not a payload string). That is, it should be processed exactly the same as:

alter table t add column
  x blob
;

This is why non-stripped comments work (/* ... */) -- they are merely payload.

Technically, stripped comments should be removed and not stored in the schema at all. However, a historical design decision decided that CREATE statements which create schema entries should just store the entire statement sans any processing.

This has led to a state of affairs where some want to treat comments as payload when they are not payload.

(14) By anonymous on 2022-08-17 18:36:29 in reply to 13 [source]

Some overhaul is needed here. Consider this malicious bit of SQL:

    create table foo (
        id integer primary key
    ) strict /* Bobby Tables Was Here

Pass it to sqlite3_exec in the current version of SQLite to get

  1. no error and
  2. a database that makes the shell's .dump command output nonsense.

(15) By Tim Streater (Clothears) on 2022-08-17 20:08:50 in reply to 12 [link] [source]

Personally, I build my tables in the host language, and put the comments there, thus avoiding bulking up the schema with extraneous text.

(16) By Ryan Smith (cuz) on 2022-08-18 00:09:38 in reply to 3.1 [link] [source]

For me the most problematic part of double-hyphen comments is that they cannot refactor.

It's often needed to reformat a query removing line-breaks or copying between windows and unix systems which treat line-breaks different.

There is no path to go from:

CREATE TABLE t(
  AA TEX, -- Col1
  BB      -- Col2
);

into

CREATE TABLE t(AA TEX, -- Col1  BB      -- Col2);

which will always fail, and there is no algorithm for mending it. Whereas this example works in all formats:

CREATE TABLE t(
  AA TEX, /* Col1 */
  BB      /* Col2 */
);

and

CREATE TABLE t(AA TEX, /* Col1 */  BB   /* Col2 */);

works perfectly just the same.

(17.1) By Sunny Saini (SunnySaini_com) on 2022-08-18 02:02:26 edited from 17.0 in reply to 12 [link] [source]

Until you can demonstrate that it is a genuine shortcoming

Then why were double hyphen comments introduced in SQLite since very old times? Why not remove this functionality completely?

(18) By Sunny Saini (SunnySaini_com) on 2022-08-18 02:07:10 in reply to 13 [link] [source]

I am not a programmer but I think this should be easy to fix by developers for Alter table code part.

(19.1) By Sunny Saini (SunnySaini_com) on 2022-08-18 02:20:34 edited from 19.0 in reply to 14 [link] [source]

This is Syntax error, the comment must be closed with */

In Android SQLite v3.8.6 I tried it as:

create table foo ( id integer primary key ) without rowid /* Bobby Tables Was Here ;

Table was created correctly and even adding values work. But SQLite_master showed extra text that already existed after ";" of my editor window.

(20) By Sunny Saini (SunnySaini_com) on 2022-08-18 02:19:27 in reply to 16 [link] [source]

Oh, so it appears it will be difficult for developers to fix it

(21) By Sunny Saini (SunnySaini_com) on 2022-08-18 02:28:30 in reply to 12 [link] [source]

adding additional code for that specific capability for the benefit of a single user.

A matter or bug reported by single user doesn't mean that it is for his benefit only.

(22) By Kevin Charles (KevinCharles) on 2022-08-18 04:03:29 in reply to 21 [link] [source]

Sunny Saini, well spotted.

You can check development activity using this link:

https://www.sqlite.org/src/timeline

Note the reference to your Forum post.

For date: 2022-08-17 at time: 20:18, Dr. Hipp added:

In the ".dump" command of the CLI, if a schema statement ends with an unterminated comment, try to terminate that comment prior to appending the ";" at the end. Forum post d7be961c5c. (Leaf check-in: 96e112da user: drh tags: trunk)

If you wish to check the Documentation repository, use:

https://www.sqlite.org/docsrc/timeline

(23) By Decker (d3x0r) on 2022-08-18 04:17:56 in reply to 17.1 [link] [source]

Even if they were taken, they aren't saved as part of the schema, so the comment is lost - sometimes it would be nice to have additional comments at the end of a table that are part of the database, and not necessarily the code that created the database.

(24) By PChemGuy on 2022-08-18 05:45:48 in reply to 1 [link] [source]

When I want to store DDL related comments, I define a meta table

CREATE TABLE table_meta (
    table_name TEXT NOT NULL COLLATE NOCASE,
    column_name TEXT NOT NULL COLLATE NOCASE,
    comment TEXT NOT NULL COLLATE NOCASE
)

and store the necessary information in this table. No problems with alterations or whatever else. Simply always follow this approach and your problem should be solved.

(25) By Stephan Beal (stephan) on 2022-08-18 10:22:14 in reply to 17.1 [link] [source]

Then why were double hyphen comments introduced in SQLite since very old times? Why not remove this functionality completely?

Because double-hyphens are part of the SQL standard.

(26.1) By Simon Slavin (slavin) on 2022-08-18 19:38:12 edited from 26.0 in reply to 24 [link] [source]

+1. Well, not this precise thing. More the underlying concept.

If you want to store something in a SQLite database, make a table for it and put in in the table. Don't rely on a weird, non-standard, barely-documented feature which preserves or deletes stuff as it wishes.

NOTE: After storing a comment in a table, use ALTER TABLE ADD COLUMN … and see what happens.

(27) By anonymous on 2022-08-18 12:36:05 in reply to 13 [link] [source]

Fortunately, the non-malicious case when all comments are properly terminated is easy to fix.

  1. In src/parse.y, when running the action for a complete CREATE statement, pParse->sLastToken will always be a semicolon token, either an organic one or an empty synthetic one generated by the tokeniser at the end of the input. So including it in the text to be processed is pointless.

  2. In sqlite3AlterFinishAddColumn in src/alter.c, stripping trailing semicolons is unnecessary (see above), and stripping trailing whitespace could remove the terminating newline from a single-line comment. Kill that loop entirely.

Index: src/alter.c
==================================================================
--- src/alter.c
+++ src/alter.c
@@ -404,14 +404,10 @@
 
 
   /* Modify the CREATE TABLE statement. */
   zCol = sqlite3DbStrNDup(db, (char*)pColDef->z, pColDef->n);
   if( zCol ){
-    char *zEnd = &zCol[pColDef->n-1];
-    while( zEnd>zCol && (*zEnd==';' || sqlite3Isspace(*zEnd)) ){
-      *zEnd-- = '\0';
-    }
     /* substr() operations on characters, but addColOffset is in bytes. So we
     ** have to use printf() to translate between these units: */
     assert( IsOrdinaryTable(pTab) );
     assert( IsOrdinaryTable(pNew) );
     sqlite3NestedParse(pParse, 

Index: src/parse.y
==================================================================
--- src/parse.y
+++ src/parse.y
@@ -1673,11 +1673,11 @@
 cmd ::= ALTER TABLE fullname(X) RENAME TO nm(Z). {
   sqlite3AlterRenameTable(pParse,X,&Z);
 }
 cmd ::= ALTER TABLE add_column_fullname
         ADD kwcolumn_opt columnname(Y) carglist. {
-  Y.n = (int)(pParse->sLastToken.z-Y.z) + pParse->sLastToken.n;
+  Y.n = (int)(pParse->sLastToken.z-Y.z);
   sqlite3AlterFinishAddColumn(pParse, &Y);
 }
 cmd ::= ALTER TABLE fullname(X) DROP kwcolumn_opt nm(Y). {
   sqlite3AlterDropColumn(pParse, X, &Y);
 }

(28) By anonymous on 2022-08-18 16:30:33 in reply to 27 [link] [source]

That should have been "a complete CREATE or ALTER statement", of course. I blame my keyboard.

(29) By Sunny Saini (SunnySaini_com) on 2022-08-18 18:31:49 in reply to 22 [link] [source]

https://www.sqlite.org/src/timeline

https://www.sqlite.org/docsrc/timeline

Kevin Charles, Thank you for these links, I didn't know about this feature earlier.

(30) By Sunny Saini (SunnySaini_com) on 2022-08-18 18:40:30 in reply to 23 [link] [source]

they aren't saved as part of the schema, so the comment is lost

Comments are saved as part of Schema but they are ignored by the parser when database is connected. When you browse SQLite_master table, you will see your saved comments in SQL column.

Also for a very large table definition it may be inconvenient to scroll back and forth to comment at end and code part above.

(31) By Sunny Saini (SunnySaini_com) on 2022-08-18 18:42:33 in reply to 24 [link] [source]

This is good idea.

(32) By Sunny Saini (SunnySaini_com) on 2022-08-18 18:46:43 in reply to 25 [link] [source]

Because double-hyphens are part of the SQL standard.

Then this standard should be maintained to its full.

(33) By Sunny Saini (SunnySaini_com) on 2022-08-18 18:51:27 in reply to 26.0 [link] [source]

Actually, double hyphen comments is a standard, it only requires small improvement.