Massaging data tables to stay in sync?

Hi Folks, I have a requirement to ensure one table is up-to date compared to another two. For example:

TableActivities
pk_id ¦ act_code ¦ act_name ¦

1 ¦ AX1 ¦ Apply Xylene
2 ¦ AX2 ¦ Remome Xylene
3 ¦ PA10 ¦ Apply Zinc
4 ¦ PA22 ¦ Apply Epoxy

TableSurfTypes
pk_id ¦ an_code ¦ surf_type_detail ¦

1 ¦ 1 ¦ Some Detail etc.
2 ¦ 2 ¦ Some Detail etc.
3 ¦ 8 ¦ Some Detail etc.
4 ¦ 10 ¦ Some Detail etc.
5 ¦ 15 ¦ Some Detail etc.
6 ¦ 21 ¦ Some Detail etc.

TableAct_SurfTypes
pk_id ¦ an_code ¦ act_code ¦ detail ¦

1 ¦ 1 ¦ AX1 ¦ Detail Text etc.
2 ¦ 2 ¦ AX1 ¦ Detail Text etc.
3 ¦ 1 ¦ PA10 ¦ Detail Text etc.
4 ¦ 2 ¦ PA10 ¦ Detail Text etc.

I need to ensure that there is one record in ‘TableAct_SurfTypes’ for every combination of 'TableActivities.act_code and ‘TableSurfTypes.an_code’!

This will be performed in at least two instances, when a new TableSurfTypes record is added (or deleted - where TableSurfTypes records will be deleted also) and when a new TableActivities record is added or deleted.

In the past I’ve used queries to build a model of all combined possibilities, check each of these for existence in TableAct_SurfTypes, then update it if nothing found, stepping through each element of the dataset in code. However, I recognise that there are often much more elegant ways to do these things in Servoy - so would welcome any suggestions on alternative methods of handling this kind of situation, hopefully without resorting to rawSQL.

We have several areas where these techniques need to be applied so I’d rather get it right now with this less complicated model so I’d very much appreciate feedback on how this type of data manipulation would best be built in Servoy, from those more experienced than I!

Cheers

Hi Jan

I think it would help if you could tell us what are the primary keys of the tables and what are the relationships between them. I assume if you get the design right, you don’t need to “massage” the tables, the design and it’s implementation with the constraints would get you the desired results.

Regards, Robert

Robert Huber:
I think it would help if you could tell us what are the primary keys of the tables and what are the relationships between them. I assume if you get the design right, you don’t need to “massage” the tables, the design and it’s implementation with the constraints would get you the desired results.
Regards, Robert

Thats what I was hoping but I’m not sure how I can set relations to support that? The tables are related (notionally - no relationships created yet) on the following:

TableActivities_to_ TableAct_SurfTypes on act_code

TableSurfTypes_to_TableAct_SurfTypes on an_code

Does that make sense Robert or did I misconstrue what you were suggesting?

Have a look at table events, they are perfect for this kind of task.

ngervasi:
Have a look at table events, they are perfect for this kind of task.

I have to disagree, since Servoy uses PRE-insert/update/delete table events this will be an issue when you are using foreign key constraints on the database (since the PK isn’t inserted yet this will give errors).
This is something I ran into last year when I tried to do something similar that Ian tries to do here.
I actually gave a presentation (Advanced DbTreeView) about this technique at ServoyWorld 2008. The technique shows how to fill a adjacency list (table) of all the possible links between nodes in a tree, so not very dissimilar what Ian tries to do.

Hope this helps.

I agree with you Robert, I was too in a hurry when I replied.

ROCLASI:
Advanced DbTreeView about this technique at ServoyWorld 2008. The technique shows how to fill a adjacency list (table) of all the possible links between nodes in a tree, so not very dissimilar what Ian tries to do.

Hope this helps.

Ahhhh… that presentation made my head burst Robert :shock: :o :D

Only saw the slide and I guess it was a lot easier to digest when the audience could hear your comentary :lol: But think I’d struggle to implement that functionality (doubtless pristine though it is!).

Might be simpler to follow my original tack - using SQL datasets and manually stepping through each. its not an operation that happens often so I dont think speed will ultimately be an issue - but I’ll revisit your slides and see if I can get to the bottom of it.

However be very interested to hear Robert Huber’s view as he implied there was some design of the tables and relationships that would ease the table sync’ing?

Kahuna:
Ahhhh… that presentation made my head burst Robert :shock: :o :D

Only saw the slide and I guess it was a lot easier to digest when the audience could hear your comentary :lol:

Well, to be honest I think my presentation wasn’t that clear either since I got very little questions and my impression was that not everyone got the gist of the presentation.
I might have to revisit this at ServoyCamp this year (for anyone who wants to know about it) because it’s such a powerful technique and Tano makes it even easier to implement.

ROCLASI:
[I might have to revisit this at ServoyCamp this year (for anyone who wants to know about it) because it’s such a powerful technique and Tano makes it even easier to implement.

That would be most welcome Robert - mean time I’ll take another look at it but I suspect in the short term I may have to take the ‘old way’ to get this functionality running - need it in the next two days to stay on track.

Just realised - this means I’ll need to stay ‘relatively’ sober at the camp!!! :shock:

Hi Ian,

So you want to have any combination of the 2 tables in TableAct_SurfTypes, right ? I assume the an_code and act_code are unique values in their respective tables.
If so then you can use the following methods that you trigger AFTER you insert or update data in the 2 tables.

function onInsertTableActivities() {
    var _sActCode   = arguments[0],
        _sServer    = "myServerName",
        _sQuery     = "INSERT INTO TableAct_SurfTypes (an_code,act_code,detail) SELECT an_code,?,surf_type_detail FROM TableSurfTypes";
    databaseManager.startTransaction();
    if ( !plugins.rawSQL.executeSQL(_sServer, "TableAct_SurfTypes", _sQuery, [_sActCode]) ) { 
        application.output("Error: " + plugins.rawSQL.getExceptionMsg());
        databaseManager.rollbackTransaction(); 
        return false;
    }
    databaseManager.commitTransaction();
    return true;
}


function onUpdateTableActivities() {
    var _sOldActCode    = arguments[0],
        _sNewActCode    = arguments[1],
        _sServer        = "myServerName",
        _sQuery;

    databaseManager.startTransaction();
    // remove all records that have this act_code value
    _sQuery = "DELETE FROM TableAct_SurfTypes WHERE act_code=?";
    if ( !plugins.rawSQL.executeSQL(_sServer, "TableAct_SurfTypes", _sQuery,[_sOldActCode]) ) { 
        application.output("Error: " + plugins.rawSQL.getExceptionMsg());
        databaseManager.rollbackTransaction();
        return false;
    }
    // Lets re-add the records with the new information
    _sQuery     = "INSERT INTO TableAct_SurfTypes (an_code,act_code,detail) SELECT an_code,?,surf_type_detail FROM TableSurfTypes";
    if ( !plugins.rawSQL.executeSQL(_sServer, "TableAct_SurfTypes", _sQuery, [_sNewActCode]) ) { 
        application.output("Error: " + plugins.rawSQL.getExceptionMsg());
        databaseManager.rollbackTransaction(); 
        return false;
    }    
    databaseManager.commitTransaction();
    databaseManager.flushAllClientsCache(sServer, "TableAct_SurfTypes");
    return true;
}

So when you insert a new record in TableActivities:

controller.newRecord();
// do your thing

if ( databaseManager.saveData() ) {
	if ( !onInsertTableActivities(act_code) ) {
		// something went wrong
	} 
} else { 
	// couldn't save the data
}

And to do an update in TableActivities:

var _sOldActCode = act_code;
// do your thing

if ( databaseManager.saveData() ) {
	if ( !onInsertTableActivities(_sOldActCode,act_code) ) {
		// something went wrong
	} 
} else { 
	// couldn't save the data
}

And here are the insert/update methods for the TableSurfTypes:

function onInsertTableSurfTypes() {
    var _sAnCode    = arguments[0],
        _sServer    = "myServerName",
        _sQuery     = "INSERT INTO TableAct_SurfTypes (an_code,act_code,detail) SELECT ?,act_code,surf_type_detail FROM TableActivities";
    databaseManager.startTransaction();
    if ( !plugins.rawSQL.executeSQL(_sServer, "TableAct_SurfTypes", _sQuery, [_sAnCode]) ) { 
        application.output("Error: " + plugins.rawSQL.getExceptionMsg());
        databaseManager.rollbackTransaction(); 
        return false;
    }
    databaseManager.commitTransaction();
    return true;
}


function onUpdateTableSurfTypes() {
    var _sOldAnCode     = arguments[0],
        _sNewAnCode     = arguments[1],
        _sServer        = "myServerName",
        _sQuery;
    databaseManager.startTransaction();
    // remove all records that have this act_code value
    _sQuery = "DELETE FROM TableAct_SurfTypes WHERE an_code=?";
    if ( !plugins.rawSQL.executeSQL(_sServer, "TableAct_SurfTypes", _sQuery,[_sOldAnCode]) ) { 
        application.output("Error: " + plugins.rawSQL.getExceptionMsg());
        databaseManager.rollbackTransaction();
        return false;
    }
    // Lets re-add the records with the new information
    _sQuery     = "INSERT INTO TableAct_SurfTypes (an_code,act_code,detail) SELECT ?,act_code,surf_type_detail FROM TableActivities";
    if ( !plugins.rawSQL.executeSQL(_sServer, "TableAct_SurfTypes", _sQuery, [_sNewAnCode]) ) { 
        application.output("Error: " + plugins.rawSQL.getExceptionMsg());
        databaseManager.rollbackTransaction(); 
        return false;
    }    
    databaseManager.commitTransaction();
    databaseManager.flushAllClientsCache(sServer, "TableAct_SurfTypes");
    return true;
}

Same deal here for the TableSurfTypes only with an_code.

Now for deleting any records in either table you simply use a relationship between the tables and the TableAct_SurfTypes table that have a cascading delete.

Hope this helps.

Robert… What a star my friend… going the whole mile again… :D :D :D :D

Fantastic stuff - I’ll digest that tonight over a vino (she who shall be obeyed - has been trying to get me out of the office for the last 2 hours).

Cant believe how you rattle this stuff off the way you do!

REALLY - thanks a bunch!

Have a good night - what’s left of it.

@Robert

Morning my friend, had the chance to step through your code from last night - thanks again for your input effort. :D

Unfortunately it’s not a route I can take for this task, as it hinges on deleting the combined table and re-building it! I had to dismiss that idea back in the old app because there is also a lot of hand entered data in the TableAct_SurfTypes. I should (on reflection) have indicated that the column ‘detail’ was there to represent all of the other columns that were not part of the relationship of the tables. Sorry Robert - it was obviously misleading :oops: .

So I guess, back to using SELECT queries and stepping through individual rows in code, to manually update the combined table :? .

Cheers

Hi Ian,

Kahuna:
I should (on reflection) have indicated that the column ‘detail’ was there to represent all of the other columns that were not part of the relationship of the tables.

So the detail column holds a concatenation of other columns of the 2 base tables ? If so you can simply select them in the insert/update.
Or will there be other data in there as well that are not in any of the 2 tables ?

ROCLASI:
Or will there be other data in there as well that are not in any of the 2 tables ?

Yes, other columns are mainly hand entered data which is stored there. It’s not generated from other tables. Therefore we cant delete it!

Background: TableAct_SurfTypes is a costings table, TableActivities lists the potential activities that can be carried out and TableSurfTypes is the type of surface on which these activities can apply to.

So the user can define all the activities his people may undertake, define all the types of surfaces he has, and my task is to ensure TableAct_SurfTypes has a row for each combination of Activity and Surface Type. When the row is in place, the user can populate these detail columns with time, material and cost data related to that Activity on that type of Surface. There are a number of these columns!

So I need to check for the existence of a particular combination and if it does not exist - create it!

So the issue hinges on the update procedure. You can savely use the insert methods.
About the update procedure, do you allow them to change the act_code or an_code at all in the 2 tables ? If not then the update methods are not needed.

ROCLASI:
So the issue hinges on the update procedure. You can savely use the insert methods.
About the update procedure, do you allow them to change the act_code or an_code at all in the 2 tables ? If not then the update methods are not needed.

Doh… I think you’re right Robert, in the two other tables (TableActivities and TableSurfTypes) users can only add or delete records, they can obviously edit some columns but not the primary an_code or act_code columns.

I’ve just reviewed the structure of these two tables and (originally) they had wacky pk’s - an_code in the case of TableActivities! They’ve been re-structured with a proper pk now since migrating them - so - you’re absolutely right. I can just update them and use a relation between for deletion!

Excellent Robert - Thanks for helping me get that straight in my head!

One last point: After running rawSQL do I need to do something to ensure all users get the latest data by broadcast?

Hi Ian,

Kahuna:
One last point: After running rawSQL do I need to do something to ensure all users get the latest data by broadcast?

Well, since they’re new records there is no (client) cache to invalidate but there is the new rawSQL plugin function named notifyDataChange(). But I am not sure if this will load any newly created records for the connected clients.
Perhaps someone from Servoy can comment on this.
You might have to use databaseManager.refreshRecordFromDatabase(foundset,-1) to load all the new records (per client).

Hope this helps.

ROCLASI:
Hope this helps.

Err… Just a LOT! :D