nubuilder populating primary key field in table with data

nubuilder populating primary key field in table with data

Postby alexdp » Tue Feb 21, 2017 1:12 am

Hello to you all,
I am a complete (and almost hopeless) nuB, so please be kind to me even if this question is too trivial.
Problem:
I completed the setting up of all the needed tables with phpMyAdmin. Data has been imported successfully into tables. Now, after reading documentation, I realized that nuBuilder recommends to use a primary key field with the following method : name of field = nameoftable_id, with varchar 25 char long, primary key. With this setup, nuBuilder will populate automatically this field on entry.
Now, I am sure you see me coming, what happends with my tables with data already in via phpMyAdmin. After I added the recommended primary key _id field and browse the table, for sure there is no data in the table_id field.
What can or should I do to correct this problem?
Thank you for reading me. Regards, Alexdp
Last edited by alexdp on Sat Mar 25, 2017 9:50 pm, edited 1 time in total.
alexdp
 
Posts: 10
Joined: Sat Sep 24, 2011 11:19 am

Re: nubuilder populating primary key field in table with dat

Postby admin » Thu Feb 23, 2017 4:49 am

Alexdp,

You'll need to populate each of these fields manually, with a unique string.

Steven
admin
Site Admin
 
Posts: 3215
Joined: Mon Jun 15, 2009 9:53 am

Re: nubuilder populating primary key field in table with dat

Postby alexdp » Thu Mar 09, 2017 4:27 am

Thank you for your reply.

The problem is that I am working with a dummy database and the number of records is limited. If I decide to use my actual database, I have tables containing more than 600 K records. I do not see myself or a member of our staff entering 600 000 unique primary key manually.

There must be a way to populate the pk field with some data that follows the nu(id) format. This could be done, I presume via phpMyAdmin or via nuBuilder procedures. With very limiting knowledge, I will try to find a simple solution.

Note : I am pretty sure my situation is not unique and that prospective users of nuBuilder would be interested.

Regards
alexdp
 
Posts: 10
Joined: Sat Sep 24, 2011 11:19 am

Re: nubuilder populating primary key field in table with dat

Postby alexdp » Thu Mar 09, 2017 6:35 am

Hello to you all,

I finally found an easy way to populate the primary key field in a nuBuilder table in which data was aready imported (csv file).
This procedure will generate a unique field content for every records in the table.
The content will be an alphanumeric string (lower case letters and digits) with a length of 15 characters (just like what nuBuilder is generating within the edit forms).
Everything is done within phpMyAdmin

----------------------------------------------------------
In the table already populated with data (tablename), create a new field named tablename_id with ( varchar (25), do not set primary key yet)
Go into SQL tab of phpMyAdmin and type : update `tablename` set `tablename_id`=(SELECT SUBSTR( MD5( UUID( ) ) , 1, 25 ));
Check the result in the Browse tab
Make the tablename_id field primary key
----------------------------------------------------------
Code: Select all
UPDATE  `team` SET  `team_id` = ( SELECT SUBSTR( MD5( UUID( ) ) , 1, 25 ) )
Last edited by alexdp on Thu Mar 23, 2017 9:25 pm, edited 1 time in total.
alexdp
 
Posts: 10
Joined: Sat Sep 24, 2011 11:19 am

Re: nubuilder populating primary key field in table with dat

Postby admin » Sun Mar 19, 2017 8:39 am

Nice answer alexdp..

In nuBuilder 3 we suggest 25 characters, just so automatic new ids created by nuBuilder still fit.
(it probably won't matter though).


Steven
admin
Site Admin
 
Posts: 3215
Joined: Mon Jun 15, 2009 9:53 am

Re: nubuilder populating primary key field in table with dat

Postby EcoReality » Mon Mar 20, 2017 2:20 am

alexdp wrote:I do not see myself or a member of our staff entering 600 000 unique primary key manually... There must be a way to populate the pk field with some data that follows the nu(id) format.


Is your existing PK referenced elsewhere in your database?

If not, you could simply remove that field's PK designation (it could still be a UNIQUE key), and add a new PK field that is unsigned int auto-increment. MySQL will then go through and automagically give each record a unique, numeric PK.

The allure of meaningful primary keys is huge. But I find the world is against that point-of-view, and that it is generally best to have your PK be an unsigned int opaque value, unrelated to anything in the record.
:::: Jan Steinman EcoReality Co-op ::::
EcoReality
 
Posts: 26
Joined: Wed Feb 15, 2017 5:20 pm
Location: Salt Spring Island, British Columbia, Canada

Re: nubuilder populating primary key field in table with dat

Postby admin » Fri Mar 31, 2017 2:01 pm

Not in nuBuilder
admin
Site Admin
 
Posts: 3215
Joined: Mon Jun 15, 2009 9:53 am


Return to General