Add SQL snippets functionality (part 1 of dynamic subscriber lists)

[restyler]

This is part 1 of implementing "segments" or "dynamic subscriber lists" (https://github.com/knadh/listmonk/issues/250). This PR adds the foundational SQL snippets functionality that is an isolated (and useful) feature itself and later this can be used by dynamic subscriber lists in a future PR.

The idea of "dynamic subscriber list" would be that it is just another "subscriber list" but it is dynamically updated via cron scheduler (and right before campaign is sent), by executing "sql snippet" attached to this list. I am pretty sure there will be some rough edges here, but at least this is something to start the discussion.

Summary

• Adds SQL snippets database table and CRUD operations
• Provides Vue.js UI for managing reusable SQL query fragments
• Includes SQL validation against allowed tables
• Adds count functionality to test snippet queries

Context

Splitting the dynamic segments feature into two parts:

1. SQL snippets in database (this PR) - foundational reusable query system
2. Dynamically updated segments (future PR) - automatic list membership management

[restyler]

Video demo:

https://github.com/user-attachments/assets/c040d8b3-c4b2-4544-b52c-e0d1494df514

[relikd]

Hahaha, nice to see my idea (https://github.com/knadh/listmonk/issues/250#issuecomment-1821915489) implemented :D thank you!

Regarding your video demo, it looks like the snippet is just pasted in the subscribers list. Maybe it would be better if the filter would use the snippet reference? E.g., if I update the snippet, all occurrences are updated as well. Or is it intentional? If so, what is your imagined scenario? An admin pre-configuring some sample queries for users to be able to edit them?

Thanks for the progress :)

[restyler]

Thanks!

E.g., if I update the snippet, all occurrences are updated as well. Or is it intentional?

This is a great question. I want to minimize the stress for current users (which might be used to editing snippets right here, in textarea at "Advanced" section) and add "progressive enhacement" to the textarea instead of strong reference to SQL snippets.

I have just added a way to quickly save snippet from Subscribers page - and live counter.

[knadh]

Thanks for the PR @restyler! Looks fine prima-facie. I'll review it closer to the next big release.

About the live counter though, I'm not sure if it's ideal. Executing queries continuously on large instances (ours at work has ~17+ million subscribers) just to get the count is going to be very problematic, especially when it's as you type. Also, from a UX perspective, the count is just one-click away anyway.

[restyler]

I'll review it closer to the next big release.

Oh, this would be so nice to have this included in next major release! Thanks!

Executing queries continuously on large instances (ours at work has ~17+ million subscribers) just to get the count is going to be very problematic, especially when it's as you type.

I agree this might be a problem on huge lists. That said, I suspect such extreme cases are quite rare, and for them live editing can be easily disabled with a single checkbox (I think this setting state should be stored, so it is disabled once and forever), and I remember that when I started writing SQL queries in Listmonk, my biggest pain was the lack of interactivity and the lack of transparency around what “attribs” my subscribers actually have (this could be solved by some lightweight sniffing feature - again, it could be a bit slow on 2% of listmonk installations - but for another 98% this would mean much easier adoption and usage).

[ilkkao]

Also there could be a "Execute snippet" button that updates the number. This would be similar e.g. how formula editing in Grafana dashboards work.

[Rush]

I’m considering adopting Listmonk, and the addition of this feature (dynamic subscriber lists) would make it an easy decision to switch.

Example use case: localized newsletters and user selecting their language of choice in the application.

With this feature, it'd be a simple case of querying for the right language property.

Without this feature, the application has to move the user between lists.