StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Updatable Primary Key problem

RE: Updatable Primary Key problem

Edhy Rijo — Thu, 07 Aug 2008 21:06:34 GMT

Today I started to implement GUID PK for a new SF project, and this thread showed to be an invaluable resource of information.

The code posted by Paul and Trent lead me to create my first Business Object base class and after creating 3 forms, you'll notice how useful is to have of a base BO class to handle things for you :hehe:.

So I just want to say THANKS! to everybody who participated in this thread, (including me ;)) because it is worthy.

Updatable Primary Key problem

Aaron Young — Thu, 07 Aug 2008 21:06:34 GMT

I want to set my own value for primary keys but the BO.Save() method doesn't write the primary key back to the database.

For example, I have an Int32 primary key called "CustomerID" and I set the value in SetDefaultValues() and the value is unique. I call BO.Add() which sets the value of CustomerID. I check the value of BO.CustomerID and it is correctly set to a valid value. However, when I do BO.Save() the CustomerID field is not written back to the SQL database (I can see this in SQL Profiler).

I have tried setting PrimaryKeyIsUpdatable to true but I guess I haven't done it correctly as Save() never writes the primary key.

Can anyone help?

Thanks in advance.

RE: Updatable Primary Key problem

Trent L. Taylor — Mon, 28 Apr 2008 11:38:13 GMT

[quote]Too many times the replication has to be dropped, the upgrade applied and then replication turned back on again. This in itself is not necessarily the problem but we could be dealing with 50G+ databases which are then pushed out to the subscribers.[/quote]

The really nice thing that has been added in the 1.6.6 beta are pre and post deployment scripts. This allows you to turn this off if you need to....while still using the DDT. This has become a very handy implementation that we have started using ourselves (pre and post deployment scripts that is :))

RE: Updatable Primary Key problem

Aaron Young — Mon, 28 Apr 2008 10:52:17 GMT

Hi Paul & Trent,

So the DDT can handle replicated databases? That is brilliant! :) SQL Server 2005 does allows some minor upgrades to a replicated database but it is not enough and on some occasions it has even rolled back a new field addition 24 hours after it was applied. Too many times the replication has to be dropped, the upgrade applied and then replication turned back on again. This in itself is not necessarily the problem but we could be dealing with 50G+ databases which are then pushed out to the subscribers.

While it is true most PCs will have an NIC, we are faced with the situation when a single site PC with no interface has been creating transactions for years which then has to be merged with another of our systems. Basically, in our application, we really need a globally unique ID and I can't see beyond the ugly Guid(). In our case the sequential ID isn't guaranteed to be globally unique and wouldn't be any different to the integer mechanism we currently use - which still has conflicts from time to time.

Aaron

RE: Updatable Primary Key problem

Paul Chase — Mon, 28 Apr 2008 10:10:15 GMT

Aaron,

If you have an application that is going to replicate it would need a Nic to be able to do so , but I suppose it is possible that it could be an external type nic that is unplugged whilst the user is adding records. I went back and forth on the what method to use as well, below is some code that uses datetime ticks to sequence a guid.

As far as updating schema, with sql 2005 at least you can make some minor schema changes directly to the publisher. http://msdn2.microsoft.com/en-us/library/ms151870.aspx .

However it is easy to get A.F.U when doing anything major. I have not had to change much except a few alter table's to add a column or 2, but in my next major release I will probably be hating replication due to the number of schema changes I will be making, most likely I'll end up having to drop and re-add subsciptions.

In my case however I can restrict my users from using the software until the database and application has been updated. I create software for internal use only so I have more flexibilty than some when it comes to things like that.

[codesnippet]

''' <summary>

''' Creates A Seqeuntial Guid based on Date time values

''' </summary>

''' <returns>Sequential Guid</returns>

''' <remarks>converted from c# code found at

''' http://www.informit.com/discussion/index.aspx?postid=a8275a70-0698-46f0-8c8f-bf687464628c

''' I changed it slightly to use utc time due to Pensacola and Ft Walton being Central Time

''' so there would not be a time zone problem </remarks>

Public Shared Function NewSequentialGuid() As Guid

Dim laGuid() As Byte = Guid.NewGuid.ToByteArray

Dim ldBaseDate As DateTime = New DateTime(1899, 1, 1)

Dim ldNow As DateTime = DateTime.UtcNow

' Get the days and milliseconds which will be used to build the byte string

Dim lsdays As TimeSpan = New TimeSpan((ldNow.Ticks - ldBaseDate.Ticks))

Dim lsmsecs As TimeSpan = New TimeSpan((ldNow.Ticks _

- (New DateTime(ldNow.Year, ldNow.Month, ldNow.Day).Ticks)))

' Convert to a byte array

Dim laDays() As Byte = BitConverter.GetBytes(lsdays.Days)

'SQL Server is accurate to 0.003 part of a second

'.NET DateTime ticks are in milliseconds

'so we divide .NET ticks by 3.333333 and should be ok

Dim laSecs() As Byte = BitConverter.GetBytes(CType((lsmsecs.TotalMilliseconds / 3.333333), Long))

'Reverse the bytes to match SQL Servers ordering

Array.Reverse(laDays)

Array.Reverse(laSecs)

' Copy the bytes into the guid

Array.Copy(laDays, (laDays.Length - 2), laGuid, (laGuid.Length - 6), 2)

Array.Copy(laSecs, (laSecs.Length - 4), laGuid, (laGuid.Length - 4), 4)

'Send it back

Return New System.Guid(laGuid)

End Function

[/codesnippet]

RE: Updatable Primary Key problem

Trent L. Taylor — Sun, 27 Apr 2008 14:45:31 GMT

[quote]While we are on the subject, does the Database Deployment Toolkit support upgrading of replicated databases?[/quote]

I have been sick over the weekend and my wife is trying to keep me in bed so I don't get worse...but she had to go to the store :D so I thought I would do a little catching up on the forum.

To answer your question, yes, the DDT will most definitely update a database structure for a SQL Server that uses replication. When doing this, however, it is best to update the structures when there will be the least amount of activity on the databases (none if possible). but in any case, you can actually write a program to deploy your structures using the DatbaseMigrator class. There is a sample that comes with the framework that shows how to do this. You can create a program that updates all servers on separate threads at the same time...reducing deployment time and making the process more streamlined. It is the same thing that you would do otherwise, just updating multiple servers on different threads at the same time.

On a separate note here, there has been a lot of discussion on GUIDs and sequential GUIDs. There is one problem wth sequential GUIDs that exists when you will not be talking to a server and you need to come back in and merge the data. It is the same problem that exists when using integers. If you come up with a mechanism to create sequential GUIDs, and you take a laptop, let's say, offsite and it creates records, and there are other laptops doing the same thing, then you can definitly have sync issues. So this then turns into the situation where each laptop has its own pre-set range to work within, with ends up having the same issues as using a PK.

I recommend doing a little test. I am of the mind that it is easier to deal with the one outlier that may (though it is extremely unlikely) produce a duplicate GUID. If you create a program that creates new records non-stop using the System.Guid.NewGuid() for 24-48 hours (this will prouce some SERIOUS records!) I believe that you will find that there will more than likely never be a duplicate. If you del with disconnected data (pre-merge or replication) in thi maanner and perform a "pre-merge" query to ensure that there are no duplicates, then you don't get into the syncing issue of sequential GUIDs. This is just some food for thought.

RE: Updatable Primary Key problem

Edhy Rijo — Sat, 26 Apr 2008 15:57:30 GMT

[quote][b]Aaron Young (04/26/2008)[/b][hr]While I like the idea of sequential GUIDs the problem with UuidCreateSequential is it still uses the mac address and it may only guarantee uniqueness on the local PC if the PC doesn't have a network adapter.[/quote]

Hummm, not NIC, not program to run :P

RE: Updatable Primary Key problem

Aaron Young — Sat, 26 Apr 2008 15:38:37 GMT

I may be wrong but I believe Microsoft switched to version 4 GUIDs from Windows 2000 onwards. This version doesn't use the mac address as it was deemed a security risk given that it allowed the GUID to be traced back to a PC. Version 4 GUIDs uses random parts for all sections.

While I like the idea of sequential GUIDs the problem with UuidCreateSequential is it still uses the mac address and it may only guarantee uniqueness on the local PC if the PC doesn't have a network adapter.

RE: Updatable Primary Key problem

Peter Jones — Fri, 25 Apr 2008 21:13:33 GMT

Hi Aaron,

As I understand it is the presence of the network card's mac address (on the machine creating the GUID) in the GUID algorithm assures uniqueness.

Peter

RE: Updatable Primary Key problem

Aaron Young — Fri, 25 Apr 2008 17:25:25 GMT

One consequence of using any form of replication is future changes to the database schema. Most database schema comparison/updating tools have problems upgrading a database currently in use for replication. In an ideal world you would be able to change your database schema which would then be replicated across to other databases. SQL Server 2005 is better at allowing some updating but most upgrading tools will fail on a replicated database which could force you to remove replication, upgrade and then setup replication again.

While we are on the subject, does the Database Deployment Toolkit support upgrading of replicated databases?

I like the idea of sequential GUIDs but does this still guarantee they will be globally unique?

RE: Updatable Primary Key problem

Paul Chase — Fri, 25 Apr 2008 14:07:25 GMT

Like Trent said there are a ton of articles explaining the different types of replication written by people that know it and can explain it way better than I could.

Just like the decision to use integer or guid primary keys it really depends' on your business requirements as to what replication scenario would work best for you. Also do not forget that StrataFrame has the Enterprise Server which may be a better option again depending on what your requirements are.

Paul

RE: Updatable Primary Key problem

Edhy Rijo — Fri, 25 Apr 2008 13:29:23 GMT

Great!

Thanks Trent.

RE: Updatable Primary Key problem

Trent L. Taylor — Fri, 25 Apr 2008 13:02:37 GMT

Edhy,

Here is a step-by-step on setting up Merge Replication. Just serach on Google for about 1000 more articles.

http://www.databasejournal.com/features/mssql/article.php/1438231

And Paul is right on the sequential GUIDs...that is definitely the safest route to go.

RE: Updatable Primary Key problem

Edhy Rijo — Fri, 25 Apr 2008 11:57:35 GMT

[quote][b]Paul Chase (04/25/2008)[/b][hr]I now use Sql merge replication and guids to great effect. One thing that is a business requirment for me is the abilitly for an office to function without an UP network connection to corporate thus a client server type of environment would not work.[/quote]

Wow Paul, thanks for sharing this info with us.

My next VFP conversion project will definately make use of this code since the use of a GUID PK is a must.

About the above comment, would you mind explaining a bit more about how to use SQL merge replication I also have those needs for my next conversion project.

RE: Updatable Primary Key problem

Paul Chase — Fri, 25 Apr 2008 11:31:01 GMT

Guys,

If you want to use GUID's then you definately will want to create the guid sequentially. using the Guid.NewGuid function will create a truly random unsequential guid which well cause performance to suffer. By creating a sequential guid you are ensuring that records are inserted at the end thus keeping page splits and index fragmentation to a minimum.This is primarily what causes perfomance problem's when people use guid's as primary keys.Using this approach you will approach insert and select perfomance that is much closer to using ints. You still will have the usual drawbacks of using guids they are ugly and big etc. There are several articles that detail this in more depth just google sequential guids.

I have 17 offices that I converted\converting from Foxpro 2.6. I had a very elaborate data merge process for consolidating data at corporate office that ran all night for several hours. I now use Sql merge replication and guids to great effect. One thing that is a business requirment for me is the abilitly for an office to function without an UP network connection to corporate thus a client server type of environment would not work.

I have not had any perfomance issues so far, Like anything else it always comes down to making a decision based on your unique business requirements.

Below is a code snippet to generate a SEQUENTIAL Guid and to add it in the default values of you base BO.

[codesnippet]

Protected Overrides Sub OnSetDefaultValues()

'DoDefault

MyBase.OnSetDefaultValues()

'-- Set Value for GUID PK

'-- Get a reference to the property descriptor (which doesn't use reflection)

Dim lodesc As FieldPropertyDescriptor = Me.GetPropertyDescriptor(Me.PrimaryKeyField)

'-- If the PK is a Guid Generate a Sequential GUID to prevent Index fragmentation

If lodesc.PropertyType Is GetType(Guid) Then

lodesc.SetValue(Me, Payroll.Base.Common.NewSequentialID)

End If

End Sub

in my common class

Public Declare Function UuidCreateSequential Lib "Rpcrt4.dll" (ByRef guid As Guid) As Integer

''' <summary>

''' Creates A Seqentail GUID

''' </summary>

''' <returns></returns>

''' <remarks></remarks>

Public Shared Function NewSequentialID() As Guid

'-- Local Variables

Dim Retval As Guid

Dim LnResult As Integer

Try

LnResult = UuidCreateSequential(Retval)

If LnResult = 0 Then

Return Retval

Else

Throw New Exception("UuidCreateSequential failed: " & LnResult)

End If

Catch ex As Exception

Throw (ex)

End Try

End Function

[/codesnippet]

RE: Updatable Primary Key problem

Aaron Young — Thu, 24 Apr 2008 16:36:54 GMT

Hi Edhy,

That is good to know. We have tested with GUIDs to a limited degree and it looked okay but our big problem will be converting legacy databases to a new format. Some of the databases are literally in use 24x7x365. But I guess that problem is for another day.

RE: Updatable Primary Key problem

Edhy Rijo — Thu, 24 Apr 2008 15:57:25 GMT

Hi Aaron,

I have a VFP application installed in 17 branches which synchronize to the main office. My solution was to used GUID PK and a BranchID field, the GUID works just find and in the main office where all the repository is there has not been performance issues because all the fields are properly indexed and the generation of reports is normal.

I should note that the synch process is one way only from branches to the main office and that may not be your case. So if I would have to do this project in SF I would need to re-think the logic since instead of synchronizing I would have all branches connected to the main office database using SF Enterprise server, but still using the GUID PK.

RE: Updatable Primary Key problem

Aaron Young — Thu, 24 Apr 2008 15:56:51 GMT

I think you are right Trent. Whatever way we have tried to engineer it, it still results in conflicts somewhere down the line. Of course, that leaves us with a real headache as we have a large no of customer databases that need to be converted from the old legacy database running with int PKs to a revised database with GUIDs PK.

RE: Updatable Primary Key problem

Trent L. Taylor — Thu, 24 Apr 2008 15:52:11 GMT

In your case I would probably go with GUIDs so that you do not have to engineer the global PKs. When weighing the costs between the two the ease of the GUIDs would win out in my eyes as we have tried to engineer the other behavior in more than one instance and it always ends up out of sync at some point which causes issues. This would fall under the replication category that Paul was talking about earlier.

RE: Updatable Primary Key problem

Aaron Young — Thu, 24 Apr 2008 15:18:08 GMT

Very interesting performance information about GUIDs which is a concern as we were looking to migrate to GUIDs from int PKs.

We have a legacy system that involves many distributed databases per customer. All databases must be updatable even if the WAN connection is down. As a result, our int PKs must be globally unique which is why we have to update the PKs from the application. We had planned on migrating to a new database model that used GUIDs but these performance figures are worrying.

Unfortunately, our PKs must be globally unique across all databases and other than the performance problem, GUIDs looked the easiest option for an automatic solution rather than our manual int solution.

RE: Updatable Primary Key problem

Trent L. Taylor — Thu, 24 Apr 2008 14:20:09 GMT

[quote]Does the newsequentialid() function in SQL 2005 change any of the thinking about considering use of UIDs? would it be difficult to have newsequentialid() used to create UIDs on the back end? [/quote]

This isn't the issue. The issue is not when creating the PK, but rather when performing queries on the data. In VFP we used to use Integer PKs which was extremely frustrating because they did not have the AutoInc stuff when we started (and their AutoInc is nowhere in the same universe as SQL Server). However, since we used Integer PKs life did get much better once we got on SQL Server and did not have to convert from GUIDs to Integer PKs.

But back to the original point, the issue is not when creating the PK. If you want to use a GUID PK, don't let the server assign it, just call the System.Guid.NewGuid() method in the SetDefaultValues of the business object versus relying on the server. It is basically impossible to get two GUIDs of the same uniqueness anyway.

When using identiy fields in SQL Server, the BO has the ability to automatically retrieve the PK once a new record is created, which I am sure that you have already discovered. So the issue isn't the whole NextId() thing that you faced in VFP, but rather when you start to query the data to retrieve records within the DB.

Hope that makes sense :)

RE: Updatable Primary Key problem

Charles R Hankey — Thu, 24 Apr 2008 12:42:58 GMT

I certainly defer to you experience regarding using guids with newid() in SF. In vfp/vfe I would use guids because generating the key on the front end saved a trip to the server before populating child fks and I often had situations where tables were going to be merged or data moved around among numerous installations of a program. But I do see the problems - and the problems regarding using UID with a clustered index are obvious.

I am very impressed with the way SF seems to give the best of both worlds in one sense - handling the fk thing with integer keys with no problem.

Does the newsequentialid() function in SQL 2005 change any of the thinking about considering use of UIDs? would it be difficult to have newsequentialid() used to create UIDs on the back end?

I'm pretty sure I'll be using Int keys in SF for the most part, but just wondered about the practicality of sequential UIDs ?

http://www.fotia.co.uk/fotia/DY.19.NewSequentialId.aspx

RE: Updatable Primary Key problem

Edhy Rijo — Thu, 24 Apr 2008 11:33:10 GMT

Thanks Trent, points taken.:hehe:

RE: Updatable Primary Key problem

Trent L. Taylor — Thu, 24 Apr 2008 10:30:48 GMT

One word of caution, if you are not planning on using replication you should not use GUID primary keys! They create a lot of additional overhead and dramatically slow down queries on the SQL side versus an integer value. So if you are planning on your databases getting very large, then I would highly recommend against using a GUID. There is definintely a time and place for GUIDs and Paul's application is one of those, but if you are creating GUIDs just to create a GUIDs PK, then I would strongly recommnend against doing this. We have been spending a lot of time in T-SQL lately and working on very complex queries and tracing execution paths...when you have a GUID as a primary key versus an integer, especially on a large database, the query can execute as much as 100 times slower (yes times, not percent). A query that took 300 ms went to 3 ms. So as the database becomes much larger and this same query took 1 second it would go from being 1 second to 10 ms. This is a huge difference!

By taking this type of logic into account, we turned a query taking 4.5 seconds the other day on a very large database into 22 ms....that was a happy day :D We made an even larger impact when dealing with dates. Instead of storing these fields that we will be testing on with >= or <= as a date, we stored them as ticks in a BigInt field. This was the largest change in performance that we had seen thus far. Databases deal with integer data better than strings when it comes to performance and parameters. You won't have as large a hit on a PK field, but you don't want to box yourself into a corner either.

RE: Updatable Primary Key problem

Edhy Rijo — Thu, 24 Apr 2008 10:25:06 GMT

[quote][b]Paul Chase (04/24/2008)[/b][hr]Here is some logic I have in my base class BO to assign the PK which may help you out if you haven't added it yet.[/quote]

Hi Paul,

Thanks for the code must appreciated. Of course I did not have it since I am currently not using GUID for the current project, but yes, I will use it. :hehe: