Ruby on Rails

Jedrin wrote in post #1058661:
> We want to use SQL/active record for logging but limit the table size
> so that older messages disappear off of the table Some process has to
> run periodically to do that.
>
> Suppose I want to keep my table size to not much bigger than
> 50,000,000 rows or so. What is the easiest, most efficient way to
> delete any extra rows that there may be ? This is an SQL/active record
> problem I have not encountered before.

I don't know the current state of using these with Rails and
ActiveRecord, but it sound to me like what you need is a Round-Robin
Database Storage Engine:

http://www.fromdual.ch/round-robin-database-storage-engine

> I would know in theory how to get all the records as an array by
> calling MyLog.find(:all) and ordering it by date and then iterating
> from where I want to chop off to the end and deleting each one, but
> that may not be the most efficient or acceptable way to do that.

Using MyLog.find(:all) would be a really bad idea. Selecting all from a
database table that has the potential of containing more than a few
hundred records is almost never a good idea.

Besides that's not the right way to count records in a table any. That's
why we have SQL count. Rails support count through aggregates:
http://api.rubyonrails.org/classes/ActiveRecord/Calculations.html#method-i-count

You would also, certainly, not want to sort the results in memory as you
suggest. You would instead as the database engine to do that for you:

Here's an approach that may work. Keep in mind this was put together
quickly so you'll need to test it out for yourself.

MAX_TABLE_SIZE = 50000000
row_count = MyLog.count
delete_limit = (row_count > MAX_TABLE_SIZE) ? row_count - MAX_TABLE_SIZE
: 0
logs_to_delete = MyLog.order('created_at').limit(delete_limit)
logs_to_delete.each do |log|
MyLog.delete(log)
end

Some example SQL the above would generate:
SELECT COUNT(*) FROM "my_logs" #returns 50000150
SELECT "my_logs".* FROM "my_logs" ORDER BY created_at LIMIT 150

DELETE FROM "my_logs" WHERE "my_logs"."id" = 1
DELETE FROM "my_logs" WHERE "my_logs"."id" = 2
DELETE FROM "my_logs" WHERE "my_logs"."id" = 3
...
DELETE FROM "my_logs" WHERE "my_logs"."id" = 150

Make a background job to run that daily. Probably still not the most
efficient way to do it, but shouldn't be too bad if run often enough.
I'm sure there's a way to do this without calling separate delete
statements for each object, but I'll leave that as an exercise for the
reader.

--
Posted via http://www.ruby-forum.com/.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To post to this group, send email to rubyonrails-talk@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.