# This file contains changes to VIKO database structure from previous version.
# Use it to upgrade from VIKO 1.0.5 to VIKO 1.1
#
# Simply run the script in your MySQL database.
#




# Allow marks consisting of alphabetic letters.
# By changing Marks.mark from INT to varchar(50),
# it's now possible to write all kinds of marks:
#  5, 4, 3, 2, 1
#  A, B, C, D, E, F
#  Perfect, Great, Good, Medium, Very bad
ALTER TABLE Marks
MODIFY mark varchar(64) NOT NULL;


# Formerly the marktype field in Lessons table
# contained an integer value: 0 designating a normal
# lesson and 1 designating preliminary exam.
# This has now replaced with ENUM('LESSON','PRELIM')

# Before changing values in Lessons table, we must
# convert date and term columns from timestamp to date,
# because otherwise these field will be automatically
# updated to current date, which we don't want.
ALTER TABLE Lessons
MODIFY date datetime NOT NULL;
ALTER TABLE Lessons
MODIFY term datetime;

# rename marktype column to old_marktype.
ALTER TABLE Lessons
CHANGE marktype old_marktype tinyint;
#
# create new marktype column.
ALTER TABLE Lessons
ADD marktype enum('LESSON', 'PRELIM') DEFAULT 'LESSON' NOT NULL;
#
# Convert values of old_marktype to marktype, so that:
# 0 -> LESSON
# 1 -> PRELIM
UPDATE Lessons
SET marktype='PRELIM' WHERE old_marktype=1;
#
# remove old_marktype
ALTER TABLE Lessons
DROP old_marktype;

# when done, convert date and term fields back to timestamp
ALTER TABLE Lessons
MODIFY date timestamp NOT NULL;
ALTER TABLE Lessons
MODIFY term timestamp;


# VIKO 1.1 starts using SHA1() function for storing passwords.
# But concurrently we have to maintain passwords generated by
# both PASSWORD() and OLD_PASSWORD() functions.
#
# The comparison of hash functions:
#   SHA1 - 40 characters
#   MD5 - 32 characters
#   PASSWORD - 41 characters
#   OLD_PASSWORD - 16 characters
#
# From above we see, that at least 41 characters is needed
# to be compatible with all hash lengths.
ALTER TABLE Users
MODIFY password     varchar(41) NOT NULL;


# The former 8 characters for storing description was definetly
# an enormous limitation. So now we gratefully make it a TEXT
# instead. Well, some might argue, this is too much for this
# field, especially if it's primary role at the moment is to store
# the name of the form, the user is in. But the role of this
# field is going to change in the future anyway.
ALTER TABLE Users
MODIFY description  text NOT NULL;


# The username should be so long, that we could use
# the equation:
#   username = firstname + lastname
# Because first- and lastname are both 32 chars,
# the username should be at least 64 chars long.
#
# Also, the username must be unique.
ALTER TABLE Users
MODIFY username     varchar(64) NOT NULL    UNIQUE;


# 64 characters for a filenames and titles was probably too easily exceeded.
# 128 characters should be enough.
# We change Files, Files_Folders, Links, Links_Folders, Topics, Courses.
ALTER TABLE Files
MODIFY filename     varchar(128);

ALTER TABLE Files
MODIFY title        varchar(128) NOT NULL;

ALTER TABLE Files_Folders
MODIFY folder_name  varchar(128) NOT NULL;

ALTER TABLE Links
MODIFY title        varchar(128) NOT NULL;

ALTER TABLE Links_Folders
MODIFY folder_name  varchar(128) NOT NULL;

ALTER TABLE Topics
MODIFY topic        varchar(128) NOT NULL;

ALTER TABLE Courses
MODIFY name         varchar(128) NOT NULL;


# Filesizes were formerly stored as varchars, which was both
# bad from database structure and performance standpoints.
# So now we convert these fields to integer instead.
ALTER TABLE Files
MODIFY filesize     int;

