photo.sql.html

图片管理程序

<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>,