Storing Last Login Plugin value in SQL
The last-login plugin sends a number (epoch seconds) as uint or string in a query to sql. If i create a column type in sql of varchar() the number is saved in the column as a string, but not very useful.
I can not figure out how to get that value into a date/time column such as date, datetime or timestamp. All three cause an error of Incorrect datetime value: '1631556506' for column.
Anyone have any idea how to get the last-login plugin to update a date/time column in sql?
On 9/14/21 02:12, dovecot@ptld.com wrote:
Anyone have any idea how to get the last-login plugin to update a date/time column in sql?
I use this to throttle updates to once in 900 seconds:
create trigger tg1 before update on mailacct for each row if new.lastlogin < (old.lastlogin + 900) then set new.lastlogin = old.lastlogin; end if;
You can try:
create trigger tg1 before update on mailacct for each row set new.logindate = FROM_UNIXTIME(new.lastlogin);
On 09-13-2021 2:20 pm, Gedalya wrote: On 9/14/21 02:12, dovecot@ptld.com wrote:
Anyone have any idea how to get the last-login plugin to update a date/time column in sql?
I use this to throttle updates to once in 900 seconds:
create trigger tg1 before update on mailacct for each row if new.lastlogin < (old.lastlogin + 900) then set new.lastlogin = old.lastlogin; end if;
You can try:
create trigger tg1 before update on mailacct for each row set new.logindate = FROM_UNIXTIME(new.lastlogin);
The problem im having with the last-login plugin is the only option i can see to use is a dict map{}. I can not create my own query for the plugin to execute otherwise this would be way easier. Using the map{} method all you can do it tell it the column name to update and the plugin/dovecot writes the insert on dupe query automatically removing any kind of flexibility or customization.
Is there a way to use the plugin and write your own sql query to run instead of using a dict map{}?
On 9/14/21 02:25, dovecot@ptld.com wrote:
The problem im having with the last-login plugin is the only option i can see to use is a dict map{}. I can not create my own query for the plugin to execute otherwise this would be way easier. Using the map{} method all you can do it tell it the column name to update and the plugin/dovecot writes the insert on dupe query automatically removing any kind of flexibility or customization.
Is there a way to use the plugin and write your own sql query to run instead of using a dict map{}?
Assuming you use MariaDB / MySQL, you create this trigger in the database. Assuming your int/bigint/varchar column is lastlogin and the table name is mailacct, the trigger will update the datetime logindate column whenever the table is updated, by whatever existing queries you have. This is your recourse when you can't get your queries to do the right thing in the first place.
participants (2)
-
dovecot＠ptld.com
-
Gedalya