RE: Any Gotcha's in using CF UUID for db record primary key?
What's the reason for using the db instead of CF to generate the UUID? To keep the load off CF? I was thinking I might use CF in the following manner to generate a UUID for a record: - Create UUID and variable to hold UUID via CF and cfset - User completes form with textual information and photo selection via cffile - Run insert query to create primary textual record, including inserting UUID - Run second insert query to create records in photo table for each cffile field mentioned above, inserting UUID for relational key to textual information After doing some reading in the MySQL 5 docs, it looks like I could use the LAST_INSERT_ID() function to return the auto-incrementing primary key of the main textual record of a property (Real Estate property), then run an insert query for the photo records on another table, using the LAST_INSERT_ID() as the relational key to the primary property table. - User completes form with primary record info and selects photos - Insert query runs creating primary property record - Run another query to retrieve LAST_INSERT_ID() (or just use that value as a variable, #LAST_INSERT_ID()# ???) - Run another query to insert photos into photo table using LAST_INSERT_ID as relational key Using LAST_INSERT_ID(), it seems that I could allow a single input form for two tables, property and property_photos, create a more user-friendly work flow, and avoid the messiness of UUID altogether. Thoughts? Rick -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Sunday, March 23, 2008 11:48 PM To: CF-Talk Subject: RE: Any Gotcha's in using CF UUID for db record primary key? At the same time I kind of DON'T agree with them if you're doing what we were doing: if you're using the key to link tables (so that you can combine multiple databases easily) and using them to link to non-DB information (log files, etc) then it seems like extra work to do a real auto-increment PK as well. I'll second this. Either use UUIDs or don't. In either case, I'd recommend that you use your database's ability to generate these instead of doing it from CF. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Training: Adobe/Google/Paperthin Certified Partners http://training.figleaf.com/ WebManiacs 2008: the ultimate conference for CF/Flex/AIR developers! http://www.webmaniacsconference.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301896 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Any Gotcha's in using CF UUID for db record primary key?
What if you have four concurrent users (or four hundred if your app get popular!), what happens if user two gets user three's id when she runs LAST_INSERT_ID()? That's the one of the only reasons I can see for using alternate keys. Whether it is a UUID created in Coldfusion or someother unique value...you KNOW what it is...everytime, because you set it, not the DB. Mark -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Monday, March 24, 2008 8:22 AM To: CF-Talk Subject: RE: Any Gotcha's in using CF UUID for db record primary key? What's the reason for using the db instead of CF to generate the UUID? To keep the load off CF? I was thinking I might use CF in the following manner to generate a UUID for a record: - Create UUID and variable to hold UUID via CF and cfset - User completes form with textual information and photo selection via cffile - Run insert query to create primary textual record, including inserting UUID - Run second insert query to create records in photo table for each cffile field mentioned above, inserting UUID for relational key to textual information After doing some reading in the MySQL 5 docs, it looks like I could use the LAST_INSERT_ID() function to return the auto-incrementing primary key of the main textual record of a property (Real Estate property), then run an insert query for the photo records on another table, using the LAST_INSERT_ID() as the relational key to the primary property table. - User completes form with primary record info and selects photos - Insert query runs creating primary property record - Run another query to retrieve LAST_INSERT_ID() (or just use that value as a variable, #LAST_INSERT_ID()# ???) - Run another query to insert photos into photo table using LAST_INSERT_ID as relational key Using LAST_INSERT_ID(), it seems that I could allow a single input form for two tables, property and property_photos, create a more user-friendly work flow, and avoid the messiness of UUID altogether. Thoughts? Rick -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Sunday, March 23, 2008 11:48 PM To: CF-Talk Subject: RE: Any Gotcha's in using CF UUID for db record primary key? At the same time I kind of DON'T agree with them if you're doing what we were doing: if you're using the key to link tables (so that you can combine multiple databases easily) and using them to link to non-DB information (log files, etc) then it seems like extra work to do a real auto-increment PK as well. I'll second this. Either use UUIDs or don't. In either case, I'd recommend that you use your database's ability to generate these instead of doing it from CF. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Training: Adobe/Google/Paperthin Certified Partners http://training.figleaf.com/ WebManiacs 2008: the ultimate conference for CF/Flex/AIR developers! http://www.webmaniacsconference.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301902 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Any Gotcha's in using CF UUID for db record primary key?
Whether it is a UUID created in ColdFusion or some other unique value...you KNOW what it is...every time, because you set it, not the DB. Until you need to use another database client, one other than ColdFusion, then things get messier. If you need to manually insert rows, or do a batch import, or ... This is the main reason I'm not a big fan of database clients (any clients, including ColdFusion) generating PKs. --- Ben -Original Message- From: Mark Fuqua [mailto:[EMAIL PROTECTED] Sent: Monday, March 24, 2008 10:00 AM To: CF-Talk Subject: RE: Any Gotcha's in using CF UUID for db record primary key? What if you have four concurrent users (or four hundred if your app get popular!), what happens if user two gets user three's id when she runs LAST_INSERT_ID()? That's the one of the only reasons I can see for using alternate keys. Whether it is a UUID created in Coldfusion or someother unique value...you KNOW what it is...everytime, because you set it, not the DB. Mark -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Monday, March 24, 2008 8:22 AM To: CF-Talk Subject: RE: Any Gotcha's in using CF UUID for db record primary key? What's the reason for using the db instead of CF to generate the UUID? To keep the load off CF? I was thinking I might use CF in the following manner to generate a UUID for a record: - Create UUID and variable to hold UUID via CF and cfset - User completes form with textual information and photo selection via cffile - Run insert query to create primary textual record, including inserting UUID - Run second insert query to create records in photo table for each cffile field mentioned above, inserting UUID for relational key to textual information After doing some reading in the MySQL 5 docs, it looks like I could use the LAST_INSERT_ID() function to return the auto-incrementing primary key of the main textual record of a property (Real Estate property), then run an insert query for the photo records on another table, using the LAST_INSERT_ID() as the relational key to the primary property table. - User completes form with primary record info and selects photos - Insert query runs creating primary property record - Run another query to retrieve LAST_INSERT_ID() (or just use that value as a variable, #LAST_INSERT_ID()# ???) - Run another query to insert photos into photo table using LAST_INSERT_ID as relational key Using LAST_INSERT_ID(), it seems that I could allow a single input form for two tables, property and property_photos, create a more user-friendly work flow, and avoid the messiness of UUID altogether. Thoughts? Rick -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Sunday, March 23, 2008 11:48 PM To: CF-Talk Subject: RE: Any Gotcha's in using CF UUID for db record primary key? At the same time I kind of DON'T agree with them if you're doing what we were doing: if you're using the key to link tables (so that you can combine multiple databases easily) and using them to link to non-DB information (log files, etc) then it seems like extra work to do a real auto-increment PK as well. I'll second this. Either use UUIDs or don't. In either case, I'd recommend that you use your database's ability to generate these instead of doing it from CF. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Training: Adobe/Google/Paperthin Certified Partners http://training.figleaf.com/ WebManiacs 2008: the ultimate conference for CF/Flex/AIR developers! http://www.webmaniacsconference.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301904 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Any Gotcha's in using CF UUID for db record primary key?
I'm thinking about changing from using auto-incrementing integers to CF-generated UUID's for primary keys in my mysql db's. Keep in mind that primary keys are by definition indexed, and in order to maintain an index, keys are compared to others. Now comparing to numbers takes only one machine instruction, while comparing 32 bytes string takes a loop of potentially 32 comparisons. So auto-incrementing integers are much more efficient. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301905 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Any Gotcha's in using CF UUID for db record primary key?
But can't you put a cflock / around the query that inserts the record and retrieves the last ID? On Mon, Mar 24, 2008 at 8:59 AM, Mark Fuqua [EMAIL PROTECTED] wrote: What if you have four concurrent users (or four hundred if your app get popular!), what happens if user two gets user three's id when she runs LAST_INSERT_ID()? That's the one of the only reasons I can see for using alternate keys. Whether it is a UUID created in Coldfusion or someother unique value...you KNOW what it is...everytime, because you set it, not the DB. Mark -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Monday, March 24, 2008 8:22 AM To: CF-Talk Subject: RE: Any Gotcha's in using CF UUID for db record primary key? What's the reason for using the db instead of CF to generate the UUID? To keep the load off CF? I was thinking I might use CF in the following manner to generate a UUID for a record: - Create UUID and variable to hold UUID via CF and cfset - User completes form with textual information and photo selection via cffile - Run insert query to create primary textual record, including inserting UUID - Run second insert query to create records in photo table for each cffile field mentioned above, inserting UUID for relational key to textual information After doing some reading in the MySQL 5 docs, it looks like I could use the LAST_INSERT_ID() function to return the auto-incrementing primary key of the main textual record of a property (Real Estate property), then run an insert query for the photo records on another table, using the LAST_INSERT_ID() as the relational key to the primary property table. - User completes form with primary record info and selects photos - Insert query runs creating primary property record - Run another query to retrieve LAST_INSERT_ID() (or just use that value as a variable, #LAST_INSERT_ID()# ???) - Run another query to insert photos into photo table using LAST_INSERT_ID as relational key Using LAST_INSERT_ID(), it seems that I could allow a single input form for two tables, property and property_photos, create a more user-friendly work flow, and avoid the messiness of UUID altogether. Thoughts? Rick -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Sunday, March 23, 2008 11:48 PM To: CF-Talk Subject: RE: Any Gotcha's in using CF UUID for db record primary key? At the same time I kind of DON'T agree with them if you're doing what we were doing: if you're using the key to link tables (so that you can combine multiple databases easily) and using them to link to non-DB information (log files, etc) then it seems like extra work to do a real auto-increment PK as well. I'll second this. Either use UUIDs or don't. In either case, I'd recommend that you use your database's ability to generate these instead of doing it from CF. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Training: Adobe/Google/Paperthin Certified Partners http://training.figleaf.com/ WebManiacs 2008: the ultimate conference for CF/Flex/AIR developers! http://www.webmaniacsconference.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301906 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Any Gotcha's in using CF UUID for db record primary key?
And this is the info in the MySQL 5 docs that convinced me that LAST_INSERT_ID() was a safe means of retrieving id's, even with unlimited number of users working simultaneously... == The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions. == Am I wrong in my understanding? No locks, no transactions needed? Rick -Original Message- From: Greg Morphis [mailto:[EMAIL PROTECTED] Sent: Monday, March 24, 2008 10:18 AM To: CF-Talk Subject: Re: Any Gotcha's in using CF UUID for db record primary key? But can't you put a cflock / around the query that inserts the record and retrieves the last ID? On Mon, Mar 24, 2008 at 8:59 AM, Mark Fuqua [EMAIL PROTECTED] wrote: What if you have four concurrent users (or four hundred if your app get popular!), what happens if user two gets user three's id when she runs LAST_INSERT_ID()? That's the one of the only reasons I can see for using alternate keys. Whether it is a UUID created in Coldfusion or someother unique value...you KNOW what it is...everytime, because you set it, not the DB. Mark ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301907 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Any Gotcha's in using CF UUID for db record primary key?
But can't you put a cflock / around the query that inserts the record and retrieves the last ID? If you want to serialize database transactions, that's what the CFTRANSACTION tag is for. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Training: Adobe/Google/Paperthin Certified Partners http://training.figleaf.com/ WebManiacs 2008: the ultimate conference for CF/Flex/AIR developers! http://www.webmaniacsconference.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301908 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Any Gotcha's in using CF UUID for db record primary key?
And this is the info in the MySQL 5 docs that convinced me that LAST_INSERT_ID() was a safe means of retrieving id's, even with unlimited number of users working simultaneously... == The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions. == Am I wrong in my understanding? No locks, no transactions needed? You are correct. No locks or transactions are needed. Many database servers provide analogous functionality. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Training: Adobe/Google/Paperthin Certified Partners http://training.figleaf.com/ WebManiacs 2008: the ultimate conference for CF/Flex/AIR developers! http://www.webmaniacsconference.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301911 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Any Gotcha's in using CF UUID for db record primary key?
What if you have four concurrent users (or four hundred if your app get popular!), what happens if user two gets user three's id when she runs LAST_INSERT_ID()? This is exactly why databases provide concurrency control. You should use it. That's the one of the only reasons I can see for using alternate keys. Whether it is a UUID created in Coldfusion or someother unique value...you KNOW what it is...everytime, because you set it, not the DB. That's great, as long as there are no other clients using the same database. Databases provide this functionality for a reason. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Training: Adobe/Google/Paperthin Certified Partners http://training.figleaf.com/ WebManiacs 2008: the ultimate conference for CF/Flex/AIR developers! http://www.webmaniacsconference.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301912 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Any Gotcha's in using CF UUID for db record primary key?
What's the reason for using the db instead of CF to generate the UUID? To keep the load off CF? Primarily, because that sort of thing is the database server's job, in the same way that calculating aggregates is something better left to the database server than your application. There are known issues with CF's creation of UUIDs. A database should be able to work with multiple applications. If one application contains what is essentially data access logic, this isn't possible. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Training: Adobe/Google/Paperthin Certified Partners http://training.figleaf.com/ WebManiacs 2008: the ultimate conference for CF/Flex/AIR developers! http://www.webmaniacsconference.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301916 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Any Gotcha's in using CF UUID for db record primary key?
Dave, Of course the other side of the coin is writing an application that can work with multiple databases... That can sometimes require a generic approach that resides in the application logic. Many shopping carts are like that. -Mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Monday, March 24, 2008 10:23 AM To: CF-Talk Subject: RE: Any Gotcha's in using CF UUID for db record primary key? What's the reason for using the db instead of CF to generate the UUID? To keep the load off CF? Primarily, because that sort of thing is the database server's job, in the same way that calculating aggregates is something better left to the database server than your application. There are known issues with CF's creation of UUIDs. A database should be able to work with multiple applications. If one application contains what is essentially data access logic, this isn't possible. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Training: Adobe/Google/Paperthin Certified Partners http://training.figleaf.com/ WebManiacs 2008: the ultimate conference for CF/Flex/AIR developers! http://www.webmaniacsconference.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301917 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Any Gotcha's in using CF UUID for db record primary key?
Dave, what issues do you speak of? I know that UUID creation is NOT fast, but when you say issues, it leads me to think you mean multiple bugs. On Mon, Mar 24, 2008 at 10:23 AM, Dave Watts [EMAIL PROTECTED] wrote: What's the reason for using the db instead of CF to generate the UUID? To keep the load off CF? Primarily, because that sort of thing is the database server's job, in the same way that calculating aggregates is something better left to the database server than your application. There are known issues with CF's creation of UUIDs. -- === Raymond Camden, Camden Media Email : [EMAIL PROTECTED] Blog : www.coldfusionjedi.com AOL IM : cfjedimaster Keep up to date with the community: http://www.coldfusionbloggers.org ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301919 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Any Gotcha's in using CF UUID for db record primary key?
I wondered the same thing, but a few minutes of Googling only turned up this: http://orangepips.instantspot.com/blog/2007/11/12/Coldfusion-and-UUIDs- which addresses security concerns that a Version 1 UUID can be reverse engineered to produce the MAC address of your server. Additionally, I am not necessarily a proponent of having your DB client generate primary keys if they are UUIDs or GUIDs, but I'm still trying to wrap my head around how it would break your application. Consider the following table: my_table my_table_id uniqueidentifier Primary Key my_name varchar(max) Ok, so let's say a Java application and a CF application are inserting into this table and creating their own GUIDs as they go. Even though I don't know WHY you would want to do that... I can't figure out how it would NOT work. The only exception I've been able to think of is if you were using MS SQL's sequential GUIDs. [ newSequentialID() ] Please enlighten me as I must be considering too simple of a scenario. ~Brad -Original Message- From: Raymond Camden [mailto:[EMAIL PROTECTED] Sent: Monday, March 24, 2008 10:39 AM To: CF-Talk Subject: Re: Any Gotcha's in using CF UUID for db record primary key? Dave, what issues do you speak of? I know that UUID creation is NOT fast, but when you say issues, it leads me to think you mean multiple bugs. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301920 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Any Gotcha's in using CF UUID for db record primary key?
Dave, what issues do you speak of? I know that UUID creation is NOT fast, but when you say issues, it leads me to think you mean multiple bugs. http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:51685#278231 http://www.bpurcell.org/blog/index.cfm?mode=entryentry=970 Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Training: Adobe/Google/Paperthin Certified Partners http://training.figleaf.com/ WebManiacs 2008: the ultimate conference for CF/Flex/AIR developers! http://www.webmaniacsconference.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301922 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Any Gotcha's in using CF UUID for db record primary key?
Wow, that convinces me. ;) On Mon, Mar 24, 2008 at 11:22 AM, Dave Watts [EMAIL PROTECTED] wrote: Dave, what issues do you speak of? I know that UUID creation is NOT fast, but when you say issues, it leads me to think you mean multiple bugs. http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:51685#278231 http://www.bpurcell.org/blog/index.cfm?mode=entryentry=970 -- === Raymond Camden, Camden Media Email : [EMAIL PROTECTED] Blog : www.coldfusionjedi.com AOL IM : cfjedimaster Keep up to date with the community: http://www.coldfusionbloggers.org ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301924 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Any Gotcha's in using CF UUID for db record primary key?
Rick Faircloth wrote: I'm thinking about changing from using auto-incrementing integers to CF-generated UUID's for primary keys in my mysql db's. Any drawbacks in doing that? 1. Storage size. Let's suppose you have a table for a many-to-many relation. That is 72 bytes for the 2 fields and another 72 bytes for the indexes. With integers that would have been 16 bytes. This directly translates to query performance because of the extra I/O the database has to do. You may or may not care about that since the overhead is a constant factor, but even at 36 vs. 16 bytes (string vs. binary UUID representation) that performance difference is measurable: http://jochem.vandieten.net/2008/02/06/postgresql-uuids-and-coldfusion-1/ 2. String vs. integer comparison and charsets Maybe not relevant for MySQL, but since you are shifting from from an integer to a string you should take the effects of character set conversion into consideration. Implicit character set conversion can degrade a query from an index scan to a table scan: http://jochem.vandieten.net/2008/03/22/ms-sql-server-and-the-coldfusion-string-format-setting/ 3. Generation time The speed of UUID generation in CF is limited to about 0.64 / clockresolution. So on Windows where the Java clock resolution is 10 milliseconds, that translates to 64 UUIDs per second. In some processes that is a bottleneck. (It can go faster, but then you get the problem where time starts moving too fast.) Jochem ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301927 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Any Gotcha's in using CF UUID for db record primary key?
Rick Faircloth wrote: The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions. == Am I wrong in my understanding? No locks, no transactions needed? Correct. However, you need to make sure that all your queries use the same connection. In order to do that you need a transaction. (There is some implicit behavior in Adobe's CFML engine that makes all queries in a single request use the same connection, but in for instance the CFML engine from NewAtlanta every query in a request can use a different connection.) Jochem ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301929 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Any Gotcha's in using CF UUID for db record primary key?
Rick Faircloth wrote: I'm thinking about changing from using auto-incrementing integers to CF-generated UUID's for primary keys in my mysql db's. Any drawbacks in doing that? 1. Storage size. == I'll ditto that. Our Database is a healthy 32 Gigs or so. We used GUIDs with the idea that we might want to leverage merge replication (which never really happened). Our DB is very relational, so many tables have several GUID columns which are foreign keys. Additionally many of those columns exist in indexes as well. Unfortunately, it is not worth the time to go back and change, but I calculated the other day that we spend about 13 Gigs of storage space on GUIDs in our tables and indexes. Had all those GUIDs been ints, it only would have been around 3 Gigs. So we have an extra 10 Gigs of space difference because we used GUIDs instead of ints. ~Brad ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301930 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Any Gotcha's in using CF UUID for db record primary key?
I need to become more familiar with using unique constraints, etc Yes you do! Do it now and save yourself pain later ;) Relying on a primary key to make a table row unique invariably leads to duplicate data (even though they have unique keys) which is a royal PITA. As a general rule, every table should have a primary key and at least one unique constraint, i.e. every row in any table should be unique regardless of the PK and the db can enforce that for you with unique constraints. Say goodbye to duplicate data today! :p Dominic -- Blog it up: http://fusion.dominicwatson.co.uk ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301938 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Any Gotcha's in using CF UUID for db record primary key?
What if you have four concurrent users (or four hundred if your app get popular!), what happens if user two gets user three's id when she runs LAST_INSERT_ID()? This is exactly why databases provide concurrency control. You should use it. That's the one of the only reasons I can see for using alternate keys. Whether it is a UUID created in Coldfusion or someother unique value...you KNOW what it is...everytime, because you set it, not the DB. That's great, as long as there are no other clients using the same database. Databases provide this functionality for a reason. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Training: Adobe/Google/Paperthin Certified Partners http://training.figleaf.com/ WebManiacs 2008: the ultimate conference for CF/Flex/AIR developers! http://www.webmaniacsconference.com/ You can use @@identity in a sql statement. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301942 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Any Gotcha's in using CF UUID for db record primary key?
Hi, all... I'm thinking about changing from using auto-incrementing integers to CF-generated UUID's for primary keys in my mysql db's. Any drawbacks in doing that? The main reason I'm thinking about swapping is so I can eliminate the two-step process of creating, say, a database record for a new Real Estate development community for the textual information, and then requiring a user to click a link with the record id in the URL to add photos for the community. With a UUID, I can apparently create the UUID in advance and use it for the primary key in the community and also for adding the photos, all on the same page. This is my *first* time using a UUID, so don't leave anything out! Thanks, Rick ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301865 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Any Gotcha's in using CF UUID for db record primary key?
Hi Rick, Personally, I would still use an auto-incrementing integer for the PK but have the UUID as a separate field with a unique constraint. Primary keys are not for making a single row unique but for facilitating the relational bit of relational databases (i.e. defining Foreign Key relationships). Having PKs as narrow as possible is better for performance when querying a database across relationships (i.e. using Joins, etc). Of course, as well as having the unique constraint on the GUID, your table should also define a constraint/index that logically defines what makes a row unique (i.e. a combination of account number and sort-code is a logical unique identifier for a bank account and should have a unique index or constraint - but not be a PK). HTH Dominic On 23/03/2008, Rick Faircloth [EMAIL PROTECTED] wrote: Hi, all... I'm thinking about changing from using auto-incrementing integers to CF-generated UUID's for primary keys in my mysql db's. Any drawbacks in doing that? The main reason I'm thinking about swapping is so I can eliminate the two-step process of creating, say, a database record for a new Real Estate development community for the textual information, and then requiring a user to click a link with the record id in the URL to add photos for the community. With a UUID, I can apparently create the UUID in advance and use it for the primary key in the community and also for adding the photos, all on the same page. This is my *first* time using a UUID, so don't leave anything out! Thanks, Rick ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301867 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Any Gotcha's in using CF UUID for db record primary key?
Thanks for the feedback, Dominic. I'll have to do some more research on this approach. I was thinking that the UUID would take the place of using the PK my databases, as in: a href=community.cfm?community_uuid=#url.community_uuid and I would begin to code all my links using the uuid as the relational key instead of the PK. I need to become more familiar with using unique constraints, etc. Rick -Original Message- From: Dominic Watson [mailto:[EMAIL PROTECTED] Sent: Sunday, March 23, 2008 7:10 PM To: CF-Talk Subject: Re: Any Gotcha's in using CF UUID for db record primary key? Hi Rick, Personally, I would still use an auto-incrementing integer for the PK but have the UUID as a separate field with a unique constraint. Primary keys are not for making a single row unique but for facilitating the relational bit of relational databases (i.e. defining Foreign Key relationships). Having PKs as narrow as possible is better for performance when querying a database across relationships (i.e. using Joins, etc). Of course, as well as having the unique constraint on the GUID, your table should also define a constraint/index that logically defines what makes a row unique (i.e. a combination of account number and sort-code is a logical unique identifier for a bank account and should have a unique index or constraint - but not be a PK). HTH Dominic ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301868 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Any Gotcha's in using CF UUID for db record primary key?
The main reason I'm thinking about swapping is so I can eliminate the two-step process of creating, say, a database record for a new Real Estate development community for the textual information, and then requiring a user to click a link with the record id in the URL to add photos for the community. I'd go with Dominic's idea personally. From a DBA perspective I've *hated* apps that used only GUIDs for their PK... especially when I have to either manually enter data or perform manual joins. SELECT * FROM PROPERTY JOIN PICTURES ON PROPERTY.ID = PICTURES.PROPERTYID WHERE PROPERTY.ID = 928 Is lot easier than typing out a 50-odd character string. If you're worried about embedding ID's in your URL, use forms or have a second unique that is your GUID Hatton ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301869 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Any Gotcha's in using CF UUID for db record primary key?
Keep you pk like it is...auto incrementing...and add another field with uuid and then you can reference them -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Sunday, March 23, 2008 7:38 PM To: CF-Talk Subject: RE: Any Gotcha's in using CF UUID for db record primary key? Thanks for the feedback, Dominic. I'll have to do some more research on this approach. I was thinking that the UUID would take the place of using the PK my databases, as in: a href=community.cfm?community_uuid=#url.community_uuid and I would begin to code all my links using the uuid as the relational key instead of the PK. I need to become more familiar with using unique constraints, etc. Rick -Original Message- From: Dominic Watson [mailto:[EMAIL PROTECTED] Sent: Sunday, March 23, 2008 7:10 PM To: CF-Talk Subject: Re: Any Gotcha's in using CF UUID for db record primary key? Hi Rick, Personally, I would still use an auto-incrementing integer for the PK but have the UUID as a separate field with a unique constraint. Primary keys are not for making a single row unique but for facilitating the relational bit of relational databases (i.e. defining Foreign Key relationships). Having PKs as narrow as possible is better for performance when querying a database across relationships (i.e. using Joins, etc). Of course, as well as having the unique constraint on the GUID, your table should also define a constraint/index that logically defines what makes a row unique (i.e. a combination of account number and sort-code is a logical unique identifier for a bank account and should have a unique index or constraint - but not be a PK). HTH Dominic ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301871 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Any Gotcha's in using CF UUID for db record primary key?
I have two tables with alternate keys...one is the user table that has a auto incrementing PK with the email (user name in this application) as a unique alternate key and one table of uploaded file info with an auto incrementing PK with file name (combination of file name and job name) as a unique alternate key. When records are added to either of these tables, records are added to other tables that have to relate to these new records and we can't be sure, with multiple users, of what the auto incrementing PK is (because that is set by the DB), but we do know the alternate key, bacause we actually insert that with the new record. So we can use that alternate key to be sure, 100% sure, we are grabbing the right record's PK. HTH Mark Fuqua -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Sunday, March 23, 2008 7:38 PM To: CF-Talk Subject: RE: Any Gotcha's in using CF UUID for db record primary key? Thanks for the feedback, Dominic. I'll have to do some more research on this approach. I was thinking that the UUID would take the place of using the PK my databases, as in: a href=community.cfm?community_uuid=#url.community_uuid and I would begin to code all my links using the uuid as the relational key instead of the PK. I need to become more familiar with using unique constraints, etc. Rick -Original Message- From: Dominic Watson [mailto:[EMAIL PROTECTED] Sent: Sunday, March 23, 2008 7:10 PM To: CF-Talk Subject: Re: Any Gotcha's in using CF UUID for db record primary key? Hi Rick, Personally, I would still use an auto-incrementing integer for the PK but have the UUID as a separate field with a unique constraint. Primary keys are not for making a single row unique but for facilitating the relational bit of relational databases (i.e. defining Foreign Key relationships). Having PKs as narrow as possible is better for performance when querying a database across relationships (i.e. using Joins, etc). Of course, as well as having the unique constraint on the GUID, your table should also define a constraint/index that logically defines what makes a row unique (i.e. a combination of account number and sort-code is a logical unique identifier for a bank account and should have a unique index or constraint - but not be a PK). HTH Dominic ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301872 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Any Gotcha's in using CF UUID for db record primary key?
For what it's worth I kind of agree with the others about the performance benefits... but at the same time the busiest site I've ever worked on (and my personal site, since it's built using the same engine) uses only UUIDs for PKs and never had an issue because of it. We used them, as I assume you are, to ensure that cross-domain information could be easy combined (something we actually didn't end up doing) and used in the application (the same PKs were used as StructKeys in session management) but we took millions of hits on moderate hardware using CF 6, Windows and SQL Server and it never winced. At the same time I kind of DON'T agree with them if you're doing what we were doing: if you're using the key to link tables (so that you can combine multiple databases easily) and using them to link to non-DB information (log files, etc) then it seems like extra work to do a real auto-increment PK as well. Jim Davis ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301873 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Any Gotcha's in using CF UUID for db record primary key?
I'd agree with Dominic. My company used to use GUIDs for OK and it's a pain in the arse. When you start getting hundreds of millions of records in the database, that varchar field for the GUID starts to take up a lot of space. Using it as the key for joins is trouble waiting to happen because now, you've got the GUID in not only one table, but MULTIPLE tables. Stick with the integer field for PK. -Original Message- From: Dominic Watson [mailto:[EMAIL PROTECTED] Sent: Sunday, March 23, 2008 6:10 PM To: CF-Talk Subject: Re: Any Gotcha's in using CF UUID for db record primary key? Hi Rick, Personally, I would still use an auto-incrementing integer for the PK but have the UUID as a separate field with a unique constraint. Primary keys are not for making a single row unique but for facilitating the relational bit of relational databases (i.e. defining Foreign Key relationships). Having PKs as narrow as possible is better for performance when querying a database across relationships (i.e. using Joins, etc). Of course, as well as having the unique constraint on the GUID, your table should also define a constraint/index that logically defines what makes a row unique (i.e. a combination of account number and sort-code is a logical unique identifier for a bank account and should have a unique index or constraint - but not be a PK). HTH Dominic On 23/03/2008, Rick Faircloth [EMAIL PROTECTED] wrote: Hi, all... I'm thinking about changing from using auto-incrementing integers to CF-generated UUID's for primary keys in my mysql db's. Any drawbacks in doing that? The main reason I'm thinking about swapping is so I can eliminate the two-step process of creating, say, a database record for a new Real Estate development community for the textual information, and then requiring a user to click a link with the record id in the URL to add photos for the community. With a UUID, I can apparently create the UUID in advance and use it for the primary key in the community and also for adding the photos, all on the same page. This is my *first* time using a UUID, so don't leave anything out! Thanks, Rick ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301874 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Any Gotcha's in using CF UUID for db record primary key?
Sounds like there are definite pros and cons to UUID usage. And, too, this seems to be a case of need driving functionality. With high traffic sites, I can see the need for various indexes, etc. My db's will all be relatively small and won't have issues with high traffic. I've never even had to use indexes to speed up db data flow. Initial impression is that working with UUID's, etc., may prove to be more effort than it's worth just to save users a step in adding unlimited photos to, say, a Real Estate property. Instead of all on one page, using the UUID as a relational key, I just have the user create the property record, then on the success confirmation page, offer a link to add photos to the property. When updating an existing property, there's no issue since the user has had to click a link with an id embedded in the URL anyway. Rick ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301882 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Any Gotcha's in using CF UUID for db record primary key?
At the same time I kind of DON'T agree with them if you're doing what we were doing: if you're using the key to link tables (so that you can combine multiple databases easily) and using them to link to non-DB information (log files, etc) then it seems like extra work to do a real auto-increment PK as well. I'll second this. Either use UUIDs or don't. In either case, I'd recommend that you use your database's ability to generate these instead of doing it from CF. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Training: Adobe/Google/Paperthin Certified Partners http://training.figleaf.com/ WebManiacs 2008: the ultimate conference for CF/Flex/AIR developers! http://www.webmaniacsconference.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301884 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
