What’s SQLEnv
SQLEnv is a Java class in the General SQL Parser(GSP) library that used to store the database metadata. With metadata in SQLEnv, GSP can build correct relationship between unqualified column and table.
Take this SQL for example,
select Quantity1,Quantity2
from aTab,bTab
without the help of metadata, GSP doesn’t know which table column Quantity1 and Quantity2 belongs to.
We can create a SQLEnv instance that provides the metadata to GSP like this:
class TSQLServerEnv extends TSQLEnv{
public TSQLServerEnv(){
super(EDbVendor.dbvmssql);
initSQLEnv();
}
@Override
public void initSQLEnv() {
// add a new database: master
TSQLCatalog sqlCatalog = createSQLCatalog("master");
// add a new schema: dbo
TSQLSchema sqlSchema = sqlCatalog.createSchema("dbo");
//add a new table: aTab
TSQLTable aTab = sqlSchema.createTable("aTab");
aTab.addColumn("Quantity1");
//add a new table: bTab
TSQLTable bTab = sqlSchema.createTable("bTab");
bTab.addColumn("Quantity2");
}
}
and then assign this SQLEnv to GSP before parsing the SQL query.
sqlparser.setSqlEnv(new TSQLServerEnv());
Database without schema
Some databases such as MySQL, Hive don’t have schema in the database. When creating SQLEnv for this kind of databases, the schema object in the SQLEnv should be set to default
.
Take this Hive SQL for example:
select column1, column2
from mydatabase.mytable1, mydatabase.mytable2
Here is an example for a SQLEnv that stores the Hive metadata.
class THiveEnv extends TSQLEnv{
public THiveEnv(){
super(EDbVendor.dbvhive);
initSQLEnv();
}
@Override
public void initSQLEnv() {
// add a new database
TSQLCatalog sqlCatalog = createSQLCatalog("mydatabase");
// hive doesn't has schema, we use a default schema
TSQLSchema sqlSchema = sqlCatalog.createSchema("default");
//add a new table: mytable1
TSQLTable mytable1 = sqlSchema.createTable("mytable1");
mytable1.addColumn("column1");
//add a new table: mytable2
TSQLTable mytable2 = sqlSchema.createTable("mytable2");
mytable2.addColumn("column2");
}
}
Load metadata from database instance
You can connect to the database instance to fetch the metadata and save it in a SqlEnv that can be used by GSP during the parsing of SQL script.
TSQLDataSource datasource = createSQLDataSource(vendor, host, port, user, passowrd, database, schema);
sqlenv = TSQLEnv.valueOf(datasource);
Build table and column relationship without Metadata
If there is no metadata available for SQL like this:
select Quantity1,Quantity2
from aTab,bTab
column Quantity1,Quantity2 will be saved in the TCustomSqlStatement.getOrphanColumns()
. You may fetch those columns from this property for further processing as you like.
Below is the Java code in gettablecolumns demo that link the orphan column to the first table in from clause.
if (stmt.getOrphanColumns().size() > 0){
infos.append(numberOfSpace(pNest+1)+" orphan columns:"+newline);
String oc = "";
for (int k=0;k<stmt.getOrphanColumns().size();k++){
TInfoRecord columnRecord = new TInfoRecord(EDbObjectType.column);
columnRecord.setColumn(stmt.getOrphanColumns().getObjectName(k));
columnRecord.setFileName(this.sqlFileName);
infoList.add(columnRecord);
oc = stmt.getOrphanColumns().getObjectName(k).getColumnNameOnly();// stmt.getOrphanColumns().getObjectName(k).toString();
if (showColumnLocation){
infos.append(numberOfSpace(pNest+3)+oc+"("+stmt.getOrphanColumns().getObjectName(k).getLocation()+")"+newline);
}else{
infos.append(numberOfSpace(pNest+3)+oc+newline);
}
if ((linkOrphanColumnToFirstTable)&&(stmt.getFirstPhysicalTable() != null)){
if ((listStarColumn) ||(!(oc.equalsIgnoreCase("*"))))
fieldlist.add(stmt.getFirstPhysicalTable().toString() + dotChar + oc );
columnRecord.setTable(stmt.getFirstPhysicalTable());
}else {
fieldlist.add("missed"+dotChar + oc+"("+stmt.getOrphanColumns().getObjectName(k).coordinate()+")" );
}
tableColumnList.append(",missed"+dotChar+oc+newline);
}
}
For the detailed Java demo, please check it here