diff --git a/docs/database.dia b/docs/database.dia index d310eec..d5f6afe 100644 --- a/docs/database.dia +++ b/docs/database.dia Binary files differ diff --git a/docs/photos.md b/docs/photos.md new file mode 100644 index 0000000..8cf9791 --- /dev/null +++ b/docs/photos.md @@ -0,0 +1,75 @@ +# Photos +The photos subsystem organizes photos for viewing on my website. +It adds a roll of photos as a page on the website. +A roll is a list of photos, and each photo can be tagged with photo tags, for like searchability and stuff. + +## New Page Types + +### Roll Page +The roll page displays a roll of images to select them for viewing. +It has the following elements + + - Roll Title + - Author Name + - Description + - Photos, each with a photograph, description, and some photo tags + +I may also enable tagging rolls by the post tags and displaying rolls in post lists. + +URL Format: /roll/{roll-url}[&pwd={roll-password}] + +Security Constraints: + - Deny access to private rolls + - request and validate the password for password restricted rolls + - Hide photos which are tagged as private + +### Photo Page +The photo page displays a single photo, its tags and its description, and maybe some similarly tagged photos. + +The photo has a caption, to describe the image, and a narrative which contextualizes the image. +The caption goes in the `alt=""` or `
` tag, while the narritive may be shown below the image. + +Page URL Format: /roll/{roll-url}/{photo-sequence}.html[&pwd={roll-password}] +Photo URL Format /photo/{photo-id}.{ext}[&pwd={roll-password}] + +Security Constraints: + - Deny access for any private or password protected roll unless the roll's password is provided, or the image is tagged with a searchable tag + +### Photo Tag Search +The photo tag page lists images tagged with a particular tag or set of tags. + +URL Format: /photosearch#t0={tagname}&t1={tagname}... + +Security Constraints: + - show photos only from public rolls or searchable tags + +### Roll List +The Roll list displays and searches all the rolls, enabling a user to browse and select a roll to view. +Each roll on the roll list shows thumbnails from itself. + +URL Format: /photorolls/[{tags or something}] + +Security Constraints: + - show only public rolls + - Hide any thumbnails for private images. + +### Management +There will be a management interface to list all rolls, and to make a new or edit a roll. +Also a management interface to edit a photo tag. + +## Metadata & Visibility +Photo visibility is an important thing, and I'll put in two mechanisms for visibility. +A roll has a visibility affecting the entire roll, and a photo tag has a visibility affecting the visibility of all photos tagged with it. + +The roll visibility has the following levels + +1. Private: never veiwable by anyone besides an author +2. Password: visible to anyone with the password +3. Hidden: visible to anyone with its URL, but not navigable or searchable +4. Public: Navigable and searchable + +The tag visibility has the following levels + +1. Searchable: visible in the tag search, even if the roll is private +3. Normal: visibility reverts to that of the photo's roll +4. Private: inaccessible even if the roll is public diff --git a/src/main/java/kim/redbow/web/blog/filters/Authenticator.java b/src/main/java/kim/redbow/web/blog/filters/Authenticator.java index e856cb5..85a95a6 100644 --- a/src/main/java/kim/redbow/web/blog/filters/Authenticator.java +++ b/src/main/java/kim/redbow/web/blog/filters/Authenticator.java @@ -31,6 +31,8 @@ { public static String LOGIN_ATTRIBUTE = "login"; String unauthRedirect; + + @Override public void init(FilterConfig fc) { this.unauthRedirect = fc.getInitParameter("unauthRedirect"); @@ -40,6 +42,7 @@ } } + @Override public void doFilter(ServletRequest rq, ServletResponse rp, FilterChain fc) throws IOException, ServletException { @@ -73,5 +76,6 @@ } } + @Override public void destroy() { } } diff --git a/src/main/java/kim/redbow/web/blog/filters/HeaderFilter.java b/src/main/java/kim/redbow/web/blog/filters/HeaderFilter.java index 2977420..b94e57f 100644 --- a/src/main/java/kim/redbow/web/blog/filters/HeaderFilter.java +++ b/src/main/java/kim/redbow/web/blog/filters/HeaderFilter.java @@ -31,6 +31,8 @@ { private ArrayList headerNames; private ArrayList headerVals; + + @Override public void init(FilterConfig fc) { this.headerNames = new ArrayList(); @@ -44,6 +46,7 @@ } } + @Override public void doFilter(ServletRequest rq, ServletResponse rp, FilterChain fc) throws IOException, ServletException { @@ -71,5 +74,6 @@ fc.doFilter(request, response); } + @Override public void destroy() { } } diff --git a/src/main/java/kim/redbow/web/blog/filters/SQLConnFilter.java b/src/main/java/kim/redbow/web/blog/filters/SQLConnFilter.java index c10d2da..fe35dd4 100644 --- a/src/main/java/kim/redbow/web/blog/filters/SQLConnFilter.java +++ b/src/main/java/kim/redbow/web/blog/filters/SQLConnFilter.java @@ -39,6 +39,7 @@ DataSource source; + @Override public void init(FilterConfig fc) throws ServletException { String ds_name = fc.getInitParameter("datasource_name"); @@ -54,6 +55,7 @@ } } + @Override public void doFilter(ServletRequest rq, ServletResponse rp, FilterChain fc) throws IOException, ServletException { @@ -102,6 +104,7 @@ } } + @Override public void destroy() { } public static Connection getConnection(HttpServletRequest req) diff --git a/src/main/java/kim/redbow/web/blog/queries/Photo.java b/src/main/java/kim/redbow/web/blog/queries/Photo.java new file mode 100644 index 0000000..62cdc7c --- /dev/null +++ b/src/main/java/kim/redbow/web/blog/queries/Photo.java @@ -0,0 +1,199 @@ +// Copyright 2025 Kimberlee Model +// +// Licensed under the Apache License, Version 2.0 (the "License"); +// you may not use this file except in compliance with the License. +// You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, software +// distributed under the License is distributed on an "AS IS" BASIS, +// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +// See the License for the specific language governing permissions and +// limitations under the License. + +package kim.redbow.web.blog.queries; + +import java.util.ArrayList; +import java.io.InputStream; +import java.io.OutputStream; +import java.io.IOException; + +import java.sql.Connection; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Timestamp; + +import kim.redbow.web.blog.Query; +import kim.redbow.web.blog.Util; + +/** + * The Photos table in the database. + * + * A photo has a blob with the photo and some metadata. The photo is + * accessed through streams in the addPhoto and sendPhoto methods, while + * the other data is accessed through attributes. + */ +public class Photo +{ + public int id = -1; + public int sequence = -1; + public int roll = -1; + public String caption = null; + public String narrative = null; + public Timestamp upload = null; + public String mimetype = null; + public String extension = null; + + /** + * Insert the metadata of a new photo into the database. + * + * Id and upload will be generated by the database. Sequence, roll, + * mimetype, and extension must be provided, while caption and narrative + * are nullable. + */ + public void insert(Connection conn) throws SQLException + { + Query q = new Query(conn, "INSERT INTO photos (sequence, roll, caption, narrative, upload, mimetype, extension) VALUES (?, ?, ?, ?, NOW(), ?, ?)"); + q.param(1, this.sequence); + q.param(2, this.roll); + q.param(3, this.caption); + q.param(4, this.narrative); + q.param(5, this.mimetype); + q.param(6, this.extension); + + try { q.execute(); } finally { q.close(); } + + q = new Query(conn, "SELECT id, upload FROM photos WHERE roll = ? AND sequence = ?"); + q.param(1, this.roll); + q.param(2, this.sequence); + ResultSet rs = q.getResults(); + + try + { + rs.first(); + this.id = rs.getInt(1); + this.upload = rs.getTimestamp(2); + } + finally { rs.close(); q.close(); } + } + + /** + * Upload the photo data itself into the database. + */ + public void addPhoto(Connection conn, InputStream ish) throws SQLException + { + Query q = new Query(conn, "UPDATE photos SET upload = NOW(), fullsize = ? WHERE id = ?"); + q.param(1, ish); + q.param(2, this.id); + + try { q.execute(); } finally { q.close(); } + + q = new Query(conn, "SELECT upload FROM photos WHERE id = ?"); + q.param(1, this.id); + ResultSet rs = q.getResults(); + try { rs.first(); this.upload = rs.getTimestamp(1); } + finally { rs.close(); q.close(); } + } + + private static String SELECT = "SELECT photos.id, photos.sequence, photos.roll, photos.caption, photos.narrative, photos.upload, photos.mimetype, photos.extension FROM photos"; + + private static Photo readResults(ResultSet rs) throws SQLException + { + Photo p = new Photo(); + p.id = rs.getInt(1); + p.sequence = rs.getInt(2); + p.roll = rs.getInt(3); + p.caption = rs.getString(4); + p.narrative = rs.getString(5); + p.upload = rs.getTimestamp(6); + p.mimetype = rs.getString(7); + p.extension = rs.getString(8); + return p; + } + + public static ArrayList byRoll(Connection conn, int roll) + throws SQLException + { + Query q = new Query(conn, SELECT + " WHERE photos.roll = ? ORDER BY photos.sequence ASC"); + + q.param(1, roll); + ResultSet rs = q.getResults(); + + ArrayList ret = new ArrayList(); + try + { + while(rs.next()) + { + ret.add(Photo.readResults(rs)); + } + } + catch(SQLException e) { throw e; } + finally { rs.close(); q.close(); } + + return ret; + } + + public static class PhotoTag + { + int id; + String name; + String description; + int visibility; + + public PhotoTag(int i, String n, String d, int v) + { + this.id = i; + this.name = n; + this.description = d; + this.visibility = v; + } + } + + /** + * Find image visibility authentication information given it's id and + * extension. + */ + public static class PhotoAuth + { + int id; + private ArrayList tags; + private Roll roll; + + public PhotoAuth(int i, ArrayList t, Roll r) + { + this.id = i; + this.tags = t; + this.roll = r; + } + + public boolean validate(Connection conn, String pwd, Login login) + { + /* TODO */ + return false; + } + } + + public static PhotoAuth ByIdExt(Connection conn, int id, String ext) + throws SQLException + { + Query q = new Query(conn, "SELECT phototags.id, phototags.name, phototags.visibility FROM phototags LEFT JOIN photodescribes ON phototags.id = photodescribes.tag LEFT JOIN photos ON photodescribes.photo = photos.id WHERE photos.id=? AND photos.extension=?"); + q.param(1, id); + q.param(2, ext); + + ArrayList tags = new ArrayList(); + ResultSet rs = q.getResults(); + + try + { + while(rs.next()) + { + tags.add(new PhotoTag(rs.getInt(1), + rs.getString(2), "", rs.getInt(4))); + } + } + finally { rs.close(); q.close(); } + + return new PhotoAuth(id, tags, Roll.byPhotoId(conn, id)); + } +} diff --git a/src/main/java/kim/redbow/web/blog/queries/Roll.java b/src/main/java/kim/redbow/web/blog/queries/Roll.java new file mode 100644 index 0000000..9c17c5a --- /dev/null +++ b/src/main/java/kim/redbow/web/blog/queries/Roll.java @@ -0,0 +1,174 @@ +// Copyright 2019 Kimberlee Model +// +// Licensed under the Apache License, Version 2.0 (the "License"); +// you may not use this file except in compliance with the License. +// You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, software +// distributed under the License is distributed on an "AS IS" BASIS, +// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +// See the License for the specific language governing permissions and +// limitations under the License. + +package kim.redbow.web.blog.queries; + +import java.util.ArrayList; +import java.io.InputStream; +import java.io.OutputStream; +import java.io.IOException; + +import java.sql.Connection; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Timestamp; + +import kim.redbow.web.blog.Query; +import kim.redbow.web.blog.Util; + +/** + * The Roll table in the database. + * + * The roll table is metadata for a roll of images. It has an author, title, + * description, timestamp, and visibility/permission information. + */ +public class Roll +{ + public static final int VIS_PRIVATE = 0; + public static final int VIS_PASSWORD = 1; + public static final int VIS_HIDDEN = 2; + public static final int VIS_PUBLIC = 3; + + public static String VisString(int vis) + { + if(vis == VIS_PRIVATE) { return "private"; } + if(vis == VIS_PASSWORD) { return "password"; } + if(vis == VIS_HIDDEN) { return "hidden"; } + if(vis == VIS_PUBLIC) { return "public"; } + return "invalid"; + } + + public int id = -1; + public String urlfragment = ""; + public int author = -1; + public String name = ""; + public Timestamp upload = null; + public String description = null; + public int visibility = VIS_PRIVATE; + public String password = null; + + /** + * Insert a new Roll into the database. id and upload will be autoassigned, + * but urlfragment, author, name, description, visibility, and password + * (if used) must be provided. + */ + public void insert(Connection conn) throws SQLException + { + // Do the insert + Query q = new Query(conn, "INSERT INTO rolls (urlfragment, author, name, upload, description, visibility, password) VALUES (?, ?, ?, NOW(), ?, ?, ?)"); + q.param(1, this.urlfragment); + q.param(2, this.author); + q.param(3, this.name); + q.param(4, this.description); + q.param(5, this.visibility); + q.param(6, this.password); + + try { q.execute(); } finally { q.close(); } + + // Retrieve the generated id and upload time. + q = new Query(conn, "SELECT id, upload FROM rolls WHERE urlfragment = ?"); + q.param(1, this.urlfragment); + + ResultSet rs = q.getResults(); + try + { + rs.first(); + this.id = rs.getInt(1); + this.upload = rs.getTimestamp(2); + } + finally { rs.close(); q.close(); } + } + + private static final String SELECT = "SELECT rolls.id, rolls.urlfragment, rolls.author, rolls.name, rolls.upload, rolls.description, rolls.visibility, rolls.password FROM rolls"; + + // Read one record from a result stet + private static Roll readResults(ResultSet rs) throws SQLException + { + Roll r = new Roll(); + r.id = rs.getInt(1); + r.urlfragment = rs.getString(2); + r.author = rs.getInt(3); + r.name = rs.getString(4); + r.upload = rs.getTimestamp(5); + r.description = rs.getString(6); + r.visibility = rs.getInt(7); + r.password = rs.getString(8); + return r; + } + + /** + * Find a roll by the ID of one of its photos. + */ + public static Roll byPhotoId(Connection conn, int photo) + throws SQLException + { + Query q = new Query(conn, SELECT + " LEFT JOIN photos ON photos.roll = rolls.id WHERE photos.id = ?"); + q.param(1, photo); + ResultSet rs = q.getResults(); + Roll ret = null; + + try + { + rs.first(); + ret = Roll.readResults(rs); + } + finally { q.close(); } + + return ret; + } + + /** + * Find a roll by its ID. + */ + public static Roll byId(Connection conn, int id) + throws SQLException + { + Query q = new Query(conn, SELECT + " WHERE rolls.id = ?"); + q.param(1, id); + ResultSet rs = q.getResults(); + Roll ret = null; + + try + { + rs.first(); + ret = Roll.readResults(rs); + } + finally { q.close(); } + + return ret; + } + + /** + * Find all of the rolls. + */ + public static ArrayList all(Connection conn) throws SQLException + { + Query q = new Query(conn, SELECT + " ORDER BY rolls.upload ASC"); + + ResultSet rs = q.getResults(); + + ArrayList ret = new ArrayList(); + try + { + while(rs.next()) + { + ret.add(Roll.readResults(rs)); + } + } + catch(SQLException e) { throw e; } + finally { rs.close(); q.close(); } + + return ret; + } +} diff --git a/src/main/java/kim/redbow/web/blog/servlets/EditPhotoRoll.java b/src/main/java/kim/redbow/web/blog/servlets/EditPhotoRoll.java new file mode 100644 index 0000000..d693409 --- /dev/null +++ b/src/main/java/kim/redbow/web/blog/servlets/EditPhotoRoll.java @@ -0,0 +1,185 @@ +// Copyright 2025 Kimberlee Model +// +// +// Licensed under the Apache License, Version 2.0 (the "License"); +// you may not use this file except in compliance with the License. +// You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, software +// distributed under the License is distributed on an "AS IS" BASIS, +// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +// See the License for the specific language governing permissions and +// limitations under the License. + +package kim.redbow.web.blog.servlet; + +import kim.redbow.web.blog.Query; +import kim.redbow.web.blog.LoggingServlet; +import kim.redbow.web.blog.Util; +import kim.redbow.web.blog.filters.SQLConnFilter; +import kim.redbow.web.blog.queries.Author; +import kim.redbow.web.blog.queries.Roll; +import kim.redbow.web.blog.queries.Photo; + +import javax.servlet.annotation.WebServlet; +import javax.servlet.annotation.MultipartConfig; +import javax.servlet.http.HttpServletRequest; +import javax.servlet.http.HttpServletResponse; +import javax.servlet.http.Part; +import javax.servlet.ServletException; +import javax.naming.NamingException; + +import org.apache.logging.log4j.LogManager; +import org.apache.logging.log4j.Logger; + +import java.sql.Connection; +import java.sql.SQLException; + +import java.util.ArrayList; +import java.io.InputStream; +import java.io.IOException; + +import javax.mail.internet.ContentDisposition; +import javax.mail.internet.ParseException; + +@WebServlet("/manage/editphotoroll/*") +@MultipartConfig +public class EditPhotoRoll extends LoggingServlet +{ + private static Logger log = LogManager.getLogger(EditPhotoRoll.class); + + /** + * This servlet handles several actions regarding photo rolls. It has + * several actions it can perform. + * + * - action=new creates a new post + */ + @Override + protected void logPost(HttpServletRequest request, + HttpServletResponse response) + throws ServletException, IOException, SQLException + { + if(request.getContentType() != null + && !request.getContentType().contains("multipart/form-data")) + { + throw new ServletException( + "expected content type: multipart/form-data, got " + + request.getContentType()); + } + + String action = request.getPathInfo(); + Connection conn = SQLConnFilter.getConnection(request); + + if("/newroll".equals(action)) + { + this.NewRoll(request, response, conn); + } + else + { + throw new ServletException("Unknown photo roll action: " + action); + } + } + + private void NewRoll(HttpServletRequest request, + HttpServletResponse response, Connection conn) + throws ServletException, IOException, SQLException + { + Roll roll = new Roll(); + ArrayList photos = new ArrayList(); + ArrayList ishs = new ArrayList(); + for(Part part : request.getParts()) + { + ContentDisposition cd = null; + try + { + cd = new ContentDisposition( + part.getHeader("content-disposition")); + } + catch(ParseException pe) { throw new ServletException(pe); } + + String name = cd.getParameter("name"); + if("urlfragment".equals(name)) + { + roll.urlfragment = Util.readToString(part.getInputStream()); + } + else if("author".equals(name)) + { + roll.author = Author.byUsername(conn, + Util.readToString(part.getInputStream()), false).id; + } + else if("name".equals(name)) + { + roll.name = Util.readToString(part.getInputStream()); + } + else if("description".equals(name)) + { + roll.description = Util.readToString(part.getInputStream()); + } + else if("visibility".equals(name)) + { + try + { + roll.visibility = Integer.valueOf( + Util.readToString(part.getInputStream())); + } + catch(NumberFormatException nfe) + { + throw new ServletException(nfe); + } + } + else if("password".equals(name)) + { + roll.password = Util.readToString(part.getInputStream()); + } + else + { + Photo photo = new Photo(); + photo.sequence = photos.size(); + photo.mimetype = part.getHeader("content-type"); + String filename = cd.getParameter("filename"); + if(photo.mimetype == null || filename == null + || !filename.contains(".")) + { + throw new ServletException( + "Missing mimetype, filename, or extension"); + } + String[] ext_parts = filename.split("\\."); + photo.extension = ext_parts[ext_parts.length - 1]; + + photos.add(photo); + ishs.add(part.getInputStream()); + } + } + + int visibility = roll.visibility; + if(visibility != Roll.VIS_PRIVATE && visibility != Roll.VIS_PASSWORD + && visibility != Roll.VIS_HIDDEN && visibility != Roll.VIS_PUBLIC) + { + throw new ServletException("Visibility not recognized"); + } + + roll.insert(conn); + log.info("Added new roll urlfragment={}", roll.urlfragment); + + for(int i = 0; i < photos.size(); i++) + { + Photo photo = photos.get(i); + InputStream ish = ishs.get(i); + + photo.roll = roll.id; + + photo.insert(conn); + photo.addPhoto(conn, ish); + + log.info("add photo to roll urlfragment={}, sequence={}", + roll.urlfragment, photo.sequence); + } + + StringBuilder sb = new StringBuilder(request.getContextPath()); + sb.append("/manage/editphotoroll.jsp?action=edit&roll="); + sb.append(roll.id); + response.sendRedirect(sb.toString()); + } +} diff --git a/src/main/java/kim/redbow/web/blog/servlets/PhotoServlet.java b/src/main/java/kim/redbow/web/blog/servlets/PhotoServlet.java new file mode 100644 index 0000000..9aeccb3 --- /dev/null +++ b/src/main/java/kim/redbow/web/blog/servlets/PhotoServlet.java @@ -0,0 +1,82 @@ +// Copyright 2019 Kimberlee Model +// +// Licensed under the Apache License, Version 2.0 (the "License"); +// you may not use this file except in compliance with the License. +// You may obtain a copy of the License at +// +// http://www.apache.org/licenses/LICENSE-2.0 +// +// Unless required by applicable law or agreed to in writing, software +// distributed under the License is distributed on an "AS IS" BASIS, +// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +// See the License for the specific language governing permissions and +// limitations under the License. + +package kim.redbow.web.blog.servlet; + +import kim.redbow.web.blog.LoggingServlet; +import kim.redbow.web.blog.Query; +import kim.redbow.web.blog.Util; +import kim.redbow.web.blog.queries.Photo; +import kim.redbow.web.blog.filters.SQLConnFilter; + +import javax.servlet.http.HttpServlet; +import javax.servlet.http.HttpServletRequest; +import javax.servlet.http.HttpServletResponse; +import javax.servlet.annotation.WebServlet; + +import javax.servlet.ServletException; +import javax.naming.NamingException; + +import org.apache.logging.log4j.LogManager; +import org.apache.logging.log4j.Logger; + +import java.sql.Connection; +import java.sql.SQLException; + +import java.io.InputStream; +import java.io.IOException; +import java.util.Date; + +@WebServlet("/photo/*") +public class PhotoServlet extends LoggingServlet +{ + private static Logger log = LogManager.getLogger(MediaServlet.class); + + @Override + protected void logGet(HttpServletRequest request, + HttpServletResponse response) + throws IOException, ServletException, SQLException + { + Connection conn = SQLConnFilter.getConnection(request); + + log.info("Photo URI path: {}", request.getRequestURI()); + log.info("Photo Context path: {}", request.getContextPath()); + log.info("Photo Servlet path: {}", request.getServletPath()); + log.info("Photo pwd parameter: {}", request.getParameter("pwd")); + + String[] img_parts = request.getRequestURI() + .replaceFirst( + request.getContextPath() + request.getServletPath() + "/", "") + .split("\\."); + + if(img_parts.length == 2) + { + log.info("image id: {}", img_parts[0]); + log.info("image ext: {}", img_parts[1]); + + int id = -1; + try { id = Integer.valueOf(img_parts[0]); } + catch(NumberFormatException nfe) + { + throw new ServletException(nfe); + } + + Photo.PhotoAuth pa = Photo.ByIdExt(conn, id, img_parts[1]); + + response.sendError(500); + } + + response.sendError(404); + } +} diff --git a/src/main/sql/photos.sql b/src/main/sql/photos.sql new file mode 100644 index 0000000..fc6f259 --- /dev/null +++ b/src/main/sql/photos.sql @@ -0,0 +1,58 @@ +-- Copyright 2024-2025 Kimberlee Model +-- +-- Licensed under the Apache License, Version 2.0 (the "License"); +-- you may not use this file except in compliance with the License. +-- You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. + +CREATE TABLE rolls +( + id INTEGER PRIMARY KEY AUTO_INCREMENT, + urlfragment VARCHAR(160) NOT NULL UNIQUE, + author INTEGER NOT NULL, + name VARCHAR(512), + upload TIMESTAMP NOT NULL, + description VARCHAR(2048), + visibility INTEGER NOT NULL, + password VARCHAR(70) NOT NULL, + FOREIGN KEY(author) REFERENCES authors(id) +); + +CREATE TABLE photos +( + id INTEGER PRIMARY KEY AUTO_INCREMENT, + sequence INTEGER NOT NULL, + roll INTEGER NOT NULL, + fullsize LONGBLOB, + caption VARCHAR(1024), + narrative VARCHAR(2048), + upload TIMESTAMP NOT NULL, + mimetype VARCHAR(160) NOT NULL, + extension CHAR(5) NOT NULL, + FOREIGN KEY(roll) REFERENCES rolls(id), + CONSTRAINT UNIQUE (roll, sequence) +); + +CREATE TABLE phototags +( + id INTEGER PRIMARY KEY AUTO_INCREMENT, + name VARCHAR(512) NOT NULL, + description VARCHAR(4096), + visibility INTEGER NOT NULL +); + +CREATE TABLE photodescribes +( + photo INTEGER, + tag INTEGER, + CONSTRAINT PRIMARY KEY(photo, tag), + FOREIGN KEY(photo) REFERENCES photos(id), + FOREIGN KEY(tag) REFERENCES phototags(id) +); diff --git a/src/main/webapp/WEB-INF/header.jsp b/src/main/webapp/WEB-INF/header.jsp index 702cf26..7287862 100644 --- a/src/main/webapp/WEB-INF/header.jsp +++ b/src/main/webapp/WEB-INF/header.jsp @@ -22,7 +22,7 @@