Basic SQL Data Objects
This section describes basic data objects relative to the PointBase RDBMS. It describes each data object individually and explains how PointBase data objects interact with one another. Read this chapter before creating a database to fully understand the behavior of each data object within the PointBase RDBMS.
Data Objects Within the PointBase RDBMS
The following diagram illustrates the relationship between basic data objects in the PointBase RDBMS. The database itself is a data object that encompasses all other data objects. A database contains Schema objects, which in turn contain Table objects. Tables whose values are derived from other tables are called Derived Tables or Views. Finally, a Column is located within a Table. Columns are the smallest data object within PointBase RDBMS.
Figure 1.1 PointBase RDBMS Data Objects
The PointBase RDBMS can contain one or more database(s). A database is at the highest level of abstraction and is simply an operating system file. PointBase stores all data in dbn files and all log information in wal files. For example, the sample database file is "sample.dbn" and the sample log file is "sample.wal." You can locate these files in the directory, <install directory>\pointbase\databases.
PointBase automatically creates other .dbn or .wal files like sample$$1.dbn or sample$$1.wal when a .dbn or .wal file reaches its maximum size. All automatically created .dbn and .wal files have the same page size as the original .dbn or .wal file.
Database Size Limit
For the default page size of 4 K, the database size is limited to 0.5 terabytes. If the default page size is 1 K, the database size is limited to 128 GB, and for the default page size of 32 K, the database is limited to 4 terra bytes.
Because PointBase supports multiple page sizes for a database, the previous limits are true assuming that the database does not use additional page sizes. If the database has more than one page size, the database size limit increases. For example, if the database has two different page sizes, one page size of 4K (0.5 terabytes), plus another page size of 32K (4 terabytes), the total database size limit is 4.5 terabytes.
PointBase supports multiple databases, but only one database concurrently. If multiple connections are made to the PointBase RDBMS, then each connection needs to access the same database. When the set of connections to a particular database is completed, then the next set of connections can be initiated to another database.
Typically, multiple databases separate data for different applications. Schemas can be used to accomplish the same effect. Refer to " Schemas" in this chapter for more information
Using PointBase, you may query a database on a CD. In this section we use the term "read-only database," when the database files are on a CD or, when the database files are set to the operating system property "read-only." PointBase supports only SELECT statements for read-only databases. Using any other statements, such as INSERT, CREATE TABLE,... etc. with a read-only database causes PointBase to throw an exception. The error message states "Invalid statement."
To have a database on a CD, you must first create the database on a writable drive. After creating the database, connect to it using the PointBase Commander or any java program [see PointBase System Guide], and then close the connection without performing any other operations during the connection.
By performing this step, you ensure that all the data is completely recovered from the log (.wal) before loading the .dbn and .wal files on a CD. You cannot recover data from a database on a CD. If the database on a CD needs recovery, the application terminates with the following message on the screen (standard system output): "Database needs recovery from log. This version does not support recovery."
To connect to the database on CD or any other location use the pointbase.ini file's "database.home" parameter or the java command line -D option to specify the location of the database. See the PointBase System Guide for more information about starting PointBase.
Operations that involve writing to the database (dbn) or log (wal) files are not allowed. Additionally, PointBase does not allow the following statements, because they use temporary tables and writes into the database.
Databases contain collections of users. Users are a means of providing security at schema level. Each schema has explicit user(s) associated with it, one of which must own the schema. The schema owner has full access to the schema and determines the access privileges of the other users. To manage users, use the CREATE USER and DROP USER SQL statements.
When you create a PointBase database using PointBase Commander, PointBase Console, or the JDBC API, the system creates a default user PUBLIC with the password PUBLIC who owns the default schema PUBLIC. The first time that you connect to the database you must connect as this user. You can then create additional users, schemas, and access privileges for subsequent connections.NOTE: You cannot connect to a database as a user who does not exist in the SYSUSERS table, which is one of the system tables in the POINTBASE schema. For a list of predefined system tables and their attributes within the POINTBASE schema, please refer to "Appendix A: System Tables" of the PointBase System guide.
Databases contain collections of independent schemas. A schema is a logical grouping of tables, indexes, triggers, routines, and other data objects under one qualifying name. Internationalization characteristics and user-level security can also be defined for schema objects.
When a database is created using PointBase Commander, PointBase Console, or the JDBC API, the PointBase RDBMS creates two schemas:
- An internal schema called POINTBASE in which the system keeps all of the system catalogs and tables
- A schema called PUBLIC, which is the default schema if no additional schemas are defined
The PointBase default user (user name: "PUBLIC" password: "PUBLIC") is the owner of this schema and has full access. Other users must be granted appropriate access privileges to use this schemaNOTE: You cannot create any user-defined data objects within the POINTBASE schema. For a list of predefined system tables and their attributes within the POINTBASE schema, please refer to "Appendix A: System Tables" of the PointBase System Guide.
Unless a different user is specified explicitly, the user who creates the schema automatically becomes the owner of that schema. The schema owner has full access privileges and must grant access privileges to other users for them to access that schema.
PointBase recommends that any new schemas should be created with the same name as the user who owns the schema. When this user accesses the database, PointBase will automatically make this schema the current schema. Data objects are mapped to the current schema by default, without the need for an explicit reference. The CURRENT_SCHEMA special register contains the name of the current schema. Please refer to the "PointBase SQL Scalar and Aggregate Functions" chapter in this guide for more information about the CURRENT_SCHEMA special register.
In databases with multiple schemas, data objects must explicitly reference the schema for which they are intended. If no explicit reference is made, PointBase automatically tries to associate the data object with the current schema. If the data object cannot be logically associated with the current schema, it references the default (PUBLIC) schema.
In databases with multiple schemas, when referencing a data object that is not in the current schema, you must append the schema name to the data object name, separated by a period. For example, if you have a schema named Employee_Info, which contains a table named Employees. Then, you must refer to that table in the following way:
To manage schemas, use the CREATE SCHEMA and DROP SCHEMA SQL statements. CREATE SCHEMA initially creates a schema and conversely, DROP SCHEMA drops a schema. The user that creates the schema owns the schema unless the optional AUTHORIZATION qualifier is used to specify another user. The schema owner can grant applicable privileges to the appropriate users.
A table comprises of a number of column objects and contains rows of data. A row is a nonempty sequence of values that correspond to the column objects in the table. Every row of the same table has the same number of columns and contains a value for every column of that table.
The following are two types of tables used in the PointBase RDBMS:
- Base Table - a table whose data is actually stored in the database.
- Derived Table - a table obtained from other tables directly or indirectly through the evaluation of a query expression.
Derived Tables or "Virtual Tables" are also known as Views. They provide an alternative way to look at the data of one or more tables. This virtual table or view derives its values from the evaluation of a query expression in a CREATE VIEW statement. The query expression can reference base tables, other views, aliases, etc. Essentially, a view is a stored SELECT statement, of which you can retrieve the results at a later time by querying the view as though it were a table. See also "CREATE VIEW" . A view can be read-only or updatable. Currently, PointBase supports Read-Only Views.
The definition of each view is stored in PointBase's system catalog SYSVIEWS. If no errors are encountered, PointBase adds the view name to the SYSVIEWS catalog table. Additionally, all referenced columns of all referenced tables will be added to the SYSVIEWTABLES catalog table.
Security for Views
Because a view is a type of table, you can grant privileges on it, and the privileges can be different than the privileges on any base table from which the view was derived. Unlike base tables, however, an owner of a view does not automatically have the authority to grant privileges on the view to others.
To grant privileges on the view to others, you must have grant privileges on every referenced column and table in the view's query expression. If you have privileges revoked on any of the referenced columns or tables, you also have the same privileges revoked on the view.
Revoking privileges on a view using the RESTRICT option will raise an error, if any users of that view had the grant option privilege and they granted that privilege to other users. If you revoke privileges on a view using the CASCADE option, you will revoke all the users' privileges on that view. Likewise, you must verify if the view has any dependent views, and verify the privileges on those as well.
Each PointBase table has a maximum limit of 32,000 columns and a minimum of one. All values contained within a specific column are of the same data type and every column has an associated default value. The system uses the default value when data is entered into a table without specifying a value for the column. The default value for a column is NULL unless the column specifies the NOT NULL constraint or a different default value.