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>