All geek questions in one place

Add scope autoincrement to existing column in file migration rails

I have records and organizations in my database. Messages belong_ to the organization and organization has_many messages.

I have a post_id column in my mail table, which I now manually increment when creating a new message. How to add auto-increment to this column bound to organization_id?

I am currently using mysql as my database, but I plan to upgrade to PostgreSQL, so the solution should work for both, if possible :)

Thanks a lot!

+4
source share
4 answers

@ richard-huxton has the correct answer and is thread safe.

Use a transaction block and use SELECT FOR UPDATE inside this transaction block. Here is my rail implementation. Use the "transaction" in the ruby ​​class to start the transaction block. Use the "lock" on the line that you want to block, essentially blocking all other concurrent accesses to this line, which is necessary to ensure a unique serial number.

class OrderFactory def self.create_with_seq(order_attributes) order_attributes.symbolize_keys! raise "merchant_id required" unless order_attributes.has_key?(:merchant_id) merchant_id = order_attributes[:merchant_id] SequentialNumber.transaction do seq = SequentialNumber.lock.where(merchant_id: merchant_id, type: 'SequentialNumberOrder').first seq.number += 1 seq.save! order_attributes[:sb_order_seq] = seq.number Order.create(order_attributes) end end end

We run sidekiq for background jobs, so I tested this method by creating 1000 background jobs to create orders using 8 workers with 8 threads. Without a lock or transaction block, a duplicate sequence number is executed as expected. With a lock and a transaction block, all sequence numbers seem unique.

+5
source

OK - I'll be rude. I do not see the significance of this. If you really want this, this is what you will need to do.

First, create the org_max_post (org_id, post_id) table org_max_post (org_id, post_id) . Fill it when adding a new organization (I would use a database trigger).

Then, when adding a new message, you need to:

  • BEGIN Transaction
  • SELECT FOR UPDATE this line of the organization for its blocking
  • Increase post_id by one, refresh the line.
  • Use this value to create a message.
  • COMMIT transaction to complete your updates and block release.

You want all this to happen within a single transaction, and with the corresponding row locked in org_max_post. You want to make sure that the new post_id gets allocated to one and only one post, and also if the message cannot fix that you are not wasting post_id time.

If you want to get smart and shorten SQL in your application code, you can do one of:

  • Wrap the portion of the hole above in the user-defined function insert_post ().
  • Paste through a view that does not have post_id and exposes it using a rule / trigger.
  • Add a trigger that overwrites everything specified in the post_id column with a correctly updated value.

Deleting a message obviously does not affect your org_max_post table, so it will not interrupt your numbering.

Prevent any database-level message updates with a trigger. Check for any changes to OLD vs NEW post_id and select an exception, if any.

Then delete the existing redundant id column in the message table and use (org_id, post_id) as the primary key. If you encounter this problem, you can use it as your button.

Oh - and post_num or post_index is probably better than post_id as it is not an identifier.

I do not know how much of this will play well with rails. I am afraid that the last time I looked at it, database processing was ridiculously primitive.

+4
source

Firstly, I have to say that this is not a good practice, but I will focus only on the solution to your problem: you can always count the number of organization posts by running on your PostsController:

 def create post = Post.new(...) ... post.post_id = Organization.find(organization_id).posts.count + 1 post.save ... end

You do not have to modify the database yourself. Let ActiveRecord take care of this.

0
source

Good to know how to implement it. I would rather use the stone itself.

0
source

Source: https://habr.com/ru/post/1485431/

More articles:


All Articles