#
# Update wikindx v2 database
#
# Use this to add new fields to the wikindx database and
# don't forget to change the dbVersion no. in INIT so that
# SYSTEMCHECK::updateDatabase() will transparently update 
# the database....
#
# There is NO error checking in SYSTEMCHECK.php as these update statements are run.  We don't want 
# an ALTER statement for example to crash the code because that column name already exists or not.
# Not having error checking allows such failing update statements to be quietly ignored meaning we can 
# store several generations of DB updates in this file and at whatever point a user upgrades WIKINDX from, 
# the DB update code will work. (Hopefully.)
#
# Any changes you add here must also be reflected in createMySQL.txt for those who are installing from scratch.
#

##########################################################

# 2.0.1
# Adding page ranges to quotes, paraphrases and musings
ALTER TABLE `WKX_resource_quote` CHANGE COLUMN `page` `page_start` VARCHAR(10) default NULL;
ALTER TABLE `WKX_resource_quote` ADD COLUMN `page_end` VARCHAR(10) default NULL;
ALTER TABLE `WKX_resource_paraphrase` CHANGE COLUMN `page` `page_start` VARCHAR(10) default NULL;
ALTER TABLE `WKX_resource_paraphrase` ADD COLUMN `page_end` VARCHAR(10) default NULL;
ALTER TABLE `WKX_resource_musing` ADD COLUMN `page_start` VARCHAR(10) default NULL;
ALTER TABLE `WKX_resource_musing` ADD COLUMN `page_end` VARCHAR(10) default NULL;

##########################################################

# Adding WKX_bibtex_string table
# `id` references `WKX_resource.id`
CREATE TABLE IF NOT EXISTS `WKX_bibtex_string` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `text` text NOT NULL,
  PRIMARY KEY  (`id`)
);

##########################################################

# 2.1.0
# adding isbn field to WKX_resource
ALTER TABLE `WKX_resource` ADD COLUMN `isbn` VARCHAR(255) default NULL;
# adding prefix field to WKX_creator
ALTER TABLE `WKX_creator` ADD COLUMN `prefix` varchar(255) default NULL;
# adding multiUser and userRegistration fields to WKX_config
ALTER TABLE `WKX_config` ADD COLUMN `multiUser` enum('N', 'Y') default 'N';
ALTER TABLE `WKX_config` ADD COLUMN `userRegistration` enum('N', 'Y') default 'N';
# dropping authWrite from WKX_config
ALTER TABLE `WKX_config` DROP COLUMN `authWrite`;
# adding `editUserIdxxxx` and `addUserIdxxxx` to appropriate tables
ALTER TABLE `WKX_resource_musing_text` ADD COLUMN `editUserIdMusing` int(10) unsigned default NULL;
ALTER TABLE `WKX_resource_musing_text` ADD COLUMN `addUserIdMusing` int(10) unsigned default NULL;
ALTER TABLE `WKX_resource_note` ADD COLUMN `editUserIdNote` int(10) unsigned default NULL;
ALTER TABLE `WKX_resource_note` ADD COLUMN `addUserIdNote` int(10) unsigned default NULL;
ALTER TABLE `WKX_resource_abstract` ADD COLUMN `editUserIdAbstract` int(10) unsigned default NULL;
ALTER TABLE `WKX_resource_abstract` ADD COLUMN `addUserIdAbstract` int(10) unsigned default NULL;
ALTER TABLE `WKX_resource_quote_text` ADD COLUMN `editUserIdQuote` int(10) unsigned default NULL;
ALTER TABLE `WKX_resource_quote_text` ADD COLUMN `addUserIdQuote` int(10) unsigned default NULL;
ALTER TABLE `WKX_resource_paraphrase_text` ADD COLUMN `editUserIdParaphrase` int(10) unsigned default NULL;
ALTER TABLE `WKX_resource_paraphrase_text` ADD COLUMN `addUserIdParaphrase` int(10) unsigned default NULL;
ALTER TABLE `WKX_resource_misc` ADD COLUMN `editUserIdResource` int(10) unsigned default NULL;
ALTER TABLE `WKX_resource_misc` ADD COLUMN `addUserIdResource` int(10) unsigned default NULL;
# Adding these columns was an error in 2.1
ALTER TABLE `WKX_resource` DROP COLUMN `editUserIdResource`;
ALTER TABLE `WKX_resource` DROP COLUMN `addUserIdResource`;


# Adding WKX_users table
CREATE TABLE IF NOT EXISTS `WKX_users` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `fullname` varchar(255) default NULL,
  `email` varchar(255) default NULL,
  `admin` enum('N', 'Y') default 'N',
  `cookie` enum('N', 'Y') default 'N',
  PRIMARY KEY  (`id`)
);

# 2.1.0
# Adding WKX_user_register table
CREATE TABLE IF NOT EXISTS `WKX_user_register` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `hashKey` varchar(255) NOT NULL,
  `email` varchar(255) default NULL,
  `timestamp` timestamp(14) NOT NULL,
  PRIMARY KEY  (`id`)
);

# 2.1.0
# adding WKX_user_bibliography table
# value of 'userId' references 'id' in `users` table
CREATE TABLE IF NOT EXISTS `WKX_user_bibliography` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `userId` int(10) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` TEXT default NULL,
/* `bibliography` is a comma-separated list of `id` numbers from WKX_resource table */
  `bibliography` TEXT default NULL,
  PRIMARY KEY  (`id`),
  KEY `userIdKey` (`userId`)
);

# 2.2
# Removing `v1DbConvert` as it is no longer required.
ALTER TABLE `WKX_config` DROP COLUMN `v1DbConvert`;

# 2.2
# Removing auto_increment on `id` in WKX_resource_musing_text, as it shouldn't have been there in the first place...
# Additionally, drop the editing user's id as v2.2 stops other users editing posts that are not their own.
# A `timestamp` is added to WKX_resource_musing_text so that user musings can be properly ordered - when updating the database 
# at a wikindx upgrade for v2.2, initialise this column to NOW and set all NULL addUserIdxxx columns to superadmin's userId=1;
# Add separate comment tables for paraphrases and musings and drop the comments column from WKX_resource_paraphrase_text and 
# WKX_resource_quote_text.
# The `comment` columns in WKX_resource_ paraphrase_text and WKX_resource_quote_text are dropped in 
# core/systemcheck/SYSTEMCHECK.php if that script detects such columns still exist AFTER transferring them 
# to the new tables below.

ALTER TABLE `WKX_resource_musing_text` CHANGE COLUMN `id` `id` int(10) unsigned NOT NULL;
ALTER TABLE `WKX_resource_musing_text` DROP COLUMN `editUserIdMusing`;
ALTER TABLE `WKX_resource_quote_text` DROP COLUMN `editUserIdQuote`;
ALTER TABLE `WKX_resource_paraphrase_text` DROP COLUMN `editUserIdParaphrase`;
ALTER TABLE `WKX_resource_musing_text` ADD COLUMN `timestamp` timestamp(14) NOT NULL;
UPDATE TABLE `WKX_resource_musing_text` SET `timestamp` = NOW();
UPDATE `WKX_resource_musing_text` SET `addUserIdMusing` = '1' WHERE `addUserIdMusing` IS NULL;
UPDATE `WKX_resource_quote_text` SET `addUserIdQuote` = '1' WHERE `addUserIdQuote` IS NULL;
UPDATE `WKX_resource_paraphrase_text` SET `addUserIdParaphrase` = '1' WHERE `addUserIdParaphrase` IS NULL;

CREATE TABLE IF NOT EXISTS `WKX_resource_quote_comment` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `quoteId` int(10) unsigned NOT NULL,
  `addUserIdQuote` int(10) unsigned default NULL,
  `comment` text default NULL,
  `timestamp` timestamp(14) NOT NULL,
  PRIMARY KEY  (`id`)
);
CREATE TABLE IF NOT EXISTS `WKX_resource_paraphrase_comment` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `paraphraseId` int(10) unsigned NOT NULL,
  `addUserIdParaphrase` int(10) unsigned default NULL,
  `comment` text default NULL,
  `timestamp` timestamp(14) NOT NULL,
  PRIMARY KEY  (`id`)
);

# v2.2
# Add email notification table
CREATE TABLE IF NOT EXISTS `WKX_notify` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `notify` enum('N', 'A', 'M') default 'N',
  PRIMARY KEY  (`id`)
);
# Add option to turn notification on/off for administrator
ALTER TABLE `WKX_config` ADD COLUMN `notify` enum('Y', 'N') default 'Y';

#v2.2
# Add news table
CREATE TABLE IF NOT EXISTS `WKX_news` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(255) NOT NULL,
  `news` text default NULL,
  `timestamp` timestamp(14) NOT NULL,
  PRIMARY KEY  (`id`)
);

#v2.3.5
/* BBCode image display limits in pixels */
ALTER TABLE `WKX_config` ADD COLUMN `imgWidthLimit` int(10) default '400';
ALTER TABLE `WKX_config` ADD COLUMN `imgHeightLimit` int(10) default '400';

#####################
# v3.0.0
# A number of tables are dropped with this upgrade and various fields are 
# added, renamed or dropped either here or in the PHP code.
# We leave all of the above SQL statements so that upgrading will work from 
# whatever version of wikindx the user is using.
#####################

# We no longer use the WKX_resource_et_al table as it is superfluous
DROP TABLE IF EXISTS `WKX_resource_et_al`;

# WKX_journal becomes WKX_collection and 3 new fields are added
# set `collectionType` to 'journal' to match resource type
ALTER TABLE `WKX_journal` RENAME `WKX_collection`;
ALTER TABLE `WKX_collection` CHANGE COLUMN `title` `collectionTitle` varchar(255) default NULL;
ALTER TABLE `WKX_collection` ADD COLUMN `collectionType` varchar(255) default NULL;
ALTER TABLE `WKX_collection` ADD COLUMN `collectionTitleShort` varchar(255) default NULL;
UPDATE `WKX_collection` SET `collectionType` = 'journal';

# WKX_notify and WKX_preferences are collated into WKX_users. We create the appropriate fields here
ALTER TABLE `WKX_users` ADD COLUMN `paging` int(10) default '20';
ALTER TABLE `WKX_users` ADD COLUMN `pagingMaxLinks` int(10) unsigned default '11';
ALTER TABLE `WKX_users` ADD COLUMN `stringLimit` int(10) default '40';
ALTER TABLE `WKX_users` ADD COLUMN `language` varchar(2) default 'en';
ALTER TABLE `WKX_users` ADD COLUMN `style` varchar(255) default 'APA';
ALTER TABLE `WKX_users` ADD COLUMN `template` varchar(255) default 'default';
ALTER TABLE `WKX_users` ADD COLUMN `notify` enum('N', 'A', 'M') default 'N';

# In WKX_resource, a variety of fields have been replaced with field1 ... field9
ALTER TABLE `WKX_resource` ADD COLUMN `field1` varchar(255) default NULL;
ALTER TABLE `WKX_resource` ADD COLUMN `field2` varchar(255) default NULL;
ALTER TABLE `WKX_resource` ADD COLUMN `field3` varchar(255) default NULL;
ALTER TABLE `WKX_resource` ADD COLUMN `field4` varchar(255) default NULL;
ALTER TABLE `WKX_resource` ADD COLUMN `field5` varchar(255) default NULL;
ALTER TABLE `WKX_resource` ADD COLUMN `field6` varchar(255) default NULL;
ALTER TABLE `WKX_resource` ADD COLUMN `field7` varchar(255) default NULL;
ALTER TABLE `WKX_resource` ADD COLUMN `field8` varchar(255) default NULL;
ALTER TABLE `WKX_resource` ADD COLUMN `field9` varchar(255) default NULL;
# File attachment field
ALTER TABLE `WKX_resource` ADD COLUMN `file` varchar(255) default NULL;

# In WKX_resource_creator, fields have been replaced with creator1 ... creator5
ALTER TABLE `WKX_resource_creator` CHANGE COLUMN `creator` `creator1` varchar(255) default NULL;
ALTER TABLE `WKX_resource_creator` CHANGE COLUMN `editor` `creator2` varchar(255) default NULL;
ALTER TABLE `WKX_resource_creator` CHANGE COLUMN `translator` `creator3` varchar(255) default NULL;
ALTER TABLE `WKX_resource_creator` CHANGE COLUMN `reviser` `creator4` varchar(255) default NULL;
ALTER TABLE `WKX_resource_creator` CHANGE COLUMN `producer` `creator5` varchar(255) default NULL;

# In WKX_resource_year, fields have been bumped up to VARCHAR(30)
ALTER TABLE `WKX_resource_year` CHANGE COLUMN `year1` `year1` varchar(30) default NULL;
ALTER TABLE `WKX_resource_year` CHANGE COLUMN `year2` `year2` varchar(30) default NULL;
ALTER TABLE `WKX_resource_year` CHANGE COLUMN `year3` `year3` varchar(30) default NULL;

# In WKX_resource_misc:
# 1/ journal field is renamed to collection
# 2/ fields month, day, edition, seriesNumber, numberOfVolumes, 
# bookVolumeNumber, journalVolumeNumber and journalIssueNumber have been 
# replaced by miscField1 ... miscField4
ALTER TABLE `WKX_resource_misc` CHANGE COLUMN `journal` `collection` int(10) unsigned default NULL;
ALTER TABLE `WKX_resource_misc` ADD COLUMN `miscField1` int(10) unsigned default NULL;
ALTER TABLE `WKX_resource_misc` ADD COLUMN `miscField2` int(10) unsigned default NULL;
ALTER TABLE `WKX_resource_misc` ADD COLUMN `miscField3` int(10) unsigned default NULL;
ALTER TABLE `WKX_resource_misc` ADD COLUMN `miscField4` int(10) unsigned default NULL;

# In WKX_resource_year, all fields are replaced by year1, year2 and year3 fields
ALTER TABLE `WKX_resource_year` ADD COLUMN `year1` varchar(9) default NULL;
ALTER TABLE `WKX_resource_year` ADD COLUMN `year2` varchar(9) default NULL;
ALTER TABLE `WKX_resource_year` ADD COLUMN `year3` varchar(9) default NULL;

# Rename `name` field in WKX_publisher to `publisherName` and `location` to `publisherLocation`
ALTER TABLE `WKX_publisher` CHANGE COLUMN `name` `publisherName` varchar(255) default NULL;
ALTER TABLE `WKX_publisher` CHANGE COLUMN `location` `publisherLocation` varchar(255) default NULL;
# Add `publisherType` column
ALTER TABLE `WKX_publisher` ADD COLUMN `publisherType` varchar(255) default NULL;
# Set `publisherType` to 'book' to match type of publisher
UPDATE `WKX_publisher` SET `publisherType` = 'book';

# rename `WKX_bibtex` to `WKX_import_raw` and add a new column for the type of import.
# For this update, set the new column to 'bibtex'
RENAME TABLE `WKX_bibtex` TO `WKX_import_raw`;
ALTER TABLE `WKX_import_raw` ADD COLUMN `importType` VARCHAR(255) NOT NULL;
ALTER TABLE `WKX_import_raw` CHANGE COLUMN `stringId` `stringId` int(10) unsigned default NULL;
UPDATE `WKX_import_raw` SET `importType` = 'bibtex';

# add columns to WKX_config to allow for file attachments to resources and user pasting of (bibTeX) entries
ALTER TABLE `WKX_config` ADD COLUMN `fileAttach` enum('N', 'Y') default 'N';
ALTER TABLE `WKX_config` ADD COLUMN `fileViewLoggedOnOnly` enum('N', 'Y') default 'N';
ALTER TABLE `WKX_config` ADD COLUMN `maxPaste` int(10) default '10';
# Set default bibliographic style to APA
UPDATE `WKX_config` SET `style` = 'APA';

# Bump `keywords` field in `WKX_resource_keyword` up to text to account for some (observed) 
# large keyword fields in bibtex files.
ALTER TABLE `WKX_resource_keyword` CHANGE COLUMN `keywords` `keywords` text default NULL;

# add a `keywords` column to each of `WKX_resource_quote`, `_paraphrase` and `_musing` so that they 
# may be associated with keywords.
ALTER TABLE `WKX_resource_quote` ADD COLUMN `quote_keywords` text default NULL;
ALTER TABLE `WKX_resource_paraphrase` ADD COLUMN `paraphrase_keywords` text default NULL;
ALTER TABLE `WKX_resource_musing` ADD COLUMN `musing_keywords` text default NULL;

# Table structure for table `WKX_custom`
# Admin-added fields.
# `id` references `resource` table's `id`.
CREATE TABLE `WKX_custom` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `custom_label` varchar(255) default NULL,
/* Small or Large */
  `custom_size` enum('S', 'L') default 'S',
  PRIMARY KEY  (`id`)
);

# Table structure for table `WKX_resource_custom`
# Resource data using custom fields.
# `resourceId` references `resource` table's `id`.
# `customId` references `custom` table's `id`.
CREATE TABLE `WKX_resource_custom` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `customId` int(10) unsigned NOT NULL,
  `resourceId` int(10) unsigned NOT NULL,
  `custom_short` varchar(255) default NULL,
  `custom_long` text default NULL,
  `addUserIdCustom` int(10) unsigned default NULL,
  `editUserIdCustom` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`)
);

############################
# End v3.0.0 update
############################

############################
# v3.0.2 update
############################
# Because of SQL confusion over the term 'group', this has been changed
# to 'category' and 'categories'
RENAME TABLE `WKX_group` TO `WKX_category`;
RENAME TABLE `WKX_resource_group` TO `WKX_resource_category`;
ALTER TABLE `WKX_category` CHANGE COLUMN `group` `category` varchar(255) default NULL;
ALTER TABLE `WKX_resource_category` CHANGE COLUMN `groups` `categories` varchar(255) default NULL;

# Because we're now using portable SQL code and data types for use with ADOdb, we change TIMESTAMP 
# values to DATETIME
ALTER TABLE `WKX_resource_musing_text` CHANGE COLUMN `timestamp` `timestamp` DATETIME NOT NULL;
ALTER TABLE `WKX_resource_quote_comment` CHANGE COLUMN `timestamp` `timestamp` DATETIME NOT NULL;
ALTER TABLE `WKX_resource_paraphrase_comment` CHANGE COLUMN `timestamp` `timestamp` DATETIME NOT NULL;
ALTER TABLE `WKX_resource_timestamp` CHANGE COLUMN `timestamp` `timestamp` DATETIME NOT NULL;
ALTER TABLE `WKX_user_register` CHANGE COLUMN `timestamp` `timestamp` DATETIME NOT NULL;
ALTER TABLE `WKX_news` CHANGE COLUMN `timestamp` `timestamp` DATETIME NOT NULL;