Parsing SQL CREATE TABLE statement using jQuery?

Question

I am writing a web app which uses SQL as input. This SQL must always be a CREATE TABLE statement, and what my app needs to do is getting the following attributes from it:

• Field name
• Type
• Length (if available)
• Binary (if available)
• Allow NULL (if available)
• Auto increment (if available)

Example SQL:

CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)

I have no idea where to start, and I'm not that good at writing such parsers. Are there any SQL parsers for JavaScript/jQuery that I can use, or any example code? Thanks.

Answer 1

This isn't a complete solution, but hopefully something to get you started.

You could check the following regex:

/^CREATE\s+TABLE\s+(\S*)\s*\((.*)\)$/

It's crude, but it should match.

You could then pull out the first matched group (assuming you need the name of the table) and then the second matched group for the field list

You can then split the field list on the comma to get the information for each field. Outside of that you could get the field name by matching the first group in /^(.)\s(.)$/ ... You'd have to get a bit more creative in parsing the second half of the statement (types, lengths, null/not null, default, identity(1,1) ) but if you can figure out some sort of pattern that would always apply, I'm sure it could be done. Note that the above is also assuming that whitespace is properly trimmed, but that is easy enough.

Answer 2

there's an OpenSource project located on Google Projects called "TrimQuery", below is an example:

var selectStatement = queryLang.parseSQL("SELECT Customer.* FROM Customer");

This has great support for the general SQL Languages such as JOINS and SELECT item.*,relation.other FROM X

Hope this helps.