SQLite autoincrement regex

Question

I am taking create statement queries from SQLite like this:

CREATE TABLE [users] ([id] INTEGER PRIMARY KEY AUTOINCREMENT, [username] VARCHAR, [password] VARCHAR, [default_project] VARCHAR) 

created by using

SELECT sql FROM sqlite_master WHERE type = 'table' AND name = :table

and determining the autoincrement field with a regular expression like this:

/\b\[?id\]?\s+INTEGER\s+PRIMARY\s+KEY\s+AUTOINCREMENT\b/Ui 

the problem is that there are different acceptable ways to write keywords such as "id", `id`, 'id'. Shown here http://www.sqlite.org/lang_keywords.html

I wanted to create a regular expression that would explicitly check for these different variations...with some help of others I have gotten to this:

$pattern = "/\b\"(id)|(\"id\")|(\[id\])|(`id`)|('id')\"\s+INTEGER\s+PRIMARY\s+KEY\s+AUTOINCREMENT\b/Ui";

however there are a couple problems with this...one being that the INTEGER PRIMARY KEY AUTOINCREMENT is no longer checked for...and that ('id') isn't being matched properly...however if I were to swap its place with the ("id")...than it would work and ("id") wouldn't....my regex skills are lacking...if someone could offer some insight, I would appreciate it

Answer 1

/(\"id\"|\[id\]|\'id\'|`id`|\\bid)\s+INTEGER\s+PRIMARY\s+KEY\s+AUTOINCREMENT/Ui

seems to work as intended.

Answer 2

[id] doesn't match "[id]" but only "i" and "d", what you're searching for is probably:

$pattern = "/\s(\"[a-z]+\"|\[[a-z]+\]|'[a-z]+'|[a-z]+)\s+INTEGER\s+PRIMARY\s+KEY\s+AUTOINCREMENT/Ui";

using [a-z]+ so that you can match any alphabetic field name and then find it in the first match (also notice that there can be rowids that don't use the AUTOINCREMENT keyword: they're both valid but with a slightly different meaning).