From c00c855cc5decf73d598af5d74a2ef57c547efd3 Mon Sep 17 00:00:00 2001 From: Charles Krinke Date: Mon, 12 Jan 2009 20:14:39 +0000 Subject: Thank you kindly, Tlaukkan (Tommil) for a patch that: Cleaned up schema creation scripts of MySQL to compliant form and created SQLite scripts from them. In future it would be optimal if scripts from different databases could be generated from MySQL scripts to ensure optimal consistency between schemas of different databases. Did not yet review column names. ID columns seem to have variety in naming which can be confusing. --- .../Resources/MySQLDialect/001_AssetStore.sql | 25 +-- .../Resources/MySQLDialect/001_GridStore.sql | 67 +++--- .../Resources/MySQLDialect/001_InventoryStore.sql | 74 +++--- .../Resources/MySQLDialect/001_RegionStore.sql | 250 ++++++++++++++------- .../Resources/MySQLDialect/001_UserStore.sql | 200 ++++++++--------- 5 files changed, 346 insertions(+), 270 deletions(-) (limited to 'OpenSim/Data/NHibernate/Resources/MySQLDialect') diff --git a/OpenSim/Data/NHibernate/Resources/MySQLDialect/001_AssetStore.sql b/OpenSim/Data/NHibernate/Resources/MySQLDialect/001_AssetStore.sql index 3484b64..9573781 100644 --- a/OpenSim/Data/NHibernate/Resources/MySQLDialect/001_AssetStore.sql +++ b/OpenSim/Data/NHibernate/Resources/MySQLDialect/001_AssetStore.sql @@ -1,15 +1,10 @@ -START TRANSACTION; - -CREATE TABLE `Assets` ( - `ID` char(36) NOT NULL, - `Type` smallint(6) default NULL, - `InvType` smallint(6) default NULL, - `Name` varchar(64) default NULL, - `Description` varchar(64) default NULL, - `Local` tinyint(1) default NULL, - `Temporary` tinyint(1) default NULL, - `Data` longblob, - PRIMARY KEY (`ID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -COMMIT; \ No newline at end of file +CREATE TABLE Assets ( + ID VARCHAR(36) NOT NULL, + Type TINYINT DEFAULT NULL, + Name VARCHAR(64) DEFAULT NULL, + Description VARCHAR(64) DEFAULT NULL, + Local BIT DEFAULT NULL, + Temporary BIT DEFAULT NULL, + Data LONGBLOB, + PRIMARY KEY (ID) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; diff --git a/OpenSim/Data/NHibernate/Resources/MySQLDialect/001_GridStore.sql b/OpenSim/Data/NHibernate/Resources/MySQLDialect/001_GridStore.sql index df09603..5fb1c19 100644 --- a/OpenSim/Data/NHibernate/Resources/MySQLDialect/001_GridStore.sql +++ b/OpenSim/Data/NHibernate/Resources/MySQLDialect/001_GridStore.sql @@ -1,32 +1,35 @@ -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, - `RegionLocX` int(10) unsigned default NULL, - `RegionLocY` int(10) unsigned default NULL, - `RegionLocZ` 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 `region_name` (`RegionName`), - KEY `region_handle` (`RegionHandle`), - KEY `overrideHandles` (`EastOverrideHandle`,`WestOverrideHandle`,`SouthOverrideHandle`,`NorthOverrideHandle`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Rev. 3'; \ No newline at end of file +CREATE TABLE Regions ( + RegionID VARCHAR(36) NOT NULL, + OwnerID VARCHAR(36) DEFAULT NULL, + OriginID VARCHAR(36) DEFAULT NULL, + RegionHandle BIGINT DEFAULT 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 DEFAULT NULL, + ServerURI VARCHAR(255) DEFAULT NULL, + RegionLocX INT DEFAULT NULL, + RegionLocY INT DEFAULT NULL, + RegionLocZ INT DEFAULT NULL, + EastOverrideHandle BIGINT DEFAULT NULL, + WestOverrideHandle BIGINT DEFAULT NULL, + SouthOverrideHandle BIGINT DEFAULT NULL, + NorthOverrideHandle BIGINT 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 DEFAULT NULL, + ServerRemotingPort INT DEFAULT NULL, + PRIMARY KEY (uuid), +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; + +CREATE INDEX RegionNameIndex ON Regions (RegionName); +CREATE INDEX RegionHandleIndex ON Regions (RegionHandle); +CREATE INDEX RegionHandlesIndex ON Regions (EastOverrideHandle,WestOverrideHandle,SouthOverrideHandle,NorthOverrideHandle); diff --git a/OpenSim/Data/NHibernate/Resources/MySQLDialect/001_InventoryStore.sql b/OpenSim/Data/NHibernate/Resources/MySQLDialect/001_InventoryStore.sql index 80afaa0..13d8cf6 100644 --- a/OpenSim/Data/NHibernate/Resources/MySQLDialect/001_InventoryStore.sql +++ b/OpenSim/Data/NHibernate/Resources/MySQLDialect/001_InventoryStore.sql @@ -1,41 +1,39 @@ -START TRANSACTION; +CREATE TABLE InventoryFolders ( + ID VARCHAR(36) NOT NULL, + Type SMALLINT DEFAULT NULL, + Version SMALLINT DEFAULT NULL, + ParentID VARCHAR(36) DEFAULT NULL, + Owner VARCHAR(36) DEFAULT NULL, + Name VARCHAR(64) DEFAULT NULL, + PRIMARY KEY (ID) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; -CREATE TABLE `InventoryFolders` ( - `ID` char(36) NOT NULL, - `Type` int(11) default NULL, - `Version` int(11) default NULL, - `ParentID` char(36) default NULL, - `Owner` char(36) default NULL, - `Name` varchar(64) default NULL, - PRIMARY KEY (`ID`), - KEY `folder_owner_id` (`Owner`), - KEY `folder_parent_id` (`ParentID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE INDEX InventoryFoldersOwnerIdIndex ON InventoryFolders (Owner); +CREATE INDEX InventoryFoldersParentIdIndex ON InventoryFolders (ParentID); -CREATE TABLE `InventoryItems` ( - `ID` char(36) NOT NULL, - `InvType` smallint(6) default NULL, - `AssetType` smallint(6) default NULL, - `AssetID` char(36) default NULL, - `Folder` char(36) default NULL, - `Owner` char(36) default NULL, - `Creator` char(36) default NULL, - `Name` varchar(64) default NULL, - `Description` varchar(64) default NULL, - `NextPermissions` int(11) default NULL, - `CurrentPermissions` int(11) default NULL, - `BasePermissions` int(11) default NULL, - `EveryOnePermissions` int(11) default NULL, - `GroupID` char(36) default NULL, - `GroupOwned` tinyint(1) default NULL, - `SalePrice` int(11) default NULL, - `SaleType` smallint(6) default NULL, - `Flags` int(11) default NULL, - `CreationDate` int(11) default NULL, - PRIMARY KEY (`ID`), - KEY `item_group_id` (`GroupID`), - KEY `item_owner_id` (`Owner`), - KEY `item_folder_id` (`Folder`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE InventoryItems ( + ID VARCHAR(36) NOT NULL, + InvType SMALLINT DEFAULT NULL, + AssetType SMALLINT DEFAULT NULL, + AssetID VARCHAR(36) DEFAULT NULL, + Folder VARCHAR(36) DEFAULT NULL, + Owner VARCHAR(36) DEFAULT NULL, + Creator VARCHAR(36) DEFAULT NULL, + Name VARCHAR(64) DEFAULT NULL, + Description VARCHAR(64) DEFAULT NULL, + NextPermissions INT DEFAULT NULL, + CurrentPermissions INT DEFAULT NULL, + BasePermissions INT DEFAULT NULL, + EveryOnePermissions INT DEFAULT NULL, + GroupID VARCHAR(36) DEFAULT NULL, + GroupOwned BIT DEFAULT NULL, + SalePrice INT DEFAULT NULL, + SaleType TINYINT DEFAULT NULL, + Flags INT DEFAULT NULL, + CreationDate INT DEFAULT NULL, + PRIMARY KEY (ID) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; -COMMIT; \ No newline at end of file +CREATE INDEX InventoryItemsGroupIdIndex ON InventoryItems (GroupID); +CREATE INDEX InventoryItemsOwnerIdIndex ON InventoryItems (Owner); +CREATE INDEX InventoryItemsFolderIdIndex ON InventoryItems (Folder); diff --git a/OpenSim/Data/NHibernate/Resources/MySQLDialect/001_RegionStore.sql b/OpenSim/Data/NHibernate/Resources/MySQLDialect/001_RegionStore.sql index 158ebb4..e44f012 100644 --- a/OpenSim/Data/NHibernate/Resources/MySQLDialect/001_RegionStore.sql +++ b/OpenSim/Data/NHibernate/Resources/MySQLDialect/001_RegionStore.sql @@ -1,87 +1,169 @@ -CREATE TABLE `Prims` ( - `UUID` char(36) NOT NULL default '', - `RegionID` char(36) default NULL, - `GroupID` char(36) default NULL, - `ParentID` int(11) default NULL, - `ParentUUID` char(36) default NULL, - `OwnerID` char(36) default NULL, - `LastOwnerID` char(36) default NULL, - `CreatorID` char(36) default NULL, - `CreationDate` int(11) default NULL, - `LinkNum` int(11) default NULL, - `Name` varchar(255) default NULL, - `Text` varchar(255) default NULL, - `Description` varchar(255) default NULL, - `SitName` varchar(255) default NULL, - `TouchName` varchar(255) default NULL, - `ObjectFlags` int(11) default NULL, - `OwnerMask` int(11) default NULL, - `NextOwnerMask` int(11) default NULL, - `GroupMask` int(11) default NULL, - `EveryoneMask` int(11) default NULL, - `BaseMask` int(11) default NULL, - `Material` int(1) default NULL, - `ScriptAccessPin` int(11) default NULL, - `TextureAnimation` blob, - `ParticleSystem` blob, - `ClickAction` int(1) default NULL, - `Color` int(11) default NULL, - `PositionX` double default NULL, - `PositionY` double default NULL, - `PositionZ` double default NULL, - `GroupPositionX` double default NULL, - `GroupPositionY` double default NULL, - `GroupPositionZ` double default NULL, - `VelocityX` double default NULL, - `VelocityY` double default NULL, - `VelocityZ` double default NULL, - `AngularVelocityX` double default NULL, - `AngularVelocityY` double default NULL, - `AngularVelocityZ` double default NULL, - `AccelerationX` double default NULL, - `AccelerationY` double default NULL, - `AccelerationZ` double default NULL, - `RotationX` double default NULL, - `RotationY` double default NULL, - `RotationZ` double default NULL, - `RotationW` double default NULL, - `SitTargetOffsetX` double default NULL, - `SitTargetOffsetY` double default NULL, - `SitTargetOffsetZ` double default NULL, - `SitTargetOrientW` double default NULL, - `SitTargetOrientX` double default NULL, - `SitTargetOrientY` double default NULL, - `SitTargetOrientZ` double default NULL, +CREATE TABLE Prims ( + UUID VARCHAR(36) NOT NULL, + RegionID VARCHAR(36) DEFAULT NULL, + GroupID VARCHAR(36) DEFAULT NULL, + ParentID INT DEFAULT NULL, + ParentUUID VARCHAR(36) DEFAULT NULL, + OwnerID VARCHAR(36) DEFAULT NULL, + LastOwnerID VARCHAR(36) DEFAULT NULL, + CreatorID VARCHAR(36) DEFAULT NULL, + CreationDate INT DEFAULT NULL, + LinkNum INT DEFAULT NULL, + Name VARCHAR(255) DEFAULT NULL, + Text VARCHAR(255) DEFAULT NULL, + Description VARCHAR(255) DEFAULT NULL, + SitName VARCHAR(255) DEFAULT NULL, + TouchName VARCHAR(255) DEFAULT NULL, + ObjectFlags INT DEFAULT NULL, + OwnerMask INT DEFAULT NULL, + NextOwnerMask INT DEFAULT NULL, + GroupMask INT DEFAULT NULL, + EveryoneMask INT DEFAULT NULL, + BaseMask INT DEFAULT NULL, + Material TINYINT DEFAULT NULL, + ScriptAccessPin INT DEFAULT NULL, + TextureAnimation BLOB, + ParticleSystem BLOB, + ClickAction TINYINT DEFAULT NULL, + Color INT DEFAULT NULL, + PositionX DOUBLE DEFAULT NULL, + PositionY DOUBLE DEFAULT NULL, + PositionZ DOUBLE DEFAULT NULL, + GroupPositionX DOUBLE DEFAULT NULL, + GroupPositionY DOUBLE DEFAULT NULL, + GroupPositionZ DOUBLE DEFAULT NULL, + VelocityX DOUBLE DEFAULT NULL, + VelocityY DOUBLE DEFAULT NULL, + VelocityZ DOUBLE DEFAULT NULL, + AngularVelocityX DOUBLE DEFAULT NULL, + AngularVelocityY DOUBLE DEFAULT NULL, + AngularVelocityZ DOUBLE DEFAULT NULL, + AccelerationX DOUBLE DEFAULT NULL, + AccelerationY DOUBLE DEFAULT NULL, + AccelerationZ DOUBLE DEFAULT NULL, + RotationX DOUBLE DEFAULT NULL, + RotationY DOUBLE DEFAULT NULL, + RotationZ DOUBLE DEFAULT NULL, + RotationW DOUBLE DEFAULT NULL, + SitTargetOffsetX DOUBLE DEFAULT NULL, + SitTargetOffsetY DOUBLE DEFAULT NULL, + SitTargetOffsetZ DOUBLE DEFAULT NULL, + SitTargetOrientW DOUBLE DEFAULT NULL, + SitTargetOrientX DOUBLE DEFAULT NULL, + SitTargetOrientY DOUBLE DEFAULT NULL, + SitTargetOrientZ DOUBLE DEFAULT NULL, -- this is the shape - `Shape` int(11) default NULL, - `ScaleX` double default NULL, - `ScaleY` double default NULL, - `ScaleZ` double default NULL, - `PCode` int(11) default NULL, - `PathBegin` int(11) default NULL, - `PathEnd` int(11) default NULL, - `PathScaleX` int(11) default NULL, - `PathScaleY` int(11) default NULL, - `PathShearX` int(11) default NULL, - `PathShearY` int(11) default NULL, - `PathSkew` int(11) default NULL, - `PathCurve` int(11) default NULL, - `PathRadiusOffset` int(11) default NULL, - `PathRevolutions` int(11) default NULL, - `PathTaperX` int(11) default NULL, - `PathTaperY` int(11) default NULL, - `PathTwist` int(11) default NULL, - `PathTwistBegin` int(11) default NULL, - `ProfileBegin` int(11) default NULL, - `ProfileEnd` int(11) default NULL, - `ProfileCurve` int(11) default NULL, - `ProfileHollow` int(11) default NULL, - `State` int(11) default NULL, - `Texture` longblob, - `ExtraParams` longblob, - PRIMARY KEY (`UUID`), - KEY `prims_regionuuid` (`RegionID`), - KEY `prims_parentuuid` (`ParentUUID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; + Shape INT DEFAULT NULL, + ScaleX DOUBLE DEFAULT NULL, + ScaleY DOUBLE DEFAULT NULL, + ScaleZ DOUBLE DEFAULT NULL, + PCode INT DEFAULT NULL, + PathBegin INT DEFAULT NULL, + PathEnd INT DEFAULT NULL, + PathScaleX INT DEFAULT NULL, + PathScaleY INT DEFAULT NULL, + PathShearX INT DEFAULT NULL, + PathShearY INT DEFAULT NULL, + PathSkew INT DEFAULT NULL, + PathCurve INT DEFAULT NULL, + PathRadiusOffset INT DEFAULT NULL, + PathRevolutions INT DEFAULT NULL, + PathTaperX INT DEFAULT NULL, + PathTaperY INT DEFAULT NULL, + PathTwist INT DEFAULT NULL, + PathTwistBegin INT DEFAULT NULL, + ProfileBegin INT DEFAULT NULL, + ProfileEnd INT DEFAULT NULL, + ProfileCurve INT DEFAULT NULL, + ProfileHollow INT DEFAULT NULL, + State INT DEFAULT NULL, + Texture LONGBLOB, + ExtraParams LONGBLOB, + PRIMARY KEY (UUID) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; +CREATE INDEX PrimsRegionIdIndex ON Prims (RegionID); +CREATE INDEX PrimsRegionParentUuidIndex ON Prims (ParentUUID); + +CREATE TABLE Terrain ( + RegionID VARCHAR(36) not null, + MapData LONGBLOB, + PRIMARY KEY (RegionID) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; + +CREATE TABLE PrimItems ( + ItemID VARCHAR(36) NOT NULL, + GroupID VARCHAR(36) DEFAULT NULL, + PrimID VARCHAR(36) DEFAULT NULL, + ParentFolderID VARCHAR(36) DEFAULT NULL, + AssetID VARCHAR(36) DEFAULT NULL, + OwnerID VARCHAR(36) DEFAULT NULL, + LastOwnerID VARCHAR(36) DEFAULT NULL, + CreatorID VARCHAR(36) DEFAULT NULL, + CreationDate BIGINT DEFAULT NULL, + InvType INT DEFAULT NULL, + Name VARCHAR(255) DEFAULT NULL, + Description VARCHAR(255) DEFAULT NULL, + NextPermissions INT DEFAULT NULL, + CurrentPermissions INT DEFAULT NULL, + BasePermissions INT DEFAULT NULL, + EveryonePermissions INT DEFAULT NULL, + GroupPermissions INT DEFAULT NULL, + Flags INT DEFAULT NULL, + PRIMARY KEY (ItemID) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; + +CREATE INDEX PrimItemsPrimIdIndex ON PrimItems (PrimID); + +CREATE TABLE RegionSettings ( + RegionID VARCHAR(36) NOT NULL, + + BlockTerraform BIT DEFAULT NULL, + BlockFly BIT DEFAULT NULL, + AllowDamage BIT DEFAULT NULL, + RestrictPushing BIT DEFAULT NULL, + AllowLandResell BIT DEFAULT NULL, + AllowLandJoinDivide BIT DEFAULT NULL, + BlockShowInSearch BIT DEFAULT NULL, + + AgentLimit INT DEFAULT NULL, + ObjectBonus DOUBLE DEFAULT NULL, + Maturity INT DEFAULT NULL, + + DisableScripts BIT DEFAULT NULL, + DisableCollisions BIT DEFAULT NULL, + DisablePhysics BIT DEFAULT NULL, + + TerrainTexture1 VARCHAR(36) DEFAULT NULL, + TerrainTexture2 VARCHAR(36) DEFAULT NULL, + TerrainTexture3 VARCHAR(36) DEFAULT NULL, + TerrainTexture4 VARCHAR(36) DEFAULT NULL, + + Elevation1NW DOUBLE DEFAULT NULL, + Elevation2NW DOUBLE DEFAULT NULL, + Elevation1NE DOUBLE DEFAULT NULL, + Elevation2NE DOUBLE DEFAULT NULL, + Elevation1SE DOUBLE DEFAULT NULL, + Elevation2SE DOUBLE DEFAULT NULL, + Elevation1SW DOUBLE DEFAULT NULL, + Elevation2SW DOUBLE DEFAULT NULL, + + WaterHeight DOUBLE DEFAULT NULL, + TerrainRaiseLimit DOUBLE DEFAULT NULL, + TerrainLowerLimit DOUBLE DEFAULT NULL, + + UseEstateSun BIT DEFAULT NULL, + Sandbox BIT DEFAULT NULL, + + SunVectorX DOUBLE DEFAULT NULL, + SunVectorY DOUBLE DEFAULT NULL, + SunVectorZ DOUBLE DEFAULT NULL, + + FixedSun BIT DEFAULT NULL, + SunPosition DOUBLE DEFAULT NULL, + + Covenant VARCHAR(36) DEFAULT NULL, + + PRIMARY KEY (RegionID) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; diff --git a/OpenSim/Data/NHibernate/Resources/MySQLDialect/001_UserStore.sql b/OpenSim/Data/NHibernate/Resources/MySQLDialect/001_UserStore.sql index a66f5b8..140aea4 100644 --- a/OpenSim/Data/NHibernate/Resources/MySQLDialect/001_UserStore.sql +++ b/OpenSim/Data/NHibernate/Resources/MySQLDialect/001_UserStore.sql @@ -1,106 +1,104 @@ -START TRANSACTION; +CREATE TABLE UserAgents ( + ProfileID VARCHAR(36) NOT NULL, + AgentIP VARCHAR(24) DEFAULT NULL, + AgentPort INT DEFAULT NULL, + AgentOnline BIT DEFAULT NULL, + SessionID VARCHAR(36) DEFAULT NULL, + SecureSessionID VARCHAR(36) DEFAULT NULL, + InitialRegion VARCHAR(255) DEFAULT NULL, + Region VARCHAR(255) DEFAULT NULL, + LoginTime INT DEFAULT NULL, + LogoutTime INT DEFAULT NULL, + Handle BIGINT DEFAULT NULL, + PositionX DOUBLE DEFAULT NULL, + PositionY DOUBLE DEFAULT NULL, + PositionZ DOUBLE DEFAULT NULL, + LookAtX DOUBLE DEFAULT NULL, + LookAtY DOUBLE DEFAULT NULL, + LookAtZ DOUBLE DEFAULT NULL, + PRIMARY KEY (ProfileID) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; -CREATE TABLE `UserAgents` ( - `ProfileID` varchar(36) NOT NULL, - `AgentIP` varchar(24) default NULL, - `AgentPort` int(11) default NULL, - `AgentOnline` tinyint(1) default NULL, - `SessionID` varchar(36) default NULL, - `SecureSessionID` varchar(36) default NULL, - `InitialRegion` varchar(255) default NULL, - `Region` varchar(255) default NULL, - `LoginTime` int(11) default NULL, - `LogoutTime` int(11) default NULL, - `Handle` bigint(20) default NULL, - `PositionX` float default NULL, - `PositionY` float default NULL, - `PositionZ` float default NULL, - `LookAtX` float default NULL, - `LookAtY` float default NULL, - `LookAtZ` float default NULL, - PRIMARY KEY (`ProfileID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE UserProfiles ( + ID VARCHAR(36) NOT NULL, + WebLoginKey VARCHAR(36) DEFAULT NULL, + FirstName VARCHAR(32) DEFAULT NULL, + SurName VARCHAR(32) DEFAULT NULL, + Email VARCHAR(250) DEFAULT NULL, + PasswordHash VARCHAR(32) DEFAULT NULL, + PasswordSalt VARCHAR(32) DEFAULT NULL, + HomeRegionID VARCHAR(36) DEFAULT NULL, + HomeRegionX INT DEFAULT NULL, + HomeRegionY INT DEFAULT NULL, + HomeLocationX DOUBLE DEFAULT NULL, + HomeLocationY DOUBLE DEFAULT NULL, + HomeLocationZ DOUBLE DEFAULT NULL, + HomeLookAtX DOUBLE DEFAULT NULL, + HomeLookAtY DOUBLE DEFAULT NULL, + HomeLookAtZ DOUBLE DEFAULT NULL, + Created INT DEFAULT NULL, + LastLogin INT DEFAULT NULL, + UserInventoryURI VARCHAR(255) DEFAULT NULL, + UserAssetURI VARCHAR(255) DEFAULT NULL, + Image VARCHAR(36) DEFAULT NULL, + FirstLifeImage VARCHAR(36) DEFAULT NULL, + AboutText TEXT DEFAULT NULL, + FirstLifeAboutText TEXT DEFAULT NULL, + CanDoMask INT DEFAULT NULL, + WantDoMask INT DEFAULT NULL, + UserFlags INT DEFAULT NULL, + GodLevel INT DEFAULT NULL, + CustomType VARCHAR(32) DEFAULT NULL, + Partner VARCHAR(36) DEFAULT NULL, + RootInventoryFolderID VARCHAR(36) DEFAULT NULL, + PRIMARY KEY (ID) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; -CREATE TABLE `UserProfiles` ( - `ID` varchar(36) NOT NULL, - `WebLoginKey` varchar(36) default NULL, - `FirstName` varchar(32) default NULL, - `SurName` varchar(32) default NULL, - `Email` varchar(250) default NULL, - `PasswordHash` varchar(32) default NULL, - `PasswordSalt` varchar(32) default NULL, - `HomeRegionID` varchar(36) default NULL, - `HomeRegionX` int(11) default NULL, - `HomeRegionY` int(11) 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) default NULL, - `LastLogin` int(11) default NULL, - `UserInventoryURI` varchar(255) default NULL, - `UserAssetURI` varchar(255) default NULL, - `Image` varchar(36) default NULL, - `FirstLifeImage` varchar(36) default NULL, - `AboutText` text default NULL, - `FirstLifeAboutText` text default NULL, - `CanDoMask` int(11) default NULL, - `WantDoMask` int(11) default NULL, - `UserFlags` int(11) default NULL, - `GodLevel` int(11) default NULL, - `CustomType` varchar(32) default NULL, - `Partner` varchar(36) default NULL, - `RootInventoryFolderID` varchar(36) default NULL, - PRIMARY KEY (`ID`), - INDEX `UserSurnameIndex` (`SurName`), - INDEX `UserFirstNameIndex` (`FirstName`), - UNIQUE INDEX `UserFullNameIndex` (`SurName`,`FirstName`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE INDEX UserSurnameIndex ON UserProfiles (SurName); +CREATE INDEX UserFirstNameIndex ON UserProfiles (FirstName); +CREATE UNIQUE INDEX UserFullNameIndex ON UserProfiles (SurName,FirstName); -CREATE TABLE `UserAppearances` ( - `Owner` varchar(36) NOT NULL, - `BodyItem` varchar(36) default NULL, - `BodyAsset` varchar(36) default NULL, - `SkinItem` varchar(36) default NULL, - `SkinAsset` varchar(36) default NULL, - `HairItem` varchar(36) default NULL, - `HairAsset` varchar(36) default NULL, - `EyesItem` varchar(36) default NULL, - `EyesAsset` varchar(36) default NULL, - `ShirtItem` varchar(36) default NULL, - `ShirtAsset` varchar(36) default NULL, - `PantsItem` varchar(36) default NULL, - `PantsAsset` varchar(36) default NULL, - `ShoesItem` varchar(36) default NULL, - `ShoesAsset` varchar(36) default NULL, - `SocksItem` varchar(36) default NULL, - `SocksAsset` varchar(36) default NULL, - `JacketItem` varchar(36) default NULL, - `JacketAsset` varchar(36) default NULL, - `GlovesItem` varchar(36) default NULL, - `GlovesAsset` varchar(36) default NULL, - `UnderShirtItem` varchar(36) default NULL, - `UnderShirtAsset` varchar(36) default NULL, - `UnderPantsItem` varchar(36) default NULL, - `UnderPantsAsset` varchar(36) default NULL, - `SkirtItem` varchar(36) default NULL, - `SkirtAsset` varchar(36) default NULL, - `Texture` longblob, - `VisualParams` longblob, - `Serial` int(11) default NULL, - `AvatarHeight` float default NULL, - PRIMARY KEY (`Owner`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE UserAppearances ( + Owner VARCHAR(36) NOT NULL, + BodyItem VARCHAR(36) DEFAULT NULL, + BodyAsset VARCHAR(36) DEFAULT NULL, + SkinItem VARCHAR(36) DEFAULT NULL, + SkinAsset VARCHAR(36) DEFAULT NULL, + HairItem VARCHAR(36) DEFAULT NULL, + HairAsset VARCHAR(36) DEFAULT NULL, + EyesItem VARCHAR(36) DEFAULT NULL, + EyesAsset VARCHAR(36) DEFAULT NULL, + ShirtItem VARCHAR(36) DEFAULT NULL, + ShirtAsset VARCHAR(36) DEFAULT NULL, + PantsItem VARCHAR(36) DEFAULT NULL, + PantsAsset VARCHAR(36) DEFAULT NULL, + ShoesItem VARCHAR(36) DEFAULT NULL, + ShoesAsset VARCHAR(36) DEFAULT NULL, + SocksItem VARCHAR(36) DEFAULT NULL, + SocksAsset VARCHAR(36) DEFAULT NULL, + JacketItem VARCHAR(36) DEFAULT NULL, + JacketAsset VARCHAR(36) DEFAULT NULL, + GlovesItem VARCHAR(36) DEFAULT NULL, + GlovesAsset VARCHAR(36) DEFAULT NULL, + UnderShirtItem VARCHAR(36) DEFAULT NULL, + UnderShirtAsset VARCHAR(36) DEFAULT NULL, + UnderPantsItem VARCHAR(36) DEFAULT NULL, + UnderPantsAsset VARCHAR(36) DEFAULT NULL, + SkirtItem VARCHAR(36) DEFAULT NULL, + SkirtAsset VARCHAR(36) DEFAULT NULL, + Texture LONGBLOB, + VisualParams LONGBLOB, + Serial INT DEFAULT NULL, + AvatarHeight FLOAT DEFAULT NULL, + PRIMARY KEY (Owner) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; -CREATE TABLE `UserFriends` ( - `UserFriendID` VARCHAR(36) NOT NULL, - `OwnerID` VARCHAR(36) NOT NULL, - `FriendID` VARCHAR(36) NOT NULL, - `FriendPermissions` INT(11) NOT NULL, - PRIMARY KEY (`UserFriendID`), - UNIQUE INDEX (`OwnerID`,`FriendID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE UserFriends ( + UserFriendID VARCHAR(36) NOT NULL, + OwnerID VARCHAR(36) DEFAULT NULL, + FriendID VARCHAR(36) DEFAULT NULL, + FriendPermissions INT DEFAULT NULL, + PRIMARY KEY (UserFriendID) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; -COMMIT; +CREATE UNIQUE INDEX UserFriendsOwnerIdFriendIdIndex ON UserFriends (OwnerID,FriendID); -- cgit v1.1