Skip to content

Instantly share code, notes, and snippets.

@umjasnik
Created December 27, 2011 20:06
Show Gist options
  • Save umjasnik/1524986 to your computer and use it in GitHub Desktop.
Save umjasnik/1524986 to your computer and use it in GitHub Desktop.
simple SQL parser using ragel - get all tables that are referenced in a SQL statement
package de.uwefleischer.statemachines;
import java.util.Set;
import java.util.TreeSet;
public class Sql {
private static Set<String> tables;
%%{
machine sql_tables;
action selectStatement {
System.out.println("select");
}
action fromStatement {
System.out.println("from");
}
action tableStart {
item = p;
}
action tableEnd {
String tableName = new String(data, item, p-item);
tableName = tableName.replaceAll("`", "");
System.out.println(tableName);
tables.add(tableName);
}
_select = 'select'i @selectStatement;
_from = 'from'i space+ @fromStatement;
_join = (space* ',' space*|(space+ ('inner'i|'left'i|'right'i|'outer'i))? space+ 'join'i space+);
_as = space+ 'as'i space+;
undefined_stuff = (space+ any+ space+)+;
name = '`'? [a-zA-Z_][a-zA-Z_0-9]* '`'?;
extended_name = (name '.')? name;
table_name = extended_name >tableStart %tableEnd;
table_alias = name;
table = table_name (_as table_alias)?;
main :=
'('? _select undefined_stuff _from table (_join table)* ')'?
;
}%%
%% write data;
public static Set<String> getTables(String statement) {
tables = new TreeSet<String>();
char[] data = statement.toCharArray();
int cs, p = 0, pe = data.length, eof = data.length, item = 0;
int stack[] = new int[1024];
int stack_size = 1;
int top;
%% write init;
%% write exec;
return tables;
}
}
package de.uwefleischer.statemachines;
import java.util.Set;
import org.junit.Test;
import static org.junit.Assert.*;
public class SqlTest {
// @BeforeClass
// public static void setUpClass() throws Exception {
// Runtime.getRuntime().exec("ragel -J Sql.rl");
// }
@Test
public void testSimpleStatement() {
String statement = "SELECT * FROM tableName";
Set tables = Sql.getTables(statement);
assertTrue(tables.contains("tableName"));
assertEquals(1, tables.size());
}
@Test
public void testWithoutStatement() {
String statement = "";
Set tables = Sql.getTables(statement);
assertEquals(0, tables.size());
}
@Test
public void testJoinWithComma() {
String statement = "SELECT * FROM tableName1,tableName2";
Set tables = Sql.getTables(statement);
assertTrue(tables.contains("tableName1"));
assertTrue(tables.contains("tableName2"));
assertEquals(2, tables.size());
}
@Test
public void testInnerJoin() {
String statement = "SELECT * FROM tableName1 LEFT JOIN tableName2";
Set tables = Sql.getTables(statement);
assertTrue(tables.contains("tableName1"));
assertTrue(tables.contains("tableName2"));
assertEquals(2, tables.size());
}
@Test
public void testJoin() {
String statement = "SELECT * FROM tableName1 JOIN tableName2";
Set tables = Sql.getTables(statement);
assertTrue(tables.contains("tableName1"));
assertTrue(tables.contains("tableName2"));
assertEquals(2, tables.size());
}
@Test
public void testWithTableAlias() {
String statement = "SELECT * FROM tableName AS tableAlias";
Set tables = Sql.getTables(statement);
assertTrue(tables.contains("tableName"));
assertEquals(1, tables.size());
}
@Test
public void testJoinWithAliases() {
String statement = "SELECT * FROM tableName1 AS table1 JOIN tableName2 AS table2";
Set tables = Sql.getTables(statement);
assertTrue(tables.contains("tableName1"));
assertTrue(tables.contains("tableName2"));
assertEquals(2, tables.size());
}
@Test
public void testWithTableEscape() {
String statement = "SELECT * FROM `tableName` AS `tableAlias`";
Set tables = Sql.getTables(statement);
assertTrue(tables.contains("tableName"));
assertEquals(1, tables.size());
}
@Test
public void testWith4Joins() {
String statement = "SELECT * FROM `tableName1` AS `tableAlias`"
+ ",`tableName2`"
+ " INNER JOIN tableName3"
+ " LEFT JOIN tableName4";
Set tables = Sql.getTables(statement);
assertTrue(tables.contains("tableName1"));
assertTrue(tables.contains("tableName2"));
assertTrue(tables.contains("tableName3"));
assertTrue(tables.contains("tableName4"));
assertEquals(4, tables.size());
}
@Test
public void testWithDatabaseAndTable() {
String statement = "SELECT * FROM db.tableName";
Set tables = Sql.getTables(statement);
assertTrue(tables.contains("db.tableName"));
assertEquals(1, tables.size());
}
@Test
public void testWithDatabaseAndTableAndEscapes() {
String statement = "SELECT * FROM `db`.`tableName`";
Set tables = Sql.getTables(statement);
assertTrue(tables.contains("db.tableName"));
assertEquals(1, tables.size());
}
@Test
public void testSubselect() {
String statement = "SELECT tableName1.test1,tableName1.test2 "
+ " FROM tableName1"
+ " WHERE `id`=(SELECT tableName2.id FROM tableName2)";
Set tables = Sql.getTables(statement);
assertTrue(tables.contains("tableName1"));
assertTrue(tables.contains("tableName2"));
assertEquals(2, tables.size());
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment