Lecture 15
-----------

VIEWS:
-------- 


SELECT *
FROM
   (SELECT baker, fullname, age
    FROM bakers
    WHERE baker not in (select baker
                        from results
                        where result = 'eliminated')
   ) as noteliminated
WHERE noteliminated.age < 45;

WITH noteliminated AS
(SELECT baker, fullname, age
    FROM bakers
    WHERE baker not in (select baker
                        from results
                        where result = 'eliminated')
)
SELECT *
FROM noteliminated
WHERE age < 45;


CREATE VIEW noteliminated(baker, name, age)
AS
SELECT baker, fullname, age
FROM bakers
WHERE baker not in (select baker
                    from results
                    where result = 'eliminated');




SELECT *
FROM noteliminated
WHERE age < 45;

---> Simplify application logic/ access control rules

---> View may hide certain optimizations or logic


CREATE VIEW  lt40(baker, fullnamename, age)
AS
SELECT baker, fullname, age
FROM bakers
WHERE age < 40 ;

-- updateable views: views in which tuples in the base relation can
be changed through the view

-> view has no joins, distinct, group by



UPDATE lt40 SET age = 40 WHERE baker = 'Manon' ;




CREATE VIEW langcounts(movieid, numlanguages) AS 
SELECT
     movieid
     , count(*) 
  FROM
     movieslanguages
  GROUP BY
     movieid;

SELECT
   m.title
   , numl.numlanguages
FROM
   movies m
   , langcounts numl
WHERE
   m.movieid = numl.movieid
   and numl.numlanguages =
           (SELECT max(numlanguages) FROM langcounts)
ORDER BY
   title ASC
;



-- check option

CREATE VIEW  lt40(baker, fullnamename, age)
AS
SELECT baker, fullname, age
FROM bakers
WHERE age < 40
WITH CHECK OPTION;



-------------

System Tables

select viewname from pg_views where viewowner = 'kkuzmin' ;

select viewname, definition from pg_views where viewowner = 'kkuzmin' ;

------------- 



Other SQL Features
--------------------

--- OR stuff you really wanted to do all this time ............


CASE statement

create table abc (
     id int,
     id2 int
) ;
insert into abc values(1,2);
insert into abc values(3,null);
insert into abc values(null,4);


select
      CASE WHEN id IS NULL THEN 0 ELSE id END
      +CASE WHEN id2 IS NULL THEN 0 ELSE id2 END
from abc ;

select id+id2 from abc where id is not null and id2 is not null
union all
select id from abc where id is not null and id2 is null
union all
select id2 from abc where id is  null and id2 is not null;


select COALESCE(id, 0) + COALESCE(id2, 0)
from abc;


--------------------- 





Access Control:
----------------

create user <username> with encrypted password <password> inherit login createdb;

create role dbstudent ;  -- implicit: login inherit
create role dbta ;

grant dbstudent to kkuzmin ;


CREATE ROLE joe LOGIN INHERIT;




Other SQL Features
--------------------

DROP TABLE events ;

CREATE TABLE events (
    name     VARCHAR(10)
    , day    VARCHAR(10)
    , time   VARCHAR(10)
    , price  INT
) ;

INSERT INTO events VALUES ('sitting','M','12:00',5);
INSERT INTO events VALUES ('reading','W','2:00',10);
INSERT INTO events VALUES ('sleeping','M','2:00',12);
INSERT INTO events VALUES ('hopping','W','12:00',8);
INSERT INTO events VALUES ('jumping','M','4:00',22);





GROUP BY

select day, time, avg(price) from events group by day, time ;

select day, avg(price) from events group by day ;

select time, avg(price) from events group by time ;


select day, time, avg(price) from events
group by grouping sets((day,time), (day), (time) ;


select day, time, avg(price) from events
group by rollup (day,time) ;

select day, time, avg(price) from events
group by grouping sets((day,time), (day), ()) ;

select day, time, avg(price) from events
group by rollup (time,day) ;


select day, time, avg(price) from events
group by cube(day,time) ;

select day, time, avg(price) from events
group by grouping sets((day,time), (day), (time), ()) ;


--> (day,time), (day), (time), ()


-------------------
Group by with Filter

Apply an aggregate to a subset of tuples in that group

SELECT day
       , sum(price) as total
       , sum(price) filter (where price>10) as totalfiltered
FROM events
GROUP BY day;

-- Wrong!
SELECT day
       , sum(price) as total
       , sum(price) as totalfiltered
FROM events
WHERE price > 10
GROUP BY day;

-------------------
Window Functions

Compute aggregates without a group by for a window of values

SELECT name, day, time, sum(price) OVER (partition by day)
FROM   events
ORDER BY day;

-------------------

Recursion

DROP TABLE parents ;

CREATE TABLE parents (
     parent   varchar(100)
     , child  varchar(100)
) ;


INSERT INTO parents VALUES('Dakota','Madison');
INSERT INTO parents VALUES('Madison','Ava');
INSERT INTO parents VALUES('Madison','Sophia');
INSERT INTO parents VALUES('Sophia','Noah');
INSERT INTO parents VALUES('Noah','Emma');


WITH anc AS
(
   SELECT parent, child FROM parents
   UNION
   SELECT p1.parent, p2.child FROM parents p1, parents p2
   WHERE  p1.child = p2.parent
)
SELECT * FROM anc ;


WITH RECURSIVE anc AS
(
   SELECT parent, child FROM parents
   UNION
   SELECT p1.parent, a.child FROM parents p1, anc a
   WHERE  p1.child = a.parent
)
SELECT * FROM anc ;







CREATE TABLE cities (
   name            text
   , population      int
   , altitude        int     -- in feet
);
     
CREATE TABLE capitals (
   state           char(2)
) INHERITS (cities);

insert into cities values('New York City', 8175133, 33);
insert into cities values('Syracuse', 145170, 380);
insert into capitals values('Albany', 97856, 312, 'NY');



SELECT
   name
   , altitude
FROM
   cities
WHERE
   altitude > 50;

SELECT
   name
   , altitude
FROM ONLY
   cities
WHERE
   altitude > 50;

----------------
Defining data types

create type phone_type as (
     num varchar(12)
     , type varchar(50)
);

create table person (
     id int
     , name varchar(30)
     , phone phone_type
) ;

insert into person values(
     1
     , 'Kara Danvers'
     , ('555-1234','work')::phone_type
) ;

SELECT phone.num FROM person ;

SELECT (phone).num, (phone).type FROM person ;


----------------
Arrays

CREATE TABLE tictactoe (
    squares   integer[3][3]
);

INSERT INTO tictactoe VALUES('{{1,2,3},{4,5,6},{7,8,9}}');

INSERT INTO tictactoe VALUES(ARRAY[ARRAY[1,2,3],ARRAY[4,5,6],ARRAY[7,8,9]]);

SELECT squares[3][2] FROM tictactoe; --not zero indexed

SELECT unnest(squares) FROM tictactoe;







CREATE TABLE messages (
     msg  text[]
) ;

INSERT INTO messages VALUES ('{"hello", "world"}') ;
INSERT INTO messages VALUES ('{"I", "feel", "so", "free"}') ;

SELECT msg[1] FROM messages ;
SELECT msg[1], msg[2] FROM messages ;
SELECT msg[1], msg[2], msg[3] FROM messages ;


SELECT msg[2:3] FROM messages; --slicing, really?


----------------
Typed objects and methods

Handling specific types of data:
* Geographic
* Text
* JSON

SELECT '{"foo": {"bar": "baz"}}'::jsonb;


SELECT '{"employees": [{"name": "Shyam", "email": "hyamjaiswal@gmail.com"}, {"name": "Bob", "email": "bob32@gmail.com"}, {"name": "Jai", "email": "jai87@gmail.com"}]}'::jsonb->'employees'->2;

CREATE TABLE journal (
  id Int NOT NULL PRIMARY KEY,
  day VARCHAR, 
  diary_information JSONB
);

INSERT INTO journal (id, day, diary_information) 
VALUES 
  (
    1, 'Tuesday', '{"title": "My first day at work", "Feeling": "Mixed                   feeling"}'
  );

INSERT INTO journal (id, day, diary_information) 
VALUES 
  (
    2, 'Wednesday', '{"title": "My second day at work", "Feeling": "Much better!"}'
  );

SELECT day, diary_information->'Feeling' FROM journal;


----------------
Geographic Data

PostGIS extension needed to support geographic data


create database geodb owner kkuzmin;
\c geodb postgres
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
SELECT PostGIS_version();
\c geodb kkuzmin

CREATE TABLE bwithloc (
     name  VARCHAR(100)
     , location geography(POINT,4326)
) ;

insert into bwithloc values('Rensselaer Polytechnic Institute',
     ST_GeographyFromText('SRID=4326;POINT(42.7308634 -73.6816793)'));

insert into bwithloc values('Shalimar Restaurant',
     ST_GeographyFromText('SRID=4326;POINT(42.732293 -73.688473)'));

insert into bwithloc values('The Placid Baker',
     ST_GeographyFromText('SRID=4326;POINT(42.7313916 -73.690868)'));
     
SRID shows the projection used to compute the latitude and longitude coordinates on the Earth's surface as defined in the WGS84 standard.

SELECT
   b1.name
   , b2.name
   , ST_DISTANCE(b1.location, b2.location) AS distance_meters
FROM
   bwithloc b1
   , bwithloc b2
WHERE
   b1.name < b2.name ;



----------------
Text Querying

to_tsvector parses a textual document into tokens, reduces the tokens to lexemes, and returns a tsvector which lists the lexemes together with their positions in the document.

SELECT to_tsvector('Oh, you weak, beautiful people who give up with such grace. What you need is someone to take hold of you gently, with love, and hand your life back to you, like something gold you let go of and I can! I''m determined to do it and nothing''s more determined than a cat on a tin roof is there? Is there, baby?');

Text queries will consist of boolean connection of keywords, tokenized and stop words removed.

SELECT to_tsquery('english', 'And & beauty & deterministic & loaves');


ts_rank_cd is used for the cover density ranking.
@@ operator is used to compare a tsquery value (the query) with a tsvector value (the document) to determine if the document matches the query.

SELECT
   b.name
   , ts_rank_cd(to_tsvector('english', r.review_text), query) AS rank
FROM
   reviews r
   , businesses b
   , to_tsquery('pizza & (crust | sauce) & (delicious|tasty)') query
WHERE
   b.business_id = r.business_id
   and to_tsvector('english', r.review_text) @@ query
ORDER BY
   rank DESC
LIMIT 10;








-- Courses with at least one 2000-level prerequisite
SELECT c.title, c.coursecode, cp.prereq
FROM
  courses c
  , course_prereqs cp
WHERE
  c.coursecode = cp.coursecode
  AND EXISTS (SELECT 1
              FROM prereqs(c.coursecode)
              WHERE prereq ~* '.*2\d{3}$')
;


-- Courses and their prerequisites as jsonb
SELECT c.coursecode, pr.prereq
FROM
  courses c
  , course_prereqs cp
  , prereqs(c.coursecode) pr
WHERE
  c.coursecode = cp.coursecode
  AND c.coursecode = pr.coursecode
;


-- All courses and their prerequisites as an array
SELECT c.coursecode, array_agg(pr.prereq)
FROM
  courses c
  , course_prereqs cp
  , prereqs(c.coursecode) pr
WHERE
  c.coursecode = cp.coursecode
  AND c.coursecode = pr.coursecode
GROUP BY
  c.coursecode
;


-- Courses with only 2000-level courses as prerequisites
SELECT c.coursecode, array_agg(pr.prereq)
FROM
  courses c
  , course_prereqs cp
  , prereqs(c.coursecode) pr
WHERE
  c.coursecode = cp.coursecode
  AND c.coursecode = pr.coursecode
  AND pr.prereq ~* '.*2\d{3}$'
GROUP BY
  c.coursecode
;


-- Courses with at least one 2000-level prerequisite
WITH matching AS (
SELECT c.coursecode
FROM
  courses c
  , course_prereqs cp
WHERE
  c.coursecode = cp.coursecode
  AND EXISTS (SELECT 1
              FROM prereqs(c.coursecode)
              WHERE prereq ~* '.*2\d{3}$')
)
SELECT
  c.coursecode, array_agg(pr.prereq)
FROM
  courses c
  , course_prereqs cp
  , prereqs(c.coursecode) pr
WHERE
  c.coursecode = cp.coursecode
  AND substring(c.coursecode, 1, 4) = 'CSCI'
  AND c.coursecode IN (SELECT * FROM matching)
GROUP BY
  c.coursecode
ORDER BY
  c.coursecode
;



CREATE TABLE test (
  id  SERIAL PRIMARY KEY,
  doc JSON
);

INSERT INTO test (doc) VALUES ('{
 "files": {
  "folder": {
   "file1": {
    "property": "blah"
   },
   "file2": {
    "property": "blah"
   },
   "file3": {
    "property": "blah"
   },
   "file4": {
    "property": "blah",
    "prop" : {
      "clap": "clap"
    }
   }
 }
},
"software": {
  "apt": {
    "package1": {
        "version": 1.2
    },
    "package2": {
        "version": 1.2
    },
    "package3": {
        "version": 1.2
    },
    "package4": {
        "version": 1.2
    }
  }
 }
}');



WITH RECURSIVE doc_key_and_value_recursive(key, value) AS (
  SELECT
    t.key,
    t.value
  FROM test, json_each(test.doc) AS t

  UNION ALL

  SELECT
    t.key,
    t.value
  FROM doc_key_and_value_recursive,
    json_each(CASE 
      WHEN json_typeof(doc_key_and_value_recursive.value) <> 'object' THEN '{}' :: JSON
      ELSE doc_key_and_value_recursive.value
    END) AS t
)
SELECT *
FROM doc_key_and_value_recursive;
--WHERE json_typeof(doc_key_and_value_recursive.value) <> 'object';
































WITH T1(id, data) AS (
  VALUES(1, '{"1":{"11":11},"2":{"12":12}}'::jsonb)
)
SELECT t.*
FROM T1, jsonb_each(T1.data) AS t(k,v)
;









SELECT *
FROM prereqs('CSCI-4380')
;

SELECT c.title, c.coursecode, cp.prereq
FROM
  courses c
  , course_prereqs cp
WHERE
  c.coursecode = cp.coursecode
  AND EXISTS (SELECT 1
       FROM prereqs(c.coursecode)
       WHERE prereq ~* '.*2\d{3}$'
       )
;