use AdventureWorks2012 ;

---------------------------------------------------------------------------------
-- Abschnitt 14.1
create function cube (
	@arg float
) returns float
as
	begin
	return @arg * @arg * @arg ;
	end	
declare @f float;
exec @f = cube 3;
print @f;
select seitenlaenge, cube ( seitenlaenge ) as volumen
	from wuerfel ;

---------------------------------------------------------------------------------
-- Abschnitt 14.5.1
/*
 * Liefert die Anzahl der Einkäufe des angegebenen Kunden
 * 20141220 mw
 */
create function Sales.CustomersOrders (
	@CustomerID int
) returns integer
as
	begin
	declare @orders int = null
	select @orders = Orders
		from Sales.CustomerOrders
		where CustomerID = @CustomerID ;
	return @orders ;
	end
go

print Sales.CustomersOrders (12283);

select CustomerID, Sales.CustomersOrders (CustomerID)
	from Sales.Customer
	where Sales.CustomersOrders (CustomerID) is not null;

declare @orders int = null;

set @orders = Sales.CustomersOrders (12283);

---------------------------------------------------------------------------------
-- Abschnitt 14.5.2
create function postalCodes ( @SalesPersonID int )
returns table
as return
	select distinct PostalCode
		from person.Address as a inner join
			sales.SalesOrderHeader as h on 
				h.billToAddressID = a.AddressID 
		where h.SalesPersonID = @SalesPersonID ;
go

select * from postalCodes( 274 ) ;

select * from postalCodes ( 274 ) ;

---------------------------------------------------------------------------------
-- Abschnitt 14.5.3
-- drop function postalCodes ;
go

create function postalCodes ( @SalesPersonID int )
returns @ret table ( postalCode nvarchar(15) )
as
	begin

	if ( @SalesPersonID is null )
		begin
		insert into @ret
			select distinct PostalCode
				from person.Address as a inner join
					sales.SalesOrderHeader as h on 
						h.billToAddressID = a.AddressID ;
		end
	else
		begin
		insert into @ret
			select distinct PostalCode
				from person.Address as a inner join
					sales.SalesOrderHeader as h on 
						h.billToAddressID = a.AddressID 
				where h.SalesPersonID = @SalesPersonID ;
		end
	return
	end
go

select * from postalCodes( null ) ;

---------------------------------------------------------------------------------
-- Abschnitt 14.5.4
-- drop table MyEmployees;

-- Create an Employee table.
CREATE TABLE MyEmployees (
	EmployeeID smallint NOT NULL,
	FirstName varchar(30) NOT NULL,
	LastName varchar(40) NOT NULL,
	Title varchar(50) NOT NULL,
	DepartmentID smallint NOT NULL,
	ManagerID int NULL,
	CONSTRAINT PK_EmployeeID PRIMARY KEY ( EmployeeID ) 
);

-- Tabelle mit Daten populieren:
INSERT INTO MyEmployees VALUES 
	(1,   'Ken',   'Sánchez', 
		'Chief Executive Officer',16,NULL),
	(273, 'Brian', 'Welcker', 
		'Vice President of Sales',3,1),
	(274, 'Stephen', 'Jiang', 
		'North American Sales Manager', 3, 273 ),
	(275, 'Michael', 'Blythe',
		'Sales Representative',3,274),
	(276, 'Linda', 'Mitchell',
		'Sales Representative',3,274),
	(285, 'Syed',  'Abbas',
		'Pacific Sales Manager',3,273),
	(286, 'Lyn',  'Tsoflias',
		'Sales Representative',3, 91),
	(16,  'David', 'Bradley', 
		'Marketing Manager', 4, 273),
	(23,  'Mary',  'Gibson',  
		'Marketing Specialist', 4, 16),
	(91,  'Clara', 'O''Connor',
		'Chief Technical Officer', 7, NULL);
							--(T-SQL; pgSQL)

select * from MyEmployees;

-- drop function Untergebene;

create function Untergebene ( @id smallint )
returns @s table ( id smallint )
	begin

	-- Rekursionsbeginn: die direkt Untergebenen
	insert into @s ( id ) 
		select EmployeeID 
			from MyEmployees 
			where ManagerID = @id;

	-- Die Rekursion: deren Untergebenen
	declare id cursor fast_forward read_only for
		select id from @s ;

	declare @uid smallint;

	open id;

	fetch next from id into @uid;
	
	while @@fetch_status = 0
		begin
		insert into @s ( id ) 
			select id from Untergebene ( @uid );
		fetch next from id into @uid;
		end
	deallocate id ;
	
	return ;
	
	end					
go

/*
drop function rek ;
drop table Flug ;
drop table Flughafen ;
*/

create table Flughafen (
	Kennung char(3) primary key ,
	Stadt varchar(100) not null ,
	Land char (2)
) ;

insert into Flughafen ( Kennung, Stadt, Land ) 
	values ( 'AMS', 'Amsterdam', 'NL' ) ,
		('AYT', 'Antalya', 'TR' ) ,
		('BCN', 'Barcelona', 'ES' ) ,
		('FRA', 'Frankfurt/Main', 'DE' ) ,
		('IST', 'Istanbul', 'TR' ) ,
		('LGW', 'London', 'UK' ) ,
		('LHR', 'London', 'UK' ) ,
		('MAD', 'Madrid', 'ES' ) ,
		('MUC', 'München', 'DE' ) ,
		('ORY', 'Paris', 'FR' ) ,
		('VIE', 'Wien', 'AT' ) ;

create table Flug ( 
	Kennung char(6) primary key ,
	von char(3) not null references Flughafen ( Kennung ) ,
	nach char(3) not null references Flughafen ( Kennung ) 
) ;

insert into Flug ( Kennung, von, nach )
	values ( 'OS001', 'vie', 'muc' ) ,
		( 'OS002', 'vie', 'ory' ) ,
		( 'OS003', 'vie', 'mad' ) ,
		( 'OS004', 'vie', 'lgw' ) ,
		( 'OS005', 'vie', 'ist' ) ,
		( 'OS006', 'vie', 'fra' ) ,
		( 'OS007', 'vie', 'bcn' ) ,
		( 'OS008', 'vie', 'ayt' ) ,
		( 'OS009', 'vie', 'ams' ) ,
		( 'LH001', 'fra', 'lhr' ) ,
		( 'LH002', 'fra', 'ory' ) ,
		( 'LH003', 'fra', 'mad' ) ,
		( 'BA001', 'lhr', 'fra' ) ,
		( 'BA002', 'lhr', 'ory' ) ,
		( 'BA003', 'lhr', 'mad' ) ,
		( 'BA004', 'lhr', 'ist' ) ,
		( 'BA005', 'lhr', 'muc' ) ;

alter function rek
	( @von char (3),
    @hops tinyint )
returns @rek table ( nach char (3) )

	begin
	
	if @hops = 0 -- Spezialfall abfangen: 0 hops 
		insert into @rek
			values ( @von ) ;
	else if @hops = 1 -- normaler abbruchfall
		insert into @rek
			select nach 
				from flug 
				where von = @von ;
	else
		begin	
		-- T-SQL kann keinen Ausdruck übergeben
		set @hops -= 1; 
		insert into @rek
			select nach 
				from flug 
				where von in (
					-- hier der Rekursionsaufruf
					select nach
						from dbo.rek ( @von , @hops ) 
				) ; 
		end;

	return ;

	end	
go

select * from dbo.rek('vie', 3) ;

---------------------------------------------------------------------------------
-- Abschnitt 15.4

-- MS SQL Server
create login Alice with password = 'Alice';	

create login Bob with password = 'Bob';

--PostgreSQL
create login Alice with password = 'Alice';	

create login Bob with password = 'Bob';

--PostgreSQL
alter role bob with login encrypted password 'bob'

create user Alice for login Alice ;

create user Bob for login Bob ;

--Als Alice:
use AdventureWorks2012 ;

select * from Person.Person ;

--Als dbo:
grant select on Person.Person to Alice ;

--Als Alice:
select Title, FirstName, LastName, Suffix from Person.Person ;

--Als dbo:
revoke select on Person.Person from Alice ;

create role Manager ;

create role Employee ;

--MS SQL Server
exec sp_addrolemember 'Manager', 'Alice';

exec sp_addrolemember 'Employee', 'Bob';

exec sp_addrolemember 'Employee', 'Manager';

-- Ab MS SQL Server 2014
alter role Employee add member Manager ;

grant delete, insert on Person.Person to Manager ;

grant update on Person.Person to Employee ;

grant select on Person.Person to public;

grant select, delete, insert, update 
	on Person.Person to Manager ;

grant select, update on Person.Person to Employee ;

revoke select on Person.Person from public;

exec sp_droprolemember 'Employee', 'Manager';

-- Ab MS SQL Server 2014
alter role Employee drop member Manager ;

---------------------------------------------------------------------------------
-- Abschnitt 15.4.5

create role Sales ;

create role Production ;

create role Purchasing ;

revoke select, insert, update, delete
	on Person.Person from Bob;

deny select, insert, update, delete
	on Person.Person to Bob;

---------------------------------------------------------------------------------
-- Abschnitt 15.4.7

--Als Alice:
select *
	from Sales.SalesOrderHeader ;

select *
	from Sales.Customer ;

select *
	from Sales.CustomerOrders ;

--Als dbo:
grant select on Sales.CustomerOrders to Alice ;

--Als Alice:
select *
	from Sales.CustomerOrders ;

select *
	from Sales.Customer ;

