Schemas

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. Babase 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 babase schema

The babase schema holds the official Babase tables. Everything in the babase schema is documented and supported.

In this schema the babase_readers and babase_editors have the access described above.

The babase_something_views schemas

Babase contains a number of schemas that exist to simplify things for those interested only in particular portions of Babase. These schemas contain nothing but views that reference other parts of Babase, the parts that are especially relevant and useful to those interested only in one of the broad categories of Babase data. These schemas and their corresponding categories are:

The categories of Babase data and their schemas
Schema Category
babase_cycles_views Sexual Cycles
babase_darting_views Darting
babase_demog_views Group Membership and Life Events
babase_physical_traits_views Physical Traits
babase_social_views Social and Multiparty Interactions
babase_support_views Support Tables
babase_swerb_views SWERB Data (Group-level Geolocation Data)
babase_weather_views Weather Data
babase_group_views Views Which Add Gid To Tables

These schemas provide an overview of the major areas of Babase. They should be especially useful to those starting out with Babase or those interested only in particular portions of Babase data.

The views in these schemas may only be queried. Any updating of Babase data must be done in the babase schema.

Note

Some of Babase's tables and views appear in more than one of these schemas, some in none.

Warning

Do not create any views that reference the views in these schemas. Reference the babase schema instead. Any views created that reference anything in these category schemas will be destroyed on occasion as Babase is modified.

The babase_history schema

The babase_history schema contains a table for each temporal table in the babase schema. The tables in this schema store the "old" versions of data from those temporal tables, allowing the ability to query for earlier versions of the data. See the Temporal Tables and babase_history appendix for more details.

The name of each table in this schema should be a concatenation of 1) the name of the related babase schema table, and 2) "_HISTORY". For example, a table in the babase schema called SOMETABLE would have a table in the babase_history schema called SOMETABLE_HISTORY.

Group permissions in the babase_history schema

Members of the babase_readers and babase_editors groups both have the same permissions in the babase_history schema: they have read access to the data but cannot perform INSERT, UPDATE, or DELETE commands in any tables[19], nor can they add new tables to the schema. Only administrators are allowed to perform these actions.

The babase_pending schema

The babase_pending schema holds tables pending planned integration into Babase. The tables in this schema are intended to be used with the official Babase tables but, unlike the official Babase tables, there is no automated validation process and the table structure has not been thoroughly reviewed. The tables in babase_pending are to be used but their content and structure may change when officially incorporated into Babase.

Documentation on the content of the babase_pending schema may be found on the babase_pending page of the Babase Wiki.

The difference between this schema and the sandbox schema is in the permissions granted.

babase_readers permissions in the babase_pending schema

Members of the babase_readers group have the same permissions they do in the babase schema, they have read access to the data but cannot add, delete or modify it. However, unlike in the babase schema, individual users may be granted the right to add, delete, or change data on a table-by-table basis.

babase_editors permissions in the babase_pending schema

Members of the babase_editors group have the permissions they normally have in the babase schema, they may add, delete or modify all data in the schema's tables.

The sandbox schema

The sandbox schema holds tables that are used together with the official Babase tables but have not yet made it into the Babase project. They will not be documented in the Babase documentation.

The groups have the following permissions:

babase_readers permissions in the sandbox schema

The babase_readers have all the permissions in the sandbox schema that the babase_editors have in the babase 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.

babase_editors permissions in the sandbox schema

The babase_editors have all the permissions of the babase_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 2.1. Creating table foo in the sandbox schema


CREATE TABLE sandbox.foo (somecolumn INTEGER);
              


PostgreSQL, the database underlying Babase, 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. Babase_editors who create tables in the sandbox schema should use the GRANT statement to grant access to Babase's other users.

This is done as follows:

Example 2.2. Granting permission to table foo in the sandbox schema


GRANT ALL ON sandbox.foo TO GROUP babase_editors;
GRANT SELECT ON sandbox.foo TO GROUP babase_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

The devel schema holds tables undergoing integration into Babase. 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 babase 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 babase schema.

The per-user schemas

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.

Caution

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 Babase. 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 2.3. Creating table foo in user mylogin's schema


CREATE TABLE mylogin.foo (somecolumn INTEGER);
            




[19] There is one exception to this rule. Members of the babase_editors group actually may insert data to these tables, but only when it is done automatically as part of an UPDATE or DELETE in a The babase schema table.


Page generated: 2024-03-06T15:02:19-05:00.