create table AV_ROOT (
   ROOT_ID int unsigned not null,
   ROOT_F_SITE mediumint(10),
   ROOT_F_LAYOUT mediumint(10),
   ROOT_PREFERENCES text,
   primary key (ROOT_ID)
);

insert into AV_ROOT (ROOT_ID, ROOT_F_SITE, ROOT_F_LAYOUT, ROOT_PREFERENCES) values (0, null, null, null);

create table AV_MODULE (
   MODULE_ID int unsigned not null auto_increment,
   MODULE_NAME tinytext,
   MODULE_TITLE tinytext,
   MODULE_DESCRIPTION text,
   MODULE_URL tinytext,
   MODULE_VERSION tinytext,
   MODULE_STATE tinytext,
   MODULE_COPYRIGHT tinytext,
   MODULE_LICENSE tinytext,
   MODULE_LICENSEURL tinytext,
   MODULE_LICENSETEXT text,
   MODULE_ISLICENSEACCEPTED tinyint,
   MODULE_AUTHOR tinytext,
   MODULE_AUTHORURL tinytext,
   MODULE_AUTHOREMAIL tinytext,
   MODULE_AUTHORORGANISATION tinytext,
   MODULE_ISSIDEBAR tinyint,
   MODULE_ISACTIVATED tinyint,
   MODULE_ISINITIALIZED tinyint,
   MODULE_HASSITESETTINGS tinyint,
   MODULE_INSTALLEDFILES text,
   MODULE_CREATETIME datetime,
   MODULE_F_USER_MODIFIER mediumint(10),
   MODULE_MODIFYTIME datetime,
   MODULE_PREFERENCES text,
   primary key (MODULE_ID)
);

ALTER TABLE `AV_ACCESSLOG` CHANGE `ACCESSLOG_ID` `ACCESSLOG_ID` INT NOT NULL AUTO_INCREMENT;

ALTER TABLE `AV_FILE` CHANGE `FILE_NAME` `FILE_FILENAME` TINYTEXT NULL DEFAULT NULL;
ALTER TABLE `AV_FILE` CHANGE `FILE_SIZE` `FILE_FILESIZE` MEDIUMINT( 10 ) NULL DEFAULT NULL;

ALTER TABLE `AV_IMAGE` ADD `IMAGE_MIMETYPE` TINYTEXT AFTER `IMAGE_ALIAS`;

ALTER TABLE `AV_MEMBERSHIP` DROP `MEMBERSHIP_USERNAME`;

ALTER TABLE `AV_POLL` CHANGE `POLL_QUESTION` `POLL_QUESTION` TEXT NULL DEFAULT NULL;

ALTER TABLE `AV_LAYOUT` ADD `LAYOUT_LOCALE` VARCHAR( 64 )
 AFTER `LAYOUT_TITLE` ;

ALTER TABLE `AV_LAYOUT` ADD `LAYOUT_F_LAYOUT_LOCALEPARENT` MEDIUMINT( 10 ) AFTER `LAYOUT_F_LAYOUT_PARENT` ;

ALTER TABLE `AV_LAYOUT` ADD `LAYOUT_MESSAGES` MEDIUMTEXT AFTER `LAYOUT_PREFERENCES` ;

ALTER TABLE `AV_SKIN` ADD `SKIN_TITLE` VARCHAR( 128 ) AFTER `SKIN_NAME` ,
ADD `SKIN_DESCRIPTION` VARCHAR( 255 ) AFTER `SKIN_TITLE` ,
ADD `SKIN_NEEDSTRANSLATION` TINYINT( 1 ) AFTER `SKIN_DESCRIPTION` ;

ALTER TABLE `AV_SKIN` ADD `SKIN_MODULE` TINYTEXT AFTER `SKIN_ISCUSTOM`;

ALTER TABLE `AV_TEXT` CHANGE `TEXT_TITLE` `TEXT_TITLE` TEXT NULL DEFAULT NULL ,
CHANGE `TEXT_TEXT` `TEXT_TEXT` TEXT NULL DEFAULT NULL ,
CHANGE `TEXT_CONTENT` `TEXT_CONTENT` TEXT NULL DEFAULT NULL ,
CHANGE `TEXT_RAWCONTENT` `TEXT_RAWCONTENT` TEXT NULL DEFAULT NULL;

ALTER TABLE `AV_USER` ADD `USER_TYPE` TINYTEXT AFTER `USER_NAME`;

ALTER TABLE `AV_USER` ADD `USER_EMAIL_ISCONFIRMED` TINYINT( 1 ) AFTER `USER_EMAIL_ISPUBLIC` ,
ADD `USER_EMAIL_LASTCONFIRMED` DATETIME AFTER `USER_EMAIL_ISCONFIRMED` ,
ADD `USER_EMAIL_HISTORY` TINYTEXT AFTER `USER_EMAIL_LASTCONFIRMED`;

ALTER TABLE `AV_USER` ADD `USER_PREFERENCES` MEDIUMTEXT AFTER `USER_URL`;

ALTER TABLE `AV_USER` ADD `USER_IPADDRESS` TINYTEXT AFTER `USER_LASTVISIT` ;

ALTER TABLE `AV_USER` ADD `USER_BLOCKUSER` MEDIUMINT( 10 ) AFTER `USER_ISBLOCKED` ,
ADD `USER_BLOCKTIME` DATETIME AFTER `USER_BLOCKUSER` ;

ALTER TABLE `AV_USER` ADD `USER_AUTH_TYPE` TINYTEXT,
ADD `USER_AUTH_USER` TINYTEXT,
ADD `USER_AUTH_DATA` TINYTEXT,
ADD `USER_FAILED_LOGIN_COUNT` TINYINT UNSIGNED;

ALTER TABLE `AV_USER` DROP INDEX `IDX_USER_PASSWORD`;

ALTER TABLE `AV_VOTE` DROP `VOTE_USERNAME`;

ALTER TABLE `AV_SITE` ADD `SITE_URL` TINYTEXT AFTER `SITE_ALIAS` ;

ALTER TABLE `AV_SITE` ADD `SITE_BLOCKUSER` MEDIUMINT( 10 ) AFTER `SITE_ISBLOCKED` ,
ADD `SITE_BLOCKTIME` DATETIME AFTER `SITE_BLOCKUSER` ;

ALTER TABLE `AV_SITE` ADD `SITE_LASTPOSTING` DATETIME AFTER `SITE_LASTUPDATE` ;

ALTER TABLE `AV_SITE` ADD `SITE_SHOW` TINYINT( 1 ) AFTER `SITE_ENABLEPING` ;

# Modifications of the databasecontent

UPDATE `AV_IMAGE` SET IMAGE_MIMETYPE = CONCAT( "image/", IMAGE_FILEEXT );

ALTER TABLE `AV_IMAGE` DROP `IMAGE_FILEEXT`;

UPDATE `AV_USER` SET USER_TYPE = "USER_LOCAL";
UPDATE `AV_USER` SET `USER_AUTH_TYPE` = "local";
UPDATE `AV_USER` SET `USER_EMAIL_ISCONFIRMED` = 1;

UPDATE `AV_SITE` SET SITE_SHOW = 1;

# update TEXT_TEXT column
UPDATE AV_TEXT SET TEXT_TEXT = 
REPLACE(
  REPLACE(
    REPLACE(
      REPLACE(
        SUBSTRING(TEXT_CONTENT, LOCATE( "<text>", `TEXT_CONTENT` ) + 6, LOCATE( "</text>", `TEXT_CONTENT` ) - 6 - LOCATE( "<text>", `TEXT_CONTENT` )
      ),"&gt;",">")
      ,"&lt;","<"
    )
  ,"&quot;", "\"")
,"&amp;", "&");

UPDATE AV_SITE set `SITE_PREFERENCES` = CONCAT(CONCAT(SUBSTRING(`SITE_PREFERENCES`, 1, LOCATE( "</hopobject>", `SITE_PREFERENCES` ) -1), "  <sidebar01>modUserStatus,modSiteMenu,modWeblogSearchbar,modContributorMenu,modAdminMenu,modArchive</sidebar01>\n    <sidebar02>modRecentUpdates,modCredits</sidebar02>\n    <mostreadisprivate type=\"float\">1.0</mostreadisprivate>\n    <rssfeedcomplete type=\"float\">1.0</rssfeedcomplete>\n    <rssfeedtopics type=\"float\">1.0</rssfeedtopics>\n    <referrersisprivate type=\"float\">1.0</referrersisprivate>\n   <rssfeedsummary type=\"float\">0.0</rssfeedsummary>\n    <rssfeedcomments type=\"float\">0.0</rssfeedcomments>\n    <use_global_spamfilter type=\"float\">1.0</use_global_spamfilter>\n    <usedayheader type=\"float\">1.0</usedayheader>\n    <modbettereditoreditor>bettereditor</modbettereditoreditor>\n    <modtrackbackactive>1</modtrackbackactive>"), SUBSTRING(`SITE_PREFERENCES`, LOCATE( "</hopobject>", `SITE_PREFERENCES` ), LENGTH(`SITE_PREFERENCES`)));