Re: Longing IDs or UUIDs as primary key?
Thanks to all of you for the valuable insights. Seems I’ve got some work to do ;-) Regards, Rudy Mortier Two Way Communications bvba > On 24 Oct 2018, at 19:17, Chuck Miller via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > No it is just painful let’s assume for some reason record in a many table > where assigns to wrong main table which would you rather type in to fix > 145764 or a 32 character UUID > In my opinion the journal aspect should have been hidden by 4D and the UUID > field should have been there but hidden > > > Regards > > Chuck > > Sent from my iPhone > >> On Oct 24, 2018, at 11:59 AM, Keith Culotta via 4D_Tech >> <4d_tech@lists.4d.com> wrote: >> >> RE: never use them to link between tables >> >> Is using them to link between tables (establish 4D Relations, correct?) a >> hazardous practice? >> >> Thanks, >> Keith - CDI >> >>> On Oct 24, 2018, at 10:49 AM, Charles Miller via 4D_Tech >>> <4d_tech@lists.4d.com> wrote: >>> >>> Rudy >>> >>> For me this always choose UUID for primary key and never use them to link >>> between tables. The overhead from space is not so great Andy I never want >>> to type in uuid to find related records etc >>> >>> Regards >>> >>> Chuck >>> >>> On Wed, Oct 24, 2018 at 10:52 AM Two Way Communications via 4D_Tech < >>> 4d_tech@lists.4d.com> wrote: >>> I have an application with a big database file ( + 60 GB), with 128 tables. (4D v17) All id fields and foreign keys are of type longint. Now, for replication and sharing purposes, I would like to change the type to UID. The process seems quite cumbersome: to start, I need to remove the ‘primary key’ flag from all the ID fields, then I need to add UID fields to every table, change the foreign keys as well, and use apply formula to make sure the relations are intact. I am a bit worried that this will have a major impact on the size of the data file. Furthermore, I need to automate the whole process so the upgrade works flawlessly at the customers site. Has anyone ever done this? Any tips? Regards, Rudy Mortier Two Way Communications bvba >> >> ** >> 4D Internet Users Group (4D iNUG) >> Archive: http://lists.4d.com/archives.html >> Options: https://lists.4d.com/mailman/options/4d_tech >> Unsub: mailto:4d_tech-unsubscr...@lists.4d.com >> ** > > ** > 4D Internet Users Group (4D iNUG) > Archive: http://lists.4d.com/archives.html > Options: https://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ** ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Longing IDs or UUIDs as primary key?
It’s a setting in the properties of the listbox column Rudy Mortier Two Way Communications bvba > On 25 Oct 2018, at 04:47, Chip Scheide via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > how do you do this? > > >> In 4D list boxes, a column can be set up to show an ellipsis in the >> middle of a string (instead of the end) when a column is too small. >> This is perfect for UUID columns. Just make them wide enough to only >> show the first and last 3 characters. Just as easy as a longint, >> really. > > Hell is other people > Jean-Paul Sartre > ** > 4D Internet Users Group (4D iNUG) > Archive: http://lists.4d.com/archives.html > Options: https://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ** ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Longing IDs or UUIDs as primary key?
how do you do this? > In 4D list boxes, a column can be set up to show an ellipsis in the > middle of a string (instead of the end) when a column is too small. > This is perfect for UUID columns. Just make them wide enough to only > show the first and last 3 characters. Just as easy as a longint, > really. Hell is other people Jean-Paul Sartre ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Longing IDs or UUIDs as primary key?
> On 24 Oct 2018, at 16:52, Two Way Communications via 4D_Tech > <4d_tech@lists.4d.com> wrote: > > The process seems quite cumbersome: to start, I need to remove the ‘primary > key’ flag from all the ID fields Rudy - Just to add to the many useful contributions made so far, I found a way to do this in a few seconds which makes the process almost painless. (Courtesy of the late but much celebrated Ortwin Zilgen). The approach is: 1: run the code below to add keys to all tables. (This will keep 4D backup happy and let you do your replication) 2: carry on as usual using your IDs as “logical keys” or “business keys” as I call them, and remain blissfully ignorant of the UUID layer Ortwin’s code that he sent me is posted below. Best Regards Peter ** Generate Primary Keys for All Tables ** ` `.By Ortwin Zilgen *** C_LONGINT($i;$P_SIZE) C_TEXT($tableName_t) $fieldName:="pkUUID" $P_SIZE:=Get last table number For ($i;1;$P_SIZE) If (Is table number valid($i)) $tableName_t:=Table name($i) $statement_t:="ALTER TABLE ["+$tableName_t+"] ADD "+$fieldName+" UUID AUTO_GENERATE PRIMARY KEY;" Begin SQL EXECUTE IMMEDIATE:$statement_t; End SQL End if End for ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Longing IDs or UUIDs as primary key?
I've heard lots of comments over the years talking about how hard it is to work with UUIDs. I’d like to offer a different perspective. I switched to UUIDs from longints for primary keys 7 or 8 years ago. It took some careful programming to migrate the datafiles in place, but it was _so_ worth it. In all that time I’ve never once had to type in a UUID by hand. Instead, I just added the odd tool here and there that lets me copy a UUID to the clipboard. Then paste it for searching. As for looking for a UUID in a list, it’s pretty easy to look for a UUID by scanning for the first 2-3 characters and then checking that the last 2-3 characters match. In 4D list boxes, a column can be set up to show an ellipsis in the middle of a string (instead of the end) when a column is too small. This is perfect for UUID columns. Just make them wide enough to only show the first and last 3 characters. Just as easy as a longint, really. -- Cannon.Smith Synergy Farm Solutions Inc. Hill Spring, AB Canada 403-626-3236 > On Oct 24, 2018, at 11:17 AM, Chuck Miller via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > No it is just painful let’s assume for some reason record in a many table > where assigns to wrong main table which would you rather type in to fix > 145764 or a 32 character UUID ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Longing IDs or UUIDs as primary key?
Sorry, forgot the UUID properties would still have to be set for the fields after all that. Again, SQL? Name it the "No way, YOU click the 'OK to Update' button" method. > On Oct 24, 2018, at 12:01 PM, Keith Culotta via 4D_Tech > <4d_tech@lists.4d.com> wrote: > > Here's a possible approach to automating, but it depends last question. > If you change a longint to an alpha field, the new new alpha field retains > the longint value. > You could send the modified Structure with the longint fields changed to > alpha. > When the Structure sees that a Datafile is not converted, for each related > field it > remembers the longint value in the One Table's field > creates and saves a UUID for the One Table's field > queries the related using the old longint value > changes the Many table's link field to the One Table's UUID. > set the relations and Primary Keys* > > Eventually marks the datafile as converted. > > *Can SQL be used to set a Primary Key for a table that has none? > > Keith - CDI > >> On Oct 24, 2018, at 9:52 AM, Two Way Communications via 4D_Tech >> <4d_tech@lists.4d.com> wrote: >> >> I have an application with a big database file ( + 60 GB), with 128 tables. >> (4D v17) >> >> All id fields and foreign keys are of type longint. >> >> Now, for replication and sharing purposes, I would like to change the type >> to UID. >> >> The process seems quite cumbersome: to start, I need to remove the ‘primary >> key’ flag from all the ID fields, then I need to add UID fields to every >> table, >> change the foreign keys as well, and use apply formula to make sure the >> relations are intact. I am a bit worried that this will have a major impact >> on the size of the data file. >> >> Furthermore, I need to automate the whole process so the upgrade works >> flawlessly at the customers site. >> >> Has anyone ever done this? >> Any tips? >> >> Regards, >> >> Rudy Mortier >> Two Way Communications bvba > ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Longing IDs or UUIDs as primary key?
No it is just painful let’s assume for some reason record in a many table where assigns to wrong main table which would you rather type in to fix 145764 or a 32 character UUID In my opinion the journal aspect should have been hidden by 4D and the UUID field should have been there but hidden Regards Chuck Sent from my iPhone > On Oct 24, 2018, at 11:59 AM, Keith Culotta via 4D_Tech > <4d_tech@lists.4d.com> wrote: > > RE: never use them to link between tables > > Is using them to link between tables (establish 4D Relations, correct?) a > hazardous practice? > > Thanks, > Keith - CDI > >> On Oct 24, 2018, at 10:49 AM, Charles Miller via 4D_Tech >> <4d_tech@lists.4d.com> wrote: >> >> Rudy >> >> For me this always choose UUID for primary key and never use them to link >> between tables. The overhead from space is not so great Andy I never want >> to type in uuid to find related records etc >> >> Regards >> >> Chuck >> >> On Wed, Oct 24, 2018 at 10:52 AM Two Way Communications via 4D_Tech < >> 4d_tech@lists.4d.com> wrote: >> >>> I have an application with a big database file ( + 60 GB), with 128 >>> tables. (4D v17) >>> >>> All id fields and foreign keys are of type longint. >>> >>> Now, for replication and sharing purposes, I would like to change the type >>> to UID. >>> >>> The process seems quite cumbersome: to start, I need to remove the >>> ‘primary key’ flag from all the ID fields, then I need to add UID fields to >>> every table, >>> change the foreign keys as well, and use apply formula to make sure the >>> relations are intact. I am a bit worried that this will have a major impact >>> on the size of the data file. >>> >>> Furthermore, I need to automate the whole process so the upgrade works >>> flawlessly at the customers site. >>> >>> Has anyone ever done this? >>> Any tips? >>> >>> Regards, >>> >>> Rudy Mortier >>> Two Way Communications bvba >>> >>> > > ** > 4D Internet Users Group (4D iNUG) > Archive: http://lists.4d.com/archives.html > Options: https://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ** ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Longing IDs or UUIDs as primary key?
Kirk, I am not suggesting using SS numbers, or anything the user can change/touch as a linking value. a longint (or other sequential value [text] - like a license plate) generated for each new record for each table which is used explicitly for identifying the record and possibly linking relational data, as needed. On Wed, 24 Oct 2018 09:59:07 -0700, Kirk Brooks via 4D_Tech wrote: > Another way to look at this is in terms of separating the logical structure > from the data. Using UUIDs compels you to think about the logical structure > separately from the contents. That's a good thing. I agree it can be useful > to have a unique serial number on some tables. But that doesn't mean it's a > good idea to use it as a key. Social Security numbers are the perfect > example of this. I don't think the 2 fields vs 1 field is really an issue. I do see what you are getting at though. > Another instance is being able to identify records any place in the > database by a single value. For instance, I have a table for recording > notes users attach to records. The idea it to be able to attach a note to > anything. If I'm using longints it takes two fields to identify the record, > table # & id #. With a UUID I only need one. Other points well taken. --- Gas is for washing parts Alcohol is for drinkin' Nitromethane is for racing ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
RE: Longing IDs or UUIDs as primary key?
I and Admin(s) may need to see/work with the relational key to attempt to determine why/if something is F-ed. off the top of my head scenario: invoice does not show expected line items. -(for me) first step examine raw data to see if the 'missing' line item(s) exist. -- to do this you need to at least search the line item table for the Invoice ID --- invoice ID is longint (type 1234567) poof line items --- invoice ID is UUID (type UUID 17 times because : typos, TL;DR, transposition) finally line items (maybe) try search again (at least look at search dialog) to be sure UUID was typed correctly, and the location of line items was not a fluke where a typed UUID was in fact another UUID in use. - proceed with determining if the line items found are what was expected. On Wed, 24 Oct 2018 16:53:16 +, Dennis, Neil wrote: > >> - Do I **really** want to type a UUID to try to follow/check on >> related records when something goes pear-shaped? >> - Do I want my admin(s) to have to type a UUID to try to chase >> related records? >> - Do I want to have to work with UUIDs, other then knowing that they >> exist and are inplace as requested/required? > > Do I ever want to have a user ever know anything about or see a > primary or foreign key anyway > > Neil > > > > > > > -- > > > > Privacy Disclaimer: This message contains confidential information > and is intended only for the named addressee. If you are not the > named addressee you should not disseminate, distribute or copy this > email. Please delete this email from your system and notify the > sender immediately by replying to this email. If you are not the > intended recipient you are notified that disclosing, copying, > distributing or taking any action in reliance on the contents of this > information is strictly prohibited. > > The Alternative Investments division of UMB Fund Services provides a > full range of services to hedge funds, funds of funds and private > equity funds. Any tax advice in this communication is not intended > to be used, and cannot be used, by a client or any other person or > entity for the purpose of (a) avoiding penalties that may be imposed > on any taxpayer or (b) promoting, marketing, or recommending to > another party any matter addressed herein. --- Gas is for washing parts Alcohol is for drinkin' Nitromethane is for racing ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Longing IDs or UUIDs as primary key?
There are other options which may be more attractive if you are willing to put more work in your synchronization code. I did something like this years ago before UUIDs existed. You need to be able to identify shared records (which can be a UUID field without it being the primary key) and then update the foreign keys with the right integer values as they are transferred into the target database. John DeSoi, Ph.D. > On Oct 24, 2018, at 11:31 AM, Two Way Communications via 4D_Tech > <4d_tech@lists.4d.com> wrote: > > To be clearer on the purpose: > > There are many customers who use my application. > > What I want to achieve is that they can ’share’ data. In order to do that, I > really do need a UUID, because I intend to exchange the records (and related > records) between their individual databases. > Obviously this will never work with LONGINT ids and foreign keys … > > Another example is replication. My users, most of the time, are working > client-server. But often, they need a part of the datafile as a local > database on their laptop, so I need to replicate at least a part of > the data into a stand-alone database. Then, if they add records to the local > database, I want to replicate that new information back to the server > database. > > The only way this would be possible is to change all the LONGINT ids to UUID, > and also the foreign keys …. ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Longing IDs or UUIDs as primary key?
why not: - Add UUID as needed. - populate Using Send/receive record during synchronization repeat for each record to import - read the record to import - verify the UUID of the imported record - if it exists -- do whatever record merging you need - if it does not exist, assign a NEW longint ID (relational field(s)), this gives each record unique relational ID(s) in each database in which that data resides. -- assign these new relational ID(s) to the relational field(s) of other imported records no need to redraw, no need to convert, no need to change existing working code. On Wed, 24 Oct 2018 09:37:08 -0700, Kirk Brooks via 4D_Tech wrote: > > a) go through every table affected and add the UUID field > b) at the same time remove the old relations & draw in the new ones > c) write a conversion method(s) to loop through all affected records >i) lookup the linked records based on the old longint field > ii) set the new UUID foreign key > d) update all the queries that relied on the old key fields --- Gas is for washing parts Alcohol is for drinkin' Nitromethane is for racing ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Longing IDs or UUIDs as primary key?
Here's a possible approach to automating, but it depends last question. If you change a longint to an alpha field, the new new alpha field retains the longint value. You could send the modified Structure with the longint fields changed to alpha. When the Structure sees that a Datafile is not converted, for each related field it remembers the longint value in the One Table's field creates and saves a UUID for the One Table's field queries the related using the old longint value changes the Many table's link field to the One Table's UUID. set the relations and Primary Keys* Eventually marks the datafile as converted. *Can SQL be used to set a Primary Key for a table that has none? Keith - CDI > On Oct 24, 2018, at 9:52 AM, Two Way Communications via 4D_Tech > <4d_tech@lists.4d.com> wrote: > > I have an application with a big database file ( + 60 GB), with 128 tables. > (4D v17) > > All id fields and foreign keys are of type longint. > > Now, for replication and sharing purposes, I would like to change the type to > UID. > > The process seems quite cumbersome: to start, I need to remove the ‘primary > key’ flag from all the ID fields, then I need to add UID fields to every > table, > change the foreign keys as well, and use apply formula to make sure the > relations are intact. I am a bit worried that this will have a major impact > on the size of the data file. > > Furthermore, I need to automate the whole process so the upgrade works > flawlessly at the customers site. > > Has anyone ever done this? > Any tips? > > Regards, > > Rudy Mortier > Two Way Communications bvba ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Longing IDs or UUIDs as primary key?
Hi Chip, Another way to look at this is in terms of separating the logical structure from the data. Using UUIDs compels you to think about the logical structure separately from the contents. That's a good thing. I agree it can be useful to have a unique serial number on some tables. But that doesn't mean it's a good idea to use it as a key. Social Security numbers are the perfect example of this. Another instance is being able to identify records any place in the database by a single value. For instance, I have a table for recording notes users attach to records. The idea it to be able to attach a note to anything. If I'm using longints it takes two fields to identify the record, table # & id #. With a UUID I only need one. Another instance in my history was the necessity to merge data from two tables into one as part of a refactoring. This was tricky because there was duplication of IDs in the series and lots of linked records as well. If they'd been UUIDs it would have been a non-issue. Same thing for syncing data from distributed databases. Another instance that comes to mind is troubleshooting new datafiles. Client has a production database that allows users to create multiple datafiles. (True story.) There's some issue that's only shows up creating new datafiles. Using long ints all the tables are starting their sequences at 1. Have a complex linking situation where a linking table with 4 fields pointing at different related tables all have the same values in all fields. I think a lot of 4D issues arise from how easy it is to conflate the data with structure. UUIDs are way to separate them and I think leads to more robust structure designs. On Wed, Oct 24, 2018 at 9:17 AM Chip Scheide via 4D_Tech < 4d_tech@lists.4d.com> wrote: > it is more of a situation of: > - Do I **really** want to type a UUID to try to follow/check on > related records when something goes pear-shaped? > - Do I want my admin(s) to have to type a UUID to try to chase related > records? > - Do I want to have to work with UUIDs, other then knowing that they > exist and are inplace as requested/required? > > > On Wed, 24 Oct 2018 10:59:48 -0500, Keith Culotta via 4D_Tech wrote: > > RE: never use them to link between tables > > > > Is using them to link between tables (establish 4D Relations, > > correct?) a hazardous practice? > > > > Thanks, > > Keith - CDI > > > >> On Oct 24, 2018, at 10:49 AM, Charles Miller via 4D_Tech > >> <4d_tech@lists.4d.com> wrote: > >> > >> Rudy > >> > >> For me this always choose UUID for primary key and never use them to > link > >> between tables. The overhead from space is not so great Andy I never > want > >> to type in uuid to find related records etc > >> > >> Regards > >> > >> Chuck > >> > >> On Wed, Oct 24, 2018 at 10:52 AM Two Way Communications via 4D_Tech < > >> 4d_tech@lists.4d.com> wrote: > >> > >>> I have an application with a big database file ( + 60 GB), with 128 > >>> tables. (4D v17) > >>> > >>> All id fields and foreign keys are of type longint. > >>> > >>> Now, for replication and sharing purposes, I would like to change the > type > >>> to UID. > >>> > >>> The process seems quite cumbersome: to start, I need to remove the > >>> ‘primary key’ flag from all the ID fields, then I need to add UID > >>> fields to > >>> every table, > >>> change the foreign keys as well, and use apply formula to make sure the > >>> relations are intact. I am a bit worried that this will have a > >>> major impact > >>> on the size of the data file. > >>> > >>> Furthermore, I need to automate the whole process so the upgrade works > >>> flawlessly at the customers site. > >>> > >>> Has anyone ever done this? > >>> Any tips? > >>> > >>> Regards, > >>> > >>> Rudy Mortier > >>> Two Way Communications bvba > >>> > >>> > > > > ** > > 4D Internet Users Group (4D iNUG) > > Archive: http://lists.4d.com/archives.html > > Options: https://lists.4d.com/mailman/options/4d_tech > > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > > ** > --- > Gas is for washing parts > Alcohol is for drinkin' > Nitromethane is for racing > ** > 4D Internet Users Group (4D iNUG) > Archive: http://lists.4d.com/archives.html > Options: https://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ** -- Kirk Brooks San Francisco, CA === *We go vote - they go home* ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Longing IDs or UUIDs as primary key?
It made no sense to me to abandon my longint primary keys used for relational integrity and uniqueness in a long standing system when 4D would automatically establish and maintain a new UUID primary key for the purposes of replication and sharing right along side my working longint field. John Baughman Kailua, Hawaii john...@hawaii.rr.com On Oct 24, 2018, at 5:49 AM, Charles Miller via 4D_Tech <4d_tech@lists.4d.com> wrote: >> Now, for replication and sharing purposes, I would like to change the type >> to UID. ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
RE: Longing IDs or UUIDs as primary key?
>- Do I **really** want to type a UUID to try to follow/check on related >records when something goes pear-shaped? >- Do I want my admin(s) to have to type a UUID to try to chase related records? >- Do I want to have to work with UUIDs, other then knowing that they exist and >are inplace as requested/required? Do I ever want to have a user ever know anything about or see a primary or foreign key anyway Neil -- Privacy Disclaimer: This message contains confidential information and is intended only for the named addressee. If you are not the named addressee you should not disseminate, distribute or copy this email. Please delete this email from your system and notify the sender immediately by replying to this email. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited. The Alternative Investments division of UMB Fund Services provides a full range of services to hedge funds, funds of funds and private equity funds. Any tax advice in this communication is not intended to be used, and cannot be used, by a client or any other person or entity for the purpose of (a) avoiding penalties that may be imposed on any taxpayer or (b) promoting, marketing, or recommending to another party any matter addressed herein. ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
RE: Longing IDs or UUIDs as primary key?
I use UUID for links between tables, I wouldn't ever go back to longint keys. They are faster than sequential keys in a lot of respects (long discussion)... I love them. Neil -- Privacy Disclaimer: This message contains confidential information and is intended only for the named addressee. If you are not the named addressee you should not disseminate, distribute or copy this email. Please delete this email from your system and notify the sender immediately by replying to this email. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited. The Alternative Investments division of UMB Fund Services provides a full range of services to hedge funds, funds of funds and private equity funds. Any tax advice in this communication is not intended to be used, and cannot be used, by a client or any other person or entity for the purpose of (a) avoiding penalties that may be imposed on any taxpayer or (b) promoting, marketing, or recommending to another party any matter addressed herein. ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Longing IDs or UUIDs as primary key?
Rudy, I did something similar once, partly to see how hard it would be. I would not race into doing it again unless there was some really compelling reason. Assuming there is that compelling reason the process worked something like this: a) go through every table affected and add the UUID field b) at the same time remove the old relations & draw in the new ones c) write a conversion method(s) to loop through all affected records i) lookup the linked records based on the old longint field ii) set the new UUID foreign key d) update all the queries that relied on the old key fields Personally I'd retain the old longint fields and keep incrementing them though in most cases I'd drop the indexes. The reason being it's useful to have a user-readable serial number that isn't an actual 'key field' but is unique enough to be useful doing ad hoc queries and debugging. It will also prevent any direct queries on it from failing though they will get slow (and identifiable) if you dropped the index. Queries that relied on the relations will fail and also be easy to spot. On Wed, Oct 24, 2018 at 7:52 AM Two Way Communications via 4D_Tech < 4d_tech@lists.4d.com> wrote: > I have an application with a big database file ( + 60 GB), with 128 > tables. (4D v17) > > All id fields and foreign keys are of type longint. > > Now, for replication and sharing purposes, I would like to change the type > to UID. > -- Kirk Brooks San Francisco, CA === *We go vote - they go home* ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Longing IDs or UUIDs as primary key?
To be clearer on the purpose: There are many customers who use my application. What I want to achieve is that they can ’share’ data. In order to do that, I really do need a UUID, because I intend to exchange the records (and related records) between their individual databases. Obviously this will never work with LONGINT ids and foreign keys … Another example is replication. My users, most of the time, are working client-server. But often, they need a part of the datafile as a local database on their laptop, so I need to replicate at least a part of the data into a stand-alone database. Then, if they add records to the local database, I want to replicate that new information back to the server database. The only way this would be possible is to change all the LONGINT ids to UUID, and also the foreign keys …. Regards, Rudy Mortier Two Way Communications bvba > On 24 Oct 2018, at 18:16, Chip Scheide via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > it is more of a situation of: > - Do I **really** want to type a UUID to try to follow/check on > related records when something goes pear-shaped? > - Do I want my admin(s) to have to type a UUID to try to chase related > records? > - Do I want to have to work with UUIDs, other then knowing that they > exist and are inplace as requested/required? > > > On Wed, 24 Oct 2018 10:59:48 -0500, Keith Culotta via 4D_Tech wrote: >> RE: never use them to link between tables >> >> Is using them to link between tables (establish 4D Relations, >> correct?) a hazardous practice? >> >> Thanks, >> Keith - CDI >> >>> On Oct 24, 2018, at 10:49 AM, Charles Miller via 4D_Tech >>> <4d_tech@lists.4d.com> wrote: >>> >>> Rudy >>> >>> For me this always choose UUID for primary key and never use them to link >>> between tables. The overhead from space is not so great Andy I never want >>> to type in uuid to find related records etc >>> >>> Regards >>> >>> Chuck >>> >>> On Wed, Oct 24, 2018 at 10:52 AM Two Way Communications via 4D_Tech < >>> 4d_tech@lists.4d.com> wrote: >>> I have an application with a big database file ( + 60 GB), with 128 tables. (4D v17) All id fields and foreign keys are of type longint. Now, for replication and sharing purposes, I would like to change the type to UID. The process seems quite cumbersome: to start, I need to remove the ‘primary key’ flag from all the ID fields, then I need to add UID fields to every table, change the foreign keys as well, and use apply formula to make sure the relations are intact. I am a bit worried that this will have a major impact on the size of the data file. Furthermore, I need to automate the whole process so the upgrade works flawlessly at the customers site. Has anyone ever done this? Any tips? Regards, Rudy Mortier Two Way Communications bvba >> >> ** >> 4D Internet Users Group (4D iNUG) >> Archive: http://lists.4d.com/archives.html >> Options: https://lists.4d.com/mailman/options/4d_tech >> Unsub: mailto:4d_tech-unsubscr...@lists.4d.com >> ** > --- > Gas is for washing parts > Alcohol is for drinkin' > Nitromethane is for racing > ** > 4D Internet Users Group (4D iNUG) > Archive: http://lists.4d.com/archives.html > Options: https://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ** ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Longing IDs or UUIDs as primary key?
it is more of a situation of: - Do I **really** want to type a UUID to try to follow/check on related records when something goes pear-shaped? - Do I want my admin(s) to have to type a UUID to try to chase related records? - Do I want to have to work with UUIDs, other then knowing that they exist and are inplace as requested/required? On Wed, 24 Oct 2018 10:59:48 -0500, Keith Culotta via 4D_Tech wrote: > RE: never use them to link between tables > > Is using them to link between tables (establish 4D Relations, > correct?) a hazardous practice? > > Thanks, > Keith - CDI > >> On Oct 24, 2018, at 10:49 AM, Charles Miller via 4D_Tech >> <4d_tech@lists.4d.com> wrote: >> >> Rudy >> >> For me this always choose UUID for primary key and never use them to link >> between tables. The overhead from space is not so great Andy I never want >> to type in uuid to find related records etc >> >> Regards >> >> Chuck >> >> On Wed, Oct 24, 2018 at 10:52 AM Two Way Communications via 4D_Tech < >> 4d_tech@lists.4d.com> wrote: >> >>> I have an application with a big database file ( + 60 GB), with 128 >>> tables. (4D v17) >>> >>> All id fields and foreign keys are of type longint. >>> >>> Now, for replication and sharing purposes, I would like to change the type >>> to UID. >>> >>> The process seems quite cumbersome: to start, I need to remove the >>> ‘primary key’ flag from all the ID fields, then I need to add UID >>> fields to >>> every table, >>> change the foreign keys as well, and use apply formula to make sure the >>> relations are intact. I am a bit worried that this will have a >>> major impact >>> on the size of the data file. >>> >>> Furthermore, I need to automate the whole process so the upgrade works >>> flawlessly at the customers site. >>> >>> Has anyone ever done this? >>> Any tips? >>> >>> Regards, >>> >>> Rudy Mortier >>> Two Way Communications bvba >>> >>> > > ** > 4D Internet Users Group (4D iNUG) > Archive: http://lists.4d.com/archives.html > Options: https://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ** --- Gas is for washing parts Alcohol is for drinkin' Nitromethane is for racing ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Longing IDs or UUIDs as primary key?
[some emoji that expresses a slight sense of relief, but not necessarily a feeling of being surprised] > On Oct 24, 2018, at 11:01 AM, Jeffrey Kain via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > No it isn't. > >> On Oct 24, 2018, at 11:59 AM, Keith Culotta via 4D_Tech >> <4d_tech@lists.4d.com> wrote: >> >> Is using them to link between tables (establish 4D Relations, correct?) a >> hazardous practice? > ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Longing IDs or UUIDs as primary key?
No it isn't. > On Oct 24, 2018, at 11:59 AM, Keith Culotta via 4D_Tech > <4d_tech@lists.4d.com> wrote: > > Is using them to link between tables (establish 4D Relations, correct?) a > hazardous practice? ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Longing IDs or UUIDs as primary key?
RE: never use them to link between tables Is using them to link between tables (establish 4D Relations, correct?) a hazardous practice? Thanks, Keith - CDI > On Oct 24, 2018, at 10:49 AM, Charles Miller via 4D_Tech > <4d_tech@lists.4d.com> wrote: > > Rudy > > For me this always choose UUID for primary key and never use them to link > between tables. The overhead from space is not so great Andy I never want > to type in uuid to find related records etc > > Regards > > Chuck > > On Wed, Oct 24, 2018 at 10:52 AM Two Way Communications via 4D_Tech < > 4d_tech@lists.4d.com> wrote: > >> I have an application with a big database file ( + 60 GB), with 128 >> tables. (4D v17) >> >> All id fields and foreign keys are of type longint. >> >> Now, for replication and sharing purposes, I would like to change the type >> to UID. >> >> The process seems quite cumbersome: to start, I need to remove the >> ‘primary key’ flag from all the ID fields, then I need to add UID fields to >> every table, >> change the foreign keys as well, and use apply formula to make sure the >> relations are intact. I am a bit worried that this will have a major impact >> on the size of the data file. >> >> Furthermore, I need to automate the whole process so the upgrade works >> flawlessly at the customers site. >> >> Has anyone ever done this? >> Any tips? >> >> Regards, >> >> Rudy Mortier >> Two Way Communications bvba >> >> ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Longing IDs or UUIDs as primary key?
Rudy For me this always choose UUID for primary key and never use them to link between tables. The overhead from space is not so great Andy I never want to type in uuid to find related records etc Regards Chuck On Wed, Oct 24, 2018 at 10:52 AM Two Way Communications via 4D_Tech < 4d_tech@lists.4d.com> wrote: > I have an application with a big database file ( + 60 GB), with 128 > tables. (4D v17) > > All id fields and foreign keys are of type longint. > > Now, for replication and sharing purposes, I would like to change the type > to UID. > > The process seems quite cumbersome: to start, I need to remove the > ‘primary key’ flag from all the ID fields, then I need to add UID fields to > every table, > change the foreign keys as well, and use apply formula to make sure the > relations are intact. I am a bit worried that this will have a major impact > on the size of the data file. > > Furthermore, I need to automate the whole process so the upgrade works > flawlessly at the customers site. > > Has anyone ever done this? > Any tips? > > Regards, > > Rudy Mortier > Two Way Communications bvba > > ** > 4D Internet Users Group (4D iNUG) > Archive: http://lists.4d.com/archives.html > Options: https://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ** -- - Chuck Miller Voice: (617) 739-0306 Fax: (617) 232-1064 Informed Solutions, Inc. Brookline, MA 02446 USA Registered 4D Developer Providers of 4D, Sybase & SQL Server connectivity http://www.informed-solutions.com - This message and any attached documents contain information which may be confidential, subject to privilege or exempt from disclosure under applicable law. These materials are intended only for the use of the intended recipient. If you are not the intended recipient of this transmission, you are hereby notified that any distribution, disclosure, printing, copying, storage, modification or the taking of any action in reliance upon this transmission is strictly prohibited. Delivery of this message to any person other than the intended recipient shall not compromise or waive such confidentiality, privilege or exemption from disclosure as to this communication. ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Longing IDs or UUIDs as primary key?
I have an application with a big database file ( + 60 GB), with 128 tables. (4D v17) All id fields and foreign keys are of type longint. Now, for replication and sharing purposes, I would like to change the type to UID. The process seems quite cumbersome: to start, I need to remove the ‘primary key’ flag from all the ID fields, then I need to add UID fields to every table, change the foreign keys as well, and use apply formula to make sure the relations are intact. I am a bit worried that this will have a major impact on the size of the data file. Furthermore, I need to automate the whole process so the upgrade works flawlessly at the customers site. Has anyone ever done this? Any tips? Regards, Rudy Mortier Two Way Communications bvba ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
