#1 Reverse building schema from query

[Marc227]

It would be great if pasting sql query was possible and
after that the schema with tables and relations would
be drawn ..

I'm very interesting in it and if you want,i would like
to help to developp that ..

Thanks,
Marc

[Nobody/Anonymous]

Logged In: NO

I am developping a class that will fill querymodel object
from a sql query :

class QueryParser

methods :

QueryParser::fillQueryModel(string sqlQuery, QueryModel model)

What do you think of it ?
I'll give you some news and what about including it in your
project ..?

[nickyb]

Logged In: YES
user_id=1189319

ok, it's very useful... but note that QueryBuilder at moment
is incomplete: Union and subquery in form clause are not
supported...I am working there above

...I'll wait your news about, and if you need help let me know

nicky

[Marc227]

Logged In: YES
user_id=1401932

Hi,

i hav begun a class.. Here an example of use and the source
.. i'd like to speak with you by msn messenger .. I let you
a mail for private message and for exchanging msn account :
rustine22@yahoo.fr

QueryParser p = new QueryParser(sql);
p.fillQueryModel(model);
ClientQueryBuilder client = new
ClientQueryBuilder(ClientWorkspace.this.keycah);
client.setQueryName(entryname);
Application.window.add(client);
client.setModel(model);

It works with basic request for the moment :

SELECT
`report`.`titre`
FROM
`report` INNER JOIN `report_application` ON
`report`.`appli_id` = `report_application`.`appli_id`
INNER JOIN `report_param` ON `report`.`id` =
`report_param`.`id_report`
INNER JOIN `report_param_operator` ON
`report_param`.`id_operator` = `report_param_operator`.`id`

TODO :

LEFT, RIGHT OUTER JOIN
ALIAS TABLE
Fields in select clause without table name
Schemas ..

I'll go on .. please contact me!!

Bye
Marc

package nicky.querybuilder;

import java.util.ArrayList;
import java.util.StringTokenizer;

public class QueryParser {
String sqlQuery;

public QueryParser(String sql)
{
sqlQuery = sql.toLowerCase();
}

public void fillQueryModel(QueryModel model)
{
fillSqlQueryDistinct(model);
fillSqlQueryTables(model);
fillSqlQueryExpressions(model);
fillSqlQueryJoins(model);
/*model.group = getSqlQueryGroup();
model.joins = getSqlQueryJoins();
model.order = getSqlQueryOrder();
model.where = getSqlQueryWhere();
model.having = getSqlQueryHaving();
model.tables = getSqlQueryTables();*/
}
void fillSqlQueryDistinct(QueryModel model)
{
String distinct = "DISTINCT";
model.distinct = (sqlQuery.indexOf(distinct)>0);
}

void fillSqlQueryTables(QueryModel model)
{
StringTokenizer st;
String fromWhere = "";
if(sqlQuery.toUpperCase().indexOf("WHERE")>0)
fromWhere =
sqlQuery.substring(sqlQuery.indexOf("FROM")+4,sqlQuery.toUpperCase().indexOf("WHERE"));
else fromWhere =
sqlQuery.substring(sqlQuery.toUpperCase().indexOf("FROM")+4);
/// TODO FROM table A, table B
String aryInnerJoin[] = new String[0];
if(fromWhere.toUpperCase().indexOf("INNER JOIN")>0)
aryInnerJoin = fromWhere.toUpperCase().split("INNER JOIN");
else if(fromWhere.toUpperCase().indexOf("LEFT
JOIN")>0) aryInnerJoin = fromWhere.toUpperCase().split("LEFT
JOIN");
else if(fromWhere.toUpperCase().indexOf("RIGHT
JOIN")>0) aryInnerJoin =
fromWhere.toUpperCase().split("RIGHT JOIN");
// TODO outer join
int onPos = 0;
QueryTokens.Table tableToken;
for(int i=0;i<aryInnerJoin.length;i++)
{
onPos = aryInnerJoin[i].indexOf(" ON ");
if(onPos>0)
{
tableToken =
tableStringFilter(aryInnerJoin[i].substring(0,onPos));
}
else
{
tableToken = tableStringFilter(aryInnerJoin[i]);
}
// TODO Schema
model.tables.add(tableToken);
}
}

void fillSqlQueryExpressions(QueryModel model)
{
String selectFrom =
sqlQuery.substring(sqlQuery.toUpperCase().indexOf("SELECT")+6,sqlQuery.toUpperCase().indexOf("FROM"));
if(selectFrom.indexOf(",")>0)
{
String arySelectFrom[] =
selectFrom.split(selectFrom);
for(int i=0;i<arySelectFrom.length;i++)
{

model.expressions.add(columnStringFilter(arySelectFrom[i]));
}
}
else
model.expressions.add(columnStringFilter(selectFrom));
}
String stringFilter(String in)
{
in = in.replaceAll("`","");
in = in.replaceAll("'","");
in = in.replaceAll("\"","");
in = in.trim();
return in;
}
void fillSqlQueryJoins(QueryModel model)
{
String fromWhere = "";
if(sqlQuery.toUpperCase().indexOf("WHERE")>0)
fromWhere =
sqlQuery.substring(sqlQuery.toUpperCase().indexOf("FROM")+4,sqlQuery.toUpperCase().indexOf("WHERE"));
else fromWhere =
sqlQuery.substring(sqlQuery.toUpperCase().indexOf("FROM")+4);
/// TODO WHERE table1.field1 = table2.field2
String aryInnerJoin[] = new String[100];
// TODO lower upper
if(fromWhere.toUpperCase().indexOf("INNER JOIN")>0)
aryInnerJoin = fromWhere.toUpperCase().split("INNER JOIN");
else if(fromWhere.toUpperCase().indexOf("LEFT
JOIN")>0) aryInnerJoin = fromWhere.toUpperCase().split("LEFT
JOIN");
else if(fromWhere.toUpperCase().indexOf("RIGHT
JOIN")>0) aryInnerJoin =
fromWhere.toUpperCase().split("RIGHT JOIN");
// TODO outer join
int onPos = 0;
String tmpJoin = "";

for(int i=0;i<aryInnerJoin.length;i++)
{
onPos = aryInnerJoin[i].toUpperCase().indexOf("
ON ");
if(onPos>0)
{
tmpJoin = aryInnerJoin[i].substring(onPos+3);
model.joins.add(joinStringFilter(tmpJoin));
}
else
{
// Nothing
}

} }

/*
ArrayList getSqlQueryGroup()
{
}
ArrayList getSqlQueryJoins()
{
}
ArrayList getSqlQueryOrder()
{
}
ArrayList getSqlQueryWhere()
{
}
ArrayList getSqlQueryHaving()
{
} ArrayList getSqlQueryTables()
{
}*/
QueryTokens.Table tableStringFilter(String in)
{
StringTokenizer st;
String tmpSchema;
String tmpTable;
QueryTokens.Table table;
in = in.replaceAll("`","");
in = in.replaceAll("'","");
in = in.replaceAll("\"","");
in = in.trim();
// TODO lower upper
in = in.toLowerCase();
if(in.indexOf(".")>0)
{
st = new StringTokenizer(in.trim(),".");
tmpSchema = st.nextToken();
tmpTable = st.nextToken();
table = new QueryTokens.Table(tmpSchema,tmpTable);
table.setAlias(tmpTable);
return table;
}
else
{
table = new QueryTokens.Table(null,in);
table.setAlias(in);
return table; }
}
QueryTokens.Column columnStringFilter(String in)
{
StringTokenizer st;
String tmpTable;
String tmpField;
in = in.replaceAll("`","");
in = in.replaceAll("'","");
in = in.replaceAll("\"","");
in = in.trim();
// TODO lower upper
in = in.toLowerCase();
if(in.indexOf(".")>0)
{
// TODO SELECT SCHEMA.TABLE.CHAMP
// TODO SELECT MAX(), FUNCTION()
st = new StringTokenizer(in.trim(),".");
tmpTable = st.nextToken();
tmpField = st.nextToken();
QueryTokens.Table table = new
QueryTokens.Table(null,tmpTable);
table.setAlias(tmpTable);
return new QueryTokens.Column(table,tmpField);
}
else return new QueryTokens.Column(null,in); }
QueryTokens.Join joinStringFilter(String in)
{
String tmp;
String tmpTable;
String tmpField;
StringTokenizer st;
QueryTokens.Table lTable;
QueryTokens.Table rTable;
QueryTokens.Column leftColumn;
QueryTokens.Column rightColumn;
in = in.replaceAll("`","");
in = in.replaceAll("'","");
in = in.replaceAll("\"","");
in = in.trim();
// TODO lower upper
in = in.toLowerCase();
// TODO join <>, >=, > ,< , <=
// TODO Schema
if(in.indexOf("=")>0)
{
String aryJoinFields[] = in.split("=");
// left field
st = new
StringTokenizer(aryJoinFields[0].trim(),".");
tmpTable = st.nextToken();
tmpField = st.nextToken();
lTable = new QueryTokens.Table(null,tmpTable);
lTable.setAlias(tmpTable);
leftColumn = new QueryTokens.Column(lTable,tmpField);

st = new
StringTokenizer(aryJoinFields[1].trim(),".");
tmpTable = st.nextToken();
tmpField = st.nextToken(); rTable =
new QueryTokens.Table(null,tmpTable);
rTable.setAlias(tmpTable);
rightColumn = new
QueryTokens.Column(rTable,tmpField);
return new
QueryTokens.Join(QueryTokens.Join.INNER_JOIN,leftColumn,"=",rightColumn);
}

return null;
}
}