Hello,
I used the table “servoy_columninfo” to generate PK indexes.The first column of each table is the identifier column.
select 'create unique index '||tablename||'_pk on '||tablename||' ('||columnname||');'
from servoy_columninfo
where columninfo_id in
(
select min(columninfo_id)
from servoy_columninfo
where connection_name = 'piperpilots'
group
by tablename
)
order by tablename
Now I’m looking for a Servoy repository table/view, which contains information about “relations”.
I want to use this info to generate Foreign key indexes in a postgreSQL application database.
Can someonw help me with this info?
Hi Peter,
You don’t need to put indices on primary key columns. When defined as Primary Key in the database they are indexed.
Only columns that you use as a (unique) row_ident that are not real PK’s you want to place an index on.
As for the relationships. Do keep in mind that not all relationships used in a solution will translate into real Foreign Key contraints in the database.
It might be easier to simply place the FK contraints yourself. How many tables are we talking about here?
I don’t want to generate indexes for forcing constraint validation.
I want to use the indexes for performance reasons. Servoy will sent a SQL statement to the database engine. In our case PostgreSQL.
The indexes will be used to prevent full table scans (based on my Oracle experiences…).
Ack!
Yes, I didn’t read your post correctly.
I think you might want to use the solutionModel to do this instead of to decipher the repository database.
Hi Peter,
Here is a way to get the indices for all the columns used in Servoy relationships that are not PK’s.
It uses the solutionModel and the databaseManager to get the tables and columns used in the relationships and to check if these are not PK’s.
function getFkIndices() {
var _sServer = "piperpilots",
_aRelations,
_aFields = new Array(),
_aRelItems,
_aTablePK,
_aCheck = new Array(),
i,
j;
// Sort function to be used in the array sort
function sortTableColumn(_aLeft, _aRight) {
if (_aLeft[0] > _aRight[0]) {
return 1
} else if (_aLeft[0] < _aRight[0]) {
return -1;
} else if (_aLeft[0] == _aRight[0] && _aLeft[1] > _aRight[1]) {
return 1;
} else if (_aLeft[0] == _aRight[0] && _aLeft[1] < _aRight[1]) {
return -1;
}
return 0;
}
// Lets get all the relationships
_aRelations = solutionModel.getRelations(_sServer);
for (i = 0; i < _aRelations.length; i++) {
_aRelItems = _aRelations[i].getRelationItems();
for (j = 0; j < _aRelItems.length; j++) {
// We don't want global variables, only real columnnames
if (utils.stringLeft(_aRelItems[j].primaryDataProviderID, 8) != "globals.") {
_aFields.push([_aRelations[i].primaryDataSource.split("/")[2], _aRelItems[j].primaryDataProviderID]);
}
_aFields.push([_aRelations[i].foreignDataSource.split("/")[2], _aRelItems[j].foreignColumnName]);
}
}
// Sort the tables and columns
_aFields.sort(sortTableColumn);
for (i = 0; i < _aFields.length; i++) {
// check for duplicate entries
if (_aCheck[0] != _aFields[i][0] || _aCheck[1] != _aFields[i][1]) {
_aCheck = _aFields[i];
// Check if this a PK, we don't need to apply an index on a PK. They already have one.
_aTablePK = databaseManager.getTable(_sServer, _aFields[i][0]).getRowIdentifierColumnNames();
if (_aTablePK.indexOf(_aFields[i][1]) == -1) {
application.output("CREATE UNIQUE INDEX " + _aFields[i].join("_") + "_idx ON " + _aFields[i][0] + " (" + _aFields[i][1] + "); ");
}
}
}
}
Just put this method in your solution that holds the relationships you want to get them from and run it in Developer. Adjust the _sServer variable accordingly.
The DDL will show up in the console window inside Developer.
Hope this helps.
Hello Robert,
I tired to use the solutionModel.getRelations(_sServer), but I receive only 2 relations. Though I have dozen of relations in my solution and in other modules. Any idea why?
Kind Regards,
Bogdan.
Hi Bogdan,
The method can only see the relations that are in the scope of it’s position in your solution. So it’s best to put this method in the main solution so it can see all the relationships of the linked modules.
Bogdan,
Is it perhaps an after_import module ? That runs in it’s own module space ![:(]( "Sad")
Regards,
I’ve made the call in my main solution, but check this:
[attachment=0]Relations.PNG[/attachment]
The relation: owner is marked with a blue icon, while the others are with black. I have only two relations marked with blue and only those are returned by that function. How can I make all of them blue? ![:)]( "Smile")
Hi Bogdan,
The blue color denotes a global variable on the left side of the relationship.
So these are global relationships or a relationship that is also filtered by a global variable.
Hi Bogdan,
udrescu_bogdan:
I’ve made the call in my main solution,
Are all these relationships using the same servername?
Hmm…I see the same thing. Not all relationships seem to be returned in the solutionModel.getRelations() call.
Will investigate a little further.
Hi Bogdan,
You are correct. Only the ‘blue’ ones are being returned. So only relationships that have a global variable on the left side of the relationship.
I will make a case for this.
Edit: scratch that. It’s me. ![:oops:]( "Embarassed")
When using ONLY the servername you get the global relationships. You also need to pass the tablename.
_aRelations = solutionModel.getRelations(_sServer);
Servoy help says :
getRelations([primary_server_name/primary_data_source], [primary_table_name]) : JSRelation
Gets an array of all relations; or an array of all global relations if the specified table is NULL.
undefined parameter is different then null parameter ?
I think the help should be : Gets an array of all relations of the primary_table_name
(And : all relations is not possible, but you can get all tables in an array from a server)
Regards,
Here is the fixed version:
function getFkIndices() {
var _sServer = "piperpilots",
_aRelations,
_aFields = new Array(),
_aRelItems,
_aTablePK,
_aTables,
_aCheck = new Array(),
i,
j,
k;
// Sort function to be used in the array sort
function sortTableColumn(_aLeft, _aRight) {
if (_aLeft[0] > _aRight[0]) {
return 1
} else if (_aLeft[0] < _aRight[0]) {
return -1;
} else if (_aLeft[0] == _aRight[0] && _aLeft[1] > _aRight[1]) {
return 1;
} else if (_aLeft[0] == _aRight[0] && _aLeft[1] < _aRight[1]) {
return -1;
}
return 0;
}
// lets get all tablenames of this connection
_aTables = databaseManager.getTableNames(_sServer);
_aTables = _aTables.concat([null]); // We also want any global relationship
for (k = 0; k < _aTables.length; k++) {
// Lets get all the relationships
_aRelations = solutionModel.getRelations(_sServer, _aTables[k]);
for (i = 0; i < _aRelations.length; i++) {
_aRelItems = _aRelations[i].getRelationItems();
for (j = 0; j < _aRelItems.length; j++) {
// We don't want global variables, only real columnnames
if (utils.stringLeft(_aRelItems[j].primaryDataProviderID, 8) != "globals.") {
_aFields.push([_aRelations[i].primaryDataSource.split("/")[2], _aRelItems[j].primaryDataProviderID]);
}
_aFields.push([_aRelations[i].foreignDataSource.split("/")[2], _aRelItems[j].foreignColumnName]);
}
}
}
// Sort the tables and columns
_aFields.sort(sortTableColumn);
for (i = 0; i < _aFields.length; i++) {
// check for duplicate entries
if (_aCheck[0] != _aFields[i][0] || _aCheck[1] != _aFields[i][1]) {
_aCheck = _aFields[i];
// Check if this a PK, we don't need to apply an index on a PK. They already have one.
_aTablePK = databaseManager.getTable(_sServer, _aFields[i][0]).getRowIdentifierColumnNames();
if (_aTablePK.indexOf(_aFields[i][1]) == -1) {
application.output("CREATE UNIQUE INDEX " + _aFields[i].join("_") + "_idx ON " + _aFields[i][0] + " (" + _aFields[i][1] + "); ");
}
}
}
}
Hi,
Yes, it works with the table name set! With it as null it’s the same thing, I get only those two relations.
But, I have global variables also in many of the other relations that are marked with a black icon!
Thanks! ![:D]( "Very Happy")
they are blue when the relation ship is completely global…
jcompagner:
they are blue when the relation ship is completely global…
I did do a quicky test by adding a global to a regular relation but I guess I didn’t see it correctly. It was a long list with relationships ![:)]( "Smile")
Anyway, I stand corrected.
