Need to be able to rename an attribute
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
Comments (15)
-
-
Account Deleted ``` 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 `julian@beemmail.com` on 2011-05-25 20:53:16
-
Account Deleted ``` 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
-
``` 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`
-
Account Deleted ``` 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
-
``` 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
-
``` 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
-
Reported by `diracleo` on 2011-10-28 01:10:44 - Status changed: `Started`
-
``` 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
-
Account Deleted ``` 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
-
Account Deleted ``` 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
-
Account Deleted ``` Everything that Joe said seems correct. ```
Reported by `romanrmr` on 2011-12-05 16:15:18
-
Account Deleted ``` 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 `master@remort.net` on 2012-04-09 13:08:17
-
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 -
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 - Log in to comment
``` 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