mysql DB specific primary key

[pistachio]

Hello everyone,

Hopefully a quick one. I have a database in which I want to put some
data into from a file. I've parsed through the file got what I want
and am about to put it into the database. But I want to check the
primary key and see if it's free before I put the data in. The
database has gaps in the primary key sequence from deleting data, I
want to put data into those gaps. Is there a quick way to do this. I
was going to do some like :

1) Go through database see where no data for primary key
2) sort this number into an array
3) use array to specify empty database slots.

Any ideas would be great esp with some code. Or how to do the 1st line
(not /usr/bin/perl -w for the funny ppl out there ).

Thanks

Paul

 

[pistachio]

You could have posted this question anywhere that MySQL is on topic.

Yes I guess I could but I was looking for Perl help

If the primary key is flagged as "auto-increment" then the whole basis for
your script has vanished. In that case the value of the primary key is
meaningless to all except for the MySQL server itself. Even though you can
query for it and see gaps in its value, your obsession to fill those gaps
is at best not recommended and at worst a mild case of OCD.

Actually it's not. The database is an online DB where the primary key
is used as a ref to the data. Hence I cannot reset it as you suggest,
nor is it an OCD type thing. I have a prim key of 15000 odd ppl think
there are 15000 piece's of info in the db, which isn't true. Before
you go and tell me that wasn't the way to make the DB, I wasn't the
one to make it, I'm just responsible for it. So thanks for the helpful
suggestion.

use DBI;

Umm funny. I was thinking more like the DBI select command with the
perl capture, something with the for loop and an array. Anyway for
your suggestion it should really be use strict; before use DBI;

Cheers,

Paul

 

[Tad McClellan]

The
database has gaps in the primary key sequence from deleting data, I
want to put data into those gaps.

Why do you want to put data into those gaps?

That is, what do you think that that will buy you?

 

[Mumia W.]

Hello everyone,

Hopefully a quick one. I have a database in which I want to put some
data into from a file. I've parsed through the file got what I want
and am about to put it into the database. But I want to check the
primary key and see if it's free before I put the data in. The
database has gaps in the primary key sequence from deleting data, I
want to put data into those gaps. Is there a quick way to do this. I
was going to do some like :

1) Go through database see where no data for primary key
2) sort this number into an array
3) use array to specify empty database slots.

Any ideas would be great esp with some code. Or how to do the 1st line
(not /usr/bin/perl -w for the funny ppl out there ).

Thanks

Paul

I don't think there is a quick way. I would suggest that you create a
table to hold open keys and write an SQL script to populate that table.

When your Perl script needs an open key, it would grab a key from the
"open_keys" table (sorted) and delete the record from that table.
Eventually, the Perl script would break because you would have run out
of open keys, but your primary objective (to fill the open slots) will
have been fulfilled.

 

[xhoster]

Yes I guess I could but I was looking for Perl help

Actually it's not. The database is an online DB

As opposed to a DB that is encased in concrete and buried in a unused
coal mine?

where the primary key
is used as a ref to the data. Hence I cannot reset it as you suggest,

He didn't suggest you reset it.

nor is it an OCD type thing. I have a prim key of 15000 odd ppl think
there are 15000 piece's of info in the db, which isn't true.

Odd people are going to think odd things, no matter what you do.
Micromanaging your database in an attempt to cure other people's delusions
is a losing proposition.

Before
you go and tell me that wasn't the way to make the DB, I wasn't the
one to make it, I'm just responsible for it.

No, you seem to be determined to compound the original mistakes over
and over again. That makes you irresponsible for it.

Umm funny. I was thinking more like the DBI select command with the
perl capture,

How could we do that without first have the DBI loaded? Since you
already seem to know what it is you want, what exactly are you asking
us for? How about you show us what you already have and tell us
where you got stuck?

Xho

 

[Ted Zlatanov]

p> Hello everyone,
p> Hopefully a quick one. I have a database in which I want to put some
p> data into from a file. I've parsed through the file got what I want
p> and am about to put it into the database. But I want to check the
p> primary key and see if it's free before I put the data in. The
p> database has gaps in the primary key sequence from deleting data, I
p> want to put data into those gaps. Is there a quick way to do this. I
p> was going to do some like :

p> 1) Go through database see where no data for primary key
p> 2) sort this number into an array
p> 3) use array to specify empty database slots.

p> Any ideas would be great esp with some code. Or how to do the 1st line
p> (not /usr/bin/perl -w for the funny ppl out there ).

Use Rose:B::Object or Class:BI to automate the task. You'll save
yourself many hours of work.

Also learn about primary keys and indexing so you know why the "gaps" in
the primary keys are completely irrelevant to your application and you
shouldn't worry about filling them in. I think you are making things
too complicated unnecessarily.

Ted