📄 photo.sql.html
字号:
<html><head><title>~/prog/eprojects/photoservlet/etc/photo.sql.html</title><meta name="Generator" content="Vim/6.1"></head><body bgcolor="#000000" text="#ffffff"><pre><font color="#00ffff"><b>-- Copyright (c) 1998 Dustin Sallings</b></font><font color="#00ffff"><b>--</b></font><font color="#00ffff"><b>-- $Id: photo.sql,v 1.29 2002/06/30 07:51:31 dustin Exp $</b></font><font color="#00ffff"><b>--</b></font><font color="#00ffff"><b>-- Use this to bootstrap your SQL database to do cool shite with the</b></font><font color="#00ffff"><b>-- photo album.</b></font><font color="#ff6060"><b>begin</b></font> transaction;<font color="#00ffff"><b>-- add support for PL/pgsql</b></font><font color="#ffff00"><b>CREATE</b></font> <font color="#ff6060"><b>FUNCTION</b></font> plpgsql_call_handler () RETURNS OPAQUE <font color="#ff6060"><b>AS</b></font> <font color="#ff40ff"><b>'/usr/local/pgsql/lib/plpgsql.so'</b></font> LANGUAGE <font color="#ff40ff"><b>'C'</b></font>;<font color="#ffff00"><b>CREATE</b></font> TRUSTED PROCEDURAL LANGUAGE <font color="#ff40ff"><b>'plpgsql'</b></font> HANDLER plpgsql_call_handler LANCOMPILER <font color="#ff40ff"><b>'PL/pgSQL'</b></font>;<font color="#00ffff"><b>-- The categories</b></font><font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>table</b></font> cat( id serial, name text <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>, primary key(id));<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> cat <font color="#ff6060"><b>to</b></font> nobody;<font color="#00ffff"><b>-- implicit sequence</b></font><font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> cat_id_seq <font color="#ff6060"><b>to</b></font> nobody;<font color="#00ffff"><b>-- Users go here</b></font><font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>table</b></font> wwwusers( id serial, username <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>, password text <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>, email text <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>, realname text <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>, canadd bool <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>, primary key(id));<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>unique</b></font> <font color="#ff6060"><b>index</b></font> user_byname <font color="#ff6060"><b>on</b></font> wwwusers(username);<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>unique</b></font> <font color="#ff6060"><b>index</b></font> user_byemail <font color="#ff6060"><b>on</b></font> wwwusers(email);<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> wwwusers <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> wwwusers_id_seq <font color="#ff6060"><b>to</b></font> nobody;<font color="#00ffff"><b>-- add guest and admin users</b></font><font color="#ffff00"><b>insert</b></font> <font color="#ff6060"><b>into</b></font> wwwusers(username, password, email, realname, canadd) <font color="#ff6060"><b>values</b></font>(<font color="#ff40ff"><b>'guest'</b></font>, <font color="#ff40ff"><b>''</b></font>, <font color="#ff40ff"><b>'photos@localhost'</b></font>, <font color="#ff40ff"><b>'Guest User'</b></font>, <font color="#ff6060"><b>false</b></font>);<font color="#00ffff"><b>-- Default password for admin is ``admin''</b></font><font color="#ffff00"><b>insert</b></font> <font color="#ff6060"><b>into</b></font> wwwusers(username, password, email, realname, canadd) <font color="#ff6060"><b>values</b></font>(<font color="#ff40ff"><b>'admin'</b></font>, <font color="#ff40ff"><b>'0DPiKuNIrrVmD8IUCuw1hQxNqZc'</b></font>, <font color="#ff40ff"><b>'photoadmin@localhost'</b></font>, <font color="#ff40ff"><b>'Admin User'</b></font>, <font color="#ff6060"><b>true</b></font>);<font color="#00ffff"><b>-- get a user ID from a username</b></font><font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>function</b></font> getwwwuser(text) returns <font color="#00ff00"><b>integer</b></font> <font color="#ff6060"><b>as</b></font> <font color="#ff40ff"><b>'select id from wwwusers where username = $1'</b></font> language <font color="#ff40ff"><b>'sql'</b></font>;<font color="#00ffff"><b>-- Where the picture info is stored.</b></font><font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>table</b></font> album( keywords <font color="#00ff00"><b>varchar</b></font>(<font color="#ff40ff"><b>50</b></font>) <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>, descr text <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>, cat <font color="#00ff00"><b>integer</b></font> <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>, taken <font color="#00ff00"><b>date</b></font> <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>, <font color="#ff6060"><b>size</b></font> <font color="#00ff00"><b>integer</b></font> <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>, width <font color="#00ff00"><b>integer</b></font> <font color="#ff6060"><b>default</b></font> <font color="#ff40ff"><b>0</b></font>, height <font color="#00ff00"><b>integer</b></font> <font color="#ff6060"><b>default</b></font> <font color="#ff40ff"><b>0</b></font>, ts datetime <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>, id serial, primary key(id), foreign key(cat) references cat(id), foreign key(addedby) references wwwusers(id));<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>index</b></font> album_bycat <font color="#ff6060"><b>on</b></font> album(cat);<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> album <font color="#ff6060"><b>to</b></font> nobody;<font color="#00ffff"><b>-- implicit sequence</b></font><font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> album_id_seq <font color="#ff6060"><b>to</b></font> nobody;<font color="#00ffff"><b>-- Notes</b></font><font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>table</b></font> commentary ( comment_id serial, 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> <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>, note text <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>, remote_addr inet <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(comment_id), foreign key(wwwuser_id) references wwwusers(id), foreign key(photo_id) references album(id));<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>index</b></font> commentary_byphoto <font color="#ff6060"><b>on</b></font> commentary(photo_id);<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>index</b></font> commentary_byuser <font color="#ff6060"><b>on</b></font> commentary(wwwuser_id);<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> commentary <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> commentary_comment_id_seq <font color="#ff6060"><b>to</b></font> nobody;<font color="#00ffff"><b>-- Get the latest date a comment was submitted for a given photo</b></font><font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>function</b></font> latestcomment(<font color="#00ff00"><b>integer</b></font>) returns timestamp <font color="#ff6060"><b>as</b></font> <font color="#ff40ff"><b>'select max(ts) from commentary where photo_id = $1'</b></font> language <font color="#ff40ff"><b>'sql'</b></font>;<font color="#00ffff"><b>-- Votes</b></font><font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>table</b></font> votes ( vote_id serial, 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> <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>, vote <font color="#ff6060"><b>smallint</b></font> <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>, remote_addr inet <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(vote_id), foreign key(wwwuser_id) references wwwusers(id), foreign key(photo_id) references album(id));<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>unique</b></font> <font color="#ff6060"><b>index</b></font> votes_byui <font color="#ff6060"><b>on</b></font> votes(wwwuser_id, photo_id);<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>index</b></font> votes_byphoto <font color="#ff6060"><b>on</b></font> votes(photo_id);<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>index</b></font> votes_byuser <font color="#ff6060"><b>on</b></font> votes(wwwuser_id);<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> votes <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> votes_vote_id_seq <font color="#ff6060"><b>to</b></font> nobody;<font color="#00ffff"><b>-- The ACLs for the categories</b></font><font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>table</b></font> wwwacl( userid <font color="#00ff00"><b>integer</b></font> <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>, cat <font color="#00ff00"><b>integer</b></font> <font color="#ffff00"><b>not</b></font> <font color="#ff6060"><b>null</b></font>, canview <font color="#00ff00"><b>boolean</b></font> <font color="#ff6060"><b>default</b></font> <font color="#ff6060"><b>true</b></font>, canadd <font color="#00ff00"><b>boolean</b></font> <font color="#ff6060"><b>default</b></font> <font color="#ff6060"><b>false</b></font>, foreign key(userid) references wwwusers(id), foreign key(cat) references cat(id));<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>index</b></font> acl_byid <font color="#ff6060"><b>on</b></font> wwwacl(userid);<font color="#ffff00"><b>create</b></font> <font color="#ff6060"><b>index</b></font> acl_bycat <font color="#ff6060"><b>on</b></font> wwwacl(cat);<font color="#ffff00"><b>grant</b></font> <font color="#ffff00"><b>all</b></font> <font color="#ff6060"><b>on</b></font> wwwacl <font color="#ff6060"><b>to</b></font> nobody;<font color="#00ffff"><b>-- view for showing acls by name</b></font>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -