Image Gallery with Uploads

CREATE TABLE image (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT,
    image_url TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE session (
    id TEXT PRIMARY KEY,
    username TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
    username TEXT PRIMARY KEY,
    password_hash TEXT NOT NULL
);

-- Create default admin user (password: admin)
INSERT INTO users (username, password_hash) 
VALUES ('admin', sqlpage.hash_password('admin'));

select 'shell' as component,
    'My image gallery' as title,
    (
        case when sqlpage.cookie('session_token') is null then 'login'
        else 'logout' end
    ) as menu_item;

select 'card' as component,
    'My image gallery' as title;

select title, description, image_url as top_image
from image;

select 'Your gallery is empty' as title,
    'You have not uploaded any images yet. Click the button below to upload a new image.' as description
where not exists (select 1 from image);

select 'button' as component;
select
    'Upload a new image' as title,
    'upload_form.sql' as link,
    'plus' as icon,
    'primary' as color;

-- Redirect to login if not authenticated
select 'redirect' as component, '/login.sql' as link
where not exists (
    select true from session 
    where sqlpage.cookie('session_token') = id 
    and created_at > datetime('now', '-1 day')
);

-- Display the upload form
select 'form' as component, 
    'Upload a new image' as title, 
    'upload.sql' as action;
    
select 'text' as type, 'Title' as name, true as required;
select 'text' as type, 'Description' as name;
select 'file' as type, 'Image' as name, 'image/*' as accept;

-- Verify authentication
select 'redirect' as component, '/login.sql' as link
where not exists (
    select true from session
    where
        sqlpage.cookie('session_token') = id and
        created_at > datetime('now', '-1 day')
);

-- Validate file type
select 'redirect' as component, '/upload_form.sql' as link
where sqlpage.uploaded_file_mime_type('Image') NOT LIKE 'image/%';

-- Insert the image
insert or ignore into image (title, description, image_url)
values (
    :Title,
    :Description,
    sqlpage.persist_uploaded_file('Image', 'images', 'jpg,jpeg,png,gif')
)
returning 'redirect' as component,
          format('/?created_id=%d', id) as link;

-- Show error if insert failed
select 'alert' as component,
    'red' as color,
    'alert-triangle' as icon,
    'Failed to upload image' as title,
    'Please try again with a smaller picture. Maximum allowed file size is 500Kb.' as description;

image-gallery/
├── sqlpage/
│   └── migrations/
│       ├── 0001_images_table.sql
│       └── 0002_users.sql
├── images/                    # Uploaded images stored here
│   ├── abc123.jpg
│   └── def456.png
├── index.sql                  # Gallery display
├── upload_form.sql           # Upload form (protected)
├── upload.sql                # Upload handler
├── login.sql                 # Login page
└── logout.sql                # Logout handler

{
  "max_uploaded_file_size": 5242880,
  "web_root": ".",
  "database_url": "sqlite://gallery.db"
}