aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/SQLiteNG
diff options
context:
space:
mode:
authorJustin Clark-Casey (justincc)2010-04-23 17:17:15 +0100
committerJustin Clark-Casey (justincc)2010-04-23 17:17:15 +0100
commita23bebdc0f106c19de09e54d88eebc6b086fdaab (patch)
tree5a0141201ae27b796608f0af77f4d9a1e1cd1d19 /OpenSim/Data/SQLiteNG
parentminor: change a comment and replace some magic numbers with the AssetType enum (diff)
downloadopensim-SC_OLD-a23bebdc0f106c19de09e54d88eebc6b086fdaab.zip
opensim-SC_OLD-a23bebdc0f106c19de09e54d88eebc6b086fdaab.tar.gz
opensim-SC_OLD-a23bebdc0f106c19de09e54d88eebc6b086fdaab.tar.bz2
opensim-SC_OLD-a23bebdc0f106c19de09e54d88eebc6b086fdaab.tar.xz
Duplicate OpenSim.Data.SQLite into OpenSim.Data.SQLiteNG. SQLiteNG will shortly be changed to work under mono 2.6 and above
Diffstat (limited to 'OpenSim/Data/SQLiteNG')
-rw-r--r--OpenSim/Data/SQLiteNG/Properties/AssemblyInfo.cs65
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/001_AssetStore.sql12
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/001_AuthStore.sql18
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/001_Avatar.sql9
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/001_FriendsStore.sql10
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/001_InventoryStore.sql32
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/001_RegionStore.sql144
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/001_UserAccount.sql17
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/001_UserStore.sql39
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/002_AssetStore.sql10
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/002_AuthStore.sql5
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/002_FriendsStore.sql5
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/002_InventoryStore.sql8
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/002_RegionStore.sql10
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/002_UserAccount.sql5
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/002_UserStore.sql5
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/003_AssetStore.sql1
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/003_InventoryStore.sql5
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/003_RegionStore.sql5
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/003_UserStore.sql6
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/004_AssetStore.sql7
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/004_InventoryStore.sql36
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/004_RegionStore.sql38
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/004_UserStore.sql6
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/005_RegionStore.sql5
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/005_UserStore.sql5
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/006_RegionStore.sql102
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/006_UserStore.sql20
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/007_RegionStore.sql8
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/007_UserStore.sql7
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/008_RegionStore.sql6
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/008_UserStore.sql5
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/009_RegionStore.sql8
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/009_UserStore.sql11
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/010_RegionStore.sql5
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/010_UserStore.sql37
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/011_RegionStore.sql28
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/012_RegionStore.sql5
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/013_RegionStore.sql6
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/014_RegionStore.sql8
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/015_RegionStore.sql6
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/016_RegionStore.sql5
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/017_RegionStore.sql8
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/018_RegionStore.sql79
-rw-r--r--OpenSim/Data/SQLiteNG/Resources/OpenSim.Data.SQLite.addin.xml20
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteAssetData.cs343
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteAuthenticationData.cs262
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteAvatarData.cs74
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteEstateData.cs387
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteFramework.cs91
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteFriendsData.cs70
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteGenericTableHandler.cs268
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteInventoryStore.cs898
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteRegionData.cs2264
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteUserAccountData.cs81
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteUtils.cs307
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteXInventoryData.cs155
-rw-r--r--OpenSim/Data/SQLiteNG/Tests/SQLiteAssetTest.cs64
-rw-r--r--OpenSim/Data/SQLiteNG/Tests/SQLiteEstateTest.cs65
-rw-r--r--OpenSim/Data/SQLiteNG/Tests/SQLiteInventoryTest.cs66
-rw-r--r--OpenSim/Data/SQLiteNG/Tests/SQLiteRegionTest.cs64
61 files changed, 6341 insertions, 0 deletions
diff --git a/OpenSim/Data/SQLiteNG/Properties/AssemblyInfo.cs b/OpenSim/Data/SQLiteNG/Properties/AssemblyInfo.cs
new file mode 100644
index 0000000..d45ab50
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Properties/AssemblyInfo.cs
@@ -0,0 +1,65 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System.Reflection;
29using System.Runtime.InteropServices;
30
31// General information about an assembly is controlled through the following
32// set of attributes. Change these attribute values to modify the information
33// associated with an assembly.
34
35[assembly : AssemblyTitle("OpenSim.Data.SQLite")]
36[assembly : AssemblyDescription("")]
37[assembly : AssemblyConfiguration("")]
38[assembly : AssemblyCompany("http://opensimulator.org")]
39[assembly : AssemblyProduct("OpenSim.Data.SQLite")]
40[assembly : AssemblyCopyright("Copyright (c) OpenSimulator.org Developers 2007-2009")]
41[assembly : AssemblyTrademark("")]
42[assembly : AssemblyCulture("")]
43
44// Setting ComVisible to false makes the types in this assembly not visible
45// to COM components. If you need to access a type in this assembly from
46// COM, set the ComVisible attribute to true on that type.
47
48[assembly : ComVisible(false)]
49
50// The following GUID is for the ID of the typelib if this project is exposed to COM
51
52[assembly : Guid("6113d5ce-4547-49f4-9236-0dcc503457b1")]
53
54// Version information for an assembly consists of the following four values:
55//
56// Major Version
57// Minor Version
58// Build Number
59// Revision
60//
61// You can specify all the values or you can default the Revision and Build Numbers
62// by using the '*' as shown below:
63
64[assembly : AssemblyVersion("0.6.5.*")]
65[assembly : AssemblyFileVersion("0.6.5.0")]
diff --git a/OpenSim/Data/SQLiteNG/Resources/001_AssetStore.sql b/OpenSim/Data/SQLiteNG/Resources/001_AssetStore.sql
new file mode 100644
index 0000000..2e026ca
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/001_AssetStore.sql
@@ -0,0 +1,12 @@
1BEGIN TRANSACTION;
2CREATE TABLE assets(
3 UUID varchar(255) primary key,
4 Name varchar(255),
5 Description varchar(255),
6 Type integer,
7 InvType integer,
8 Local integer,
9 Temporary integer,
10 Data blob);
11
12COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/001_AuthStore.sql b/OpenSim/Data/SQLiteNG/Resources/001_AuthStore.sql
new file mode 100644
index 0000000..468567d
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/001_AuthStore.sql
@@ -0,0 +1,18 @@
1BEGIN TRANSACTION;
2
3CREATE TABLE auth (
4 UUID char(36) NOT NULL,
5 passwordHash char(32) NOT NULL default '',
6 passwordSalt char(32) NOT NULL default '',
7 webLoginKey varchar(255) NOT NULL default '',
8 accountType VARCHAR(32) NOT NULL DEFAULT 'UserAccount',
9 PRIMARY KEY (`UUID`)
10);
11
12CREATE TABLE tokens (
13 UUID char(36) NOT NULL,
14 token varchar(255) NOT NULL,
15 validity datetime NOT NULL
16);
17
18COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/001_Avatar.sql b/OpenSim/Data/SQLiteNG/Resources/001_Avatar.sql
new file mode 100644
index 0000000..7ec906b
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/001_Avatar.sql
@@ -0,0 +1,9 @@
1BEGIN TRANSACTION;
2
3CREATE TABLE Avatars (
4 PrincipalID CHAR(36) NOT NULL,
5 Name VARCHAR(32) NOT NULL,
6 Value VARCHAR(255) NOT NULL DEFAULT '',
7 PRIMARY KEY(PrincipalID, Name));
8
9COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/001_FriendsStore.sql b/OpenSim/Data/SQLiteNG/Resources/001_FriendsStore.sql
new file mode 100644
index 0000000..f1b9ab9
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/001_FriendsStore.sql
@@ -0,0 +1,10 @@
1BEGIN TRANSACTION;
2
3CREATE TABLE `Friends` (
4 `PrincipalID` CHAR(36) NOT NULL,
5 `Friend` VARCHAR(255) NOT NULL,
6 `Flags` VARCHAR(16) NOT NULL DEFAULT 0,
7 `Offered` VARCHAR(32) NOT NULL DEFAULT 0,
8 PRIMARY KEY(`PrincipalID`, `Friend`));
9
10COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/001_InventoryStore.sql b/OpenSim/Data/SQLiteNG/Resources/001_InventoryStore.sql
new file mode 100644
index 0000000..554d5c2
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/001_InventoryStore.sql
@@ -0,0 +1,32 @@
1BEGIN TRANSACTION;
2
3CREATE TABLE inventoryfolders(
4 UUID varchar(255) primary key,
5 name varchar(255),
6 agentID varchar(255),
7 parentID varchar(255),
8 type integer,
9 version integer);
10
11CREATE TABLE inventoryitems(
12 UUID varchar(255) primary key,
13 assetID varchar(255),
14 assetType integer,
15 invType integer,
16 parentFolderID varchar(255),
17 avatarID varchar(255),
18 creatorsID varchar(255),
19 inventoryName varchar(255),
20 inventoryDescription varchar(255),
21 inventoryNextPermissions integer,
22 inventoryCurrentPermissions integer,
23 inventoryBasePermissions integer,
24 inventoryEveryOnePermissions integer,
25 salePrice integer default 99,
26 saleType integer default 0,
27 creationDate integer default 2000,
28 groupID varchar(255) default '00000000-0000-0000-0000-000000000000',
29 groupOwned integer default 0,
30 flags integer default 0);
31
32COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/001_RegionStore.sql b/OpenSim/Data/SQLiteNG/Resources/001_RegionStore.sql
new file mode 100644
index 0000000..39e8180
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/001_RegionStore.sql
@@ -0,0 +1,144 @@
1BEGIN TRANSACTION;
2
3CREATE TABLE prims(
4 UUID varchar(255) primary key,
5 RegionUUID varchar(255),
6 ParentID integer,
7 CreationDate integer,
8 Name varchar(255),
9 SceneGroupID varchar(255),
10 Text varchar(255),
11 Description varchar(255),
12 SitName varchar(255),
13 TouchName varchar(255),
14 CreatorID varchar(255),
15 OwnerID varchar(255),
16 GroupID varchar(255),
17 LastOwnerID varchar(255),
18 OwnerMask integer,
19 NextOwnerMask integer,
20 GroupMask integer,
21 EveryoneMask integer,
22 BaseMask integer,
23 PositionX float,
24 PositionY float,
25 PositionZ float,
26 GroupPositionX float,
27 GroupPositionY float,
28 GroupPositionZ float,
29 VelocityX float,
30 VelocityY float,
31 VelocityZ float,
32 AngularVelocityX float,
33 AngularVelocityY float,
34 AngularVelocityZ float,
35 AccelerationX float,
36 AccelerationY float,
37 AccelerationZ float,
38 RotationX float,
39 RotationY float,
40 RotationZ float,
41 RotationW float,
42 ObjectFlags integer,
43 SitTargetOffsetX float NOT NULL default 0,
44 SitTargetOffsetY float NOT NULL default 0,
45 SitTargetOffsetZ float NOT NULL default 0,
46 SitTargetOrientW float NOT NULL default 0,
47 SitTargetOrientX float NOT NULL default 0,
48 SitTargetOrientY float NOT NULL default 0,
49 SitTargetOrientZ float NOT NULL default 0);
50
51CREATE TABLE primshapes(
52 UUID varchar(255) primary key,
53 Shape integer,
54 ScaleX float,
55 ScaleY float,
56 ScaleZ float,
57 PCode integer,
58 PathBegin integer,
59 PathEnd integer,
60 PathScaleX integer,
61 PathScaleY integer,
62 PathShearX integer,
63 PathShearY integer,
64 PathSkew integer,
65 PathCurve integer,
66 PathRadiusOffset integer,
67 PathRevolutions integer,
68 PathTaperX integer,
69 PathTaperY integer,
70 PathTwist integer,
71 PathTwistBegin integer,
72 ProfileBegin integer,
73 ProfileEnd integer,
74 ProfileCurve integer,
75 ProfileHollow integer,
76 Texture blob,
77 ExtraParams blob,
78 State Integer NOT NULL default 0);
79
80CREATE TABLE primitems(
81 itemID varchar(255) primary key,
82 primID varchar(255),
83 assetID varchar(255),
84 parentFolderID varchar(255),
85 invType integer,
86 assetType integer,
87 name varchar(255),
88 description varchar(255),
89 creationDate integer,
90 creatorID varchar(255),
91 ownerID varchar(255),
92 lastOwnerID varchar(255),
93 groupID varchar(255),
94 nextPermissions string,
95 currentPermissions string,
96 basePermissions string,
97 everyonePermissions string,
98 groupPermissions string);
99
100CREATE TABLE terrain(
101 RegionUUID varchar(255),
102 Revision integer,
103 Heightfield blob);
104
105CREATE TABLE land(
106 UUID varchar(255) primary key,
107 RegionUUID varchar(255),
108 LocalLandID string,
109 Bitmap blob,
110 Name varchar(255),
111 Desc varchar(255),
112 OwnerUUID varchar(255),
113 IsGroupOwned string,
114 Area integer,
115 AuctionID integer,
116 Category integer,
117 ClaimDate integer,
118 ClaimPrice integer,
119 GroupUUID varchar(255),
120 SalePrice integer,
121 LandStatus integer,
122 LandFlags string,
123 LandingType string,
124 MediaAutoScale string,
125 MediaTextureUUID varchar(255),
126 MediaURL varchar(255),
127 MusicURL varchar(255),
128 PassHours float,
129 PassPrice string,
130 SnapshotUUID varchar(255),
131 UserLocationX float,
132 UserLocationY float,
133 UserLocationZ float,
134 UserLookAtX float,
135 UserLookAtY float,
136 UserLookAtZ float,
137 AuthbuyerID varchar(36) NOT NULL default '00000000-0000-0000-0000-000000000000');
138
139CREATE TABLE landaccesslist(
140 LandUUID varchar(255),
141 AccessUUID varchar(255),
142 Flags string);
143
144COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/001_UserAccount.sql b/OpenSim/Data/SQLiteNG/Resources/001_UserAccount.sql
new file mode 100644
index 0000000..c38d9a7
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/001_UserAccount.sql
@@ -0,0 +1,17 @@
1BEGIN TRANSACTION;
2
3-- useraccounts table
4CREATE TABLE UserAccounts (
5 PrincipalID CHAR(36) primary key,
6 ScopeID CHAR(36) NOT NULL,
7 FirstName VARCHAR(64) NOT NULL,
8 LastName VARCHAR(64) NOT NULL,
9 Email VARCHAR(64),
10 ServiceURLs TEXT,
11 Created INT(11),
12 UserLevel integer NOT NULL DEFAULT 0,
13 UserFlags integer NOT NULL DEFAULT 0,
14 UserTitle varchar(64) NOT NULL DEFAULT ''
15);
16
17COMMIT; \ No newline at end of file
diff --git a/OpenSim/Data/SQLiteNG/Resources/001_UserStore.sql b/OpenSim/Data/SQLiteNG/Resources/001_UserStore.sql
new file mode 100644
index 0000000..b584594
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/001_UserStore.sql
@@ -0,0 +1,39 @@
1BEGIN TRANSACTION;
2
3-- users table
4CREATE TABLE users(
5 UUID varchar(255) primary key,
6 username varchar(255),
7 surname varchar(255),
8 passwordHash varchar(255),
9 passwordSalt varchar(255),
10 homeRegionX integer,
11 homeRegionY integer,
12 homeLocationX float,
13 homeLocationY float,
14 homeLocationZ float,
15 homeLookAtX float,
16 homeLookAtY float,
17 homeLookAtZ float,
18 created integer,
19 lastLogin integer,
20 rootInventoryFolderID varchar(255),
21 userInventoryURI varchar(255),
22 userAssetURI varchar(255),
23 profileCanDoMask integer,
24 profileWantDoMask integer,
25 profileAboutText varchar(255),
26 profileFirstText varchar(255),
27 profileImage varchar(255),
28 profileFirstImage varchar(255),
29 webLoginKey text default '00000000-0000-0000-0000-000000000000');
30-- friends table
31CREATE TABLE userfriends(
32 ownerID varchar(255),
33 friendID varchar(255),
34 friendPerms integer,
35 ownerPerms integer,
36 datetimestamp integer);
37
38COMMIT;
39
diff --git a/OpenSim/Data/SQLiteNG/Resources/002_AssetStore.sql b/OpenSim/Data/SQLiteNG/Resources/002_AssetStore.sql
new file mode 100644
index 0000000..5339b84
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/002_AssetStore.sql
@@ -0,0 +1,10 @@
1BEGIN TRANSACTION;
2
3CREATE TEMPORARY TABLE assets_backup(UUID,Name,Description,Type,Local,Temporary,Data);
4INSERT INTO assets_backup SELECT UUID,Name,Description,Type,Local,Temporary,Data FROM assets;
5DROP TABLE assets;
6CREATE TABLE assets(UUID,Name,Description,Type,Local,Temporary,Data);
7INSERT INTO assets SELECT UUID,Name,Description,Type,Local,Temporary,Data FROM assets_backup;
8DROP TABLE assets_backup;
9
10COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/002_AuthStore.sql b/OpenSim/Data/SQLiteNG/Resources/002_AuthStore.sql
new file mode 100644
index 0000000..3237b68
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/002_AuthStore.sql
@@ -0,0 +1,5 @@
1BEGIN TRANSACTION;
2
3INSERT INTO auth (UUID, passwordHash, passwordSalt, webLoginKey) SELECT `UUID` AS UUID, `passwordHash` AS passwordHash, `passwordSalt` AS passwordSalt, `webLoginKey` AS webLoginKey FROM users;
4
5COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/002_FriendsStore.sql b/OpenSim/Data/SQLiteNG/Resources/002_FriendsStore.sql
new file mode 100644
index 0000000..6733502
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/002_FriendsStore.sql
@@ -0,0 +1,5 @@
1BEGIN TRANSACTION;
2
3INSERT INTO `Friends` SELECT `ownerID`, `friendID`, `friendPerms`, 0 FROM `userfriends`;
4
5COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/002_InventoryStore.sql b/OpenSim/Data/SQLiteNG/Resources/002_InventoryStore.sql
new file mode 100644
index 0000000..01951d6
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/002_InventoryStore.sql
@@ -0,0 +1,8 @@
1BEGIN TRANSACTION;
2
3create index inventoryfolders_agentid on inventoryfolders(agentid);
4create index inventoryfolders_parentid on inventoryfolders(parentid);
5create index inventoryitems_parentfolderid on inventoryitems(parentfolderid);
6create index inventoryitems_avatarid on inventoryitems(avatarid);
7
8COMMIT; \ No newline at end of file
diff --git a/OpenSim/Data/SQLiteNG/Resources/002_RegionStore.sql b/OpenSim/Data/SQLiteNG/Resources/002_RegionStore.sql
new file mode 100644
index 0000000..c5c7c99
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/002_RegionStore.sql
@@ -0,0 +1,10 @@
1BEGIN TRANSACTION;
2
3CREATE TABLE regionban(
4 regionUUID varchar (255),
5 bannedUUID varchar (255),
6 bannedIp varchar (255),
7 bannedIpHostMask varchar (255)
8 );
9
10COMMIT; \ No newline at end of file
diff --git a/OpenSim/Data/SQLiteNG/Resources/002_UserAccount.sql b/OpenSim/Data/SQLiteNG/Resources/002_UserAccount.sql
new file mode 100644
index 0000000..c7a6293
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/002_UserAccount.sql
@@ -0,0 +1,5 @@
1BEGIN TRANSACTION;
2
3INSERT INTO UserAccounts (PrincipalID, ScopeID, FirstName, LastName, Email, ServiceURLs, Created) SELECT `UUID` AS PrincipalID, '00000000-0000-0000-0000-000000000000' AS ScopeID, username AS FirstName, surname AS LastName, '' as Email, '' AS ServiceURLs, created as Created FROM users;
4
5COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/002_UserStore.sql b/OpenSim/Data/SQLiteNG/Resources/002_UserStore.sql
new file mode 100644
index 0000000..48fc680
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/002_UserStore.sql
@@ -0,0 +1,5 @@
1BEGIN;
2
3ALTER TABLE users add homeRegionID varchar(36) NOT NULL default '00000000-0000-0000-0000-000000000000';
4
5COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/003_AssetStore.sql b/OpenSim/Data/SQLiteNG/Resources/003_AssetStore.sql
new file mode 100644
index 0000000..f54f8d9
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/003_AssetStore.sql
@@ -0,0 +1 @@
DELETE FROM assets WHERE UUID = 'dc4b9f0bd00845c696a401dd947ac621'
diff --git a/OpenSim/Data/SQLiteNG/Resources/003_InventoryStore.sql b/OpenSim/Data/SQLiteNG/Resources/003_InventoryStore.sql
new file mode 100644
index 0000000..4c6da91
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/003_InventoryStore.sql
@@ -0,0 +1,5 @@
1BEGIN;
2
3alter table inventoryitems add column inventoryGroupPermissions integer unsigned not null default 0;
4
5COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/003_RegionStore.sql b/OpenSim/Data/SQLiteNG/Resources/003_RegionStore.sql
new file mode 100644
index 0000000..4db2f75
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/003_RegionStore.sql
@@ -0,0 +1,5 @@
1BEGIN;
2
3ALTER TABLE primitems add flags integer not null default 0;
4
5COMMIT; \ No newline at end of file
diff --git a/OpenSim/Data/SQLiteNG/Resources/003_UserStore.sql b/OpenSim/Data/SQLiteNG/Resources/003_UserStore.sql
new file mode 100644
index 0000000..6f890ee
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/003_UserStore.sql
@@ -0,0 +1,6 @@
1BEGIN;
2
3ALTER TABLE users add userFlags integer NOT NULL default 0;
4ALTER TABLE users add godLevel integer NOT NULL default 0;
5
6COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/004_AssetStore.sql b/OpenSim/Data/SQLiteNG/Resources/004_AssetStore.sql
new file mode 100644
index 0000000..39421c4
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/004_AssetStore.sql
@@ -0,0 +1,7 @@
1BEGIN;
2
3update assets
4 set UUID = substr(UUID, 1, 8) || "-" || substr(UUID, 9, 4) || "-" || substr(UUID, 13, 4) || "-" || substr(UUID, 17, 4) || "-" || substr(UUID, 21, 12)
5 where UUID not like '%-%';
6
7COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/004_InventoryStore.sql b/OpenSim/Data/SQLiteNG/Resources/004_InventoryStore.sql
new file mode 100644
index 0000000..e8f4d46
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/004_InventoryStore.sql
@@ -0,0 +1,36 @@
1BEGIN;
2
3update inventoryitems
4 set UUID = substr(UUID, 1, 8) || "-" || substr(UUID, 9, 4) || "-" || substr(UUID, 13, 4) || "-" || substr(UUID, 17, 4) || "-" || substr(UUID, 21, 12)
5 where UUID not like '%-%';
6
7update inventoryitems
8 set assetID = substr(assetID, 1, 8) || "-" || substr(assetID, 9, 4) || "-" || substr(assetID, 13, 4) || "-" || substr(assetID, 17, 4) || "-" || substr(assetID, 21, 12)
9 where assetID not like '%-%';
10
11update inventoryitems
12 set parentFolderID = substr(parentFolderID, 1, 8) || "-" || substr(parentFolderID, 9, 4) || "-" || substr(parentFolderID, 13, 4) || "-" || substr(parentFolderID, 17, 4) || "-" || substr(parentFolderID, 21, 12)
13 where parentFolderID not like '%-%';
14
15update inventoryitems
16 set avatarID = substr(avatarID, 1, 8) || "-" || substr(avatarID, 9, 4) || "-" || substr(avatarID, 13, 4) || "-" || substr(avatarID, 17, 4) || "-" || substr(avatarID, 21, 12)
17 where avatarID not like '%-%';
18
19update inventoryitems
20 set creatorsID = substr(creatorsID, 1, 8) || "-" || substr(creatorsID, 9, 4) || "-" || substr(creatorsID, 13, 4) || "-" || substr(creatorsID, 17, 4) || "-" || substr(creatorsID, 21, 12)
21 where creatorsID not like '%-%';
22
23
24update inventoryfolders
25 set UUID = substr(UUID, 1, 8) || "-" || substr(UUID, 9, 4) || "-" || substr(UUID, 13, 4) || "-" || substr(UUID, 17, 4) || "-" || substr(UUID, 21, 12)
26 where UUID not like '%-%';
27
28update inventoryfolders
29 set agentID = substr(agentID, 1, 8) || "-" || substr(agentID, 9, 4) || "-" || substr(agentID, 13, 4) || "-" || substr(agentID, 17, 4) || "-" || substr(agentID, 21, 12)
30 where agentID not like '%-%';
31
32update inventoryfolders
33 set parentID = substr(parentID, 1, 8) || "-" || substr(parentID, 9, 4) || "-" || substr(parentID, 13, 4) || "-" || substr(parentID, 17, 4) || "-" || substr(parentID, 21, 12)
34 where parentID not like '%-%';
35
36COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/004_RegionStore.sql b/OpenSim/Data/SQLiteNG/Resources/004_RegionStore.sql
new file mode 100644
index 0000000..de328cb
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/004_RegionStore.sql
@@ -0,0 +1,38 @@
1BEGIN;
2
3create table regionsettings (
4 regionUUID char(36) not null,
5 block_terraform integer not null,
6 block_fly integer not null,
7 allow_damage integer not null,
8 restrict_pushing integer not null,
9 allow_land_resell integer not null,
10 allow_land_join_divide integer not null,
11 block_show_in_search integer not null,
12 agent_limit integer not null,
13 object_bonus float not null,
14 maturity integer not null,
15 disable_scripts integer not null,
16 disable_collisions integer not null,
17 disable_physics integer not null,
18 terrain_texture_1 char(36) not null,
19 terrain_texture_2 char(36) not null,
20 terrain_texture_3 char(36) not null,
21 terrain_texture_4 char(36) not null,
22 elevation_1_nw float not null,
23 elevation_2_nw float not null,
24 elevation_1_ne float not null,
25 elevation_2_ne float not null,
26 elevation_1_se float not null,
27 elevation_2_se float not null,
28 elevation_1_sw float not null,
29 elevation_2_sw float not null,
30 water_height float not null,
31 terrain_raise_limit float not null,
32 terrain_lower_limit float not null,
33 use_estate_sun integer not null,
34 fixed_sun integer not null,
35 sun_position float not null,
36 covenant char(36));
37
38COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/004_UserStore.sql b/OpenSim/Data/SQLiteNG/Resources/004_UserStore.sql
new file mode 100644
index 0000000..03142af
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/004_UserStore.sql
@@ -0,0 +1,6 @@
1BEGIN;
2
3ALTER TABLE users add customType varchar(32) not null default '';
4ALTER TABLE users add partner char(36) not null default '00000000-0000-0000-0000-000000000000';
5
6COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/005_RegionStore.sql b/OpenSim/Data/SQLiteNG/Resources/005_RegionStore.sql
new file mode 100644
index 0000000..1f6d1bd
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/005_RegionStore.sql
@@ -0,0 +1,5 @@
1BEGIN;
2
3delete from regionsettings;
4
5COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/005_UserStore.sql b/OpenSim/Data/SQLiteNG/Resources/005_UserStore.sql
new file mode 100644
index 0000000..e45c09a
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/005_UserStore.sql
@@ -0,0 +1,5 @@
1BEGIN;
2
3CREATE TABLE `avatarattachments` (`UUID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', `attachpoint` int(11) NOT NULL DEFAULT 0, `item` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', `asset` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000');
4
5COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/006_RegionStore.sql b/OpenSim/Data/SQLiteNG/Resources/006_RegionStore.sql
new file mode 100644
index 0000000..94ed818
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/006_RegionStore.sql
@@ -0,0 +1,102 @@
1BEGIN TRANSACTION;
2
3CREATE TABLE estate_groups (
4 EstateID int(10) NOT NULL,
5 uuid char(36) NOT NULL
6);
7
8CREATE TABLE estate_managers (
9 EstateID int(10) NOT NULL,
10 uuid char(36) NOT NULL
11);
12
13CREATE TABLE estate_map (
14 RegionID char(36) NOT NULL default '00000000-0000-0000-0000-000000000000',
15 EstateID int(11) NOT NULL
16);
17
18CREATE TABLE estate_settings (
19 EstateID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
20 EstateName varchar(64) default NULL,
21 AbuseEmailToEstateOwner tinyint(4) NOT NULL,
22 DenyAnonymous tinyint(4) NOT NULL,
23 ResetHomeOnTeleport tinyint(4) NOT NULL,
24 FixedSun tinyint(4) NOT NULL,
25 DenyTransacted tinyint(4) NOT NULL,
26 BlockDwell tinyint(4) NOT NULL,
27 DenyIdentified tinyint(4) NOT NULL,
28 AllowVoice tinyint(4) NOT NULL,
29 UseGlobalTime tinyint(4) NOT NULL,
30 PricePerMeter int(11) NOT NULL,
31 TaxFree tinyint(4) NOT NULL,
32 AllowDirectTeleport tinyint(4) NOT NULL,
33 RedirectGridX int(11) NOT NULL,
34 RedirectGridY int(11) NOT NULL,
35 ParentEstateID int(10) NOT NULL,
36 SunPosition double NOT NULL,
37 EstateSkipScripts tinyint(4) NOT NULL,
38 BillableFactor float NOT NULL,
39 PublicAccess tinyint(4) NOT NULL
40);
41insert into estate_settings (EstateID,EstateName,AbuseEmailToEstateOwner,DenyAnonymous,ResetHomeOnTeleport,FixedSun,DenyTransacted,BlockDwell,DenyIdentified,AllowVoice,UseGlobalTime,PricePerMeter,TaxFree,AllowDirectTeleport,RedirectGridX,RedirectGridY,ParentEstateID,SunPosition,PublicAccess,EstateSkipScripts,BillableFactor) values ( 99, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '');
42delete from estate_settings;
43CREATE TABLE estate_users (
44 EstateID int(10) NOT NULL,
45 uuid char(36) NOT NULL
46);
47
48CREATE TABLE estateban (
49 EstateID int(10) NOT NULL,
50 bannedUUID varchar(36) NOT NULL,
51 bannedIp varchar(16) NOT NULL,
52 bannedIpHostMask varchar(16) NOT NULL,
53 bannedNameMask varchar(64) default NULL
54);
55
56drop table regionsettings;
57CREATE TABLE regionsettings (
58 regionUUID char(36) NOT NULL,
59 block_terraform int(11) NOT NULL,
60 block_fly int(11) NOT NULL,
61 allow_damage int(11) NOT NULL,
62 restrict_pushing int(11) NOT NULL,
63 allow_land_resell int(11) NOT NULL,
64 allow_land_join_divide int(11) NOT NULL,
65 block_show_in_search int(11) NOT NULL,
66 agent_limit int(11) NOT NULL,
67 object_bonus float NOT NULL,
68 maturity int(11) NOT NULL,
69 disable_scripts int(11) NOT NULL,
70 disable_collisions int(11) NOT NULL,
71 disable_physics int(11) NOT NULL,
72 terrain_texture_1 char(36) NOT NULL,
73 terrain_texture_2 char(36) NOT NULL,
74 terrain_texture_3 char(36) NOT NULL,
75 terrain_texture_4 char(36) NOT NULL,
76 elevation_1_nw float NOT NULL,
77 elevation_2_nw float NOT NULL,
78 elevation_1_ne float NOT NULL,
79 elevation_2_ne float NOT NULL,
80 elevation_1_se float NOT NULL,
81 elevation_2_se float NOT NULL,
82 elevation_1_sw float NOT NULL,
83 elevation_2_sw float NOT NULL,
84 water_height float NOT NULL,
85 terrain_raise_limit float NOT NULL,
86 terrain_lower_limit float NOT NULL,
87 use_estate_sun int(11) NOT NULL,
88 fixed_sun int(11) NOT NULL,
89 sun_position float NOT NULL,
90 covenant char(36) default NULL,
91 Sandbox tinyint(4) NOT NULL,
92 PRIMARY KEY (regionUUID)
93);
94
95CREATE INDEX estate_ban_estate_id on estateban(EstateID);
96CREATE INDEX estate_groups_estate_id on estate_groups(EstateID);
97CREATE INDEX estate_managers_estate_id on estate_managers(EstateID);
98CREATE INDEX estate_map_estate_id on estate_map(EstateID);
99CREATE UNIQUE INDEX estate_map_region_id on estate_map(RegionID);
100CREATE INDEX estate_users_estate_id on estate_users(EstateID);
101
102COMMIT; \ No newline at end of file
diff --git a/OpenSim/Data/SQLiteNG/Resources/006_UserStore.sql b/OpenSim/Data/SQLiteNG/Resources/006_UserStore.sql
new file mode 100644
index 0000000..f9454c5
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/006_UserStore.sql
@@ -0,0 +1,20 @@
1BEGIN TRANSACTION;
2
3-- usersagents table
4CREATE TABLE IF NOT EXISTS useragents(
5 UUID varchar(255) primary key,
6 agentIP varchar(255),
7 agentPort integer,
8 agentOnline boolean,
9 sessionID varchar(255),
10 secureSessionID varchar(255),
11 regionID varchar(255),
12 loginTime integer,
13 logoutTime integer,
14 currentRegion varchar(255),
15 currentHandle varchar(255),
16 currentPosX float,
17 currentPosY float,
18 currentPosZ float);
19
20COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/007_RegionStore.sql b/OpenSim/Data/SQLiteNG/Resources/007_RegionStore.sql
new file mode 100644
index 0000000..1c813a0
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/007_RegionStore.sql
@@ -0,0 +1,8 @@
1begin;
2
3alter table estate_settings add column AbuseEmail varchar(255) not null default '';
4
5alter table estate_settings add column EstateOwner varchar(36) not null default '';
6
7commit;
8
diff --git a/OpenSim/Data/SQLiteNG/Resources/007_UserStore.sql b/OpenSim/Data/SQLiteNG/Resources/007_UserStore.sql
new file mode 100644
index 0000000..8b0cd28
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/007_UserStore.sql
@@ -0,0 +1,7 @@
1BEGIN TRANSACTION;
2
3ALTER TABLE useragents add currentLookAtX float not null default 128;
4ALTER TABLE useragents add currentLookAtY float not null default 128;
5ALTER TABLE useragents add currentLookAtZ float not null default 70;
6
7COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/008_RegionStore.sql b/OpenSim/Data/SQLiteNG/Resources/008_RegionStore.sql
new file mode 100644
index 0000000..28bfbf5
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/008_RegionStore.sql
@@ -0,0 +1,6 @@
1begin;
2
3alter table estate_settings add column DenyMinors tinyint not null default 0;
4
5commit;
6
diff --git a/OpenSim/Data/SQLiteNG/Resources/008_UserStore.sql b/OpenSim/Data/SQLiteNG/Resources/008_UserStore.sql
new file mode 100644
index 0000000..97da818
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/008_UserStore.sql
@@ -0,0 +1,5 @@
1BEGIN TRANSACTION;
2
3ALTER TABLE users add email varchar(250);
4
5COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/009_RegionStore.sql b/OpenSim/Data/SQLiteNG/Resources/009_RegionStore.sql
new file mode 100644
index 0000000..1f40548
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/009_RegionStore.sql
@@ -0,0 +1,8 @@
1BEGIN;
2
3ALTER TABLE prims ADD COLUMN ColorR integer not null default 0;
4ALTER TABLE prims ADD COLUMN ColorG integer not null default 0;
5ALTER TABLE prims ADD COLUMN ColorB integer not null default 0;
6ALTER TABLE prims ADD COLUMN ColorA integer not null default 0;
7
8COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/009_UserStore.sql b/OpenSim/Data/SQLiteNG/Resources/009_UserStore.sql
new file mode 100644
index 0000000..8ab03ef
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/009_UserStore.sql
@@ -0,0 +1,11 @@
1BEGIN;
2
3update users
4 set UUID = substr(UUID, 1, 8) || "-" || substr(UUID, 9, 4) || "-" || substr(UUID, 13, 4) || "-" || substr(UUID, 17, 4) || "-" || substr(UUID, 21, 12)
5 where UUID not like '%-%';
6
7update useragents
8 set UUID = substr(UUID, 1, 8) || "-" || substr(UUID, 9, 4) || "-" || substr(UUID, 13, 4) || "-" || substr(UUID, 17, 4) || "-" || substr(UUID, 21, 12)
9 where UUID not like '%-%';
10
11COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/010_RegionStore.sql b/OpenSim/Data/SQLiteNG/Resources/010_RegionStore.sql
new file mode 100644
index 0000000..b91ccf0
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/010_RegionStore.sql
@@ -0,0 +1,5 @@
1BEGIN;
2
3ALTER TABLE prims ADD COLUMN ClickAction INTEGER NOT NULL default 0;
4
5COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/010_UserStore.sql b/OpenSim/Data/SQLiteNG/Resources/010_UserStore.sql
new file mode 100644
index 0000000..5f956da
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/010_UserStore.sql
@@ -0,0 +1,37 @@
1BEGIN TRANSACTION;
2
3CREATE TABLE IF NOT EXISTS avatarappearance(
4 Owner varchar(36) NOT NULL primary key,
5 BodyItem varchar(36) DEFAULT NULL,
6 BodyAsset varchar(36) DEFAULT NULL,
7 SkinItem varchar(36) DEFAULT NULL,
8 SkinAsset varchar(36) DEFAULT NULL,
9 HairItem varchar(36) DEFAULT NULL,
10 HairAsset varchar(36) DEFAULT NULL,
11 EyesItem varchar(36) DEFAULT NULL,
12 EyesAsset varchar(36) DEFAULT NULL,
13 ShirtItem varchar(36) DEFAULT NULL,
14 ShirtAsset varchar(36) DEFAULT NULL,
15 PantsItem varchar(36) DEFAULT NULL,
16 PantsAsset varchar(36) DEFAULT NULL,
17 ShoesItem varchar(36) DEFAULT NULL,
18 ShoesAsset varchar(36) DEFAULT NULL,
19 SocksItem varchar(36) DEFAULT NULL,
20 SocksAsset varchar(36) DEFAULT NULL,
21 JacketItem varchar(36) DEFAULT NULL,
22 JacketAsset varchar(36) DEFAULT NULL,
23 GlovesItem varchar(36) DEFAULT NULL,
24 GlovesAsset varchar(36) DEFAULT NULL,
25 UnderShirtItem varchar(36) DEFAULT NULL,
26 UnderShirtAsset varchar(36) DEFAULT NULL,
27 UnderPantsItem varchar(36) DEFAULT NULL,
28 UnderPantsAsset varchar(36) DEFAULT NULL,
29 SkirtItem varchar(36) DEFAULT NULL,
30 SkirtAsset varchar(36) DEFAULT NULL,
31 Texture blob,
32 VisualParams blob,
33 Serial int DEFAULT NULL,
34 AvatarHeight float DEFAULT NULL
35);
36
37COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/011_RegionStore.sql b/OpenSim/Data/SQLiteNG/Resources/011_RegionStore.sql
new file mode 100644
index 0000000..42bef89
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/011_RegionStore.sql
@@ -0,0 +1,28 @@
1BEGIN;
2
3ALTER TABLE prims ADD COLUMN PayPrice INTEGER NOT NULL default 0;
4ALTER TABLE prims ADD COLUMN PayButton1 INTEGER NOT NULL default 0;
5ALTER TABLE prims ADD COLUMN PayButton2 INTEGER NOT NULL default 0;
6ALTER TABLE prims ADD COLUMN PayButton3 INTEGER NOT NULL default 0;
7ALTER TABLE prims ADD COLUMN PayButton4 INTEGER NOT NULL default 0;
8ALTER TABLE prims ADD COLUMN LoopedSound varchar(36) NOT NULL default '00000000-0000-0000-0000-000000000000';
9ALTER TABLE prims ADD COLUMN LoopedSoundGain float NOT NULL default 0;
10ALTER TABLE prims ADD COLUMN TextureAnimation string;
11ALTER TABLE prims ADD COLUMN ParticleSystem string;
12ALTER TABLE prims ADD COLUMN OmegaX float NOT NULL default 0;
13ALTER TABLE prims ADD COLUMN OmegaY float NOT NULL default 0;
14ALTER TABLE prims ADD COLUMN OmegaZ float NOT NULL default 0;
15ALTER TABLE prims ADD COLUMN CameraEyeOffsetX float NOT NULL default 0;
16ALTER TABLE prims ADD COLUMN CameraEyeOffsetY float NOT NULL default 0;
17ALTER TABLE prims ADD COLUMN CameraEyeOffsetZ float NOT NULL default 0;
18ALTER TABLE prims ADD COLUMN CameraAtOffsetX float NOT NULL default 0;
19ALTER TABLE prims ADD COLUMN CameraAtOffsetY float NOT NULL default 0;
20ALTER TABLE prims ADD COLUMN CameraAtOffsetZ float NOT NULL default 0;
21ALTER TABLE prims ADD COLUMN ForceMouselook string NOT NULL default 0;
22ALTER TABLE prims ADD COLUMN ScriptAccessPin INTEGER NOT NULL default 0;
23ALTER TABLE prims ADD COLUMN AllowedDrop INTEGER NOT NULL default 0;
24ALTER TABLE prims ADD COLUMN DieAtEdge string NOT NULL default 0;
25ALTER TABLE prims ADD COLUMN SalePrice INTEGER NOT NULL default 0;
26ALTER TABLE prims ADD COLUMN SaleType string NOT NULL default 0;
27
28COMMIT; \ No newline at end of file
diff --git a/OpenSim/Data/SQLiteNG/Resources/012_RegionStore.sql b/OpenSim/Data/SQLiteNG/Resources/012_RegionStore.sql
new file mode 100644
index 0000000..d952b78
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/012_RegionStore.sql
@@ -0,0 +1,5 @@
1BEGIN;
2
3ALTER TABLE prims ADD COLUMN Material INTEGER NOT NULL default 3;
4
5COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/013_RegionStore.sql b/OpenSim/Data/SQLiteNG/Resources/013_RegionStore.sql
new file mode 100644
index 0000000..11529cd
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/013_RegionStore.sql
@@ -0,0 +1,6 @@
1BEGIN;
2
3ALTER TABLE land ADD COLUMN OtherCleanTime INTEGER NOT NULL default 0;
4ALTER TABLE land ADD COLUMN Dwell INTEGER NOT NULL default 0;
5
6COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/014_RegionStore.sql b/OpenSim/Data/SQLiteNG/Resources/014_RegionStore.sql
new file mode 100644
index 0000000..c59b27e
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/014_RegionStore.sql
@@ -0,0 +1,8 @@
1begin;
2
3ALTER TABLE regionsettings ADD COLUMN sunvectorx double NOT NULL default 0;
4ALTER TABLE regionsettings ADD COLUMN sunvectory double NOT NULL default 0;
5ALTER TABLE regionsettings ADD COLUMN sunvectorz double NOT NULL default 0;
6
7commit;
8
diff --git a/OpenSim/Data/SQLiteNG/Resources/015_RegionStore.sql b/OpenSim/Data/SQLiteNG/Resources/015_RegionStore.sql
new file mode 100644
index 0000000..c43f356
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/015_RegionStore.sql
@@ -0,0 +1,6 @@
1BEGIN;
2
3ALTER TABLE prims ADD COLUMN CollisionSound varchar(36) NOT NULL default '00000000-0000-0000-0000-000000000000';
4ALTER TABLE prims ADD COLUMN CollisionSoundVolume float NOT NULL default 0;
5
6COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/016_RegionStore.sql b/OpenSim/Data/SQLiteNG/Resources/016_RegionStore.sql
new file mode 100644
index 0000000..52f160c
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/016_RegionStore.sql
@@ -0,0 +1,5 @@
1BEGIN;
2
3ALTER TABLE prims ADD COLUMN VolumeDetect INTEGER NOT NULL DEFAULT 0;
4
5COMMIT;
diff --git a/OpenSim/Data/SQLiteNG/Resources/017_RegionStore.sql b/OpenSim/Data/SQLiteNG/Resources/017_RegionStore.sql
new file mode 100644
index 0000000..6c6b7b5
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/017_RegionStore.sql
@@ -0,0 +1,8 @@
1BEGIN;
2CREATE TEMPORARY TABLE prims_backup(UUID,RegionUUID,CreationDate,Name,SceneGroupID,Text,Description,SitName,TouchName,CreatorID,OwnerID,GroupID,LastOwnerID,OwnerMask,NextOwnerMask,GroupMask,EveryoneMask,BaseMask,PositionX,PositionY,PositionZ,GroupPositionX,GroupPositionY,GroupPositionZ,VelocityX,VelocityY,VelocityZ,AngularVelocityX,AngularVelocityY,AngularVelocityZ,AccelerationX,AccelerationY,AccelerationZ,RotationX,RotationY,RotationZ,RotationW,ObjectFlags,SitTargetOffsetX,SitTargetOffsetY,SitTargetOffsetZ,SitTargetOrientW,SitTargetOrientX,SitTargetOrientY,SitTargetOrientZ,ColorR,ColorG,ColorB,ColorA,ClickAction,PayPrice,PayButton1,PayButton2,PayButton3,PayButton4,LoopedSound,LoopedSoundGain,TextureAnimation,ParticleSystem,OmegaX,OmegaY,OmegaZ,CameraEyeOffsetX,CameraEyeOffsetY,CameraEyeOffsetZ,CameraAtOffsetX,CameraAtOffsetY,CameraAtOffsetZ,ForceMouselook,ScriptAccessPin,AllowedDrop,DieAtEdge,SalePrice,SaleType,Material,CollisionSound,CollisionSoundVolume,VolumeDetect);
3INSERT INTO prims_backup SELECT UUID,RegionUUID,CreationDate,Name,SceneGroupID,Text,Description,SitName,TouchName,CreatorID,OwnerID,GroupID,LastOwnerID,OwnerMask,NextOwnerMask,GroupMask,EveryoneMask,BaseMask,PositionX,PositionY,PositionZ,GroupPositionX,GroupPositionY,GroupPositionZ,VelocityX,VelocityY,VelocityZ,AngularVelocityX,AngularVelocityY,AngularVelocityZ,AccelerationX,AccelerationY,AccelerationZ,RotationX,RotationY,RotationZ,RotationW,ObjectFlags,SitTargetOffsetX,SitTargetOffsetY,SitTargetOffsetZ,SitTargetOrientW,SitTargetOrientX,SitTargetOrientY,SitTargetOrientZ,ColorR,ColorG,ColorB,ColorA,ClickAction,PayPrice,PayButton1,PayButton2,PayButton3,PayButton4,LoopedSound,LoopedSoundGain,TextureAnimation,ParticleSystem,OmegaX,OmegaY,OmegaZ,CameraEyeOffsetX,CameraEyeOffsetY,CameraEyeOffsetZ,CameraAtOffsetX,CameraAtOffsetY,CameraAtOffsetZ,ForceMouselook,ScriptAccessPin,AllowedDrop,DieAtEdge,SalePrice,SaleType,Material,CollisionSound,CollisionSoundVolume,VolumeDetect FROM prims;
4DROP TABLE prims;
5CREATE TABLE prims(UUID,RegionUUID,CreationDate,Name,SceneGroupID,Text,Description,SitName,TouchName,CreatorID,OwnerID,GroupID,LastOwnerID,OwnerMask,NextOwnerMask,GroupMask,EveryoneMask,BaseMask,PositionX,PositionY,PositionZ,GroupPositionX,GroupPositionY,GroupPositionZ,VelocityX,VelocityY,VelocityZ,AngularVelocityX,AngularVelocityY,AngularVelocityZ,AccelerationX,AccelerationY,AccelerationZ,RotationX,RotationY,RotationZ,RotationW,ObjectFlags,SitTargetOffsetX,SitTargetOffsetY,SitTargetOffsetZ,SitTargetOrientW,SitTargetOrientX,SitTargetOrientY,SitTargetOrientZ,ColorR,ColorG,ColorB,ColorA,ClickAction,PayPrice,PayButton1,PayButton2,PayButton3,PayButton4,LoopedSound,LoopedSoundGain,TextureAnimation,ParticleSystem,OmegaX,OmegaY,OmegaZ,CameraEyeOffsetX,CameraEyeOffsetY,CameraEyeOffsetZ,CameraAtOffsetX,CameraAtOffsetY,CameraAtOffsetZ,ForceMouselook,ScriptAccessPin,AllowedDrop,DieAtEdge,SalePrice,SaleType,Material,CollisionSound,CollisionSoundVolume,VolumeDetect);
6INSERT INTO prims SELECT UUID,RegionUUID,CreationDate,Name,SceneGroupID,Text,Description,SitName,TouchName,CreatorID,OwnerID,GroupID,LastOwnerID,OwnerMask,NextOwnerMask,GroupMask,EveryoneMask,BaseMask,PositionX,PositionY,PositionZ,GroupPositionX,GroupPositionY,GroupPositionZ,VelocityX,VelocityY,VelocityZ,AngularVelocityX,AngularVelocityY,AngularVelocityZ,AccelerationX,AccelerationY,AccelerationZ,RotationX,RotationY,RotationZ,RotationW,ObjectFlags,SitTargetOffsetX,SitTargetOffsetY,SitTargetOffsetZ,SitTargetOrientW,SitTargetOrientX,SitTargetOrientY,SitTargetOrientZ,ColorR,ColorG,ColorB,ColorA,ClickAction,PayPrice,PayButton1,PayButton2,PayButton3,PayButton4,LoopedSound,LoopedSoundGain,TextureAnimation,ParticleSystem,OmegaX,OmegaY,OmegaZ,CameraEyeOffsetX,CameraEyeOffsetY,CameraEyeOffsetZ,CameraAtOffsetX,CameraAtOffsetY,CameraAtOffsetZ,ForceMouselook,ScriptAccessPin,AllowedDrop,DieAtEdge,SalePrice,SaleType,Material,CollisionSound,CollisionSoundVolume,VolumeDetect FROM prims_backup;
7DROP TABLE prims_backup;
8COMMIT; \ No newline at end of file
diff --git a/OpenSim/Data/SQLiteNG/Resources/018_RegionStore.sql b/OpenSim/Data/SQLiteNG/Resources/018_RegionStore.sql
new file mode 100644
index 0000000..6a390c2
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/018_RegionStore.sql
@@ -0,0 +1,79 @@
1BEGIN;
2
3update terrain
4 set RegionUUID = substr(RegionUUID, 1, 8) || "-" || substr(RegionUUID, 9, 4) || "-" || substr(RegionUUID, 13, 4) || "-" || substr(RegionUUID, 17, 4) || "-" || substr(RegionUUID, 21, 12)
5 where RegionUUID not like '%-%';
6
7
8update landaccesslist
9 set LandUUID = substr(LandUUID, 1, 8) || "-" || substr(LandUUID, 9, 4) || "-" || substr(LandUUID, 13, 4) || "-" || substr(LandUUID, 17, 4) || "-" || substr(LandUUID, 21, 12)
10 where LandUUID not like '%-%';
11
12update landaccesslist
13 set AccessUUID = substr(AccessUUID, 1, 8) || "-" || substr(AccessUUID, 9, 4) || "-" || substr(AccessUUID, 13, 4) || "-" || substr(AccessUUID, 17, 4) || "-" || substr(AccessUUID, 21, 12)
14 where AccessUUID not like '%-%';
15
16
17update prims
18 set UUID = substr(UUID, 1, 8) || "-" || substr(UUID, 9, 4) || "-" || substr(UUID, 13, 4) || "-" || substr(UUID, 17, 4) || "-" || substr(UUID, 21, 12)
19 where UUID not like '%-%';
20
21update prims
22 set RegionUUID = substr(RegionUUID, 1, 8) || "-" || substr(RegionUUID, 9, 4) || "-" || substr(RegionUUID, 13, 4) || "-" || substr(RegionUUID, 17, 4) || "-" || substr(RegionUUID, 21, 12)
23 where RegionUUID not like '%-%';
24
25update prims
26 set SceneGroupID = substr(SceneGroupID, 1, 8) || "-" || substr(SceneGroupID, 9, 4) || "-" || substr(SceneGroupID, 13, 4) || "-" || substr(SceneGroupID, 17, 4) || "-" || substr(SceneGroupID, 21, 12)
27 where SceneGroupID not like '%-%';
28
29update prims
30 set CreatorID = substr(CreatorID, 1, 8) || "-" || substr(CreatorID, 9, 4) || "-" || substr(CreatorID, 13, 4) || "-" || substr(CreatorID, 17, 4) || "-" || substr(CreatorID, 21, 12)
31 where CreatorID not like '%-%';
32
33update prims
34 set OwnerID = substr(OwnerID, 1, 8) || "-" || substr(OwnerID, 9, 4) || "-" || substr(OwnerID, 13, 4) || "-" || substr(OwnerID, 17, 4) || "-" || substr(OwnerID, 21, 12)
35 where OwnerID not like '%-%';
36
37update prims
38 set GroupID = substr(GroupID, 1, 8) || "-" || substr(GroupID, 9, 4) || "-" || substr(GroupID, 13, 4) || "-" || substr(GroupID, 17, 4) || "-" || substr(GroupID, 21, 12)
39 where GroupID not like '%-%';
40
41update prims
42 set LastOwnerID = substr(LastOwnerID, 1, 8) || "-" || substr(LastOwnerID, 9, 4) || "-" || substr(LastOwnerID, 13, 4) || "-" || substr(LastOwnerID, 17, 4) || "-" || substr(LastOwnerID, 21, 12)
43 where LastOwnerID not like '%-%';
44
45
46update primshapes
47 set UUID = substr(UUID, 1, 8) || "-" || substr(UUID, 9, 4) || "-" || substr(UUID, 13, 4) || "-" || substr(UUID, 17, 4) || "-" || substr(UUID, 21, 12)
48 where UUID not like '%-%';
49
50
51update land
52 set UUID = substr(UUID, 1, 8) || "-" || substr(UUID, 9, 4) || "-" || substr(UUID, 13, 4) || "-" || substr(UUID, 17, 4) || "-" || substr(UUID, 21, 12)
53 where UUID not like '%-%';
54
55update land
56 set RegionUUID = substr(RegionUUID, 1, 8) || "-" || substr(RegionUUID, 9, 4) || "-" || substr(RegionUUID, 13, 4) || "-" || substr(RegionUUID, 17, 4) || "-" || substr(RegionUUID, 21, 12)
57 where RegionUUID not like '%-%';
58
59update land
60 set OwnerUUID = substr(OwnerUUID, 1, 8) || "-" || substr(OwnerUUID, 9, 4) || "-" || substr(OwnerUUID, 13, 4) || "-" || substr(OwnerUUID, 17, 4) || "-" || substr(OwnerUUID, 21, 12)
61 where OwnerUUID not like '%-%';
62
63update land
64 set GroupUUID = substr(GroupUUID, 1, 8) || "-" || substr(GroupUUID, 9, 4) || "-" || substr(GroupUUID, 13, 4) || "-" || substr(GroupUUID, 17, 4) || "-" || substr(GroupUUID, 21, 12)
65 where GroupUUID not like '%-%';
66
67update land
68 set MediaTextureUUID = substr(MediaTextureUUID, 1, 8) || "-" || substr(MediaTextureUUID, 9, 4) || "-" || substr(MediaTextureUUID, 13, 4) || "-" || substr(MediaTextureUUID, 17, 4) || "-" || substr(MediaTextureUUID, 21, 12)
69 where MediaTextureUUID not like '%-%';
70
71update land
72 set SnapshotUUID = substr(SnapshotUUID, 1, 8) || "-" || substr(SnapshotUUID, 9, 4) || "-" || substr(SnapshotUUID, 13, 4) || "-" || substr(SnapshotUUID, 17, 4) || "-" || substr(SnapshotUUID, 21, 12)
73 where SnapshotUUID not like '%-%';
74
75update land
76 set AuthbuyerID = substr(AuthbuyerID, 1, 8) || "-" || substr(AuthbuyerID, 9, 4) || "-" || substr(AuthbuyerID, 13, 4) || "-" || substr(AuthbuyerID, 17, 4) || "-" || substr(AuthbuyerID, 21, 12)
77 where AuthbuyerID not like '%-%';
78
79COMMIT; \ No newline at end of file
diff --git a/OpenSim/Data/SQLiteNG/Resources/OpenSim.Data.SQLite.addin.xml b/OpenSim/Data/SQLiteNG/Resources/OpenSim.Data.SQLite.addin.xml
new file mode 100644
index 0000000..e6764fa
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Resources/OpenSim.Data.SQLite.addin.xml
@@ -0,0 +1,20 @@
1<Addin id="OpenSim.Data.SQLite" version="0.1">
2 <Runtime>
3 <Import assembly="OpenSim.Data.SQLite.dll"/>
4 </Runtime>
5 <Dependencies>
6 <Addin id="OpenSim.Data" version="0.5" />
7 </Dependencies>
8 <Extension path = "/OpenSim/GridData">
9 <Plugin id="SQLiteGridData" provider="OpenSim.Data.SQLite.dll" type="OpenSim.Data.SQLite.SQLiteGridData" />
10 </Extension>
11 <Extension path = "/OpenSim/AssetData">
12 <Plugin id="SQLiteAssetData" provider="OpenSim.Data.SQLite.dll" type="OpenSim.Data.SQLite.SQLiteAssetData" />
13 </Extension>
14 <Extension path = "/OpenSim/InventoryData">
15 <Plugin id="SQLiteInventoryData" provider="OpenSim.Data.SQLite.dll" type="OpenSim.Data.SQLite.SQLiteInventoryStore" />
16 </Extension>
17 <Extension path = "/OpenSim/UserData">
18 <Plugin id="SQLiteUserData" provider="OpenSim.Data.SQLite.dll" type="OpenSim.Data.SQLite.SQLiteUserData" />
19 </Extension>
20</Addin>
diff --git a/OpenSim/Data/SQLiteNG/SQLiteAssetData.cs b/OpenSim/Data/SQLiteNG/SQLiteAssetData.cs
new file mode 100644
index 0000000..a032670
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/SQLiteAssetData.cs
@@ -0,0 +1,343 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Data;
30using System.Reflection;
31using System.Collections.Generic;
32using log4net;
33using Mono.Data.SqliteClient;
34using OpenMetaverse;
35using OpenSim.Framework;
36
37namespace OpenSim.Data.SQLite
38{
39 /// <summary>
40 /// An asset storage interface for the SQLite database system
41 /// </summary>
42 public class SQLiteAssetData : AssetDataBase
43 {
44// private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
45
46 private const string SelectAssetSQL = "select * from assets where UUID=:UUID";
47 private const string SelectAssetMetadataSQL = "select Name, Description, Type, Temporary, UUID from assets limit :start, :count";
48 private const string DeleteAssetSQL = "delete from assets where UUID=:UUID";
49 private const string InsertAssetSQL = "insert into assets(UUID, Name, Description, Type, Local, Temporary, Data) values(:UUID, :Name, :Description, :Type, :Local, :Temporary, :Data)";
50 private const string UpdateAssetSQL = "update assets set Name=:Name, Description=:Description, Type=:Type, Local=:Local, Temporary=:Temporary, Data=:Data where UUID=:UUID";
51 private const string assetSelect = "select * from assets";
52
53 private SqliteConnection m_conn;
54
55 override public void Dispose()
56 {
57 if (m_conn != null)
58 {
59 m_conn.Close();
60 m_conn = null;
61 }
62 }
63
64 /// <summary>
65 /// <list type="bullet">
66 /// <item>Initialises AssetData interface</item>
67 /// <item>Loads and initialises a new SQLite connection and maintains it.</item>
68 /// <item>use default URI if connect string is empty.</item>
69 /// </list>
70 /// </summary>
71 /// <param name="dbconnect">connect string</param>
72 override public void Initialise(string dbconnect)
73 {
74 if (dbconnect == string.Empty)
75 {
76 dbconnect = "URI=file:Asset.db,version=3";
77 }
78 m_conn = new SqliteConnection(dbconnect);
79 m_conn.Open();
80
81 Assembly assem = GetType().Assembly;
82 Migration m = new Migration(m_conn, assem, "AssetStore");
83 m.Update();
84
85 return;
86 }
87
88 /// <summary>
89 /// Fetch Asset
90 /// </summary>
91 /// <param name="uuid">UUID of ... ?</param>
92 /// <returns>Asset base</returns>
93 override public AssetBase GetAsset(UUID uuid)
94 {
95 lock (this)
96 {
97 using (SqliteCommand cmd = new SqliteCommand(SelectAssetSQL, m_conn))
98 {
99 cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.ToString()));
100 using (IDataReader reader = cmd.ExecuteReader())
101 {
102 if (reader.Read())
103 {
104 AssetBase asset = buildAsset(reader);
105 reader.Close();
106 return asset;
107 }
108 else
109 {
110 reader.Close();
111 return null;
112 }
113 }
114 }
115 }
116 }
117
118 /// <summary>
119 /// Create an asset
120 /// </summary>
121 /// <param name="asset">Asset Base</param>
122 override public void StoreAsset(AssetBase asset)
123 {
124 //m_log.Info("[ASSET DB]: Creating Asset " + asset.FullID.ToString());
125 if (ExistsAsset(asset.FullID))
126 {
127 //LogAssetLoad(asset);
128
129 lock (this)
130 {
131 using (SqliteCommand cmd = new SqliteCommand(UpdateAssetSQL, m_conn))
132 {
133 cmd.Parameters.Add(new SqliteParameter(":UUID", asset.FullID.ToString()));
134 cmd.Parameters.Add(new SqliteParameter(":Name", asset.Name));
135 cmd.Parameters.Add(new SqliteParameter(":Description", asset.Description));
136 cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type));
137 cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local));
138 cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary));
139 cmd.Parameters.Add(new SqliteParameter(":Data", asset.Data));
140
141 cmd.ExecuteNonQuery();
142 }
143 }
144 }
145 else
146 {
147 lock (this)
148 {
149 using (SqliteCommand cmd = new SqliteCommand(InsertAssetSQL, m_conn))
150 {
151 cmd.Parameters.Add(new SqliteParameter(":UUID", asset.FullID.ToString()));
152 cmd.Parameters.Add(new SqliteParameter(":Name", asset.Name));
153 cmd.Parameters.Add(new SqliteParameter(":Description", asset.Description));
154 cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type));
155 cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local));
156 cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary));
157 cmd.Parameters.Add(new SqliteParameter(":Data", asset.Data));
158
159 cmd.ExecuteNonQuery();
160 }
161 }
162 }
163 }
164
165// /// <summary>
166// /// Some... logging functionnality
167// /// </summary>
168// /// <param name="asset"></param>
169// private static void LogAssetLoad(AssetBase asset)
170// {
171// string temporary = asset.Temporary ? "Temporary" : "Stored";
172// string local = asset.Local ? "Local" : "Remote";
173//
174// int assetLength = (asset.Data != null) ? asset.Data.Length : 0;
175//
176// m_log.Debug("[ASSET DB]: " +
177// string.Format("Loaded {5} {4} Asset: [{0}][{3}] \"{1}\":{2} ({6} bytes)",
178// asset.FullID, asset.Name, asset.Description, asset.Type,
179// temporary, local, assetLength));
180// }
181
182 /// <summary>
183 /// Check if an asset exist in database
184 /// </summary>
185 /// <param name="uuid">The asset UUID</param>
186 /// <returns>True if exist, or false.</returns>
187 override public bool ExistsAsset(UUID uuid)
188 {
189 lock (this) {
190 using (SqliteCommand cmd = new SqliteCommand(SelectAssetSQL, m_conn))
191 {
192 cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.ToString()));
193 using (IDataReader reader = cmd.ExecuteReader())
194 {
195 if (reader.Read())
196 {
197 reader.Close();
198 return true;
199 }
200 else
201 {
202 reader.Close();
203 return false;
204 }
205 }
206 }
207 }
208 }
209
210 /// <summary>
211 /// Delete an asset from database
212 /// </summary>
213 /// <param name="uuid"></param>
214 public void DeleteAsset(UUID uuid)
215 {
216 using (SqliteCommand cmd = new SqliteCommand(DeleteAssetSQL, m_conn))
217 {
218 cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.ToString()));
219
220 cmd.ExecuteNonQuery();
221 }
222 }
223
224 /// <summary>
225 ///
226 /// </summary>
227 /// <param name="row"></param>
228 /// <returns></returns>
229 private static AssetBase buildAsset(IDataReader row)
230 {
231 // TODO: this doesn't work yet because something more
232 // interesting has to be done to actually get these values
233 // back out. Not enough time to figure it out yet.
234 AssetBase asset = new AssetBase(
235 new UUID((String)row["UUID"]),
236 (String)row["Name"],
237 Convert.ToSByte(row["Type"]),
238 UUID.Zero.ToString()
239 );
240
241 asset.Description = (String) row["Description"];
242 asset.Local = Convert.ToBoolean(row["Local"]);
243 asset.Temporary = Convert.ToBoolean(row["Temporary"]);
244 asset.Data = (byte[]) row["Data"];
245 return asset;
246 }
247
248 private static AssetMetadata buildAssetMetadata(IDataReader row)
249 {
250 AssetMetadata metadata = new AssetMetadata();
251
252 metadata.FullID = new UUID((string) row["UUID"]);
253 metadata.Name = (string) row["Name"];
254 metadata.Description = (string) row["Description"];
255 metadata.Type = Convert.ToSByte(row["Type"]);
256 metadata.Temporary = Convert.ToBoolean(row["Temporary"]); // Not sure if this is correct.
257
258 // Current SHA1s are not stored/computed.
259 metadata.SHA1 = new byte[] {};
260
261 return metadata;
262 }
263
264 /// <summary>
265 /// Returns a list of AssetMetadata objects. The list is a subset of
266 /// the entire data set offset by <paramref name="start" /> containing
267 /// <paramref name="count" /> elements.
268 /// </summary>
269 /// <param name="start">The number of results to discard from the total data set.</param>
270 /// <param name="count">The number of rows the returned list should contain.</param>
271 /// <returns>A list of AssetMetadata objects.</returns>
272 public override List<AssetMetadata> FetchAssetMetadataSet(int start, int count)
273 {
274 List<AssetMetadata> retList = new List<AssetMetadata>(count);
275
276 lock (this)
277 {
278 using (SqliteCommand cmd = new SqliteCommand(SelectAssetMetadataSQL, m_conn))
279 {
280 cmd.Parameters.Add(new SqliteParameter(":start", start));
281 cmd.Parameters.Add(new SqliteParameter(":count", count));
282
283 using (IDataReader reader = cmd.ExecuteReader())
284 {
285 while (reader.Read())
286 {
287 AssetMetadata metadata = buildAssetMetadata(reader);
288 retList.Add(metadata);
289 }
290 }
291 }
292 }
293
294 return retList;
295 }
296
297 /***********************************************************************
298 *
299 * Database Binding functions
300 *
301 * These will be db specific due to typing, and minor differences
302 * in databases.
303 *
304 **********************************************************************/
305
306 #region IPlugin interface
307
308 /// <summary>
309 ///
310 /// </summary>
311 override public string Version
312 {
313 get
314 {
315 Module module = GetType().Module;
316 // string dllName = module.Assembly.ManifestModule.Name;
317 Version dllVersion = module.Assembly.GetName().Version;
318
319 return
320 string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build,
321 dllVersion.Revision);
322 }
323 }
324
325 /// <summary>
326 /// Initialise the AssetData interface using default URI
327 /// </summary>
328 override public void Initialise()
329 {
330 Initialise("URI=file:Asset.db,version=3");
331 }
332
333 /// <summary>
334 /// Name of this DB provider
335 /// </summary>
336 override public string Name
337 {
338 get { return "SQLite Asset storage engine"; }
339 }
340
341 #endregion
342 }
343} \ No newline at end of file
diff --git a/OpenSim/Data/SQLiteNG/SQLiteAuthenticationData.cs b/OpenSim/Data/SQLiteNG/SQLiteAuthenticationData.cs
new file mode 100644
index 0000000..aa10734
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/SQLiteAuthenticationData.cs
@@ -0,0 +1,262 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections;
30using System.Collections.Generic;
31using System.Data;
32using OpenMetaverse;
33using OpenSim.Framework;
34using Mono.Data.SqliteClient;
35
36namespace OpenSim.Data.SQLite
37{
38 public class SQLiteAuthenticationData : SQLiteFramework, IAuthenticationData
39 {
40 private string m_Realm;
41 private List<string> m_ColumnNames;
42 private int m_LastExpire;
43 private string m_connectionString;
44
45 protected static SqliteConnection m_Connection;
46 private static bool m_initialized = false;
47
48 public SQLiteAuthenticationData(string connectionString, string realm)
49 : base(connectionString)
50 {
51 m_Realm = realm;
52 m_connectionString = connectionString;
53
54 if (!m_initialized)
55 {
56 m_Connection = new SqliteConnection(connectionString);
57 m_Connection.Open();
58
59 using (SqliteConnection dbcon = (SqliteConnection)((ICloneable)m_Connection).Clone())
60 {
61 dbcon.Open();
62 Migration m = new Migration(dbcon, GetType().Assembly, "AuthStore");
63 m.Update();
64 dbcon.Close();
65 }
66
67 m_initialized = true;
68 }
69 }
70
71 public AuthenticationData Get(UUID principalID)
72 {
73 AuthenticationData ret = new AuthenticationData();
74 ret.Data = new Dictionary<string, object>();
75
76 SqliteCommand cmd = new SqliteCommand("select * from `" + m_Realm + "` where UUID = :PrincipalID");
77 cmd.Parameters.Add(new SqliteParameter(":PrincipalID", principalID.ToString()));
78
79 IDataReader result = ExecuteReader(cmd, m_Connection);
80
81 try
82 {
83 if (result.Read())
84 {
85 ret.PrincipalID = principalID;
86
87 if (m_ColumnNames == null)
88 {
89 m_ColumnNames = new List<string>();
90
91 DataTable schemaTable = result.GetSchemaTable();
92 foreach (DataRow row in schemaTable.Rows)
93 m_ColumnNames.Add(row["ColumnName"].ToString());
94 }
95
96 foreach (string s in m_ColumnNames)
97 {
98 if (s == "UUID")
99 continue;
100
101 ret.Data[s] = result[s].ToString();
102 }
103
104 return ret;
105 }
106 else
107 {
108 return null;
109 }
110 }
111 catch
112 {
113 }
114 finally
115 {
116 CloseCommand(cmd);
117 }
118
119 return null;
120 }
121
122 public bool Store(AuthenticationData data)
123 {
124 if (data.Data.ContainsKey("UUID"))
125 data.Data.Remove("UUID");
126
127 string[] fields = new List<string>(data.Data.Keys).ToArray();
128 string[] values = new string[data.Data.Count];
129 int i = 0;
130 foreach (object o in data.Data.Values)
131 values[i++] = o.ToString();
132
133 SqliteCommand cmd = new SqliteCommand();
134
135 if (Get(data.PrincipalID) != null)
136 {
137
138
139 string update = "update `" + m_Realm + "` set ";
140 bool first = true;
141 foreach (string field in fields)
142 {
143 if (!first)
144 update += ", ";
145 update += "`" + field + "` = :" + field;
146 cmd.Parameters.Add(new SqliteParameter(":" + field, data.Data[field]));
147
148 first = false;
149 }
150
151 update += " where UUID = :UUID";
152 cmd.Parameters.Add(new SqliteParameter(":UUID", data.PrincipalID.ToString()));
153
154 cmd.CommandText = update;
155 try
156 {
157 if (ExecuteNonQuery(cmd, m_Connection) < 1)
158 {
159 CloseCommand(cmd);
160 return false;
161 }
162 }
163 catch (Exception e)
164 {
165 Console.WriteLine(e.ToString());
166 CloseCommand(cmd);
167 return false;
168 }
169 }
170
171 else
172 {
173 string insert = "insert into `" + m_Realm + "` (`UUID`, `" +
174 String.Join("`, `", fields) +
175 "`) values (:UUID, :" + String.Join(", :", fields) + ")";
176
177 cmd.Parameters.Add(new SqliteParameter(":UUID", data.PrincipalID.ToString()));
178 foreach (string field in fields)
179 cmd.Parameters.Add(new SqliteParameter(":" + field, data.Data[field]));
180
181 cmd.CommandText = insert;
182
183 try
184 {
185 if (ExecuteNonQuery(cmd, m_Connection) < 1)
186 {
187 CloseCommand(cmd);
188 return false;
189 }
190 }
191 catch (Exception e)
192 {
193 Console.WriteLine(e.ToString());
194 CloseCommand(cmd);
195 return false;
196 }
197 }
198
199 CloseCommand(cmd);
200
201 return true;
202 }
203
204 public bool SetDataItem(UUID principalID, string item, string value)
205 {
206 SqliteCommand cmd = new SqliteCommand("update `" + m_Realm +
207 "` set `" + item + "` = " + value + " where UUID = '" + principalID.ToString() + "'");
208
209 if (ExecuteNonQuery(cmd, m_Connection) > 0)
210 return true;
211
212 return false;
213 }
214
215 public bool SetToken(UUID principalID, string token, int lifetime)
216 {
217 if (System.Environment.TickCount - m_LastExpire > 30000)
218 DoExpire();
219
220 SqliteCommand cmd = new SqliteCommand("insert into tokens (UUID, token, validity) values ('" + principalID.ToString() +
221 "', '" + token + "', datetime('now', 'localtime', '+" + lifetime.ToString() + " minutes'))");
222
223 if (ExecuteNonQuery(cmd, m_Connection) > 0)
224 {
225 cmd.Dispose();
226 return true;
227 }
228
229 cmd.Dispose();
230 return false;
231 }
232
233 public bool CheckToken(UUID principalID, string token, int lifetime)
234 {
235 if (System.Environment.TickCount - m_LastExpire > 30000)
236 DoExpire();
237
238 SqliteCommand cmd = new SqliteCommand("update tokens set validity = datetime('now', 'localtime', '+" + lifetime.ToString() +
239 " minutes') where UUID = '" + principalID.ToString() + "' and token = '" + token + "' and validity > datetime('now', 'localtime')");
240
241 if (ExecuteNonQuery(cmd, m_Connection) > 0)
242 {
243 cmd.Dispose();
244 return true;
245 }
246
247 cmd.Dispose();
248
249 return false;
250 }
251
252 private void DoExpire()
253 {
254 SqliteCommand cmd = new SqliteCommand("delete from tokens where validity < datetime('now', 'localtime')");
255 ExecuteNonQuery(cmd, m_Connection);
256
257 cmd.Dispose();
258
259 m_LastExpire = System.Environment.TickCount;
260 }
261 }
262}
diff --git a/OpenSim/Data/SQLiteNG/SQLiteAvatarData.cs b/OpenSim/Data/SQLiteNG/SQLiteAvatarData.cs
new file mode 100644
index 0000000..b3f4a4c
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/SQLiteAvatarData.cs
@@ -0,0 +1,74 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Reflection;
32using System.Threading;
33using log4net;
34using OpenMetaverse;
35using OpenSim.Framework;
36using Mono.Data.SqliteClient;
37
38namespace OpenSim.Data.SQLite
39{
40 /// <summary>
41 /// A SQLite Interface for Avatar Data
42 /// </summary>
43 public class SQLiteAvatarData : SQLiteGenericTableHandler<AvatarBaseData>,
44 IAvatarData
45 {
46 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
47
48 public SQLiteAvatarData(string connectionString, string realm) :
49 base(connectionString, realm, "Avatar")
50 {
51 }
52
53 public bool Delete(UUID principalID, string name)
54 {
55 SqliteCommand cmd = new SqliteCommand();
56
57 cmd.CommandText = String.Format("delete from {0} where `PrincipalID` = :PrincipalID and `Name` = :Name", m_Realm);
58 cmd.Parameters.Add(":PrincipalID", principalID.ToString());
59 cmd.Parameters.Add(":Name", name);
60
61 try
62 {
63 if (ExecuteNonQuery(cmd, m_Connection) > 0)
64 return true;
65
66 return false;
67 }
68 finally
69 {
70 CloseCommand(cmd);
71 }
72 }
73 }
74}
diff --git a/OpenSim/Data/SQLiteNG/SQLiteEstateData.cs b/OpenSim/Data/SQLiteNG/SQLiteEstateData.cs
new file mode 100644
index 0000000..bd6b776
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/SQLiteEstateData.cs
@@ -0,0 +1,387 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Reflection;
32using log4net;
33using Mono.Data.SqliteClient;
34using OpenMetaverse;
35using OpenSim.Framework;
36using OpenSim.Region.Framework.Interfaces;
37
38namespace OpenSim.Data.SQLite
39{
40 public class SQLiteEstateStore : IEstateDataStore
41 {
42 private static readonly ILog m_log =
43 LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
44
45 private SqliteConnection m_connection;
46 private string m_connectionString;
47
48 private FieldInfo[] m_Fields;
49 private Dictionary<string, FieldInfo> m_FieldMap =
50 new Dictionary<string, FieldInfo>();
51
52 public void Initialise(string connectionString)
53 {
54 m_connectionString = connectionString;
55
56 m_log.Info("[ESTATE DB]: Sqlite - connecting: "+m_connectionString);
57
58 m_connection = new SqliteConnection(m_connectionString);
59 m_connection.Open();
60
61 Assembly assem = GetType().Assembly;
62 Migration m = new Migration(m_connection, assem, "EstateStore");
63 m.Update();
64
65 m_connection.Close();
66 m_connection.Open();
67
68 Type t = typeof(EstateSettings);
69 m_Fields = t.GetFields(BindingFlags.NonPublic |
70 BindingFlags.Instance |
71 BindingFlags.DeclaredOnly);
72
73 foreach (FieldInfo f in m_Fields)
74 if (f.Name.Substring(0, 2) == "m_")
75 m_FieldMap[f.Name.Substring(2)] = f;
76 }
77
78 private string[] FieldList
79 {
80 get { return new List<string>(m_FieldMap.Keys).ToArray(); }
81 }
82
83 public EstateSettings LoadEstateSettings(UUID regionID, bool create)
84 {
85 string sql = "select estate_settings."+String.Join(",estate_settings.", FieldList)+" from estate_map left join estate_settings on estate_map.EstateID = estate_settings.EstateID where estate_settings.EstateID is not null and RegionID = :RegionID";
86
87 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
88
89 cmd.CommandText = sql;
90 cmd.Parameters.Add(":RegionID", regionID.ToString());
91
92 return DoLoad(cmd, regionID, create);
93 }
94
95 private EstateSettings DoLoad(SqliteCommand cmd, UUID regionID, bool create)
96 {
97 EstateSettings es = new EstateSettings();
98 es.OnSave += StoreEstateSettings;
99
100 IDataReader r = cmd.ExecuteReader();
101
102 if (r.Read())
103 {
104 foreach (string name in FieldList)
105 {
106 if (m_FieldMap[name].GetValue(es) is bool)
107 {
108 int v = Convert.ToInt32(r[name]);
109 if (v != 0)
110 m_FieldMap[name].SetValue(es, true);
111 else
112 m_FieldMap[name].SetValue(es, false);
113 }
114 else if (m_FieldMap[name].GetValue(es) is UUID)
115 {
116 UUID uuid = UUID.Zero;
117
118 UUID.TryParse(r[name].ToString(), out uuid);
119 m_FieldMap[name].SetValue(es, uuid);
120 }
121 else
122 {
123 m_FieldMap[name].SetValue(es, Convert.ChangeType(r[name], m_FieldMap[name].FieldType));
124 }
125 }
126 r.Close();
127 }
128 else if (create)
129 {
130 r.Close();
131
132 List<string> names = new List<string>(FieldList);
133
134 names.Remove("EstateID");
135
136 string sql = "insert into estate_settings ("+String.Join(",", names.ToArray())+") values ( :"+String.Join(", :", names.ToArray())+")";
137
138 cmd.CommandText = sql;
139 cmd.Parameters.Clear();
140
141 foreach (string name in FieldList)
142 {
143 if (m_FieldMap[name].GetValue(es) is bool)
144 {
145 if ((bool)m_FieldMap[name].GetValue(es))
146 cmd.Parameters.Add(":"+name, "1");
147 else
148 cmd.Parameters.Add(":"+name, "0");
149 }
150 else
151 {
152 cmd.Parameters.Add(":"+name, m_FieldMap[name].GetValue(es).ToString());
153 }
154 }
155
156 cmd.ExecuteNonQuery();
157
158 cmd.CommandText = "select LAST_INSERT_ROWID() as id";
159 cmd.Parameters.Clear();
160
161 r = cmd.ExecuteReader();
162
163 r.Read();
164
165 es.EstateID = Convert.ToUInt32(r["id"]);
166
167 r.Close();
168
169 cmd.CommandText = "insert into estate_map values (:RegionID, :EstateID)";
170 cmd.Parameters.Add(":RegionID", regionID.ToString());
171 cmd.Parameters.Add(":EstateID", es.EstateID.ToString());
172
173 // This will throw on dupe key
174 try
175 {
176 cmd.ExecuteNonQuery();
177 }
178 catch (Exception)
179 {
180 }
181
182 es.Save();
183 }
184
185 LoadBanList(es);
186
187 es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
188 es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
189 es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
190 return es;
191 }
192
193 public void StoreEstateSettings(EstateSettings es)
194 {
195 List<string> fields = new List<string>(FieldList);
196 fields.Remove("EstateID");
197
198 List<string> terms = new List<string>();
199
200 foreach (string f in fields)
201 terms.Add(f+" = :"+f);
202
203 string sql = "update estate_settings set "+String.Join(", ", terms.ToArray())+" where EstateID = :EstateID";
204
205 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
206
207 cmd.CommandText = sql;
208
209 foreach (string name in FieldList)
210 {
211 if (m_FieldMap[name].GetValue(es) is bool)
212 {
213 if ((bool)m_FieldMap[name].GetValue(es))
214 cmd.Parameters.Add(":"+name, "1");
215 else
216 cmd.Parameters.Add(":"+name, "0");
217 }
218 else
219 {
220 cmd.Parameters.Add(":"+name, m_FieldMap[name].GetValue(es).ToString());
221 }
222 }
223
224 cmd.ExecuteNonQuery();
225
226 SaveBanList(es);
227 SaveUUIDList(es.EstateID, "estate_managers", es.EstateManagers);
228 SaveUUIDList(es.EstateID, "estate_users", es.EstateAccess);
229 SaveUUIDList(es.EstateID, "estate_groups", es.EstateGroups);
230 }
231
232 private void LoadBanList(EstateSettings es)
233 {
234 es.ClearBans();
235
236 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
237
238 cmd.CommandText = "select bannedUUID from estateban where EstateID = :EstateID";
239 cmd.Parameters.Add(":EstateID", es.EstateID);
240
241 IDataReader r = cmd.ExecuteReader();
242
243 while (r.Read())
244 {
245 EstateBan eb = new EstateBan();
246
247 UUID uuid = new UUID();
248 UUID.TryParse(r["bannedUUID"].ToString(), out uuid);
249
250 eb.BannedUserID = uuid;
251 eb.BannedHostAddress = "0.0.0.0";
252 eb.BannedHostIPMask = "0.0.0.0";
253 es.AddBan(eb);
254 }
255 r.Close();
256 }
257
258 private void SaveBanList(EstateSettings es)
259 {
260 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
261
262 cmd.CommandText = "delete from estateban where EstateID = :EstateID";
263 cmd.Parameters.Add(":EstateID", es.EstateID.ToString());
264
265 cmd.ExecuteNonQuery();
266
267 cmd.Parameters.Clear();
268
269 cmd.CommandText = "insert into estateban (EstateID, bannedUUID, bannedIp, bannedIpHostMask, bannedNameMask) values ( :EstateID, :bannedUUID, '', '', '' )";
270
271 foreach (EstateBan b in es.EstateBans)
272 {
273 cmd.Parameters.Add(":EstateID", es.EstateID.ToString());
274 cmd.Parameters.Add(":bannedUUID", b.BannedUserID.ToString());
275
276 cmd.ExecuteNonQuery();
277 cmd.Parameters.Clear();
278 }
279 }
280
281 void SaveUUIDList(uint EstateID, string table, UUID[] data)
282 {
283 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
284
285 cmd.CommandText = "delete from "+table+" where EstateID = :EstateID";
286 cmd.Parameters.Add(":EstateID", EstateID.ToString());
287
288 cmd.ExecuteNonQuery();
289
290 cmd.Parameters.Clear();
291
292 cmd.CommandText = "insert into "+table+" (EstateID, uuid) values ( :EstateID, :uuid )";
293
294 foreach (UUID uuid in data)
295 {
296 cmd.Parameters.Add(":EstateID", EstateID.ToString());
297 cmd.Parameters.Add(":uuid", uuid.ToString());
298
299 cmd.ExecuteNonQuery();
300 cmd.Parameters.Clear();
301 }
302 }
303
304 UUID[] LoadUUIDList(uint EstateID, string table)
305 {
306 List<UUID> uuids = new List<UUID>();
307
308 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
309
310 cmd.CommandText = "select uuid from "+table+" where EstateID = :EstateID";
311 cmd.Parameters.Add(":EstateID", EstateID);
312
313 IDataReader r = cmd.ExecuteReader();
314
315 while (r.Read())
316 {
317 // EstateBan eb = new EstateBan();
318
319 UUID uuid = new UUID();
320 UUID.TryParse(r["uuid"].ToString(), out uuid);
321
322 uuids.Add(uuid);
323 }
324 r.Close();
325
326 return uuids.ToArray();
327 }
328
329 public EstateSettings LoadEstateSettings(int estateID)
330 {
331 string sql = "select estate_settings."+String.Join(",estate_settings.", FieldList)+" from estate_settings where estate_settings.EstateID :EstateID";
332
333 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
334
335 cmd.CommandText = sql;
336 cmd.Parameters.Add(":EstateID", estateID.ToString());
337
338 return DoLoad(cmd, UUID.Zero, false);
339 }
340
341 public List<int> GetEstates(string search)
342 {
343 List<int> result = new List<int>();
344
345 string sql = "select EstateID from estate_settings where estate_settings.EstateName :EstateName";
346
347 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
348
349 cmd.CommandText = sql;
350 cmd.Parameters.Add(":EstateName", search);
351
352 IDataReader r = cmd.ExecuteReader();
353
354 while (r.Read())
355 {
356 result.Add(Convert.ToInt32(r["EstateID"]));
357 }
358 r.Close();
359
360 return result;
361 }
362
363 public bool LinkRegion(UUID regionID, int estateID)
364 {
365 SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand();
366
367 cmd.CommandText = "insert into estate_map values (:RegionID, :EstateID)";
368 cmd.Parameters.Add(":RegionID", regionID.ToString());
369 cmd.Parameters.Add(":EstateID", estateID.ToString());
370
371 if (cmd.ExecuteNonQuery() == 0)
372 return false;
373
374 return true;
375 }
376
377 public List<UUID> GetRegions(int estateID)
378 {
379 return new List<UUID>();
380 }
381
382 public bool DeleteEstate(int estateID)
383 {
384 return false;
385 }
386 }
387}
diff --git a/OpenSim/Data/SQLiteNG/SQLiteFramework.cs b/OpenSim/Data/SQLiteNG/SQLiteFramework.cs
new file mode 100644
index 0000000..20b5085
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/SQLiteFramework.cs
@@ -0,0 +1,91 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections;
30using System.Collections.Generic;
31using System.Data;
32using OpenMetaverse;
33using OpenSim.Framework;
34using Mono.Data.SqliteClient;
35
36namespace OpenSim.Data.SQLite
37{
38 /// <summary>
39 /// A database interface class to a user profile storage system
40 /// </summary>
41 public class SQLiteFramework
42 {
43 protected Object m_lockObject = new Object();
44
45 protected SQLiteFramework(string connectionString)
46 {
47 }
48
49 //////////////////////////////////////////////////////////////
50 //
51 // All non queries are funneled through one connection
52 // to increase performance a little
53 //
54 protected int ExecuteNonQuery(SqliteCommand cmd, SqliteConnection connection)
55 {
56 lock (connection)
57 {
58 SqliteConnection newConnection =
59 (SqliteConnection)((ICloneable)connection).Clone();
60 newConnection.Open();
61
62 cmd.Connection = newConnection;
63 //Console.WriteLine("XXX " + cmd.CommandText);
64
65 return cmd.ExecuteNonQuery();
66 }
67 }
68
69 protected IDataReader ExecuteReader(SqliteCommand cmd, SqliteConnection connection)
70 {
71 lock (connection)
72 {
73 SqliteConnection newConnection =
74 (SqliteConnection)((ICloneable)connection).Clone();
75 newConnection.Open();
76
77 cmd.Connection = newConnection;
78 //Console.WriteLine("XXX " + cmd.CommandText);
79
80 return cmd.ExecuteReader();
81 }
82 }
83
84 protected void CloseCommand(SqliteCommand cmd)
85 {
86 cmd.Connection.Close();
87 cmd.Connection.Dispose();
88 cmd.Dispose();
89 }
90 }
91}
diff --git a/OpenSim/Data/SQLiteNG/SQLiteFriendsData.cs b/OpenSim/Data/SQLiteNG/SQLiteFriendsData.cs
new file mode 100644
index 0000000..0b12182
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/SQLiteFriendsData.cs
@@ -0,0 +1,70 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections;
30using System.Collections.Generic;
31using System.Data;
32using OpenMetaverse;
33using OpenSim.Framework;
34using Mono.Data.SqliteClient;
35
36namespace OpenSim.Data.SQLite
37{
38 public class SQLiteFriendsData : SQLiteGenericTableHandler<FriendsData>, IFriendsData
39 {
40 public SQLiteFriendsData(string connectionString, string realm)
41 : base(connectionString, realm, "FriendsStore")
42 {
43 }
44
45 public FriendsData[] GetFriends(UUID userID)
46 {
47 SqliteCommand cmd = new SqliteCommand();
48
49 cmd.CommandText = String.Format("select a.*,case when b.Flags is null then -1 else b.Flags end as TheirFlags from {0} as a left join {0} as b on a.PrincipalID = b.Friend and a.Friend = b.PrincipalID where a.PrincipalID = :PrincipalID", m_Realm);
50 cmd.Parameters.Add(":PrincipalID", userID.ToString());
51
52 return DoQuery(cmd);
53
54 }
55
56 public bool Delete(UUID principalID, string friend)
57 {
58 SqliteCommand cmd = new SqliteCommand();
59
60 cmd.CommandText = String.Format("delete from {0} where PrincipalID = :PrincipalID and Friend = :Friend", m_Realm);
61 cmd.Parameters.Add(":PrincipalID", principalID.ToString());
62 cmd.Parameters.Add(":Friend", friend);
63
64 ExecuteNonQuery(cmd, cmd.Connection);
65
66 return true;
67 }
68
69 }
70}
diff --git a/OpenSim/Data/SQLiteNG/SQLiteGenericTableHandler.cs b/OpenSim/Data/SQLiteNG/SQLiteGenericTableHandler.cs
new file mode 100644
index 0000000..b39bb19
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/SQLiteGenericTableHandler.cs
@@ -0,0 +1,268 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Reflection;
32using log4net;
33using Mono.Data.SqliteClient;
34using OpenMetaverse;
35using OpenSim.Framework;
36using OpenSim.Region.Framework.Interfaces;
37
38namespace OpenSim.Data.SQLite
39{
40 public class SQLiteGenericTableHandler<T> : SQLiteFramework where T: class, new()
41 {
42// private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
43
44 protected Dictionary<string, FieldInfo> m_Fields =
45 new Dictionary<string, FieldInfo>();
46
47 protected List<string> m_ColumnNames = null;
48 protected string m_Realm;
49 protected FieldInfo m_DataField = null;
50
51 protected static SqliteConnection m_Connection;
52 private static bool m_initialized;
53
54 public SQLiteGenericTableHandler(string connectionString,
55 string realm, string storeName) : base(connectionString)
56 {
57 m_Realm = realm;
58
59 if (!m_initialized)
60 {
61 m_Connection = new SqliteConnection(connectionString);
62 m_Connection.Open();
63
64 if (storeName != String.Empty)
65 {
66 Assembly assem = GetType().Assembly;
67 SqliteConnection newConnection =
68 (SqliteConnection)((ICloneable)m_Connection).Clone();
69 newConnection.Open();
70
71 Migration m = new Migration(newConnection, assem, storeName);
72 m.Update();
73 newConnection.Close();
74 newConnection.Dispose();
75 }
76
77 m_initialized = true;
78 }
79
80 Type t = typeof(T);
81 FieldInfo[] fields = t.GetFields(BindingFlags.Public |
82 BindingFlags.Instance |
83 BindingFlags.DeclaredOnly);
84
85 if (fields.Length == 0)
86 return;
87
88 foreach (FieldInfo f in fields)
89 {
90 if (f.Name != "Data")
91 m_Fields[f.Name] = f;
92 else
93 m_DataField = f;
94 }
95 }
96
97 private void CheckColumnNames(IDataReader reader)
98 {
99 if (m_ColumnNames != null)
100 return;
101
102 m_ColumnNames = new List<string>();
103
104 DataTable schemaTable = reader.GetSchemaTable();
105 foreach (DataRow row in schemaTable.Rows)
106 {
107 if (row["ColumnName"] != null &&
108 (!m_Fields.ContainsKey(row["ColumnName"].ToString())))
109 m_ColumnNames.Add(row["ColumnName"].ToString());
110 }
111 }
112
113 public T[] Get(string field, string key)
114 {
115 return Get(new string[] { field }, new string[] { key });
116 }
117
118 public T[] Get(string[] fields, string[] keys)
119 {
120 if (fields.Length != keys.Length)
121 return new T[0];
122
123 List<string> terms = new List<string>();
124
125 SqliteCommand cmd = new SqliteCommand();
126
127 for (int i = 0 ; i < fields.Length ; i++)
128 {
129 cmd.Parameters.Add(new SqliteParameter(":" + fields[i], keys[i]));
130 terms.Add("`" + fields[i] + "` = :" + fields[i]);
131 }
132
133 string where = String.Join(" and ", terms.ToArray());
134
135 string query = String.Format("select * from {0} where {1}",
136 m_Realm, where);
137
138 cmd.CommandText = query;
139
140 return DoQuery(cmd);
141 }
142
143 protected T[] DoQuery(SqliteCommand cmd)
144 {
145 IDataReader reader = ExecuteReader(cmd, m_Connection);
146 if (reader == null)
147 return new T[0];
148
149 CheckColumnNames(reader);
150
151 List<T> result = new List<T>();
152
153 while (reader.Read())
154 {
155 T row = new T();
156
157 foreach (string name in m_Fields.Keys)
158 {
159 if (m_Fields[name].GetValue(row) is bool)
160 {
161 int v = Convert.ToInt32(reader[name]);
162 m_Fields[name].SetValue(row, v != 0 ? true : false);
163 }
164 else if (m_Fields[name].GetValue(row) is UUID)
165 {
166 UUID uuid = UUID.Zero;
167
168 UUID.TryParse(reader[name].ToString(), out uuid);
169 m_Fields[name].SetValue(row, uuid);
170 }
171 else if (m_Fields[name].GetValue(row) is int)
172 {
173 int v = Convert.ToInt32(reader[name]);
174 m_Fields[name].SetValue(row, v);
175 }
176 else
177 {
178 m_Fields[name].SetValue(row, reader[name]);
179 }
180 }
181
182 if (m_DataField != null)
183 {
184 Dictionary<string, string> data =
185 new Dictionary<string, string>();
186
187 foreach (string col in m_ColumnNames)
188 {
189 data[col] = reader[col].ToString();
190 if (data[col] == null)
191 data[col] = String.Empty;
192 }
193
194 m_DataField.SetValue(row, data);
195 }
196
197 result.Add(row);
198 }
199
200 CloseCommand(cmd);
201
202 return result.ToArray();
203 }
204
205 public T[] Get(string where)
206 {
207 SqliteCommand cmd = new SqliteCommand();
208
209 string query = String.Format("select * from {0} where {1}",
210 m_Realm, where);
211
212 cmd.CommandText = query;
213
214 return DoQuery(cmd);
215 }
216
217 public bool Store(T row)
218 {
219 SqliteCommand cmd = new SqliteCommand();
220
221 string query = "";
222 List<String> names = new List<String>();
223 List<String> values = new List<String>();
224
225 foreach (FieldInfo fi in m_Fields.Values)
226 {
227 names.Add(fi.Name);
228 values.Add(":" + fi.Name);
229 cmd.Parameters.Add(new SqliteParameter(":" + fi.Name, fi.GetValue(row).ToString()));
230 }
231
232 if (m_DataField != null)
233 {
234 Dictionary<string, string> data =
235 (Dictionary<string, string>)m_DataField.GetValue(row);
236
237 foreach (KeyValuePair<string, string> kvp in data)
238 {
239 names.Add(kvp.Key);
240 values.Add(":" + kvp.Key);
241 cmd.Parameters.Add(new SqliteParameter(":" + kvp.Key, kvp.Value));
242 }
243 }
244
245 query = String.Format("replace into {0} (`", m_Realm) + String.Join("`,`", names.ToArray()) + "`) values (" + String.Join(",", values.ToArray()) + ")";
246
247 cmd.CommandText = query;
248
249 if (ExecuteNonQuery(cmd, m_Connection) > 0)
250 return true;
251
252 return false;
253 }
254
255 public bool Delete(string field, string val)
256 {
257 SqliteCommand cmd = new SqliteCommand();
258
259 cmd.CommandText = String.Format("delete from {0} where `{1}` = :{1}", m_Realm, field);
260 cmd.Parameters.Add(new SqliteParameter(field, val));
261
262 if (ExecuteNonQuery(cmd, m_Connection) > 0)
263 return true;
264
265 return false;
266 }
267 }
268}
diff --git a/OpenSim/Data/SQLiteNG/SQLiteInventoryStore.cs b/OpenSim/Data/SQLiteNG/SQLiteInventoryStore.cs
new file mode 100644
index 0000000..a5e0517
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/SQLiteInventoryStore.cs
@@ -0,0 +1,898 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Reflection;
32using log4net;
33using Mono.Data.SqliteClient;
34using OpenMetaverse;
35using OpenSim.Framework;
36
37namespace OpenSim.Data.SQLite
38{
39 /// <summary>
40 /// An Inventory Interface to the SQLite database
41 /// </summary>
42 public class SQLiteInventoryStore : SQLiteUtil, IInventoryDataPlugin
43 {
44 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
45
46 private const string invItemsSelect = "select * from inventoryitems";
47 private const string invFoldersSelect = "select * from inventoryfolders";
48
49 private static SqliteConnection conn;
50 private static DataSet ds;
51 private static SqliteDataAdapter invItemsDa;
52 private static SqliteDataAdapter invFoldersDa;
53
54 private static bool m_Initialized = false;
55
56 public void Initialise()
57 {
58 m_log.Info("[SQLiteInventoryData]: " + Name + " cannot be default-initialized!");
59 throw new PluginNotInitialisedException(Name);
60 }
61
62 /// <summary>
63 /// <list type="bullet">
64 /// <item>Initialises Inventory interface</item>
65 /// <item>Loads and initialises a new SQLite connection and maintains it.</item>
66 /// <item>use default URI if connect string string is empty.</item>
67 /// </list>
68 /// </summary>
69 /// <param name="dbconnect">connect string</param>
70 public void Initialise(string dbconnect)
71 {
72 if (!m_Initialized)
73 {
74 m_Initialized = true;
75
76 if (dbconnect == string.Empty)
77 {
78 dbconnect = "URI=file:inventoryStore.db,version=3";
79 }
80 m_log.Info("[INVENTORY DB]: Sqlite - connecting: " + dbconnect);
81 conn = new SqliteConnection(dbconnect);
82
83 conn.Open();
84
85 Assembly assem = GetType().Assembly;
86 Migration m = new Migration(conn, assem, "InventoryStore");
87 m.Update();
88
89 SqliteCommand itemsSelectCmd = new SqliteCommand(invItemsSelect, conn);
90 invItemsDa = new SqliteDataAdapter(itemsSelectCmd);
91 // SqliteCommandBuilder primCb = new SqliteCommandBuilder(primDa);
92
93 SqliteCommand foldersSelectCmd = new SqliteCommand(invFoldersSelect, conn);
94 invFoldersDa = new SqliteDataAdapter(foldersSelectCmd);
95
96 ds = new DataSet();
97
98 ds.Tables.Add(createInventoryFoldersTable());
99 invFoldersDa.Fill(ds.Tables["inventoryfolders"]);
100 setupFoldersCommands(invFoldersDa, conn);
101 m_log.Info("[INVENTORY DB]: Populated Inventory Folders Definitions");
102
103 ds.Tables.Add(createInventoryItemsTable());
104 invItemsDa.Fill(ds.Tables["inventoryitems"]);
105 setupItemsCommands(invItemsDa, conn);
106 m_log.Info("[INVENTORY DB]: Populated Inventory Items Definitions");
107
108 ds.AcceptChanges();
109 }
110 }
111
112 /// <summary>
113 /// Closes the inventory interface
114 /// </summary>
115 public void Dispose()
116 {
117 if (conn != null)
118 {
119 conn.Close();
120 conn = null;
121 }
122 if (invItemsDa != null)
123 {
124 invItemsDa.Dispose();
125 invItemsDa = null;
126 }
127 if (invFoldersDa != null)
128 {
129 invFoldersDa.Dispose();
130 invFoldersDa = null;
131 }
132 if (ds != null)
133 {
134 ds.Dispose();
135 ds = null;
136 }
137 }
138
139 /// <summary>
140 ///
141 /// </summary>
142 /// <param name="row"></param>
143 /// <returns></returns>
144 public InventoryItemBase buildItem(DataRow row)
145 {
146 InventoryItemBase item = new InventoryItemBase();
147 item.ID = new UUID((string) row["UUID"]);
148 item.AssetID = new UUID((string) row["assetID"]);
149 item.AssetType = Convert.ToInt32(row["assetType"]);
150 item.InvType = Convert.ToInt32(row["invType"]);
151 item.Folder = new UUID((string) row["parentFolderID"]);
152 item.Owner = new UUID((string) row["avatarID"]);
153 item.CreatorId = (string)row["creatorsID"];
154 item.Name = (string) row["inventoryName"];
155 item.Description = (string) row["inventoryDescription"];
156
157 item.NextPermissions = Convert.ToUInt32(row["inventoryNextPermissions"]);
158 item.CurrentPermissions = Convert.ToUInt32(row["inventoryCurrentPermissions"]);
159 item.BasePermissions = Convert.ToUInt32(row["inventoryBasePermissions"]);
160 item.EveryOnePermissions = Convert.ToUInt32(row["inventoryEveryOnePermissions"]);
161 item.GroupPermissions = Convert.ToUInt32(row["inventoryGroupPermissions"]);
162
163 // new fields
164 if (!Convert.IsDBNull(row["salePrice"]))
165 item.SalePrice = Convert.ToInt32(row["salePrice"]);
166
167 if (!Convert.IsDBNull(row["saleType"]))
168 item.SaleType = Convert.ToByte(row["saleType"]);
169
170 if (!Convert.IsDBNull(row["creationDate"]))
171 item.CreationDate = Convert.ToInt32(row["creationDate"]);
172
173 if (!Convert.IsDBNull(row["groupID"]))
174 item.GroupID = new UUID((string)row["groupID"]);
175
176 if (!Convert.IsDBNull(row["groupOwned"]))
177 item.GroupOwned = Convert.ToBoolean(row["groupOwned"]);
178
179 if (!Convert.IsDBNull(row["Flags"]))
180 item.Flags = Convert.ToUInt32(row["Flags"]);
181
182 return item;
183 }
184
185 /// <summary>
186 /// Fill a database row with item data
187 /// </summary>
188 /// <param name="row"></param>
189 /// <param name="item"></param>
190 private static void fillItemRow(DataRow row, InventoryItemBase item)
191 {
192 row["UUID"] = item.ID.ToString();
193 row["assetID"] = item.AssetID.ToString();
194 row["assetType"] = item.AssetType;
195 row["invType"] = item.InvType;
196 row["parentFolderID"] = item.Folder.ToString();
197 row["avatarID"] = item.Owner.ToString();
198 row["creatorsID"] = item.CreatorId.ToString();
199 row["inventoryName"] = item.Name;
200 row["inventoryDescription"] = item.Description;
201
202 row["inventoryNextPermissions"] = item.NextPermissions;
203 row["inventoryCurrentPermissions"] = item.CurrentPermissions;
204 row["inventoryBasePermissions"] = item.BasePermissions;
205 row["inventoryEveryOnePermissions"] = item.EveryOnePermissions;
206 row["inventoryGroupPermissions"] = item.GroupPermissions;
207
208 // new fields
209 row["salePrice"] = item.SalePrice;
210 row["saleType"] = item.SaleType;
211 row["creationDate"] = item.CreationDate;
212 row["groupID"] = item.GroupID.ToString();
213 row["groupOwned"] = item.GroupOwned;
214 row["flags"] = item.Flags;
215 }
216
217 /// <summary>
218 /// Add inventory folder
219 /// </summary>
220 /// <param name="folder">Folder base</param>
221 /// <param name="add">true=create folder. false=update existing folder</param>
222 /// <remarks>nasty</remarks>
223 private void addFolder(InventoryFolderBase folder, bool add)
224 {
225 lock (ds)
226 {
227 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
228
229 DataRow inventoryRow = inventoryFolderTable.Rows.Find(folder.ID.ToString());
230 if (inventoryRow == null)
231 {
232 if (! add)
233 m_log.ErrorFormat("Interface Misuse: Attempting to Update non-existant inventory folder: {0}", folder.ID);
234
235 inventoryRow = inventoryFolderTable.NewRow();
236 fillFolderRow(inventoryRow, folder);
237 inventoryFolderTable.Rows.Add(inventoryRow);
238 }
239 else
240 {
241 if (add)
242 m_log.ErrorFormat("Interface Misuse: Attempting to Add inventory folder that already exists: {0}", folder.ID);
243
244 fillFolderRow(inventoryRow, folder);
245 }
246
247 invFoldersDa.Update(ds, "inventoryfolders");
248 }
249 }
250
251 /// <summary>
252 /// Move an inventory folder
253 /// </summary>
254 /// <param name="folder">folder base</param>
255 private void moveFolder(InventoryFolderBase folder)
256 {
257 lock (ds)
258 {
259 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
260
261 DataRow inventoryRow = inventoryFolderTable.Rows.Find(folder.ID.ToString());
262 if (inventoryRow == null)
263 {
264 inventoryRow = inventoryFolderTable.NewRow();
265 fillFolderRow(inventoryRow, folder);
266 inventoryFolderTable.Rows.Add(inventoryRow);
267 }
268 else
269 {
270 moveFolderRow(inventoryRow, folder);
271 }
272
273 invFoldersDa.Update(ds, "inventoryfolders");
274 }
275 }
276
277 /// <summary>
278 /// add an item in inventory
279 /// </summary>
280 /// <param name="item">the item</param>
281 /// <param name="add">true=add item ; false=update existing item</param>
282 private void addItem(InventoryItemBase item, bool add)
283 {
284 lock (ds)
285 {
286 DataTable inventoryItemTable = ds.Tables["inventoryitems"];
287
288 DataRow inventoryRow = inventoryItemTable.Rows.Find(item.ID.ToString());
289 if (inventoryRow == null)
290 {
291 if (!add)
292 m_log.ErrorFormat("[INVENTORY DB]: Interface Misuse: Attempting to Update non-existant inventory item: {0}", item.ID);
293
294 inventoryRow = inventoryItemTable.NewRow();
295 fillItemRow(inventoryRow, item);
296 inventoryItemTable.Rows.Add(inventoryRow);
297 }
298 else
299 {
300 if (add)
301 m_log.ErrorFormat("[INVENTORY DB]: Interface Misuse: Attempting to Add inventory item that already exists: {0}", item.ID);
302
303 fillItemRow(inventoryRow, item);
304 }
305
306 invItemsDa.Update(ds, "inventoryitems");
307
308 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
309
310 inventoryRow = inventoryFolderTable.Rows.Find(item.Folder.ToString());
311 if (inventoryRow != null) //MySQL doesn't throw an exception here, so sqlite shouldn't either.
312 inventoryRow["version"] = (int)inventoryRow["version"] + 1;
313
314 invFoldersDa.Update(ds, "inventoryfolders");
315 }
316 }
317
318 /// <summary>
319 /// TODO : DataSet commit
320 /// </summary>
321 public void Shutdown()
322 {
323 // TODO: DataSet commit
324 }
325
326 /// <summary>
327 /// The name of this DB provider
328 /// </summary>
329 /// <returns>Name of DB provider</returns>
330 public string Name
331 {
332 get { return "SQLite Inventory Data Interface"; }
333 }
334
335 /// <summary>
336 /// Returns the version of this DB provider
337 /// </summary>
338 /// <returns>A string containing the DB provider version</returns>
339 public string Version
340 {
341 get
342 {
343 Module module = GetType().Module;
344 // string dllName = module.Assembly.ManifestModule.Name;
345 Version dllVersion = module.Assembly.GetName().Version;
346
347
348 return
349 string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build,
350 dllVersion.Revision);
351 }
352 }
353
354 /// <summary>
355 /// Returns a list of inventory items contained within the specified folder
356 /// </summary>
357 /// <param name="folderID">The UUID of the target folder</param>
358 /// <returns>A List of InventoryItemBase items</returns>
359 public List<InventoryItemBase> getInventoryInFolder(UUID folderID)
360 {
361 lock (ds)
362 {
363 List<InventoryItemBase> retval = new List<InventoryItemBase>();
364 DataTable inventoryItemTable = ds.Tables["inventoryitems"];
365 string selectExp = "parentFolderID = '" + folderID + "'";
366 DataRow[] rows = inventoryItemTable.Select(selectExp);
367 foreach (DataRow row in rows)
368 {
369 retval.Add(buildItem(row));
370 }
371
372 return retval;
373 }
374 }
375
376 /// <summary>
377 /// Returns a list of the root folders within a users inventory
378 /// </summary>
379 /// <param name="user">The user whos inventory is to be searched</param>
380 /// <returns>A list of folder objects</returns>
381 public List<InventoryFolderBase> getUserRootFolders(UUID user)
382 {
383 return new List<InventoryFolderBase>();
384 }
385
386 // see InventoryItemBase.getUserRootFolder
387 public InventoryFolderBase getUserRootFolder(UUID user)
388 {
389 lock (ds)
390 {
391 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
392 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
393 string selectExp = "agentID = '" + user + "' AND parentID = '" + UUID.Zero + "'";
394 DataRow[] rows = inventoryFolderTable.Select(selectExp);
395 foreach (DataRow row in rows)
396 {
397 folders.Add(buildFolder(row));
398 }
399
400 // There should only ever be one root folder for a user. However, if there's more
401 // than one we'll simply use the first one rather than failing. It would be even
402 // nicer to print some message to this effect, but this feels like it's too low a
403 // to put such a message out, and it's too minor right now to spare the time to
404 // suitably refactor.
405 if (folders.Count > 0)
406 {
407 return folders[0];
408 }
409
410 return null;
411 }
412 }
413
414 /// <summary>
415 /// Append a list of all the child folders of a parent folder
416 /// </summary>
417 /// <param name="folders">list where folders will be appended</param>
418 /// <param name="parentID">ID of parent</param>
419 protected void getInventoryFolders(ref List<InventoryFolderBase> folders, UUID parentID)
420 {
421 lock (ds)
422 {
423 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
424 string selectExp = "parentID = '" + parentID + "'";
425 DataRow[] rows = inventoryFolderTable.Select(selectExp);
426 foreach (DataRow row in rows)
427 {
428 folders.Add(buildFolder(row));
429 }
430
431 }
432 }
433
434 /// <summary>
435 /// Returns a list of inventory folders contained in the folder 'parentID'
436 /// </summary>
437 /// <param name="parentID">The folder to get subfolders for</param>
438 /// <returns>A list of inventory folders</returns>
439 public List<InventoryFolderBase> getInventoryFolders(UUID parentID)
440 {
441 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
442 getInventoryFolders(ref folders, parentID);
443 return folders;
444 }
445
446 /// <summary>
447 /// See IInventoryDataPlugin
448 /// </summary>
449 /// <param name="parentID"></param>
450 /// <returns></returns>
451 public List<InventoryFolderBase> getFolderHierarchy(UUID parentID)
452 {
453 /* Note: There are subtle changes between this implementation of getFolderHierarchy and the previous one
454 * - We will only need to hit the database twice instead of n times.
455 * - We assume the database is well-formed - no stranded/dangling folders, all folders in heirarchy owned
456 * by the same person, each user only has 1 inventory heirarchy
457 * - The returned list is not ordered, instead of breadth-first ordered
458 There are basically 2 usage cases for getFolderHeirarchy:
459 1) Getting the user's entire inventory heirarchy when they log in
460 2) Finding a subfolder heirarchy to delete when emptying the trash.
461 This implementation will pull all inventory folders from the database, and then prune away any folder that
462 is not part of the requested sub-heirarchy. The theory is that it is cheaper to make 1 request from the
463 database than to make n requests. This pays off only if requested heirarchy is large.
464 By making this choice, we are making the worst case better at the cost of making the best case worse
465 - Francis
466 */
467
468 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
469 DataRow[] folderRows = null, parentRow;
470 InventoryFolderBase parentFolder = null;
471 lock (ds)
472 {
473 /* Fetch the parent folder from the database to determine the agent ID.
474 * Then fetch all inventory folders for that agent from the agent ID.
475 */
476 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
477 string selectExp = "UUID = '" + parentID + "'";
478 parentRow = inventoryFolderTable.Select(selectExp); // Assume at most 1 result
479 if (parentRow.GetLength(0) >= 1) // No result means parent folder does not exist
480 {
481 parentFolder = buildFolder(parentRow[0]);
482 UUID agentID = parentFolder.Owner;
483 selectExp = "agentID = '" + agentID + "'";
484 folderRows = inventoryFolderTable.Select(selectExp);
485 }
486
487 if (folderRows != null && folderRows.GetLength(0) >= 1) // No result means parent folder does not exist
488 { // or has no children
489 /* if we're querying the root folder, just return an unordered list of all folders in the user's
490 * inventory
491 */
492 if (parentFolder.ParentID == UUID.Zero)
493 {
494 foreach (DataRow row in folderRows)
495 {
496 InventoryFolderBase curFolder = buildFolder(row);
497 if (curFolder.ID != parentID) // Return all folders except the parent folder of heirarchy
498 folders.Add(buildFolder(row));
499 }
500 } // If requesting root folder
501 /* else we are querying a non-root folder. We currently have a list of all of the user's folders,
502 * we must construct a list of all folders in the heirarchy below parentID.
503 * Our first step will be to construct a hash table of all folders, indexed by parent ID.
504 * Once we have constructed the hash table, we will do a breadth-first traversal on the tree using the
505 * hash table to find child folders.
506 */
507 else
508 { // Querying a non-root folder
509
510 // Build a hash table of all user's inventory folders, indexed by each folder's parent ID
511 Dictionary<UUID, List<InventoryFolderBase>> hashtable =
512 new Dictionary<UUID, List<InventoryFolderBase>>(folderRows.GetLength(0));
513
514 foreach (DataRow row in folderRows)
515 {
516 InventoryFolderBase curFolder = buildFolder(row);
517 if (curFolder.ParentID != UUID.Zero) // Discard root of tree - not needed
518 {
519 if (hashtable.ContainsKey(curFolder.ParentID))
520 {
521 // Current folder already has a sibling - append to sibling list
522 hashtable[curFolder.ParentID].Add(curFolder);
523 }
524 else
525 {
526 List<InventoryFolderBase> siblingList = new List<InventoryFolderBase>();
527 siblingList.Add(curFolder);
528 // Current folder has no known (yet) siblings
529 hashtable.Add(curFolder.ParentID, siblingList);
530 }
531 }
532 } // For all inventory folders
533
534 // Note: Could release the ds lock here - we don't access folderRows or the database anymore.
535 // This is somewhat of a moot point as the callers of this function usually lock db anyways.
536
537 if (hashtable.ContainsKey(parentID)) // if requested folder does have children
538 folders.AddRange(hashtable[parentID]);
539
540 // BreadthFirstSearch build inventory tree **Note: folders.Count is *not* static
541 for (int i = 0; i < folders.Count; i++)
542 if (hashtable.ContainsKey(folders[i].ID))
543 folders.AddRange(hashtable[folders[i].ID]);
544
545 } // if requesting a subfolder heirarchy
546 } // if folder parentID exists and has children
547 } // lock ds
548 return folders;
549 }
550
551 /// <summary>
552 /// Returns an inventory item by its UUID
553 /// </summary>
554 /// <param name="item">The UUID of the item to be returned</param>
555 /// <returns>A class containing item information</returns>
556 public InventoryItemBase getInventoryItem(UUID item)
557 {
558 lock (ds)
559 {
560 DataRow row = ds.Tables["inventoryitems"].Rows.Find(item.ToString());
561 if (row != null)
562 {
563 return buildItem(row);
564 }
565 else
566 {
567 return null;
568 }
569 }
570 }
571
572 /// <summary>
573 /// Returns a specified inventory folder by its UUID
574 /// </summary>
575 /// <param name="folder">The UUID of the folder to be returned</param>
576 /// <returns>A class containing folder information</returns>
577 public InventoryFolderBase getInventoryFolder(UUID folder)
578 {
579 // TODO: Deep voodoo here. If you enable this code then
580 // multi region breaks. No idea why, but I figured it was
581 // better to leave multi region at this point. It does mean
582 // that you don't get to see system textures why creating
583 // clothes and the like. :(
584 lock (ds)
585 {
586 DataRow row = ds.Tables["inventoryfolders"].Rows.Find(folder.ToString());
587 if (row != null)
588 {
589 return buildFolder(row);
590 }
591 else
592 {
593 return null;
594 }
595 }
596 }
597
598 /// <summary>
599 /// Creates a new inventory item based on item
600 /// </summary>
601 /// <param name="item">The item to be created</param>
602 public void addInventoryItem(InventoryItemBase item)
603 {
604 addItem(item, true);
605 }
606
607 /// <summary>
608 /// Updates an inventory item with item (updates based on ID)
609 /// </summary>
610 /// <param name="item">The updated item</param>
611 public void updateInventoryItem(InventoryItemBase item)
612 {
613 addItem(item, false);
614 }
615
616 /// <summary>
617 /// Delete an inventory item
618 /// </summary>
619 /// <param name="item">The item UUID</param>
620 public void deleteInventoryItem(UUID itemID)
621 {
622 lock (ds)
623 {
624 DataTable inventoryItemTable = ds.Tables["inventoryitems"];
625
626 DataRow inventoryRow = inventoryItemTable.Rows.Find(itemID.ToString());
627 if (inventoryRow != null)
628 {
629 inventoryRow.Delete();
630 }
631
632 invItemsDa.Update(ds, "inventoryitems");
633 }
634 }
635
636 public InventoryItemBase queryInventoryItem(UUID itemID)
637 {
638 return getInventoryItem(itemID);
639 }
640
641 public InventoryFolderBase queryInventoryFolder(UUID folderID)
642 {
643 return getInventoryFolder(folderID);
644 }
645
646 /// <summary>
647 /// Delete all items in the specified folder
648 /// </summary>
649 /// <param name="folderId">id of the folder, whose item content should be deleted</param>
650 /// <todo>this is horribly inefficient, but I don't want to ruin the overall structure of this implementation</todo>
651 private void deleteItemsInFolder(UUID folderId)
652 {
653 List<InventoryItemBase> items = getInventoryInFolder(folderId);
654
655 foreach (InventoryItemBase i in items)
656 deleteInventoryItem(i.ID);
657 }
658
659 /// <summary>
660 /// Adds a new folder specified by folder
661 /// </summary>
662 /// <param name="folder">The inventory folder</param>
663 public void addInventoryFolder(InventoryFolderBase folder)
664 {
665 addFolder(folder, true);
666 }
667
668 /// <summary>
669 /// Updates a folder based on its ID with folder
670 /// </summary>
671 /// <param name="folder">The inventory folder</param>
672 public void updateInventoryFolder(InventoryFolderBase folder)
673 {
674 addFolder(folder, false);
675 }
676
677 /// <summary>
678 /// Moves a folder based on its ID with folder
679 /// </summary>
680 /// <param name="folder">The inventory folder</param>
681 public void moveInventoryFolder(InventoryFolderBase folder)
682 {
683 moveFolder(folder);
684 }
685
686 /// <summary>
687 /// Delete a folder
688 /// </summary>
689 /// <remarks>
690 /// This will clean-up any child folders and child items as well
691 /// </remarks>
692 /// <param name="folderID">the folder UUID</param>
693 public void deleteInventoryFolder(UUID folderID)
694 {
695 lock (ds)
696 {
697 List<InventoryFolderBase> subFolders = getFolderHierarchy(folderID);
698
699 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
700 DataRow inventoryRow;
701
702 //Delete all sub-folders
703 foreach (InventoryFolderBase f in subFolders)
704 {
705 inventoryRow = inventoryFolderTable.Rows.Find(f.ID.ToString());
706 if (inventoryRow != null)
707 {
708 deleteItemsInFolder(f.ID);
709 inventoryRow.Delete();
710 }
711 }
712
713 //Delete the actual row
714 inventoryRow = inventoryFolderTable.Rows.Find(folderID.ToString());
715 if (inventoryRow != null)
716 {
717 deleteItemsInFolder(folderID);
718 inventoryRow.Delete();
719 }
720
721 invFoldersDa.Update(ds, "inventoryfolders");
722 }
723 }
724
725 /***********************************************************************
726 *
727 * Data Table definitions
728 *
729 **********************************************************************/
730
731 /// <summary>
732 /// Create the "inventoryitems" table
733 /// </summary>
734 private static DataTable createInventoryItemsTable()
735 {
736 DataTable inv = new DataTable("inventoryitems");
737
738 createCol(inv, "UUID", typeof (String)); //inventoryID
739 createCol(inv, "assetID", typeof (String));
740 createCol(inv, "assetType", typeof (Int32));
741 createCol(inv, "invType", typeof (Int32));
742 createCol(inv, "parentFolderID", typeof (String));
743 createCol(inv, "avatarID", typeof (String));
744 createCol(inv, "creatorsID", typeof (String));
745
746 createCol(inv, "inventoryName", typeof (String));
747 createCol(inv, "inventoryDescription", typeof (String));
748 // permissions
749 createCol(inv, "inventoryNextPermissions", typeof (Int32));
750 createCol(inv, "inventoryCurrentPermissions", typeof (Int32));
751 createCol(inv, "inventoryBasePermissions", typeof (Int32));
752 createCol(inv, "inventoryEveryOnePermissions", typeof (Int32));
753 createCol(inv, "inventoryGroupPermissions", typeof (Int32));
754
755 // sale info
756 createCol(inv, "salePrice", typeof(Int32));
757 createCol(inv, "saleType", typeof(Byte));
758
759 // creation date
760 createCol(inv, "creationDate", typeof(Int32));
761
762 // group info
763 createCol(inv, "groupID", typeof(String));
764 createCol(inv, "groupOwned", typeof(Boolean));
765
766 // Flags
767 createCol(inv, "flags", typeof(UInt32));
768
769 inv.PrimaryKey = new DataColumn[] { inv.Columns["UUID"] };
770 return inv;
771 }
772
773 /// <summary>
774 /// Creates the "inventoryfolders" table
775 /// </summary>
776 /// <returns></returns>
777 private static DataTable createInventoryFoldersTable()
778 {
779 DataTable fol = new DataTable("inventoryfolders");
780
781 createCol(fol, "UUID", typeof (String)); //folderID
782 createCol(fol, "name", typeof (String));
783 createCol(fol, "agentID", typeof (String));
784 createCol(fol, "parentID", typeof (String));
785 createCol(fol, "type", typeof (Int32));
786 createCol(fol, "version", typeof (Int32));
787
788 fol.PrimaryKey = new DataColumn[] {fol.Columns["UUID"]};
789 return fol;
790 }
791
792 /// <summary>
793 ///
794 /// </summary>
795 /// <param name="da"></param>
796 /// <param name="conn"></param>
797 private void setupItemsCommands(SqliteDataAdapter da, SqliteConnection conn)
798 {
799 lock (ds)
800 {
801 da.InsertCommand = createInsertCommand("inventoryitems", ds.Tables["inventoryitems"]);
802 da.InsertCommand.Connection = conn;
803
804 da.UpdateCommand = createUpdateCommand("inventoryitems", "UUID=:UUID", ds.Tables["inventoryitems"]);
805 da.UpdateCommand.Connection = conn;
806
807 SqliteCommand delete = new SqliteCommand("delete from inventoryitems where UUID = :UUID");
808 delete.Parameters.Add(createSqliteParameter("UUID", typeof(String)));
809 delete.Connection = conn;
810 da.DeleteCommand = delete;
811 }
812 }
813
814 /// <summary>
815 ///
816 /// </summary>
817 /// <param name="da"></param>
818 /// <param name="conn"></param>
819 private void setupFoldersCommands(SqliteDataAdapter da, SqliteConnection conn)
820 {
821 lock (ds)
822 {
823 da.InsertCommand = createInsertCommand("inventoryfolders", ds.Tables["inventoryfolders"]);
824 da.InsertCommand.Connection = conn;
825
826 da.UpdateCommand = createUpdateCommand("inventoryfolders", "UUID=:UUID", ds.Tables["inventoryfolders"]);
827 da.UpdateCommand.Connection = conn;
828
829 SqliteCommand delete = new SqliteCommand("delete from inventoryfolders where UUID = :UUID");
830 delete.Parameters.Add(createSqliteParameter("UUID", typeof(String)));
831 delete.Connection = conn;
832 da.DeleteCommand = delete;
833 }
834 }
835
836 /// <summary>
837 ///
838 /// </summary>
839 /// <param name="row"></param>
840 /// <returns></returns>
841 private static InventoryFolderBase buildFolder(DataRow row)
842 {
843 InventoryFolderBase folder = new InventoryFolderBase();
844 folder.ID = new UUID((string) row["UUID"]);
845 folder.Name = (string) row["name"];
846 folder.Owner = new UUID((string) row["agentID"]);
847 folder.ParentID = new UUID((string) row["parentID"]);
848 folder.Type = Convert.ToInt16(row["type"]);
849 folder.Version = Convert.ToUInt16(row["version"]);
850 return folder;
851 }
852
853 /// <summary>
854 ///
855 /// </summary>
856 /// <param name="row"></param>
857 /// <param name="folder"></param>
858 private static void fillFolderRow(DataRow row, InventoryFolderBase folder)
859 {
860 row["UUID"] = folder.ID.ToString();
861 row["name"] = folder.Name;
862 row["agentID"] = folder.Owner.ToString();
863 row["parentID"] = folder.ParentID.ToString();
864 row["type"] = folder.Type;
865 row["version"] = folder.Version;
866 }
867
868 /// <summary>
869 ///
870 /// </summary>
871 /// <param name="row"></param>
872 /// <param name="folder"></param>
873 private static void moveFolderRow(DataRow row, InventoryFolderBase folder)
874 {
875 row["UUID"] = folder.ID.ToString();
876 row["parentID"] = folder.ParentID.ToString();
877 }
878
879 public List<InventoryItemBase> fetchActiveGestures (UUID avatarID)
880 {
881 lock (ds)
882 {
883 List<InventoryItemBase> items = new List<InventoryItemBase>();
884
885 DataTable inventoryItemTable = ds.Tables["inventoryitems"];
886 string selectExp
887 = "avatarID = '" + avatarID + "' AND assetType = " + (int)AssetType.Gesture + " AND flags = 1";
888 //m_log.DebugFormat("[SQL]: sql = " + selectExp);
889 DataRow[] rows = inventoryItemTable.Select(selectExp);
890 foreach (DataRow row in rows)
891 {
892 items.Add(buildItem(row));
893 }
894 return items;
895 }
896 }
897 }
898}
diff --git a/OpenSim/Data/SQLiteNG/SQLiteRegionData.cs b/OpenSim/Data/SQLiteNG/SQLiteRegionData.cs
new file mode 100644
index 0000000..d2ba9ae
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/SQLiteRegionData.cs
@@ -0,0 +1,2264 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Drawing;
32using System.IO;
33using System.Reflection;
34using log4net;
35using Mono.Data.SqliteClient;
36using OpenMetaverse;
37using OpenSim.Framework;
38using OpenSim.Region.Framework.Interfaces;
39using OpenSim.Region.Framework.Scenes;
40
41namespace OpenSim.Data.SQLite
42{
43 /// <summary>
44 /// A RegionData Interface to the SQLite database
45 /// </summary>
46 public class SQLiteRegionData : IRegionDataStore
47 {
48 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
49
50 private const string primSelect = "select * from prims";
51 private const string shapeSelect = "select * from primshapes";
52 private const string itemsSelect = "select * from primitems";
53 private const string terrainSelect = "select * from terrain limit 1";
54 private const string landSelect = "select * from land";
55 private const string landAccessListSelect = "select distinct * from landaccesslist";
56 private const string regionbanListSelect = "select * from regionban";
57 private const string regionSettingsSelect = "select * from regionsettings";
58
59 private DataSet ds;
60 private SqliteDataAdapter primDa;
61 private SqliteDataAdapter shapeDa;
62 private SqliteDataAdapter itemsDa;
63 private SqliteDataAdapter terrainDa;
64 private SqliteDataAdapter landDa;
65 private SqliteDataAdapter landAccessListDa;
66 private SqliteDataAdapter regionSettingsDa;
67
68 private SqliteConnection m_conn;
69
70 private String m_connectionString;
71
72 // Temporary attribute while this is experimental
73
74 /***********************************************************************
75 *
76 * Public Interface Functions
77 *
78 **********************************************************************/
79
80 /// <summary>
81 /// See IRegionDataStore
82 /// <list type="bullet">
83 /// <item>Initialises RegionData Interface</item>
84 /// <item>Loads and initialises a new SQLite connection and maintains it.</item>
85 /// </list>
86 /// </summary>
87 /// <param name="connectionString">the connection string</param>
88 public void Initialise(string connectionString)
89 {
90 m_connectionString = connectionString;
91
92 ds = new DataSet();
93
94 m_log.Info("[REGION DB]: Sqlite - connecting: " + connectionString);
95 m_conn = new SqliteConnection(m_connectionString);
96 m_conn.Open();
97
98
99
100 SqliteCommand primSelectCmd = new SqliteCommand(primSelect, m_conn);
101 primDa = new SqliteDataAdapter(primSelectCmd);
102 // SqliteCommandBuilder primCb = new SqliteCommandBuilder(primDa);
103
104 SqliteCommand shapeSelectCmd = new SqliteCommand(shapeSelect, m_conn);
105 shapeDa = new SqliteDataAdapter(shapeSelectCmd);
106 // SqliteCommandBuilder shapeCb = new SqliteCommandBuilder(shapeDa);
107
108 SqliteCommand itemsSelectCmd = new SqliteCommand(itemsSelect, m_conn);
109 itemsDa = new SqliteDataAdapter(itemsSelectCmd);
110
111 SqliteCommand terrainSelectCmd = new SqliteCommand(terrainSelect, m_conn);
112 terrainDa = new SqliteDataAdapter(terrainSelectCmd);
113
114 SqliteCommand landSelectCmd = new SqliteCommand(landSelect, m_conn);
115 landDa = new SqliteDataAdapter(landSelectCmd);
116
117 SqliteCommand landAccessListSelectCmd = new SqliteCommand(landAccessListSelect, m_conn);
118 landAccessListDa = new SqliteDataAdapter(landAccessListSelectCmd);
119
120 SqliteCommand regionSettingsSelectCmd = new SqliteCommand(regionSettingsSelect, m_conn);
121 regionSettingsDa = new SqliteDataAdapter(regionSettingsSelectCmd);
122 // This actually does the roll forward assembly stuff
123 Assembly assem = GetType().Assembly;
124 Migration m = new Migration(m_conn, assem, "RegionStore");
125 m.Update();
126
127 lock (ds)
128 {
129 ds.Tables.Add(createPrimTable());
130 setupPrimCommands(primDa, m_conn);
131 primDa.Fill(ds.Tables["prims"]);
132
133 ds.Tables.Add(createShapeTable());
134 setupShapeCommands(shapeDa, m_conn);
135
136 ds.Tables.Add(createItemsTable());
137 setupItemsCommands(itemsDa, m_conn);
138 itemsDa.Fill(ds.Tables["primitems"]);
139
140 ds.Tables.Add(createTerrainTable());
141 setupTerrainCommands(terrainDa, m_conn);
142
143 ds.Tables.Add(createLandTable());
144 setupLandCommands(landDa, m_conn);
145
146 ds.Tables.Add(createLandAccessListTable());
147 setupLandAccessCommands(landAccessListDa, m_conn);
148
149 ds.Tables.Add(createRegionSettingsTable());
150
151 setupRegionSettingsCommands(regionSettingsDa, m_conn);
152
153 // WORKAROUND: This is a work around for sqlite on
154 // windows, which gets really unhappy with blob columns
155 // that have no sample data in them. At some point we
156 // need to actually find a proper way to handle this.
157 try
158 {
159 shapeDa.Fill(ds.Tables["primshapes"]);
160 }
161 catch (Exception)
162 {
163 m_log.Info("[REGION DB]: Caught fill error on primshapes table");
164 }
165
166 try
167 {
168 terrainDa.Fill(ds.Tables["terrain"]);
169 }
170 catch (Exception)
171 {
172 m_log.Info("[REGION DB]: Caught fill error on terrain table");
173 }
174
175 try
176 {
177 landDa.Fill(ds.Tables["land"]);
178 }
179 catch (Exception)
180 {
181 m_log.Info("[REGION DB]: Caught fill error on land table");
182 }
183
184 try
185 {
186 landAccessListDa.Fill(ds.Tables["landaccesslist"]);
187 }
188 catch (Exception)
189 {
190 m_log.Info("[REGION DB]: Caught fill error on landaccesslist table");
191 }
192
193 try
194 {
195 regionSettingsDa.Fill(ds.Tables["regionsettings"]);
196 }
197 catch (Exception)
198 {
199 m_log.Info("[REGION DB]: Caught fill error on regionsettings table");
200 }
201 return;
202 }
203 }
204
205 public void Dispose()
206 {
207 if (m_conn != null)
208 {
209 m_conn.Close();
210 m_conn = null;
211 }
212 if (ds != null)
213 {
214 ds.Dispose();
215 ds = null;
216 }
217 if (primDa != null)
218 {
219 primDa.Dispose();
220 primDa = null;
221 }
222 if (shapeDa != null)
223 {
224 shapeDa.Dispose();
225 shapeDa = null;
226 }
227 if (itemsDa != null)
228 {
229 itemsDa.Dispose();
230 itemsDa = null;
231 }
232 if (terrainDa != null)
233 {
234 terrainDa.Dispose();
235 terrainDa = null;
236 }
237 if (landDa != null)
238 {
239 landDa.Dispose();
240 landDa = null;
241 }
242 if (landAccessListDa != null)
243 {
244 landAccessListDa.Dispose();
245 landAccessListDa = null;
246 }
247 if (regionSettingsDa != null)
248 {
249 regionSettingsDa.Dispose();
250 regionSettingsDa = null;
251 }
252 }
253
254 public void StoreRegionSettings(RegionSettings rs)
255 {
256 lock (ds)
257 {
258 DataTable regionsettings = ds.Tables["regionsettings"];
259
260 DataRow settingsRow = regionsettings.Rows.Find(rs.RegionUUID.ToString());
261 if (settingsRow == null)
262 {
263 settingsRow = regionsettings.NewRow();
264 fillRegionSettingsRow(settingsRow, rs);
265 regionsettings.Rows.Add(settingsRow);
266 }
267 else
268 {
269 fillRegionSettingsRow(settingsRow, rs);
270 }
271
272 Commit();
273 }
274 }
275 public RegionLightShareData LoadRegionWindlightSettings(UUID regionUUID)
276 {
277 //This connector doesn't support the windlight module yet
278 //Return default LL windlight settings
279 return new RegionLightShareData();
280 }
281 public void StoreRegionWindlightSettings(RegionLightShareData wl)
282 {
283 //This connector doesn't support the windlight module yet
284 }
285 public RegionSettings LoadRegionSettings(UUID regionUUID)
286 {
287 lock (ds)
288 {
289 DataTable regionsettings = ds.Tables["regionsettings"];
290
291 string searchExp = "regionUUID = '" + regionUUID.ToString() + "'";
292 DataRow[] rawsettings = regionsettings.Select(searchExp);
293 if (rawsettings.Length == 0)
294 {
295 RegionSettings rs = new RegionSettings();
296 rs.RegionUUID = regionUUID;
297 rs.OnSave += StoreRegionSettings;
298
299 StoreRegionSettings(rs);
300
301 return rs;
302 }
303 DataRow row = rawsettings[0];
304
305 RegionSettings newSettings = buildRegionSettings(row);
306 newSettings.OnSave += StoreRegionSettings;
307
308 return newSettings;
309 }
310 }
311
312 /// <summary>
313 /// Adds an object into region storage
314 /// </summary>
315 /// <param name="obj">the object</param>
316 /// <param name="regionUUID">the region UUID</param>
317 public void StoreObject(SceneObjectGroup obj, UUID regionUUID)
318 {
319 uint flags = obj.RootPart.GetEffectiveObjectFlags();
320
321 // Eligibility check
322 //
323 if ((flags & (uint)PrimFlags.Temporary) != 0)
324 return;
325 if ((flags & (uint)PrimFlags.TemporaryOnRez) != 0)
326 return;
327
328 lock (ds)
329 {
330 foreach (SceneObjectPart prim in obj.Children.Values)
331 {
332// m_log.Info("[REGION DB]: Adding obj: " + obj.UUID + " to region: " + regionUUID);
333 addPrim(prim, obj.UUID, regionUUID);
334 }
335 }
336
337 Commit();
338 // m_log.Info("[Dump of prims]: " + ds.GetXml());
339 }
340
341 /// <summary>
342 /// Removes an object from region storage
343 /// </summary>
344 /// <param name="obj">the object</param>
345 /// <param name="regionUUID">the region UUID</param>
346 public void RemoveObject(UUID obj, UUID regionUUID)
347 {
348 // m_log.InfoFormat("[REGION DB]: Removing obj: {0} from region: {1}", obj.Guid, regionUUID);
349
350 DataTable prims = ds.Tables["prims"];
351 DataTable shapes = ds.Tables["primshapes"];
352
353 string selectExp = "SceneGroupID = '" + obj + "' and RegionUUID = '" + regionUUID + "'";
354 lock (ds)
355 {
356 DataRow[] primRows = prims.Select(selectExp);
357 foreach (DataRow row in primRows)
358 {
359 // Remove shape rows
360 UUID uuid = new UUID((string) row["UUID"]);
361 DataRow shapeRow = shapes.Rows.Find(uuid.ToString());
362 if (shapeRow != null)
363 {
364 shapeRow.Delete();
365 }
366
367 RemoveItems(uuid);
368
369 // Remove prim row
370 row.Delete();
371 }
372 }
373
374 Commit();
375 }
376
377 /// <summary>
378 /// Remove all persisted items of the given prim.
379 /// The caller must acquire the necessrary synchronization locks and commit or rollback changes.
380 /// </summary>
381 /// <param name="uuid">The item UUID</param>
382 private void RemoveItems(UUID uuid)
383 {
384 DataTable items = ds.Tables["primitems"];
385
386 String sql = String.Format("primID = '{0}'", uuid);
387 DataRow[] itemRows = items.Select(sql);
388
389 foreach (DataRow itemRow in itemRows)
390 {
391 itemRow.Delete();
392 }
393 }
394
395 /// <summary>
396 /// Load persisted objects from region storage.
397 /// </summary>
398 /// <param name="regionUUID">The region UUID</param>
399 /// <returns>List of loaded groups</returns>
400 public List<SceneObjectGroup> LoadObjects(UUID regionUUID)
401 {
402 Dictionary<UUID, SceneObjectGroup> createdObjects = new Dictionary<UUID, SceneObjectGroup>();
403
404 List<SceneObjectGroup> retvals = new List<SceneObjectGroup>();
405
406 DataTable prims = ds.Tables["prims"];
407 DataTable shapes = ds.Tables["primshapes"];
408
409 string byRegion = "RegionUUID = '" + regionUUID + "'";
410
411 lock (ds)
412 {
413 DataRow[] primsForRegion = prims.Select(byRegion);
414 m_log.Info("[REGION DB]: Loaded " + primsForRegion.Length + " prims for region: " + regionUUID);
415
416 // First, create all groups
417 foreach (DataRow primRow in primsForRegion)
418 {
419 try
420 {
421 SceneObjectPart prim = null;
422
423 string uuid = (string) primRow["UUID"];
424 string objID = (string) primRow["SceneGroupID"];
425
426 if (uuid == objID) //is new SceneObjectGroup ?
427 {
428 prim = buildPrim(primRow);
429 DataRow shapeRow = shapes.Rows.Find(prim.UUID.ToString());
430 if (shapeRow != null)
431 {
432 prim.Shape = buildShape(shapeRow);
433 }
434 else
435 {
436 m_log.Info(
437 "[REGION DB]: No shape found for prim in storage, so setting default box shape");
438 prim.Shape = PrimitiveBaseShape.Default;
439 }
440
441 SceneObjectGroup group = new SceneObjectGroup(prim);
442 createdObjects.Add(group.UUID, group);
443 retvals.Add(group);
444 LoadItems(prim);
445 }
446 }
447 catch (Exception e)
448 {
449 m_log.Error("[REGION DB]: Failed create prim object in new group, exception and data follows");
450 m_log.Info("[REGION DB]: " + e.ToString());
451 foreach (DataColumn col in prims.Columns)
452 {
453 m_log.Info("[REGION DB]: Col: " + col.ColumnName + " => " + primRow[col]);
454 }
455 }
456 }
457
458 // Now fill the groups with part data
459 foreach (DataRow primRow in primsForRegion)
460 {
461 try
462 {
463 SceneObjectPart prim = null;
464
465 string uuid = (string) primRow["UUID"];
466 string objID = (string) primRow["SceneGroupID"];
467 if (uuid != objID) //is new SceneObjectGroup ?
468 {
469 prim = buildPrim(primRow);
470 DataRow shapeRow = shapes.Rows.Find(prim.UUID.ToString());
471 if (shapeRow != null)
472 {
473 prim.Shape = buildShape(shapeRow);
474 }
475 else
476 {
477 m_log.Warn(
478 "[REGION DB]: No shape found for prim in storage, so setting default box shape");
479 prim.Shape = PrimitiveBaseShape.Default;
480 }
481
482 createdObjects[new UUID(objID)].AddPart(prim);
483 LoadItems(prim);
484 }
485 }
486 catch (Exception e)
487 {
488 m_log.Error("[REGION DB]: Failed create prim object in group, exception and data follows");
489 m_log.Info("[REGION DB]: " + e.ToString());
490 foreach (DataColumn col in prims.Columns)
491 {
492 m_log.Info("[REGION DB]: Col: " + col.ColumnName + " => " + primRow[col]);
493 }
494 }
495 }
496 }
497 return retvals;
498 }
499
500 /// <summary>
501 /// Load in a prim's persisted inventory.
502 /// </summary>
503 /// <param name="prim">the prim</param>
504 private void LoadItems(SceneObjectPart prim)
505 {
506 //m_log.DebugFormat("[DATASTORE]: Loading inventory for {0}, {1}", prim.Name, prim.UUID);
507
508 DataTable dbItems = ds.Tables["primitems"];
509 String sql = String.Format("primID = '{0}'", prim.UUID.ToString());
510 DataRow[] dbItemRows = dbItems.Select(sql);
511 IList<TaskInventoryItem> inventory = new List<TaskInventoryItem>();
512
513 foreach (DataRow row in dbItemRows)
514 {
515 TaskInventoryItem item = buildItem(row);
516 inventory.Add(item);
517
518 //m_log.DebugFormat("[DATASTORE]: Restored item {0}, {1}", item.Name, item.ItemID);
519 }
520
521 prim.Inventory.RestoreInventoryItems(inventory);
522 }
523
524 /// <summary>
525 /// Store a terrain revision in region storage
526 /// </summary>
527 /// <param name="ter">terrain heightfield</param>
528 /// <param name="regionID">region UUID</param>
529 public void StoreTerrain(double[,] ter, UUID regionID)
530 {
531 lock (ds)
532 {
533 int revision = Util.UnixTimeSinceEpoch();
534
535 // This is added to get rid of the infinitely growing
536 // terrain databases which negatively impact on SQLite
537 // over time. Before reenabling this feature there
538 // needs to be a limitter put on the number of
539 // revisions in the database, as this old
540 // implementation is a DOS attack waiting to happen.
541
542 using (
543 SqliteCommand cmd =
544 new SqliteCommand("delete from terrain where RegionUUID=:RegionUUID and Revision <= :Revision",
545 m_conn))
546 {
547 cmd.Parameters.Add(new SqliteParameter(":RegionUUID", regionID.ToString()));
548 cmd.Parameters.Add(new SqliteParameter(":Revision", revision));
549 cmd.ExecuteNonQuery();
550 }
551
552 // the following is an work around for .NET. The perf
553 // issues associated with it aren't as bad as you think.
554 m_log.Info("[REGION DB]: Storing terrain revision r" + revision.ToString());
555 String sql = "insert into terrain(RegionUUID, Revision, Heightfield)" +
556 " values(:RegionUUID, :Revision, :Heightfield)";
557
558 using (SqliteCommand cmd = new SqliteCommand(sql, m_conn))
559 {
560 cmd.Parameters.Add(new SqliteParameter(":RegionUUID", regionID.ToString()));
561 cmd.Parameters.Add(new SqliteParameter(":Revision", revision));
562 cmd.Parameters.Add(new SqliteParameter(":Heightfield", serializeTerrain(ter)));
563 cmd.ExecuteNonQuery();
564 }
565 }
566 }
567
568 /// <summary>
569 /// Load the latest terrain revision from region storage
570 /// </summary>
571 /// <param name="regionID">the region UUID</param>
572 /// <returns>Heightfield data</returns>
573 public double[,] LoadTerrain(UUID regionID)
574 {
575 lock (ds)
576 {
577 double[,] terret = new double[(int)Constants.RegionSize, (int)Constants.RegionSize];
578 terret.Initialize();
579
580 String sql = "select RegionUUID, Revision, Heightfield from terrain" +
581 " where RegionUUID=:RegionUUID order by Revision desc";
582
583 using (SqliteCommand cmd = new SqliteCommand(sql, m_conn))
584 {
585 cmd.Parameters.Add(new SqliteParameter(":RegionUUID", regionID.ToString()));
586
587 using (IDataReader row = cmd.ExecuteReader())
588 {
589 int rev = 0;
590 if (row.Read())
591 {
592 // TODO: put this into a function
593 using (MemoryStream str = new MemoryStream((byte[])row["Heightfield"]))
594 {
595 using (BinaryReader br = new BinaryReader(str))
596 {
597 for (int x = 0; x < (int)Constants.RegionSize; x++)
598 {
599 for (int y = 0; y < (int)Constants.RegionSize; y++)
600 {
601 terret[x, y] = br.ReadDouble();
602 }
603 }
604 }
605 }
606 rev = (int) row["Revision"];
607 }
608 else
609 {
610 m_log.Info("[REGION DB]: No terrain found for region");
611 return null;
612 }
613
614 m_log.Info("[REGION DB]: Loaded terrain revision r" + rev.ToString());
615 }
616 }
617 return terret;
618 }
619 }
620
621 /// <summary>
622 ///
623 /// </summary>
624 /// <param name="globalID"></param>
625 public void RemoveLandObject(UUID globalID)
626 {
627 lock (ds)
628 {
629 // Can't use blanket SQL statements when using SqlAdapters unless you re-read the data into the adapter
630 // after you're done.
631 // replaced below code with the SqliteAdapter version.
632 //using (SqliteCommand cmd = new SqliteCommand("delete from land where UUID=:UUID", m_conn))
633 //{
634 // cmd.Parameters.Add(new SqliteParameter(":UUID", globalID.ToString()));
635 // cmd.ExecuteNonQuery();
636 //}
637
638 //using (SqliteCommand cmd = new SqliteCommand("delete from landaccesslist where LandUUID=:UUID", m_conn))
639 //{
640 // cmd.Parameters.Add(new SqliteParameter(":UUID", globalID.ToString()));
641 // cmd.ExecuteNonQuery();
642 //}
643
644 DataTable land = ds.Tables["land"];
645 DataTable landaccesslist = ds.Tables["landaccesslist"];
646 DataRow landRow = land.Rows.Find(globalID.ToString());
647 if (landRow != null)
648 {
649 land.Rows.Remove(landRow);
650 }
651 List<DataRow> rowsToDelete = new List<DataRow>();
652 foreach (DataRow rowToCheck in landaccesslist.Rows)
653 {
654 if (rowToCheck["LandUUID"].ToString() == globalID.ToString())
655 rowsToDelete.Add(rowToCheck);
656 }
657 for (int iter = 0; iter < rowsToDelete.Count; iter++)
658 {
659 landaccesslist.Rows.Remove(rowsToDelete[iter]);
660 }
661
662
663 }
664 Commit();
665 }
666
667 /// <summary>
668 ///
669 /// </summary>
670 /// <param name="parcel"></param>
671 public void StoreLandObject(ILandObject parcel)
672 {
673 lock (ds)
674 {
675 DataTable land = ds.Tables["land"];
676 DataTable landaccesslist = ds.Tables["landaccesslist"];
677
678 DataRow landRow = land.Rows.Find(parcel.LandData.GlobalID.ToString());
679 if (landRow == null)
680 {
681 landRow = land.NewRow();
682 fillLandRow(landRow, parcel.LandData, parcel.RegionUUID);
683 land.Rows.Add(landRow);
684 }
685 else
686 {
687 fillLandRow(landRow, parcel.LandData, parcel.RegionUUID);
688 }
689
690 // I know this caused someone issues before, but OpenSim is unusable if we leave this stuff around
691 //using (SqliteCommand cmd = new SqliteCommand("delete from landaccesslist where LandUUID=:LandUUID", m_conn))
692 //{
693 // cmd.Parameters.Add(new SqliteParameter(":LandUUID", parcel.LandData.GlobalID.ToString()));
694 // cmd.ExecuteNonQuery();
695
696// }
697
698 // This is the slower.. but more appropriate thing to do
699
700 // We can't modify the table with direct queries before calling Commit() and re-filling them.
701 List<DataRow> rowsToDelete = new List<DataRow>();
702 foreach (DataRow rowToCheck in landaccesslist.Rows)
703 {
704 if (rowToCheck["LandUUID"].ToString() == parcel.LandData.GlobalID.ToString())
705 rowsToDelete.Add(rowToCheck);
706 }
707 for (int iter = 0; iter < rowsToDelete.Count; iter++)
708 {
709 landaccesslist.Rows.Remove(rowsToDelete[iter]);
710 }
711 rowsToDelete.Clear();
712 foreach (ParcelManager.ParcelAccessEntry entry in parcel.LandData.ParcelAccessList)
713 {
714 DataRow newAccessRow = landaccesslist.NewRow();
715 fillLandAccessRow(newAccessRow, entry, parcel.LandData.GlobalID);
716 landaccesslist.Rows.Add(newAccessRow);
717 }
718 }
719
720 Commit();
721 }
722
723 /// <summary>
724 ///
725 /// </summary>
726 /// <param name="regionUUID"></param>
727 /// <returns></returns>
728 public List<LandData> LoadLandObjects(UUID regionUUID)
729 {
730 List<LandData> landDataForRegion = new List<LandData>();
731 lock (ds)
732 {
733 DataTable land = ds.Tables["land"];
734 DataTable landaccesslist = ds.Tables["landaccesslist"];
735 string searchExp = "RegionUUID = '" + regionUUID + "'";
736 DataRow[] rawDataForRegion = land.Select(searchExp);
737 foreach (DataRow rawDataLand in rawDataForRegion)
738 {
739 LandData newLand = buildLandData(rawDataLand);
740 string accessListSearchExp = "LandUUID = '" + newLand.GlobalID + "'";
741 DataRow[] rawDataForLandAccessList = landaccesslist.Select(accessListSearchExp);
742 foreach (DataRow rawDataLandAccess in rawDataForLandAccessList)
743 {
744 newLand.ParcelAccessList.Add(buildLandAccessData(rawDataLandAccess));
745 }
746
747 landDataForRegion.Add(newLand);
748 }
749 }
750 return landDataForRegion;
751 }
752
753 /// <summary>
754 ///
755 /// </summary>
756 public void Commit()
757 {
758 lock (ds)
759 {
760 primDa.Update(ds, "prims");
761 shapeDa.Update(ds, "primshapes");
762
763 itemsDa.Update(ds, "primitems");
764
765 terrainDa.Update(ds, "terrain");
766 landDa.Update(ds, "land");
767 landAccessListDa.Update(ds, "landaccesslist");
768 try
769 {
770 regionSettingsDa.Update(ds, "regionsettings");
771 }
772 catch (SqliteExecutionException SqlEx)
773 {
774 if (SqlEx.Message.Contains("logic error"))
775 {
776 throw new Exception(
777 "There was a SQL error or connection string configuration error when saving the region settings. This could be a bug, it could also happen if ConnectionString is defined in the [DatabaseService] section of StandaloneCommon.ini in the config_include folder. This could also happen if the config_include folder doesn't exist or if the OpenSim.ini [Architecture] section isn't set. If this is your first time running OpenSimulator, please restart the simulator and bug a developer to fix this!",
778 SqlEx);
779 }
780 else
781 {
782 throw SqlEx;
783 }
784 }
785 ds.AcceptChanges();
786 }
787 }
788
789 /// <summary>
790 /// See <see cref="Commit"/>
791 /// </summary>
792 public void Shutdown()
793 {
794 Commit();
795 }
796
797 /***********************************************************************
798 *
799 * Database Definition Functions
800 *
801 * This should be db agnostic as we define them in ADO.NET terms
802 *
803 **********************************************************************/
804
805 /// <summary>
806 ///
807 /// </summary>
808 /// <param name="dt"></param>
809 /// <param name="name"></param>
810 /// <param name="type"></param>
811 private static void createCol(DataTable dt, string name, Type type)
812 {
813 DataColumn col = new DataColumn(name, type);
814 dt.Columns.Add(col);
815 }
816
817 /// <summary>
818 /// Creates the "terrain" table
819 /// </summary>
820 /// <returns>terrain table DataTable</returns>
821 private static DataTable createTerrainTable()
822 {
823 DataTable terrain = new DataTable("terrain");
824
825 createCol(terrain, "RegionUUID", typeof (String));
826 createCol(terrain, "Revision", typeof (Int32));
827 createCol(terrain, "Heightfield", typeof (Byte[]));
828
829 return terrain;
830 }
831
832 /// <summary>
833 /// Creates the "prims" table
834 /// </summary>
835 /// <returns>prim table DataTable</returns>
836 private static DataTable createPrimTable()
837 {
838 DataTable prims = new DataTable("prims");
839
840 createCol(prims, "UUID", typeof (String));
841 createCol(prims, "RegionUUID", typeof (String));
842 createCol(prims, "CreationDate", typeof (Int32));
843 createCol(prims, "Name", typeof (String));
844 createCol(prims, "SceneGroupID", typeof (String));
845 // various text fields
846 createCol(prims, "Text", typeof (String));
847 createCol(prims, "ColorR", typeof (Int32));
848 createCol(prims, "ColorG", typeof (Int32));
849 createCol(prims, "ColorB", typeof (Int32));
850 createCol(prims, "ColorA", typeof (Int32));
851 createCol(prims, "Description", typeof (String));
852 createCol(prims, "SitName", typeof (String));
853 createCol(prims, "TouchName", typeof (String));
854 // permissions
855 createCol(prims, "ObjectFlags", typeof (Int32));
856 createCol(prims, "CreatorID", typeof (String));
857 createCol(prims, "OwnerID", typeof (String));
858 createCol(prims, "GroupID", typeof (String));
859 createCol(prims, "LastOwnerID", typeof (String));
860 createCol(prims, "OwnerMask", typeof (Int32));
861 createCol(prims, "NextOwnerMask", typeof (Int32));
862 createCol(prims, "GroupMask", typeof (Int32));
863 createCol(prims, "EveryoneMask", typeof (Int32));
864 createCol(prims, "BaseMask", typeof (Int32));
865 // vectors
866 createCol(prims, "PositionX", typeof (Double));
867 createCol(prims, "PositionY", typeof (Double));
868 createCol(prims, "PositionZ", typeof (Double));
869 createCol(prims, "GroupPositionX", typeof (Double));
870 createCol(prims, "GroupPositionY", typeof (Double));
871 createCol(prims, "GroupPositionZ", typeof (Double));
872 createCol(prims, "VelocityX", typeof (Double));
873 createCol(prims, "VelocityY", typeof (Double));
874 createCol(prims, "VelocityZ", typeof (Double));
875 createCol(prims, "AngularVelocityX", typeof (Double));
876 createCol(prims, "AngularVelocityY", typeof (Double));
877 createCol(prims, "AngularVelocityZ", typeof (Double));
878 createCol(prims, "AccelerationX", typeof (Double));
879 createCol(prims, "AccelerationY", typeof (Double));
880 createCol(prims, "AccelerationZ", typeof (Double));
881 // quaternions
882 createCol(prims, "RotationX", typeof (Double));
883 createCol(prims, "RotationY", typeof (Double));
884 createCol(prims, "RotationZ", typeof (Double));
885 createCol(prims, "RotationW", typeof (Double));
886
887 // sit target
888 createCol(prims, "SitTargetOffsetX", typeof (Double));
889 createCol(prims, "SitTargetOffsetY", typeof (Double));
890 createCol(prims, "SitTargetOffsetZ", typeof (Double));
891
892 createCol(prims, "SitTargetOrientW", typeof (Double));
893 createCol(prims, "SitTargetOrientX", typeof (Double));
894 createCol(prims, "SitTargetOrientY", typeof (Double));
895 createCol(prims, "SitTargetOrientZ", typeof (Double));
896
897 createCol(prims, "PayPrice", typeof(Int32));
898 createCol(prims, "PayButton1", typeof(Int32));
899 createCol(prims, "PayButton2", typeof(Int32));
900 createCol(prims, "PayButton3", typeof(Int32));
901 createCol(prims, "PayButton4", typeof(Int32));
902
903 createCol(prims, "LoopedSound", typeof(String));
904 createCol(prims, "LoopedSoundGain", typeof(Double));
905 createCol(prims, "TextureAnimation", typeof(String));
906 createCol(prims, "ParticleSystem", typeof(String));
907
908 createCol(prims, "OmegaX", typeof(Double));
909 createCol(prims, "OmegaY", typeof(Double));
910 createCol(prims, "OmegaZ", typeof(Double));
911
912 createCol(prims, "CameraEyeOffsetX", typeof(Double));
913 createCol(prims, "CameraEyeOffsetY", typeof(Double));
914 createCol(prims, "CameraEyeOffsetZ", typeof(Double));
915
916 createCol(prims, "CameraAtOffsetX", typeof(Double));
917 createCol(prims, "CameraAtOffsetY", typeof(Double));
918 createCol(prims, "CameraAtOffsetZ", typeof(Double));
919
920 createCol(prims, "ForceMouselook", typeof(Int16));
921
922 createCol(prims, "ScriptAccessPin", typeof(Int32));
923
924 createCol(prims, "AllowedDrop", typeof(Int16));
925 createCol(prims, "DieAtEdge", typeof(Int16));
926
927 createCol(prims, "SalePrice", typeof(Int32));
928 createCol(prims, "SaleType", typeof(Int16));
929
930 // click action
931 createCol(prims, "ClickAction", typeof (Byte));
932
933 createCol(prims, "Material", typeof(Byte));
934
935 createCol(prims, "CollisionSound", typeof(String));
936 createCol(prims, "CollisionSoundVolume", typeof(Double));
937
938 createCol(prims, "VolumeDetect", typeof(Int16));
939
940 // Add in contraints
941 prims.PrimaryKey = new DataColumn[] {prims.Columns["UUID"]};
942
943 return prims;
944 }
945
946 /// <summary>
947 /// Creates "primshapes" table
948 /// </summary>
949 /// <returns>shape table DataTable</returns>
950 private static DataTable createShapeTable()
951 {
952 DataTable shapes = new DataTable("primshapes");
953 createCol(shapes, "UUID", typeof (String));
954 // shape is an enum
955 createCol(shapes, "Shape", typeof (Int32));
956 // vectors
957 createCol(shapes, "ScaleX", typeof (Double));
958 createCol(shapes, "ScaleY", typeof (Double));
959 createCol(shapes, "ScaleZ", typeof (Double));
960 // paths
961 createCol(shapes, "PCode", typeof (Int32));
962 createCol(shapes, "PathBegin", typeof (Int32));
963 createCol(shapes, "PathEnd", typeof (Int32));
964 createCol(shapes, "PathScaleX", typeof (Int32));
965 createCol(shapes, "PathScaleY", typeof (Int32));
966 createCol(shapes, "PathShearX", typeof (Int32));
967 createCol(shapes, "PathShearY", typeof (Int32));
968 createCol(shapes, "PathSkew", typeof (Int32));
969 createCol(shapes, "PathCurve", typeof (Int32));
970 createCol(shapes, "PathRadiusOffset", typeof (Int32));
971 createCol(shapes, "PathRevolutions", typeof (Int32));
972 createCol(shapes, "PathTaperX", typeof (Int32));
973 createCol(shapes, "PathTaperY", typeof (Int32));
974 createCol(shapes, "PathTwist", typeof (Int32));
975 createCol(shapes, "PathTwistBegin", typeof (Int32));
976 // profile
977 createCol(shapes, "ProfileBegin", typeof (Int32));
978 createCol(shapes, "ProfileEnd", typeof (Int32));
979 createCol(shapes, "ProfileCurve", typeof (Int32));
980 createCol(shapes, "ProfileHollow", typeof (Int32));
981 createCol(shapes, "State", typeof(Int32));
982 // text TODO: this isn't right, but I'm not sure the right
983 // way to specify this as a blob atm
984 createCol(shapes, "Texture", typeof (Byte[]));
985 createCol(shapes, "ExtraParams", typeof (Byte[]));
986
987 shapes.PrimaryKey = new DataColumn[] {shapes.Columns["UUID"]};
988
989 return shapes;
990 }
991
992 /// <summary>
993 /// creates "primitems" table
994 /// </summary>
995 /// <returns>item table DataTable</returns>
996 private static DataTable createItemsTable()
997 {
998 DataTable items = new DataTable("primitems");
999
1000 createCol(items, "itemID", typeof (String));
1001 createCol(items, "primID", typeof (String));
1002 createCol(items, "assetID", typeof (String));
1003 createCol(items, "parentFolderID", typeof (String));
1004
1005 createCol(items, "invType", typeof (Int32));
1006 createCol(items, "assetType", typeof (Int32));
1007
1008 createCol(items, "name", typeof (String));
1009 createCol(items, "description", typeof (String));
1010
1011 createCol(items, "creationDate", typeof (Int64));
1012 createCol(items, "creatorID", typeof (String));
1013 createCol(items, "ownerID", typeof (String));
1014 createCol(items, "lastOwnerID", typeof (String));
1015 createCol(items, "groupID", typeof (String));
1016
1017 createCol(items, "nextPermissions", typeof (UInt32));
1018 createCol(items, "currentPermissions", typeof (UInt32));
1019 createCol(items, "basePermissions", typeof (UInt32));
1020 createCol(items, "everyonePermissions", typeof (UInt32));
1021 createCol(items, "groupPermissions", typeof (UInt32));
1022 createCol(items, "flags", typeof (UInt32));
1023
1024 items.PrimaryKey = new DataColumn[] { items.Columns["itemID"] };
1025
1026 return items;
1027 }
1028
1029 /// <summary>
1030 /// Creates "land" table
1031 /// </summary>
1032 /// <returns>land table DataTable</returns>
1033 private static DataTable createLandTable()
1034 {
1035 DataTable land = new DataTable("land");
1036 createCol(land, "UUID", typeof (String));
1037 createCol(land, "RegionUUID", typeof (String));
1038 createCol(land, "LocalLandID", typeof (UInt32));
1039
1040 // Bitmap is a byte[512]
1041 createCol(land, "Bitmap", typeof (Byte[]));
1042
1043 createCol(land, "Name", typeof (String));
1044 createCol(land, "Desc", typeof (String));
1045 createCol(land, "OwnerUUID", typeof (String));
1046 createCol(land, "IsGroupOwned", typeof (Boolean));
1047 createCol(land, "Area", typeof (Int32));
1048 createCol(land, "AuctionID", typeof (Int32)); //Unemplemented
1049 createCol(land, "Category", typeof (Int32)); //Enum OpenMetaverse.Parcel.ParcelCategory
1050 createCol(land, "ClaimDate", typeof (Int32));
1051 createCol(land, "ClaimPrice", typeof (Int32));
1052 createCol(land, "GroupUUID", typeof (string));
1053 createCol(land, "SalePrice", typeof (Int32));
1054 createCol(land, "LandStatus", typeof (Int32)); //Enum. OpenMetaverse.Parcel.ParcelStatus
1055 createCol(land, "LandFlags", typeof (UInt32));
1056 createCol(land, "LandingType", typeof (Byte));
1057 createCol(land, "MediaAutoScale", typeof (Byte));
1058 createCol(land, "MediaTextureUUID", typeof (String));
1059 createCol(land, "MediaURL", typeof (String));
1060 createCol(land, "MusicURL", typeof (String));
1061 createCol(land, "PassHours", typeof (Double));
1062 createCol(land, "PassPrice", typeof (UInt32));
1063 createCol(land, "SnapshotUUID", typeof (String));
1064 createCol(land, "UserLocationX", typeof (Double));
1065 createCol(land, "UserLocationY", typeof (Double));
1066 createCol(land, "UserLocationZ", typeof (Double));
1067 createCol(land, "UserLookAtX", typeof (Double));
1068 createCol(land, "UserLookAtY", typeof (Double));
1069 createCol(land, "UserLookAtZ", typeof (Double));
1070 createCol(land, "AuthbuyerID", typeof(String));
1071 createCol(land, "OtherCleanTime", typeof(Int32));
1072 createCol(land, "Dwell", typeof(Int32));
1073
1074 land.PrimaryKey = new DataColumn[] {land.Columns["UUID"]};
1075
1076 return land;
1077 }
1078
1079 /// <summary>
1080 /// create "landaccesslist" table
1081 /// </summary>
1082 /// <returns>Landacceslist DataTable</returns>
1083 private static DataTable createLandAccessListTable()
1084 {
1085 DataTable landaccess = new DataTable("landaccesslist");
1086 createCol(landaccess, "LandUUID", typeof (String));
1087 createCol(landaccess, "AccessUUID", typeof (String));
1088 createCol(landaccess, "Flags", typeof (UInt32));
1089
1090 return landaccess;
1091 }
1092
1093 private static DataTable createRegionSettingsTable()
1094 {
1095 DataTable regionsettings = new DataTable("regionsettings");
1096 createCol(regionsettings, "regionUUID", typeof(String));
1097 createCol(regionsettings, "block_terraform", typeof (Int32));
1098 createCol(regionsettings, "block_fly", typeof (Int32));
1099 createCol(regionsettings, "allow_damage", typeof (Int32));
1100 createCol(regionsettings, "restrict_pushing", typeof (Int32));
1101 createCol(regionsettings, "allow_land_resell", typeof (Int32));
1102 createCol(regionsettings, "allow_land_join_divide", typeof (Int32));
1103 createCol(regionsettings, "block_show_in_search", typeof (Int32));
1104 createCol(regionsettings, "agent_limit", typeof (Int32));
1105 createCol(regionsettings, "object_bonus", typeof (Double));
1106 createCol(regionsettings, "maturity", typeof (Int32));
1107 createCol(regionsettings, "disable_scripts", typeof (Int32));
1108 createCol(regionsettings, "disable_collisions", typeof (Int32));
1109 createCol(regionsettings, "disable_physics", typeof (Int32));
1110 createCol(regionsettings, "terrain_texture_1", typeof(String));
1111 createCol(regionsettings, "terrain_texture_2", typeof(String));
1112 createCol(regionsettings, "terrain_texture_3", typeof(String));
1113 createCol(regionsettings, "terrain_texture_4", typeof(String));
1114 createCol(regionsettings, "elevation_1_nw", typeof (Double));
1115 createCol(regionsettings, "elevation_2_nw", typeof (Double));
1116 createCol(regionsettings, "elevation_1_ne", typeof (Double));
1117 createCol(regionsettings, "elevation_2_ne", typeof (Double));
1118 createCol(regionsettings, "elevation_1_se", typeof (Double));
1119 createCol(regionsettings, "elevation_2_se", typeof (Double));
1120 createCol(regionsettings, "elevation_1_sw", typeof (Double));
1121 createCol(regionsettings, "elevation_2_sw", typeof (Double));
1122 createCol(regionsettings, "water_height", typeof (Double));
1123 createCol(regionsettings, "terrain_raise_limit", typeof (Double));
1124 createCol(regionsettings, "terrain_lower_limit", typeof (Double));
1125 createCol(regionsettings, "use_estate_sun", typeof (Int32));
1126 createCol(regionsettings, "sandbox", typeof (Int32));
1127 createCol(regionsettings, "sunvectorx",typeof (Double));
1128 createCol(regionsettings, "sunvectory",typeof (Double));
1129 createCol(regionsettings, "sunvectorz",typeof (Double));
1130 createCol(regionsettings, "fixed_sun", typeof (Int32));
1131 createCol(regionsettings, "sun_position", typeof (Double));
1132 createCol(regionsettings, "covenant", typeof(String));
1133 regionsettings.PrimaryKey = new DataColumn[] { regionsettings.Columns["regionUUID"] };
1134 return regionsettings;
1135 }
1136
1137 /***********************************************************************
1138 *
1139 * Convert between ADO.NET <=> OpenSim Objects
1140 *
1141 * These should be database independant
1142 *
1143 **********************************************************************/
1144
1145 /// <summary>
1146 ///
1147 /// </summary>
1148 /// <param name="row"></param>
1149 /// <returns></returns>
1150 private SceneObjectPart buildPrim(DataRow row)
1151 {
1152 // Code commented. Uncomment to test the unit test inline.
1153
1154 // The unit test mentions this commented code for the purposes
1155 // of debugging a unit test failure
1156
1157 // SceneObjectGroup sog = new SceneObjectGroup();
1158 // SceneObjectPart sop = new SceneObjectPart();
1159 // sop.LocalId = 1;
1160 // sop.Name = "object1";
1161 // sop.Description = "object1";
1162 // sop.Text = "";
1163 // sop.SitName = "";
1164 // sop.TouchName = "";
1165 // sop.UUID = UUID.Random();
1166 // sop.Shape = PrimitiveBaseShape.Default;
1167 // sog.SetRootPart(sop);
1168 // Add breakpoint in above line. Check sop fields.
1169
1170 // TODO: this doesn't work yet because something more
1171 // interesting has to be done to actually get these values
1172 // back out. Not enough time to figure it out yet.
1173
1174 SceneObjectPart prim = new SceneObjectPart();
1175 prim.UUID = new UUID((String) row["UUID"]);
1176 // explicit conversion of integers is required, which sort
1177 // of sucks. No idea if there is a shortcut here or not.
1178 prim.CreationDate = Convert.ToInt32(row["CreationDate"]);
1179 prim.Name = row["Name"] == DBNull.Value ? string.Empty : (string)row["Name"];
1180 // various text fields
1181 prim.Text = (String) row["Text"];
1182 prim.Color = Color.FromArgb(Convert.ToInt32(row["ColorA"]),
1183 Convert.ToInt32(row["ColorR"]),
1184 Convert.ToInt32(row["ColorG"]),
1185 Convert.ToInt32(row["ColorB"]));
1186 prim.Description = (String) row["Description"];
1187 prim.SitName = (String) row["SitName"];
1188 prim.TouchName = (String) row["TouchName"];
1189 // permissions
1190 prim.ObjectFlags = Convert.ToUInt32(row["ObjectFlags"]);
1191 prim.CreatorID = new UUID((String) row["CreatorID"]);
1192 prim.OwnerID = new UUID((String) row["OwnerID"]);
1193 prim.GroupID = new UUID((String) row["GroupID"]);
1194 prim.LastOwnerID = new UUID((String) row["LastOwnerID"]);
1195 prim.OwnerMask = Convert.ToUInt32(row["OwnerMask"]);
1196 prim.NextOwnerMask = Convert.ToUInt32(row["NextOwnerMask"]);
1197 prim.GroupMask = Convert.ToUInt32(row["GroupMask"]);
1198 prim.EveryoneMask = Convert.ToUInt32(row["EveryoneMask"]);
1199 prim.BaseMask = Convert.ToUInt32(row["BaseMask"]);
1200 // vectors
1201 prim.OffsetPosition = new Vector3(
1202 Convert.ToSingle(row["PositionX"]),
1203 Convert.ToSingle(row["PositionY"]),
1204 Convert.ToSingle(row["PositionZ"])
1205 );
1206 prim.GroupPosition = new Vector3(
1207 Convert.ToSingle(row["GroupPositionX"]),
1208 Convert.ToSingle(row["GroupPositionY"]),
1209 Convert.ToSingle(row["GroupPositionZ"])
1210 );
1211 prim.Velocity = new Vector3(
1212 Convert.ToSingle(row["VelocityX"]),
1213 Convert.ToSingle(row["VelocityY"]),
1214 Convert.ToSingle(row["VelocityZ"])
1215 );
1216 prim.AngularVelocity = new Vector3(
1217 Convert.ToSingle(row["AngularVelocityX"]),
1218 Convert.ToSingle(row["AngularVelocityY"]),
1219 Convert.ToSingle(row["AngularVelocityZ"])
1220 );
1221 prim.Acceleration = new Vector3(
1222 Convert.ToSingle(row["AccelerationX"]),
1223 Convert.ToSingle(row["AccelerationY"]),
1224 Convert.ToSingle(row["AccelerationZ"])
1225 );
1226 // quaternions
1227 prim.RotationOffset = new Quaternion(
1228 Convert.ToSingle(row["RotationX"]),
1229 Convert.ToSingle(row["RotationY"]),
1230 Convert.ToSingle(row["RotationZ"]),
1231 Convert.ToSingle(row["RotationW"])
1232 );
1233
1234 prim.SitTargetPositionLL = new Vector3(
1235 Convert.ToSingle(row["SitTargetOffsetX"]),
1236 Convert.ToSingle(row["SitTargetOffsetY"]),
1237 Convert.ToSingle(row["SitTargetOffsetZ"]));
1238 prim.SitTargetOrientationLL = new Quaternion(
1239 Convert.ToSingle(
1240 row["SitTargetOrientX"]),
1241 Convert.ToSingle(
1242 row["SitTargetOrientY"]),
1243 Convert.ToSingle(
1244 row["SitTargetOrientZ"]),
1245 Convert.ToSingle(
1246 row["SitTargetOrientW"]));
1247
1248 prim.ClickAction = Convert.ToByte(row["ClickAction"]);
1249 prim.PayPrice[0] = Convert.ToInt32(row["PayPrice"]);
1250 prim.PayPrice[1] = Convert.ToInt32(row["PayButton1"]);
1251 prim.PayPrice[2] = Convert.ToInt32(row["PayButton2"]);
1252 prim.PayPrice[3] = Convert.ToInt32(row["PayButton3"]);
1253 prim.PayPrice[4] = Convert.ToInt32(row["PayButton4"]);
1254
1255 prim.Sound = new UUID(row["LoopedSound"].ToString());
1256 prim.SoundGain = Convert.ToSingle(row["LoopedSoundGain"]);
1257 prim.SoundFlags = 1; // If it's persisted at all, it's looped
1258
1259 if (!row.IsNull("TextureAnimation"))
1260 prim.TextureAnimation = Convert.FromBase64String(row["TextureAnimation"].ToString());
1261 if (!row.IsNull("ParticleSystem"))
1262 prim.ParticleSystem = Convert.FromBase64String(row["ParticleSystem"].ToString());
1263
1264 prim.AngularVelocity = new Vector3(
1265 Convert.ToSingle(row["OmegaX"]),
1266 Convert.ToSingle(row["OmegaY"]),
1267 Convert.ToSingle(row["OmegaZ"])
1268 );
1269
1270 prim.SetCameraEyeOffset(new Vector3(
1271 Convert.ToSingle(row["CameraEyeOffsetX"]),
1272 Convert.ToSingle(row["CameraEyeOffsetY"]),
1273 Convert.ToSingle(row["CameraEyeOffsetZ"])
1274 ));
1275
1276 prim.SetCameraAtOffset(new Vector3(
1277 Convert.ToSingle(row["CameraAtOffsetX"]),
1278 Convert.ToSingle(row["CameraAtOffsetY"]),
1279 Convert.ToSingle(row["CameraAtOffsetZ"])
1280 ));
1281
1282 if (Convert.ToInt16(row["ForceMouselook"]) != 0)
1283 prim.SetForceMouselook(true);
1284
1285 prim.ScriptAccessPin = Convert.ToInt32(row["ScriptAccessPin"]);
1286
1287 if (Convert.ToInt16(row["AllowedDrop"]) != 0)
1288 prim.AllowedDrop = true;
1289
1290 if (Convert.ToInt16(row["DieAtEdge"]) != 0)
1291 prim.DIE_AT_EDGE = true;
1292
1293 prim.SalePrice = Convert.ToInt32(row["SalePrice"]);
1294 prim.ObjectSaleType = Convert.ToByte(row["SaleType"]);
1295
1296 prim.Material = Convert.ToByte(row["Material"]);
1297
1298 prim.CollisionSound = new UUID(row["CollisionSound"].ToString());
1299 prim.CollisionSoundVolume = Convert.ToSingle(row["CollisionSoundVolume"]);
1300
1301 if (Convert.ToInt16(row["VolumeDetect"]) != 0)
1302 prim.VolumeDetectActive = true;
1303
1304 return prim;
1305 }
1306
1307 /// <summary>
1308 /// Build a prim inventory item from the persisted data.
1309 /// </summary>
1310 /// <param name="row"></param>
1311 /// <returns></returns>
1312 private static TaskInventoryItem buildItem(DataRow row)
1313 {
1314 TaskInventoryItem taskItem = new TaskInventoryItem();
1315
1316 taskItem.ItemID = new UUID((String)row["itemID"]);
1317 taskItem.ParentPartID = new UUID((String)row["primID"]);
1318 taskItem.AssetID = new UUID((String)row["assetID"]);
1319 taskItem.ParentID = new UUID((String)row["parentFolderID"]);
1320
1321 taskItem.InvType = Convert.ToInt32(row["invType"]);
1322 taskItem.Type = Convert.ToInt32(row["assetType"]);
1323
1324 taskItem.Name = (String)row["name"];
1325 taskItem.Description = (String)row["description"];
1326 taskItem.CreationDate = Convert.ToUInt32(row["creationDate"]);
1327 taskItem.CreatorID = new UUID((String)row["creatorID"]);
1328 taskItem.OwnerID = new UUID((String)row["ownerID"]);
1329 taskItem.LastOwnerID = new UUID((String)row["lastOwnerID"]);
1330 taskItem.GroupID = new UUID((String)row["groupID"]);
1331
1332 taskItem.NextPermissions = Convert.ToUInt32(row["nextPermissions"]);
1333 taskItem.CurrentPermissions = Convert.ToUInt32(row["currentPermissions"]);
1334 taskItem.BasePermissions = Convert.ToUInt32(row["basePermissions"]);
1335 taskItem.EveryonePermissions = Convert.ToUInt32(row["everyonePermissions"]);
1336 taskItem.GroupPermissions = Convert.ToUInt32(row["groupPermissions"]);
1337 taskItem.Flags = Convert.ToUInt32(row["flags"]);
1338
1339 return taskItem;
1340 }
1341
1342 /// <summary>
1343 /// Build a Land Data from the persisted data.
1344 /// </summary>
1345 /// <param name="row"></param>
1346 /// <returns></returns>
1347 private LandData buildLandData(DataRow row)
1348 {
1349 LandData newData = new LandData();
1350
1351 newData.GlobalID = new UUID((String) row["UUID"]);
1352 newData.LocalID = Convert.ToInt32(row["LocalLandID"]);
1353
1354 // Bitmap is a byte[512]
1355 newData.Bitmap = (Byte[]) row["Bitmap"];
1356
1357 newData.Name = (String) row["Name"];
1358 newData.Description = (String) row["Desc"];
1359 newData.OwnerID = (UUID)(String) row["OwnerUUID"];
1360 newData.IsGroupOwned = (Boolean) row["IsGroupOwned"];
1361 newData.Area = Convert.ToInt32(row["Area"]);
1362 newData.AuctionID = Convert.ToUInt32(row["AuctionID"]); //Unemplemented
1363 newData.Category = (ParcelCategory) Convert.ToInt32(row["Category"]);
1364 //Enum OpenMetaverse.Parcel.ParcelCategory
1365 newData.ClaimDate = Convert.ToInt32(row["ClaimDate"]);
1366 newData.ClaimPrice = Convert.ToInt32(row["ClaimPrice"]);
1367 newData.GroupID = new UUID((String) row["GroupUUID"]);
1368 newData.SalePrice = Convert.ToInt32(row["SalePrice"]);
1369 newData.Status = (ParcelStatus) Convert.ToInt32(row["LandStatus"]);
1370 //Enum. OpenMetaverse.Parcel.ParcelStatus
1371 newData.Flags = Convert.ToUInt32(row["LandFlags"]);
1372 newData.LandingType = (Byte) row["LandingType"];
1373 newData.MediaAutoScale = (Byte) row["MediaAutoScale"];
1374 newData.MediaID = new UUID((String) row["MediaTextureUUID"]);
1375 newData.MediaURL = (String) row["MediaURL"];
1376 newData.MusicURL = (String) row["MusicURL"];
1377 newData.PassHours = Convert.ToSingle(row["PassHours"]);
1378 newData.PassPrice = Convert.ToInt32(row["PassPrice"]);
1379 newData.SnapshotID = (UUID)(String) row["SnapshotUUID"];
1380 try
1381 {
1382
1383 newData.UserLocation =
1384 new Vector3(Convert.ToSingle(row["UserLocationX"]), Convert.ToSingle(row["UserLocationY"]),
1385 Convert.ToSingle(row["UserLocationZ"]));
1386 newData.UserLookAt =
1387 new Vector3(Convert.ToSingle(row["UserLookAtX"]), Convert.ToSingle(row["UserLookAtY"]),
1388 Convert.ToSingle(row["UserLookAtZ"]));
1389
1390 }
1391 catch (InvalidCastException)
1392 {
1393 m_log.ErrorFormat("[PARCEL]: unable to get parcel telehub settings for {1}", newData.Name);
1394 newData.UserLocation = Vector3.Zero;
1395 newData.UserLookAt = Vector3.Zero;
1396 }
1397 newData.ParcelAccessList = new List<ParcelManager.ParcelAccessEntry>();
1398 UUID authBuyerID = UUID.Zero;
1399
1400 UUID.TryParse((string)row["AuthbuyerID"], out authBuyerID);
1401
1402 newData.OtherCleanTime = Convert.ToInt32(row["OtherCleanTime"]);
1403 newData.Dwell = Convert.ToInt32(row["Dwell"]);
1404
1405 return newData;
1406 }
1407
1408 private RegionSettings buildRegionSettings(DataRow row)
1409 {
1410 RegionSettings newSettings = new RegionSettings();
1411
1412 newSettings.RegionUUID = new UUID((string) row["regionUUID"]);
1413 newSettings.BlockTerraform = Convert.ToBoolean(row["block_terraform"]);
1414 newSettings.AllowDamage = Convert.ToBoolean(row["allow_damage"]);
1415 newSettings.BlockFly = Convert.ToBoolean(row["block_fly"]);
1416 newSettings.RestrictPushing = Convert.ToBoolean(row["restrict_pushing"]);
1417 newSettings.AllowLandResell = Convert.ToBoolean(row["allow_land_resell"]);
1418 newSettings.AllowLandJoinDivide = Convert.ToBoolean(row["allow_land_join_divide"]);
1419 newSettings.BlockShowInSearch = Convert.ToBoolean(row["block_show_in_search"]);
1420 newSettings.AgentLimit = Convert.ToInt32(row["agent_limit"]);
1421 newSettings.ObjectBonus = Convert.ToDouble(row["object_bonus"]);
1422 newSettings.Maturity = Convert.ToInt32(row["maturity"]);
1423 newSettings.DisableScripts = Convert.ToBoolean(row["disable_scripts"]);
1424 newSettings.DisableCollisions = Convert.ToBoolean(row["disable_collisions"]);
1425 newSettings.DisablePhysics = Convert.ToBoolean(row["disable_physics"]);
1426 newSettings.TerrainTexture1 = new UUID((String) row["terrain_texture_1"]);
1427 newSettings.TerrainTexture2 = new UUID((String) row["terrain_texture_2"]);
1428 newSettings.TerrainTexture3 = new UUID((String) row["terrain_texture_3"]);
1429 newSettings.TerrainTexture4 = new UUID((String) row["terrain_texture_4"]);
1430 newSettings.Elevation1NW = Convert.ToDouble(row["elevation_1_nw"]);
1431 newSettings.Elevation2NW = Convert.ToDouble(row["elevation_2_nw"]);
1432 newSettings.Elevation1NE = Convert.ToDouble(row["elevation_1_ne"]);
1433 newSettings.Elevation2NE = Convert.ToDouble(row["elevation_2_ne"]);
1434 newSettings.Elevation1SE = Convert.ToDouble(row["elevation_1_se"]);
1435 newSettings.Elevation2SE = Convert.ToDouble(row["elevation_2_se"]);
1436 newSettings.Elevation1SW = Convert.ToDouble(row["elevation_1_sw"]);
1437 newSettings.Elevation2SW = Convert.ToDouble(row["elevation_2_sw"]);
1438 newSettings.WaterHeight = Convert.ToDouble(row["water_height"]);
1439 newSettings.TerrainRaiseLimit = Convert.ToDouble(row["terrain_raise_limit"]);
1440 newSettings.TerrainLowerLimit = Convert.ToDouble(row["terrain_lower_limit"]);
1441 newSettings.UseEstateSun = Convert.ToBoolean(row["use_estate_sun"]);
1442 newSettings.Sandbox = Convert.ToBoolean(row["sandbox"]);
1443 newSettings.SunVector = new Vector3 (
1444 Convert.ToSingle(row["sunvectorx"]),
1445 Convert.ToSingle(row["sunvectory"]),
1446 Convert.ToSingle(row["sunvectorz"])
1447 );
1448 newSettings.FixedSun = Convert.ToBoolean(row["fixed_sun"]);
1449 newSettings.SunPosition = Convert.ToDouble(row["sun_position"]);
1450 newSettings.Covenant = new UUID((String) row["covenant"]);
1451
1452 return newSettings;
1453 }
1454
1455 /// <summary>
1456 /// Build a land access entry from the persisted data.
1457 /// </summary>
1458 /// <param name="row"></param>
1459 /// <returns></returns>
1460 private static ParcelManager.ParcelAccessEntry buildLandAccessData(DataRow row)
1461 {
1462 ParcelManager.ParcelAccessEntry entry = new ParcelManager.ParcelAccessEntry();
1463 entry.AgentID = new UUID((string) row["AccessUUID"]);
1464 entry.Flags = (AccessList) row["Flags"];
1465 entry.Time = new DateTime();
1466 return entry;
1467 }
1468
1469 /// <summary>
1470 ///
1471 /// </summary>
1472 /// <param name="val"></param>
1473 /// <returns></returns>
1474 private static Array serializeTerrain(double[,] val)
1475 {
1476 MemoryStream str = new MemoryStream(((int)Constants.RegionSize * (int)Constants.RegionSize) *sizeof (double));
1477 BinaryWriter bw = new BinaryWriter(str);
1478
1479 // TODO: COMPATIBILITY - Add byte-order conversions
1480 for (int x = 0; x < (int)Constants.RegionSize; x++)
1481 for (int y = 0; y < (int)Constants.RegionSize; y++)
1482 bw.Write(val[x, y]);
1483
1484 return str.ToArray();
1485 }
1486
1487// private void fillTerrainRow(DataRow row, UUID regionUUID, int rev, double[,] val)
1488// {
1489// row["RegionUUID"] = regionUUID;
1490// row["Revision"] = rev;
1491
1492 // MemoryStream str = new MemoryStream(((int)Constants.RegionSize * (int)Constants.RegionSize)*sizeof (double));
1493// BinaryWriter bw = new BinaryWriter(str);
1494
1495// // TODO: COMPATIBILITY - Add byte-order conversions
1496 // for (int x = 0; x < (int)Constants.RegionSize; x++)
1497 // for (int y = 0; y < (int)Constants.RegionSize; y++)
1498// bw.Write(val[x, y]);
1499
1500// row["Heightfield"] = str.ToArray();
1501// }
1502
1503 /// <summary>
1504 ///
1505 /// </summary>
1506 /// <param name="row"></param>
1507 /// <param name="prim"></param>
1508 /// <param name="sceneGroupID"></param>
1509 /// <param name="regionUUID"></param>
1510 private static void fillPrimRow(DataRow row, SceneObjectPart prim, UUID sceneGroupID, UUID regionUUID)
1511 {
1512 row["UUID"] = prim.UUID.ToString();
1513 row["RegionUUID"] = regionUUID.ToString();
1514 row["CreationDate"] = prim.CreationDate;
1515 row["Name"] = prim.Name;
1516 row["SceneGroupID"] = sceneGroupID.ToString();
1517 // the UUID of the root part for this SceneObjectGroup
1518 // various text fields
1519 row["Text"] = prim.Text;
1520 row["Description"] = prim.Description;
1521 row["SitName"] = prim.SitName;
1522 row["TouchName"] = prim.TouchName;
1523 // permissions
1524 row["ObjectFlags"] = prim.ObjectFlags;
1525 row["CreatorID"] = prim.CreatorID.ToString();
1526 row["OwnerID"] = prim.OwnerID.ToString();
1527 row["GroupID"] = prim.GroupID.ToString();
1528 row["LastOwnerID"] = prim.LastOwnerID.ToString();
1529 row["OwnerMask"] = prim.OwnerMask;
1530 row["NextOwnerMask"] = prim.NextOwnerMask;
1531 row["GroupMask"] = prim.GroupMask;
1532 row["EveryoneMask"] = prim.EveryoneMask;
1533 row["BaseMask"] = prim.BaseMask;
1534 // vectors
1535 row["PositionX"] = prim.OffsetPosition.X;
1536 row["PositionY"] = prim.OffsetPosition.Y;
1537 row["PositionZ"] = prim.OffsetPosition.Z;
1538 row["GroupPositionX"] = prim.GroupPosition.X;
1539 row["GroupPositionY"] = prim.GroupPosition.Y;
1540 row["GroupPositionZ"] = prim.GroupPosition.Z;
1541 row["VelocityX"] = prim.Velocity.X;
1542 row["VelocityY"] = prim.Velocity.Y;
1543 row["VelocityZ"] = prim.Velocity.Z;
1544 row["AngularVelocityX"] = prim.AngularVelocity.X;
1545 row["AngularVelocityY"] = prim.AngularVelocity.Y;
1546 row["AngularVelocityZ"] = prim.AngularVelocity.Z;
1547 row["AccelerationX"] = prim.Acceleration.X;
1548 row["AccelerationY"] = prim.Acceleration.Y;
1549 row["AccelerationZ"] = prim.Acceleration.Z;
1550 // quaternions
1551 row["RotationX"] = prim.RotationOffset.X;
1552 row["RotationY"] = prim.RotationOffset.Y;
1553 row["RotationZ"] = prim.RotationOffset.Z;
1554 row["RotationW"] = prim.RotationOffset.W;
1555
1556 // Sit target
1557 Vector3 sitTargetPos = prim.SitTargetPositionLL;
1558 row["SitTargetOffsetX"] = sitTargetPos.X;
1559 row["SitTargetOffsetY"] = sitTargetPos.Y;
1560 row["SitTargetOffsetZ"] = sitTargetPos.Z;
1561
1562 Quaternion sitTargetOrient = prim.SitTargetOrientationLL;
1563 row["SitTargetOrientW"] = sitTargetOrient.W;
1564 row["SitTargetOrientX"] = sitTargetOrient.X;
1565 row["SitTargetOrientY"] = sitTargetOrient.Y;
1566 row["SitTargetOrientZ"] = sitTargetOrient.Z;
1567 row["ColorR"] = Convert.ToInt32(prim.Color.R);
1568 row["ColorG"] = Convert.ToInt32(prim.Color.G);
1569 row["ColorB"] = Convert.ToInt32(prim.Color.B);
1570 row["ColorA"] = Convert.ToInt32(prim.Color.A);
1571 row["PayPrice"] = prim.PayPrice[0];
1572 row["PayButton1"] = prim.PayPrice[1];
1573 row["PayButton2"] = prim.PayPrice[2];
1574 row["PayButton3"] = prim.PayPrice[3];
1575 row["PayButton4"] = prim.PayPrice[4];
1576
1577
1578 row["TextureAnimation"] = Convert.ToBase64String(prim.TextureAnimation);
1579 row["ParticleSystem"] = Convert.ToBase64String(prim.ParticleSystem);
1580
1581 row["OmegaX"] = prim.AngularVelocity.X;
1582 row["OmegaY"] = prim.AngularVelocity.Y;
1583 row["OmegaZ"] = prim.AngularVelocity.Z;
1584
1585 row["CameraEyeOffsetX"] = prim.GetCameraEyeOffset().X;
1586 row["CameraEyeOffsetY"] = prim.GetCameraEyeOffset().Y;
1587 row["CameraEyeOffsetZ"] = prim.GetCameraEyeOffset().Z;
1588
1589 row["CameraAtOffsetX"] = prim.GetCameraAtOffset().X;
1590 row["CameraAtOffsetY"] = prim.GetCameraAtOffset().Y;
1591 row["CameraAtOffsetZ"] = prim.GetCameraAtOffset().Z;
1592
1593
1594 if ((prim.SoundFlags & 1) != 0) // Looped
1595 {
1596 row["LoopedSound"] = prim.Sound.ToString();
1597 row["LoopedSoundGain"] = prim.SoundGain;
1598 }
1599 else
1600 {
1601 row["LoopedSound"] = UUID.Zero.ToString();
1602 row["LoopedSoundGain"] = 0.0f;
1603 }
1604
1605 if (prim.GetForceMouselook())
1606 row["ForceMouselook"] = 1;
1607 else
1608 row["ForceMouselook"] = 0;
1609
1610 row["ScriptAccessPin"] = prim.ScriptAccessPin;
1611
1612 if (prim.AllowedDrop)
1613 row["AllowedDrop"] = 1;
1614 else
1615 row["AllowedDrop"] = 0;
1616
1617 if (prim.DIE_AT_EDGE)
1618 row["DieAtEdge"] = 1;
1619 else
1620 row["DieAtEdge"] = 0;
1621
1622 row["SalePrice"] = prim.SalePrice;
1623 row["SaleType"] = Convert.ToInt16(prim.ObjectSaleType);
1624
1625 // click action
1626 row["ClickAction"] = prim.ClickAction;
1627
1628 row["SalePrice"] = prim.SalePrice;
1629 row["Material"] = prim.Material;
1630
1631 row["CollisionSound"] = prim.CollisionSound.ToString();
1632 row["CollisionSoundVolume"] = prim.CollisionSoundVolume;
1633 if (prim.VolumeDetectActive)
1634 row["VolumeDetect"] = 1;
1635 else
1636 row["VolumeDetect"] = 0;
1637
1638 }
1639
1640 /// <summary>
1641 ///
1642 /// </summary>
1643 /// <param name="row"></param>
1644 /// <param name="taskItem"></param>
1645 private static void fillItemRow(DataRow row, TaskInventoryItem taskItem)
1646 {
1647 row["itemID"] = taskItem.ItemID.ToString();
1648 row["primID"] = taskItem.ParentPartID.ToString();
1649 row["assetID"] = taskItem.AssetID.ToString();
1650 row["parentFolderID"] = taskItem.ParentID.ToString();
1651
1652 row["invType"] = taskItem.InvType;
1653 row["assetType"] = taskItem.Type;
1654
1655 row["name"] = taskItem.Name;
1656 row["description"] = taskItem.Description;
1657 row["creationDate"] = taskItem.CreationDate;
1658 row["creatorID"] = taskItem.CreatorID.ToString();
1659 row["ownerID"] = taskItem.OwnerID.ToString();
1660 row["lastOwnerID"] = taskItem.LastOwnerID.ToString();
1661 row["groupID"] = taskItem.GroupID.ToString();
1662 row["nextPermissions"] = taskItem.NextPermissions;
1663 row["currentPermissions"] = taskItem.CurrentPermissions;
1664 row["basePermissions"] = taskItem.BasePermissions;
1665 row["everyonePermissions"] = taskItem.EveryonePermissions;
1666 row["groupPermissions"] = taskItem.GroupPermissions;
1667 row["flags"] = taskItem.Flags;
1668 }
1669
1670 /// <summary>
1671 ///
1672 /// </summary>
1673 /// <param name="row"></param>
1674 /// <param name="land"></param>
1675 /// <param name="regionUUID"></param>
1676 private static void fillLandRow(DataRow row, LandData land, UUID regionUUID)
1677 {
1678 row["UUID"] = land.GlobalID.ToString();
1679 row["RegionUUID"] = regionUUID.ToString();
1680 row["LocalLandID"] = land.LocalID;
1681
1682 // Bitmap is a byte[512]
1683 row["Bitmap"] = land.Bitmap;
1684
1685 row["Name"] = land.Name;
1686 row["Desc"] = land.Description;
1687 row["OwnerUUID"] = land.OwnerID.ToString();
1688 row["IsGroupOwned"] = land.IsGroupOwned;
1689 row["Area"] = land.Area;
1690 row["AuctionID"] = land.AuctionID; //Unemplemented
1691 row["Category"] = land.Category; //Enum OpenMetaverse.Parcel.ParcelCategory
1692 row["ClaimDate"] = land.ClaimDate;
1693 row["ClaimPrice"] = land.ClaimPrice;
1694 row["GroupUUID"] = land.GroupID.ToString();
1695 row["SalePrice"] = land.SalePrice;
1696 row["LandStatus"] = land.Status; //Enum. OpenMetaverse.Parcel.ParcelStatus
1697 row["LandFlags"] = land.Flags;
1698 row["LandingType"] = land.LandingType;
1699 row["MediaAutoScale"] = land.MediaAutoScale;
1700 row["MediaTextureUUID"] = land.MediaID.ToString();
1701 row["MediaURL"] = land.MediaURL;
1702 row["MusicURL"] = land.MusicURL;
1703 row["PassHours"] = land.PassHours;
1704 row["PassPrice"] = land.PassPrice;
1705 row["SnapshotUUID"] = land.SnapshotID.ToString();
1706 row["UserLocationX"] = land.UserLocation.X;
1707 row["UserLocationY"] = land.UserLocation.Y;
1708 row["UserLocationZ"] = land.UserLocation.Z;
1709 row["UserLookAtX"] = land.UserLookAt.X;
1710 row["UserLookAtY"] = land.UserLookAt.Y;
1711 row["UserLookAtZ"] = land.UserLookAt.Z;
1712 row["AuthbuyerID"] = land.AuthBuyerID.ToString();
1713 row["OtherCleanTime"] = land.OtherCleanTime;
1714 row["Dwell"] = land.Dwell;
1715 }
1716
1717 /// <summary>
1718 ///
1719 /// </summary>
1720 /// <param name="row"></param>
1721 /// <param name="entry"></param>
1722 /// <param name="parcelID"></param>
1723 private static void fillLandAccessRow(DataRow row, ParcelManager.ParcelAccessEntry entry, UUID parcelID)
1724 {
1725 row["LandUUID"] = parcelID.ToString();
1726 row["AccessUUID"] = entry.AgentID.ToString();
1727 row["Flags"] = entry.Flags;
1728 }
1729
1730 private static void fillRegionSettingsRow(DataRow row, RegionSettings settings)
1731 {
1732 row["regionUUID"] = settings.RegionUUID.ToString();
1733 row["block_terraform"] = settings.BlockTerraform;
1734 row["block_fly"] = settings.BlockFly;
1735 row["allow_damage"] = settings.AllowDamage;
1736 row["restrict_pushing"] = settings.RestrictPushing;
1737 row["allow_land_resell"] = settings.AllowLandResell;
1738 row["allow_land_join_divide"] = settings.AllowLandJoinDivide;
1739 row["block_show_in_search"] = settings.BlockShowInSearch;
1740 row["agent_limit"] = settings.AgentLimit;
1741 row["object_bonus"] = settings.ObjectBonus;
1742 row["maturity"] = settings.Maturity;
1743 row["disable_scripts"] = settings.DisableScripts;
1744 row["disable_collisions"] = settings.DisableCollisions;
1745 row["disable_physics"] = settings.DisablePhysics;
1746 row["terrain_texture_1"] = settings.TerrainTexture1.ToString();
1747 row["terrain_texture_2"] = settings.TerrainTexture2.ToString();
1748 row["terrain_texture_3"] = settings.TerrainTexture3.ToString();
1749 row["terrain_texture_4"] = settings.TerrainTexture4.ToString();
1750 row["elevation_1_nw"] = settings.Elevation1NW;
1751 row["elevation_2_nw"] = settings.Elevation2NW;
1752 row["elevation_1_ne"] = settings.Elevation1NE;
1753 row["elevation_2_ne"] = settings.Elevation2NE;
1754 row["elevation_1_se"] = settings.Elevation1SE;
1755 row["elevation_2_se"] = settings.Elevation2SE;
1756 row["elevation_1_sw"] = settings.Elevation1SW;
1757 row["elevation_2_sw"] = settings.Elevation2SW;
1758 row["water_height"] = settings.WaterHeight;
1759 row["terrain_raise_limit"] = settings.TerrainRaiseLimit;
1760 row["terrain_lower_limit"] = settings.TerrainLowerLimit;
1761 row["use_estate_sun"] = settings.UseEstateSun;
1762 row["Sandbox"] = settings.Sandbox; // database uses upper case S for sandbox
1763 row["sunvectorx"] = settings.SunVector.X;
1764 row["sunvectory"] = settings.SunVector.Y;
1765 row["sunvectorz"] = settings.SunVector.Z;
1766 row["fixed_sun"] = settings.FixedSun;
1767 row["sun_position"] = settings.SunPosition;
1768 row["covenant"] = settings.Covenant.ToString();
1769 }
1770
1771 /// <summary>
1772 ///
1773 /// </summary>
1774 /// <param name="row"></param>
1775 /// <returns></returns>
1776 private PrimitiveBaseShape buildShape(DataRow row)
1777 {
1778 PrimitiveBaseShape s = new PrimitiveBaseShape();
1779 s.Scale = new Vector3(
1780 Convert.ToSingle(row["ScaleX"]),
1781 Convert.ToSingle(row["ScaleY"]),
1782 Convert.ToSingle(row["ScaleZ"])
1783 );
1784 // paths
1785 s.PCode = Convert.ToByte(row["PCode"]);
1786 s.PathBegin = Convert.ToUInt16(row["PathBegin"]);
1787 s.PathEnd = Convert.ToUInt16(row["PathEnd"]);
1788 s.PathScaleX = Convert.ToByte(row["PathScaleX"]);
1789 s.PathScaleY = Convert.ToByte(row["PathScaleY"]);
1790 s.PathShearX = Convert.ToByte(row["PathShearX"]);
1791 s.PathShearY = Convert.ToByte(row["PathShearY"]);
1792 s.PathSkew = Convert.ToSByte(row["PathSkew"]);
1793 s.PathCurve = Convert.ToByte(row["PathCurve"]);
1794 s.PathRadiusOffset = Convert.ToSByte(row["PathRadiusOffset"]);
1795 s.PathRevolutions = Convert.ToByte(row["PathRevolutions"]);
1796 s.PathTaperX = Convert.ToSByte(row["PathTaperX"]);
1797 s.PathTaperY = Convert.ToSByte(row["PathTaperY"]);
1798 s.PathTwist = Convert.ToSByte(row["PathTwist"]);
1799 s.PathTwistBegin = Convert.ToSByte(row["PathTwistBegin"]);
1800 // profile
1801 s.ProfileBegin = Convert.ToUInt16(row["ProfileBegin"]);
1802 s.ProfileEnd = Convert.ToUInt16(row["ProfileEnd"]);
1803 s.ProfileCurve = Convert.ToByte(row["ProfileCurve"]);
1804 s.ProfileHollow = Convert.ToUInt16(row["ProfileHollow"]);
1805 s.State = Convert.ToByte(row["State"]);
1806
1807 byte[] textureEntry = (byte[])row["Texture"];
1808 s.TextureEntry = textureEntry;
1809
1810 s.ExtraParams = (byte[]) row["ExtraParams"];
1811 return s;
1812 }
1813
1814 /// <summary>
1815 ///
1816 /// </summary>
1817 /// <param name="row"></param>
1818 /// <param name="prim"></param>
1819 private static void fillShapeRow(DataRow row, SceneObjectPart prim)
1820 {
1821 PrimitiveBaseShape s = prim.Shape;
1822 row["UUID"] = prim.UUID.ToString();
1823 // shape is an enum
1824 row["Shape"] = 0;
1825 // vectors
1826 row["ScaleX"] = s.Scale.X;
1827 row["ScaleY"] = s.Scale.Y;
1828 row["ScaleZ"] = s.Scale.Z;
1829 // paths
1830 row["PCode"] = s.PCode;
1831 row["PathBegin"] = s.PathBegin;
1832 row["PathEnd"] = s.PathEnd;
1833 row["PathScaleX"] = s.PathScaleX;
1834 row["PathScaleY"] = s.PathScaleY;
1835 row["PathShearX"] = s.PathShearX;
1836 row["PathShearY"] = s.PathShearY;
1837 row["PathSkew"] = s.PathSkew;
1838 row["PathCurve"] = s.PathCurve;
1839 row["PathRadiusOffset"] = s.PathRadiusOffset;
1840 row["PathRevolutions"] = s.PathRevolutions;
1841 row["PathTaperX"] = s.PathTaperX;
1842 row["PathTaperY"] = s.PathTaperY;
1843 row["PathTwist"] = s.PathTwist;
1844 row["PathTwistBegin"] = s.PathTwistBegin;
1845 // profile
1846 row["ProfileBegin"] = s.ProfileBegin;
1847 row["ProfileEnd"] = s.ProfileEnd;
1848 row["ProfileCurve"] = s.ProfileCurve;
1849 row["ProfileHollow"] = s.ProfileHollow;
1850 row["State"] = s.State;
1851
1852 row["Texture"] = s.TextureEntry;
1853 row["ExtraParams"] = s.ExtraParams;
1854 }
1855
1856 /// <summary>
1857 ///
1858 /// </summary>
1859 /// <param name="prim"></param>
1860 /// <param name="sceneGroupID"></param>
1861 /// <param name="regionUUID"></param>
1862 private void addPrim(SceneObjectPart prim, UUID sceneGroupID, UUID regionUUID)
1863 {
1864
1865 DataTable prims = ds.Tables["prims"];
1866 DataTable shapes = ds.Tables["primshapes"];
1867
1868 DataRow primRow = prims.Rows.Find(prim.UUID.ToString());
1869 if (primRow == null)
1870 {
1871 primRow = prims.NewRow();
1872 fillPrimRow(primRow, prim, sceneGroupID, regionUUID);
1873 prims.Rows.Add(primRow);
1874 }
1875 else
1876 {
1877 fillPrimRow(primRow, prim, sceneGroupID, regionUUID);
1878 }
1879
1880 DataRow shapeRow = shapes.Rows.Find(prim.UUID.ToString());
1881 if (shapeRow == null)
1882 {
1883 shapeRow = shapes.NewRow();
1884 fillShapeRow(shapeRow, prim);
1885 shapes.Rows.Add(shapeRow);
1886 }
1887 else
1888 {
1889 fillShapeRow(shapeRow, prim);
1890 }
1891 }
1892
1893 /// <summary>
1894 /// see IRegionDatastore
1895 /// </summary>
1896 /// <param name="primID"></param>
1897 /// <param name="items"></param>
1898 public void StorePrimInventory(UUID primID, ICollection<TaskInventoryItem> items)
1899 {
1900 m_log.InfoFormat("[REGION DB]: Entered StorePrimInventory with prim ID {0}", primID);
1901
1902 DataTable dbItems = ds.Tables["primitems"];
1903
1904 // For now, we're just going to crudely remove all the previous inventory items
1905 // no matter whether they have changed or not, and replace them with the current set.
1906 lock (ds)
1907 {
1908 RemoveItems(primID);
1909
1910 // repalce with current inventory details
1911 foreach (TaskInventoryItem newItem in items)
1912 {
1913// m_log.InfoFormat(
1914// "[DATASTORE]: ",
1915// "Adding item {0}, {1} to prim ID {2}",
1916// newItem.Name, newItem.ItemID, newItem.ParentPartID);
1917
1918 DataRow newItemRow = dbItems.NewRow();
1919 fillItemRow(newItemRow, newItem);
1920 dbItems.Rows.Add(newItemRow);
1921 }
1922 }
1923
1924 Commit();
1925 }
1926
1927 /***********************************************************************
1928 *
1929 * SQL Statement Creation Functions
1930 *
1931 * These functions create SQL statements for update, insert, and create.
1932 * They can probably be factored later to have a db independant
1933 * portion and a db specific portion
1934 *
1935 **********************************************************************/
1936
1937 /// <summary>
1938 /// Create an insert command
1939 /// </summary>
1940 /// <param name="table">table name</param>
1941 /// <param name="dt">data table</param>
1942 /// <returns>the created command</returns>
1943 /// <remarks>
1944 /// This is subtle enough to deserve some commentary.
1945 /// Instead of doing *lots* and *lots of hardcoded strings
1946 /// for database definitions we'll use the fact that
1947 /// realistically all insert statements look like "insert
1948 /// into A(b, c) values(:b, :c) on the parameterized query
1949 /// front. If we just have a list of b, c, etc... we can
1950 /// generate these strings instead of typing them out.
1951 /// </remarks>
1952 private static SqliteCommand createInsertCommand(string table, DataTable dt)
1953 {
1954 string[] cols = new string[dt.Columns.Count];
1955 for (int i = 0; i < dt.Columns.Count; i++)
1956 {
1957 DataColumn col = dt.Columns[i];
1958 cols[i] = col.ColumnName;
1959 }
1960
1961 string sql = "insert into " + table + "(";
1962 sql += String.Join(", ", cols);
1963 // important, the first ':' needs to be here, the rest get added in the join
1964 sql += ") values (:";
1965 sql += String.Join(", :", cols);
1966 sql += ")";
1967 SqliteCommand cmd = new SqliteCommand(sql);
1968
1969 // this provides the binding for all our parameters, so
1970 // much less code than it used to be
1971 foreach (DataColumn col in dt.Columns)
1972 {
1973 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
1974 }
1975 return cmd;
1976 }
1977
1978
1979 /// <summary>
1980 /// create an update command
1981 /// </summary>
1982 /// <param name="table">table name</param>
1983 /// <param name="pk"></param>
1984 /// <param name="dt"></param>
1985 /// <returns>the created command</returns>
1986 private static SqliteCommand createUpdateCommand(string table, string pk, DataTable dt)
1987 {
1988 string sql = "update " + table + " set ";
1989 string subsql = String.Empty;
1990 foreach (DataColumn col in dt.Columns)
1991 {
1992 if (subsql.Length > 0)
1993 {
1994 // a map function would rock so much here
1995 subsql += ", ";
1996 }
1997 subsql += col.ColumnName + "= :" + col.ColumnName;
1998 }
1999 sql += subsql;
2000 sql += " where " + pk;
2001 SqliteCommand cmd = new SqliteCommand(sql);
2002
2003 // this provides the binding for all our parameters, so
2004 // much less code than it used to be
2005
2006 foreach (DataColumn col in dt.Columns)
2007 {
2008 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
2009 }
2010 return cmd;
2011 }
2012
2013 /// <summary>
2014 /// create an update command
2015 /// </summary>
2016 /// <param name="table">table name</param>
2017 /// <param name="pk"></param>
2018 /// <param name="dt"></param>
2019 /// <returns>the created command</returns>
2020 private static SqliteCommand createUpdateCommand(string table, string pk1, string pk2, DataTable dt)
2021 {
2022 string sql = "update " + table + " set ";
2023 string subsql = String.Empty;
2024 foreach (DataColumn col in dt.Columns)
2025 {
2026 if (subsql.Length > 0)
2027 {
2028 // a map function would rock so much here
2029 subsql += ", ";
2030 }
2031 subsql += col.ColumnName + "= :" + col.ColumnName;
2032 }
2033 sql += subsql;
2034 sql += " where " + pk1 + " and " + pk2;
2035 SqliteCommand cmd = new SqliteCommand(sql);
2036
2037 // this provides the binding for all our parameters, so
2038 // much less code than it used to be
2039
2040 foreach (DataColumn col in dt.Columns)
2041 {
2042 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
2043 }
2044 return cmd;
2045 }
2046
2047 /// <summary>
2048 ///
2049 /// </summary>
2050 /// <param name="dt">Data Table</param>
2051 /// <returns></returns>
2052 // private static string defineTable(DataTable dt)
2053 // {
2054 // string sql = "create table " + dt.TableName + "(";
2055 // string subsql = String.Empty;
2056 // foreach (DataColumn col in dt.Columns)
2057 // {
2058 // if (subsql.Length > 0)
2059 // {
2060 // // a map function would rock so much here
2061 // subsql += ",\n";
2062 // }
2063 // subsql += col.ColumnName + " " + sqliteType(col.DataType);
2064 // if (dt.PrimaryKey.Length > 0 && col == dt.PrimaryKey[0])
2065 // {
2066 // subsql += " primary key";
2067 // }
2068 // }
2069 // sql += subsql;
2070 // sql += ")";
2071 // return sql;
2072 // }
2073
2074 /***********************************************************************
2075 *
2076 * Database Binding functions
2077 *
2078 * These will be db specific due to typing, and minor differences
2079 * in databases.
2080 *
2081 **********************************************************************/
2082
2083 ///<summary>
2084 /// This is a convenience function that collapses 5 repetitive
2085 /// lines for defining SqliteParameters to 2 parameters:
2086 /// column name and database type.
2087 ///
2088 /// It assumes certain conventions like :param as the param
2089 /// name to replace in parametrized queries, and that source
2090 /// version is always current version, both of which are fine
2091 /// for us.
2092 ///</summary>
2093 ///<returns>a built sqlite parameter</returns>
2094 private static SqliteParameter createSqliteParameter(string name, Type type)
2095 {
2096 SqliteParameter param = new SqliteParameter();
2097 param.ParameterName = ":" + name;
2098 param.DbType = dbtypeFromType(type);
2099 param.SourceColumn = name;
2100 param.SourceVersion = DataRowVersion.Current;
2101 return param;
2102 }
2103
2104 /// <summary>
2105 ///
2106 /// </summary>
2107 /// <param name="da"></param>
2108 /// <param name="conn"></param>
2109 private void setupPrimCommands(SqliteDataAdapter da, SqliteConnection conn)
2110 {
2111 da.InsertCommand = createInsertCommand("prims", ds.Tables["prims"]);
2112 da.InsertCommand.Connection = conn;
2113
2114 da.UpdateCommand = createUpdateCommand("prims", "UUID=:UUID", ds.Tables["prims"]);
2115 da.UpdateCommand.Connection = conn;
2116
2117 SqliteCommand delete = new SqliteCommand("delete from prims where UUID = :UUID");
2118 delete.Parameters.Add(createSqliteParameter("UUID", typeof (String)));
2119 delete.Connection = conn;
2120 da.DeleteCommand = delete;
2121 }
2122
2123 /// <summary>
2124 ///
2125 /// </summary>
2126 /// <param name="da"></param>
2127 /// <param name="conn"></param>
2128 private void setupItemsCommands(SqliteDataAdapter da, SqliteConnection conn)
2129 {
2130 da.InsertCommand = createInsertCommand("primitems", ds.Tables["primitems"]);
2131 da.InsertCommand.Connection = conn;
2132
2133 da.UpdateCommand = createUpdateCommand("primitems", "itemID = :itemID", ds.Tables["primitems"]);
2134 da.UpdateCommand.Connection = conn;
2135
2136 SqliteCommand delete = new SqliteCommand("delete from primitems where itemID = :itemID");
2137 delete.Parameters.Add(createSqliteParameter("itemID", typeof (String)));
2138 delete.Connection = conn;
2139 da.DeleteCommand = delete;
2140 }
2141
2142 /// <summary>
2143 ///
2144 /// </summary>
2145 /// <param name="da"></param>
2146 /// <param name="conn"></param>
2147 private void setupTerrainCommands(SqliteDataAdapter da, SqliteConnection conn)
2148 {
2149 da.InsertCommand = createInsertCommand("terrain", ds.Tables["terrain"]);
2150 da.InsertCommand.Connection = conn;
2151 }
2152
2153 /// <summary>
2154 ///
2155 /// </summary>
2156 /// <param name="da"></param>
2157 /// <param name="conn"></param>
2158 private void setupLandCommands(SqliteDataAdapter da, SqliteConnection conn)
2159 {
2160 da.InsertCommand = createInsertCommand("land", ds.Tables["land"]);
2161 da.InsertCommand.Connection = conn;
2162
2163 da.UpdateCommand = createUpdateCommand("land", "UUID=:UUID", ds.Tables["land"]);
2164 da.UpdateCommand.Connection = conn;
2165
2166 SqliteCommand delete = new SqliteCommand("delete from land where UUID=:UUID");
2167 delete.Parameters.Add(createSqliteParameter("UUID", typeof(String)));
2168 da.DeleteCommand = delete;
2169 da.DeleteCommand.Connection = conn;
2170 }
2171
2172 /// <summary>
2173 ///
2174 /// </summary>
2175 /// <param name="da"></param>
2176 /// <param name="conn"></param>
2177 private void setupLandAccessCommands(SqliteDataAdapter da, SqliteConnection conn)
2178 {
2179 da.InsertCommand = createInsertCommand("landaccesslist", ds.Tables["landaccesslist"]);
2180 da.InsertCommand.Connection = conn;
2181
2182 da.UpdateCommand = createUpdateCommand("landaccesslist", "LandUUID=:landUUID", "AccessUUID=:AccessUUID", ds.Tables["landaccesslist"]);
2183 da.UpdateCommand.Connection = conn;
2184
2185 SqliteCommand delete = new SqliteCommand("delete from landaccesslist where LandUUID= :LandUUID and AccessUUID= :AccessUUID");
2186 delete.Parameters.Add(createSqliteParameter("LandUUID", typeof(String)));
2187 delete.Parameters.Add(createSqliteParameter("AccessUUID", typeof(String)));
2188 da.DeleteCommand = delete;
2189 da.DeleteCommand.Connection = conn;
2190
2191 }
2192
2193 private void setupRegionSettingsCommands(SqliteDataAdapter da, SqliteConnection conn)
2194 {
2195 da.InsertCommand = createInsertCommand("regionsettings", ds.Tables["regionsettings"]);
2196 da.InsertCommand.Connection = conn;
2197 da.UpdateCommand = createUpdateCommand("regionsettings", "regionUUID=:regionUUID", ds.Tables["regionsettings"]);
2198 da.UpdateCommand.Connection = conn;
2199 }
2200
2201 /// <summary>
2202 ///
2203 /// </summary>
2204 /// <param name="da"></param>
2205 /// <param name="conn"></param>
2206 private void setupShapeCommands(SqliteDataAdapter da, SqliteConnection conn)
2207 {
2208 da.InsertCommand = createInsertCommand("primshapes", ds.Tables["primshapes"]);
2209 da.InsertCommand.Connection = conn;
2210
2211 da.UpdateCommand = createUpdateCommand("primshapes", "UUID=:UUID", ds.Tables["primshapes"]);
2212 da.UpdateCommand.Connection = conn;
2213
2214 SqliteCommand delete = new SqliteCommand("delete from primshapes where UUID = :UUID");
2215 delete.Parameters.Add(createSqliteParameter("UUID", typeof (String)));
2216 delete.Connection = conn;
2217 da.DeleteCommand = delete;
2218 }
2219
2220 /***********************************************************************
2221 *
2222 * Type conversion functions
2223 *
2224 **********************************************************************/
2225
2226 /// <summary>
2227 /// Type conversion function
2228 /// </summary>
2229 /// <param name="type"></param>
2230 /// <returns></returns>
2231 private static DbType dbtypeFromType(Type type)
2232 {
2233 if (type == typeof (String))
2234 {
2235 return DbType.String;
2236 }
2237 else if (type == typeof (Int32))
2238 {
2239 return DbType.Int32;
2240 }
2241 else if (type == typeof (Double))
2242 {
2243 return DbType.Double;
2244 }
2245 else if (type == typeof (Byte))
2246 {
2247 return DbType.Byte;
2248 }
2249 else if (type == typeof (Double))
2250 {
2251 return DbType.Double;
2252 }
2253 else if (type == typeof (Byte[]))
2254 {
2255 return DbType.Binary;
2256 }
2257 else
2258 {
2259 return DbType.String;
2260 }
2261 }
2262
2263 }
2264}
diff --git a/OpenSim/Data/SQLiteNG/SQLiteUserAccountData.cs b/OpenSim/Data/SQLiteNG/SQLiteUserAccountData.cs
new file mode 100644
index 0000000..67cf716
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/SQLiteUserAccountData.cs
@@ -0,0 +1,81 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections;
30using System.Collections.Generic;
31using System.Data;
32using OpenMetaverse;
33using OpenSim.Framework;
34using Mono.Data.SqliteClient;
35
36namespace OpenSim.Data.SQLite
37{
38 public class SQLiteUserAccountData : SQLiteGenericTableHandler<UserAccountData>, IUserAccountData
39 {
40 public SQLiteUserAccountData(string connectionString, string realm)
41 : base(connectionString, realm, "UserAccount")
42 {
43 }
44
45 public UserAccountData[] GetUsers(UUID scopeID, string query)
46 {
47 string[] words = query.Split(new char[] {' '});
48
49 for (int i = 0 ; i < words.Length ; i++)
50 {
51 if (words[i].Length < 3)
52 {
53 if (i != words.Length - 1)
54 Array.Copy(words, i + 1, words, i, words.Length - i - 1);
55 Array.Resize(ref words, words.Length - 1);
56 }
57 }
58
59 if (words.Length == 0)
60 return new UserAccountData[0];
61
62 if (words.Length > 2)
63 return new UserAccountData[0];
64
65 SqliteCommand cmd = new SqliteCommand();
66
67 if (words.Length == 1)
68 {
69 cmd.CommandText = String.Format("select * from {0} where (ScopeID='{1}' or ScopeID='00000000-0000-0000-0000-000000000000') and (FirstName like '{2}%' or LastName like '{2}%')",
70 m_Realm, scopeID.ToString(), words[0]);
71 }
72 else
73 {
74 cmd.CommandText = String.Format("select * from {0} where (ScopeID='{1}' or ScopeID='00000000-0000-0000-0000-000000000000') and (FirstName like '{2}%' or LastName like '{3}%')",
75 m_Realm, scopeID.ToString(), words[0], words[1]);
76 }
77
78 return DoQuery(cmd);
79 }
80 }
81}
diff --git a/OpenSim/Data/SQLiteNG/SQLiteUtils.cs b/OpenSim/Data/SQLiteNG/SQLiteUtils.cs
new file mode 100644
index 0000000..4a835ce
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/SQLiteUtils.cs
@@ -0,0 +1,307 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Data;
30using Mono.Data.SqliteClient;
31
32namespace OpenSim.Data.SQLite
33{
34 /// <summary>
35 /// A base class for methods needed by all SQLite database classes
36 /// </summary>
37 public class SQLiteUtil
38 {
39 /***********************************************************************
40 *
41 * Database Definition Helper Functions
42 *
43 * This should be db agnostic as we define them in ADO.NET terms
44 *
45 **********************************************************************/
46
47 /// <summary>
48 ///
49 /// </summary>
50 /// <param name="dt"></param>
51 /// <param name="name"></param>
52 /// <param name="type"></param>
53 public static void createCol(DataTable dt, string name, Type type)
54 {
55 DataColumn col = new DataColumn(name, type);
56 dt.Columns.Add(col);
57 }
58
59 /***********************************************************************
60 *
61 * SQL Statement Creation Functions
62 *
63 * These functions create SQL statements for update, insert, and create.
64 * They can probably be factored later to have a db independant
65 * portion and a db specific portion
66 *
67 **********************************************************************/
68
69 /// <summary>
70 /// Create an insert command
71 /// </summary>
72 /// <param name="table">table name</param>
73 /// <param name="dt">data table</param>
74 /// <returns>the created command</returns>
75 /// <remarks>
76 /// This is subtle enough to deserve some commentary.
77 /// Instead of doing *lots* and *lots of hardcoded strings
78 /// for database definitions we'll use the fact that
79 /// realistically all insert statements look like "insert
80 /// into A(b, c) values(:b, :c) on the parameterized query
81 /// front. If we just have a list of b, c, etc... we can
82 /// generate these strings instead of typing them out.
83 /// </remarks>
84 public static SqliteCommand createInsertCommand(string table, DataTable dt)
85 {
86
87 string[] cols = new string[dt.Columns.Count];
88 for (int i = 0; i < dt.Columns.Count; i++)
89 {
90 DataColumn col = dt.Columns[i];
91 cols[i] = col.ColumnName;
92 }
93
94 string sql = "insert into " + table + "(";
95 sql += String.Join(", ", cols);
96 // important, the first ':' needs to be here, the rest get added in the join
97 sql += ") values (:";
98 sql += String.Join(", :", cols);
99 sql += ")";
100 SqliteCommand cmd = new SqliteCommand(sql);
101
102 // this provides the binding for all our parameters, so
103 // much less code than it used to be
104 foreach (DataColumn col in dt.Columns)
105 {
106 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
107 }
108 return cmd;
109 }
110
111 /// <summary>
112 /// create an update command
113 /// </summary>
114 /// <param name="table">table name</param>
115 /// <param name="pk"></param>
116 /// <param name="dt"></param>
117 /// <returns>the created command</returns>
118 public static SqliteCommand createUpdateCommand(string table, string pk, DataTable dt)
119 {
120 string sql = "update " + table + " set ";
121 string subsql = String.Empty;
122 foreach (DataColumn col in dt.Columns)
123 {
124 if (subsql.Length > 0)
125 {
126 // a map function would rock so much here
127 subsql += ", ";
128 }
129 subsql += col.ColumnName + "= :" + col.ColumnName;
130 }
131 sql += subsql;
132 sql += " where " + pk;
133 SqliteCommand cmd = new SqliteCommand(sql);
134
135 // this provides the binding for all our parameters, so
136 // much less code than it used to be
137
138 foreach (DataColumn col in dt.Columns)
139 {
140 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
141 }
142 return cmd;
143 }
144
145 /// <summary>
146 ///
147 /// </summary>
148 /// <param name="dt">Data Table</param>
149 /// <returns></returns>
150 public static string defineTable(DataTable dt)
151 {
152 string sql = "create table " + dt.TableName + "(";
153 string subsql = String.Empty;
154 foreach (DataColumn col in dt.Columns)
155 {
156 if (subsql.Length > 0)
157 {
158 // a map function would rock so much here
159 subsql += ",\n";
160 }
161 subsql += col.ColumnName + " " + sqliteType(col.DataType);
162 if (dt.PrimaryKey.Length > 0)
163 {
164 if (col == dt.PrimaryKey[0])
165 {
166 subsql += " primary key";
167 }
168 }
169 }
170 sql += subsql;
171 sql += ")";
172 return sql;
173 }
174
175 /***********************************************************************
176 *
177 * Database Binding functions
178 *
179 * These will be db specific due to typing, and minor differences
180 * in databases.
181 *
182 **********************************************************************/
183
184 ///<summary>
185 /// <para>
186 /// This is a convenience function that collapses 5 repetitive
187 /// lines for defining SqliteParameters to 2 parameters:
188 /// column name and database type.
189 /// </para>
190 ///
191 /// <para>
192 /// It assumes certain conventions like :param as the param
193 /// name to replace in parametrized queries, and that source
194 /// version is always current version, both of which are fine
195 /// for us.
196 /// </para>
197 ///</summary>
198 /// <param name="name"></param>
199 /// <param name="type"></param>
200 ///<returns>a built sqlite parameter</returns>
201 public static SqliteParameter createSqliteParameter(string name, Type type)
202 {
203 SqliteParameter param = new SqliteParameter();
204 param.ParameterName = ":" + name;
205 param.DbType = dbtypeFromType(type);
206 param.SourceColumn = name;
207 param.SourceVersion = DataRowVersion.Current;
208 return param;
209 }
210
211 /***********************************************************************
212 *
213 * Type conversion functions
214 *
215 **********************************************************************/
216
217 /// <summary>
218 /// Type conversion function
219 /// </summary>
220 /// <param name="type">a type</param>
221 /// <returns>a DbType</returns>
222 public static DbType dbtypeFromType(Type type)
223 {
224 if (type == typeof (String))
225 {
226 return DbType.String;
227 }
228 else if (type == typeof (Int32))
229 {
230 return DbType.Int32;
231 }
232 else if (type == typeof (UInt32))
233 {
234 return DbType.UInt32;
235 }
236 else if (type == typeof (Int64))
237 {
238 return DbType.Int64;
239 }
240 else if (type == typeof (UInt64))
241 {
242 return DbType.UInt64;
243 }
244 else if (type == typeof (Double))
245 {
246 return DbType.Double;
247 }
248 else if (type == typeof (Boolean))
249 {
250 return DbType.Boolean;
251 }
252 else if (type == typeof (Byte[]))
253 {
254 return DbType.Binary;
255 }
256 else
257 {
258 return DbType.String;
259 }
260 }
261
262 /// <summary>
263 /// </summary>
264 /// <param name="type">a Type</param>
265 /// <returns>a string</returns>
266 /// <remarks>this is something we'll need to implement for each db slightly differently.</remarks>
267 public static string sqliteType(Type type)
268 {
269 if (type == typeof (String))
270 {
271 return "varchar(255)";
272 }
273 else if (type == typeof (Int32))
274 {
275 return "integer";
276 }
277 else if (type == typeof (UInt32))
278 {
279 return "integer";
280 }
281 else if (type == typeof (Int64))
282 {
283 return "varchar(255)";
284 }
285 else if (type == typeof (UInt64))
286 {
287 return "varchar(255)";
288 }
289 else if (type == typeof (Double))
290 {
291 return "float";
292 }
293 else if (type == typeof (Boolean))
294 {
295 return "integer";
296 }
297 else if (type == typeof (Byte[]))
298 {
299 return "blob";
300 }
301 else
302 {
303 return "string";
304 }
305 }
306 }
307}
diff --git a/OpenSim/Data/SQLiteNG/SQLiteXInventoryData.cs b/OpenSim/Data/SQLiteNG/SQLiteXInventoryData.cs
new file mode 100644
index 0000000..a66e0c6
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/SQLiteXInventoryData.cs
@@ -0,0 +1,155 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Data;
30using System.Reflection;
31using System.Collections.Generic;
32using Mono.Data.SqliteClient;
33using log4net;
34using OpenMetaverse;
35using OpenSim.Framework;
36
37namespace OpenSim.Data.SQLite
38{
39 /// <summary>
40 /// A MySQL Interface for the Asset Server
41 /// </summary>
42 public class SQLiteXInventoryData : IXInventoryData
43 {
44// private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
45
46 private SQLiteGenericTableHandler<XInventoryFolder> m_Folders;
47 private SqliteItemHandler m_Items;
48
49 public SQLiteXInventoryData(string conn, string realm)
50 {
51 m_Folders = new SQLiteGenericTableHandler<XInventoryFolder>(
52 conn, "inventoryfolders", "InventoryStore");
53 m_Items = new SqliteItemHandler(
54 conn, "inventoryitems", String.Empty);
55 }
56
57 public XInventoryFolder[] GetFolders(string[] fields, string[] vals)
58 {
59 return m_Folders.Get(fields, vals);
60 }
61
62 public XInventoryItem[] GetItems(string[] fields, string[] vals)
63 {
64 return m_Items.Get(fields, vals);
65 }
66
67 public bool StoreFolder(XInventoryFolder folder)
68 {
69 return m_Folders.Store(folder);
70 }
71
72 public bool StoreItem(XInventoryItem item)
73 {
74 return m_Items.Store(item);
75 }
76
77 public bool DeleteFolders(string field, string val)
78 {
79 return m_Folders.Delete(field, val);
80 }
81
82 public bool DeleteItems(string field, string val)
83 {
84 return m_Items.Delete(field, val);
85 }
86
87 public bool MoveItem(string id, string newParent)
88 {
89 return m_Items.MoveItem(id, newParent);
90 }
91
92 public XInventoryItem[] GetActiveGestures(UUID principalID)
93 {
94 return m_Items.GetActiveGestures(principalID);
95 }
96
97 public int GetAssetPermissions(UUID principalID, UUID assetID)
98 {
99 return m_Items.GetAssetPermissions(principalID, assetID);
100 }
101 }
102
103 public class SqliteItemHandler : SQLiteGenericTableHandler<XInventoryItem>
104 {
105 public SqliteItemHandler(string c, string t, string m) :
106 base(c, t, m)
107 {
108 }
109
110 public bool MoveItem(string id, string newParent)
111 {
112 SqliteCommand cmd = new SqliteCommand();
113
114 cmd.CommandText = String.Format("update {0} set parentFolderID = :ParentFolderID where inventoryID = :InventoryID", m_Realm);
115 cmd.Parameters.Add(new SqliteParameter(":ParentFolderID", newParent));
116 cmd.Parameters.Add(new SqliteParameter(":InventoryID", id));
117
118 return ExecuteNonQuery(cmd, m_Connection) == 0 ? false : true;
119 }
120
121 public XInventoryItem[] GetActiveGestures(UUID principalID)
122 {
123 SqliteCommand cmd = new SqliteCommand();
124 cmd.CommandText = String.Format("select * from inventoryitems where avatarId = :uuid and assetType = :type and flags = 1", m_Realm);
125
126 cmd.Parameters.Add(new SqliteParameter(":uuid", principalID.ToString()));
127 cmd.Parameters.Add(new SqliteParameter(":type", (int)AssetType.Gesture));
128
129 return DoQuery(cmd);
130 }
131
132 public int GetAssetPermissions(UUID principalID, UUID assetID)
133 {
134 SqliteCommand cmd = new SqliteCommand();
135
136 cmd.CommandText = String.Format("select inventoryCurrentPermissions from inventoryitems where avatarID = :PrincipalID and assetID = :AssetID", m_Realm);
137 cmd.Parameters.Add(new SqliteParameter(":PrincipalID", principalID.ToString()));
138 cmd.Parameters.Add(new SqliteParameter(":AssetID", assetID.ToString()));
139
140 IDataReader reader = ExecuteReader(cmd, m_Connection);
141
142 int perms = 0;
143
144 while (reader.Read())
145 {
146 perms |= Convert.ToInt32(reader["inventoryCurrentPermissions"]);
147 }
148
149 reader.Close();
150 CloseCommand(cmd);
151
152 return perms;
153 }
154 }
155}
diff --git a/OpenSim/Data/SQLiteNG/Tests/SQLiteAssetTest.cs b/OpenSim/Data/SQLiteNG/Tests/SQLiteAssetTest.cs
new file mode 100644
index 0000000..0c2f5df
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Tests/SQLiteAssetTest.cs
@@ -0,0 +1,64 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System.IO;
29using NUnit.Framework;
30using OpenSim.Data.Tests;
31using OpenSim.Tests.Common;
32
33namespace OpenSim.Data.SQLite.Tests
34{
35 [TestFixture, DatabaseTest]
36 public class SQLiteAssetTest : BasicAssetTest
37 {
38 public string file;
39 public string connect;
40
41 [TestFixtureSetUp]
42 public void Init()
43 {
44 // SQLite doesn't work on power or z linux
45 if (Directory.Exists("/proc/ppc64") || Directory.Exists("/proc/dasd"))
46 {
47 Assert.Ignore();
48 }
49
50 SuperInit();
51 file = Path.GetTempFileName() + ".db";
52 connect = "URI=file:" + file + ",version=3";
53 db = new SQLiteAssetData();
54 db.Initialise(connect);
55 }
56
57 [TestFixtureTearDown]
58 public void Cleanup()
59 {
60 db.Dispose();
61 File.Delete(file);
62 }
63 }
64}
diff --git a/OpenSim/Data/SQLiteNG/Tests/SQLiteEstateTest.cs b/OpenSim/Data/SQLiteNG/Tests/SQLiteEstateTest.cs
new file mode 100644
index 0000000..30f6641
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Tests/SQLiteEstateTest.cs
@@ -0,0 +1,65 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System.IO;
29using NUnit.Framework;
30using OpenSim.Data.Tests;
31using OpenSim.Tests.Common;
32
33namespace OpenSim.Data.SQLite.Tests
34{
35 [TestFixture, DatabaseTest]
36 public class SQLiteEstateTest : BasicEstateTest
37 {
38 public string file = "regiontest.db";
39 public string connect;
40
41 [TestFixtureSetUp]
42 public void Init()
43 {
44 // SQLite doesn't work on power or z linux
45 if (Directory.Exists("/proc/ppc64") || Directory.Exists("/proc/dasd"))
46 {
47 Assert.Ignore();
48 }
49
50 SuperInit();
51 file = Path.GetTempFileName() + ".db";
52 connect = "URI=file:" + file + ",version=3";
53 db = new SQLiteEstateStore();
54 db.Initialise(connect);
55 regionDb = new SQLiteRegionData();
56 regionDb.Initialise(connect);
57 }
58
59 [TestFixtureTearDown]
60 public void Cleanup()
61 {
62 regionDb.Dispose();
63 }
64 }
65}
diff --git a/OpenSim/Data/SQLiteNG/Tests/SQLiteInventoryTest.cs b/OpenSim/Data/SQLiteNG/Tests/SQLiteInventoryTest.cs
new file mode 100644
index 0000000..98458a3
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Tests/SQLiteInventoryTest.cs
@@ -0,0 +1,66 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System.IO;
29using NUnit.Framework;
30using OpenSim.Data.Tests;
31using OpenSim.Tests.Common;
32
33namespace OpenSim.Data.SQLite.Tests
34{
35 [TestFixture, DatabaseTest]
36 public class SQLiteInventoryTest : BasicInventoryTest
37 {
38 public string file;
39 public string connect;
40
41 [TestFixtureSetUp]
42 public void Init()
43 {
44 // SQLite doesn't work on power or z linux
45 if (Directory.Exists("/proc/ppc64") || Directory.Exists("/proc/dasd"))
46 {
47 Assert.Ignore();
48 }
49
50 SuperInit();
51
52 file = Path.GetTempFileName() + ".db";
53 connect = "URI=file:" + file + ",version=3";
54
55 db = new SQLiteInventoryStore();
56 db.Initialise(connect);
57 }
58
59 [TestFixtureTearDown]
60 public void Cleanup()
61 {
62 db.Dispose();
63 File.Delete(file);
64 }
65 }
66}
diff --git a/OpenSim/Data/SQLiteNG/Tests/SQLiteRegionTest.cs b/OpenSim/Data/SQLiteNG/Tests/SQLiteRegionTest.cs
new file mode 100644
index 0000000..abb97cf
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/Tests/SQLiteRegionTest.cs
@@ -0,0 +1,64 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System.IO;
29using NUnit.Framework;
30using OpenSim.Data.Tests;
31using OpenSim.Tests.Common;
32
33namespace OpenSim.Data.SQLite.Tests
34{
35 [TestFixture, DatabaseTest]
36 public class SQLiteRegionTest : BasicRegionTest
37 {
38 public string file = "regiontest.db";
39 public string connect;
40
41 [TestFixtureSetUp]
42 public void Init()
43 {
44 // SQLite doesn't work on power or z linux
45 if (Directory.Exists("/proc/ppc64") || Directory.Exists("/proc/dasd"))
46 {
47 Assert.Ignore();
48 }
49
50 SuperInit();
51 file = Path.GetTempFileName() + ".db";
52 connect = "URI=file:" + file + ",version=3";
53 db = new SQLiteRegionData();
54 db.Initialise(connect);
55 }
56
57 [TestFixtureTearDown]
58 public void Cleanup()
59 {
60 db.Dispose();
61 File.Delete(file);
62 }
63 }
64}