User Tools

Site Tools


podoc:preferences_sql_table

This is an old revision of the document!


Database Table Description of "preferences"

Name

preferences

Description

Purpose

Schema

CREATE TABLE preferences (
  identifier                INTEGER NOT NULL PRIMARY KEY,
  print_out                 CHAR(3) CHECK (print_out IN ('ps', 'pdf')),
  start_hour                INTEGER CHECK (start_hour >= 0 AND start_hour < 24),
  end_hour                  INTEGER CHECK (start_hour >= 0 AND start_hour < 24),
  start_day                 INTEGER CHECK (start_day IN (1, 0)),
  num_of_rows               INTEGER,
  num_of_columns            INTEGER,
  folder_view               CHAR(5) CHECK (folder_view IN ('slide', 'list')),
  num_of_rows_l             INTEGER,
  bulk_upload_enable        CHAR(1) CHECK (bulk_upload_enable IN ('t', 'f')),
  paper                     INTEGER REFERENCES paper(identifier),
  label                     INTEGER REFERENCES label(identifier),
  shop_enable               CHAR(1) CHECK (shop_enable IN ('t', 'f')),
  popup_enable              CHAR(1) CHECK (shop_enable IN ('t', 'f')),
  popup_delay               INTEGER,
  quota_size                INTEGER,
  quota_count               INTEGER,
  search_enable_stemming    CHAR(1) CHECK (search_enable_stemming IN ('t', 'f')),
  search_default_mode       INTEGER,
  download_counter          INTEGER,
  download_expiry_period    INTEGER
);

References tables

  • label references label(identifier)
  • paper references paper(identifier)

Is referenced by tables

  • users(preferences) references identifier

None.

None.

None.

History

Used first

Version 2.5

Used last

Still in use

Schema change history

= Version 2.5 =

create table preferences (
  identifier            integer not null primary key,
  print_out             char(3) check (print_out in ('ps', 'pdf')),
  start_hour            integer check (start_hour >= 0 and start_hour < 24),
  end_hour              integer check (start_hour >= 0 and start_hour < 24),
  start_day             integer check (start_day in (1, 0)),
  num_of_rows           integer,
  sharpen_image_radius  float,
  sharpen_image_sigma   float,
  sharpen_thumb_radius  float,
  sharpen_thumb_sigma   float,
  num_of_columns        integer,
  copyright_statement   text,
  folder_view           char(5) check (folder_view in ('slide', 'list')),
  num_of_rows_l         integer,
  current_volume        varchar(500),
  volume_max_size       integer
);

= Version 2.6 =

alter table preferences add column copyright_statement_photo text;
alter table preferences add column bulk_upload_enable char(1) check (bulk_upload_enable in ('t', 'f'));
alter table preferences add column paper integer references paper(identifier);
alter table preferences add column label integer references label(identifier);

= Version 2.7 =

-- extend system preferences table with the current volume and upper size limit
alter table system_preferences add column current_volume varchar(500);
alter table system_preferences add column volume_max_size integer;

= Version 2.8 =

ALTER TABLE preferences ADD COLUMN shop_enable  CHAR(1) CHECK (shop_enable IN ('t', 'f'));
ALTER TABLE preferences ADD COLUMN popup_enable CHAR(1) CHECK (shop_enable IN ('t', 'f'));
ALTER TABLE preferences ADD COLUMN popup_delay  INTEGER;

= Version 2.11 =

-- we rename colums first since dropping was implemented only with 7.3
ALTER TABLE preferences RENAME COLUMN sharpen_image_radius TO drop_me_sharpen_image_radius;
ALTER TABLE preferences RENAME COLUMN sharpen_image_sigma  TO drop_me_sharpen_image_sigma;
ALTER TABLE preferences RENAME COLUMN sharpen_thumb_radius TO drop_me_sharpen_thumb_radius;
ALTER TABLE preferences RENAME COLUMN sharpen_thumb_sigma  TO drop_me_sharpen_thumb_sigma;
 
-- drop columns which aren't used any longer
ALTER TABLE preferences DROP COLUMN drop_me_sharpen_image_radius;
ALTER TABLE preferences DROP COLUMN drop_me_sharpen_image_sigma;
ALTER TABLE preferences DROP COLUMN drop_me_sharpen_thumb_radius;
ALTER TABLE preferences DROP COLUMN drop_me_sharpen_thumb_sigma;

= Version 2.13 =

ALTER TABLE preferences ADD quota_size INTEGER;
ALTER TABLE preferences ADD quota_count INTEGER;

= Version 2.16 =

ALTER TABLE preferences ADD search_enable_stemming CHAR(1) CHECK (search_enable_stemming IN ('t', 'f'));
ALTER TABLE preferences ADD search_default_mode  INTEGER;

= Version 2.19 =

ALTER TABLE preferences ADD COLUMN download_counter INTEGER;
ALTER TABLE preferences ADD COLUMN download_expiry_period INTEGER;

= Version 2.21 =

-- remove obsolete rows
ALTER TABLE preferences DROP COLUMN copyright_statement_photo;
ALTER TABLE preferences DROP COLUMN copyright_statement;

= Version 2.34 =

-- Drop obselete user preferences
ALTER TABLE preferences DROP num_of_rows;
ALTER TABLE preferences DROP num_of_columns;
ALTER TABLE preferences DROP num_of_rows_l;
-- DROP TABLE preferences;
Data change history

= Version 2.5 =

INSERT INTO preferences VALUES (1, 'pdf', '8', '20', '1', 4, 4, '', 'slide', '20', 0.2, 0.2, 0.4, 0.4);

= Version 2.6 =

-- set bulk upload flag to false for everybody except the administrator
UPDATE preferences SET bulk_upload_enable='f';
UPDATE preferences SET bulk_upload_enable='t' WHERE identifier = (SELECT preferences FROM users WHERE TYPE=4);
UPDATE preferences SET paper='13';
UPDATE preferences SET label='17';

= Version 2.7 =

UPDATE system_preferences SET volume_max_size=640;

= Version 2.8 =

UPDATE preferences SET shop_enable ='f';
UPDATE preferences SET popup_enable='f';
UPDATE preferences SET popup_delay =500;
UPDATE preferences SET shop_enable ='t' WHERE identifier = (SELECT preferences FROM users WHERE TYPE=4);
UPDATE preferences SET popup_enable='t' WHERE identifier = (SELECT preferences FROM users WHERE TYPE=4);

= Version 2.13 =

-- set all quotas to unlimited
UPDATE preferences SET quota_size = 0;
UPDATE preferences SET quota_count = 0;

= Version 2.16 =

UPDATE preferences SET search_enable_stemming = 'f';
UPDATE preferences SET search_default_mode = 1;

= Version 2.19 =

UPDATE preferences SET download_counter = 10;
UPDATE preferences SET download_expiry_period = 14;

= Version 2.29 =

UPDATE preferences SET download_counter=10 WHERE download_counter IS NULL;
UPDATE preferences SET download_expiry_period=1095 WHERE download_expiry_period IS NULL;
podoc/preferences_sql_table.1192803843.txt.gz · Last modified: 2007/10/19 14:24 by Luud