photo.sql.html

图片管理程序

    expires <font color="#00ff00"><b>date</b></font> <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,
    primary key(profile_id)
);
<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>unique</b></font> <font color="#ff6060"><b>index</b></font> user_profilesbyname <font color="#ff6060"><b>on</b></font> user_profiles(name);
<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> user_profiles <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_profiles_profile_id_seq <font color="#ff6060"><b>to</b></font> nobody;

<font color="#00ffff"><b>-- Profile ACLs</b></font>
<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>table</b></font> user_profile_acls (
    profile_id <font color="#00ff00"><b>integer</b></font> <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,
    cat_id <font color="#00ff00"><b>integer</b></font> <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,
    foreign key(profile_id) references user_profiles(profile_id),
    foreign key(cat_id) references cat(id)
);
<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>index</b></font> user_profile_aclsbyp <font color="#ff6060"><b>on</b></font> user_profile_acls(profile_id);
<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> user_profile_acls <font color="#ff6060"><b>to</b></font> nobody;

<font color="#00ffff"><b>-- This table logs when users are created with profiles</b></font>
<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>table</b></font> user_profile_log (
    log_id serial,
    profile_id <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>,
    ts timestamp <font color="#ff6060"><b>default</b></font> now(),
    remote_addr inet <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,
    primary key(log_id),
    foreign key(profile_id) references user_profiles(profile_id),
    foreign key(wwwuser_id) references wwwusers(id)
);
<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>index</b></font> user_profile_log_byuser <font color="#ff6060"><b>on</b></font> user_profile_log(wwwuser_id);
<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>index</b></font> user_profile_log_byprof <font color="#ff6060"><b>on</b></font> user_profile_log(profile_id);
<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> user_profile_log_log_id_seq <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_profile_log <font color="#ff6060"><b>to</b></font> nobody;

<font color="#00ffff"><b>-- Table for galleries</b></font>
<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>table</b></font> galleries (
    gallery_id serial,
    gallery_name <font color="#00ff00"><b>varchar</b></font>(<font color="#ff40ff"><b>64</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>,
    ispublic <font color="#00ff00"><b>boolean</b></font> <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,
    ts timestamp <font color="#ff6060"><b>default</b></font> now(),
    primary key(gallery_id),
    foreign key(wwwuser_id) references wwwusers(id)
);
<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> galleries <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> galleries_gallery_id_seq <font color="#ff6060"><b>to</b></font> nobody;

<font color="#00ffff"><b>-- The actual images stored in the galleries</b></font>
<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>table</b></font> galleries_map (
    gallery_id <font color="#00ff00"><b>integer</b></font> <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,
    album_id <font color="#00ff00"><b>integer</b></font> <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>,
    foreign key(gallery_id) references galleries(gallery_id),
    foreign key(album_id) references album(id)
);
<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>index</b></font> galleries_mapbygal <font color="#ff6060"><b>on</b></font> galleries_map(gallery_id);
<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>index</b></font> galleries_mapbyalbum <font color="#ff6060"><b>on</b></font> galleries_map(album_id);
<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> galleries_map <font color="#ff6060"><b>to</b></font> nobody;

<font color="#00ffff"><b>-- Show the profile users along with the profiles that created them</b></font>
<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>view</b></font> user_byprofiles <font color="#ff6060"><b>as</b></font>
    <font color="#ffff00"><b>select</b></font>  wwwusers.id <font color="#ff6060"><b>as</b></font> user_id, wwwusers.username, wwwusers.realname,
            user_profiles.profile_id <font color="#ff6060"><b>as</b></font> profile,
            user_profiles.description <font color="#ff6060"><b>as</b></font> profile_desc,
            <font color="#00ff00"><b>date</b></font>(ts) <font color="#ff6060"><b>as</b></font> created
        <font color="#ff6060"><b>from</b></font>
            wwwusers, user_profiles, user_profile_log
        <font color="#ff6060"><b>where</b></font>
            wwwusers.id=user_profile_log.wwwuser_id
            <font color="#ffff00"><b>and</b></font> user_profiles.profile_id=user_profile_log.profile_id
        <font color="#ff6060"><b>order</b></font> <font color="#ff6060"><b>by</b></font>
            user_id
;
<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>select</b></font> <font color="#ff6060"><b>on</b></font> user_byprofiles <font color="#ff6060"><b>to</b></font> nobody;

<font color="#00ffff"><b>-- View the profiles</b></font>
<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>view</b></font> user_profile_view <font color="#ff6060"><b>as</b></font>
    <font color="#ffff00"><b>select</b></font> p.name, p.description, p.expires,
            c.name <font color="#ff6060"><b>as</b></font> cat_name
        <font color="#ff6060"><b>from</b></font>
            user_profiles p, cat c, user_profile_acls a
        <font color="#ff6060"><b>where</b></font>
            p.profile_id=a.profile_id
            <font color="#ffff00"><b>and</b></font> c.id=a.cat_id
        <font color="#ff6060"><b>order</b></font> <font color="#ff6060"><b>by</b></font>
            p.expires
;
<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>select</b></font> <font color="#ff6060"><b>on</b></font> user_profile_view <font color="#ff6060"><b>to</b></font> nobody
;

<font color="#00ffff"><b>-- Log view</b></font>
<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>view</b></font> log_user_ip_agent <font color="#ff6060"><b>as</b></font>
    <font color="#ffff00"><b>select</b></font> u.username, l.ts, l.remote_addr, a.user_agent,
        l.extra_info <font color="#ff6060"><b>as</b></font> img_size
    <font color="#ff6060"><b>from</b></font> wwwusers u, photo_logs l, user_agent a, log_types t
    <font color="#ff6060"><b>where</b></font> u.id = l.wwwuser_id <font color="#ffff00"><b>and</b></font>
        a.user_agent_id = l.user_agent
        <font color="#ffff00"><b>and</b></font> l.log_type = t.log_type_id
        <font color="#ffff00"><b>and</b></font> t.log_type =<font color="#ff40ff"><b>'ImgView'</b></font>;
;

<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>select</b></font> <font color="#ff6060"><b>on</b></font> log_user_ip_agent <font color="#ff6060"><b>to</b></font> nobody
;

<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>view</b></font> log_user_ip_keywords <font color="#ff6060"><b>as</b></font>
    <font color="#ffff00"><b>select</b></font> a.id <font color="#ff6060"><b>as</b></font> photo_id, u.username, l.remote_addr, a.keywords, l.ts
        <font color="#ff6060"><b>from</b></font> wwwusers u, photo_logs l, album a
      <font color="#ff6060"><b>where</b></font> u.id = l.wwwuser_id <font color="#ffff00"><b>and</b></font>
        a.id = l.photo_id
;

<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>select</b></font> <font color="#ff6060"><b>on</b></font> log_user_ip_keywords <font color="#ff6060"><b>to</b></font> nobody
;

<font color="#00ffff"><b>-- For viewing auth logs</b></font>
<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>view</b></font> auth_log_view <font color="#ff6060"><b>as</b></font>
    <font color="#ffff00"><b>select</b></font>
        u.username, l.remote_addr, l.ts, t.log_type
    <font color="#ff6060"><b>from</b></font>
        wwwusers u, photo_logs l, log_types t
    <font color="#ff6060"><b>where</b></font>
        u.id=l.wwwuser_id
        <font color="#ffff00"><b>and</b></font> l.log_type = t.log_type_id
        <font color="#ffff00"><b>and</b></font> (t.log_type=<font color="#ff40ff"><b>'Login'</b></font> <font color="#ffff00"><b>or</b></font> t.log_type=<font color="#ff40ff"><b>'AuthFail'</b></font>)
    <font color="#ff6060"><b>order</b></font> <font color="#ff6060"><b>by</b></font>
        l.ts <font color="#ff6060"><b>desc</b></font>
;
<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>select</b></font> <font color="#ff6060"><b>on</b></font> auth_log_view <font color="#ff6060"><b>to</b></font> nobody
;


<font color="#00ffff"><b>-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --</b></font>

<font color="#00ffff"><b>-- MISC QUERIES</b></font>

<font color="#00ffff"><b>-- Garbage collector, unfortunately, this will not work in a view.</b></font>

<font color="#00ffff"><b>-- select distinct id from image_store where id not in</b></font>
<font color="#00ffff"><b>--  (select id from album);</b></font>

<font color="#ffff00"><b>commit</b></font>;
</pre>
</body>
</html>