Migrate to cloud - how to handle updating additional database table.

[jair101]

It is about time that we migrate our community to the cloud. I think it will be mostly fine, but there is one key functionality that right now we do through creating auxillary database table and updating it through an event on a daily basis. This is all done directly in the database and once moving to the cloud I won't have access to it... So I am wondering what is the best way to do that.

OK, a bit more details. We are travel oriented community and I have created 200+ Yes/No profile fields the users can toggle for each country they have visited. It looks like this in the profile:  https://imgur.com/cCeNNCm

So once a day, I am executing a database request that imports the data from the custom profiles in an extra database table with the following columns:

id,field_name,iso_code,count
1,field_27,AT,135
2,field_36,DE,1047
etc..

This extra table ensures the mapping between field name, iso code and number of users that have visited the particular country. The query that generates that looks like this:

TRUNCATE countries_map;
Insert into countries_map (ISO, cntvis) select 'AU', sum(field_27) from core_pfields_content;
Insert into countries_map (ISO, cntvis) select 'AT', sum(field_28) from core_pfields_content;
Insert into countries_map (ISO, cntvis) select 'AZ', sum(field_29) from core_pfields_content;
Insert into countries_map (ISO, cntvis) select 'AL', sum(field_30) from core_pfields_content;
Insert into countries_map (ISO, cntvis) select 'DZ', sum(field_31) from core_pfields_content;
Insert into countries_map (ISO, cntvis) select 'AS', sum(field_32) from core_pfields_content;
Insert into countries_map (ISO, cntvis) select 'VI', sum(field_33) from core_pfields_content;
.....

I need this extra query and extra table (countries_map), because we tried to do it by joining the results of 240 queries together and the speed was not pretty. So this query is executed once a day, truncates and populates this table, we are not concerned with live updates for visited countries so once a day is fine. This is all then fed in to this page created with the help of Pages and google geocharts that shows the most visited countries by our members: https://magelanci.com/atlas/

This is a core functionality for our community and I definitely need to find a way to do the same in the cloud without direct database access. So what is the best way to do that?

Some magic through the REST API? Store the auxiliary table in Pages database and somehow update/obtain data from it? I cannot think of how to do that...

Maybe I can commission some developer to do an app for me for this functionality, however I have been burned a bit by disappearing developers, so I'd rather use core IPS functionality.

All ideas and suggestions will be appreciated, this is kind of a blocker for our migration to Cloud, which I definitely need to do as I don't have the time to admin my dedicated server responsibly.

[Marc]

Please bear in mind Im not a developer of plugins, so this is just my own passing thought. Some of the plugin developers may have more thoughts on this.

I would thing a plugin/app is probably your best option here, as you need an extra table and need to ensure you are in control of that table. You would need a plugin/app that adds that table, and adds a task that does the queries you mention above.

[jair101]

7 hours ago, Marc Stridgen said:

Please bear in mind Im not a developer of plugins, so this is just my own passing thought. Some of the plugin developers may have more thoughts on this.

I would thing a plugin/app is probably your best option here, as you need an extra table and need to ensure you are in control of that table. You would need a plugin/app that adds that table, and adds a task that does the queries you mention above.

Thanks. This is my last resort and possibly unacceptable. Doing this on my own will be a bit of a challenge, depending on 3rd party as well. 

If there is no some creative solution, I would probably request my database monthly, run the queries offline and update the data manually. Or maybe stick to the self hosted for the foreseeable future. 

[Jim M]

10 minutes ago, jair101 said:

I would probably request my database monthly, run the queries offline and update the data manually. Or maybe stick to the self hosted for the foreseeable future. 

I don't really see this as an option as we won't be able to run a re-import migration on your community monthly or it will become cost prohibitive to you. Additionally, that would be highly disruptive to your users as it would be the whole database being restored to when the database backup was taken.

A third party application/plugin would be the only way to do this on Cloud because you also would need your custom database table associated to one or it might be removed at some point.

[jair101]

15 minutes ago, Jim M said:

I don't really see this as an option as we won't be able to run a re-import migration on your community monthly or it will become cost prohibitive to you. Additionally, that would be highly disruptive to your users as it would be the whole database being restored to when the database backup was taken.

A third party application/plugin would be the only way to do this on Cloud because you also would need your custom database table associated to one or it might be removed at some point.

No, I meant that I can execute my queries on the database copy that you provide, I won't be needing you to import it back. Once I have it, I can simply hardcode the values in the Pages block that vizualizes the chart. Now, if providing me a copy of the database once a month will also be expensive, I would have to reconsider my entire strategy from scratch. 

[Randy Calvert]

4 minutes ago, jair101 said:

No, I meant that I can execute my queries on the database copy that you provide, I won't be needing you to import it back. Once I have it, I can simply hardcode the values in the Pages block that vizualizes the chart. Now, if providing me a copy of the database once a month will also be expensive, I would have to reconsider my entire strategy from scratch. 

Having a plugin created to export whatever data you need will be the easiest, cheapest, and most sustainable way of doing what you want.   Exporting the database is not simple/easy/quick, especially doing it on a regularly recurring basis.  

Edited July 25, 2023 by Randy Calvert

[jair101]

5 minutes ago, Randy Calvert said:

Having a plugin created to export whatever data you need will be the easiest, cheapest, and most sustainable way of doing what you want.   Exporting the database is not simple/easy/quick, especially doing it on a regularly recurring basis.  

Fair enough, this is the case for today. However, I already see that v5 is introducing major changes to the development, I vaguely remember that all plugins need to be made in an app. I've been also burned with custom plugins and apps before.

Furthermore, I will be needing my database on regular basis regardless, simply to have a copy outside of IPS environment - as much as I trust their backups, it is a good practice to keep a recent copy at an independent place. Once a month I hope is reasonable. 

Anyway, thanks for the feedback all, my main question was whether this could be done with Pages or API, I guess it won't be easy, I'll think about it a bit more. 

[Marc]

13 hours ago, jair101 said:

Furthermore, I will be needing my database on regular basis regardless, simply to have a copy outside of IPS environment - as much as I trust their backups, it is a good practice to keep a recent copy at an independent place. Once a month I hope is reasonable. 

In terms of the data, its your data. All you would do is submit a ticket requesting this, along with your verification code (a code requested for you to add by the client area as a customer). We would then provide you with the backup. If thats once a month, no problem.