
create table fotolib_user (
  fu_id            integer        not null,
  fu_name          varchar(20)    not null,
  fu_password      varchar(200)   not null,
  fu_level         integer        not null,
  fu_pass_change   integer        not null,
  constraint fotolib_user_pk primary key ( fu_id ),
  constraint fotolib_user_uk unique key ( fu_name )
);

grant select, update on fotolib_user to www;


create table fotolib_tag (
  ft_id            integer        not null,
  ft_kind          integer        not null,
  ft_name          varchar(40)    not null,
  ft_descr         varchar(200)       null,
  ft_timestamp     timestamp(0)   not null,
  constraint fotolib_tag_pk primary key ( ft_id )
);

create index fotolib_tag_name_idx on fotolib_tag
  ( ft_kind, ft_name, ft_descr );

grant select, insert, update, delete on fotolib_tag to www;


create table fotolib_foto (
  ff_id                integer        not null,
  fu_id                integer        not null,
  ff_seq_nr            integer        not null,
  ff_state             integer        not null,
  ff_public            integer        not null,
  ff_filename          varchar(500)   not null,
 --ff_file_ok           integer        not null, -- for clients
  ff_hash              varchar(32)    not null,
  ff_camera            varchar(100)   not null,
  ff_orig_name         varchar(100)   not null,
  ff_timv_width        integer        not null,
  ff_timv_height       integer        not null,
  ff_timv_mtime        integer        not null,
  ff_timv_angle        integer        not null,
  ff_timv_flip         integer        not null,
  ff_exif_latitude     varchar(50)        null,
  ff_exif_longitude    varchar(50)        null,
  ff_exif_date         timestamp(0)   not null,
  ff_date_offset       integer        not null,
  ff_real_date         timestamp(0)   not null,
  ff_disp_date         timestamp(0)   not null,
  ff_comment           varchar(2000)      null,
  ff_timestamp         timestamp(0)   not null,
  constraint fotolib_foto_pk primary key ( ff_id ),
  constraint fotolib_foto_uk unique key ( fu_id, ff_seq_nr )
);

grant select, insert, update, delete on fotolib_foto to www;



create table fotolib_foto_tag (
  ff_id            integer        not null,
  ft_id            integer        not null,
  fft_timestamp    timestamp(0)   not null,
  constraint fotolib_foto_tag_pk primary key ( ff_id, ft_id )
);

create index fotolib_foto_tag_idx on fotolib_foto_tag ( ft_id, ff_id );

grant select, insert, update, delete on fotolib_foto_tag to www;


