# 1.11 postgreSQL "schemas"

## Things can get messy

Over time, your instance of postgreSQL for this course will accumulate many tables,
such as the ones about a university that we've been using in class,
the ones about an HR department we used in a prep, and the one about
world countries that we used in another prep.
We can end up with many, many tables, all lumped together,
and this can cause name conflicts.
For example, there can only be one table named `Employee`.
If we've already defined one for the company HR data and then later decide we want one for
the university data,
we are forced to pick a new name.
The need to have names that are completely unique across all our domains 
leads to cumbersome names.

We can avoid these clashes -- and make things more organized --
by groupig the names into separate sets or "namespaces".
These namespaces are called SQL schemas.


## Creating a namespace using dot notation

We can simply use dot notation to create a new namespace.
For example, instead of defining a new table like this:

```SQL
create table Employee ...
```

we can write:

```SQL
create table HR.Employee ...
```

This allows us later to define a table called `University.Employee` -- the names are not in conflict.
And if we define everything to do with the university data using the `University` prefix
and everything about the company HR data using the `HR` prefix,
this makes a logical separation and reduces cognitive load.

In that case, when we want to refer to a table or other database element 
(e.g., a user-defined type or named constraint)
we have to use its *full* name.
For example:

```SQL
csc343h-prof=> select * from Employee;
ERROR:  relation "employee" does not exist
LINE 1: select * from Employee;
                      ^
csc343h-prof=> select * from HR.Employee;
 eid |      name       | salary | dept 
-----+-----------------+--------+------
   1 | Bill Gates      |     59 |   55
   2 | Marissa Mayer   |     82 |   55
   3 | Sheryl Sandberg |     17 |   55
   4 | Larry Ellison   |     55 |   33
   5 | Tim Cook        |     48 |   55
   6 | Mark Zuckerberg |     95 |   22
   7 | Jeff Weiner     |     28 |   33
   8 | Larry Page      |    145 |   33
   9 | Diane Horton    |    145 |   33
(9 rows)
```

## Creating a namespace using `CREATE SCHEMA`

Having to give the full name every time you refer to a table or other database element
can be tedious.

There is a useful analogy to ordinary conversations, 
where we often refer to a person without using their full name.
A first name alone can suffice
if we take advantage of what we know about the context.
For instance, if I am talking with friends about my job and I mention "Peter",
they infer that I'm referring to the Peter that I work with;
if I'm talking about Thanksgiving and mention "Peter", 
they infer that it's the Peter in my family.

postgreSQL provides a way to define different contexts and to say which one we are "in"
at a give time.
These contexts are called **schemas**.
Here's an example of how to use a schema:

```SQL
-- Define a new schema called University:
create schema University;
-- Tell postgres that every new name we define and every name we refer to
-- is in the University schema.
set search_path to University;
-- Because we set the search path to University, this new table is part 
-- of the University schema.
create table Student(
	sID integer primary key,
	firstName varchar(15) not null,
	surName varchar(15) not null,
	campus Campus,
	email varchar(25),
	cgpa CGPA);
-- And because we set the search path to University, when we refer to a
-- table, psql infers we are talking about a table in the University scheme.
-- So we can refer to this table without having to use a prefix:
select * from Student;
-- We can still use the full, prefixed name for our table, we just don't 
-- have to:
select * from University.Student;
```

## The search path

We used the keywords `set search_path to` in order to say
what schema we wanted to be working in.
More generally, we can give a *list* of schemas, for instance

```SQL
set search_path to University, HR, World;
```

This tells postgreSQL that whenever we refer to a name `X`, 
it should first look in the University schema for it
(that is, look for `University.X`),
and if there is no such thing, then look in the HR schema
(that is, look for `HR.X`),
and if there is no such thing, then look in the World schema
(that is, look for `World.X`).
If it never finds X in any of those schemas, an error is generated.

```SQL
csc343h-prof=> set search_path to University, HR, World;
SET
csc343h-prof=> select * from Frisbee;
ERROR:  relation "Frisbee" does not exist
LINE 1: select * from Frisbee;
                      ^
```

Any new thing we define will go into the first schema on the list, 
in this case, the `University` schema.

You can ask to see the search path:

```SQL
csc343h-prof=> show search_path;
      search_path      
-----------------------
 university, hr, world
(1 row)
```


## The default search path


If you don't set the search path, it gets a default value:

```SQL
cdf> psql csc343h-prof
psql (16.11 (Ubuntu 16.11-0ubuntu0.24.04.1))
Type "help" for help.

csc343h-prof=> show search_path;
   search_path   
-----------------
 "$user", public
(1 row)
```

What are `"$user"` and `public`, and where did they come from?
The schema called `public` is defined automatically.
The `$user` at the front of the search path is special;
it is not actually a schema.
Instead it is a placeholder showing that,
*if* you create a schema with the same name as your username (in my case `prof`) --
it is not created automatically for you --
that schema will go at the front of the search path.[^1]


This means that if we start up postgreSQL and immediately create a new table called `Frindle`,
it goes in the schema with the same name as your username, if you have created that schema.
If you haven't,
the new table goes into the `public` namespace and its full name is `public.Frindle`.
(Earlier, we said that 
any new thing we define will go into the first schema on the search path list
but 
the rule is a tiny bit more complicated:
of all the schemas on the search path,
any new thing goes into the first of these schemas that has been defined.)

Note that, on our database servers, 
you don't have permission to create things in the public schema.
so this sequence of actions generates an error:

```SQL
cdf> psql csc343h-prof
psql (16.11 (Ubuntu 16.11-0ubuntu0.24.04.1))
Type "help" for help.
csc343h-prof=> -- Schema prof has not been defined, so Frindle should go into
csc343h-prof=> -- the public schema.
csc343h-prof=> create table Frindle (num int);
ERROR:  permission denied for schema public
LINE 1: create table Frindle (num int);
                     ^
```

The solution is to create a schema and set the search_path, 
so that `Frindle` can go into that schema.



## A handy three-line pattern

To remove a schema, we can simply say `drop schema`, however, this generates errors
if the schema contains anything:

```SQL
csc343h-prof=> drop schema university;
ERROR:  cannot drop schema university because other objects depend on it
DETAIL:  type university.campus depends on schema university
type university.cgpa depends on schema university
type university.department depends on schema university
type university.grade depends on schema university
table university.course depends on schema university
table university.offering depends on schema university
table university.student depends on schema university
table university.took depends on schema university
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
```

As the hint suggests, we can use `CASCADE` to ask for everything defined within the schema
to be deleted before the schema itself is deleted:

```SQL
csc343h-prof=> -- Adding CASCADE:
csc343h-prof=> drop schema university cascade;
NOTICE:  drop cascades to 9 other objects
DETAIL:  drop cascades to type university.campus
drop cascades to type university.cgpa
drop cascades to type university.department
drop cascades to type university.grade
drop cascades to table university.course
drop cascades to table university.offering
drop cascades to table university.student
drop cascades to table university.took
drop cascades to table university.flooper
DROP SCHEMA
csc343h-prof=> -- Now Took (and everything else) is gone:
csc343h-prof=> select * from Took;
ERROR:  relation "Took" does not exist
LINE 1: select * from took;
                      ^
csc343h-prof=> We won't find it using its (former) full name either:
csc343h-prof=> select * from University.Took;
ERROR:  relation "university.Took" does not exist
LINE 1: select * from University.Took;
                      ^
```

Dropping a schema is 
a great way to clean up if you want to redefine your schema and load your data anew.
So it can be a good thing to put at the top of a DDL file.
However, the first time you import that file it will generate an error, because the schema
has never been defined!
This is solved by saying to drop the schema, but only if it has been defined:

```SQL
drop schema if exists University cascade;
```

A handy pattern for the top of a DDL file is to (1) clean up by dropping the schema if it exists,
(2) define the schema anew, and then (3) set the search path to that schema so that all definitions
that follow will go into that schema.
This is the full pattern:

```SQL
drop schema if exists University cascade;
create schema University;
set search_path to University;
```

## Your tables are not lost

Whatever you do in your database stays in your database
even after you exit psql. 
However, you might think your tables have been lost
if you forget about schemas. 
Here's an example:

```SQL
cdf> psql csc343h-prof
psql (16.11 (Ubuntu 16.11-0ubuntu0.24.04.1))
Type "help" for help.

csc343h-prof=> -- Here we create a schema and set the search path to it.
csc343h-prof=> create schema atlantis;
CREATE SCHEMA
csc343h-prof=> set search_path to atlantis;
SET
csc343h-prof=> -- Let's make a table and populate it.
csc343h-prof=> create table people(name text);
CREATE TABLE
csc343h-prof=> insert into people values ('Critias'), ('Socrates');
INSERT 0 2
csc343h-prof=> -- The table is here and all is well.
csc343h-prof=> select * from people;
   name   
----------
 Critias
 Socrates
(2 rows)

csc343h-prof=> -- Let's leave psql and return to it, and see if our table survives.
csc343h-prof=> \q
cdf> psql csc343h-prof
psql (16.11 (Ubuntu 16.11-0ubuntu0.24.04.1))
Type "help" for help.

csc343h-prof=> -- It's gone!!
csc343h-prof=> select * from people;
ERROR:  relation "people" does not exist
LINE 1: select * from people;
                      ^
csc343h-prof=> -- What about the persistence of data?
csc343h-prof=> -- Isn't that one of the key things a relational DBMS is 
csc343h-prof=> -- supposed to ensure???
csc343h-prof=> -- Yes it is, and in fact we do have persistence.  We just
csc343h-prof=> -- have to remember that, since the we had set our search_path
csc343h-prof=> -- to atlantis before creating the table, the name of the table
csc343h-prof=> -- is atlantis.people.
csc343h-prof=> select * from atlantis.people;
   name   
----------
 Critias
 Socrates
(2 rows)

csc343h-prof=> -- We can spare ourselves having to completely specify 
csc343h-prof=> -- the name by setting our search path again.
csc343h-prof=> set search_path to atlantis;
SET
csc343h-prof=> select * from people;
   name   
----------
 Critias
 Socrates
(2 rows)
```

[^1]: You may wonder what the purpose would be of having a schema with the same name as your username.
It can be useful when
working in a context where mutliple users have access to the same database.
Each of them can define a schema named for their own username
and keep their private tables and other definitions separate.
Note that our database server does not allow multiple users to share the same database.