I am working on an application that needs to support the multi-tenant model. I am using the symfony2 php framework and doctrine2.
我正在开发一个需要支持多租户模型的应用程序。我正在使用symfony2 php框架和doctrine2。
I'm not sure the best way to go about architecting this requirement. Does Symfony's ACL functionality provide a part of the solution?
我不确定构建此要求的最佳方法。 Symfony的ACL功能是否提供了解决方案的一部分？
What recommendations or ideas could you provide? Are there any sample symfony2 applications or open source applications that are available which have implemented this strategy?
您可以提供哪些建议或想法？是否有任何可用的示例symfony2应用程序或开源应用程序已实施此策略？
My first thought is to use a tenant_id column in all the tables and have this relate to the account object in the application. I'm not sure though if ACL is supposed to take care of what i'm wanting to do, or if your still responsible for all of the queries against your objects so they don't return un-authorized data.
我的第一个想法是在所有表中使用tenant_id列，并将其与应用程序中的帐户对象相关联。我不确定ACL是否应该照顾我想要做的事情，或者你是否仍然对所有针对你的对象的查询负责，这样他们就不会返回未经授权的数据。
If I wasn't using Doctrine, it might be easy to say just append Where tenant_id = @accountid to each query, but i'm not sure that is the right approach here.
如果我没有使用Doctrine，可能很容易说只是追加每个查询的where tenant_id = @accountid，但我不确定这是正确的方法。
Thanks
谢谢
6 个解决方案
#1
6
We've been doing this for some time now (although not with symfony and doctrine but the problems remain the same) - we started with one huge database and implemented a 'environment identifier' per row in each of the tables. This way schema migrations were easy: all the code was unified thus a schema change was a single change to code and schema.
我们已经这样做了一段时间了（尽管没有symfony和doctrine，但问题仍然存在） - 我们从一个庞大的数据库开始，并在每个表中的每行实现一个“环境标识符”。这种模式迁移很简单：所有代码都是统一的，因此模式更改只是对代码和模式的单一更改。
This however lead to problems with speed (large tables), agility (moving/backuping etc large datasets is alot more intensive than alot of smaller ones) and of course more easily breakable environments since a single failure will pull down every dataset in the system...
然而，这会导致速度问题（大表），敏捷性（移动/备份等大型数据集比很多小型数据集更加密集），当然更容易破碎的环境，因为单个故障将拉低系统中的每个数据集。 ..
We then switched to multiple databases; each environment its own schema. By utilizing the migrations provided with Doctrine (1 in our case) we're able to quickly update the entire app or just a single environment. Also the ability to move specific changes between tentants allows for better precision in speed and optimization.
然后我们切换到多个数据库;每个环境都有自己的架构。通过利用Doctrine提供的迁移（在我们的例子中为1），我们能够快速更新整个应用程序或仅单个环境。此外，在帐篷之间移动特定变化的能力允许更快的速度和优化精度。
My advice would be: create a single core which is extended into the different tenants and keep the local customized database configuration per tentant. (in a app.ini-like structure)
我的建议是：创建一个扩展到不同租户的单个核心，并保留每个帐户的本地自定义数据库配置。 （在类似app.ini的结构中）
i.e.
即
/
apps/
tentant1/
models/ <--- specific to the tenant
libraries/ <--- specific to the tenant
config/
app.ini <--- specific configuration
tentant2/
/**/ etc
core/
models/ <--- system wide models
libraries/ <--- system wide libraries (i.e. doctrine)
core.ini <--- system wide configuration
This could keep everything organized. We are even going as far as having the comeplete structure of core available per tentant. Thus being able to override every aspect of the 'core' specific to the tenant.
这可以保持一切井井有条。我们甚至可以为每个帐篷提供核心的结构。因此能够覆盖特定于租户的“核心”的每个方面。
#2
3
We do this with one of our main solutions at work, and it's certainly possible. We use Symfony2's bundles to create a "base" bundle which is then extended by other per-client bundles.
我们使用我们的主要解决方案之一来做到这一点，这当然是可能的。我们使用Symfony2的bundle来创建一个“基础”包，然后由其他每个客户端包进行扩展。
That said, we're looking at moving away from doing things this way in the future. The decision to go multi-tenant was not the right one for us, as many of our clients are uncomfortable with their data being in the same database tables as everyone else's data. This is completely aside from the potential issues of slow performance as the tables grow.
也就是说，我们正在考虑在未来以这种方式做事。对于我们来说，多租户的决定并不合适，因为我们的许多客户对他们的数据与其他人的数据在同一个数据库表中感到不舒服。这完全不包括表格增长时性能缓慢的潜在问题。
We've also found that Doctrine 2 has some pretty serious issues unless it's kept well under control. Whilst this may be a side effect of poorly structured code and database logic, I feel it's a bit of a hole for an ORM to be able to get to the point where it throws a fatal error because it's used too much memory - particularly when the only reason it's using so much memory is because it's batching up SQL queries so that they can be made "more efficient".
我们还发现，Doctrine 2有一些相当严重的问题，除非它得到很好的控制。虽然这可能是结构不良的代码和数据库逻辑的副作用，但我觉得ORM能够达到导致致命错误的点是一个漏洞，因为它使用了太多的内存 - 特别是当唯一的原因是它使用了如此多的内存是因为它正在对SQL查询进行批处理，以便它们可以“更高效”。
This is purely my opinion, of course :) What doesn't work for us may well work for you, but I do feel you'd be better off keeping separate databases per-client, even if they're all stored on the same server.
这纯粹是我的观点，当然:)对我们来说不起作用可能对你有用，但我觉得你最好每个客户保留单独的数据库，即使它们都存储在同一个数据库中服务器。
#3
1
BEST builds different notices in different mind. Please be more specific to ask questions. One of the way to develop multi-tenant system is to put a common primary key in all the tables to build the relationship. The type and nature of the primary key is dependable project wise.
BEST以不同的思维构建不同的通知。请更具体地提问。开发多租户系统的方法之一是在所有表中放置一个公共主键来建立关系。主键的类型和性质是可靠的项目。
#4
1
Why not to try different databases for each client to keep the data separated and give them unique entry point to your app. Ex: http://client1.project.net which with the routing system will map to client1 database.The bad side of this: more complex database changes, because all databases for each client needs to be upgraded.
为什么不为每个客户端尝试不同的数据库来保持数据分离，并为它们提供唯一的入口点。例如：http：//client1.project.net，它与路由系统将映射到client1数据库。坏的一面是：更复杂的数据库更改，因为每个客户端的所有数据库都需要升级。
#5
0
I think that, To manage multi-tenant multi-database with symfony 2/3. We can config auto_mapping: false for ORM of doctrine. file: config.yml
我认为，要使用symfony 2/3管理多租户多数据库。我们可以为教条的ORM配置auto_mapping：false。 file：config.yml
doctrine:
dbal:
default_connection: master
connections:
master:
driver: pdo_mysql
host: '%master_database_host%'
port: '%master_database_port%'
dbname: '%master_database_name%'
user: '%master_database_user%'
password: '%master_database_password%'
charset: UTF8
tenant:
driver: pdo_mysql
host: '%tenant_database_host%'
port: '%tenant_database_port%'
dbname: '%tenant_database_name%'
user: '%tenant_database_user%'
password: '%tenant_database_password%'
charset: UTF8
orm:
default_entity_manager: master
auto_generate_proxy_classes: "%kernel.debug%"
entity_managers:
master:
connection: master
auto_mapping: false
mappings:
AppBundle:
type: yml
dir: Resources/master/config/doctrine
tenant:
connection: tenant
auto_mapping: false
mappings:
AppBundle:
type: yml
dir: Resources/tenant/config/doctrine
After that, we cannot handle connection of each tenant by override connection info in request_listener like article: http://mohdhallal.github.io/blog/2014/09/12/handling-multiple-entity-managers-in-doctrine-the-smart-way/ I hope that, this practice can help someone working with multi-tenant
之后，我们无法通过request_listener中的覆盖连接信息处理每个租户的连接，如文章：http：//mohdhallal.github.io/blog/2014/09/12/handling-multiple-entity-managers-in-doctrine-the -smart-way /我希望，这种做法可以帮助那些与多租户合作的人
Regards,
问候，
Vuong Nguyen
Vuong Nguyen
#6
-1
This is something I've been trying to figure out as well. Best I could come up with (not in an implementation yet, but in theory) is this: give each tenant their own database login and use views to keep them from seeing other people's data.
这也是我一直试图解决的问题。我能想出的最好（不是在实现中，但在理论上）是这样的：给每个租户他们自己的数据库登录并使用视图来阻止他们看到其他人的数据。
我跑过这个链接描述了一种方法，只为简单的旧MySQL（不是Symfony / Doctrine）。
Basically, you have your actual database tables, but each table has a column that stores the name of the database user that made the row. Views are then created that always filter by this column so whenever a user logs in to the database (via an admin tool or even connecting through Symfony/Doctrine), they are only ever returned records directly associated with them. This allows you to keep the data "separate", but still in one database. When pulling data (say for an entity in Symfony), you are pulling data from a filtered view vs. the actual database table.
基本上，您拥有实际的数据库表，但每个表都有一个列，用于存储创建该行的数据库用户的名称。然后创建始终按此列过滤的视图，以便每当用户登录数据库时（通过管理工具或甚至通过Symfony / Doctrine连接），它们只会返回与它们直接关联的记录。这允许您将数据“分开”，但仍然在一个数据库中。在提取数据时（例如，对于Symfony中的实体），您将从过滤后的视图中提取数据，而不是实际的数据库表。
Now, this solution isn't exactly Symfony/Doctrine friendly. I was able to get a very quick and rudimentary test of this running before; Doctrine was able to use the database views just fine (it could insert, edit, and delete entries from a view no problem). However, when doing things like creating/updating schema, it's not fun. Granted, Symfony/Doctrine seems pretty extensible, so I'm confident there is a way to have it automated, but this kind of setup isn't supported out-of-the-box. Doctrine would need to be told to update the tables, always append the column for holding the username to the entity tables it creates, update the views as well, etc. (You would also need a way to load up the proper database config in your Symfony app, mainly the different logins as the server and other stuff would be the same.) But, if this can be overcome, your app itself could run these multiple tenants completely "ignorant" of the fact that other people's data is sitting in the database.
现在，这个解决方案并不完全符合Symfony / Doctrine。我之前能够对这次跑步做一个非常快速和初步的测试; Doctrine能够很好地使用数据库视图（它可以插入，编辑和删除视图中的条目没问题）。但是，在创建/更新架构时，这并不好玩。当然，Symfony / Doctrine看起来很可扩展，所以我相信有一种方法可以实现自动化，但这种设置不支持开箱即用。需要告诉Doctrine更新表，总是将用于保存用户名的列附加到它创建的实体表中，同时更新视图等等。（您还需要一种方法来加载适当的数据库配置） Symfony应用程序，主要是作为服务器和其他东西的不同登录将是相同的。）但是，如果这可以克服，你的应用程序本身可以运行这些多个租户完全“无知”的事实，其他人的数据坐在数据库。
#1
6
We've been doing this for some time now (although not with symfony and doctrine but the problems remain the same) - we started with one huge database and implemented a 'environment identifier' per row in each of the tables. This way schema migrations were easy: all the code was unified thus a schema change was a single change to code and schema.
我们已经这样做了一段时间了（尽管没有symfony和doctrine，但问题仍然存在） - 我们从一个庞大的数据库开始，并在每个表中的每行实现一个“环境标识符”。这种模式迁移很简单：所有代码都是统一的，因此模式更改只是对代码和模式的单一更改。
This however lead to problems with speed (large tables), agility (moving/backuping etc large datasets is alot more intensive than alot of smaller ones) and of course more easily breakable environments since a single failure will pull down every dataset in the system...
然而，这会导致速度问题（大表），敏捷性（移动/备份等大型数据集比很多小型数据集更加密集），当然更容易破碎的环境，因为单个故障将拉低系统中的每个数据集。 ..
We then switched to multiple databases; each environment its own schema. By utilizing the migrations provided with Doctrine (1 in our case) we're able to quickly update the entire app or just a single environment. Also the ability to move specific changes between tentants allows for better precision in speed and optimization.
然后我们切换到多个数据库;每个环境都有自己的架构。通过利用Doctrine提供的迁移（在我们的例子中为1），我们能够快速更新整个应用程序或仅单个环境。此外，在帐篷之间移动特定变化的能力允许更快的速度和优化精度。
My advice would be: create a single core which is extended into the different tenants and keep the local customized database configuration per tentant. (in a app.ini-like structure)
我的建议是：创建一个扩展到不同租户的单个核心，并保留每个帐户的本地自定义数据库配置。 （在类似app.ini的结构中）
i.e.
即
/
apps/
tentant1/
models/ <--- specific to the tenant
libraries/ <--- specific to the tenant
config/
app.ini <--- specific configuration
tentant2/
/**/ etc
core/
models/ <--- system wide models
libraries/ <--- system wide libraries (i.e. doctrine)
core.ini <--- system wide configuration
This could keep everything organized. We are even going as far as having the comeplete structure of core available per tentant. Thus being able to override every aspect of the 'core' specific to the tenant.
这可以保持一切井井有条。我们甚至可以为每个帐篷提供核心的结构。因此能够覆盖特定于租户的“核心”的每个方面。
#2
3
We do this with one of our main solutions at work, and it's certainly possible. We use Symfony2's bundles to create a "base" bundle which is then extended by other per-client bundles.
我们使用我们的主要解决方案之一来做到这一点，这当然是可能的。我们使用Symfony2的bundle来创建一个“基础”包，然后由其他每个客户端包进行扩展。
That said, we're looking at moving away from doing things this way in the future. The decision to go multi-tenant was not the right one for us, as many of our clients are uncomfortable with their data being in the same database tables as everyone else's data. This is completely aside from the potential issues of slow performance as the tables grow.
也就是说，我们正在考虑在未来以这种方式做事。对于我们来说，多租户的决定并不合适，因为我们的许多客户对他们的数据与其他人的数据在同一个数据库表中感到不舒服。这完全不包括表格增长时性能缓慢的潜在问题。
We've also found that Doctrine 2 has some pretty serious issues unless it's kept well under control. Whilst this may be a side effect of poorly structured code and database logic, I feel it's a bit of a hole for an ORM to be able to get to the point where it throws a fatal error because it's used too much memory - particularly when the only reason it's using so much memory is because it's batching up SQL queries so that they can be made "more efficient".
我们还发现，Doctrine 2有一些相当严重的问题，除非它得到很好的控制。虽然这可能是结构不良的代码和数据库逻辑的副作用，但我觉得ORM能够达到导致致命错误的点是一个漏洞，因为它使用了太多的内存 - 特别是当唯一的原因是它使用了如此多的内存是因为它正在对SQL查询进行批处理，以便它们可以“更高效”。
This is purely my opinion, of course :) What doesn't work for us may well work for you, but I do feel you'd be better off keeping separate databases per-client, even if they're all stored on the same server.
这纯粹是我的观点，当然:)对我们来说不起作用可能对你有用，但我觉得你最好每个客户保留单独的数据库，即使它们都存储在同一个数据库中服务器。
#3
1
BEST builds different notices in different mind. Please be more specific to ask questions. One of the way to develop multi-tenant system is to put a common primary key in all the tables to build the relationship. The type and nature of the primary key is dependable project wise.
BEST以不同的思维构建不同的通知。请更具体地提问。开发多租户系统的方法之一是在所有表中放置一个公共主键来建立关系。主键的类型和性质是可靠的项目。
#4
1
Why not to try different databases for each client to keep the data separated and give them unique entry point to your app. Ex: http://client1.project.net which with the routing system will map to client1 database.The bad side of this: more complex database changes, because all databases for each client needs to be upgraded.
为什么不为每个客户端尝试不同的数据库来保持数据分离，并为它们提供唯一的入口点。例如：http：//client1.project.net，它与路由系统将映射到client1数据库。坏的一面是：更复杂的数据库更改，因为每个客户端的所有数据库都需要升级。
#5
0
I think that, To manage multi-tenant multi-database with symfony 2/3. We can config auto_mapping: false for ORM of doctrine. file: config.yml
我认为，要使用symfony 2/3管理多租户多数据库。我们可以为教条的ORM配置auto_mapping：false。 file：config.yml
doctrine:
dbal:
default_connection: master
connections:
master:
driver: pdo_mysql
host: '%master_database_host%'
port: '%master_database_port%'
dbname: '%master_database_name%'
user: '%master_database_user%'
password: '%master_database_password%'
charset: UTF8
tenant:
driver: pdo_mysql
host: '%tenant_database_host%'
port: '%tenant_database_port%'
dbname: '%tenant_database_name%'
user: '%tenant_database_user%'
password: '%tenant_database_password%'
charset: UTF8
orm:
default_entity_manager: master
auto_generate_proxy_classes: "%kernel.debug%"
entity_managers:
master:
connection: master
auto_mapping: false
mappings:
AppBundle:
type: yml
dir: Resources/master/config/doctrine
tenant:
connection: tenant
auto_mapping: false
mappings:
AppBundle:
type: yml
dir: Resources/tenant/config/doctrine
After that, we cannot handle connection of each tenant by override connection info in request_listener like article: http://mohdhallal.github.io/blog/2014/09/12/handling-multiple-entity-managers-in-doctrine-the-smart-way/ I hope that, this practice can help someone working with multi-tenant
之后，我们无法通过request_listener中的覆盖连接信息处理每个租户的连接，如文章：http：//mohdhallal.github.io/blog/2014/09/12/handling-multiple-entity-managers-in-doctrine-the -smart-way /我希望，这种做法可以帮助那些与多租户合作的人
Regards,
问候，
Vuong Nguyen
Vuong Nguyen
#6
-1
This is something I've been trying to figure out as well. Best I could come up with (not in an implementation yet, but in theory) is this: give each tenant their own database login and use views to keep them from seeing other people's data.
这也是我一直试图解决的问题。我能想出的最好（不是在实现中，但在理论上）是这样的：给每个租户他们自己的数据库登录并使用视图来阻止他们看到其他人的数据。
我跑过这个链接描述了一种方法，只为简单的旧MySQL（不是Symfony / Doctrine）。
Basically, you have your actual database tables, but each table has a column that stores the name of the database user that made the row. Views are then created that always filter by this column so whenever a user logs in to the database (via an admin tool or even connecting through Symfony/Doctrine), they are only ever returned records directly associated with them. This allows you to keep the data "separate", but still in one database. When pulling data (say for an entity in Symfony), you are pulling data from a filtered view vs. the actual database table.
基本上，您拥有实际的数据库表，但每个表都有一个列，用于存储创建该行的数据库用户的名称。然后创建始终按此列过滤的视图，以便每当用户登录数据库时（通过管理工具或甚至通过Symfony / Doctrine连接），它们只会返回与它们直接关联的记录。这允许您将数据“分开”，但仍然在一个数据库中。在提取数据时（例如，对于Symfony中的实体），您将从过滤后的视图中提取数据，而不是实际的数据库表。
Now, this solution isn't exactly Symfony/Doctrine friendly. I was able to get a very quick and rudimentary test of this running before; Doctrine was able to use the database views just fine (it could insert, edit, and delete entries from a view no problem). However, when doing things like creating/updating schema, it's not fun. Granted, Symfony/Doctrine seems pretty extensible, so I'm confident there is a way to have it automated, but this kind of setup isn't supported out-of-the-box. Doctrine would need to be told to update the tables, always append the column for holding the username to the entity tables it creates, update the views as well, etc. (You would also need a way to load up the proper database config in your Symfony app, mainly the different logins as the server and other stuff would be the same.) But, if this can be overcome, your app itself could run these multiple tenants completely "ignorant" of the fact that other people's data is sitting in the database.
现在，这个解决方案并不完全符合Symfony / Doctrine。我之前能够对这次跑步做一个非常快速和初步的测试; Doctrine能够很好地使用数据库视图（它可以插入，编辑和删除视图中的条目没问题）。但是，在创建/更新架构时，这并不好玩。当然，Symfony / Doctrine看起来很可扩展，所以我相信有一种方法可以实现自动化，但这种设置不支持开箱即用。需要告诉Doctrine更新表，总是将用于保存用户名的列附加到它创建的实体表中，同时更新视图等等。（您还需要一种方法来加载适当的数据库配置） Symfony应用程序，主要是作为服务器和其他东西的不同登录将是相同的。）但是，如果这可以克服，你的应用程序本身可以运行这些多个租户完全“无知”的事实，其他人的数据坐在数据库。