User Tools

Site Tools


podoc:preferences_sql_table

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

Version 2.33

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;

= Version 2.34 =

-- Fix this broken preference..
UPDATE preferences SET search_default_mode = 1 WHERE search_default_mode IS NULL;
UPDATE preferences SET folder_view = 'slide' WHERE folder_view IS NULL;
podoc/preferences_sql_table.txt · Last modified: 2007/10/19 18:54 by Luud