Forum: Ruby on Rails How to log SQL Create,Update,Delete only?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Wai T. (Guest)
on 2007-03-28 17:45
Hi everyone,

Does anyone know of a way to log SQL statements (but without the SELECT
queries)?  I would like to keep track of the changes made to the
database, but I don't want the log file to be flooded with SELECT
statements (I am using the default logger at debug level).  Is it
possible to do that with the rails logger?
alexbrie (Guest)
on 2007-03-28 19:39
(Received via mailing list)
You should look for a grep manual - grep only the lines with SQL but
not those with SELECT. Since I'm not a grep master, I'm not sure
how...

On Mar 28, 4:45 pm, Wai T. <removed_email_address@domain.invalid>
Wai T. (Guest)
on 2007-03-28 19:50
alexbrie wrote:
> You should look for a grep manual - grep only the lines with SQL but
> not those with SELECT. Since I'm not a grep master, I'm not sure
> how...
>
> On Mar 28, 4:45 pm, Wai T. <removed_email_address@domain.invalid>

I know it is possible to grep from the log file, but it is very
inefficient.  For example, if I want to look at the last 10 create
command, the entire 1GB log file would have to be loaded and "grepped".
Erik A. (Guest)
on 2007-03-28 22:19
(Received via mailing list)
Why not just use database triggers and use a table for logging?
That way you can also execute Rails logic against the log and do
something
useful with it.
Wai T. (Guest)
on 2007-03-29 01:06
Erik A. wrote:
> Why not just use database triggers and use a table for logging?
> That way you can also execute Rails logic against the log and do
> something
> useful with it.

Thanks for the suggestion Erik.  I am using MySQL, so I can try trigger.
However, this is my first time write one, do you have a sample trigger I
can use as reference?  I can only find example where the trigger operate
on the same database that triggers it.  Thanks.
Wai T. (Guest)
on 2007-03-29 01:11
Hmm...

It doesn't seem like trigger can add anything beside a simple "ITEM
ADDED" to a log table.
Sooo (Guest)
on 2007-03-29 05:42
(Received via mailing list)
U can parse the mysql bin log and track it.

On Mar 28, 9:45 pm, Wai T. <removed_email_address@domain.invalid>
Erik A. (Guest)
on 2007-04-01 13:59
(Received via mailing list)
Wai, I don't think it is a good idea to use database triggers to write
to
log files, if you mean that.
You can write a trigger that logs changes to a database table from which
you
can then SELECT from.

Erik
Romul (Guest)
on 2007-04-13 18:03
>I am using MySQL, so I can try trigger.
>However, this is my first time write one, do you have a sample trigger I
>can use as reference?

DELIMITER //

DROP TRIGGER tbl_Insert//
DROP TRIGGER tbl_Update//
DROP TRIGGER tbl_Delete//

commit //

CREATE TRIGGER tbl_Insert AFTER INSERT ON tbl
  FOR EACH ROW
BEGIN
  INSERT INTO _history (Action, ActDate, User)
    values ('Add', Now(), SUBSTRING_INDEX(USER(),_utf8'@',1));
END//

CREATE TRIGGER tbl_Update BEFORE UPDATE ON tbl
  FOR EACH ROW
BEGIN
  Declare Act varchar(400);

  Set Act = 'Edit:';

  if (select old.field1<>new.field1) then  Select CONCAT(Act,' Field1')
into Act; end if;
  if (select old.field2<>new.field2) then  Select CONCAT(Act,' Field2')
into Act; end if;

  INSERT INTO firm_history (Action, ActDate, User)
    values (Act, Now(), SUBSTRING_INDEX(USER(),_utf8'@',1));
END//

CREATE TRIGGER tbl_Delete BEFORE DELETE ON tbl
  FOR EACH ROW
BEGIN
  Declare Act varchar(400);

  Set Act = 'Del';

  INSERT INTO firm_history (Action, ActDate, User)
    values (Act, Now(), SUBSTRING_INDEX(USER(),_utf8'@',1));
END//
urbanus (Guest)
on 2007-04-13 18:26
(Received via mailing list)
> Does anyone know of a way to log SQL statements (but without the SELECT
> queries)?  I would like to keep track of the changes made to the
> database, but I don't want the log file to be flooded with SELECT
> statements (I am using the default logger at debug level).  Is it
> possible to do that with the rails logger?

Suggestion: do it using the logger and ActiveRecord callbacks.

http://api.rubyonrails.com/classes/ActiveRecord/Ca...

That way you can choose to log before_create, before_save,
before_destroy or whatever.


Regards,
Dave
Romul (Guest)
on 2007-04-23 11:53
urbanus wrote:
>> Does anyone know of a way to log SQL statements (but without the SELECT
>> queries)?  I would like to keep track of the changes made to the
>> database, but I don't want the log file to be flooded with SELECT
>> statements (I am using the default logger at debug level).  Is it
>> possible to do that with the rails logger?
>
> Suggestion: do it using the logger and ActiveRecord callbacks.
>
> http://api.rubyonrails.com/classes/ActiveRecord/Ca...
>
> That way you can choose to log before_create, before_save,
> before_destroy or whatever.
>
>
> Regards,
> Dave

Is any sample how to log changes with callback hooks, especially with
log changes in fields, such was in MySQL sample in this topic.

Reagards,
Romul
This topic is locked and can not be replied to.