DocID, GUID, and primary keys

[josevelez]

I'm creating my database from scratch using in.de and I am confused about something. The documentation recommends the use of a docid for a primary key.

I understand and like the reasoning behind using a docid versus a standard counter or incremental value for a primary key but I'm lost as to how to go about creating a primary key within in.de. I also noticed reference to guid and I saw some related functions for converting between docid to guid and vise versa.

Could someone elaborate more on this please?

Thanks!

Jose

[g.lanzi]

Ok, let me try to explain the DocIDs a bit.

A DocID is a a string of 20 characters, and it's stored inside a charfixed(20) database field. Usually they're adopted toghether with the document orientation, for that the entity framework has a service called "Document Identification" that is able to assign to each new document its own DocID.

A DocID has the same expression potential of a GUID, exactly the same. In fact a DocID is a GUID compressed. A DocID (just like a GUID) is really unique and can't be reproduced by any other program in the world. That's why they're so useful.

Two databases (with the same structure) in which the primary keys are DocIDs can be easly merged without collision. That's why a DocID Primari Key is the basis for the bidirectional synchronization of the mobile offline apps created with In.de.

If you aren't using the DO, then using a DocID can be a little bit confusing, because the database isn't able to automatically assign a new DocID to a new row. You can use the DocID2GUID (http://doc.instantdeveloper.com/eng/?ARTID=52468577-C145-408A-ABA2-BE8B0A7CD2D3 ) and the Guid2DocID (http://doc.instantdeveloper.com/eng/?ARTID=02180EE6-18FF-477D-A92B-85BEA00ABA27 ) functions to convert one format into the other, and you can use the newDocID function to get a newDocID (http://doc.instantdeveloper.com/eng/?ARTID=4F1F2E61-D694-492A-B076-E1FAF58D84DC ) whenever you want.

If you want to use a DocID in a database oriented panel, you have to use the newDocID in the ValidateRow event, writing something like:

Code: Select all

if (ID == null)
  ID = newDocID();


[josevelez]

Giuseppe,

I'm still a little confused. So If I am creating my database from scratch inside of In.de, when I create a new table and add fields how do I setup the primary key?

What should the primary key be? DocID or GUID? What is the appropriate use for each?

Thanks!

[john w]

You should find a Primary Key option when you look at the properties for the field you want as the primary key in the table you are working on. Double click the field name, Its a checkbox on the lower left. I have not done it another way but you might be able to set it through the properties view, under View -> Properties. Set Key to True

[josevelez]

John,

I noticed where I can specify primary key but I'm still not 100% on how to use the DocID and GUID. That is what is hanging me up.

I've build my database inside of MySQL with no problems (around 120 tables) but In.de seems to work a little bit differently so I want to start from scratch within In.de.

What I want to do is take advantage of using something like a DocID or GUID for my primary key instead of a typical integer (auto increment) value. The problem is that I don't understand exactly how this gets done.

What I would like to know is:

1. What exactly are GUIDs and DocIds used for?
2. How do I know when or where to use either?
3. How do I implement one of these as a primary key when creating tables from scratch within In.de.

Thanks!

Jose

The attachment primarykey.png is no longer available

[john w]

I will be honest I don't know anyone using guide as primary keys. It seems to me it would complicate that whole primary key foreign key relationship. For similar reasons the docid may not be a good choice for primary key. I forget how many characters are in the guid but to me it is neither logical or easily remembered. While it is self generated so is a primary key field set as an identity constraint

[josevelez]

I will be honest I don't know anyone using guide as primary keys. It seems to me it would complicate that whole primary key foreign key relationship. For similar reasons the docid may not be a good choice for primary key. I forget how many characters are in the guid but to me it is neither logical or easily remembered. While it is self generated so is a primary key field set as an identity constraint

John,

At first it does not seem intuitive, but I agree with the documentation that when you start to deal with multiple databases (think mobile and server instances) you could have problems when a counter is used to generate primary keys.

For example, say I have an application that tracks accidents real time by letting users with their mobile devices add accidents as they see them while driving. If each user is adding an accident (whether their device is on or offline) then each is generating a new primary key with that new accident record. What happens if two or more users try to add an accident with a primary key of 1, 2, 3...? See the problem? Each database, depending on whether the application is using an offline local database or the server database, is only aware of its own generated keys.

From what I gather, the DocID or GUID would eliminate this problem since the key that is generated is unique across all domains/databases/whatever you want to call it.

By the way, it was page 39 of the User's Manual where I saw mention of DocID and GUID.

Giuseppe,

Could you elaborate more on this? Or correct me if somehow I've misinterpreted this.

Thanks!

[g.lanzi]

@josevelez you got the picture, DocIDs are substitutes of the normal Counter Primary Key fields, they solve completely the collision of two identifiers created by different databases or applications.

Speaking about the data type, a DocID is a 20 characters string and a GUID is a 36 character string. A DocID can contain a lot of strange characters (;,:.-/%&/$()£é*[]{} etc...) while a GUID can contain only the characters in the ranges A-Z, 0-9 and -. A DocID can be explained simply as a compressed GUID, and viceversa.

Actually, programming with In.de you always use DocIDs.

The databases can't create DocIDs, so if you want to use them you have to do something. If you use a database oriented panel you can simply use the NewDocID function to create a new DocID at the right moment, if you use a Document Oriented panel you have only to activate the Document Identification service in the classe's properties. This service simply create assigns a new DocID to the document when it has to be initialized.

Usually, when you want to create a mobile application you use document oriented classes, because they are able to synchronize and you can activate the identification service too.
As you said before, without the DocIDs the data sinchronization could be really hard because the counter created by the two databases (local SQLite and remote SQL Server, for example) aren't aligned.

[josevelez]

Giuseppe,

I'm a little slow so please bear with me, but I'm still fuzzy on the implementation part.

So when I create a new table do I still need to create an id field with integer data type with auto increment to be used as the primary key?

I'm not following you when you say that I have to do something to create DocIDs. How does a database oriented panel come into play when I'm just trying to setup my database?

Do I need to create a special field within my new table for the DocId?

Thanks!

Jose

[john w]

Jose

I have learned something new about the use of GUIDs. I still don't know anyone who has used them, but with the advent of mobile that changes a lot of things. So thanks for asking the question, and thanks to Giuseppe for the answer. I will follow this thread a while longer to learn more about the docid.