Transcript PostgreSQL ORDBMS mod

PostgreSQL ORDBMS
Server Hierarchy
Managing Databases
• create database name;
• createdb name
• At the time of initialisation of Data Storage area
by initdb command following database are created
postgres
template1
template0
• datistemplate
• Datisallowconn
• Select * from pg_database
• drop database name;
• dropdb name
tablespaces
• Locations in the file system for database objects
– Initial volume (where the database clustered was
initially put) may run out of space
– Performance (heavily used index may be placed on fast
disk)
• create tablespace fastspace location
‘/mnt/sda1/postgresql/data’
• (location must be an empty directory owned by
postgres)
• Grant Create Privilege to others for creating
objects(tables, indexes or even a database)
• set default_tablespace = fastspace;
tablespaces
• tablespaces associated with a database store
– System catalogs
– Temporary files created by the server
– Tables and indexes create without tablespace clause
• initdb command creates following tablespaces
– pg_global (used for shared system catalogs)
– pg_default (default tablespace for template1 and
template0 and hence the default tablespace for other
databases also, unless overridden by tablespace clause
in create database command
• drop tablespace name; (from an empty tablespace)
Schema
• a database contains, one or more named schemas,
which in turn may contain, one or more named
database objects like tables, functions, types etc.
– server.database.schema.table
– To allow many users to connect without interfering with
each other
– To organize database objects into logical groups to
make them more meaningful
– Third party applications may be put in different
schemas so that they do not collide with the names of
other objects.
– Schemas are analogous to directories at OS level,
except Schemas can not be nested.
Schemas contd…
•
•
•
•
•
create schema name;
create table schemaname.tablename (..)
drop schema name; (for an empty schema)
drop schema name cascade;
create schema schemaname authorization username;
Public Schema
• Every new database contains public schema
• Tables created without specifying the
schema name are put into public schema
Schema Search Path
• unqualified object names (e.g. tablename only) are
determined by following a search path, which is a
list of schemas to look in
• The first matching table in the search path is taken
to be the one which is wanted.
• If no match found in the search path an error is
raised, even if the matching name exists in some
other schema
• The first schema named in the search path is called
the current schema, where new tables will be
created (if schema name is not specified in the
create table statement)
• show search_path will show “$user”,public
Schemas and Privileges
• By default users can not access objects in
schemas. To allow owner can give usage
privilege on the schema. To allow users to
make use of the objects in the schema,
additional privilege may need to be granted,
as appropriate for the object
• create privilege is required for creating
objects in that schema.
• By default every user has create and usage
privileges on public schema.
System Catalog Schema
• In addition to public and user-created
schemas, each database contains a
pg_catalog schema, which contains the
system tables and all the built-in data types,
functions, and operators. pg_catalog is
always effectively part of the search path.
• This ensures all the built-in names will be
found.
Usage patterns
• If no schemas exists then all the users will be
working in public schema implicitly. This setup is
mainly recommended when there is only a single
user or a few cooperating users in a database.
• Create schema for each user with the same name
as that user. Default search path starts with $user,
which resolves to the user name. Therefore, if
each user has a separate schema, they access their
own schemas by default.
• For this setup, revoke access to the public schema
(or drop it altogether), so users are truly
constrained to their own schemas.
Usage patterns contd…
• To install shared applications (tables to be used by
everyone, additional functions provided by third
parties, etc.), put them into separate schemas.
Remember to grant appropriate privileges to allow
the other users to access them. Users can then
refer to these additional objects by qualifying the
names with a schema name, or they can put the
additional schemas into their search path, as they
choose.
PostgreSQL is extensible
• PostgreSQL is extensible because its operation is
catalog-driven
• One key difference between PostgreSQL and
standard relational database systems is that
PostgreSQL stores much more information in its
catalogs: not only information about tables and
columns, but also information about data types,
functions, access methods
• These tables can be modified by the user, and
since PostgreSQL bases its operation on these
tables, this means that PostgreSQL can be
extended by users.
PostgreSQL is extensible
• By comparison, conventional database systems
can only be extended by changing hardcoded
procedures in the source code or by loading
modules specially written by the DBMS vendor.
• The PostgreSQL server can moreover incorporate
user-written code into itself through dynamic
loading. That is, the user can specify an object
code file (e.g., a shared library) that implements a
new type or function, and PostgreSQL will load it
as required.
• This ability to modify its operation “on the fly”
makes PostgreSQL uniquely suited for rapid
prototyping of new applications and storage
structures.
Numeric DataTypes
Character Data Types
Character Data Types
Date/Time Data Types
Geometric Data Types
Network Address Data Types
Complex Types
A composite type describes the structure of a row or
record; it is in essence just a list of field names and
their data types. PostgreSQL allows values of
composite types to be used in many of the same ways
that simple types can be used. For example, a column
of a table can be declared to be of a composite type.
Complex Types
• create type inventory_item as (
name text,
supplier_id integer,
price numeric);
• create table on_hand (item inventory_item, count int);
• insert into on_hand values ( row('fuzzy dice',42,1.92),10000);
• create function price_extension(inventory_item, integer) returns numeric as
'select $1.price * $2' language 'SQL';
• select * from on_hand;
• select price_extension(item, 10) from on_hand;
Inheritance
• Inheritance allows a table to inherit some of its
column attributes from one or more other tables,
creating a parent-child relationship. This causes
the child table to have each of the same columns
and constraints as its inherited table (or tables), as
well as its own defined columns.
• create table childtable definition inherits
( parenttable [, ...] )
Inheritance
booktown=# CREATE TABLE distinguished_authors (award text)
booktown-#
INHERITS (authors);
CREATE
booktown=# \d distinguished_authors
Table "distinguished_authors"
Attribute
| Type | Modifier
---------------------+---------+---------id
| integer | not null
last_name
| text
|
first_name
| text
|
award
|text
|
Inheritance
booktown=# INSERT INTO distinguished_authors
booktown-#
VALUES (nextval('author_ids'),
booktown(#
'Simon', 'Neil', 'Pulitzer Prize');
INSERT 3629421 1
booktown=# SELECT * FROM distinguished_authors
booktown-#
WHERE last_name = 'Simon';
id | last_name | first_name | award
-------+-----------+------------+---------------25043 | Simon | Neil
| Pulitzer Prize
(1 row)
booktown=# SELECT * FROM authors WHERE last_name = 'Simon';
id | last_name | first_name
-------+-----------+-----------25043 | Simon | Neil(1 row)
booktown=# SELECT * FROM ONLY authors WHERE last_name = 'Simon';
id | last_name | first_name
----+-----------+-----------(0 rows)
Arrays
• PostgreSQL supports non-atomic values in
individual table columns through data
constructs called arrays. An array itself is
not a data type, but an extension of any
PostgreSQL data type.
Arrays
•
•
create table favorite_books (employee_id integer, books text[]);
insert into favorite_books values (102,
'{"The Hitchhiker\'s Guide to the Galaxy"}');
• insert into favorite_books values (103,
'{"The Hobbit", "Kitten, Squared"}');
• select books from favorite_books;
books
----------------------------------------------------{"The Hitchhiker's Guide to the Galaxy"}
{"The Hobbit","Kitten, Squared"}
(2 rows)
• select books[1] from favorite_books;
books
-------------------------------------The Hitchhiker's Guide to the Galaxy
The Hobbit
(2 rows)
•
select books[1:2] from favorite_books;
Arrays
• create table favorite_authors (employee_id integer, authors_and_titles text[][]);
• insert into favorite_authors values (102,
'{{"Sidney Sheildon","Otherside of Midnight"},
{"Charles Dickens","Great Expectations" },
{"Prem Chand","Gaban"}}');
• select authors_and_titles[1][1] AS author, authors_and_titles[1][2] as title
from favorite_authors;
author
|
title
----------------------------------------J.R.R. Tolkien | The Silmarillion
(1 row)
Function Overloading
• More than one function may be defined
with the same SQL name, so long as the
arguments they take are different. In other
words, function names can be overloaded.
When a query is executed, the server will
determine which function to call from the
data types and the number of the provided
arguments.
Operator Overloading
• Operators may become overloaded in much the
same way as functions. This means that an
operator is created with the same name as an
existing operator, but affects a different set of
defined types. More than one operator may have
the same name, although two operators may not
share the same name if they accept the same
argument definitions. As long as a function exists
to accept the number and type of arguments
implied by the type of operator defined, though,
the operator may be overloaded.
Access to PostgreSQL
• PGAdmin III – GUI based client
• Psql - command based client