Cannot Re-edit Table (remove/rename columns, change column type)

[Former user]

Originally reported on Google Code with ID 12

What steps will reproduce the problem?

1. Create table "names".
2. Create two columns in the table -- first name and last name.
3. Try to edit the table and remove one of the columns, even with no data in it.
4. Says successful, but doesn't do it.

What is the expected output? What do you see instead?

Successful column deletion. I saw unsuccessful column deletion.

What version of the product are you using? On what operating system?

1.8.2 on Ubuntu 10.04 Linux

Please provide any additional information below.

Love what I see so far. Amazing that it's all in one single file.

Reported by volomike on 2011-04-20 05:19:55

[Dane Iracleous]

``` Could you tell me what version of PHP, what version of SQLite, what library extension you are using (PDO, SQLite3, SQLiteDatabase, etc.), and and version that library extension is at? Following the steps you described does not cause a problem - it works fine and the column gets deleted as it should. This means there is a difference between your setup and my setup.

I'm glad you like the tool! The single-fileness is definitely a plus. ```

Reported by `diracleo` on 2011-04-20 05:42:08 - Status changed: `Started`

[Former user]

``` PHP 5.3.2-1ubuntu4.7

PDO

PDO support => enabled PDO drivers => mysql, pgsql, sqlite, sqlite2

pdo_sqlite

PDO Driver for SQLite 3.x => enabled SQLite Library => 3.6.22

SQLite

SQLite support => enabled PECL Module version => 2.0-dev $Id: sqlite.c 293036 2010-01-03 09:23:27Z sebastian $ SQLite Library => 2.8.17 SQLite Encoding => UTF-8

Directive => Local Value => Master Value sqlite.assoc_case => 0 => 0

sqlite3

SQLite3 support => enabled SQLite3 module version => 0.7-dev SQLite Library => 3.6.22

Directive => Local Value => Master Value sqlite3.extension_dir => no value => no value

```

Reported by `volomike` on 2011-04-20 05:46:35

[Dane Iracleous]

``` So, on the main page for the database, what are the values for:

SQLite version: x SQLite extension: y

?

They should be x=3 and y=PDO

Is that right? ```

Reported by `diracleo` on 2011-04-20 06:01:21

[Former user]

``` SQLite version: 3 SQLite extension: PDO

Yes, correct.

```

Reported by `volomike` on 2011-04-20 06:04:57

[Dane Iracleous]

``` I have no idea why this is happening for you. This seems to be a common theme with these issues - reproducible by one person, and not for another. Ian, do you know what's up... ```

Reported by `diracleo` on 2011-04-20 07:31:16

[Former user]

``` Same issue here.

SQLite version: 3 SQLite extension: PDO PHP version: 5.3.1

Error I get when I try to delete is: An error occured. This may be a bug that needs to be reported at code.google.com/p/phpliteadmin/issues/list ```

Reported by `gyssler` on 2011-04-27 00:24:55

[Former user]

``` Just tested on a cPanel install as well, didn't work:

SQLite version: 3 SQLite extension: PDO PHP version: 5.3.4 ```

Reported by `gyssler` on 2011-04-27 01:16:41

[Dane Iracleous]

``` SQLite currently does not allow a full set of ALTER TABLE options. The way that phpLiteAdmin allows editing, deleting, and adding columns after the table has already been created is complicated and involves creating temporary tables and other hacks.

This area of the tool is the most delicate, and I'm not surprised that there are problems with it. Could you describe the structure of the table before trying to delete, which column you are deleting, and any other relevant info? ```

Reported by `diracleo` on 2011-04-28 04:31:07

[Former user]

``` Is there a way i can e-mail you? I'll send you the URL to a running version of phpliteadmin on my box, and you can try to create/delete a simple two column table. You should get the error, there's nothing complicated about the tables. ```

Reported by `gyssler` on 2011-04-28 17:33:24

[Dane Iracleous]

``` Sure go ahead and email me at [e-mail]. ```

Reported by `diracleo` on 2011-05-31 08:00:57

[Dane Iracleous]

``` Did you email me yet? ```

Reported by `diracleo` on 2011-06-08 15:24:52

[Former user]

``` I just tested again and this bug is unresolved on the latest stable version 1.8.6 that I just downloaded last week.

OS: Ubuntu 10.04.2 LTS Kernel: Linux 2.6.32-32-generic #62-Ubuntu SMP Wed Apr 20 21:54:21 UTC 2011 i686 GNU/Linux SQlite version: 3 SQLite extension: PDO PHP version:

PHP 5.3.2-1ubuntu4.9 with Suhosin-Patch (cli) (built: May 3 2011 00:43:34) Copyright (c) 1997-2009 The PHP Group Zend Engine v2.3.0, Copyright (c) 1998-2010 Zend Technologies with the ionCube PHP Loader v4.0.8, Copyright (c) 2002-2011, by ionCube Ltd.

There's nothing special about the table. I explain everything in the original report, along with the steps to recreate the problem.

```

Reported by `volomike` on 2011-06-08 21:05:31

[Former user]

``` Perhaps you can show me how to set the script in debug mode so that all errors are written out?

```

Reported by `volomike` on 2011-06-08 21:20:04

[Former user]

``` I'm also getting this. ```

Reported by `darktemp69` on 2011-08-27 20:52:44

[Dane Iracleous]

``` There's a debug variable available in v1.8.9 that might help you. ```

Reported by `diracleo` on 2011-10-25 10:14:59

[Christopher Kramer]

``` I have the same problem, but not when deleting a column but when renaming it or changing the data type. Sometimes it says the table was altered successfully, but nothing happens. Sometimes an error happens.

How I can reproduce at the moment: 1. CREATE TABLE test_table(test_column INTEGER PRIMARY KEY NOT NULL) 2. edit the column test_column into test_column2 3. error: Error: near "test_column2": syntax error.

SQLite version: 3.7.7.1 SQLite extension [?]: PDO PHP version: 5.3.8 ```

Reported by `crazy4chrissi` on 2011-12-03 16:05:25

[Christopher Kramer]

``` Issue 101 has been merged into this issue. ```

Reported by `crazy4chrissi` on 2012-06-02 20:21:38

[Christopher Kramer]

``` This bug is just very annoying, so I mark it high-priority now. ```

Reported by `crazy4chrissi` on 2012-06-02 20:40:34 - Labels added: Priority-High - Labels removed: Priority-Medium

[Former user]

``` Agree with you. Basic functionality ))

03.06.2012 02:40, [e-mail] написал: ```

Reported by `[e-mail]` on 2012-06-04 05:57:20

[Christopher Kramer]

``` Including all associated problems in the title as quested in duplicate issue #101.

Also, here is a good link posted by [e-mail] (just so it is not lost...):

http://stackoverflow.com/questions/805363/how-do-i-rename-a-column-in-a-sqlite-database-table ```

Reported by `crazy4chrissi` on 2012-06-04 17:46:17

[Christopher Kramer]

``` I am currently working on this issue. I could not believe my eyes when I saw the way phpliteadmin implements this (or, to be precise, the code we borrow for doing this: http://code.jenseng.com/db/). The code is so naive that it hurts.

While debugging this, I am nearly doing a complete rewrite. Dropping columns already works pretty reliably for SQLite3. SQLiteDatabase still causes some typing problems (never thought I'd have typing problems in SQLite...). I am working on changing columns at the moment. Adding columns and rename should be pretty simple then. ```

Reported by `crazy4chrissi` on 2012-10-06 23:11:59

[Christopher Kramer]

``` More progress on this issue. Dropping, changing and adding (for SQLiteDatabase) "works" now. It still needs some debugging, but I will soon give you a version to test this. It will need a lot of testing, as the whole approach of parsing SQL really depends a lot on the syntax used and SQL allows for several variations I might not have thought of yet.

After the currently "implemented" features have been debugged, we might add new features like: - add a new column somewhere in the middle (quite simple) - changing column constraints (NOT NULL, auto increment, default value) - moving columns - ... ```

Reported by `crazy4chrissi` on 2012-10-07 23:26:55

[Christopher Kramer]

``` Okay, so I committed my current version of the reworked ALTER TABLE support to svn. You can get it here: https://phpliteadmin.googlecode.com/svn/source/1.9.3/phpliteadmin.php

I'd be pleased if somebody tested this. It is not perfect yet. Some remarks: - backup your db before using this! (But the current stable version is even more dangerous I'd say ;-) ) - does not implement table-renames yet (only affects SQLite2, SQLite3 can do this on its own) - will drop indexes/triggers associated with the table (except primary keys)

If it does not work, please provide: - the CREATE TABLE statement of the table you tried to alter (you can get this on the structure-tab) - the action you tried to perform (drop/change/add? which columns?) - what it did (error? no error but no action? messed up something?)

Thanks! ```

Reported by `crazy4chrissi` on 2012-10-08 23:23:25

[Christopher Kramer]

Some more debugging done. I added optional debugging output. If you run into a problem,
please set $debug=true; and try again, then post the output here.

Reported by crazy4chrissi on 2012-10-10 22:12:44

[Former user]

Got an extrajob for a small web-catalogue. Will try your new version in action. You
are doing a needful job. Thanks a lot.

PS : I wonder if i could just give phpliteadmin to a non-IT customer to add/remove
items from his own website. Like phpmyadmin. But it's quite raw/buggy for doing that
now.

Reported by [e-mail] on 2012-10-14 08:07:46

[Christopher Kramer]

Just implemented ALTER TABLE RENAME TO for SQlite version2 databases in revision #244.


You can get the latest development version from svn here:
http://phpliteadmin.googlecode.com/svn/source/1.9.3/phpliteadmin.php

Reported by crazy4chrissi on 2012-10-25 20:24:08

[Dane Iracleous]

This improvement definitely warrants a new release. Let me know when you get it to a
stable enough state that we can include it in v1.9.3

Reported by diracleo on 2012-10-26 01:36:32

[Christopher Kramer]

Yes, I want to implement recreation of triggers and indexes and do some more testing
and then we can release 1.9.3

Reported by crazy4chrissi on 2012-10-26 08:10:05

[Christopher Kramer]

Some more problems I still need to fix. I'll write them down here so I won't forget
and you can track progress:

1. SQLite accepts any kind of non-SQL-standard quoting identifiers and values, so this
is legal:
CREATE TABLE [my_table] ('corr''ect_sql' INTEGER, "wr""ong" TEXT, `wr``ong` TEXT, [wrong2]
TEXT)
In sqlite_master, we get this original CREATE TABLE statement back, so my function
currently would run into problems here.

Funny aside: SQLite does not reliably allow to use [wro]]ng]. I looked into SQLite's
C-Code and found that sqlite3Dequote() does this correctly, but sqlite3GetToken() has
an error that prevents this in at least some cases (Line 111008 in current sqlite3.c
amalgamation-version 3.7.14.1). Maybe I should open a bug at their bug tracker / send
them a patch.

2. SQlite has even weaker typing than I expected. You can create any type on-the-fly
like this:
CREATE TABLE my_table('a' my_new_type)
No need for
CREATE DISTINCT TYPE my_new_type AS TEXT;
or something like that.
Currently my function hard-codes possible types, so this would run into problems.
Easy to fix by getting the current datatype using PRAGMA table_info()

3. I implemented recreation of triggers & indexes, but this does not work yet with
REANAME TO (sqlite2 only) because the table-name needs to be exchanges in the CREATE
TRIGGER/INDEX statement.

So long for the moment.

Reported by crazy4chrissi on 2012-10-27 23:39:20

[Former user]

i didn't get all the issues you've described here, but can remember some troubles with
phpsqliteadmin when you've just come into this project. somewhere around 1.8 version.
it should be actual in new version too. :

1. i've made a big bug about quotation. quotes in names of the records, as i expect,
should work as good as in sqlite3 binary under linux and under the firefox-sqlite-manager-plugin.
actually i don't think that weirdos like " 'corr''ect_sql' INTEGER, " are realy needful
for people. just compare quotation with mentioned tools. i still import huge cvs via
sqlite3 and bash scripts to create initial sqlite db.

2. types. there are more types (~10) in official documentation, sqlite-manager from
firefox. and may be in sqlite3 binary than in phpliteadmin. it may lead to inconcistance
somehow... maybe... for example date(now) - doesn't work in phpsqliteadmin, but it
works in sqlite3. it's generic.

3. in older versions i couldn't rename tables. didn't track it for new versions yet.
may be it fixed...

thanks a lot for your work !

Reported by [e-mail] on 2012-10-28 07:15:02

[Christopher Kramer]

Thanks for your feedback. I will also have a look at the firefox-plugin you mentioned.

1. Quotes in records work really fine in current svn version, no problem here any more.
I am aware that probably (hopefully) not many users use special characters like quotes
in their column- or table-names, but as sqlite permits them to do, I will support it.

2. You are not completely correct about the number of official types I think. See https://www.sqlite.org/datatype3.html
There are only 5 internal types ("storage classes") in SQlite3. Date for example can
be one of INTEGER, TEXT or REAL, but is not really a special type of its own. You could
use "my_date" instead of "DATE" and it would work the same way. But of course you are
correct that DATE is used very often and lots of other tools treat it as a special
type so we should support it as well. We will do so in future versions. Maybe we will
also support custom type-names as well, although I don't really see the point of them.
We have distinct types in SQL because they are distinct, i.e. we have strong typing.
But SQlite does have very weak typing, so introducing a new type-name is more an annotation.

3. Renaming tables is supported by sqlite3 on its own. Problem is sqlite2 doesn't support
it, so this is why I implemented this in the alterTable function.

Reported by crazy4chrissi on 2012-10-28 12:26:41

[Former user]

just assembled a new website. tried to use phpliteadmin only. it was unsuccessfull.
there are some bugs with sqlite3 DB:
1. i still can't edit existing row parameters in a table.
a) it allows to reedit only name and type of the field (column). 
b) it doesn't do that actually. shows that all is done, but. nothing changed. achtung!
))
2. i can't create name of field like 'e-mail'. because of '-' symbol. but it's possible
to create such a field in firefox-sqlite-manager.

check it right now (it's quick and easy to reproduce) and create/extend existing bugs
if needed.

Anyway it gets better. Thanks.

Reported by [e-mail] on 2012-10-28 13:02:42

[Christopher Kramer]

Thanks again for your feedback. Which version did you try? Please try the SVN version:
http://phpliteadmin.googlecode.com/svn/source/1.9.3/phpliteadmin.php

1.a) that's still true. We'll work on that in future versions.
1.b) should be fixed in SVN as long as you are not affected by one if the points I
listed above
2. Please try this in the SVN version. Should work there.

Reported by crazy4chrissi on 2012-10-28 13:06:27

[Former user]

it was 1.9.2 . thanks, i'll check SVN.

Reported by [e-mail] on 2012-10-28 15:33:42

[Former user]

SVN's 1.9.3 :
1. editing of fields works fine
2. i can create names of fields with '-' symbol

Big thanks for your work !

Reported by [e-mail] on 2012-10-28 15:37:37

[Christopher Kramer]

I fixed the first 2 problems I mentioned in comment #30.

For 2., maybe it would be nice to let the user select the old type, but this is rather
an enhancement than a defect. It now works as expected, replacing the old type with
the new selected one.

Issue 3. from comment #30 is still to be done (recreating triggers/indexes for the
correct table name after a rename).

Reported by crazy4chrissi on 2012-10-28 22:45:30

[Christopher Kramer]

Issue 116 has been merged into this issue.

Reported by crazy4chrissi on 2012-10-30 22:35:31

[Christopher Kramer]

*This comment continues with the numbering I started in comment #30 *

-- 3. --
I worked on the third issue mentioned in comment #30.

-- 3.a -- DONE
This means you can now rename tables in a SQLIte2-DB which has indexes assigned to
it and it will keep (recreate) the indexes.

-- 3b. -- TODO
The same thing is still missing for recreation of triggers. I'll fix this soon. It's
only that the CREATE TRIGGER statement is a little more complex than the CREATE INDEX
statement and I just did not feel like writing the regex for this now ;-)
Coming soon...

-- 4a. -- TODO
I got another idea: When dropping or renaming a column with an index assigned, this
won't work at the moment. I guess one would expect (or at least "hope"...) that the
index is automatically dropped/adjusted as well in this case.
I'll implement this as well.

-- 4b. -- WON'T FIX
I guess the same thing is not easily implementable for triggers. Triggers can contain
column-names in the WHEN-clause and in the statements between BEGIN and END as well.
The ALTER TABLE statement might change these column-names so recreation will fail.
But it would be _very_ hard to reliably replace changed column names in there. We'd
need to build nearly a complete SQL parser. The column might even be referenced in
a trigger of another table, so we'd need to check all triggers of all tables. I guess
I'll stop at this point. I'm not even sure if other DBs that fully implement ALTER
TABLE do this cleverly. I'd say it's no shame not to support this ;-)
Do other tools support this?

-- 5. -- TODO
I forgot about Foreign keys so far. I have not tried yet what happens if columns are
dropped or renamed and there is a foreign key relationship to this column. I guess
the relationship is being dropped, or the transaction fails.
I guess I'll need to have a look at this as well.
But as long as we do not really support foreign keys, maybe we can defer this to the
next release (as long as the result of an ALTER TABLE does not break anything important
here).

Reported by crazy4chrissi on 2012-11-02 20:46:04

[Christopher Kramer]

Issue 21 has been merged into this issue.

Reported by crazy4chrissi on 2012-11-06 15:22:37

[Christopher Kramer]

Issue #143 is an example where it did not work yet. I guess I'll merge #143 into this
one soon. I fixed the particular problem of #143 in svn and improved error handling.
Now proper error messages are displayed if alter table fails.

Reported by crazy4chrissi on 2012-11-13 21:39:20

[Christopher Kramer]

Issue 143 has been merged into this issue.

Reported by crazy4chrissi on 2013-03-01 00:11:44

[Christopher Kramer]

Let's hope I get this closed completely in 1.9.5

Reported by crazy4chrissi on 2013-03-19 23:31:34 - Labels added: Target-1.9.5

[Christopher Kramer]

Another concrete problem with ALTER TABLE has been discovered. See issue #202.

Reported by crazy4chrissi on 2013-03-21 23:24:26

[Christopher Kramer]

Issue #202 has been fixed.

As I lately set up the demo with the Chinook sample db, I noticed altering some of
the tables in there also fails (without a proper error message, like issue #203). I
guess mostly the use of foreign keys or indexes on these columns in there is the cause.
But before I close this issue here, I should make sure altering the chinook db tables
works, or if not possible, at least gives a reasonable error message.

Reported by crazy4chrissi on 2013-03-25 10:51:50 - Blocked on: #203

[Christopher Kramer]

-- 4a. -- 
"When dropping or renaming a column with an index assigned,one would expect that the
index is automatically dropped/adjusted as well in this case."

I just implemented this partly. In case of dropping a column, all indexes that refer
to the column now get dropped implicitly.
Note that one can argue whether this is expected behaviour, but SQLite also implicitly
drops indexes and triggers if you drop a table. So I'd say that this is in the spirit
of SQLite.

In case of renaming a column with an index assigned, the query still will fail. I still
need to implement this.

Reported by crazy4chrissi on 2013-04-26 22:55:40

[Former user]

I think renaming a column should result in reflecting this change in indexes and triggers.
Dropping them isn't in the spirit of making working with SQLite easier.

Reported by romanrmr on 2013-04-28 00:22:52

[Christopher Kramer]

"I think renaming a column should result in reflecting this change in indexes and triggers."
Yes. For indexes, I will implement this for sure. But I guess for triggers this will
get too complicated: In a CREATE TRIGGER statement, references to the column are between
BEGIN and END inside SELECT, DELETE, UPDATE or INSERT statements. If you only imagine
the complexity of parsing a SELECT statement, it is really tricky to replace column
names in there correctly.

"Dropping them isn't in the spirit of making working with SQLite easier."
I am not sure what you mean here. Do you mean dropping indexes/triggers in case of
a rename of a column or in case of dropping a column?
I think in case a column is renamed, I agree that triggers and indexes must not be
dropped. I probably did not make it clear above. When I said "I still need to implement
this", I meant that the change gets reflected in the index. Dropping would be very
easy, but not a good solution.

In case a column is dropped, I think implicitly dropping indexes makes working with
SQLite easier. If you want to get rid of a column, you don't care about the index.
So it would be less easy if you get an error message and have to go back, drop the
index yourself and then drop the column again.
As I said, when you drop a table, SQLite also implicitly drops everything assigned
to it. So I think it is intuitive if the same happens if you drop a column with an
index assigned.

Reported by crazy4chrissi on 2013-04-28 12:08:26

[Former user]

Yes, you are right. I misunderstood you. Dropping a column should result in dropping
indexes and triggers associated with it.

Regarding renaming a column... wouldn't simple match and replace be a good approach
to reflecting column renames in triggers?

Reported by romanrmr on 2013-04-28 15:03:10

[Christopher Kramer]

| Regarding renaming a column... wouldn't simple match and replace be a good approach

| to reflecting column renames in triggers?


Unfortunately no. Your column name could occur within the CREATE TRIGGER statement
at lots of places where it does not refer to your column but in a different context.
The most simple example is something like this:
SELECT a.foo FROM a, b WHERE a.foo=b.foo
Now if you rename foo of table a into foo2 and do a simple search&replace, the statement
would look like this:
SELECT a.foo2 FROM a, b WHERE a.foo2=b.foo2
But b.foo2 does not exist because it is still called b.
This is even an example that I consider quite realistic.

Moreover, you can have column names like "select" in SQLite. If you rename "select"
to "bar", you would get:
bar a.foo FROM a, b WHERE a.foo=b.foo

I could give you hundreds of other examples where this would not work. It is far too
dangerous to do a search & replace as things might happen that the user does not expect
and does not even notice. As triggers really execute stuff, it is very dangerous to
blindly mess around in there.

The only thing I could think of:
We give a message like:
"You rename a column and there is a trigger that seems to refer to that column name.
You need to adjust the trigger to be able to complete the renameing of the column.
Please correct the following CREATE TRIGGER statement:"
And then we will write the CREATE TRIGGER statement and everywhere the column name
occurs, the user can choose (e.g. using a drop down) whether this should get replaced
by the new column name or not.

For the user, this task should be comparably simple (at least if he knows SQL) whereas
for phpLiteAdmin, it is rather tricky.

Reported by crazy4chrissi on 2013-04-29 09:46:42

[Christopher Kramer]

"But b.foo2 does not exist because it is still called b." should be:
"But b.foo2 does not exist because it is still called foo."

Reported by crazy4chrissi on 2013-04-29 09:48:30

[Former user]

You are correct, and I hadn't given this enough thought when I made my proposal.

Your suggestion seems user-friendly enough.

Reported by romanrmr on 2013-04-29 10:38:15

[Christopher Kramer]

Here is a summary of the todos left (6a and b are new.)

-- 3b. -- TODO
When renaming a table (SQlite2 only), triggers need to be recreated for the correct
table.

-- 4a1. -- TODO
Renaming a column with an index assigned, the index should get adjusted.

-- 4b. -- TODO (MAYBE)
When renaming a column with a trigger referring to it, the trigger needs to get adjusted.
As automatically adjusting the CREATE TRIGGER statement is rather complex, user interaction
could solve this (as described in comment 50).

-- 5a. -- TODO
When dropping a column with a foreign key reference, this reference should get dropped
implicitly.

-- 5b. -- TODO
When renaming a column with a foreign key reference, this reference should get adjusted.

-- 6a. -- TODO
When dropping a column which is (part of) a primary key, the primary key should get
dropped implicitly. (And there should be another option add/change primary keys.)
Same for UNIQUE table-constraints.

-- 6b. -- TODO
When renamimg a column which is (part of) a primary key, the rename should be reflected
in the primary key definition.
Same for UNIQUE table-constraints.

Reported by crazy4chrissi on 2013-05-01 21:17:37

[Christopher Kramer]

6a and 6b already work if the definition was done through a column-constraint rather
than a table-constraint. So in other words, this usually already works for primary
keys if they are not compound primary keys.

Reported by crazy4chrissi on 2013-05-01 21:20:20

[Former user]

Why the trigger statment ? ... that's something that took my head in mysql ... long
time ago ...

Another way : tested quickly in sqlite 3. 

//1. create a temp table with new id
CREATE TEMPORARY TABLE TEMP_TABLE(id2 INTEGER PRIMARY KEY);
//2. insert value from original table
INSERT INTO TEMP_TABLE SELECT id FROM test_table;
//3. delete the original table
DROP TABLE test_table;
//4. create a new empty table with the rename id 
CREATE TABLE test_table (id2 INTEGER PRIMARY KEY);
//5. add data
INSERT INTO test_table SELECT id2 FROM TEMP_TABLE;
//6. the end of temp
DROP TABLE TEMP_TABLE;

Note : I'm not sure but in my mind foreign key isn't sqlite official ... the problem
isn't solve by previuos sql string.
So think, tempory data, pragma infos, and also hidden or not tables for specific sql
(and not sqlite)data, functions, views, etc ... 

Note : this method should be use to change data type also.

Reported by 1stKraven on 2013-05-30 23:17:14

[Christopher Kramer]

> Why the trigger statment ? 

I don't understand what you mean with this. Why we have triggers in sql at all? Maybe
just have a look at wikipedia: https://en.wikipedia.org/wiki/Database_trigger#The_need_and_the_usage

> Another way : tested quickly in sqlite 3. 
That's more or less exactly the way phpLiteAdmin alters tables if the corresponding
alter table statement is not supported by SQLite.  The problem is just that it is not
that trivial to build the right sql statements correctly in all cases because the table
you are altering can have lots of stuff like column constraints (e.g. UNIQUE), Indexes,
Triggers, compound primary keys etc. and we have to cope with all these possibilities.

> Note : this method should be use to change data type also.
It is.

> Note : I'm not sure but in my mind foreign key isn't sqlite official ...
In my mind it is as of SQLite 3.6.19  ;-)
http://www.sqlite.org/foreignkeys.html

Reported by crazy4chrissi on 2013-05-31 09:01:20

[Christopher Kramer]

I improved the regex to match the columns before with r441 which should solve lots of
problems with changing or dropping columns.

Reported by crazy4chrissi on 2013-12-26 17:45:58

[Christopher Kramer]

With r444, it is now possible to add primary keys (only if the table has no primary
key yet).

Reported by crazy4chrissi on 2013-12-29 23:01:00

[Christopher Kramer]

Reported by crazy4chrissi on 2014-01-02 12:42:21 - Labels added: Target-1.9.6 - Labels removed: Target-1.9.5

[Christopher Kramer]

I created individual tickets for all the remaining sub-issues.

• issue #279: recreate trigger with new name after renaming a table (SQlite2 only)
• issue #280: Renaming a column with an index assigned, the index should get adjusted.
• issue #281: drop foreign key when removing the column that it refers to
• issue #282: Adjust foreign key references when referred column gets renamed
• issue #283: Adjust primary key when its column is renamed
• issue #284: Drop primary key if its column is dropped

Therefore, I will now close this issue sigh Thanks for all the feedback. If you encounter any new problem editing a table, please open a separate issue and don't post it here. Thanks.