Getting rid of reserved SQL words

[Romain LE DISEZ-5]

Hi all,

Andy, Bharat: you said a month ago that you want to rename the columns
that use reserved SQL words (left, right, key, value, ...).

As it is not your priority, I understand you will not spend time on
this. I'm free for it now, and if you're OK, I want to start working on
it.

I checked all the columns name with these tables :
 -
http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-0.html
 -
http://www.postgresql.org/docs/8.4/interactive/sql-keywords-appendix.html
 - http://developer.mimer.com/validator/sql-reserved-words.tml

A strict reading of those pages reveals that these columns must be
renamed :
      * caches.key
      * comments.state
      * graphics_rules.operation
      * incoming_translations.key
      * incoming_translations.translation
      * items.left
      * items.level
      * items.right
      * logs.timestamp
      * messages.key
      * messages.value
      * outgoing_translations.key
      * outgoing_translations.translation
      * search_records.data
      * sessions.data
      * tags.count
      * tasks.state
      * users.admin
      * vars.value

Going step by step, I propose to work on :
      * items.left                 -> left_bound
      * items.right                -> right_bound

It's the easiest part and I already have a patch for that.

Then, if it gets merged, I will work on :
      * caches.key                 -> hash (?)
      * incoming_translations.key  -> hash (?)
      * messages.key               -> label
      * outgoing_translations.key  -> hash (?)

Step 3 :
      * messages.value             -> content
      * vars.value                 -> content

If these 3 steps get completed, we will see if there is problems with
the others columns.

The last step will be to remove the backquotes.

I'm waiting for comments.

Greetings.


------------------------------------------------------------------------------
__[ g a l l e r y - d e v e l ]_________________________

[ list info/archive --> http://gallery.sf.net/lists.php ]
[ gallery info/FAQ/download --> http://gallery.sf.net ]

[Tim Almdal]

Why don't you just go and do it in your fork,  We can always pull the changes at some point after you have insured that that the unit tests still run.

----- Original Message -----
From: Romain LE DISEZ <romain.g3@...>
Date: Thursday, July 23, 2009 5:50 am
Subject: [Gallery-devel] Getting rid of reserved SQL words
To: Gallery Community <gallery-devel@...>

> Hi all,
>
> Andy, Bharat: you said a month ago that you want to rename the columns
> that use reserved SQL words (left, right, key, value, ...).
>
> As it is not your priority, I understand you will not spend time on
> this. I'm free for it now, and if you're OK, I want to start
> working on
> it.
>
> I checked all the columns name with these tables :
>  -
> http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-
> version-reference-reservedwords-5-0.html
>  -
> http://www.postgresql.org/docs/8.4/interactive/sql-keywords-
> appendix.html - http://developer.mimer.com/validator/sql-
> reserved-words.tml
>
> A strict reading of those pages reveals that these columns must be
> renamed :
>       * caches.key
>       * comments.state
>       * graphics_rules.operation
>       * incoming_translations.key
>       * incoming_translations.translation
>       * items.left
>       * items.level
>       * items.right
>       * logs.timestamp
>       * messages.key
>       * messages.value
>       * outgoing_translations.key
>       * outgoing_translations.translation
>       * search_records.data
>       * sessions.data
>       * tags.count
>       * tasks.state
>       * users.admin
>       * vars.value
>
> Going step by step, I propose to work on :
>       *
> items.left                 -> left_bound
>       *
> items.right                -> right_bound
>
> It's the easiest part and I already have a patch for that.
>
> Then, if it gets merged, I will work on :
>       *
> caches.key                 -> hash (?)
>       * incoming_translations.key 
> -> hash (?)
>       *
> messages.key               -> label
>       * outgoing_translations.key 
> -> hash (?)
>
> Step 3 :
>       *
> messages.value             -> content
>       *
> vars.value                 -> content
>
> If these 3 steps get completed, we will see if there is problems with
> the others columns.
>
> The last step will be to remove the backquotes.
>
> I'm waiting for comments.
>
> Greetings.
>
>
> -----------------------------------------------------------------
> -------------
> __[ g a l l e r y - d e v e l ]_________________________
>
> [ list info/archive --> http://gallery.sf.net/lists.php ]
> [ gallery info/FAQ/download --> http://gallery.sf.net ]
>

Website: http://www.timalmdal.com


------------------------------------------------------------------------------

__[ g a l l e r y - d e v e l ]_________________________

[ list info/archive --> http://gallery.sf.net/lists.php ]
[ gallery info/FAQ/download --> http://gallery.sf.net ]

[Romain LE DISEZ-5]

It's what I'm planning to do. But I prefer to discuss it before, for few
reasons :
      * choice of the new names
      * maybe somebody already started it but not finished
      * I want to be sure there is motivation, I don't want to waste my
        time on something that will probably never be accepted
      * ...

Greetings.

Le jeudi 23 juillet 2009 à 05:53 -0700, Tim Almdal a écrit :

> Why don't you just go and do it in your fork,  We can always pull the
> changes at some point after you have insured that that the unit tests
> still run.
>
> ----- Original Message -----
> From: Romain LE DISEZ <romain.g3@...>
> Date: Thursday, July 23, 2009 5:50 am
> Subject: [Gallery-devel] Getting rid of reserved SQL words
> To: Gallery Community <gallery-devel@...>
>
> > Hi all,
> >
> > Andy, Bharat: you said a month ago that you want to rename the
> columns
> > that use reserved SQL words (left, right, key, value, ...).
> >
> > As it is not your priority, I understand you will not spend time on
> > this. I'm free for it now, and if you're OK, I want to start
> > working on
> > it.
> >
> > I checked all the columns name with these tables :
> >  -
> > http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-
> > version-reference-reservedwords-5-0.html
> >  -
> > http://www.postgresql.org/docs/8.4/interactive/sql-keywords-
> > appendix.html - http://developer.mimer.com/validator/sql-
> > reserved-words.tml
> >
> > A strict reading of those pages reveals that these columns must be
> > renamed :
> >       * caches.key
> >       * comments.state
> >       * graphics_rules.operation
> >       * incoming_translations.key
> >       * incoming_translations.translation
> >       * items.left
> >       * items.level
> >       * items.right
> >       * logs.timestamp
> >       * messages.key
> >       * messages.value
> >       * outgoing_translations.key
> >       * outgoing_translations.translation
> >       * search_records.data
> >       * sessions.data
> >       * tags.count
> >       * tasks.state
> >       * users.admin
> >       * vars.value
> >
> > Going step by step, I propose to work on :
> >       *
> > items.left                 -> left_bound
> >       *
> > items.right                -> right_bound
> >
> > It's the easiest part and I already have a patch for that.
> >
> > Then, if it gets merged, I will work on :
> >       *
> > caches.key                 -> hash (?)
> >       * incoming_translations.key  
> > -> hash (?)
> >       *
> > messages.key               -> label
> >       * outgoing_translations.key  
> > -> hash (?)
> >
> > Step 3 :
> >       *
> > messages.value             -> content
> >       *
> > vars.value                 -> content
> >
> > If these 3 steps get completed, we will see if there is problems
> with
> > the others columns.
> >
> > The last step will be to remove the backquotes.
> >
> > I'm waiting for comments.
> >
> > Greetings.
> >
> >
> > -----------------------------------------------------------------
> > -------------
> > __[ g a l l e r y - d e v e l ]_________________________
> >
> > [ list info/archive --> http://gallery.sf.net/lists.php ]
> > [ gallery info/FAQ/download --> http://gallery.sf.net ]
> >
>
> Website: http://www.timalmdal.com
>

------------------------------------------------------------------------------
__[ g a l l e r y - d e v e l ]_________________________

[ list info/archive --> http://gallery.sf.net/lists.php ]
[ gallery info/FAQ/download --> http://gallery.sf.net ]

[Bharat Mediratta]

I'm going to defer to Andy on this.  The names that I chose for those
columns were the ones that were the most intuitive for me, so it's
likely that anything else you choose is (for me) a step in the wrong
direction.  But if we can do it in a fashion that doesn't impact our
users and doesn't require us to do crazy stuff in the code, then I'm ok
with changing them.

-Bharat

Romain LE DISEZ wrote:

> Hi all,
>
> Andy, Bharat: you said a month ago that you want to rename the columns
> that use reserved SQL words (left, right, key, value, ...).
>
> As it is not your priority, I understand you will not spend time on
> this. I'm free for it now, and if you're OK, I want to start working on
> it.
>
> I checked all the columns name with these tables :
>  -
> http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-0.html
>  -
> http://www.postgresql.org/docs/8.4/interactive/sql-keywords-appendix.html
>  - http://developer.mimer.com/validator/sql-reserved-words.tml
>
> A strict reading of those pages reveals that these columns must be
> renamed :
>       * caches.key
>       * comments.state
>       * graphics_rules.operation
>       * incoming_translations.key
>       * incoming_translations.translation
>       * items.left
>       * items.level
>       * items.right
>       * logs.timestamp
>       * messages.key
>       * messages.value
>       * outgoing_translations.key
>       * outgoing_translations.translation
>       * search_records.data
>       * sessions.data
>       * tags.count
>       * tasks.state
>       * users.admin
>       * vars.value
>
> Going step by step, I propose to work on :
>       * items.left                 -> left_bound
>       * items.right                -> right_bound
>
> It's the easiest part and I already have a patch for that.
>
> Then, if it gets merged, I will work on :
>       * caches.key                 -> hash (?)
>       * incoming_translations.key  -> hash (?)
>       * messages.key               -> label
>       * outgoing_translations.key  -> hash (?)
>
> Step 3 :
>       * messages.value             -> content
>       * vars.value                 -> content
>
> If these 3 steps get completed, we will see if there is problems with
> the others columns.
>
> The last step will be to remove the backquotes.
>
> I'm waiting for comments.
>
> Greetings.
>
>
> ------------------------------------------------------------------------------
> __[ g a l l e r y - d e v e l ]_________________________
>
> [ list info/archive --> http://gallery.sf.net/lists.php ]
> [ gallery info/FAQ/download --> http://gallery.sf.net ]
>

------------------------------------------------------------------------------
__[ g a l l e r y - d e v e l ]_________________________

[ list info/archive --> http://gallery.sf.net/lists.php ]
[ gallery info/FAQ/download --> http://gallery.sf.net ]

[Andy Staudacher-3]

On Thu, Jul 23, 2009 at 12:50 PM, Romain LE DISEZ <romain.g3@...> wrote:

Hi all,

Andy, Bharat: you said a month ago that you want to rename the columns
that use reserved SQL words (left, right, key, value, ...).

As it is not your priority, I understand you will not spend time on
this. I'm free for it now, and if you're OK, I want to start working on
it.

I checked all the columns name with these tables :
 -
http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-0.html
 -
http://www.postgresql.org/docs/8.4/interactive/sql-keywords-appendix.html
 - http://developer.mimer.com/validator/sql-reserved-words.tml

A strict reading of those pages reveals that these columns must be

"key, left, right"...that's what I'd expect to be reserved and I could do without.

"count", "timestamp" sorta as well.

But "data", "state", "level", "translation"? That's kinda ridiculous. 

How about a less strict reading of these pages? I'd like to know what the absolute minimum set of keywords is we have to avoid.

I.e. Can we run a query "select state from comments;" against MySQL 5 and PG 8.4 without errors with expected results? If so, let's allow "state" as a column name. Etc.

It'd be great if you could generate such a less strict list of forbidden column names that are in use in G2 beta 2.

Thanks,

 - Andy

renamed :
     * caches.key
     * comments.state
     * graphics_rules.operation
     * incoming_translations.key
     * incoming_translations.translation
     * items.left
     * items.level
     * items.right
     * logs.timestamp
     * messages.key
     * messages.value
     * outgoing_translations.key
     * outgoing_translations.translation
     * search_records.data
     * sessions.data
     * tags.count
     * tasks.state
     * users.admin
     * vars.value

Going step by step, I propose to work on :
     * items.left                 -> left_bound
     * items.right                -> right_bound

It's the easiest part and I already have a patch for that.

Then, if it gets merged, I will work on :
     * caches.key                 -> hash (?)
     * incoming_translations.key  -> hash (?)
     * messages.key               -> label
     * outgoing_translations.key  -> hash (?)

Step 3 :
     * messages.value             -> content
     * vars.value                 -> content

If these 3 steps get completed, we will see if there is problems with
the others columns.

The last step will be to remove the backquotes.

I'm waiting for comments.

Greetings.


------------------------------------------------------------------------------
__[ g a l l e r y - d e v e l ]_________________________

[ list info/archive --> http://gallery.sf.net/lists.php ]
[ gallery info/FAQ/download --> http://gallery.sf.net ]

------------------------------------------------------------------------------

__[ g a l l e r y - d e v e l ]_________________________

[ list info/archive --> http://gallery.sf.net/lists.php ]
[ gallery info/FAQ/download --> http://gallery.sf.net ]

[Romain LE DISEZ-5]

Le 23 juil. 09 à 20:26, Andy Staudacher a écrit :

> "key, left, right"...that's what I'd expect to be reserved and I  
> could do without.
> "count", "timestamp" sorta as well.
> But "data", "state", "level", "translation"? That's kinda ridiculous.
>
> How about a less strict reading of these pages? I'd like to know  
> what the absolute minimum set of keywords is we have to avoid.
> I.e. Can we run a query "select state from comments;" against MySQL  
> 5 and PG 8.4 without errors with expected results? If so, let's  
> allow "state" as a column name. Etc.
>
> It'd be great if you could generate such a less strict list of  
> forbidden column names that are in use in G2 beta 2.
>
> Thanks,
>  - Andy

I agree with you. As I wrote, this list is a "strict reading" but I  
propose, in the 3 steps, to only rename "left", "right", "key" and  
"value". I also wrote that we will see (and discuss) if there are  
problems with the other columns of the list.

For now, I only ask your opinion for "left", "right", "key" and "value".

Going throught the list, it seems that only "left", "right" and "key"  
must be renamed for MySQL and PgSQL (I don't know for the other  
RDBMS). Maybe I could stop at step 2 for now.

Greetings.
------------------------------------------------------------------------------
__[ g a l l e r y - d e v e l ]_________________________

[ list info/archive --> http://gallery.sf.net/lists.php ]
[ gallery info/FAQ/download --> http://gallery.sf.net ]

[Andy Staudacher-3]

On Thu, Jul 23, 2009 at 1:33 PM, Romain LE DISEZ <romain.g3@...> wrote:

Le 23 juil. 09 à 20:26, Andy Staudacher a écrit :

"key, left, right"...that's what I'd expect to be reserved and I could do without.
"count", "timestamp" sorta as well.
But "data", "state", "level", "translation"? That's kinda ridiculous.

How about a less strict reading of these pages? I'd like to know what the absolute minimum set of keywords is we have to avoid.
I.e. Can we run a query "select state from comments;" against MySQL 5 and PG 8.4 without errors with expected results? If so, let's allow "state" as a column name. Etc.

It'd be great if you could generate such a less strict list of forbidden column names that are in use in G2 beta 2.

Thanks,
 - Andy

I agree with you. As I wrote, this list is a "strict reading" but I propose, in the 3 steps, to only rename "left", "right", "key" and "value". I also wrote that we will see (and discuss) if there are problems with the other columns of the list.

For now, I only ask your opinion for "left", "right", "key" and "value".

Going throught the list, it seems that only "left", "right" and "key" must be renamed for MySQL and PgSQL (I don't know for the other RDBMS). Maybe I could stop at step 2 for now.

In general, I'm open to these changes and willing to accept them if you can provide the G3 upgrade code as well.

Can you make sure that we really only need to rename left, right, key and value? It's fine if you want to implement the changes step by step, but before merging the first of your patches to the official G3 source tree, I want to be sure that the scope of the changes is limited to these 4 column names.

I.e. you could create a test table with all the column names we use in G3, excluding left, right, key, value. And then you could check if you can run a bunch of SQL queries on that table in PG and MySQL.

As for the column names that we should choose. Here are some modifications to your proposal:

 * items.left                 -> lft, left_val, left_value, left_bound
 * items.right                -> rgt, right_val, right_value, right_bound

I guess I'd prefer lft and rgt.

 * caches.key                 -> cache_key
 * incoming_translations.key  -> msg_key

 * outgoing_translations.key  -> msg_key

 * messages.key               -> label

 * messages.value             -> content, text, message

I guess I'd prefer text.

 * vars.value                 -> content

@Bharat:

What would you prefer?

 - Andy

Greetings.

------------------------------------------------------------------------------

__[ g a l l e r y - d e v e l ]_________________________

[ list info/archive --> http://gallery.sf.net/lists.php ]
[ gallery info/FAQ/download --> http://gallery.sf.net ]

[Bharat Mediratta]

Andy Staudacher wrote:

> As for the column names that we should choose. Here are some
> modifications to your proposal:
>
>  * items.left                 -> lft, left_val, left_value, left_bound
>  * items.right                -> rgt, right_val, right_value, right_bound
>
> I guess I'd prefer lft and rgt.
>
>  * caches.key                 -> cache_key
>  * incoming_translations.key  -> msg_key
>  * outgoing_translations.key  -> msg_key
>  * messages.key               -> label
>
>  * messages.value             -> content, text, message
>
> I guess I'd prefer text.
>
>  * vars.value                 -> content
>
> @Bharat:
> What would you prefer?

I preferred the full words which is why I went with "left" and "right".
 Partly because I don't see "lft" as "left".  I guess I prefer
"left_ptr" and "right_ptr".

Everything else looks fine to me.

-Bharat


------------------------------------------------------------------------------
__[ g a l l e r y - d e v e l ]_________________________

[ list info/archive --> http://gallery.sf.net/lists.php ]
[ gallery info/FAQ/download --> http://gallery.sf.net ]

[Romain LE DISEZ-5]

Le 24 juil. 09 à 10:26, Andy Staudacher a écrit :

> In general, I'm open to these changes and willing to accept them if  
> you can provide the G3 upgrade code as well.

Sure. I will.


> Can you make sure that we really only need to rename left, right,  
> key and value? It's fine if you want to implement the changes step  
> by step, but before merging the first of your patches to the  
> official G3 source tree, I want to be sure that the scope of the  
> changes is limited to these 4 column names.

No, there is only 3 columns that MUST be renamed :
  - left, right and key for MySQL
  - left and right for PgSQL


> I.e. you could create a test table with all the column names we use  
> in G3, excluding left, right, key, value. And then you could check  
> if you can run a bunch of SQL queries on that table in PG and MySQL.

I did. For each column, the tests were :
  - CREATE TABLE test ( {column} INTEGER );
  - CREATE TABLE test2 ( {column} INTEGER );
  - SELECT {column} FROM test ORDER BY {column};
  - SELECT {column} FROM test GROUP BY {column};
  - INSERT INTO test({column}) VALUES(10);
  - UPDATE test SET {column}=15;
  - DELETE FROM test WHERE {column}=5;
  - SELECT {column} INNER JOIN test2.{column}=test.{column};
  - SELECT {column} LEFT JOIN test2.{column}=test.{column};
  - SELECT {column} RIGHT JOIN test2.{column}=test.{column};

I think it is enought to conclude that the others columns will not  
cause problems (at least for MySQL and PgSQL).

> As for the column names that we should choose. Here are some  
> modifications to your proposal:
>
>  * items.left                 -> lft, left_val, left_value, left_bound
>  * items.right                -> rgt, right_val, right_value,  
> right_bound

I'm going to code that. I will use the proposition of Bharat :  
left_ptr and right_ptr. If it gets merged, I will code the rename of  
the columns "key" with your proposition (cache_key, msg_key and label)

Greetings.

------------------------------------------------------------------------------
__[ g a l l e r y - d e v e l ]_________________________

[ list info/archive --> http://gallery.sf.net/lists.php ]
[ gallery info/FAQ/download --> http://gallery.sf.net ]