Hi all, I''ve got a rails and database question. Accounts have jobs, and job numbers should be assigned per account. Account 89 should have jobs 1-whatever, which are not the same as account 67, which has jobs 1-whatever. In the past, I have accomplished this quite easily with: create table job ( accountid int(10) unsigned not null default 0, jobid int(10) unsigned not null auto_increment, ... other fields ... primary key (accountid, jobid) ); Then, when inserting into the table, mysql takes care that the jobid is the highest for the given account, and unique as well. The rails way is to create one numeric key for the whole table, and then use a specifically named column for the foreign key: create table jobs ( id int(10) unsigned not null auto_increment account_id int(10) unsigned not null default 0, jobseq int(10) unsigned not null, # not assigned by mysql :( ... other fields ... primary key (id) ); Which is workable, but now the job numbers aren''t specific to the customer, as I would like them to be. In fact, in this model, they are not supplied or managed by the database at all. Is there a safe way to generate the next jobseq during the insert operation, such that the jobseq is the next job for the given customer, and, most importantly, two jobs for the same customer cannot have the same jobseq assigned, even accidentally? Many thanks. Regards, Rich
On 7/19/06, Rich Duzenbury <rduz@theduz.com> wrote:> Hi all, > > I''ve got a rails and database question. Accounts have jobs, and job > numbers should be assigned per account. Account 89 should have jobs > 1-whatever, which are not the same as account 67, which has jobs > 1-whatever. > > In the past, I have accomplished this quite easily with: > > create table job ( > accountid int(10) unsigned not null default 0, > jobid int(10) unsigned not null auto_increment, > ... other fields ... > primary key (accountid, jobid) > ); > > Then, when inserting into the table, mysql takes care that the jobid is > the highest for the given account, and unique as well. > > The rails way is to create one numeric key for the whole table, and then > use a specifically named column for the foreign key: > > create table jobs ( > id int(10) unsigned not null auto_increment > account_id int(10) unsigned not null default 0, > jobseq int(10) unsigned not null, # not assigned by mysql :( > ... other fields ... > primary key (id) > ); > > Which is workable, but now the job numbers aren''t specific to the > customer, as I would like them to be. In fact, in this model, they are > not supplied or managed by the database at all. > > Is there a safe way to generate the next jobseq during the insert > operation, such that the jobseq is the next job for the given customer,You could either: 1. Give each account a next_jobseq field and increment this value along with creating the job, inside a transaction with the serialized isolation level. or 2. Lock the jobs table, find max(jobseq)+1 for that account and create the job before unlocking. #1 obviously scales better, as it will let you create jobs for different accounts concurrently> and, most importantly, two jobs for the same customer cannot have the > same jobseq assigned, even accidentally?Most efficient/secure approach is no doubt a unique constraint in the db. create table jobs ( id serial, account_id integer, jobseq integer, primary key (id), unique (account_id, jobseq) ); Rails also has the validate_uniqueness_of validation. See <http://api.rubyonrails.org/classes/ActiveRecord/Validations/ClassMethods.html#M000816>. I think the AR validations add a lot of overhead myself, but figure there may be advantages to using them in addition to db level constraints. Isak> > Many thanks. > > Regards, > Rich > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Rich, I wrote an acts_as_sequenced extension recently that should work nicely here. The code is on my blog. Here is the relevant post: http://www.depixelate.com/articles/2006/07/19/acts-as-sequenced Zack On 7/19/06, Isak Hansen <isak.hansen@gmail.com> wrote:> On 7/19/06, Rich Duzenbury <rduz@theduz.com> wrote: > > Hi all, > > > > I''ve got a rails and database question. Accounts have jobs, and job > > numbers should be assigned per account. Account 89 should have jobs > > 1-whatever, which are not the same as account 67, which has jobs > > 1-whatever. > > > > In the past, I have accomplished this quite easily with: > > > > create table job ( > > accountid int(10) unsigned not null default 0, > > jobid int(10) unsigned not null auto_increment, > > ... other fields ... > > primary key (accountid, jobid) > > ); > > > > Then, when inserting into the table, mysql takes care that the jobid is > > the highest for the given account, and unique as well. > > > > The rails way is to create one numeric key for the whole table, and then > > use a specifically named column for the foreign key: > > > > create table jobs ( > > id int(10) unsigned not null auto_increment > > account_id int(10) unsigned not null default 0, > > jobseq int(10) unsigned not null, # not assigned by mysql :( > > ... other fields ... > > primary key (id) > > ); > > > > Which is workable, but now the job numbers aren''t specific to the > > customer, as I would like them to be. In fact, in this model, they are > > not supplied or managed by the database at all. > > > > Is there a safe way to generate the next jobseq during the insert > > operation, such that the jobseq is the next job for the given customer, > > You could either: > > 1. Give each account a next_jobseq field and increment this value > along with creating the job, inside a transaction with the serialized > isolation level. > or > 2. Lock the jobs table, find max(jobseq)+1 for that account and create > the job before unlocking. > > #1 obviously scales better, as it will let you create jobs for > different accounts concurrently > > > > and, most importantly, two jobs for the same customer cannot have the > > same jobseq assigned, even accidentally? > > Most efficient/secure approach is no doubt a unique constraint in the db. > > create table jobs ( > id serial, > account_id integer, > jobseq integer, > primary key (id), > unique (account_id, jobseq) > ); > > Rails also has the validate_uniqueness_of validation. See > <http://api.rubyonrails.org/classes/ActiveRecord/Validations/ClassMethods.html#M000816>. > > I think the AR validations add a lot of overhead myself, but figure > there may be advantages to using them in addition to db level > constraints. > > > Isak > > > > > Many thanks. > > > > Regards, > > Rich > > > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >