Add cron jobs

[fkollmann]

Hi, please add a cron scheduler mechanism, e.g. based on the edge functions. Best Regards, Felix

[marcobambini]

Hi @fkollmann, thanks a lot for your request.
Can you please describe a use case for that functionality?

[fkollmann]

Thanks for the feedback!

Sure, basically batch processing of database tables, basic datawarehousing for reporting and providing data.

My use case: Events are coming in via the edge function. These events now need to be processed, basic event sourcing.

I would use a trigger/webhook, but I do not want to process them on a per-event basis, but multiple at once, to be more efficient.

Hope this helps.

Best Regards, Felix

[alelazcano]

@fkollmann I'll explain how I solved it with SQLite (native), but it should also work without problems in SQLiteCloud. I must admit that it's a somewhat complicated workaround.

If you receive the information via webhook, you then save the data in one or more tables. And then, you want to process that data to generate other information in the same or other tables.

I'll give an example that comes to mind, thinking out loud:

1. You receive orders from an e-commerce site, save them in total_orders.
2. Then you want those orders to migrate to other tables according to certain criteria, with only a select number of columns (and not all of them), and you will sort them by routes or delivery zones to fulfill the orders.
3. Orders from Zone A will go to the Transport A table, and those from Zone B will go to the Transport B table.

How do you divide that into batches? Why would you do it in batches?

First, you could program a trigger that, when total_orders is updated, reads the order Zone and, based on that, copies the fields you need to the Transport table XX or YY. This will execute the recipe every time an event you configure (update, create, delete, etc.) occurs in that table.

If you want to do it in batches, which is sometimes necessary and sometimes not, but rather if you're looking to run it in batches because you don't understand the benefits of events (although it could be due to performance, architecture, etc.; you have to analyze the use case), you can create an "intermediate table" or a "special" column that serves as a trigger index.

Let me explain:

Option 1) You can take the datetime column from the orders and select that the trigger is fired when the minutes are a multiple of 5. Or when the auto-increment ID is a multiple of 15 (15, 30, 45, 60, 75, 90, 105, 120, 135, 150, 165, 180, 195, 210, 225, 240, 255, 270, 285, 300, 315, 330, 345, 360, 375, 390, 405, 420, 435, 450, 465, 480) it will be executed. This will mean that it won't be executed on each new row, but rather when a "batch" of 15 orders is completed.

Option 2) In my case, we had a log table where we had a PHP cron job that ran every 15 minutes. We had to write a line when it completed successfully. There, a trigger would be executed that would take that update, and we would move data between tables. And then we changed that cron job. It ran every minute, populated a general table, and used a trigger to detect when the update time and/or ID were multiples of 5, so we did it in batches of 5.

It's a bit convoluted; in MySQL or Postgres, it could be solved with a Store Procedure, but SQLite doesn't have stored procedures. Likewise, most use cases can be solved creatively with triggers.

Please tell me if this makes sense to you. Although it's been a while since your question, it's something that, since we use SQLite (native) so much, we've managed this way. Now that we're testing managed cloud platforms, we're trying other ways.