Hi All,
I work with Marc Fournier, for those who don't know, and have been
working extensively with the schemas feature for Hub.Org's new Account
Management package. Each client's data is stored in a seperate schema
set asside just for them (though they will never have direct access to
it, it helps us "balance" out the data. Each schema has the same set of
tables, with which different data is stored, depending on the client.
I've run into two issues and I've been reading over the docs in
relation to schemas in the hopes that I could find a solution to my
problems. Here are the details of the issues:
1. The first issue I'm running with, is that if I have several schemas
with the following name:
public test_001 test_002 test_003
the test_* schemas have the same tables listed in the their name
spaces, such that:
Schema test_001 contains:
test_contact; test_domains; test_accounts; ...Schema test_002 contains:
test_contact; test_domains; test_accounts; ...
etc.
As you can see this is a nice, clean way to break down some datasets.
But, if I do:
set search_path to public, test_001, test_002;
I only get access to the tables in test_001 and public, the tables in
test_002 are not listed, and thus I do not see them on the screen while
doing a "\d".
2. Issue 2, is more a SQL issue then anything, but I'd like to be able
to do something to the affect of:
SELECT * FROM test_*.test_domains ORDER BY domain_name;
or better yet:
SELECT * from public.domain_summary ds LEFT JOIN test_*.test_domains td
ON (td.domain_id = ds.id);
This would mean I can run one query to get all of the information I
need, rather then having to run a query for each client that I have to
list domains for. If I've got 10,000 clients, that's going to take a ton
of time to get the results I'm looking for...
Does anyone know if any of these issues have been addressed in 7.4? I
see some references to schema based changes, but no details on what
those changes were. If not, are there any plans to do something like
that?
--
Chris Bowlby <excalibur@hub.org>
Hub.Org Networking Services
