Dynamically set SqlDataSource's database

[Michaelnel]

I am using gwt 2.2, smartgwt 2.4 eval, chrome 11.

The scenario:
The website will cater to different companies.
Each company will have its own database.
But each database's structure is identical, only the actual contained data will differ.

i.e. Database A will have tables X, Y and Z and database B will also have tables X, Y and Z. Only the data in the tables will differ from each database.

What I would like to know:
Is possible to set a SqlDataSource's Database dynamically?
eg. A user will login user his/her username + password. The credentials will be validated and the company the user is linked to will determine which database should be used throughout the users session.

Hope I have made everything clear, if not please don't hesitate to ask for more information.

Any help on the matter would be greatly appreciated.

[Isomorphic]

One way to do this is to use DataSource.addDynamicDSGenerator(). Specifically:

1. client-side use DataSource IDs that include the current user's company ID. You will probably want to wrap DataSource.get()/load() so that you always call it indirectly through your own API (eg MyDS.get(companyId, dataSourceId)) which automatically adds the companyId as a prefix to the DataSource ID before calling DataSource.get().

2. server-side, add a generator that parses out the company ID and provides a DataSource definition that uses different DataSource.dbName settings according to the user's company.

[Michaelnel]

Ok, so just to clarify, I should do something similar to the following:

For all datasources which DataBases are dependent on the user I should:

1. Add a prefix to the String in (Client-Side) DataSource.get(). eg. prefix = "dbDefined"
2. Add the dbName, eg dbName = "dbName"
3. Add the actual DataSource name, eg. dsName = "MyDataSource"

After steps 1-3 implement it like this: DataSource.get("dbDefined/dbName/MyDataSource")

Then on Server side:

1. Create CustomGenerator
a. Create a Class that implements DynamicDSGenerator (called = CustomGenerator)
b. In the getDataSource() method implement the generation of the DataSource specified by the string defined above.

2. Create a servlet that loads on startup
a. In the servlet call DataSource.addDynamicDSGenerator(ddsg, prefix)
b. Pass the following parameters to the method above
i. new CustomGenerator()
ii. "dbDefined"

Does that sound about right?

[Isomorphic]

That's correct, although bear in mind that the DataSource ID must be a valid identifier, so instead of "dbDefined/dbName/MyDataSource" you would want something like "dbDefined_dbName_MyDataSource".

[Michaelnel]

Ah, yes. A problem I quickly realized and corrected. My problem now is as follows:

In Custom generator I have the following:

public class CustomGenerator implements DynamicDSGenerator{

	@Override
	public DataSource getDataSource(String id, DSRequest dsRequest) {
		String[] params = id.split("_");
		DataSource ds = null;
				
		try {
			ds = DataSource.fromXML(loadDataSourceDocument(params[1], params[2]));
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return ds;
	}

	//Get the datasource from xml and set the datasource's dbName
	private Document loadDataSourceDocument(String dbName, String dsName){
		DocumentBuilderFactory fac = DocumentBuilderFactory.newInstance();
		try{
			DocumentBuilder builder = fac.newDocumentBuilder();
			Document doc = builder.parse("ds/" + dsName + ".ds.xml");
			doc.getDocumentElement().setAttribute("dbName", dbName);
			
			return doc;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
}

Where the DataSource is used I have:

	Function func = new Function() {
		@Override
		public void execute() {
			System.out.println("### DS load complete");
			getDS();
		}
	};	    
	DataSource.load("dbDefined_dbName_dsName", func, true);

But after that subsequent calls to DataSource.get("dsName") still returns the ds with the default dbName.

Any ideas as to why this might be?

[Isomorphic]

DataSource.load() is asynchronous. Use the callback parameter to get a notification of when the DataSource is loaded.

[Michaelnel]

I am aware of the fact. I apologize, guess I should have added that in the Function's execute() method I have the getDS() method which then uses DataSource.get() etc.

[Isomorphic]

Ah. Then you need to troubleshoot what's going on over the network - you can see the call being made and the response using either the RPC tab of the Developer Console or Firebug. You can also look at the server-side logs to see if there are any errors, check if your code was called, etc.

[Michaelnel]

A few println stmts confirm that DataSource.load() does reach my DDSG and everything up until DataSource.fromXML() does run correctly.

No errors on server log.

Dev console shows the RPC request. The Response -> RAW shows(I substituted dsID, field definitions, dbName and op bindings):

{
    ID:"dsName", 
    serverType:"sql", 
    fields:[
        various fields
    ], 
    dbName:"updated_DB_Name", 
    tableName:"table", 
    operationBindings:[
        various operation bindings
    ]
}

So the returned DS is as it should be (after the updated dbName)

Although I have noticed that calling DataSource.get("dsName") or DataSource.get("dbDefined_dbName_dsName") does not invoke my DDSG.

Any thoughts?

[Isomorphic]

In the RPC tab can you uncheck "Auto-format" and/or provide the raw response as shown by Firebug?

Are you calling DS.get() with the exact same string as you passed to DataSource.load()?

[Michaelnel]

raw response without auto-format (Again I substituted dsID, field definitions, dbName and op bindings):

isc.DataSource.create({
    dbName:"updated_DB_Name",
    tableName:"table",
    ID:"dsName",
    dataFormat:"iscServer",
    dataSourceVersion:"1",
    operationBindings:[
        various operation bindings
    ],
    generatedBy:"SC_SNAPSHOT-2011-01-06/EVAL Deployment 2011-01-06",
    fields:[
        various fields
    ],
    serverType:"sql"
})

The Firebug raw response is the same.

And yes/no. I called DS.get() with only the dsName AND the same string as the DS.load(). I did this after the load completes. Calling only the dsName returns the unedited DS (i.e. with default dbName). Calling with the same string as load() returns null.

[Isomorphic]

OK, it's hard to tell what's going on when you're editing the diagnostic information...

You show ID as "dsName". The ID of the generated DataSource should match both what you're passing to DataSource.load() and DataSource.get(). If this is the case and you're seeing a response formatted like what you've shown here, there's no way it can fail.

[Michaelnel]

Ok so lets assume the following:

datasource name = myDS (eg. myDS.ds.xml)
Database name = MyDatabase

My call to load is: DataSource.load("dbDefined_MyDatabase_myDS")

DataSource.get("myDS") returns the defualt(unedited) DS.
DataSource.get("dbDefined_MyDatabase_myDS") returns null.

*Note: I have a DataSource definition in my "ds" dir, namely, myDS.ds.xml.

[Isomorphic]

So again, the ID in the dynamically generated XML passed to DataSource.fromXML() should be the same as the ID passed to both DataSource.load() and DataSource.get(). It doesn't look like this is the case.

The fact that you also have a file somewhere called myDS.ds.xml that serves as a template is irrelevant to this process. In fact, it probably should not be in the "ds" dir because that makes it a live DataSource that clients can actually send requests to, whereas you seem to intend it as a template only.

[Michaelnel]

Updated everything as recommended.

the string= dbDefined_MyDatabase_myDS

load("dbDefined_MyDatabase_myDS")
get("dbDefined_MyDatabase_myDS")

AND in my DDSG I changed the ID to "dbDefined_MyDatabase_myDS"

So now everything is using = dbDefined_MyDatabase_myDS

But now I get an error:
java.lang.Exception: Can't find dataSource: dbDefined_MyDatabase_myDS - please make sure that you have a dbDefined_MyDatabase_myDS.ds.xml file for it in [webRoot]/shared/ds