Hi,
I am new to Servoy and a former Foxpro developer.
In Foxpro i am accustomed to use candidate keys, how can i get the same result in Servoy?
Best regards,
Maurice
Ambitius Software Development
What are Candidate keys?
I think that it’s a field that is not the primary key but that must be unique. (for exemple a customer code that is not the customer_id)
I think that it’s a field that is not the primary key but that must be unique. (for exemple a customer code that is not the customer_id)
If that is the case you can create an integer field in the database and make it so that either the database or Servoy incements the value upon creation of a new record added.
Exactly it is a field or combination of fields that must be unique but it is not the primairy key.
But, when a have a primairy key that is my customer_id and a want the customer code to be unique also, how can I achieve that?
do i need to write a script for that?
Thanks
maurice:
But, when a have a primairy key that is my customer_id and a want the customer code to be unique also, how can I achieve that?
do i need to write a script for that?
You don’t need a script for that. Take a look at the “Auto Enter” tab of your table, there you’ll find the option to have your field filled automatically with a sequence or an uuid.
But i don’t want a field filled automatically with a sequence or an uuid, i want a message if a value already exists in the database.
In that case you will have to script that indeed.
Hi maurice,
This is typically a thing that the database server handles for you. You do need to apply a ‘unique constraint’ on that column or columns.
One downside of this is when you deploy you solution to a server with an empty database then Servoy will create the tables for you but not the unique constraint (just like it doesn’t create indexes for you).
This might not be an issue in your situation, for example, when you distribute your solution with the (Sybase) database file or when you supply a DDL (Data Definition Language) SQL script with it that creates the tables and all.
You could also have an onStart method in your solution that checks the existence of these constraints/indexes and apply them when needed (should be a one time thing).
If you don’t want to go this route at all then you need to use a method that you call yourself when you apply a new customer_id or attach this method to an onInsert table event within Servoy so it’s triggered automatically.
So plenty of options here to choose from.
Hope this helps.
Ok, thanks for the help!
Hi Maurice
In the process of data modelling the field or combination of fields (composite candidate key) is called candidate key (field) because the single field or the combination of fields has/have the potential to become the identification key (also called primary key although in detail this is not correct as in theory a identification key has to be unique whereas a primary doesn’t have to be but commonly in praxi is). That’s the reason why a field is called a candidate field.
Regards, Robert
maurice:
Exactly it is a field or combination of fields that must be unique but it is not the primairy key.
But, when a have a primairy key that is my customer_id and a want the customer code to be unique also, how can I achieve that?
do i need to write a script for that?Thanks
Hi Maurice
Opposite to Robert’s (Roclasi) view I don’t think it’s a downside if you do handle the constraints in the model of the database, but a big advantage to do it there. You may know about the MVC (Model View Controller) paradigma where there is an absolut clean separation of the model > simplified for this discussion the database, the view > Servoy forms and the controller > JavaScript or Java code in Servoy Developer. A very clean example of the MVC model, also in praxi, is WebObjects (btw, an example done in WO is the Apple Store). The MVC paradigma says in short you should separate the three layers (tiers) to be independant of each other.
In Servoy you can cleanly separate the model from the view/controller, although I don’t know how many Servoy developers do that. You can do your data modelling and code generation for the database in various tools like Entity Modeler (free, standalone or Eclipse plug-in) or Sybase PowerDesigner or Oracle Designer. The view and controller parts are alltogether within the Servoy Developer.
As you additionally can manipulate database parts, i. e. tables and columns in the Servoy Developer, you actually could (can) do quite all parts in Servoy. As some or many Servoy developers do that, there may always be differences of how the various developers “look” at a problem and a possible solution and it may help to take that into consideration when getting answers in the forum.
For example, if you design your database you specify if an attribute has to be unique and (therefor) as well mandatory or if it’s an optional (NULL allowd) attribute. These specifications can then be transformed from the Entity Relationship Model (or an enhanced version of that theory) to the Relational Model which is the one understood by the database by an exactly defined transformation process where you may have options to influance the transformation priocess, for example when you designed so called Supertype/Subtype Entities where you have 3 choices on how you want them transfered to the Relational Model, i. e. tables and columns.
What I try to say if you have the possibility and ability, keep the model separate from the view and controller which is anyway merged into Servoy Developer. This of course is only true if you believe in the advantages of the MVC paradigma. At least when working with Servoy you have the chance to separate model from the rest.
An advantage of that is that the database already “knows” about all these things, which means you put the necessary “logic” to the database (what it is made for and optimized for since many many years) or you program all that logic as well in the Servoy Developer with the help of JavaScript or Java.
You have to make the decision
Hope this helps a bit, best regards, Robert
ROCLASI:
Hi maurice,This is typically a thing that the database server handles for you. You do need to apply a ‘unique constraint’ on that column or columns.
One downside of this is when you deploy you solution to a server with an empty database then Servoy will create the tables for you but not the unique constraint (just like it doesn’t create indexes for you).
This might not be an issue in your situation, for example, when you distribute your solution with the (Sybase) database file or when you supply a DDL (Data Definition Language) SQL script with it that creates the tables and all.
You could also have an onStart method in your solution that checks the existence of these constraints/indexes and apply them when needed (should be a one time thing).
If you don’t want to go this route at all then you need to use a method that you call yourself when you apply a new customer_id or attach this method to an onInsert table event within Servoy so it’s triggered automatically.So plenty of options here to choose from.
Hope this helps.
Hi Robert,
Robert Huber:
Opposite to Robert’s (Roclasi) view I don’t think it’s a downside if you do handle the constraints in the model of the database, but a big advantage to do it there.
I never said that using a unique constraint had a downside other than when deploying your solution to a virgin Servoy server install it won’t be applied to the database.
And in fact I think using a unique constraint is the preferred way to go for the same reason I think sequences should be managed in the database layer as well. But that is just my preference.
Hi Robert
I didn’t want in any way offend you of course (I very much assume you know that). I just took the opportunity to (try to) explain how there are many different views on how an answer in the forum can be implicitly based on. This was a quite a big problem for me to realize when I joined this forum originally as I made quite a few wrong assumptions.
I also wanted promote the idea that with Servoy you can at least separate very well the model from the rest and this decision has to be made sensibly at the beginning of a project.
Best regards, Robert
ROCLASI:
Hi Robert,Robert Huber:
Opposite to Robert’s (Roclasi) view I don’t think it’s a downside if you do handle the constraints in the model of the database, but a big advantage to do it there.I never said that using a unique constraint had a downside other than when deploying your solution to a virgin Servoy server install it won’t be applied to the database.
And in fact I think using a unique constraint is the preferred way to go for the same reason I think sequences should be managed in the database layer as well. But that is just my preference.
Robert Huber:
I didn’t want in any way offend you of course (I very much assume you know that).
Yes I know
I was actually only refering to the first part of the line I quoted.
But yes, more (different) views on a subject is a good thing.
You can also separate the model cleanly inside Servoy.
Personally, I’m a dumb database kinda guy (not looking for an argument, both side have their virtues – just my preference). I don’t like constraints, triggers, stored procedures. I’d rather manage those things inside my application. Additionally, I think there are aspect of a “Model” in MVC that are handled nicely in Servoy dataprovider calculations, relationships, and table events that couldn’t necessarily be done well at the SQL level.
For new solutions, I always now create a “[solution]_model” module that contains my data-layer info that can be shared across multiple UI (view/controller) solutions – it contains calculations, aggregates, relationships, table events, etc.
greg.
agiletortoise:
or new solutions, I always now create a “[solution]_model” module that contains my data-layer info that can be shared across multiple UI (view/controller) solutions – it contains calculations, aggregates, relationships, table events, etc.
I like that idea. Maybe it’s time to start some sort of best practices thread/forum.
Hello Greg
As you would assume from my post(s) I personally don’t think it’s just a personal preference what to use. If you use the database just as independant containers to store data in them and connecting them together in the application logic you are throwing away all the work relational database (RDBMS) vendors have invested since the first commercially available RDBMS (Oracle) become available. So ignoring all this tens of thousends of days of work doesn’t seem very logical to me, or as you say, a personal preference.
I also don’t know what you mean by saying you don’t like constraints, triggers and stored procedures. To me it’s like saying I don’t like variables, arrays, methods and …
Why would anyone program a constraint in application logic (at may be n places) when you can define it once in the database?
To me, if someone uses a database just as a persistance container, it seems quite a big overkill to me to use a whole RDBMS just for that, but of course I may be wrong. I am asking myself why database vendors worked so hard to improve their RDBMS since the 80’ies if for no reason but just to store some data persistent?
With Servoy we build database frontend application, so it already implies the database is a major part of the app, so why not use the things database vendors developed (for good reasons I assume) during the last 30 years?
Of course I am not here to tell you how to do things, so you are still free to do whatever is appropriate. I just don’t get it why so many programmers put such big efforts into programming something which is already here, ready to use.
I would also like to say that english is not my native language, so I am not always sure if my written comments contain appropriately what I would like to say in a good manner.
Best regards, Robert
agiletortoise:
You can also separate the model cleanly inside Servoy.Personally, I’m a dumb database kinda guy (not looking for an argument, both side have their virtues – just my preference). I don’t like constraints, triggers, stored procedures. I’d rather manage those things inside my application. Additionally, I think there are aspect of a “Model” in MVC that are handled nicely in Servoy dataprovider calculations, relationships, and table events that couldn’t necessarily be done well at the SQL level.
For new solutions, I always now create a “[solution]_model” module that contains my data-layer info that can be shared across multiple UI (view/controller) solutions – it contains calculations, aggregates, relationships, table events, etc.
greg.
First off, your manner was fine.
As I said, there are plenty of valid reasons to use “smart” databases in your application design. However, there are plenty of valid reasons not to as well.
Just because I don’t put my “Model” logic at the RDBMS level doesn’t mean I repeat myself. I believe you mentioned WebObjects as a good example of MVC design…it is, and it largely wraps your database rows in objects with your logic in them – logic that is written at the application level. Ruby on Rails “ActiveRecord” is another good example where the business logic of the model is at the application level. There are many Java ORM products that do the same thing.
This can be good. It allows you to maintain DB independence. It allows your application logic to all be written in the same language and reside in the same repository. From a development point of view is often much easier to maintain over the application life-cycle, where you don’t have to worry about clashing code layers at the DB-level.
I’m not trying to convince you to change your ways, but I also don’t feel bad about all the man hours that have gone into perfecting Oracle. A lot of man hours have gone into Microsoft Word as well, but I’d rather use a text editor to write something – that, I hope, we can at least agree is a personal preference!
greg.
Hello Greg
Yes, you are right, mainly to achieve the object relational approach.
agiletortoise:
First off, your manner was fine.As I said, there are plenty of valid reasons to use “smart” databases in your application design. However, there are plenty of valid reasons not to as well.
Just because I don’t put my “Model” logic at the RDBMS level doesn’t mean I repeat myself. I believe you mentioned WebObjects as a good example of MVC design…it is, and it largely wraps your database rows in objects with your logic in them – logic that is written at the application level. Ruby on Rails “ActiveRecord” is another good example where the business logic of the model is at the application level. There are many Java ORM products that do the same thing.
May be I don’t understand exactly what you mean here, but we do use already at least 2 languages in Servoy, JavaScript and SQL (embedded), so there is no additional language when using a database as it is also SQL. But may be you mean something else? And in the application life cycle, we do have to worry about the database in any case, in whatever way we use it, do we? (I currently set up a new server and have to “fight” with a new version of Postgres as well as a new version of SQL Anywhere
agiletortoise:
This can be good. It allows you to maintain DB independence. It allows your application logic to all be written in the same language and reside in the same repository. From a development point of view is often much easier to maintain over the application life-cycle, where you don’t have to worry about clashing code layers at the DB-level.
I also don’t feel bad about all the man hours that have gone into perfecting Oracle (some of them have I lost in just using Oracle
Hey, that’s great, I completely agree with you about Word
agiletortoise:
I’m not trying to convince you to change your ways, but I also don’t feel bad about all the man hours that have gone into perfecting Oracle. A lot of man hours have gone into Microsoft Word as well, but I’d rather use a text editor to write something – that, I hope, we can at least agree is a personal preference!greg.
Best regards, Robert