we're not talking about a computed column here, are we? Again, reactor works fine with db-generated INT primary keys... the only difference in my situation is that instead of int, it's a db-generated uniqueidentifier... where the only possible way to have the db generate a new value on insert (again,just like the INT PKs) is to set ROWGUIDCOL. I'm not sure if I'm making myself clear, and I think some test code is probably in order at this point...
_____ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gareth Cole Sent: Tuesday, December 12, 2006 12:06 PM To: [email protected] Subject: RE: [Reactor for CF] Two questions Hi Brian, I think it was myself that originally had the problems with computed columns. Since then I've been fine using reactor in new projects where I can build the database with reactor in mind, but it's complicated using reactor with an existing db. As I understand it both issues would be solved by ignoring the column in INSERT and UPDATE statements. Could I propose that we add a 'readOnly attribute to the 'field' tag in the config file? This would be optional and default to false. Although this may not be as clean a solution as reactor determining computed cols etc from the db, it might be easier to implement, and would provide the developer with more control. There could be other scenarios where the developer would prefer reactor did not update a column. I'd be willing to spend some time over the holidays looking at code for this fix, if people think it is sensible. Gareth -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brian Kotek Sent: 12 December 2006 16:26 To: [email protected] Subject: Re: [Reactor for CF] Two questions No, we've used Reactor with SQL Server using unique identifiers as primary keys and had no problems (well, we've had some problems but not related to the fact that we were using unique identifiers as our primary keys). The separate issue is the SQL Server-specific ROWGUIDCOL, which can be applied to any column, not just the primary key. The issue here is not that someone is trying to change the primary key. First, it is perfectly valid to change the primary key as long as the new key meets the unique constraint. Second, what if you are not altering the value but are simply inserting the same value into the table again along with other data? This is what Reactor does when you save via a DAO and it works just fine, EXCEPT for columns with the ROWGUIDCOL property enabled. Those columns are not allowed to be part of an update statement at all. So the long and the short is: you can use SQL Server as a back end for Reactor just fine, as long as you do NOT enable the ROWGUIDCOL property. To make Reactor work with this property will require fairly significant changes to the way Reactor generates the components for a table, becuase it will have to leave ROWGUIDCOL columns in the Records (getter and setter) but will have to intelligently omit those columns from any insert or update statements. The same solution will also probably allow Reactor to work with computed columns. It isn't going to be a simple fix though, and since the ROWGUIDCOL is specific to SQL Server only, it is probably not an extremely high priority to modify the whole framework just to allow this one database-specific bit of behavior. Regarding your specific issue, make sure you have the latest from SVN, delete ALL the generated Reactor files, purge your template cache in the CF administrator, and if necessary for a reinitialization of your application (if you have such a setup) to force Reactor to re-read the XML and regenerate the CFCs. Hope that helps, Brian On 12/12/06, Marc <[EMAIL PROTECTED]> wrote: Brian et al, >From the SQL Server BOL: ---------------------------------------------------------------------------- Globally Unique Identifiers Although the IDENTITY property automates row numbering within one table, separate tables, each with its own identifier column, can generate the same values. This is because the IDENTITY property is guaranteed to be unique only for the table on which it is used. If an application must generate an identifier column that is unique across the entire database, or every database on every networked computer in the world, use the ROWGUIDCOL property, the uniqueidentifier data type, and the NEWID function. When you use the ROWGUIDCOL property to define a globally unique identifier column, consider that: * A table can have only one ROWGUIDCOL column, and that column must be defined using the uniqueidentifier data type. * SQL Server does not automatically generate values for the column. To insert a globally unique value, create a DEFAULT definition on the column that uses the NEWID function to generate a globally unique value. * The column can be referenced in a select list by using the ROWGUIDCOL keyword after the ROWGUIDCOL property is set. This is similar to the way an IDENTITY column can be referenced using the IDENTITYCOL keyword. * The OBJECTPROPERTY function can be used to determine if a table has a ROWGUIDCOL column, and the COLUMNPROPERTY function can be used to determine the name of the ROWGUIDCOL column. * Because the ROWGUIDCOL property does not enforce uniqueness, the UNIQUE constraint should be used to ensure that unique values are inserted into the ROWGUIDCOL column. ---------------------------------------------------------------------------- Because the above mentions the use of the UNIQUE constraint, I looked that up as well... I've highlighted relevant text in bold green. UNIQUE Constraints You can use UNIQUE constraints to ensure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of: * A column, or combination of columns, that is not the primary key. Multiple UNIQUE constraints can be defined on a table, whereas only one PRIMARY KEY constraint can be defined on a table. * A column that allows null values. UNIQUE constraints can be defined on columns that allow null values, whereas PRIMARY KEY constraints can be defined only on columns that do not allow null values. A UNIQUE constraint can also be referenced by a FOREIGN KEY constraint. ---------------------------------------------------------------------------- Finally, my main reason for using uniqueidentifier as the primary key datatype for all my tables is also covered by the BOL: Using uniqueidentifier Data The uniqueidentifier data type stores 16-byte binary values that operate as globally unique identifiers (GUIDs). A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value. The main use for a GUID is for assigning an identifier that must be unique in a network that has many computers at many sites. ---------------------------------------------------------------------------- The above represents the exact qualities I am looking for in a primary key for all my tables that are to be used with Reactor. For a simple example, I just created a table using Enterprise Manager, and did not relate it to any other tables. I created it with a uniqueidentifier column, and set that column as the primary key. I then turned on "isRowGUID" for the myKey column, which automatically populated the default value to newid()... consistent with the BOL instructions above. Finally, I added a sample column "myFirstColumn" for regular data. As expected, adding a sample row to this table automatically generated a key in myKey column, for that row. Since I set myKey as the primary key (little yellow key appears, in Enterprise Manager), and since I used a datatype of uniqueidentifier, and since I set is Is RowGUID (ROWGUIDCOL) and made the default value of for the column newid(), SQL Server will now generate a GUID for each new row inserted into the table, and guarantee their uniqueness within the table... with the added benefit that the GUIDs will likely be unique across all databases, theoretically across the entire planet. (This has saved my butt several times, btw.) So, based on all of the above, here is the sample table schema that was generated by Enterprise Manager: ---------------------------------------------------------------------------- CREATE TABLE [sampleGUID] ( [myKey] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT [DF_sampleGUID_myKey] DEFAULT (newid()), [myFirstColumn] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , CONSTRAINT [PK_sampleGUID] PRIMARY KEY CLUSTERED ( [myKey] ) ON [PRIMARY] ) ON [PRIMARY] GO ---------------------------------------------------------------------------- This is a perfectly valid configuration, that I think should work with Reactor. The more complex (real world) table schema I submitted on 12/10/2006 follows the same exact logic as above, only it had an additional constraint for it's foreign key, which again, should not cause problems for Reactor. So... can anyone confirm that this table schema / primary key datatype will in fact work with Reactor, and if not, can Reactor be modified to do so? Brian, simply put, once should NOT be updating a column that has the ROWGUIDCOL attribute, as that would mean someone is trying to insert or change a Primary Key manually... defeating the purpose of using uniqueidentifier / ROWGUIDCOL / newid(), which lets the DATABASE do what it's supposed to do - generate and maintain uniqueness of primary keys. Foreign Keys, on the other hand, one would expect to insert/update. But then, I I've never set any of my foreign key columns to have the ROWGUIDCOL property, since they are not the, uh, row's GUID column... the primary key column is. Therefore, a foreign key column can certainly have a datatype of uniqueidentifier (but would not possess the ROWGUIDCOL or newid() default value like the primary key column does), so the table can be related to another table(s)... but again, still using primary keys of datatype uniqueidentifier. Welcoming community input on this... am I the only one trying to use MSSQL db-generated uniqueidentifiers as my table primary keys, with Reactor? Marc _____ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brian Kotek Sent: Monday, December 11, 2006 11:26 PM To: [email protected] Subject: Re: [Reactor for CF] Two questions Note also that the ROWGUIDCOL attribute is probably going to cause additional problems because SQL Server will throw an error if you try to update a column with this attribute. On 12/11/06, Tom Chiverton <[EMAIL PROTECTED]> wrote: On Monday 11 December 2006 16:26, Marc wrote: > Isn't it BOTH? It's a primary key, and it's of datatype > "uniqueidentifier"...? There may be many unique IDs in a row ('candidate primary keys'). There can be only one actual 'primary key'. -- Tom Chiverton Helping to synergistically optimize 24/365 clusters **************************************************** This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ <http://www.mail-archive.com/reactor%40doughughes.net/> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
