<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>view</b></font> show_acl <font color="#ff6060"><b>as</b></font>    <font color="#ffff00"><b>select</b></font> wwwusers.username, wwwacl.cat, cat.name, wwwacl.canview,        wwwacl.canadd    <font color="#ff6060"><b>from</b></font> wwwusers, wwwacl, cat    <font color="#ff6060"><b>where</b></font> wwwusers.id=wwwacl.userid    <font color="#ffff00"><b>and</b></font> wwwacl.cat=cat.id;<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> show_acl <font color="#ff6060"><b>to</b></font> nobody;<font color="#00ffff"><b>-- The group file for the Web server's ACL crap.</b></font><font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>table</b></font> wwwgroup(    userid    <font color="#00ff00"><b>integer</b></font> <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,    groupname <font color="#00ff00"><b>varchar</b></font>(<font color="#ff40ff"><b>16</b></font>) <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,    foreign key(userid) references wwwusers(id));<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> wwwgroup <font color="#ff6060"><b>to</b></font> nobody;<font color="#00ffff"><b>-- Add the admin user to the wwwgroup</b></font><font color="#ffff00"><b>insert</b></font> <font color="#ff6060"><b>into</b></font> wwwgroup <font color="#ff6060"><b>values</b></font>(getwwwuser(<font color="#ff40ff"><b>'admin'</b></font>), <font color="#ff40ff"><b>'admin'</b></font>);<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>view</b></font> show_group <font color="#ff6060"><b>as</b></font>    <font color="#ffff00"><b>select</b></font> wwwusers.username, wwwgroup.groupname    <font color="#ff6060"><b>from</b></font> wwwusers, wwwgroup    <font color="#ff6060"><b>where</b></font> wwwusers.id=wwwgroup.userid;<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> show_group <font color="#ff6060"><b>to</b></font> nobody;<font color="#00ffff"><b>-- Search saves</b></font><font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>table</b></font> searches (    searches_id serial,    name        text <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,    addedby     <font color="#00ff00"><b>integer</b></font> <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,    search      text <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,    ts          datetime <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,    primary key(searches_id),    foreign key(addedby) references wwwusers(id));<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> searches <font color="#ff6060"><b>to</b></font> nobody;<font color="#00ffff"><b>-- implicit seqeunce</b></font><font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> searches_searches_id_seq <font color="#ff6060"><b>to</b></font> nobody;<font color="#00ffff"><b>-- Hmm...  Store images in text?  OK, sure...</b></font><font color="#00ffff"><b>-- This is keyed of the id in the album table</b></font><font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>table</b></font> image_store (    id   <font color="#00ff00"><b>integer</b></font> <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,    line <font color="#00ff00"><b>integer</b></font> <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,    data text <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,    foreign key(id) references album(id) <font color="#ff6060"><b>on</b></font> <font color="#ffff00"><b>delete</b></font> cascade);<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> image_store <font color="#ff6060"><b>to</b></font> nobody;<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>index</b></font> images_id <font color="#ff6060"><b>on</b></font> image_store(id);<font color="#00ffff"><b>-- A SQL function to return the count of elements in a category.</b></font><font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>function</b></font> catsum (<font color="#00ff00"><b>integer</b></font>)    returns <font color="#00ff00"><b>integer</b></font> <font color="#ff6060"><b>AS</b></font>    <font color="#ff40ff"><b>'select count(*) from album where cat = $1'</b></font>    language <font color="#ff40ff"><b>'SQL'</b></font>;<font color="#00ffff"><b>-- User Agent table, for recording user-agents in logs.</b></font><font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>table</b></font> user_agent (    user_agent_id serial,    user_agent text,    primary key(user_agent_id));<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> user_agent <font color="#ff6060"><b>to</b></font> nobody;<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> user_agent_user_agent_id_seq <font color="#ff6060"><b>to</b></font> nobody;<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>unique</b></font> <font color="#ff6060"><b>index</b></font> user_agent_text <font color="#ff6060"><b>on</b></font> user_agent(user_agent);<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>function</b></font> get_agent(text) returns <font color="#00ff00"><b>integer</b></font> <font color="#ff6060"><b>as</b></font><font color="#ff40ff"><b>'</b></font><font color="#ff40ff"><b>declare</b></font><font color="#ff40ff"><b>    id integer;</b></font><font color="#ff40ff"><b>begin</b></font><font color="#ff40ff"><b>    select user_agent_id into id from user_agent where user_agent = $1;</b></font><font color="#ff40ff"><b>    if not found then</b></font><font color="#ff40ff"><b>        insert into user_agent(user_agent) values($1);</b></font><font color="#ff40ff"><b>        select user_agent_id into id from user_agent where user_agent = $1;</b></font><font color="#ff40ff"><b>    end if;</b></font><font color="#ff40ff"><b>    return(id);</b></font><font color="#ff40ff"><b>end;</b></font><font color="#ff40ff"><b>'</b></font> language <font color="#ff40ff"><b>'plpgsql'</b></font>;<font color="#00ffff"><b>-- Log various activities</b></font><font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>table</b></font> log_types (    log_type_id serial,    log_type <font color="#00ff00"><b>varchar</b></font>(<font color="#ff40ff"><b>32</b></font>),    primary key(log_type_id));<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>unique</b></font> <font color="#ff6060"><b>index</b></font> log_types_bytype <font color="#ff6060"><b>on</b></font> log_types(log_type);<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> log_types <font color="#ff6060"><b>to</b></font> nobody;<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> log_types_log_type_id_seq <font color="#ff6060"><b>to</b></font> nobody;<font color="#00ffff"><b>-- Insert some data.</b></font><font color="#ffff00"><b>insert</b></font> <font color="#ff6060"><b>into</b></font> log_types(log_type) <font color="#ff6060"><b>values</b></font>(<font color="#ff40ff"><b>'Login'</b></font>);<font color="#ffff00"><b>insert</b></font> <font color="#ff6060"><b>into</b></font> log_types(log_type) <font color="#ff6060"><b>values</b></font>(<font color="#ff40ff"><b>'ImgView'</b></font>);<font color="#ffff00"><b>insert</b></font> <font color="#ff6060"><b>into</b></font> log_types(log_type) <font color="#ff6060"><b>values</b></font>(<font color="#ff40ff"><b>'Upload'</b></font>);<font color="#ffff00"><b>insert</b></font> <font color="#ff6060"><b>into</b></font> log_types(log_type) <font color="#ff6060"><b>values</b></font>(<font color="#ff40ff"><b>'AuthFail'</b></font>);<font color="#ffff00"><b>insert</b></font> <font color="#ff6060"><b>into</b></font> log_types(log_type) <font color="#ff6060"><b>values</b></font>(<font color="#ff40ff"><b>'Request'</b></font>);<font color="#00ffff"><b>-- A function for looking up log types</b></font><font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>function</b></font> get_log_type(TEXT) returns <font color="#00ff00"><b>INTEGER</b></font> <font color="#ff6060"><b>as</b></font>    <font color="#ff40ff"><b>'select log_type_id from log_types where log_type = $1'</b></font>    language <font color="#ff40ff"><b>'sql'</b></font> <font color="#ff6060"><b>with</b></font> (iscachable);<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>table</b></font> photo_logs (    log_id serial,    log_type <font color="#00ff00"><b>integer</b></font> <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,    wwwuser_id <font color="#00ff00"><b>integer</b></font> <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,    photo_id <font color="#00ff00"><b>integer</b></font>,    remote_addr inet <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,    user_agent <font color="#00ff00"><b>integer</b></font> <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,    extra_info text,    ts datetime <font color="#ff6060"><b>default</b></font> now(),    primary key(log_id),    foreign key(log_type) references log_types(log_type_id),    foreign key(wwwuser_id) references wwwusers(id),    foreign key(user_agent) references user_agent(user_agent_id));<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>index</b></font> photo_logs_bytype <font color="#ff6060"><b>on</b></font> photo_logs(log_type);<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>index</b></font> photo_logs_byuser <font color="#ff6060"><b>on</b></font> photo_logs(wwwuser_id);<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>index</b></font> photo_logs_byphoto <font color="#ff6060"><b>on</b></font> photo_logs(photo_id);<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> photo_logs <font color="#ff6060"><b>to</b></font> nobody;<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> photo_logs_log_id_seq <font color="#ff6060"><b>to</b></font> nobody;<font color="#00ffff"><b>-- New user profiles</b></font><font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>table</b></font> user_profiles (    profile_id serial,    name <font color="#00ff00"><b>varchar</b></font>(<font color="#ff40ff"><b>32</b></font>) <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,    description text <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,