I regret not having built schemas into DSRI in 1983. It should have
been obvious that a multi-level qualified name space would be a
generally useful feature, but the DEC convention was to qualify names
with prefixes (e.g. RDB$). My subsequent databases have all had
schemas. There is an argument that name space qualification should be
arbitrarily deep, but two levels seem to do the trick.

My schema have been mostly pure namespace, with a single exception: In
Netfrastructure, a schema has sequence increment and system id to aid in
cluster wide unique sequences.

The great win for schemas is the ability to support multiple instances
of an application within a single physical database. A secondary
advantage is that schemas get system tables out of the user's namespace.

In Netrfrastructure, I implemented application control over a name
resolution search list, allowing schema names to pushed and popped. The
feature was rarely used.

MySQL, incidentally, has made a total mish-mash of schemas and
databases. A database in MySQL is nothing but a directory in which
table definitions reside, and a schema is nothing but a synonym for
database. There have been any number of attempts to infer
characteristics to databases / schemas, most ineffective.

The issue in adding schemas to Firebird is managing the name space of
the system tables. There are probably many workable solutions ranging
from special case hacks to links to a semi-magic "null" schema. Lots of
room for architecture there, but be careful of designing in public
nuisance subject to abuse.


daltoncalford wrote:

> We use Schema's, in a variety of databases. (MS,Oracle,DB2, Pervasive)
>
> 1.) Sage Accpac uses schemas to group together a variety of different companies under a single database, with a common set of users and preferences (including their rights). This allows for consolidated corporate queries between different databases while client software points to a single schema for their own access. This is just one type of application where you have the same data/table structures within the same database but not holding the same data - you could do the same thing with a filter column and include that column as criteria for every query but schemas are simpler, and can have different security while the filtering column example would not have any security unless the client app or dba writes some sort of extra, non-standard security mechinism.
>
> 2.) When working on large databases, you may need to perform a short term OLAP study. If you do this outside of a schema, it is easy to have problems with after the fact cleanup. For example, the developer builds the processes/tables etc and uses names that are logical to them. unfortunately they are close in name to other processes/tables that perform other tasks (the infamous "foo" table or "temp" table that soon becomes "foo392" and "temp_for_john_072009") as the specification is for short term reports, the result is the developer does not spend much time in engineering the task. Going in after the fact and trying to figure out what is active, what is static and what is garbage becomes a logistical nightmare. Especially when you have multiple offices and the politics that goes with it. The new features in 2.5 for remote database access helps, but, the best solution would be to have schemas that can be pointed to remote databases. You define the schema and it can be local to the current database or pointing to a schema on a remote database/server. This would be very helpful in large databases where historical data is spread across multiple locations or when you have the problem with data being duplicated in different databases due to the lack of cross database queries.
>
> 3.) Schemas extend the life of firebirds 32 Char limitation to object names. You could now segment/organize your database in a more hierachal fashion and know what data /domains/procedures etc belong together (As you can see, I suggest that all database objects support the schema extensions).
>
> 4.) Many schools use Schemas to separate out the work of one student from another. It allows for many developers to be granted access to one physical database while not having them worry about the actions of any other developer.
>
> These are just the off the top of my head answers, there are many more reasons, but I have yet to have my coffee.
>
> best regards
>
> Dalton.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> --- In Firebird-Architect@yahoogroups.com, "Claudio Valderrama C." <cvalde@...> wrote:
>
>> Hello.
>> Not sure this is the right time to post the subject, but since I was already
>> writing a letter when someone mentioned schemas in fb-devel...
>>
>> Our parser treats "schema" as synonym for "database" for historical reasons.
>> Not having read the standard regarding schemas but some examples in the net,
>> I have a rough idea:
>>
>> - a db is divided into schemas
>> - there's a default, implicit schema (that may or may not be mentioned
>> explicitly)
>> - the catalog (system tables) lives in its own schema (don't know how
>> relevant it's, but I found it in other products)
>> - a schema can have security (and new privileges are needed regarding the
>> right to execute DDL, anyway)
>> - a schema can change its owner
>> - objects can be moved between schemas (what a mess)
>> - relationships should work between objects in different schemas
>> - dropping a schema gets rid of all objects in it (but cannot be done if
>> another schema references some object there, of course)
>> - a schema is a logical partition and has nothing to do with object
>> placement techniques like "tablespaces"
>>
>> Now, who sees schemas as an urgent need and why?
>>
>> C.
>> ---
>> Claudio Valderrama C. - www.cvalde.net
>> Consultant, SW developer.
>>
>>
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
>

--
Jim Starkey
Founder, NimbusDB, Inc.
978 526-1376