/*
 * Lecture topics:
   CREATE for defining tables 
     Specifying domains
     PRIMARY KEY
     UNIQUE
     FOREIGN KEY 
     NOT NULL
     CHECK
     DEFAULT
     UNKNOWNs
     Maintenance of referential integrity
   Constraint checking 
     NOT DEFERRABLE 
     DEFERRABLE
   ASSERTIONs
   Trigger “on” INSERT, UPDATE, DELETE, “firing” BEFORE, AFTER, INSTEAD
   Views
   Updating views with SQL UPDATE
   Updating views with INSTEAD TRIGGERs
   ALTER, DROP, REPLACE
   Privileges:
      Select Insert Update Delete References
*/

/********************** Basic Definition **********************/
/*
 A minimal definition :
  Name of the table
  Names of the columns
  Domains of the columns

  Different systems have different types:
 - Integers, Real numbers, Char strings (fixed or variable length),
   bit strings  (fixed or variable length), dates and times
   
 - SQLite types:
   Integers, Real, Text, Blob

*/
/*
create table Plant(P      INTEGER, 
                   Pname  TEXT, 
                   Pcity  TEXT, 
                   Profit INTEGER); 
*/

/********************** Not Null **********************/
/*
Specifies that the values in one or more columns 
 must not be NULL
*/
/*
create table Plant(P      INTEGER NOT NULL, 
                   Pname  TEXT, 
                   Pcity  TEXT, 
                   Profit INTEGER); 
*/

/********************** Constraints **********************/

/*
- Constraint name is not required, but useful if you 
want to later DROP or ALTER the constaint.
- Best practice is to give it a meaningful name.
*/
/*
create table Plant(P      INTEGER NOT NULL, 
                   Pname  TEXT, 
                   Pcity  TEXT, 
                   Profit INTEGER,
                   CONSTRAINT C1 PRIMARY KEY (P) ); 
*/
/********************** Primary Key **********************/

/*
- The column P is the primary key (only one possible)
- Could be multiple columns, PRIMARY KEY (P, PName)
*/
/*
create table Plant(P      INTEGER NOT NULL, 
                   Pname  TEXT, 
                   Pcity  TEXT, 
                   Profit INTEGER,
                   CONSTRAINT C1 PRIMARY KEY (P) ); 
*/
/********************** Unique  **********************/

/*
- The “subtuple” PCITY,PNAME is a candidate key
- It could be NULL
- Not that the primary key must not be NULL
*/
/*
create table Plant(P      INTEGER NOT NULL, 
                   Pname  TEXT, 
                   Pcity  TEXT, 
                   Profit INTEGER,
                   CONSTRAINT C1 PRIMARY KEY (P),
                   CONSTRAINT C2 UNIQUE (Pcity, Profit) ); 
*/

/********************** Check (and Unknown) **********************/

/*
- Every tuple must satisfy this condition
- The condition is satisfied, when it is either TRUE or UNKNOWN
  i.e., if PCity is Null, condition is satisfied
- Note that in DML, Unknown meant "not satisfied" 
*/

create table Plant(P      INTEGER NOT NULL, 
                   Pname  TEXT, 
                   Pcity  TEXT, 
                   Profit INTEGER,
                   CONSTRAINT C1 PRIMARY KEY (P),
                   CONSTRAINT C2 UNIQUE (Pcity, Profit),
                   CONSTRAINT C3 CHECK ( Pcity <> Pname ) ); 

/********************** Default **********************/
/*
create table Customer(C     INTEGER, 
                      Cname TEXT DEFAULT (NULL),
                      Ccity TEXT DEFAULT ('Lugano'),
                      PRIMARY KEY(C));
*/

/********************** Foreign Key **********************/
/*

P in Customer has to reference the primary key of Plant
Either P has a non NULL value, and this value is in Plant
or P is NULL

Foreign key can also refer to UNIQUE and not only to PRIMARY KEY

*/
/*
create table Customer(C     INTEGER, 
                      Cname TEXT, 
                      Ccity TEXT, 
                      P     INTEGER,        
                      PRIMARY KEY(C),
                      FOREIGN KEY (P) REFERENCES Plant );
*/
/********************** On Delete Set Null **********************/
/*

We have a specification that if P listed in some tuple of Customer 
is deleted from Plant (that is the tuple with this value of 
primary key is deleted), then that value of P in Plant is 
automatically replaced by NULL
*/
create table Customer(C     INTEGER, 
                      Cname TEXT, 
                      Ccity TEXT, 
                      P     INTEGER,        
                      PRIMARY KEY(C),
                      FOREIGN KEY (P) REFERENCES Plant ON DELETE SET NULL);

/********************** On Delete Cascade **********************/

/*
We have a specification that if C listed in some tuple of Invoice 
is deleted from Customer (that is the tuple with this value of 
primary key is deleted), all the tuples with this value of C in 
Invoice must be deleted
*/
/*
create table Invoice(I     INTEGER,
                     Amt   INTEGER,
                     Idate TEXT,
                     C     INTEGER,
                     PRIMARY KEY(I),
                     FOREIGN KEY (C) REFERENCES Customer ON DELETE CASCADE);
*/

/********************** On Update Cascade **********************/
/*
- Constraints can also be enforced on INSERT or UPDATE
- CASCADE: the new value of the primary key is copied to the foreign key
- SET NULL: the new value of the foreign key is NULL
- SET DEFAULT: the new value of the foreign key is a specified default value 
*/
/*
create table Invoice(I     INTEGER,
                     Amt   INTEGER,
                     Idate TEXT,
                     C     INTEGER,
                     PRIMARY KEY(I),
                     FOREIGN KEY (C) REFERENCES Customer ON UPDATE CASCADE);
*/

/********************** Alter Table **********************/

/*
 Not permitted in SQLite.
*/
/*
ALTER TABLE Plant ADD CONSTRAINT C5 PRIMARY KEY (P);
*/

/********************** Assertions **********************/

/*
- Like a CHECK constraint, but not attached to a table.
- Useful for describing constraints that refer to more
  than one table
- Not implemented in SQLite, Oracle, others.
- Difficult to implement correctly and efficiently. 
*/
/*
CREATE ASSERTION Assertion01 
CHECK
  ( (SELECT COUNT (*) FROM Plant) 
  + (SELECT COUNT (*) FROM Customer)
   < 1000 );
*/

/********************** Triggers **********************/
/*
Actions that can be taken before/after/instead INSERT, UPDATE, or DELETE
Note: NEW refers to added rows, OLD refers to deleted rows
*/


/*
CREATE TRIGGER trigger01 AFTER INSERT ON Customer 
FOR EACH ROW
WHEN (NEW.Cname = 'Robert')
BEGIN
INSERT INTO Plant 
       VALUES('1000', 'Robert_Plant','Lugano', '10000');
END ;

insert into Customer values('1000', 'Robert', 'Boston', NULL);
*/

/********************** Views **********************/
/*
A “continuously current” table that is derived by means of 
a SELECT statement from other tables
*/
CREATE VIEW GoodPlant AS 
SELECT *
FROM Plant
WHERE Profit > 0.0;

/********************** Updating Views **********************/
/*
No problem
*/
/*
INSERT INTO GoodPlant
VALUES (675, 'Major', 'Philadelphia', .25);
*/
/********************** Updating Views **********************/
/*
 Change in Plant, but not shown in view
*/
/*
CREATE VIEW Plant_In_Chicago AS SELECT *
FROM Plant
WHERE City = 'Chicago';

INSERT INTO Plant_In_Chicago
VALUES (897,'Minor','Philadelphia',.1);
*/
/********************** Updating Views With Check Option **********************/

/* 
Will reject the insert
*/
/*
CREATE VIEW Plant_In_Chicago AS SELECT *
FROM Plant
WHERE City = 'Chicago'
WITH CHECK OPTION;
*/

/********************** Not Updating Views **********************/
/*
 Will be rejected, because there is no well-defined way 
 for reflecting this update in the base tables
*/
/*
CREATE VIEW Profit_On_Date AS 
SELECT Profit, Date
FROM Plant, Invoice, Customer 
WHERE Plant.P = Customer.P AND Invoice.C = Customer.C;

INSERT INTO Profit_On_Date VALUES (0.9,2009-02-01);
*/

/********************** Not Updating Views **********************/

/*
 Will be rejected 
*/
/*
CREATE VIEW Avg_Amt
AS SELECT AVG(Amt) FROM Invoice
WHERE Idate = ‘2009-02-01’;

CREATE VIEW Avg_Amt
AS SELECT AVG(Amt) FROM Invoice
WHERE Idate = ‘2009-02-01’;
*/

/*
The following are the major conditions (there are others) that must be 
true for an updatable view:
- Is drawn from one TABLE
- No joins, unions, differences, intersections
- If the underlying TABLE is a view, it must be updateable
- The SELECTed columns are column references (each column at most 
once and without DISTINCT) and not values or aggregates
- No GROUP BY
*/

/* Use triggers to update views? */


/***********************
SQL Data Control Language
***********************/

/********************** Examples of Privileges **********************/

/* Typical */
/*
GRANT SELECT, INSERT ON Customer
TO Li, Brown;
*/
/* On a column */
/*
GRANT SELECT ON Customer.City TO Li, Brown;    
*/
/* All Privileges */
/*
GRANT ALL ON Customer TO Li, Brown;
*/
/* Can pass privileges to other users */
/*
GRANT SELECT, INSERT ON Customer
TO Li, Brown
WITH GRANT OPTION;
*/
/********************** Unknown  **********************/
/*
create table TEST_UNKNOWN (
 X INTEGER,
 Y INTEGER,
 check (X=Y)
);

Insert into TEST_UNKNOWN values('a','a');
Insert into TEST_UNKNOWN values('b',null);
Insert into TEST_UNKNOWN values('c','d');

select *
from TEST_UNKNOWN;

select *
from TEST_UNKNOWN;
where x=y;
*/