Re: Installing LedgerSMB into a schema?

["Chris Travers" <..hidden..>]

On 11/6/06, David Tangye <..hidden..> wrote:
> On Mon, 2006-11-06 at 00:25 -0600, Charley Tiggs wrote:
> > Is it possible to install LedgerSMB into a schema?
>
> I am a bit confused by this question and the discussion of the past
> hour.
>
> LSMB IS in a schema surely: the 'public' schema.
I think the discussions of the past bit have been about what parts of
the applications should be put in which schemas.  Sorry if that has
been lost.

Questions include:
1)  Should multiple companies be multiple schemas in one db or in
multiple databases?
2)  Shoud aspects of the application be moved into separate schemas?

This being said, I am going to talk about what I think would be
required to get LedgerSMB working in a LedgerSMB schema along with
other apps.

I think it should be possible using sed or an equivalent to add a
schema declaration to the create table clauses.  Something like sed
's/create table /CREATE TABLE myschema./i' Pg-database.sql.  Then as
long as myschema is in your search path.....  Note that this works
best if you only have one db user used here and the schema name is the
same as the username.  So at the moment, this possibly creates some
security issues.

I would be happy to provide technical guidance to make this happen for
you.  Hopefully that experiment will help us understand what is
necessary to make this scenario more supportable.

> I presume that the discussion is about having sets of accounts in:
>
>  - the public schema of separate databases/datasets as at present,
> and/or
>  - separate schemas of one database, presumably named after the set of
> accounts they contain.
>
> ... and either/both being made available, eg with reporting/stored
> functions in just one schema no matter how many sets of accounts a user
> might create.
>
> I am sure Josh has a good idea of the ramifications of having to address
> tables as [schema].[table].

This shouldn't be necessary.  We should be able to do all sort of cool
wizardry to prevent this from becoming a problem (such as setting a
search path :-) ).

> The issue of combining sets of accounts via views/joins across
> schemas/sets of accounts sounds interesting, but I wonder if this is
> really of any use to 99% of users; vs the idea that a database is a
> discretely secure container of each company/client's sets of accounts.
There is also a question of whether 99% of users will have
multi-company setups...

Best Wishes,
Chris Travers