====== 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:<code>DROP TABLE ipl_users
CREATE VIEW ipl_users AS SELECT * FROM master.ipl_users</code>
  - 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:<code>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)</code>
  - 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.
===== Links =====
  * [[https://joomla.stackexchange.com/questions/10145/how-to-share-table-between-2-or-more-databases|How to share table between 2 or more databases?]]
  * [[https://forum.joomla.org/viewtopic.php?f=470&t=473666#p2015114|Users Sharing or Synchronisation between multiple sites]]
  * [[http://muddyhudson.com/blog/1-socialtech/128-hack-joomla-cookie-domain-for-moodle-cross-login|Hack Joomla Cookie Domain]]
  * [[https://dev.mysql.com/doc/refman/5.7/en/create-view.html|CREATE VIEW Syntax]]
  * [[https://dev.mysql.com/doc/refman/5.7/en/view-updatability.html|Updatable and Insertable Views]]
  * [[http://www.mysqltutorial.org/create-sql-views-mysql.aspx|Creating Views in MySQL]]