Table of Contents
MySQL shared tables for multi-league setup
In one of the leagues I'm applying the website we have 3 different leagues running under the same organisation. We would like to have the ratings calculated over all the games played, which requires a more complex database setup. Some of the core tables and some of the league specific tables need to be shared, which can be achieved with MySQL's Views.
Share tables
- Choose the “master” site from which the users will be shared (this is the database which will actually store the data).
- Copy the $secret value from the master site's configuration.php file to the same file on all the “slave” sites.
- Ensure access to the master site's database is given to the users of each of the slave sites' databases.
- Run the following commands on each slave site table:
DROP TABLE ipl_users CREATE VIEW ipl_users AS SELECT * FROM master.ipl_users
- If you need to connect leagues with existing data, you need to adjust IDs in the following tables: xxx
- You can check whether all primary keys are consecutive with the following SQL query:
SELECT a.id+1 AS start, MIN(b.id) - 1 AS end FROM master AS a, master AS b WHERE a.id < b.id GROUP BY a.id HAVING start < MIN(b.id)
- List of tables to be shared:
| Joomla 1.5 Core | Joomla 3.x Core | Bethico League Extension |
|---|---|---|
| ipl_users | ipl_users | ipl_ipl_bar |
| ipl_session | ipl_session | ipl_ipl_date |
| ipl_stats_agents | ipl_usergroups | ipl_ipl_game |
| ipl_core_acl_aro | ipl_user_notes | ipl_ipl_map |
| ipl_core_acl_aro_groups | ipl_user_profiles | ipl_ipl_match |
| ipl_core_acl_aro_map | ipl_user_usergroup_map | ipl_ipl_player |
| ipl_core_acl_aro_sections | ipl_viewlevels | ipl_ipl_player_game |
| ipl_core_acl_groups_aro_map | ipl_ipl_season | |
| ipl_ipl_site | ||
| ipl_ipl_tournament |
Other settings
- Make sure to set Session Handler to “none” in Global Configuration → System. The database session handler does not work well with shared tables.