Schemas partition databases. Tables, procedures, triggers, and so forth are all kept in schemas. Schemas are like sub-databases within a database. The salient difference between schemas and databases is that a single SQL statement can refer to objects in the different schemas of the parent database, but cannot refer to objects in other databases -- tables within a database can be related, but tables in different databases cannot. Gombe-MI uses schemas to partition each database into areas where users have a greater or lesser degree of freedom to make changes. For further information on schemas see the schema documentation for PostgreSQL.
Each database is divided into the same schemas. That is, each schema described below exists within each of the databases described here.
The system looks at the different schemas for objects, for example table names appearing in SQL queries, in the order in which the schemas are listed below. If the table does not appear in the first schema it looks in the second, and so forth. As soon as a table is found with the name given, that table is used and the search stops.
To explicitly reference an object in a specific schema, place the name of the schema in front of the object, separating the two with a period (e.g. schemaname.tablename).
The gombemi schema holds the “official” Gombe-MI tables. Everything in the gombemi schema is documented and supported.
In this schema the gombemi_readers and gombemi_editors have the access described above.
Gombe-MI contains a number of schemas that exist to simplify things for those interested only in particular portions of Gombe-MI. These schemas contain nothing but views that reference other parts of Gombe-MI (both tables and views), the parts that are especially relevant and useful to those interested only in one of the broad categories of Gombe-MI data. These schemas and their corresponding categories are:
| Schema | Category |
|---|---|
gombemi_support_views
|
Support Table Related |
gombemi_demography_views
|
Demography Related |
gombemi_follow_views
|
Follow Mechanics Related |
gombemi_event_views
|
Follow Event Related |
gombemi_distance_views
|
Follow Distance Related |
gombemi_group_views
|
Group Related |
These schemas provide an overview of the major areas of Gombe-MI. They should be especially useful to those starting out with Gombe-MI or those interested only in particular portions of Gombe-MI data.
The views in these schemas may only be queried. Any
updating of Gombe-MI data must be done in the
gombemi schema.
Some of Gombe-MI's tables and views appear in more than one of these schemas, some in none.
Do not create any views that reference the views in
these schemas. Reference the gombemi schema
instead. Any views created that reference anything in these
“category schemas” will be destroyed on
occasion as Gombe-MI is modified.
The sandbox schema holds tables that are used together with the “official” Gombe-MI tables but have not yet made it into the Gombe-MI project. They will not be documented in the Gombe-MI documentation.
The shared roles have the following permissions:
The gombemi_readers have all the permissions in the sandbox schema that the gombemi_editors have in the gombemi schema. They may add, delete, or modify any information in the schema but may not alter the structure of the schema by adding or removing tables, procedures, triggers, or anything else.
The gombemi_editors have all the permissions of the gombemi_readers, plus they may add or delete tables, stored procedures, or any other sort of object necessary to control the structure of the data.
Because of the schema search order the schema name must be used to qualify anything created in the sandbox schema. E.g.
Example 1.1. Creating table foo in the sandbox
schema
CREATE TABLE sandbox.foo (somecolumn INTEGER);
PostgreSQL, the database underlying Gombe-MI, is secure by default. This means that any tables or other database objects cannot be accessed by anyone but their creator without permission of the creator. gombemi_editors who create tables in the sandbox schema should use the GRANT statement to grant access to Gombe-MI's other users.
This is done as follows:
Example 1.2. Granting permission to table
foo in the sandbox schema
GRANT ALL ON sandbox.foo TO ROLE gombemi_editors;
GRANT SELECT ON sandbox.foo TO ROLE gombemi_readers;
There is one other issue. Only the creator of a table can change its structure -- to add another column, change the table name, etc. And only the creator can destroy (DROP) the table.
The devel schema holds tables undergoing integration into Gombe-MI. Normally it is empty, but during the design and development of new tables it may contain the tables being developed.
The tables in this schema do not necessarily contain valid or finalized data and so are not expected to be used for other than developmental purposes.
Permissions are granted in the devel schema on the same basis as the granting of permissions in the gombemi schema.
The difference between this schema and the sandbox schema is that the development tools support the creation and modification of the tables in the devel schema, which facilitates the movement of tables from the devel schema into the gombemi schema.
Each user has her own schema, a schema named with the user's login. Users have permissions to do anything they want in their own schemas, and no permissions whatsoever to anybody else's schema. A user's schema is private.
Users are not encouraged to grant others permissions to the tables in their schema, as shown in the Section: “The sandbox schema” above. A user's schema is deleted when she leaves Gombe-MI. All shared tables belong in the sandbox schema where they can be maintained without regard to personnel changes.
Because of the schema search order the schema name must be used to qualify anything created in the user's schema. E.g.
Example 1.3. Creating table foo in user
mylogin's schema
CREATE TABLE mylogin.foo (somecolumn INTEGER);