My project involves running ad hoc SQL statements against a SQLite database. I'm looking for a JavaScript regex expression to parse out the ALTER, CREATE and UPDATE sql statements. Here's examples and the results I'm looking for.

Create Index

CREATE INDEX Christy...
CREATE INDEX IF NOT EXISTS Christy...
CREATE UNIQUE INDEX Christy...
CREATE UNIQUE INDEX IF NOT EXISTS Christy...

Result

a = [..."CREATE", "INDEX", "Christy"]

---

Create Table Without Table开发者_运维问答 Constraint

CREATE TABLE Vicky (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,score REAL DEFAULT .10 CHECK (weight<=1));
CREATE TABLE IF NOT EXISTS Vicky (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,score REAL DEFAULT .10 CHECK (weight<=1));

Result

a = [..."CREATE", "TABLE", "Vicky", "id INTEGER PRIMARY KEY AUTOINCREMENT","name TEXT","score REAL DEFAULT .10 CHECK (weight<=1)"]

---

Create Table With Table Constraint

CREATE TABLE Vicky (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,score REAL DEFAULT .10 CHECK (weight<=1)),PRIMARY KEY (id, name));
CREATE TABLE IF NOT EXISTS Vicky (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,score REAL DEFAULT .10 CHECK (weight<=1)),PRIMARY KEY (id, name));

Result

a = [..."CREATE", "TABLE", "Vicky", "id INTEGER PRIMARY KEY AUTOINCREMENT","name TEXT","score REAL DEFAULT .10 CHECK (weight<=1)","PRIMARY KEY (id, name))"]

---

Create Trigger

CREATE TRIGGER Arwen...
CREATE TRIGGER IF NOT EXISTS Arwen...

Result

a = [..."CREATE", "TRIGGER", "Arwen"]

---

Create View

CREATE VIEW Snow...
CREATE VIEW IF NOT EXISTS Snow...

Result

a = [..."CREATE", "VIEW", "Snow"]

---

Alter Table

ALTER TABLE Vicky...

Result

a = [..."ALTER", "TABLE", "Vicky"]

---

Update Table

UPDATE Vicky...
UPDATE OR ROLLBACK Vicky...
UPDATE OR ABORT Vicky...
UPDATE OR REPLACE Vicky...
UPDATE OR FAIL Vicky...
UPDATE OR IGNORE Vicky...

Result

a = [..."UPDATE", "TABLE", "Vicky"] 

---

Here's links to SQLite's docs for these SQL statements.

ALTER TABLE

CREATE INDEX

CREATE TABLE

CREATE TRIGGER

CREATE VIEW

UPDATE

Thanks,

HeadDataZombie

---

Are you trying to parse them to validate accuracy? Validity? Security?

Parsing SQL isn't easy, and I doubt you'll be able to do it in a regex with that many patterns. Perhaps this link might help. See also previous question parsing-sql-create-table-statement-using-jquery and notice mention of TrimQuery, which might help with the JS implementation.

If you're trying to log activities that these modification (CREATE/ALTER/UPDATE) statements are running, perhaps just creating triggers to insert into a logging table might suffice.

---

SQL is hard (if not impossible) to parse with regular expressions. However...

The wierd diagram at the top of each page of the SQLite manual is actually telling you everything you need to know to use a parser generator like PEG.js or JS/CC to parse your SQL statements.

For example, the following PEG.js code will parse an ALTER TABLE statement perfectly:

start = altertable

altertable = "alter" w "table" w ( dbname:identifier w? "." ) ? w? tablename:identifier w ( renameto / addcolumn)

renameto = "rename" w "to" w newname:identifier

addcolumn = "add" w ( "column" w )? col:identifier

identifier = chars:([a-zA-Z][a-zA-Z_0-9]*)

w = " " +

You do, of course, need to add the actions that build a parse tree.