I have a sqlite database that gets populated by a different process. This process generates tables in the db and fills them with data.
I am trying to apply a set of pre-written queries against this db, but I need to make sure that all of the tables referenced in the query are created in the db before I run it to prevent errors. I am trying to determine all of the possible ways that a table can be referenced in a SQL to make sure I cover all of the options.
simple:
select col1 from table1
joins:
select col1,col2 from table1 join table2 on col1 = col2
select col1,col2 from table1 left outer join table2 on col1 = col2
select col1,col2 from table1, table2 on col1 = col2
select col1,col2 from table1, table2 where col1 = col2
subqueries:
select col1,(select col2 from table2 where col1 = col2) as ag2 from table1
select col1 from table1 where col1 in (select col2 from table2)
alias:
select col1,col2 from table1 t1, table2 t2 where col1 = col2
select col1,col2,col3 from table1 t1, table2 t2,table3 t3 where col1 = col2
I am thinking to use a RegEx to identify the few occurrences.
from [table] [alias]
join [table] [alias]
from [table] [alias], [table] [alias]
This RegEx seems to account for most of the variances. Table names appear in group2 or group3:
(from|join)\s+([\w]+)|,\s*([\w]+)\s*([\w]\s*)?(on|where)
My questions:
- Have I identified all of the possible ways for a table to be used in a query?
- Are there any other false positives from my expression?
- I can't get all of the table names from the alias section. Help?
- Is there a better approach than RegEx?
I will be using this in Python code if that affects the format of the RegEx.
