Need to be able to rename an attribute

[Former user]

Originally reported on Google Code with ID 21

I know that SQLite doesn't make it easy, but SQLite DB management software needs to
make it possible because it's a real-world need. 

And of course, it needs to handle everything that is attached or associated with a
table that is going to be dropped during renaming an attribute - triggers, views, etc.

Reported by romanrmr on 2011-05-13 22:31:06

[Dane Iracleous]

``` Very good suggestion. If you look in the Database class, there is a function called alterTable. This is where the renaming of attributes would need to be implemented. If someone wants to take on this task, go ahead. I don't have the time right now. ```

Reported by `diracleo` on 2011-05-22 06:34:46

[Former user]

``` I will do terrible things to good people if someone would be willing to integrate alterTable.

As it stands, phpLiteAdmin is almost unusable to me due to lacking this feature.

Amazing job on everything else. ```

Reported by `[e-mail]` on 2011-05-25 20:53:16

[Former user]

``` Dude (#2), your first sentence will prevent people from implementing this feature if they don't want good people to suffer :) ```

Reported by `romanrmr` on 2011-05-25 22:41:15

[Dane Iracleous]

``` You can now edit the table column field and type as of v1.8.9 ```

Reported by `diracleo` on 2011-10-27 01:09:43 - Status changed: `Fixed`

[Former user]

``` Does it deal with triggers, views, etc that may be associated with an existing field? ```

Reported by `romanrmr` on 2011-10-27 01:38:25

[Dane Iracleous]

``` The triggers, views, etc are not affected. Only the field name and/or type are affected. So if a field has a trigger before editing it, it will still have the trigger after editing it. ```

Reported by `diracleo` on 2011-10-27 02:56:33

[Dane Iracleous]

``` Actually I'm not sure if my last statement is correct. To be honest, I'm not an expert on SQL so I don't know if the triggers and views would need to be updated to reflect the changed field name. Does anyone know? ```

Reported by `diracleo` on 2011-10-28 01:10:29

[Dane Iracleous]

Reported by `diracleo` on 2011-10-28 01:10:44 - Status changed: `Started`

[Dane Iracleous]

``` I reopened the issue to see if the associated triggers and views become broken after changing the name of a column. ```

Reported by `diracleo` on 2011-10-28 01:12:04

[Former user]

``` Are you the one who has implemented the feature?

As far as I know, SQLite doesn't support renaming attributes directly and, subsequently, this has to be done as a hack: create a new table, copy data from the existing table along with triggers, indexes and what not, and then delete the existing table and rename the new table into what the old table was named.

Please see http://sourceforge.net/tracker/?func=detail&aid=3388093&group_id=264133&atid=1127745 (the comment from the developer). ```

Reported by `romanrmr` on 2011-10-28 01:29:19

[Former user]

``` The ::alterTable() method creates also a new table and inserts the data into the new table. So either all indexes and triggers will get lost or the edit will fail. I can't test it because editing column names is erroneous and i get different errors.

imho the ::alterTable() method should get replaced. Working with regular expression on sql statments does not seems like a good idea. I would suggest to read the table information with "PRAGMA table_info(tableName)" and use that information to build the CREATE statment; copy the data with "INSERT INTO <target> SELECT FROM <source> and rebuild the triggers and indexes. Also "BEGIN TRANSACTION" and "END TRANSACTION" should be used. ```

Reported by `joe.scylla` on 2011-12-05 15:26:16

[Former user]

``` Everything that Joe said seems correct. ```

Reported by `romanrmr` on 2011-12-05 16:15:18

[Former user]

``` i can't rename field by clicking 'edit' in 'structure' part of an existing table. it writes "Table 'test' has been altered successfully." but field still named as before.

SQLite version: 3.7.7 SQLite extension [?]: PDO PHP version: 5.3.6-13ubuntu3.6 ```

Reported by `[e-mail]` on 2012-04-09 13:08:17

[Christopher Kramer]

This is another concrete example of (famous) issue #12.

I did a lot of work on this and it should work a lot better now.

Please have a try with the version in SVN:
http://phpliteadmin.googlecode.com/svn/source/1.9.3/phpliteadmin.php

Also refer to issue #12 for more information on this.

About what joe said:
- I think regular expressions are not the worst idea, but the implementation in 1.9.2
and before did not really use regex, it was all really naive working on strings without
thinking of any special case at all. The author of this clearly did not really know
how to use regular expressions.
- I used PRAGMA table_info to some extent
- I used transactions (of course!)
- rebuilding indexes and triggers is not yet done but should be very easy.

Reported by crazy4chrissi on 2012-10-25 21:25:58

[Christopher Kramer]

With 1.9.3, this is now "almost fixed". In case somebody still runs into a problem,
please see issue #12 or open a new issue.

Just another remark on what joe said: So there are 2 approaches of creating the CREATE
TABLE statement of the new table. The one we use is adjusting the old one. The one
you proposed would be creating a new one. Problem with that approach is that there
could have been a lot of stuff in the CREATE TABLE statment before that you cannot
easily recreate from data you get by PRGAMA table_info(). You get the list of columns,
but you do not get all the column-constraints, table-constraints, foreign-keys and
so on easily. (At least I did not find a way to do so yet). And once SQLite offers
something new, you would need to add this as well.

So the way I went was debugging the current approach using proper regex to parse the
SQL of the old CREATE TABLE statement and adjust it.

I'll mark this issue as a duplicate of issue #12 now. We should really should not have
more than one issue for this any longer.

Reported by crazy4chrissi on 2012-11-06 15:22:36 - Status changed: Duplicate - Merged into: #12