[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

How to check record duplication before saving?

Frank Tsao

4/11/2008 8:37:00 PM

Hi, everyone,

I am a beginner in ruby programming and I have a minor question about
data checking, if someone who could give me a suggestion that would be
great.

The system needs to prevent a duplicated record happened in database,
the record is consisted of two fields which are column A and column B.
Supposing a record has been saved in database that the A=1, B=1.
Afterward, the same set of record can not be accepted again, for
example, record: A=1, B=2 and A=2, B=1 both are acceptable. In other
words, record: A=1, B=1 can not be saved in database.

So how to check and prevent a new record that has the same columns with
same values tend to be saved in database?


Frank
--
Posted via http://www.ruby-....
25 Answers

Dave Thomas

4/11/2008 8:41:00 PM

0


On Apr 11, 2008, at 3:36 PM, Frank Tsao wrote:
> So how to check and prevent a new record that has the same columns
> with
> same values tend to be saved in database?

Try creating a unique database index that spans the two columns.


Regards


Dave

Phillip Gawlowski

4/11/2008 8:48:00 PM

0

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Frank Tsao wrote:
| Hi, everyone,
|
| I am a beginner in ruby programming and I have a minor question about
| data checking, if someone who could give me a suggestion that would be
| great.
|
| The system needs to prevent a duplicated record happened in database,
| the record is consisted of two fields which are column A and column B.
| Supposing a record has been saved in database that the A=1, B=1.
| Afterward, the same set of record can not be accepted again, for
| example, record: A=1, B=2 and A=2, B=1 both are acceptable. In other
| words, record: A=1, B=1 can not be saved in database.
|
| So how to check and prevent a new record that has the same columns with
| same values tend to be saved in database?

Most database systems can handle that, be defining a UNIQUE constraint
on columns.

However, that means that A = 1 and B = 2 won't work.

Different methods to access databases have different ways to handle
these kinds of collisions, or at least different ways to implement the
collision detection.

(For example, you can fetch all records, and check if they are already
set, but that is rather bad for memory consumption and speed, but might
be enough in your case.)

So, if you can tell us how you access your database (and what it is), we
can help you out much better.

- --
Phillip Gawlowski
Twitter: twitter.com/cynicalryan

~ - You know you've been hacking too long when...
...your brain keeps hallucinating random "system error: collision with
stack heap" or "Guru Meditation Mode # Three billion and fifty-two,
press left button to continue" or even "This is not a DOS disk." error
messages.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail....

iEYEARECAAYFAkf/zngACgkQbtAgaoJTgL/2mwCfU4Ndpir25pE11+V9JyTYvAmB
CAkAn0N6erBRi8ISLPD/ptHkM/GhFMGU
=oV1Q
-----END PGP SIGNATURE-----

Dave Thomas

4/11/2008 9:03:00 PM

0


On Apr 11, 2008, at 3:47 PM, Phillip Gawlowski wrote:
> Most database systems can handle that, be defining a UNIQUE constraint
> on columns.
>
> However, that means that A = 1 and B = 2 won't work.

Sure it will- you define the unique index to span the two columns, and
then only non-unoque combinations of those columns will fail.

Dave

RubyTalk@gmail.com

4/11/2008 9:46:00 PM

0

If you are using Rails and migrations

add_index(:table, [:column1, :column2], :unique => true)

if not something like this sql

CREATE UNIQUE INDEX index_name ON table(column1,column2);

Becker

On Fri, Apr 11, 2008 at 2:02 PM, Dave Thomas <dave@pragprog.com> wrote:
>
> On Apr 11, 2008, at 3:47 PM, Phillip Gawlowski wrote:
>
> > Most database systems can handle that, be defining a UNIQUE constraint
> > on columns.
> >
> > However, that means that A = 1 and B = 2 won't work.
> >
>
> Sure it will- you define the unique index to span the two columns, and then
> only non-unoque combinations of those columns will fail.
>
> Dave
>
>

Frank Tsao

4/11/2008 11:01:00 PM

0

RubyTalk@gmail.com wrote:
> If you are using Rails and migrations
>
> add_index(:table, [:column1, :column2], :unique => true)
>
> if not something like this sql
>
> CREATE UNIQUE INDEX index_name ON table(column1,column2);
>
> Becker

Thanks for your help, but the situation in A=1, B=2 or A=2, B=1 that
wont be accepted if using unique.

My point is that when record(A=1, B=1) was existed in database, any new
record(A=1, B=1) can not be valided. They can be (A=1, B=2), (A=1, B=3)
or (A=5, B=1) etc.
--
Posted via http://www.ruby-....

Frank Tsao

4/11/2008 11:02:00 PM

0

Dave Thomas wrote:
> On Apr 11, 2008, at 3:36 PM, Frank Tsao wrote:
>> So how to check and prevent a new record that has the same columns
>> with
>> same values tend to be saved in database?
>
> Try creating a unique database index that spans the two columns.
>
>
> Regards
>
>
> Dave

Thanks for your help, but the situation in A=1, B=2 or A=2, B=1 that
wont be accepted if using unique.

My point is that when record(A=1, B=1) was existed in database, any new
record(A=1, B=1) can not be valided. They can be (A=1, B=2), (A=1, B=3)
or (A=5, B=1) etc.
--
Posted via http://www.ruby-....

Frank Tsao

4/11/2008 11:11:00 PM

0

Thanks for your help. I just used Instant Rails 2.0 for developing.
Mongrel and Sqlite.
--
Posted via http://www.ruby-....

Phillip Gawlowski

4/11/2008 11:20:00 PM

0

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Frank Tsao wrote:
| Thanks for your help. I just used Instant Rails 2.0 for developing.
| Mongrel and Sqlite.

As far as I can see, you have to iterate of all the present records to
check for uniqueness of values, since SQLite3 doesn't allow for a
multi-column uniqueness.

With MySQl and other RDBMSes, Dave Thomas' idea should work (I don't
know, since I never had to handle this situation yet myself).

- --
Phillip Gawlowski
Twitter: twitter.com/cynicalryan

Rule of Open-Source Programming #13:

Your first release can always be improved upon.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail....

iEYEARECAAYFAkf/8hUACgkQbtAgaoJTgL88EgCfWkL9pAPPKoEJ/8fBNWdaoa48
PbUAoINqSXEudAti8l6/YlauOAgPp1/f
=ZaP/
-----END PGP SIGNATURE-----

Todd Benson

4/11/2008 11:44:00 PM

0

On Fri, Apr 11, 2008 at 6:01 PM, Frank Tsao <shuhao.tsao@gmail.com> wrote:
> RubyTalk@gmail.com wrote:
> > If you are using Rails and migrations
> >
> > add_index(:table, [:column1, :column2], :unique => true)
> >
> > if not something like this sql
> >
> > CREATE UNIQUE INDEX index_name ON table(column1,column2);
> >
> > Becker
>
> Thanks for your help, but the situation in A=1, B=2 or A=2, B=1 that
> wont be accepted if using unique.
>
> My point is that when record(A=1, B=1) was existed in database, any new
> record(A=1, B=1) can not be valided. They can be (A=1, B=2), (A=1, B=3)
> or (A=5, B=1) etc.

You probably have a primary key constraint on one of the columns
(unless you are using kindergarten SQLite). Example without correct
caps...

create table stuff (
colA int not null,
colB int not null,
primary key (colA, colB)
)

...or if you succumb to the idea integer IDs for the primary key are a
good idea...

create table stuff (
id int not null primary key,
colA int not null,
colB int not null,
unique (colA, colB)
)

Not tested.

Todd

Todd Benson

4/12/2008 4:34:00 PM

0

On Fri, Apr 11, 2008 at 6:43 PM, Todd Benson <caduceass@gmail.com> wrote:

> You probably have a primary key constraint on one of the columns
> (unless you are using kindergarten SQLite).

That probably sounded bad. Sorry.

> Example without correct
> caps...
>
> create table stuff (
> colA int not null,
> colB int not null,
> primary key (colA, colB)
> )
>
> ...or if you succumb to the idea integer IDs for the primary key are a
> good idea...
>
> create table stuff (
> id int not null primary key,
> colA int not null,
> colB int not null,
> unique (colA, colB)
> )

In rails, I should point out I _have_ to use the integer ID for my
primary key, which irks me to no end because it goes against
everything I know about set theory. That whining aside, I might try
to fix it, but the underlying rails code is intimidating.

Todd