BEGIN;
USE atmail;

--
-- Create SQLite schema specific "typed" tables
--

CREATE TEMPORARY TABLE `UserSettings` (
  `Account` varchar(128) NOT NULL default '',
  `MboxOrder` varchar(12) default NULL,
  `RealName` varchar(40) default NULL,
  `Refresh` smallint(5) default NULL,
  `EmailHeaders` varchar(8) default NULL,
  `TimeZone` varchar(32) default NULL,
  `MsgNum` varchar(4) default NULL,
  `EmptyTrash` smallint(1) default NULL,
  `NewWindow` smallint(1) default NULL,
  `HtmlEditor` smallint(1) default NULL,
  `ReplyTo` varchar(255) default NULL,
  `Signature` text,
  `FontStyle` varchar(21) default NULL,
  `LeaveMsgs` smallint(1) default NULL,
  `LoginType` varchar(12) default NULL,
  `Advanced` smallint(1) default NULL,
  `PrimaryColor` varchar(7) default NULL,
  `SecondaryColor` varchar(7) default NULL,
  `LinkColor` varchar(7) default NULL,
  `VlinkColor` varchar(7) default NULL,
  `BgColor` varchar(7) default NULL,
  `TextColor` varchar(7) default NULL,
  `HeaderColor` varchar(7) default NULL,
  `HeadColor` varchar(7) default NULL,
  `MailType` varchar(4) default NULL,
  `ThirdColor` varchar(7) default NULL,
  `Mode` varchar(4) default NULL,
  `Service` smallint(1) default NULL,
  `Language` varchar(10) default NULL,
  `StartPage` char(1) default NULL,
  `OnColor` varchar(7) default NULL,
  `OffColor` varchar(7) default NULL,
  `TextHeadColor` varchar(7) default NULL,
  `SelectColor` varchar(7) default NULL,
  `TopBg` varchar(24) default NULL,
  `AutoTrash` tinyint(1) default NULL,
  `MailServer` varchar(64) default NULL,
  `MailAuth` tinyint(1) default NULL,
  `PGPenable` smallint(1) default NULL,
  `PGPappend` smallint(1) default NULL,
  `PGPsign` smallint(1) default NULL,
  `SpamTreatment` varchar(10) default NULL,
  `AbookTrusted` smallint(1) default NULL,
  `AntiVirus` smallint(1) default NULL,
  `PassCode` varchar(64) default NULL,
  `DateFormat` varchar(8) default NULL,
  `TimeFormat` varchar(8) default NULL,
  `AutoComplete` smallint(1) default NULL,
  `EmailEncoding` varchar(16) default NULL,
  `DisplayImages` smallint(1) default NULL,
  `Ajax` char(1) default NULL,
  PRIMARY KEY  (`Account`)
);

CREATE TEMPORARY TABLE `MailSort` (
  `EmailAddress` varchar(64) default NULL,
  `EmailSubject` varchar(64) default NULL,
  `EmailFolder` varchar(32) default NULL,
  `Account` varchar(128) default NULL,
  KEY `iAccount` (`Account`)
);

CREATE TEMPORARY TABLE `Abook` (
  `UserEmail` varchar(255) default NULL,
  `UserEmail2` varchar(255) default NULL,
  `UserEmail3` varchar(255) default NULL,
  `UserEmail4` varchar(255) default NULL,
  `UserEmail5` varchar(255) default NULL,
  `UserFirstName` varchar(128) default NULL,
  `UserMiddleName` varchar(128) default NULL,
  `UserLastName` varchar(128) default NULL,
  `UserTitle` varchar(128) default NULL,
  `UserGender` char(1) default NULL,
  `UserDOB` datetime default NULL,
  `UserHomeAddress` varchar(128) default NULL,
  `UserHomeCity` varchar(128) default NULL,
  `UserHomeState` varchar(128) default NULL,
  `UserHomeZip` varchar(128) default NULL,
  `UserHomeCountry` varchar(128) default NULL,
  `UserHomePhone` varchar(128) default NULL,
  `UserHomeMobile` varchar(128) default NULL,
  `UserHomeFax` varchar(128) default NULL,
  `UserURL` varchar(128) default NULL,
  `UserWorkCompany` varchar(128) default NULL,
  `UserWorkTitle` varchar(128) default NULL,
  `UserWorkDept` varchar(128) default NULL,
  `UserWorkOffice` varchar(128) default NULL,
  `UserWorkAddress` varchar(128) default NULL,
  `UserWorkCity` varchar(128) default NULL,
  `UserWorkState` varchar(128) default NULL,
  `UserWorkZip` varchar(128) default NULL,
  `UserWorkCountry` varchar(128) default NULL,
  `UserWorkPhone` varchar(128) default NULL,
  `UserWorkMobile` varchar(128) default NULL,
  `UserWorkFax` varchar(128) default NULL,
  `UserType` varchar(16) default NULL,
  `UserInfo` text,
  `UserPgpKey` text,
  `Account` varchar(128) NOT NULL default '',
  `id` bigint(10) unsigned NOT NULL auto_increment,
  `DateAdded` datetime default NULL,
  `DateModified` varchar(12) default NULL,
  `EntryID` varchar(64) default NULL,
  PRIMARY KEY  (`id`),
  KEY `iAccount` (`Account`),
  KEY `UserEmail` (`UserEmail`)
);

CREATE TEMPORARY TABLE `AbookGroup` (
  `GroupName` varchar(32) default NULL,
  `GroupEmail` varchar(64) default NULL,
  `Account` varchar(128) default NULL,
  `id` mediumint(12) NOT NULL auto_increment,
  PRIMARY KEY  (`id`),
  KEY `iAccount` (`Account`)
);

--
-- Create SQLite schema table
--
CREATE TEMPORARY TABLE `Accounts_sqlite` (
  `UserAccount` varchar(32) default NULL,
  `Account` varchar(128) NOT NULL default '',
  `Type` varchar(4) default NULL,
  `MailServer` varchar(64) default NULL
);

--
-- Insert SQLite data
--
source upgrade.dump;

--
-- Process SQLite schema specific "typed" tables data
--

ALTER TABLE UserSettings ADD UseSSL tinyint(1) DEFAULT 0;
ALTER TABLE Accounts_sqlite ADD UseSSL tinyint(1) DEFAULT 0;

INSERT INTO Abook_a SELECT * FROM Abook WHERE Account LIKE 'a%';
DELETE FROM Abook WHERE Account LIKE 'a%';
INSERT INTO Abook_b SELECT * FROM Abook WHERE Account LIKE 'b%';
DELETE FROM Abook WHERE Account LIKE 'b%';
INSERT INTO Abook_c SELECT * FROM Abook WHERE Account LIKE 'c%';
DELETE FROM Abook WHERE Account LIKE 'c%';
INSERT INTO Abook_d SELECT * FROM Abook WHERE Account LIKE 'd%';
DELETE FROM Abook WHERE Account LIKE 'd%';
INSERT INTO Abook_e SELECT * FROM Abook WHERE Account LIKE 'e%';
DELETE FROM Abook WHERE Account LIKE 'e%';
INSERT INTO Abook_f SELECT * FROM Abook WHERE Account LIKE 'f%';
DELETE FROM Abook WHERE Account LIKE 'f%';
INSERT INTO Abook_g SELECT * FROM Abook WHERE Account LIKE 'g%';
DELETE FROM Abook WHERE Account LIKE 'g%';
INSERT INTO Abook_h SELECT * FROM Abook WHERE Account LIKE 'h%';
DELETE FROM Abook WHERE Account LIKE 'h%';
INSERT INTO Abook_i SELECT * FROM Abook WHERE Account LIKE 'i%';
DELETE FROM Abook WHERE Account LIKE 'i%';
INSERT INTO Abook_j SELECT * FROM Abook WHERE Account LIKE 'j%';
DELETE FROM Abook WHERE Account LIKE 'j%';
INSERT INTO Abook_k SELECT * FROM Abook WHERE Account LIKE 'k%';
DELETE FROM Abook WHERE Account LIKE 'k%';
INSERT INTO Abook_l SELECT * FROM Abook WHERE Account LIKE 'l%';
DELETE FROM Abook WHERE Account LIKE 'l%';
INSERT INTO Abook_m SELECT * FROM Abook WHERE Account LIKE 'm%';
DELETE FROM Abook WHERE Account LIKE 'm%';
INSERT INTO Abook_n SELECT * FROM Abook WHERE Account LIKE 'n%';
DELETE FROM Abook WHERE Account LIKE 'n%';
INSERT INTO Abook_o SELECT * FROM Abook WHERE Account LIKE 'o%';
DELETE FROM Abook WHERE Account LIKE 'o%';
INSERT INTO Abook_p SELECT * FROM Abook WHERE Account LIKE 'p%';
DELETE FROM Abook WHERE Account LIKE 'p%';
INSERT INTO Abook_q SELECT * FROM Abook WHERE Account LIKE 'q%';
DELETE FROM Abook WHERE Account LIKE 'q%';
INSERT INTO Abook_r SELECT * FROM Abook WHERE Account LIKE 'r%';
DELETE FROM Abook WHERE Account LIKE 'r%';
INSERT INTO Abook_s SELECT * FROM Abook WHERE Account LIKE 's%';
DELETE FROM Abook WHERE Account LIKE 's%';
INSERT INTO Abook_t SELECT * FROM Abook WHERE Account LIKE 't%';
DELETE FROM Abook WHERE Account LIKE 't%';
INSERT INTO Abook_u SELECT * FROM Abook WHERE Account LIKE 'u%';
DELETE FROM Abook WHERE Account LIKE 'u%';
INSERT INTO Abook_v SELECT * FROM Abook WHERE Account LIKE 'v%';
DELETE FROM Abook WHERE Account LIKE 'v%';
INSERT INTO Abook_w SELECT * FROM Abook WHERE Account LIKE 'w%';
DELETE FROM Abook WHERE Account LIKE 'w%';
INSERT INTO Abook_x SELECT * FROM Abook WHERE Account LIKE 'x%';
DELETE FROM Abook WHERE Account LIKE 'x%';
INSERT INTO Abook_y SELECT * FROM Abook WHERE Account LIKE 'y%';
DELETE FROM Abook WHERE Account LIKE 'y%';
INSERT INTO Abook_z SELECT * FROM Abook WHERE Account LIKE 'z%';
DELETE FROM Abook WHERE Account LIKE 'z%';
INSERT INTO AbookGroup_a SELECT * FROM AbookGroup WHERE Account LIKE 'a%';
DELETE FROM AbookGroup WHERE Account LIKE 'a%';
INSERT INTO AbookGroup_b SELECT * FROM AbookGroup WHERE Account LIKE 'b%';
DELETE FROM AbookGroup WHERE Account LIKE 'b%';
INSERT INTO AbookGroup_c SELECT * FROM AbookGroup WHERE Account LIKE 'c%';
DELETE FROM AbookGroup WHERE Account LIKE 'c%';
INSERT INTO AbookGroup_d SELECT * FROM AbookGroup WHERE Account LIKE 'd%';
DELETE FROM AbookGroup WHERE Account LIKE 'd%';
INSERT INTO AbookGroup_e SELECT * FROM AbookGroup WHERE Account LIKE 'e%';
DELETE FROM AbookGroup WHERE Account LIKE 'e%';
INSERT INTO AbookGroup_f SELECT * FROM AbookGroup WHERE Account LIKE 'f%';
DELETE FROM AbookGroup WHERE Account LIKE 'f%';
INSERT INTO AbookGroup_g SELECT * FROM AbookGroup WHERE Account LIKE 'g%';
DELETE FROM AbookGroup WHERE Account LIKE 'g%';
INSERT INTO AbookGroup_h SELECT * FROM AbookGroup WHERE Account LIKE 'h%';
DELETE FROM AbookGroup WHERE Account LIKE 'h%';
INSERT INTO AbookGroup_i SELECT * FROM AbookGroup WHERE Account LIKE 'i%';
DELETE FROM AbookGroup WHERE Account LIKE 'i%';
INSERT INTO AbookGroup_j SELECT * FROM AbookGroup WHERE Account LIKE 'j%';
DELETE FROM AbookGroup WHERE Account LIKE 'j%';
INSERT INTO AbookGroup_k SELECT * FROM AbookGroup WHERE Account LIKE 'k%';
DELETE FROM AbookGroup WHERE Account LIKE 'k%';
INSERT INTO AbookGroup_l SELECT * FROM AbookGroup WHERE Account LIKE 'l%';
DELETE FROM AbookGroup WHERE Account LIKE 'l%';
INSERT INTO AbookGroup_m SELECT * FROM AbookGroup WHERE Account LIKE 'm%';
DELETE FROM AbookGroup WHERE Account LIKE 'm%';
INSERT INTO AbookGroup_n SELECT * FROM AbookGroup WHERE Account LIKE 'n%';
DELETE FROM AbookGroup WHERE Account LIKE 'n%';
INSERT INTO AbookGroup_o SELECT * FROM AbookGroup WHERE Account LIKE 'o%';
DELETE FROM AbookGroup WHERE Account LIKE 'o%';
INSERT INTO AbookGroup_p SELECT * FROM AbookGroup WHERE Account LIKE 'p%';
DELETE FROM AbookGroup WHERE Account LIKE 'p%';
INSERT INTO AbookGroup_q SELECT * FROM AbookGroup WHERE Account LIKE 'q%';
DELETE FROM AbookGroup WHERE Account LIKE 'q%';
INSERT INTO AbookGroup_r SELECT * FROM AbookGroup WHERE Account LIKE 'r%';
DELETE FROM AbookGroup WHERE Account LIKE 'r%';
INSERT INTO AbookGroup_s SELECT * FROM AbookGroup WHERE Account LIKE 's%';
DELETE FROM AbookGroup WHERE Account LIKE 's%';
INSERT INTO AbookGroup_t SELECT * FROM AbookGroup WHERE Account LIKE 't%';
DELETE FROM AbookGroup WHERE Account LIKE 't%';
INSERT INTO AbookGroup_u SELECT * FROM AbookGroup WHERE Account LIKE 'u%';
DELETE FROM AbookGroup WHERE Account LIKE 'u%';
INSERT INTO AbookGroup_v SELECT * FROM AbookGroup WHERE Account LIKE 'v%';
DELETE FROM AbookGroup WHERE Account LIKE 'v%';
INSERT INTO AbookGroup_w SELECT * FROM AbookGroup WHERE Account LIKE 'w%';
DELETE FROM AbookGroup WHERE Account LIKE 'w%';
INSERT INTO AbookGroup_x SELECT * FROM AbookGroup WHERE Account LIKE 'x%';
DELETE FROM AbookGroup WHERE Account LIKE 'x%';
INSERT INTO AbookGroup_y SELECT * FROM AbookGroup WHERE Account LIKE 'y%';
DELETE FROM AbookGroup WHERE Account LIKE 'y%';
INSERT INTO AbookGroup_z SELECT * FROM AbookGroup WHERE Account LIKE 'z%';
DELETE FROM AbookGroup WHERE Account LIKE 'z%';
INSERT INTO MailSort_a SELECT * FROM MailSort WHERE Account LIKE 'a%';
DELETE FROM MailSort WHERE Account LIKE 'a%';
INSERT INTO MailSort_b SELECT * FROM MailSort WHERE Account LIKE 'b%';
DELETE FROM MailSort WHERE Account LIKE 'b%';
INSERT INTO MailSort_c SELECT * FROM MailSort WHERE Account LIKE 'c%';
DELETE FROM MailSort WHERE Account LIKE 'c%';
INSERT INTO MailSort_d SELECT * FROM MailSort WHERE Account LIKE 'd%';
DELETE FROM MailSort WHERE Account LIKE 'd%';
INSERT INTO MailSort_e SELECT * FROM MailSort WHERE Account LIKE 'e%';
DELETE FROM MailSort WHERE Account LIKE 'e%';
INSERT INTO MailSort_f SELECT * FROM MailSort WHERE Account LIKE 'f%';
DELETE FROM MailSort WHERE Account LIKE 'f%';
INSERT INTO MailSort_g SELECT * FROM MailSort WHERE Account LIKE 'g%';
DELETE FROM MailSort WHERE Account LIKE 'g%';
INSERT INTO MailSort_h SELECT * FROM MailSort WHERE Account LIKE 'h%';
DELETE FROM MailSort WHERE Account LIKE 'h%';
INSERT INTO MailSort_i SELECT * FROM MailSort WHERE Account LIKE 'i%';
DELETE FROM MailSort WHERE Account LIKE 'i%';
INSERT INTO MailSort_j SELECT * FROM MailSort WHERE Account LIKE 'j%';
DELETE FROM MailSort WHERE Account LIKE 'j%';
INSERT INTO MailSort_k SELECT * FROM MailSort WHERE Account LIKE 'k%';
DELETE FROM MailSort WHERE Account LIKE 'k%';
INSERT INTO MailSort_l SELECT * FROM MailSort WHERE Account LIKE 'l%';
DELETE FROM MailSort WHERE Account LIKE 'l%';
INSERT INTO MailSort_m SELECT * FROM MailSort WHERE Account LIKE 'm%';
DELETE FROM MailSort WHERE Account LIKE 'm%';
INSERT INTO MailSort_n SELECT * FROM MailSort WHERE Account LIKE 'n%';
DELETE FROM MailSort WHERE Account LIKE 'n%';
INSERT INTO MailSort_o SELECT * FROM MailSort WHERE Account LIKE 'o%';
DELETE FROM MailSort WHERE Account LIKE 'o%';
INSERT INTO MailSort_p SELECT * FROM MailSort WHERE Account LIKE 'p%';
DELETE FROM MailSort WHERE Account LIKE 'p%';
INSERT INTO MailSort_q SELECT * FROM MailSort WHERE Account LIKE 'q%';
DELETE FROM MailSort WHERE Account LIKE 'q%';
INSERT INTO MailSort_r SELECT * FROM MailSort WHERE Account LIKE 'r%';
DELETE FROM MailSort WHERE Account LIKE 'r%';
INSERT INTO MailSort_s SELECT * FROM MailSort WHERE Account LIKE 's%';
DELETE FROM MailSort WHERE Account LIKE 's%';
INSERT INTO MailSort_t SELECT * FROM MailSort WHERE Account LIKE 't%';
DELETE FROM MailSort WHERE Account LIKE 't%';
INSERT INTO MailSort_u SELECT * FROM MailSort WHERE Account LIKE 'u%';
DELETE FROM MailSort WHERE Account LIKE 'u%';
INSERT INTO MailSort_v SELECT * FROM MailSort WHERE Account LIKE 'v%';
DELETE FROM MailSort WHERE Account LIKE 'v%';
INSERT INTO MailSort_w SELECT * FROM MailSort WHERE Account LIKE 'w%';
DELETE FROM MailSort WHERE Account LIKE 'w%';
INSERT INTO MailSort_x SELECT * FROM MailSort WHERE Account LIKE 'x%';
DELETE FROM MailSort WHERE Account LIKE 'x%';
INSERT INTO MailSort_y SELECT * FROM MailSort WHERE Account LIKE 'y%';
DELETE FROM MailSort WHERE Account LIKE 'y%';
INSERT INTO MailSort_z SELECT * FROM MailSort WHERE Account LIKE 'z%';
DELETE FROM MailSort WHERE Account LIKE 'z%';
INSERT INTO UserSettings_a SELECT * FROM UserSettings WHERE Account LIKE 'a%';
DELETE FROM UserSettings WHERE Account LIKE 'a%';
INSERT INTO UserSettings_b SELECT * FROM UserSettings WHERE Account LIKE 'b%';
DELETE FROM UserSettings WHERE Account LIKE 'b%';
INSERT INTO UserSettings_c SELECT * FROM UserSettings WHERE Account LIKE 'c%';
DELETE FROM UserSettings WHERE Account LIKE 'c%';
INSERT INTO UserSettings_d SELECT * FROM UserSettings WHERE Account LIKE 'd%';
DELETE FROM UserSettings WHERE Account LIKE 'd%';
INSERT INTO UserSettings_e SELECT * FROM UserSettings WHERE Account LIKE 'e%';
DELETE FROM UserSettings WHERE Account LIKE 'e%';
INSERT INTO UserSettings_f SELECT * FROM UserSettings WHERE Account LIKE 'f%';
DELETE FROM UserSettings WHERE Account LIKE 'f%';
INSERT INTO UserSettings_g SELECT * FROM UserSettings WHERE Account LIKE 'g%';
DELETE FROM UserSettings WHERE Account LIKE 'g%';
INSERT INTO UserSettings_h SELECT * FROM UserSettings WHERE Account LIKE 'h%';
DELETE FROM UserSettings WHERE Account LIKE 'h%';
INSERT INTO UserSettings_i SELECT * FROM UserSettings WHERE Account LIKE 'i%';
DELETE FROM UserSettings WHERE Account LIKE 'i%';
INSERT INTO UserSettings_j SELECT * FROM UserSettings WHERE Account LIKE 'j%';
DELETE FROM UserSettings WHERE Account LIKE 'j%';
INSERT INTO UserSettings_k SELECT * FROM UserSettings WHERE Account LIKE 'k%';
DELETE FROM UserSettings WHERE Account LIKE 'k%';
INSERT INTO UserSettings_l SELECT * FROM UserSettings WHERE Account LIKE 'l%';
DELETE FROM UserSettings WHERE Account LIKE 'l%';
INSERT INTO UserSettings_m SELECT * FROM UserSettings WHERE Account LIKE 'm%';
DELETE FROM UserSettings WHERE Account LIKE 'm%';
INSERT INTO UserSettings_n SELECT * FROM UserSettings WHERE Account LIKE 'n%';
DELETE FROM UserSettings WHERE Account LIKE 'n%';
INSERT INTO UserSettings_o SELECT * FROM UserSettings WHERE Account LIKE 'o%';
DELETE FROM UserSettings WHERE Account LIKE 'o%';
INSERT INTO UserSettings_p SELECT * FROM UserSettings WHERE Account LIKE 'p%';
DELETE FROM UserSettings WHERE Account LIKE 'p%';
INSERT INTO UserSettings_q SELECT * FROM UserSettings WHERE Account LIKE 'q%';
DELETE FROM UserSettings WHERE Account LIKE 'q%';
INSERT INTO UserSettings_r SELECT * FROM UserSettings WHERE Account LIKE 'r%';
DELETE FROM UserSettings WHERE Account LIKE 'r%';
INSERT INTO UserSettings_s SELECT * FROM UserSettings WHERE Account LIKE 's%';
DELETE FROM UserSettings WHERE Account LIKE 's%';
INSERT INTO UserSettings_t SELECT * FROM UserSettings WHERE Account LIKE 't%';
DELETE FROM UserSettings WHERE Account LIKE 't%';
INSERT INTO UserSettings_u SELECT * FROM UserSettings WHERE Account LIKE 'u%';
DELETE FROM UserSettings WHERE Account LIKE 'u%';
INSERT INTO UserSettings_v SELECT * FROM UserSettings WHERE Account LIKE 'v%';
DELETE FROM UserSettings WHERE Account LIKE 'v%';
INSERT INTO UserSettings_w SELECT * FROM UserSettings WHERE Account LIKE 'w%';
DELETE FROM UserSettings WHERE Account LIKE 'w%';
INSERT INTO UserSettings_x SELECT * FROM UserSettings WHERE Account LIKE 'x%';
DELETE FROM UserSettings WHERE Account LIKE 'x%';
INSERT INTO UserSettings_y SELECT * FROM UserSettings WHERE Account LIKE 'y%';
DELETE FROM UserSettings WHERE Account LIKE 'y%';
INSERT INTO UserSettings_z SELECT * FROM UserSettings WHERE Account LIKE 'z%';
DELETE FROM UserSettings WHERE Account LIKE 'z%';
INSERT INTO Abook_other SELECT * FROM Abook;
INSERT INTO AbookGroup_other SELECT * FROM AbookGroup;
INSERT INTO MailSort_other SELECT * FROM MailSort;
INSERT INTO UserSettings_other SELECT * FROM UserSettings;

INSERT INTO Accounts SELECT * FROM Accounts_sqlite;

COMMIT;
