foreign keys in datasources

[bpipa]

SmartGWT Pro:
Isomorphic SmartClient/SmartGWT Framework (v9.0p_2013-09-17/Pro Deployment 2013-09-17)

One thing to note - I am doing all of this on the server side using
import com.isomorphic.datasource.DataSource;
import com.isomorphic.datasource.DataSourceManager;
and this code is NOT used on the clientside.

I have a mySQL users table:
users table:
id|user_name
------------
1|jsmith

Here is the datasource XML for it:

<DataSource ID="users" serverType="sql" dataSourceVersion="1" autoDeriveSchema="true">
    <fields>
        <field name="id" type="sequence" primaryKey="true">
            <columnCode>b80bb7740288fda1f201890375a60c8f</columnCode>
        </field>
        <field name="user_name" type="text" length="45">
            <columnCode>c56f5648d4c0e137f9de3dfc7a54d55b</columnCode>
        </field>
    </fields>
    <allowAdvancedCriteria>false</allowAdvancedCriteria>
    <tableCode>9bc65c2abec141778ffaa729489f3e87</tableCode>
    <generatedBy>v9.0p_2013-09-17/Pro Deployment 2013-09-17</generatedBy>
</DataSource>

and I have a few other tables that use the user_id as a foreign key. I want to be able to do things with the other tables based on the user_name vs the user_id (jsmith vs 1). An example test table:

test table:
id|user_id|text
1|1|testing
where user_id is a FK pointing to the id in the users table

This works just fine:

DataSource ds = DataSourceManager.get("test");    
Map<String,Object> record = new HashMap<String,Object>();
record.put("user_id", 1);
record.put("text", "testing");
ds.add(record);

but what I would like to be able to do is something like

DataSource ds = DataSourceManager.get("test");    
Map<String,Object> record = new HashMap<String,Object>();
record.put("users.user_name", "jsmith");
record.put("text", "testing");
ds.add(record);

When I try this I get:

"DataSource - DataSource auditlog includes fields from DataSource users but we cannot establish a direct or indirect relation between those DataSources.  You must specify foreignKey field(s) on the including DataSource (test) that either express a direct relationship to users, or express an indrect relationship via one or more intervening tables"

which makes it sounds liek i need soemthign more in my DS.XML file.

I do have a datasource setup for both the users and test tables and I have autoDeriveSchema="true" for both of them. In the test datasource, I have the user_id setup as:

<field name="user_id" type="integer">
    <columnCode>e8701ad48ba05a91604e480dd60899a3</columnCode>
    <fkColumnCode>b80bb7740288fda1f201890375a60c8f</fkColumnCode>
    <fkTableCode>9bc65c2abec141778ffaa729489f3e87</fkTableCode>
</field>

I also tried adding to the test table definition:
<field includeFrom="users.user_name"/>
and that didn't work either.

I've searched for examples of using foreign keys in datasources and haven't been able to find any examples of its use like this so I'm not sure if I can't find it or we cannot use it like this. I suspect it can be done, I just haven't stumbled on how yet. if the answer is RTFM (or RTF forum), please point me to where it is - I'd be glad to read how to do this. I know I'm close but I just can't get the last bit. :)

I know I could just abandon this idea completely and just use the user_id everywhere (and that will work), but this seems like something I should be able to do and I'm curious how to do it.

Thanks,
Brian

[Isomorphic]

Docs for foreignKey are on DataSource.foreignKey.

The meaning of fkTableCode, fkColumnCode et al are also in the docs under dataSourceField.fkTableCode. This is related to automatically detecting that two SQL auto-derived DataSources have a foreignKey relationship.

Unfortunately we can't make any sense of the rest of your post. You are trying to insert some records into a DataSource called "test" that you didn't post. One of the fields (users.user_name) is an invalid field name. Other than the invalid field name, the data inserted into "test" doesn't bear any relation to the DataSource you did post. So we have no idea what you're getting at.

[bpipa]

Docs for foreignKey are on DataSource.foreignKey.

The meaning of fkTableCode, fkColumnCode et al are also in the docs under dataSourceField.fkTableCode. This is related to automatically detecting that two SQL auto-derived DataSources have a foreignKey relationship.

Unfortunately we can't make any sense of the rest of your post. You are trying to insert some records into a DataSource called "test" that you didn't post. One of the fields (users.user_name) is an invalid field name. Other than the invalid field name, the data inserted into "test" doesn't bear any relation to the DataSource you did post. So we have no idea what you're getting at.

I actually made up the test example for this post since it mimics my actual usage. I'll see if the docs clear things up for me, if not, I'll clarify and post more info.

Basic question... when you say "Docs for foreignKey are on DataSource.foreignKey. The meaning of fkTableCode, fkColumnCode et al are also in the docs under dataSourceField.fkTableCode." where exactly are "the docs"? I have found lots of docs but none that I have found seem to explain fully how to use foreign keys in the DS XML like this. Can you post the URL to the docs that you refer to? I don't see anything about foreign keys here:
http://www.smartclient.com/smartgwtee/server/javadoc/com/isomorphic/datasource/DataSource.html
and I see a little about them here:
http://www.smartclient.com/smartgwtee/javadoc/com/smartgwt/client/docs/serverds/DataSourceField.html but not enough to help me out.

Is there a sample project/example that uses foriegn keys in the DS XML that I coudl look to for guidance (I see the showcase has TeamMembers2.ds.xml that uses "includeFrom" so maybe that will help me)?

Thanks,
Brian

[Isomorphic]

Use the docs in the "serverds" package for properties that can be set in .ds.xml files. The QuickStart Guide covers the available docs.

There are 20+ samples in the EE Showcase that involve foreignKey. All the Field Include samples do, many of the others. We don't even follow what you're trying to do, so there's no way to be more specific.

[bpipa]

Use the docs in the "serverds" package for properties that can be set in .ds.xml files.

Do you mean this?
http://www.smartclient.com/smartgwtee/javadoc/com/smartgwt/client/docs/serverds/package-tree.html
or something else?

The QuickStart Guide covers the available docs.

yeah - I've looked through the SmartClient and SmartGWT quickstart guides and didn't find any info that helped.

There are 20+ samples in the EE Showcase that involve foreignKey. All the Field Include samples do, many of the others.

Ok, I'll poke around in there and see what I can find, thanks.

We don't even follow what you're trying to do, so there's no way to be more specific.

I'll see if I can find what I need based on what you did answer... if not, I'll add more details and try and convey what I'm asking in a way you can understand. Here's one more quick try:

Take a look at my original post and compare this that works:
record.put("user_id", 1);
vs this example of what I'd like to be able to do:
record.put(SOME_WAY_TO_REFER_TO_USER_NAME, "jsmith");
where
SOME_WAY_TO_REFER_TO_USER_NAME might be "users.user_name" or something similar (and I would have *bolded* those two lines in my original post if I knew how to bold text in this forum)

I'm just trying to figure out how to use the user_name instead of the user id when my table has the user_id as the foreign key. Basically, is there an easy way to go from the foreign id to the foreign user_name without doing a separate lookup every time? As an example usage, if I show data from a table using the user id foreign key, I don't want to show the user id, I want to show the user_name for that id. If this still doesn't clear it up (and it probably won't), I'll add more details later if I can't find answers elsewhere.

Thanks for your help,
Brian

[bpipa]

OK, in coding this up, I came up with a much more concrete example and created a brand new post with that info: http://forums.smartclient.com/showthread.php?t=28923