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