Is it possible to separate template sites from user subsites DB?

I have Pro Sites, New Blog Templates, Domain Mapping, and Provisioning network installed and activated. Say I have around 200 original template sites freshly and carefully crafted/built and yet still no mixing with other user's subsites, all of them (template sites) are kept together under one roof and created in a sequential number of blog_id. I've come across the big problem when facing with maintenance and update the main site.

- I have a tendency to add a new template every week. Say the last one was Blog_id & New_blog_template_id = 200. When adding the new ones, I want the whole set of new blog template id are in a same group of number. Ex. the new ones should be 201, 202, and so on without any user subsites are mixed in between.
- When maintenance and update the old template sites, if they are in the same group, sequential numbers, it would be easier to remember and won't get confused.

My questions are as follows:
1. Could I separate the template sites from user's subsites in a different DB?
2. If #1 is not doable, could I reserve template sites in a range ex. 1-500 are for template sites, whenever there is a new site signup from the users, it will start from 501 and so on.?

  • Sajid

    Hi @sene,
    Hope you are doing good today :slight_smile:

    I am afraid, you can not use separate databases to for templates and separate for user sites. We do have MutliDB plugin but it still creates different databases automatically according to your settings. So I am not exactly sure you can do something like that but you can try it. Please bear in mind that this is not a drag and drop to plugins folder type plugin. Its only for advanced users who have good knowledge of WordPress Multisites, servers and databases.

    To reserve the IDs you can alter the ID of last created subsite (make sure its not a live website) from example make it 500. Now MySQL will automatically add higher ID number for all new sites. You can change the ID from phpMyadmin as seen in attached screenshot.

    But in this case, sites created for templates will also have higher number of IDs. So you have to change that ID number back via phpMyadmin in blogs table and also make sure to update other tables of that site accordingly. For example WordPress adds ID number of that site with all tables of that site. If your site ID is 200 then your subsite wp_posts table will be wp_200_posts. Same goes with all tables of this subsite.

    Hope that helps! Feel free to post a reply if you need further assistance :slight_smile:
    Cheers, Sajid

  • Code Injector

    I forgot to mention that to make a correction/alternation directly via phpMyAdmin isn't the solution I would like to achieve as it is a manual solution not automated. Perhaps a few line of code inside mu-plugins when:-
    - Validate the user role, if the site isn't created by SuperAdmin --> then start using number 501 blog_id automatically and so on.
    - Or other effective alternative solution, please advice.

  • Panos

    Hello @sene,
    Hope you had a nice weekend!

    I find it hard to make this plan work. I have to propose a different approach for this, but I am not sure if it will fit your needs since I haven't figured out where you want to use this functionality.

    Instead of changing the blog ids, you can just make a SELECT in the templates table and either check if a specific blog_id is included in the templates list, or return the list of blog_ids that are used as templates.

    If you are interested in this approach please let me know and also include more information on how you intend to use this, so I could provide further information.

    Kind regards,
    Panos

  • Code Injector

    Hi Panos and Sajid,

    If I understand you correctly, I don't think the offered solution to checking by query is a proper way I should go for. The MultiDB plugin sounds a bit promising to me minus the fact that it may not be working well together with the set of these plugins: Pro Sites, NBT, Domain Mapping, and Provisioning as I am not sure whether they were designed/coded to work on different DBs. My plan is to keep everything organised nice and clean in prepraring for a better site maintenance and etc. The current problem I'm encountering are as follow:

    CURRENT SITUATION
    1. Main Site + Template Sites: Blog_ID = 1 - 200 : Start by having template sites up to 200 sites and all the Blog_ID are in sequence (Original/Startup DB).
    2. TEST Environment (Local Dev Machine) : Backup and upload a current fresh DB to the live server
    3. LIVE Environment (Renting Server) : User#1 starts to subscribe for their subsite --> Blog_ID = 201
    4. LIVE Environment (Renting Server) : User#2 starts to subscribe for their subsite --> Blog_ID = 202
    5. LIVE Environment (Renting Server) : User#3 starts to subscribe for their subsite --> Blog_ID = 203
    6. LIVE Environment (Renting Server) : Backup a new whole DB which includes up to Blog_ID 203 and all related files to my local server in preparing to add the new templates. (PLEASE NOTE Blog_ID 201 - 203 are constantly up for any changes in DB and files after my backup as well as the new subsites from the new users could be created at any time.)
    7. TEST Environment (Local Dev Machine) : Update DB and files to matching with everything as to the live server.
    8. TEST Environment (Local Dev Machine) : Start adding the new Template#1 in addition to my current template collection which most of time requires days/weeks to complete --> Blog_ID = 204 (T)
    9. TEST Environment (Local Dev Machine) : Adding Template#2 --> Blog_ID = 205
    10. LIVE Environment (Renting Server) : User#4 starts to subscribe for their subsite --> Blog_ID = 204 (L)
    11. LIVE Environment (Renting Server) : Compare the different between the current LIVE DB and TEST DB.
    12. LIVE Environment (Renting Server) : Update Blog_ID 201 - 203
    13. LIVE Environment (Renting Server) : Changing the Blog_ID 204 (L) to Blog_ID = 206
    14. LIVE Environment (Renting Server) : Merging the LIVE and TEST DB by adding Blog_ID = 204 (T) & 205 from TEST to LIVE + Replacing Blog_ID 1 -200 from TEST to LIVE in case there's some changes during the development.
    ****** But in a real life, there will be more than just a few sites created, added, edited, updated, and etc in which if I do not keep track of what Blog_ID are for templates and which are for user subsites, it would be a chaos.

    WOULD WANT TO ACHIEVE
    1. Main Site + Template Sites: Blog_ID = 1 - 200 : Start by having template sites up to 200 sites and all the Blog_ID are in sequence (Original/Startup DB).
    - 1.1 Having the main sites + template sites Blog_IDs reserve from 1 - 500.
    - 1.2 Having the user subsites starts from Blog_ID 501 onwards.
    ====== Either 1.1 and 1.2 could be kept in the same DB or on the differnt DB =======

    2. TEST Environment (Local Dev Machine) : Backup and upload a current fresh DB to the live server
    3. LIVE Environment (Renting Server) : User#1 starts to subscribe for their subsite --> Blog_ID = 501
    4. LIVE Environment (Renting Server) : User#2 starts to subscribe for their subsite --> Blog_ID = 502
    5. LIVE Environment (Renting Server) : User#3 starts to subscribe for their subsite --> Blog_ID = 503
    6. LIVE Environment (Renting Server) : Backup a new whole DB (single DB) which includes up to Blog_ID 503 and all related files to my local server in preparing to add the new templates. If there are 2 DBs : Templates DB (1-500) & Subsites DB (501+), then I can ignore this step and proceed to adding new templates on my TEST Environment.
    7. TEST Environment (Local Dev Machine) : Start adding the new Template#1 in addition to my current template collection --> Blog_ID = 201
    8. TEST Environment (Local Dev Machine) : Adding Template#2 --> Blog_ID = 202
    9. LIVE Environment (Renting Server) : User#4 starts to subscribe for their subsite --> Blog_ID = 504
    10 LIVE Environment (Renting Server)
    - 10.1 1 x DB -- Update 1-202 from TEST to LIVE, leave 501+ onwards intact. (single DB scenario)
    - 10.2 2 x DBs -- Replacing from old Templates DB (1-200) to new version (1-202) from TEST to LIVE (multiple DB scenario), and then leave Subsites DB totally untouched on LIVE.

    Hope I don't get you confused. Please advice on the details given. Thank you very much.

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.