How to generate queries with random joins

[Gaurav Chander]

Hi All,

This is my first post. Sorry if it is in the wrong section.

I am working on a web application in which user will be selecting columns form different tables to be part of the final report.
My problem is that tables have joins on them and i'm not able to figure out how to generate the sql query on the base of selection criteria as user can select any number of tables and joins are to be put accordingly in run time.
Thanks for the help.

Regards
GC

[Tarun Bolla]

Here is an example...
Consider three tables as below
User --> UserID, Name, Age, Sex
Account --> AccountID, UserID, Balance, Type
Transaction --> TransactionID, AccountID, Amount, Type
Now if the user wants to see User.Name, Account.Balance, Transaction.Amount, Transaction.Type
Here is a sample code..considering the parameter will be like User.Name-Account.Balance-Transaction.Amount-Transaction.Type (You can use any other separator according to your wish)

String selectStmt = "SELECT ";
String joinStmt = " FROM USER";

String params[] = ((String)request.getParameter("select")).split("-");
for(String param : params){
	String[] select = param.split(".");
	if(!joinStmt.contains(" " + select[0] + " ")){
		joinStmt += " JOIN " + select[0] + " ON USER.USERID = " + select[0] + ".USERID"
	}
	selectStmt += selectStmt.equals("SELECT ")?param:"," + param;
}

String sql = selectStmt + joinStmt;

You need to refine this code a bit to suit your req..but i hope this will give you a start

[Rob Spoor]

That code should never be used in any production environment without any validation of the parameter. Right now it's highly susceptible to SQL injection.

[Tarun Bolla]

Rob Spoor wrote:That code should never be used in any production environment without any validation of the parameter. Right now it's highly susceptible to SQL injection.

Yeah...I concentrated on the logic but security measure can be taken. Basically with this small req the parameter validation would be like (using a regular expression)

"^[a-zA-Z]+\\.[a-zA-Z]+(-[a-zA-Z]+\\.[a-zA-Z]+)*$"

or more specifically

"^((USER)|(ACCOUNT)|(TRANSACTION))\\.((USERID)|(NAME)|.....|(TYPE))(-((USER)|(ACCOUNT)|(TRANSACTION))\\.((USERID)|(NAME)|.....|(TYPE)))*$"

[Martin Vashko]

Tarun Bolla wrote:

Rob Spoor wrote:That code should never be used in any production environment without any validation of the parameter. Right now it's highly susceptible to SQL injection.

Yeah...I concentrated on the logic but security measure can be taken. Basically with this small req the parameter validation would be like (using a regular expression)

"^[a-zA-Z]+\\.[a-zA-Z]+(-[a-zA-Z]+\\.[a-zA-Z]+)*$"

or more specifically

"^((USER)|(ACCOUNT)|(TRANSACTION))\\.((USERID)|(NAME)|.....|(TYPE))(-((USER)|(ACCOUNT)|(TRANSACTION))\\.((USERID)|(NAME)|.....|(TYPE)))*$"

Or even better, checking the user supplied table/column names against a list of known tables/columns. I would not opt for anything less. Also, proper variables (if any) should be bound, not stuffed in as literals.

There is also a performance concern. Random queries may take very very long time to complete, if there are not proper indexes in place. If this is an OLTP system, a better solution might be providing only a few specific reports. That way a set of queries could be maintained with corresponding indexes to support them in the database. (This is often ignored by developers, who then whine that the database is slow and are tempted to try to replicate database functionality in the middle tier. )

[Wendy L Gibbons]

have you considered using hibernate, or such things?

I THINK this would handle all that tricky stuff for you.

[Tarun Bolla]

Martin Vajsar wrote:Or even better, checking the user supplied table/column names against a list of known tables/columns. I would not opt for anything less

Thats what the below quote is about....

Tarun Bolla wrote:

"^((USER)|(ACCOUNT)|(TRANSACTION))\\.((USERID)|(NAME)|.....|(TYPE))(-((USER)|(ACCOUNT)|(TRANSACTION))\\.((USERID)|(NAME)|.....|(TYPE)))*$"

But this stuff is getting far too specific for a beginners question. Ofcourse a lot can be done. But i tried to keep the code short and simple for an idea on how to start.

[Martin Vashko]

Tarun Bolla wrote:

Martin Vajsar wrote:Or even better, checking the user supplied table/column names against a list of known tables/columns. I would not opt for anything less

Thats what the below quote is about....

Tarun Bolla wrote:

"^((USER)|(ACCOUNT)|(TRANSACTION))\\.((USERID)|(NAME)|.....|(TYPE))(-((USER)|(ACCOUNT)|(TRANSACTION))\\.((USERID)|(NAME)|.....|(TYPE)))*$"

Ahh, sorry. I just wouldn't use regular expressions for that, so I missed it.

[Gaurav Chander]

Tarun Bolla wrote:Here is an example...
Consider three tables as below
User --> UserID, Name, Age, Sex
Account --> AccountID, UserID, Balance, Type
Transaction --> TransactionID, AccountID, Amount, Type
Now if the user wants to see User.Name, Account.Balance, Transaction.Amount, Transaction.Type
Here is a sample code..considering the parameter will be like User.Name-Account.Balance-Transaction.Amount-Transaction.Type (You can use any other separator according to your wish)

String selectStmt = "SELECT ";
String joinStmt = " FROM USER";

String params[] = ((String)request.getParameter("select")).split("-");
for(String param : params){
	String[] select = param.split(".");
	if(!joinStmt.contains(" " + select[0] + " ")){
		joinStmt += " JOIN " + select[0] + " ON USER.USERID = " + select[0] + ".USERID"
	}
	selectStmt += selectStmt.equals("SELECT ")?param:"," + param;
}

String sql = selectStmt + joinStmt;

You need to refine this code a bit to suit your req..but i hope this will give you a start

Thank you for the explanation.

I am able to assemble simple queries for example select [column list] from [table list] where [condition list]
but the problem is that I'm not sure what tables will be part of the join .

An example query :

select distinct
CAMPAIGN.LEADS_TYPE,
CAMPAIGN.CAMPAIGN_TYPE,
ADVERTISER.ACC_NAME,
USER_PROFILE.EMAIL,
ADVERTISER.ACC_TYPE,
AD_UNIT.AD_NAME
from
CAMPAIGN JOIN ADVERTISER ON CAMPAIGN.user_profile_id_fk = ADVERTISER.user_profile_id_fk
JOIN USER_PROFILE ON ADVERTISER.user_profile_id_fk = USER_PROFILE.id
JOIN AD_UNIT ON AD_UNIT.campaign_id_fk = CAMPAIGN.id;


now for the selected columns join will be as defined in the query but as the column list changes so do the joins and there are about 14 tables so I'm not sure how to handle this.

If if try to consider all the possible conditions it will be a very lengthy code and I'm not sure if that is feasible or not.

Thank you all for your time and effort.

[Tarun Bolla]

Gaurav Chander wrote: there are about 14 tables so I'm not sure how to handle this.

That can be made easier with UI design. Consider this design
1. You need 2 dropdowns with all the tables(filled initially), columns(not filled initially)
2. When ever the selected table changes, columns drop down populates with respective columns
3. And you can provide a button like "Select this"
4. When user clicks on the button you can concatenate the TABLENAME.COLUMNNAME to another list in UI showing the user that he has opted to select those
5. When the user clicks submit, you will build the concatenated string from the list on client side and send it to server to build the query on server side

This way you need not worry about 14 or 40 tables

[Gaurav Chander]

Tarun Bolla wrote:

Gaurav Chander wrote: there are about 14 tables so I'm not sure how to handle this.

That can be made easier with UI design. Consider this design
1. You need 2 dropdowns with all the tables(filled initially), columns(not filled initially)
2. When ever the selected table changes, columns drop down populates with respective columns
3. And you can provide a button like "Select this"
4. When user clicks on the button you can concatenate the TABLENAME.COLUMNNAME to another list in UI showing the user that he has opted to select those
5. When the user clicks submit, you will build the concatenated string from the list on client side and send it to server to build the query on server side

This way you need not worry about 14 or 40 tables

Thank you..

[Wendy L Gibbons]

Tarun Bolla wrote:

Gaurav Chander wrote: there are about 14 tables so I'm not sure how to handle this.

That can be made easier with UI design. Consider this design
1. You need 2 dropdowns with all the tables(filled initially), columns(not filled initially)
2. When ever the selected table changes, columns drop down populates with respective columns
3. And you can provide a button like "Select this"
4. When user clicks on the button you can concatenate the TABLENAME.COLUMNNAME to another list in UI showing the user that he has opted to select those
5. When the user clicks submit, you will build the concatenated string from the list on client side and send it to server to build the query on server side

This way you need not worry about 14 or 40 tables

This doesn't in any way address the joins between the tables, if he has 40 tables he will need to store somewhere all of the primary keys between the tables, and the foriegn keys between the tables. As the list the user selects may not include all the tables required for the select to work.
Imagine
people table -> car table -> engine size table

the user requests all the people with 1.8l engines
he needs to work out the link between people and engine size.

[Tarun Bolla]

Wendy Gibbons wrote:This doesn't in any way address the joins between the tables, if he has 40 tables he will need to store somewhere all of the primary keys between the tables, and the foriegn keys between the tables.

My apologies to the OP. I was under a perception that all the tables carry single join column. It seems like a bigger picture now and a good problem to solve. See you soon. (with another solution ofcourse)

[Wendy L Gibbons]

Tarun Bolla wrote:

Wendy Gibbons wrote:This doesn't in any way address the joins between the tables, if he has 40 tables he will need to store somewhere all of the primary keys between the tables, and the foriegn keys between the tables.

My apologies to the OP. I was under a perception that all the tables carry single join column. It seems like a bigger picture now and a good problem to solve. See you soon. (with another solution ofcourse)

yours was a very nice interface solution, now he needs to work on the back end, he has the user generated portion, but not all the key links.

[Tarun Bolla]

See if this can help you.. I sooo think this will help you ..

package joinmt.console;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import joinmt.utils.TablesAndColumns;

public class QueryBuilder {
	public static void main(String[] args) {
		String selectParam = "Users.UserID-TransactionNotes.Note";
		String[] selects = selectParam.split("-");
		
		String selectStmt = "SELECT ";
		String joinStmt = " FROM ";
		
		List<String> tablesUsed = new ArrayList<String>();
		
		Map<String, List<String>> relationMap = TablesAndColumns.getRelationMap();
		
		for(int i = 0; i < selects.length ; i++){
			String select = selects[i];
			String[] selector = select.split("\\.");
			if(tablesUsed.size() == 0){
				selectStmt += select;
				joinStmt += selector[0];
			}else{
				selectStmt += ", " + select;
				if(!tablesUsed.contains(selector[0])){
					String join = getDirectJoin(selector, tablesUsed, relationMap);
					joinStmt += join;
				}
			}
			tablesUsed.add(selector[0]);
		}
		System.out.println(selectStmt + joinStmt);
	}
	
	private static String getJoins(String[] selector, List<String> tablesUsed, List<String> prevChain,
			Map<String, List<String>> relationMap) {
		for(Map.Entry<String, List<String>> entry : relationMap.entrySet()){
			String tableNow = entry.getKey();
			if(tableNow.equals(selector[0]) || prevChain.contains(tableNow))
				continue;
			String join = getDirectJoin(selector, tableNow, relationMap);
			if(join != null){
				if(tablesUsed.contains(tableNow))
					return join;
				else{
					prevChain.add(tableNow);
					tablesUsed.add(tableNow);
					String[] selector_1 = new String[2];
					selector_1[0] = tableNow;
					selector_1[1] = relationMap.get(tableNow).get(0);
					String join_1 = getJoins(selector_1, tablesUsed, prevChain, relationMap);
					if(join_1 != null)
						return join_1 + join;
					prevChain.remove(tableNow);
				}
			}
		}
		return null;
	}
	
	private static String getDirectJoin(String[] selector, String tableUsed,
			Map<String, List<String>> relationMap) {
		List<String> relations = relationMap.get(tableUsed);
		// Normal order
		for(int i = 1 ; i < relations.size(); i++){
			String fRelation = relations.get(i);
			if(fRelation.startsWith(selector[0])){
				return " JOIN " + selector[0] + " ON " + tableUsed + "." + relations.get(0) + " = " + fRelation;
			}
		}
		// Reverse order
		relations = relationMap.get(selector[0]);
		for(int i = 1 ; i < relations.size(); i++){
			String fRelation = relations.get(i);
			if(fRelation.startsWith(tableUsed)){
				return " JOIN " + selector[0] + " ON " + selector[0] + "." + relations.get(0) + " = " + fRelation;
			}
		}
		return null;
	}

	private static String getDirectJoin(String[] selector, List<String> tablesUsed,
			Map<String, List<String>> relationMap) {
		// Check if table already used
		for(String tableUsed : tablesUsed){
			if(selector[0].equals(tableUsed))
				return "";
		}
		// Check if the present table has a direct relation
		for(String tableUsed : tablesUsed){
			// Normal order
			List<String> relations = relationMap.get(tableUsed);
			for(int i = 1 ; i < relations.size(); i++){
				String fRelation = relations.get(i);
				if(fRelation.startsWith(selector[0])){
					return " JOIN " + selector[0] + " ON " + tableUsed + "." + relations.get(0) + " = " + fRelation;
				}
			}
			// Reverse Order
			relations = relationMap.get(selector[0]);
			for(int i = 1 ; i < relations.size(); i++){
				String fRelation = relations.get(i);
				if(fRelation.startsWith(tableUsed)){
					return " JOIN " + selector[0] + " ON " + selector[0] + "." + relations.get(0) + " = " + fRelation;
				}
			}
		}
		return getJoins(selector, tablesUsed, new ArrayList<String>(), relationMap);
	}
}

Table And column along with foreign key mappings in a java file

package joinmt.utils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class TablesAndColumns {
    // This property is used for UI generation.. Doesnt constitute any logic. Albeit it can be used to understand the table structure i used here
	private static List<String> tables = new ArrayList<String>();
	
	static {
		tables.add("Users");
		tables.add("Accounts");
		tables.add("Transactions");
		tables.add("Drafts");
		tables.add("Cheques");
		tables.add("TransactionNotes");
	}
	
	public static List<String> getTables(){
		return tables;
	}
	
	public static List<String> getColumns(String table){
		List<String> columns = new ArrayList<String>();
		if("Users".equals(table)){
			columns.add("UserID");
			columns.add("Name");
			columns.add("Age");
			columns.add("Sex");
			columns.add("DOB");
		}else if("Accounts".equals(table)){
			columns.add("AccountID");
			columns.add("UserID");
			columns.add("Since");
			columns.add("Balance");
		}else if("Transactions".equals(table)){
			columns.add("TransactionID");
			columns.add("FromAccountID");
			columns.add("ToAccountID");
			columns.add("Dated");
			columns.add("Amount");
			columns.add("Comment");
		}else if("Drafts".equals(table)){
			columns.add("DraftID");
			columns.add("FromAccountID");
			columns.add("ToAccountID");
			columns.add("Dated");
			columns.add("Amount");
		}else if("Cheques".equals(table)){
			columns.add("ChequeID");
			columns.add("FromAccountID");
			columns.add("Dated");
			columns.add("Amount");
		}else if("TransactionNotes".equals(table)){
			columns.add("NoteID");
			columns.add("TransactionID");
			columns.add("Dated");
			columns.add("Note");
		}
		return columns;
	}

    // Specify relations in between tables
	public static Map<String, List<String>> getRelationMap(){
		Map<String, List<String>> relationMap = new HashMap<String, List<String>>();
		
		List<String> keyList = new ArrayList<String>(); // First entry is Primary Key, Later entries are foreign key references
		keyList.add("UserID");
		keyList.add("Accounts.UserID");
		relationMap.put("Users", keyList);
		
		keyList = new ArrayList<String>();
		keyList.add("AccountID");
		keyList.add("Transactions.FromAccountID");
		keyList.add("Transactions.ToAccountID");
		keyList.add("Drafts.FromAccountID");
		keyList.add("Drafts.ToAccountID");
		keyList.add("Cheques.FromAccountID");
		relationMap.put("Accounts", keyList);
		
		keyList = new ArrayList<String>();
		keyList.add("TransactionID");
		keyList.add("TransactionNotes.TransactionID");
		relationMap.put("Transactions", keyList);
		
		keyList = new ArrayList<String>();
		keyList.add("DraftID");
		relationMap.put("Drafts", keyList);
		
		keyList = new ArrayList<String>();
		keyList.add("ChequeID");
		relationMap.put("Cheques", keyList);
		
		keyList = new ArrayList<String>();
		keyList.add("NoteID");
		relationMap.put("TransactionNotes", keyList);
		
		return relationMap;
	}
}

PHEW.....

[Wendy L Gibbons]

just testing it now, but the columns and tables data (in a live application) should be in a configuration file loaded at runtime, you don't want to have to change and recompile the code every time the database changes.

IT WORKED, I added another table cheque stuff linked from cheques. and selelcted qb.main("Users-Accounts", "Users.UserID-TransactionNotes.Note-ChequeStuff.ChequeID");

I also played with it a bit, made the columns a map from table to a list of columns, and passed in a list of the tables you actually want selecting, so in above select the tables users and accounts.

oops well over my lunch hour

[Tarun Bolla]

Thanks for the testing Wendy! Seems like Gaurav has lost interest in this...

[Gaurav Chander]

Hi!

I finally made it working.

I used a properties file which has the join conditions and joins are picked on the basis of columns selected in run time.

A sample join looks like below.

ADVERTISER=1 USER_PROFILE; JOIN ADVERTISER ON USER_PROFILE.ID \= ADVERTISER.USER_PROFILE_ID_FK

so when ADVERTISER tables is selected join is with user profile and join is separated with ';'. Join tables are given numbers and joins are appended into query according to their order.

Thanks everyone for your time.