Skip to content
Advertisement

weekly event select into file with different filenames depending on the variables(MariaDB)

I’ve always been a silent reader here until now. Now I would like to ask for your expertise and post my ver first question here.

I have to achieve the following task on a weekly basis in my MariaDB via Events:

Every Week on Saturday night at midnight, i want to save the results of a certain view in an excel file (xlsx). The filename should be variable depending on the site_id and the current timestamp.

After saving the results into the file I want to cleanup the DB Tables with another Event, but the previous event must be successfully finished as a condition to start the cleanup event.

e.g.filename: viewname_[site_id]_timestamp.xlsx

overall_weekly _3_01082022.xlsx

This is what I have so far:

EVENT 1(saving results into file):

CREATE EVENT overall_weekly
ON SCHEDULE EVERY 1 WEEK 
STARTS TRUNCATE(CURRENT_TIMESTAMP) + '00:00:00' HOUR_SECONDS 
ON COMPLETION PRESERVE
ENABLE 
DO
    DECLARE  @path = char
    DECLARE  @view = char 
    DECLARE  @site_id = int(3)
    DECLARE  @timestamp = timestamp
    DECLARE  @filetype = char(5)
    DECLARE  @full_filename = char
    
    SET  @path = "/home/reports/"
    SET  @view = "overall_traffic_weekly"
    SET  @site_id = 3
    SET  @timestamp = current_timestamp 
    SET  @filetype = ".xlsx"
    SET  @full_filename = CONCAT(@path,@view,@site_id,@timestamp,@filetype)
         
     SELECT * FROM 
        (   
            SELECT 'Column_name_1','Column_name2', ...
            UNION ALL
        (
            SELECT * FROM overall_weekly 
            WHERE site_id = 3
        )
        ) resulting_set

    INTO OUTFILE  @full_filename
    FIELDS TERMINATED BY ';' 
    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '/n';

EVENT 2(cleanup):

EVENT 1 must be SUCCESSFULLY finished for event 2 to start. IF event 1 finishes with errors, cleanup must not start.

    CREATE EVENT cleanup
    ON SCHEDULE EVERY 1 WEEK 
    STARTS TRUNCATE(CURRENT_TIMESTAMP) + '03:00:00' HOUR_SECONDS 
    ON COMPLETION PRESERVE
    ENABLE 
    DO
      TRUNCATE sourcetable1,
      TRUNCATE Sourcetable2
   ;

Many thanks for reading.

Advertisement

Answer

Problem solved: I used 2 tables instead and matched the 2 records together in a third table

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement