ALTER TABLE: Adding columns to a table with compound primary key fails (SQLite v2 only)

[Christopher Kramer]

Originally reported on Google Code with ID 202

What steps will reproduce the problem?
1. Create a table like this:
CREATE TABLE 'books' ('author' TEXT NOT NULL, 'title' TEXT NOT NULL, PRIMARY KEY ('author',
'title'))
2. Try to add columns:
ALTER TABLE "books" ADD 'year' INTEGER

What is the expected output? What do you see instead?
Expected: column added successfully
Actual output: ERROR: ERROR: Altering of Table books failed - Table books does not
exist.

PLA Version: 1.9.4
SQLite version: 2.8.17
SQLite extension [?]: SQLiteDatabase
PHP version: 5.3.3-7+squeeze15


Generally, ALTER TABLE problems are issue #12. This is a concrete example of what still
does not work.

Reported by crazy4chrissi on 2013-03-21 23:14:33

[Christopher Kramer]

Moreover, we should get rid of the 2nd "ERROR".

Reported by crazy4chrissi on 2013-03-21 23:16:51

[Christopher Kramer]

The same problem is likely to happen with any other table-constraint like CONSTRAINT,
CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE.
See https://www.sqlite.org/syntaxdiagrams.html#table-constraint

We probably need to add some lookahead in $preg_column_definiton that makes sure no
column definition starts with one of these keywords.

Reported by crazy4chrissi on 2013-03-21 23:32:29

[Christopher Kramer]

The Debug output

Reported by crazy4chrissi on 2013-03-22 11:12:20

<hr> * Attachment: alter_PM_debug.txt

[Christopher Kramer]

Note: This issue only affects SQLite v2 DBs because v3 supports ALTER TABLE ADD and
therefore, our own alterTable() function is not used in this case.

I am currently debugging this. A negative lookahead in $preg_pattern_add and catching
table-constraints behind will do the job.

Reported by crazy4chrissi on 2013-03-22 12:44:30 - Status changed: Started

[Christopher Kramer]

This issue was closed by revision r379.

Reported by crazy4chrissi on 2013-03-22 15:57:59 - Status changed: Fixed

[Christopher Kramer]

I just fixed this using a negative lookahead. Should work for all table-constraints.

I think we can include this in 1.9.4.1

Reported by crazy4chrissi on 2013-03-22 15:59:50 - Labels added: Target-1.9.4.1