Monday, February 7, 2005

MyPhotoGallery

I'm working on enhancing my photo galleries on my site, and I've made some progress tonight. So far I have set up the database schema, created the databases, threw together a couple of wrapper functions in python, one to hold a database connection, another to Add a user to said database. When I get this up and running, like most everythin on my site, I'll release the code.
CREATE DATABASE MyPhotoGallery;
USE MyPhotoGallery;

CREATE TABLE users (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user VARCHAR(64), 
  email VARCHAR(255),
  password BLOB,
  admin BOOL DEFAULT 0,
  UNIQUE email (email),
  UNIQUE user (user)
);

CREATE TABLE photos (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  photo VARCHAR(255),
  album BIGINT UNSIGNED,
  title VARCHAR(255)
);

CREATE TABLE comments (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  comment TEXT,
  photo BIGINT UNSIGNED
);

CREATE TABLE users_groups (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user BIGINT UNSIGNED,
  group_id BIGINT UNSIGNED
);

CREATE TABLE album_groups (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  album_id BIGINT UNSIGNED,
  group_id BIGINT UNSIGNED
);

CREATE TABLE groups (
  name VARCHAR(64), 
  owner VARCHAR(64),
  password BLOB,
  UNIQUE name (name)
);

CREATE TABLE album (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  album VARCHAR(255), 
  title VARCHAR(255),
  description TEXT,
  owner BIGINT UNSIGNED,
  parent BIGINT UNSIGNED
);