foodclub.org

Our club tracks order history as a requirement for membership. members must order the last 2 out of 3 orders to keep their membership. This has been tracked in the past with a spreadsheet, but it would be much easier to run a db query. Is there some way to setup a query to execute automatically and email someone? or to connect it to a web page? or to simply create a stored procedure? Also, is it possible to see a column in the user view that would denote current active users?

Let me know what might be possible. I already have the queries written.

Thank you,

Wait, so you already wrote SQL queries of your "custom_view_..." tables and tested them in phpmyadmin? But you want to know if it's possible to automate these so you don't have to manually run them?

That is quite the "advanced" question... I don't know the answer. phpMyAdmin might have something. Have you looked around in there? I could investigate a stored procedure, but that wouldn't have the e-mail connectivity. All it would do is save you from copy/pasting the SQL query in each time.

It doesn't really look like there is any way to get the results of the query to an email address through phpMyAdmin. I did some google-ing on that today.

It wouldn't be me executing the queries. It is other folks managing the club that are not as familiar with sql - thus I'm trying to make it really easy. But even if it was me, it would sure be nice to have that info land in my mailbox every ordering cycle rather than going and pulling it each time.

Unless there is some neat functionality hidden in one of the tools that are already setup here, I do not (rightly) have privileges to do any of it. I'm probably the only one that would be interested in some kind of reporting tool on top of MariaDB?

Maybe best possibility is to get privileges to 'create routine' so I can create a stored procedure and simplify the query that would need to be executed by those other folks? It doesn't hurt to ask right?

Okay, I granted you "create routine" privilege. I don't see what harm you could cause with that. Hopefully you don't prove me wrong.

As for e-mail connectivity, I think it wouldn't be that hard to write a small script, the problem is maintaining it. The only thing that really gets maintained on the foodclub servers these days is code that has been integrated into the core foodclub codebase. It has to be that way so the servers can go scalable, i.e. many servers in different data centers. So basically it would need to be some feature added to Foodclub admin, like "E-mail results of custom SQL query". That is not out of the question, just would need some other clubs requesting it to get done anytime soon.

As for a reporting layer on top of MariaDB, if you find any good open source candidates, I'm happy to consider them. Especially if they are easy to maintain (rpm package for centos), have a good security record, and run without modification under SElinux.

Wondering if is it possible to see a column in the user view that would denote current active users? What I want to do is exclude any disabled (non-active) users.

The user_state column of your custom_view_users_naturalfamilyclub view has this info. user_state can be either NULL (active), frozen, suspended, or disabled.

Thanks for your help. Couple of issues: I am using user 'naturalfamly'. The 'user_state' column is not visible. I also don't seem to have permission to create a routine.

Okay, user_state should be there now. Your view was created before the column was added, so it didn't have it. The view needed to be dropped and re-created.

About the routine, yes I wasn't sure about the security implications of that after all. I'm not sure if mariadb allows you to query tables that you don't have access to, when you have create routine privileges. Also I was thinking that really what you need is a view, not a routine. Can you send me the code for the routine that you want to create? I can just create it for you, if it can't be done with a view.