RFE: Rename Column

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

RFE: Rename Column

Elrond-3

Hi everybody,

Short: Could you implement alter table rename column?

I know that sqlite does only support a limited subset of
SQL. Some subsets have only been added lately, like foreign
keys, and can be disabled at compile time to safe space.

Renaming a table is already supported, which is good.

I hope, renaming a column should not be extremely complex.
There are even dangerous recipes out there [1] that
manipulate the internal structure (sqlite_master) using the
public API. So could those recipes be turned into
(hopefully safer) internal code?


Cheers

  Elrond

[1] http://stackoverflow.com/a/6684034/1890086
_______________________________________________
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: RFE: Rename Column

Igor Tandetnik-2
On 1/4/2014 7:15 PM, Elrond wrote:
> Short: Could you implement alter table rename column?

The problem would be, what to do with all the indexes, triggers, views
and foreign keys that reference that column? SQLite stores database
schema as text, in the form of CREATE... statements. I suspect it would
require heroic efforts to go over all those statements, find all
occurrences of X (where X is the old name of the column), and filter out
false positives (cases where X names something else, say a column in a
different table).
--
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: RFE: Rename Column

Petite Abeille-2

On Jan 5, 2014, at 6:56 PM, Igor Tandetnik <[hidden email]> wrote:

> On 1/4/2014 7:15 PM, Elrond wrote:
>> Short: Could you implement alter table rename column?
>
> The problem would be, what to do with all the indexes, triggers, views and foreign keys that reference that column?

Sure, but that’s an unrelated set of problems, isn’t it? As it stands, one cannot even rename a column.

(P.S. Other DBs tend to simply invalidate all the related objects and let the user sort it out)

_______________________________________________
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: RFE: Rename Column

Igor Tandetnik-2
On 1/5/2014 1:41 PM, Petite Abeille wrote:
> On Jan 5, 2014, at 6:56 PM, Igor Tandetnik <[hidden email]> wrote:
>
>> On 1/4/2014 7:15 PM, Elrond wrote:
>>> Short: Could you implement alter table rename column?
>>
>> The problem would be, what to do with all the indexes, triggers, views and foreign keys that reference that column?
>
> Sure, but that’s an unrelated set of problems, isn’t it? As it stands, one cannot even rename a column.

In SQLite, if you update just the CREATE TABLE statement but no other
parts of the schema, you may easily end up with a database that can't be
opened at all.

> (P.S. Other DBs tend to simply invalidate all the related objects and let the user sort it out)

If you are willing to let the user sort it out, then the existing
mechanism of directly manipulating sqlite_master should suffice.
--
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: RFE: Rename Column

Simon Slavin-3
In reply to this post by Petite Abeille-2

On 5 Jan 2014, at 6:41pm, Petite Abeille <[hidden email]> wrote:

> On Jan 5, 2014, at 6:56 PM, Igor Tandetnik <[hidden email]> wrote:
>
>> On 1/4/2014 7:15 PM, Elrond wrote:
>>> Short: Could you implement alter table rename column?
>>
>> The problem would be, what to do with all the indexes, triggers, views and foreign keys that reference that column?
>
> Sure, but that’s an unrelated set of problems, isn’t it? As it stands, one cannot even rename a column.

You're both right.  Igor's statement pretty-much /is/ the reason one cannot rename a column.  One would need to write a parser and changer for SQL statements that could identify and change column names in many statements with all sorts of weird possibilities for formatting.

Two alternatives: (a) actually write the parser-and-changer that processes SQL commands, or (b) wait until the major file format changes in SQLite4, then change the way SQL stores the CREATE commands needed to construct a database so it stores a structured version of the commands instead of the raw text.

The advantage of either change would be that it allows almost all the ALTER TABLE commands SQL users expect, not just changing column names.  This simplifies life not just for normal users but also for all the writers of SQLite GUI managers out there, who have to write nasty risky time-consuming hacks if they want to accomplish those operations.

Simon.
_______________________________________________
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: RFE: Rename Column

Elrond-3

Hi,

On Sun, Jan 05, 2014 at 22:58:31 +0000, Simon Slavin wrote:
[...]
> You're both right.  Igor's statement pretty-much /is/ the
> reason one cannot rename a column.  One would need to
> write a parser and changer for SQL statements that could
> identify and change column names in many statements with
> all sorts of weird possibilities for formatting.

Isn't most of the parser already existant, because sqlite
must parse the in-db-schema-sql to know the structure of
the database?
And there already must be code to identify column names in
the parsed structure, for exactly the above named reasons
(checking constraints, checking foreign keys)?

Obviously, the parser is not a parser-and-changer. Agreed.

What about this simple solution:

Let "alter table rename column" parse the schema, search
for the column name. If it's in no constraint, etc, then do
the rename in the sqlite_master table.  If the column is
used anywhere, error out (which is not worse than before).
This will at least stop people from creating databases,
that can't be opened after mangling around in the
sqlite_master table.

As originally stated, my aim is not a full blown alter
table implementation right away, but only renaming
(possibly unreferenced) columns.


> Two alternatives: (a) actually write the
> parser-and-changer that processes SQL commands, or

For creating the actual changer, one might use the already
parsed in-memory-structure, change it, and dump it back out
as sql.  That might break formatting for people who love
it, but that's still lots better than doing nothing.  Being
able to dump the internal structures as sql might also be
helpful for debugging and writing test cases.


> (b) wait until the major file format changes in SQLite4,
> then change the way SQL stores the CREATE commands needed
> to construct a database so it stores a structured version
> of the commands instead of the raw text.

The "structured version" could be the dumped-sql from above?

But of course, sqlite4 could use some fancy structured
format for that as well. But why invent a new (binary)
format, when we have a text based format at hand, that can
do everything needed?


> The advantage of either change would be that it allows
> almost all the ALTER TABLE commands SQL users expect, not
> just changing column names.

If that's true, that would be great!


> This simplifies life not just for normal users but also
> for all the writers of SQLite GUI managers out there,

Simplifying the world for everyone would be awesome.

Like the foreignkeys are awesome.  Instead of everybody
having to create (possibly wrong) triggers, those triggers
are now builtin.


> who have to write nasty risky time-consuming hacks if
> they want to accomplish those operations.

I am especially afraid of the risky part. That's not the
reliable attribute sqlite is known for.
That's why my suggestion from above is to start with simple
operations, and if sqlite can't do them reliably (because
it would break something), then error out.
That would even improve the gui managers: They could try
the sqlite internal variant (reliable), then ask the user
"Okay, the reliable method did not work, I have some
fallback methods, which are not as reliable, but used to
work most of the time.  Should I apply those?"


> Simon.

Cheers

  Elrond
_______________________________________________
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: RFE: Rename Column

phaworth
In reply to this post by Elrond-3
>
>
>
> You're both right.  Igor's statement pretty-much /is/ the reason one
> cannot rename a column.  One would need to write a parser and changer for
> SQL statements that could identify and change column names in many
> statements with all sorts of weird possibilities for formatting.
>
> Two alternatives: (a) actually write the parser-and-changer that processes
> SQL commands, or (b) wait until the major file format changes in SQLite4,
> then change the way SQL stores the CREATE commands needed to construct a
> database so it stores a structured version of the commands instead of the
> raw text.
>
> The advantage of either change would be that it allows almost all the
> ALTER TABLE commands SQL users expect, not just changing column names.
>  This simplifies life not just for normal users but also for all the
> writers of SQLite GUI managers out there, who have to write nasty risky
> time-consuming hacks if they want to accomplish those operations.
>
> Simon.
>

SQLiteAdmin is one of those "nasty, risky, time consuming hacks" :-)  It
provides a way to rename a column and deals with changing the name in all
the places it could occur in the database, plus just about any db structure
changes that you'd want to do.  Not as good as a built-in solution
admittedly but it's saved my bacon several times.  If interested, check it
out at www.lcsql.com.  Having written the code, I'd agree with the "nasty"
and "time consuming" adjectives, but not so much the "risky" since all the
changes are done within a transaction and I also offer the option of
backing up the database before making any changes.



Pete
lcSQL Software <http://www.lcsql.com>
_______________________________________________
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: RFE: Rename Column

decalek
In reply to this post by Simon Slavin-3
On 06.01.2014 00:58, Simon Slavin wrote:

>
> On 5 Jan 2014, at 6:41pm, Petite Abeille <[hidden email]> wrote:
>> On Jan 5, 2014, at 6:56 PM, Igor Tandetnik <[hidden email]> wrote:
>>> On 1/4/2014 7:15 PM, Elrond wrote:
>>>> Short: Could you implement alter table rename column?
>>>
>>> The problem would be, what to do with all the indexes, triggers, views and foreign keys that reference that column?
>>
>> Sure, but that’s an unrelated set of problems, isn’t it? As it stands, one cannot even rename a column.
>
> You're both right.  Igor's statement pretty-much /is/ the reason one cannot rename a column.  One would need to write a parser and changer for SQL statements that could identify and change column names in many statements with all sorts of weird possibilities for formatting.
>
> Two alternatives: (a) actually write the parser-and-changer that processes SQL commands, or (b) wait until the major file format changes in SQLite4, then change the way SQL stores the CREATE commands needed to construct a database so it stores a structured version of the commands instead of the raw text.

If someone have a little time to experiment with the (a) road, it is not
so complicated as it might look at the first glance: For example sqld3
[1] is a PEG parser which, as author claims, is derived from the
SQLite's railroad syntax diagrams, which in order are derived (I
believe) from the SQLite's sources (i.e. there are chances, that the
grammar is sound).

PEG [2], is the simplest possible kind of grammar machinery, (probably
easiest for understanding for non computer language experts) - no
scanner/parser split, no ambiguity, as people often said - something
like RegExps on steroids :-).

The above project is in Ruby (is there someone who reads Ruby to give
some test results?) and it is 3 years old, but this is not so important
- I think Richard and the team are able to point out even more clever
path for pure grammar extraction in sync with the latest SQLite sources.

Once a Language grammar is available for given PEG implementation it is
usually easy to translate it for another - because the PEG rules (for
the syntax rules :-) ) are basically the same everywhere.

There are hundreds of PEG implementations already - at least several per
language. My personal favorite is one of the smallest, pure C libs -
LPeg [3], which just like the SQLite itself compiles the grammar to the
VM code. LPegLJ [4] port of [3] even does not need a C compiler (the
source code - it is JIT-ed on demand)

Parsing is the first step. I think, it would be funny if the  dogfooding
principle for the second - transformation step is tried. i.e. when the
parse trees of SQlite SQL are stored back in (e.g. in memory) SQlite and
transformed there ;-).

BTW, transformation relaying on data stores are applied in one of the
modern products in that field - Rascal [5] (IMP PDB).

Another note: This topic seems somewhat related to the recent
discussions (e.g. CTEs) about SQLite RFEs which are statically
implementable (by rewriting, without changes to the SQLite engine)

Kind Regards,
Alek

[1] https://github.com/steveyen/sqld3
[2] http://en.wikipedia.org/wiki/Parsing_expression_grammar
[3] http://www.inf.puc-rio.br/~roberto/lpeg/
[4] https://github.com/sacek/LPegLJ
[5] http://www.rascal-mpl.org/

P.S. @Simon, and others closely following: Please point me to the docs
for the new style (structured) SQL objects representation in the SQlite4
- only tables? or scripts too?

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users