From c52c68f314c67c76c7181a6d0828f476290fbd66 Mon Sep 17 00:00:00 2001 From: Sean Dague Date: Wed, 2 Apr 2008 15:24:31 +0000 Subject: whole lot more moving --- OpenSim/Data/MySQL/Resources/AvatarAppearance.sql | 42 ++++++++++++++++++++++ OpenSim/Data/MySQL/Resources/CreateAgentsTable.sql | 24 +++++++++++++ OpenSim/Data/MySQL/Resources/CreateAssetsTable.sql | 11 ++++++ .../Data/MySQL/Resources/CreateFoldersTable.sql | 11 ++++++ OpenSim/Data/MySQL/Resources/CreateItemsTable.sql | 18 ++++++++++ OpenSim/Data/MySQL/Resources/CreateLogsTable.sql | 10 ++++++ .../Data/MySQL/Resources/CreateRegionsTable.sql | 32 +++++++++++++++++ .../MySQL/Resources/CreateUserFriendsTable.sql | 11 ++++++ OpenSim/Data/MySQL/Resources/CreateUsersTable.sql | 35 ++++++++++++++++++ .../Resources/UpgradeFoldersTableToVersion2.sql | 4 +++ .../Resources/UpgradeItemsTableToVersion2.sql | 9 +++++ .../Resources/UpgradeRegionsTableToVersion2.sql | 4 +++ .../Resources/UpgradeRegionsTableToVersion3.sql | 18 ++++++++++ .../Resources/UpgradeUsersTableToVersion2.sql | 3 ++ 14 files changed, 232 insertions(+) create mode 100644 OpenSim/Data/MySQL/Resources/AvatarAppearance.sql create mode 100644 OpenSim/Data/MySQL/Resources/CreateAgentsTable.sql create mode 100644 OpenSim/Data/MySQL/Resources/CreateAssetsTable.sql create mode 100644 OpenSim/Data/MySQL/Resources/CreateFoldersTable.sql create mode 100644 OpenSim/Data/MySQL/Resources/CreateItemsTable.sql create mode 100644 OpenSim/Data/MySQL/Resources/CreateLogsTable.sql create mode 100644 OpenSim/Data/MySQL/Resources/CreateRegionsTable.sql create mode 100644 OpenSim/Data/MySQL/Resources/CreateUserFriendsTable.sql create mode 100644 OpenSim/Data/MySQL/Resources/CreateUsersTable.sql create mode 100644 OpenSim/Data/MySQL/Resources/UpgradeFoldersTableToVersion2.sql create mode 100644 OpenSim/Data/MySQL/Resources/UpgradeItemsTableToVersion2.sql create mode 100644 OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion2.sql create mode 100644 OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion3.sql create mode 100644 OpenSim/Data/MySQL/Resources/UpgradeUsersTableToVersion2.sql (limited to 'OpenSim/Data/MySQL/Resources') diff --git a/OpenSim/Data/MySQL/Resources/AvatarAppearance.sql b/OpenSim/Data/MySQL/Resources/AvatarAppearance.sql new file mode 100644 index 0000000..b638ee2 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/AvatarAppearance.sql @@ -0,0 +1,42 @@ +-- +-- Create schema avatar_appearance +-- + +CREATE DATABASE IF NOT EXISTS avatar_appearance; +USE avatar_appearance; + +DROP TABLE IF EXISTS `avatarappearance`; +CREATE TABLE `avatarappearance` ( + `UUID` char(36) NOT NULL, + `Serial` int(10) unsigned NOT NULL, + `WearableItem0` char(36) NOT NULL, + `WearableAsset0` char(36) NOT NULL, + `WearableItem1` char(36) NOT NULL, + `WearableAsset1` char(36) NOT NULL, + `WearableItem2` char(36) NOT NULL, + `WearableAsset2` char(36) NOT NULL, + `WearableItem3` char(36) NOT NULL, + `WearableAsset3` char(36) NOT NULL, + `WearableItem4` char(36) NOT NULL, + `WearableAsset4` char(36) NOT NULL, + `WearableItem5` char(36) NOT NULL, + `WearableAsset5` char(36) NOT NULL, + `WearableItem6` char(36) NOT NULL, + `WearableAsset6` char(36) NOT NULL, + `WearableItem7` char(36) NOT NULL, + `WearableAsset7` char(36) NOT NULL, + `WearableItem8` char(36) NOT NULL, + `WearableAsset8` char(36) NOT NULL, + `WearableItem9` char(36) NOT NULL, + `WearableAsset9` char(36) NOT NULL, + `WearableItem10` char(36) NOT NULL, + `WearableAsset10` char(36) NOT NULL, + `WearableItem11` char(36) NOT NULL, + `WearableAsset11` char(36) NOT NULL, + `WearableItem12` char(36) NOT NULL, + `WearableAsset12` char(36) NOT NULL, + + + PRIMARY KEY (`UUID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + diff --git a/OpenSim/Data/MySQL/Resources/CreateAgentsTable.sql b/OpenSim/Data/MySQL/Resources/CreateAgentsTable.sql new file mode 100644 index 0000000..3ef7bc9 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/CreateAgentsTable.sql @@ -0,0 +1,24 @@ +SET FOREIGN_KEY_CHECKS=0; +-- ---------------------------- +-- Table structure for agents +-- ---------------------------- +CREATE TABLE `agents` ( + `UUID` varchar(36) NOT NULL, + `sessionID` varchar(36) NOT NULL, + `secureSessionID` varchar(36) NOT NULL, + `agentIP` varchar(16) NOT NULL, + `agentPort` int(11) NOT NULL, + `agentOnline` tinyint(4) NOT NULL, + `loginTime` int(11) NOT NULL, + `logoutTime` int(11) NOT NULL, + `currentRegion` varchar(36) NOT NULL, + `currentHandle` bigint(20) unsigned NOT NULL, + `currentPos` varchar(64) NOT NULL, + PRIMARY KEY (`UUID`), + UNIQUE KEY `session` (`sessionID`), + UNIQUE KEY `ssession` (`secureSessionID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; + +-- ---------------------------- +-- Records +-- ---------------------------- diff --git a/OpenSim/Data/MySQL/Resources/CreateAssetsTable.sql b/OpenSim/Data/MySQL/Resources/CreateAssetsTable.sql new file mode 100644 index 0000000..2c750fe --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/CreateAssetsTable.sql @@ -0,0 +1,11 @@ +CREATE TABLE `assets` ( + `id` binary(16) NOT NULL, + `name` varchar(64) NOT NULL, + `description` varchar(64) NOT NULL, + `assetType` tinyint(4) NOT NULL, + `invType` tinyint(4) NOT NULL, + `local` tinyint(1) NOT NULL, + `temporary` tinyint(1) NOT NULL, + `data` longblob NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; \ No newline at end of file diff --git a/OpenSim/Data/MySQL/Resources/CreateFoldersTable.sql b/OpenSim/Data/MySQL/Resources/CreateFoldersTable.sql new file mode 100644 index 0000000..b5bddde --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/CreateFoldersTable.sql @@ -0,0 +1,11 @@ +CREATE TABLE `inventoryfolders` ( + `folderID` varchar(36) NOT NULL default '', + `agentID` varchar(36) default NULL, + `parentFolderID` varchar(36) default NULL, + `folderName` varchar(64) default NULL, + `type` smallint NOT NULL default 0, + `version` int NOT NULL default 0, + PRIMARY KEY (`folderID`), + KEY `owner` (`agentID`), + KEY `parent` (`parentFolderID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 2'; diff --git a/OpenSim/Data/MySQL/Resources/CreateItemsTable.sql b/OpenSim/Data/MySQL/Resources/CreateItemsTable.sql new file mode 100644 index 0000000..1723ee3 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/CreateItemsTable.sql @@ -0,0 +1,18 @@ +CREATE TABLE `inventoryitems` ( + `inventoryID` varchar(36) NOT NULL default '', + `assetID` varchar(36) default NULL, + `assetType` int(11) default NULL, + `parentFolderID` varchar(36) default NULL, + `avatarID` varchar(36) default NULL, + `inventoryName` varchar(64) default NULL, + `inventoryDescription` varchar(128) default NULL, + `inventoryNextPermissions` int(10) unsigned default NULL, + `inventoryCurrentPermissions` int(10) unsigned default NULL, + `invType` int(11) default NULL, + `creatorID` varchar(36) default NULL, + `inventoryBasePermissions` int(10) unsigned NOT NULL default 0, + `inventoryEveryOnePermissions` int(10) unsigned NOT NULL default 0, + PRIMARY KEY (`inventoryID`), + KEY `owner` (`avatarID`), + KEY `folder` (`parentFolderID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 2'; diff --git a/OpenSim/Data/MySQL/Resources/CreateLogsTable.sql b/OpenSim/Data/MySQL/Resources/CreateLogsTable.sql new file mode 100644 index 0000000..64b3a80 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/CreateLogsTable.sql @@ -0,0 +1,10 @@ +CREATE TABLE `logs` ( + `logID` int(10) unsigned NOT NULL auto_increment, + `target` varchar(36) default NULL, + `server` varchar(64) default NULL, + `method` varchar(64) default NULL, + `arguments` varchar(255) default NULL, + `priority` int(11) default NULL, + `message` text, + PRIMARY KEY (`logID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; diff --git a/OpenSim/Data/MySQL/Resources/CreateRegionsTable.sql b/OpenSim/Data/MySQL/Resources/CreateRegionsTable.sql new file mode 100644 index 0000000..cb0f9bd --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/CreateRegionsTable.sql @@ -0,0 +1,32 @@ +CREATE TABLE `regions` ( + `uuid` varchar(36) NOT NULL, + `regionHandle` bigint(20) unsigned NOT NULL, + `regionName` varchar(32) default NULL, + `regionRecvKey` varchar(128) default NULL, + `regionSendKey` varchar(128) default NULL, + `regionSecret` varchar(128) default NULL, + `regionDataURI` varchar(255) default NULL, + `serverIP` varchar(64) default NULL, + `serverPort` int(10) unsigned default NULL, + `serverURI` varchar(255) default NULL, + `locX` int(10) unsigned default NULL, + `locY` int(10) unsigned default NULL, + `locZ` int(10) unsigned default NULL, + `eastOverrideHandle` bigint(20) unsigned default NULL, + `westOverrideHandle` bigint(20) unsigned default NULL, + `southOverrideHandle` bigint(20) unsigned default NULL, + `northOverrideHandle` bigint(20) unsigned default NULL, + `regionAssetURI` varchar(255) default NULL, + `regionAssetRecvKey` varchar(128) default NULL, + `regionAssetSendKey` varchar(128) default NULL, + `regionUserURI` varchar(255) default NULL, + `regionUserRecvKey` varchar(128) default NULL, + `regionUserSendKey` varchar(128) default NULL, `regionMapTexture` varchar(36) default NULL, + `serverHttpPort` int(10) default NULL, `serverRemotingPort` int(10) default NULL, + `owner_uuid` varchar(36) default '00000000-0000-0000-0000-000000000000' not null, + `originUUID` varchar(36), + PRIMARY KEY (`uuid`), + KEY `regionName` (`regionName`), + KEY `regionHandle` (`regionHandle`), + KEY `overrideHandles` (`eastOverrideHandle`,`westOverrideHandle`,`southOverrideHandle`,`northOverrideHandle`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Rev. 3'; diff --git a/OpenSim/Data/MySQL/Resources/CreateUserFriendsTable.sql b/OpenSim/Data/MySQL/Resources/CreateUserFriendsTable.sql new file mode 100644 index 0000000..8480d48 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/CreateUserFriendsTable.sql @@ -0,0 +1,11 @@ +SET FOREIGN_KEY_CHECKS=0; +-- ---------------------------- +-- Table structure for users +-- ---------------------------- +CREATE TABLE `userfriends` ( + `ownerID` VARCHAR(37) NOT NULL, + `friendID` VARCHAR(37) NOT NULL, + `friendPerms` INT NOT NULL, + `datetimestamp` INT NOT NULL, + UNIQUE KEY (`ownerID`, `friendID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev.1'; \ No newline at end of file diff --git a/OpenSim/Data/MySQL/Resources/CreateUsersTable.sql b/OpenSim/Data/MySQL/Resources/CreateUsersTable.sql new file mode 100644 index 0000000..d9e8ae2 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/CreateUsersTable.sql @@ -0,0 +1,35 @@ +SET FOREIGN_KEY_CHECKS=0; +-- ---------------------------- +-- Table structure for users +-- ---------------------------- +CREATE TABLE `users` ( + `UUID` varchar(36) NOT NULL default '', + `username` varchar(32) NOT NULL, + `lastname` varchar(32) NOT NULL, + `passwordHash` varchar(32) NOT NULL, + `passwordSalt` varchar(32) NOT NULL, + `homeRegion` bigint(20) unsigned default NULL, + `homeLocationX` float default NULL, + `homeLocationY` float default NULL, + `homeLocationZ` float default NULL, + `homeLookAtX` float default NULL, + `homeLookAtY` float default NULL, + `homeLookAtZ` float default NULL, + `created` int(11) NOT NULL, + `lastLogin` int(11) NOT NULL, + `userInventoryURI` varchar(255) default NULL, + `userAssetURI` varchar(255) default NULL, + `profileCanDoMask` int(10) unsigned default NULL, + `profileWantDoMask` int(10) unsigned default NULL, + `profileAboutText` text, + `profileFirstText` text, + `profileImage` varchar(36) default NULL, + `profileFirstImage` varchar(36) default NULL, + `webLoginKey` varchar(36) default NULL, + PRIMARY KEY (`UUID`), + UNIQUE KEY `usernames` (`username`,`lastname`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 2'; + +-- ---------------------------- +-- Records +-- ---------------------------- diff --git a/OpenSim/Data/MySQL/Resources/UpgradeFoldersTableToVersion2.sql b/OpenSim/Data/MySQL/Resources/UpgradeFoldersTableToVersion2.sql new file mode 100644 index 0000000..b5a7964 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/UpgradeFoldersTableToVersion2.sql @@ -0,0 +1,4 @@ +ALTER TABLE `inventoryfolders` + ADD COLUMN `type` smallint NOT NULL default 0, + ADD COLUMN `version` int NOT NULL default 0, +COMMENT='Rev. 2'; diff --git a/OpenSim/Data/MySQL/Resources/UpgradeItemsTableToVersion2.sql b/OpenSim/Data/MySQL/Resources/UpgradeItemsTableToVersion2.sql new file mode 100644 index 0000000..d1ef504 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/UpgradeItemsTableToVersion2.sql @@ -0,0 +1,9 @@ +ALTER TABLE `inventoryitems` + CHANGE COLUMN `type` `assetType` int(11) default NULL, + ADD COLUMN `invType` int(11) default NULL, + ADD COLUMN `creatorID` varchar(36) default NULL, + ADD COLUMN `inventoryBasePermissions` int(10) unsigned NOT NULL default 0, + ADD COLUMN `inventoryEveryOnePermissions` int(10) unsigned NOT NULL default 0, +COMMENT='Rev. 2'; + +UPDATE `inventoryitems` SET invType=assetType; diff --git a/OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion2.sql b/OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion2.sql new file mode 100644 index 0000000..034b755 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion2.sql @@ -0,0 +1,4 @@ +ALTER TABLE `regions` + ADD COLUMN `originUUID` varchar(36), +COMMENT='Rev. 2'; +UPDATE `regions` SET originUUID=uuid; diff --git a/OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion3.sql b/OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion3.sql new file mode 100644 index 0000000..b48afec --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion3.sql @@ -0,0 +1,18 @@ +DROP PROCEDURE IF EXISTS upgraderegions3; + +create procedure upgraderegions3() +BEGIN +DECLARE db_name varchar(64); +select database() into db_name; +IF ((select count(*) from information_schema.columns where table_name='regions' and column_name='owner_uuid' and table_schema=db_name) > 0) +THEN + ALTER TABLE `regions`, COMMENT='Rev. 3'; +ELSE + ALTER TABLE `regions` + ADD COLUMN `owner_uuid` varchar(36) default '00000000-0000-0000-0000-000000000000' not null after serverRemotingPort, COMMENT='Rev. 3'; +END IF; +END; + +call upgraderegions3(); + + diff --git a/OpenSim/Data/MySQL/Resources/UpgradeUsersTableToVersion2.sql b/OpenSim/Data/MySQL/Resources/UpgradeUsersTableToVersion2.sql new file mode 100644 index 0000000..dd21a66 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/UpgradeUsersTableToVersion2.sql @@ -0,0 +1,3 @@ +ALTER TABLE `users` + ADD COLUMN `webLoginKey` varchar(36) default '00000000-0000-0000-0000-000000000000' NOT NULL, +COMMENT='Rev. 2'; \ No newline at end of file -- cgit v1.1