1. Database Programming
在Procedural Language中操作數(shù)據(jù)庫(kù)時(shí)返吻，往往包含三個(gè)步驟：
（1）code in a programming language（PHP比較常用）
（2）SQL query/update statments（用來(lái)獲得數(shù)據(jù)庫(kù)的信息）
（3）code to map between tuples and PL data（連接PL和database）
從網(wǎng)頁(yè)調(diào)取數(shù)據(jù)的時(shí)候，除了sql, PHP外风喇，往往還需要用到html, css和javascript柱衔。
2. Catalogs
Catalogs是關(guān)于databse的數(shù)據(jù)樊破，比如有多少table，多少trigger唆铐，多少fuction等哲戚。system catalog也被叫做data dictionary或者system view。
SQL:2003 standard metadata: INFORMATION_SCHEMA.
INFORMATION_SCHEMA is available globally and includes:
--Schemata(catalog_name, schema_name, schema_owner, ...)
--Tables(table_catalog, table_schema, table_name, table_type, ...)
--Columns(table_catalog, table_schema, table_name, column_name, ordinal_position, column_default, data_type, ...)
--Views(table_catalog, table_schema, table_name, view_definition, ...) Table_Constraints(..., constraint_name, ..., constraint_type, ...)
2.1 練習(xí)1
訪問(wèn)Catalog獲取databse的table信息：
create or replace view myTables
as
select table_name
from information_schema.tables
where table_schema = 'public' and table_type = 'BASE TABLE'
order by table_name;
2.2 練習(xí)2
訪問(wèn)Catalog獲取table和attributes信息：
create or replace view myTableColumns
as
select t.table_name, c.column_name
from information_schema.tables t
join information_schema.columns c
on (t.table_name = c.table_name)
where t.table_schema = 'public' and t.table_type = 'BASE TABLE'
order by t.table_name, c.ordinal_position;
create or replace view mySchema("table","attributes")
as
select table_name, concat(column_name)
from myTableColumns
group by table_name
order by table_name;
2.3 練習(xí)3
寫一個(gè)plpgsql函數(shù)實(shí)現(xiàn)以下功能：
--whose argument is a table name (from the public schema)
--whose result is a CREATE TABLE statement to build the table
--only handle constraints mentioned in the columns table
create type ColumnRecord as (
"table" text,
"column" text,
"type" text,
"constraint" text
);
create or replace function
colDataType(_col information_schema.columns) returns text
as $$
declare
_out text;
begin
_out := _col.data_type;
_out := _out||coalesce(' '||_col.character_maximum_length::text,'');
-- if (_col.character_maximum_length is not null) then
-- _out = _out||(_col.character_maximum_length::text);
-- end if;
_out := replace(_out,'character varying','varchar');
if (_out like 'timestamp%') then _out := 'timestamp'; end if;
return _out;
end;
$$ language plpgsql;
create or replace function
colConstraints(_col information_schema.columns) returns text
as $$
declare
_rec record;
_out text;
begin
_out := '';
if (_col.is_nullable = 'NO') then
_out := _out||','||'NOT NULL';
end if;
for _rec in
select tc.constraint_type
from information_schema.constraint_column_usage ccu
join information_schema.table_constraints tc
on (ccu.constraint_name = tc.constraint_name)
where tc.table_name = _col.table_name
and ccu.column_name = _col.column_name
loop
_out := _out||','||_rec.constraint_type;
end loop;
return substring(_out,2,length(_out));
end;
$$ language plpgsql;
create or replace function
schema() returns setof ColumnRecord
as $$
declare
_col information_schema.columns;
_c ColumnRecord;
begin
for _col in
select c.*
from information_schema.tables t
join information_schema.columns c
on (t.table_name = c.table_name)
where t.table_schema='public' and t.table_type = 'BASE TABLE'
loop
_c."table" := _col.table_name;
_c."column" := _col.column_name;
_c."type" := colDataType(_col);
_c."constraint" := colConstraints(_col);
return next _c;
end loop;
return;
end;
$$ language plpgsql;
3. Security, Privilege, Authorisation
3.1 Access Control
創(chuàng)建用戶：
CREATE USER Name IDENTIFIED BY 'Password'
ALTER USER Name IDENTIFIED BY 'NewPassword'
ALTER USER Name WITH Capabilities
ALTER USER Name SET ConfigParameter = ...
創(chuàng)建用戶分組：
CREATE GROUP Name
ALTER GROUP Name ADD USER User1, User2, ...
ALTER GROUP Name DROP USER User1, User2, ...
設(shè)置用戶權(quán)限：
CREATE ROLE UserName Options
-- where Options include ...
PASSWORD 'Password'
CREATEDB | NOCREATEDB
CREATEUSER | NOCREATEUSER
IN GROUP GroupName
VALID UNTIL 'TimeStamp'
