aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/SQLiteNG
diff options
context:
space:
mode:
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.cs257
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteAvatarData.cs74
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteEstateData.cs387
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteFramework.cs95
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteFriendsData.cs70
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteGenericTableHandler.cs270
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteInventoryStore.cs909
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteRegionData.cs2321
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteUserAccountData.cs81
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteUtils.cs307
-rw-r--r--OpenSim/Data/SQLiteNG/SQLiteXInventoryData.cs155
57 files changed, 6151 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..4aeb67b
--- /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.SQLiteNG")]
36[assembly : AssemblyDescription("")]
37[assembly : AssemblyConfiguration("")]
38[assembly : AssemblyCompany("http://opensimulator.org")]
39[assembly : AssemblyProduct("OpenSim.Data.SQLiteNG")]
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..9b34a21
--- /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.Sqlite;
34using OpenMetaverse;
35using OpenSim.Framework;
36
37namespace OpenSim.Data.SQLiteNG
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}
diff --git a/OpenSim/Data/SQLiteNG/SQLiteAuthenticationData.cs b/OpenSim/Data/SQLiteNG/SQLiteAuthenticationData.cs
new file mode 100644
index 0000000..4a5dc2e
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/SQLiteAuthenticationData.cs
@@ -0,0 +1,257 @@
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.Sqlite;
35
36namespace OpenSim.Data.SQLiteNG
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 Migration m = new Migration(m_Connection, GetType().Assembly, "AuthStore");
60 m.Update();
61
62 m_initialized = true;
63 }
64 }
65
66 public AuthenticationData Get(UUID principalID)
67 {
68 AuthenticationData ret = new AuthenticationData();
69 ret.Data = new Dictionary<string, object>();
70
71 SqliteCommand cmd = new SqliteCommand("select * from `" + m_Realm + "` where UUID = :PrincipalID");
72 cmd.Parameters.Add(new SqliteParameter(":PrincipalID", principalID.ToString()));
73
74 IDataReader result = ExecuteReader(cmd, m_Connection);
75
76 try
77 {
78 if (result.Read())
79 {
80 ret.PrincipalID = principalID;
81
82 if (m_ColumnNames == null)
83 {
84 m_ColumnNames = new List<string>();
85
86 DataTable schemaTable = result.GetSchemaTable();
87 foreach (DataRow row in schemaTable.Rows)
88 m_ColumnNames.Add(row["ColumnName"].ToString());
89 }
90
91 foreach (string s in m_ColumnNames)
92 {
93 if (s == "UUID")
94 continue;
95
96 ret.Data[s] = result[s].ToString();
97 }
98
99 return ret;
100 }
101 else
102 {
103 return null;
104 }
105 }
106 catch
107 {
108 }
109 finally
110 {
111 //CloseCommand(cmd);
112 }
113
114 return null;
115 }
116
117 public bool Store(AuthenticationData data)
118 {
119 if (data.Data.ContainsKey("UUID"))
120 data.Data.Remove("UUID");
121
122 string[] fields = new List<string>(data.Data.Keys).ToArray();
123 string[] values = new string[data.Data.Count];
124 int i = 0;
125 foreach (object o in data.Data.Values)
126 values[i++] = o.ToString();
127
128 SqliteCommand cmd = new SqliteCommand();
129
130 if (Get(data.PrincipalID) != null)
131 {
132
133
134 string update = "update `" + m_Realm + "` set ";
135 bool first = true;
136 foreach (string field in fields)
137 {
138 if (!first)
139 update += ", ";
140 update += "`" + field + "` = :" + field;
141 cmd.Parameters.Add(new SqliteParameter(":" + field, data.Data[field]));
142
143 first = false;
144 }
145
146 update += " where UUID = :UUID";
147 cmd.Parameters.Add(new SqliteParameter(":UUID", data.PrincipalID.ToString()));
148
149 cmd.CommandText = update;
150 try
151 {
152 if (ExecuteNonQuery(cmd, m_Connection) < 1)
153 {
154 //CloseCommand(cmd);
155 return false;
156 }
157 }
158 catch (Exception e)
159 {
160 Console.WriteLine(e.ToString());
161 //CloseCommand(cmd);
162 return false;
163 }
164 }
165
166 else
167 {
168 string insert = "insert into `" + m_Realm + "` (`UUID`, `" +
169 String.Join("`, `", fields) +
170 "`) values (:UUID, :" + String.Join(", :", fields) + ")";
171
172 cmd.Parameters.Add(new SqliteParameter(":UUID", data.PrincipalID.ToString()));
173 foreach (string field in fields)
174 cmd.Parameters.Add(new SqliteParameter(":" + field, data.Data[field]));
175
176 cmd.CommandText = insert;
177
178 try
179 {
180 if (ExecuteNonQuery(cmd, m_Connection) < 1)
181 {
182 //CloseCommand(cmd);
183 return false;
184 }
185 }
186 catch (Exception e)
187 {
188 Console.WriteLine(e.ToString());
189 //CloseCommand(cmd);
190 return false;
191 }
192 }
193
194 //CloseCommand(cmd);
195
196 return true;
197 }
198
199 public bool SetDataItem(UUID principalID, string item, string value)
200 {
201 SqliteCommand cmd = new SqliteCommand("update `" + m_Realm +
202 "` set `" + item + "` = " + value + " where UUID = '" + principalID.ToString() + "'");
203
204 if (ExecuteNonQuery(cmd, m_Connection) > 0)
205 return true;
206
207 return false;
208 }
209
210 public bool SetToken(UUID principalID, string token, int lifetime)
211 {
212 if (System.Environment.TickCount - m_LastExpire > 30000)
213 DoExpire();
214
215 SqliteCommand cmd = new SqliteCommand("insert into tokens (UUID, token, validity) values ('" + principalID.ToString() +
216 "', '" + token + "', datetime('now', 'localtime', '+" + lifetime.ToString() + " minutes'))");
217
218 if (ExecuteNonQuery(cmd, m_Connection) > 0)
219 {
220 cmd.Dispose();
221 return true;
222 }
223
224 cmd.Dispose();
225 return false;
226 }
227
228 public bool CheckToken(UUID principalID, string token, int lifetime)
229 {
230 if (System.Environment.TickCount - m_LastExpire > 30000)
231 DoExpire();
232
233 SqliteCommand cmd = new SqliteCommand("update tokens set validity = datetime('now', 'localtime', '+" + lifetime.ToString() +
234 " minutes') where UUID = '" + principalID.ToString() + "' and token = '" + token + "' and validity > datetime('now', 'localtime')");
235
236 if (ExecuteNonQuery(cmd, m_Connection) > 0)
237 {
238 cmd.Dispose();
239 return true;
240 }
241
242 cmd.Dispose();
243
244 return false;
245 }
246
247 private void DoExpire()
248 {
249 SqliteCommand cmd = new SqliteCommand("delete from tokens where validity < datetime('now', 'localtime')");
250 ExecuteNonQuery(cmd, m_Connection);
251
252 cmd.Dispose();
253
254 m_LastExpire = System.Environment.TickCount;
255 }
256 }
257}
diff --git a/OpenSim/Data/SQLiteNG/SQLiteAvatarData.cs b/OpenSim/Data/SQLiteNG/SQLiteAvatarData.cs
new file mode 100644
index 0000000..d0fd49c
--- /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.Sqlite;
37
38namespace OpenSim.Data.SQLiteNG
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.AddWithValue(":PrincipalID", principalID.ToString());
59 cmd.Parameters.AddWithValue(":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..2e2d717
--- /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.Sqlite;
34using OpenMetaverse;
35using OpenSim.Framework;
36using OpenSim.Region.Framework.Interfaces;
37
38namespace OpenSim.Data.SQLiteNG
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.AddWithValue(":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.AddWithValue(":"+name, "1");
147 else
148 cmd.Parameters.AddWithValue(":"+name, "0");
149 }
150 else
151 {
152 cmd.Parameters.AddWithValue(":"+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.AddWithValue(":RegionID", regionID.ToString());
171 cmd.Parameters.AddWithValue(":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.AddWithValue(":"+name, "1");
215 else
216 cmd.Parameters.AddWithValue(":"+name, "0");
217 }
218 else
219 {
220 cmd.Parameters.AddWithValue(":"+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.AddWithValue(":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.AddWithValue(":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.AddWithValue(":EstateID", es.EstateID.ToString());
274 cmd.Parameters.AddWithValue(":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.AddWithValue(":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.AddWithValue(":EstateID", EstateID.ToString());
297 cmd.Parameters.AddWithValue(":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.AddWithValue(":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.AddWithValue(":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.AddWithValue(":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.AddWithValue(":RegionID", regionID.ToString());
369 cmd.Parameters.AddWithValue(":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..f0ddc59
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/SQLiteFramework.cs
@@ -0,0 +1,95 @@
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.Sqlite;
35
36namespace OpenSim.Data.SQLiteNG
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/*
59 SqliteConnection newConnection =
60 (SqliteConnection)((ICloneable)connection).Clone();
61 newConnection.Open();
62
63 cmd.Connection = newConnection;
64*/
65 cmd.Connection = connection;
66 //Console.WriteLine("XXX " + cmd.CommandText);
67
68 return cmd.ExecuteNonQuery();
69 }
70 }
71
72 protected IDataReader ExecuteReader(SqliteCommand cmd, SqliteConnection connection)
73 {
74 lock (connection)
75 {
76 //SqliteConnection newConnection =
77 // (SqliteConnection)((ICloneable)connection).Clone();
78 //newConnection.Open();
79
80 //cmd.Connection = newConnection;
81 cmd.Connection = connection;
82 //Console.WriteLine("XXX " + cmd.CommandText);
83
84 return cmd.ExecuteReader();
85 }
86 }
87
88 protected void CloseCommand(SqliteCommand cmd)
89 {
90 cmd.Connection.Close();
91 cmd.Connection.Dispose();
92 cmd.Dispose();
93 }
94 }
95}
diff --git a/OpenSim/Data/SQLiteNG/SQLiteFriendsData.cs b/OpenSim/Data/SQLiteNG/SQLiteFriendsData.cs
new file mode 100644
index 0000000..702a1d8
--- /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.Sqlite;
35
36namespace OpenSim.Data.SQLiteNG
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.AddWithValue(":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.AddWithValue(":PrincipalID", principalID.ToString());
62 cmd.Parameters.AddWithValue(":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..632c5bf
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/SQLiteGenericTableHandler.cs
@@ -0,0 +1,270 @@
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.Sqlite;
34using OpenMetaverse;
35using OpenSim.Framework;
36using OpenSim.Region.Framework.Interfaces;
37
38namespace OpenSim.Data.SQLiteNG
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 Console.WriteLine(string.Format("OPENING CONNECTION FOR {0} USING {1}", storeName, connectionString));
63 m_Connection.Open();
64
65 if (storeName != String.Empty)
66 {
67 Assembly assem = GetType().Assembly;
68 //SqliteConnection newConnection =
69 // (SqliteConnection)((ICloneable)m_Connection).Clone();
70 //newConnection.Open();
71
72 //Migration m = new Migration(newConnection, assem, storeName);
73 Migration m = new Migration(m_Connection, assem, storeName);
74 m.Update();
75 //newConnection.Close();
76 //newConnection.Dispose();
77 }
78
79 m_initialized = true;
80 }
81
82 Type t = typeof(T);
83 FieldInfo[] fields = t.GetFields(BindingFlags.Public |
84 BindingFlags.Instance |
85 BindingFlags.DeclaredOnly);
86
87 if (fields.Length == 0)
88 return;
89
90 foreach (FieldInfo f in fields)
91 {
92 if (f.Name != "Data")
93 m_Fields[f.Name] = f;
94 else
95 m_DataField = f;
96 }
97 }
98
99 private void CheckColumnNames(IDataReader reader)
100 {
101 if (m_ColumnNames != null)
102 return;
103
104 m_ColumnNames = new List<string>();
105
106 DataTable schemaTable = reader.GetSchemaTable();
107 foreach (DataRow row in schemaTable.Rows)
108 {
109 if (row["ColumnName"] != null &&
110 (!m_Fields.ContainsKey(row["ColumnName"].ToString())))
111 m_ColumnNames.Add(row["ColumnName"].ToString());
112 }
113 }
114
115 public T[] Get(string field, string key)
116 {
117 return Get(new string[] { field }, new string[] { key });
118 }
119
120 public T[] Get(string[] fields, string[] keys)
121 {
122 if (fields.Length != keys.Length)
123 return new T[0];
124
125 List<string> terms = new List<string>();
126
127 SqliteCommand cmd = new SqliteCommand();
128
129 for (int i = 0 ; i < fields.Length ; i++)
130 {
131 cmd.Parameters.Add(new SqliteParameter(":" + fields[i], keys[i]));
132 terms.Add("`" + fields[i] + "` = :" + fields[i]);
133 }
134
135 string where = String.Join(" and ", terms.ToArray());
136
137 string query = String.Format("select * from {0} where {1}",
138 m_Realm, where);
139
140 cmd.CommandText = query;
141
142 return DoQuery(cmd);
143 }
144
145 protected T[] DoQuery(SqliteCommand cmd)
146 {
147 IDataReader reader = ExecuteReader(cmd, m_Connection);
148 if (reader == null)
149 return new T[0];
150
151 CheckColumnNames(reader);
152
153 List<T> result = new List<T>();
154
155 while (reader.Read())
156 {
157 T row = new T();
158
159 foreach (string name in m_Fields.Keys)
160 {
161 if (m_Fields[name].GetValue(row) is bool)
162 {
163 int v = Convert.ToInt32(reader[name]);
164 m_Fields[name].SetValue(row, v != 0 ? true : false);
165 }
166 else if (m_Fields[name].GetValue(row) is UUID)
167 {
168 UUID uuid = UUID.Zero;
169
170 UUID.TryParse(reader[name].ToString(), out uuid);
171 m_Fields[name].SetValue(row, uuid);
172 }
173 else if (m_Fields[name].GetValue(row) is int)
174 {
175 int v = Convert.ToInt32(reader[name]);
176 m_Fields[name].SetValue(row, v);
177 }
178 else
179 {
180 m_Fields[name].SetValue(row, reader[name]);
181 }
182 }
183
184 if (m_DataField != null)
185 {
186 Dictionary<string, string> data =
187 new Dictionary<string, string>();
188
189 foreach (string col in m_ColumnNames)
190 {
191 data[col] = reader[col].ToString();
192 if (data[col] == null)
193 data[col] = String.Empty;
194 }
195
196 m_DataField.SetValue(row, data);
197 }
198
199 result.Add(row);
200 }
201
202 //CloseCommand(cmd);
203
204 return result.ToArray();
205 }
206
207 public T[] Get(string where)
208 {
209 SqliteCommand cmd = new SqliteCommand();
210
211 string query = String.Format("select * from {0} where {1}",
212 m_Realm, where);
213
214 cmd.CommandText = query;
215
216 return DoQuery(cmd);
217 }
218
219 public bool Store(T row)
220 {
221 SqliteCommand cmd = new SqliteCommand();
222
223 string query = "";
224 List<String> names = new List<String>();
225 List<String> values = new List<String>();
226
227 foreach (FieldInfo fi in m_Fields.Values)
228 {
229 names.Add(fi.Name);
230 values.Add(":" + fi.Name);
231 cmd.Parameters.Add(new SqliteParameter(":" + fi.Name, fi.GetValue(row).ToString()));
232 }
233
234 if (m_DataField != null)
235 {
236 Dictionary<string, string> data =
237 (Dictionary<string, string>)m_DataField.GetValue(row);
238
239 foreach (KeyValuePair<string, string> kvp in data)
240 {
241 names.Add(kvp.Key);
242 values.Add(":" + kvp.Key);
243 cmd.Parameters.Add(new SqliteParameter(":" + kvp.Key, kvp.Value));
244 }
245 }
246
247 query = String.Format("replace into {0} (`", m_Realm) + String.Join("`,`", names.ToArray()) + "`) values (" + String.Join(",", values.ToArray()) + ")";
248
249 cmd.CommandText = query;
250
251 if (ExecuteNonQuery(cmd, m_Connection) > 0)
252 return true;
253
254 return false;
255 }
256
257 public bool Delete(string field, string val)
258 {
259 SqliteCommand cmd = new SqliteCommand();
260
261 cmd.CommandText = String.Format("delete from {0} where `{1}` = :{1}", m_Realm, field);
262 cmd.Parameters.Add(new SqliteParameter(field, val));
263
264 if (ExecuteNonQuery(cmd, m_Connection) > 0)
265 return true;
266
267 return false;
268 }
269 }
270}
diff --git a/OpenSim/Data/SQLiteNG/SQLiteInventoryStore.cs b/OpenSim/Data/SQLiteNG/SQLiteInventoryStore.cs
new file mode 100644
index 0000000..9207ca3
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/SQLiteInventoryStore.cs
@@ -0,0 +1,909 @@
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.Sqlite;
34using OpenMetaverse;
35using OpenSim.Framework;
36
37namespace OpenSim.Data.SQLiteNG
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 CreateDataSetMapping(invFoldersDa, "inventoryfolders");
102 m_log.Info("[INVENTORY DB]: Populated Inventory Folders Definitions");
103
104 ds.Tables.Add(createInventoryItemsTable());
105 invItemsDa.Fill(ds.Tables["inventoryitems"]);
106 setupItemsCommands(invItemsDa, conn);
107 CreateDataSetMapping(invItemsDa, "inventoryitems");
108 m_log.Info("[INVENTORY DB]: Populated Inventory Items Definitions");
109
110 ds.AcceptChanges();
111 }
112 }
113
114 /// <summary>
115 /// Closes the inventory interface
116 /// </summary>
117 public void Dispose()
118 {
119 if (conn != null)
120 {
121 conn.Close();
122 conn = null;
123 }
124 if (invItemsDa != null)
125 {
126 invItemsDa.Dispose();
127 invItemsDa = null;
128 }
129 if (invFoldersDa != null)
130 {
131 invFoldersDa.Dispose();
132 invFoldersDa = null;
133 }
134 if (ds != null)
135 {
136 ds.Dispose();
137 ds = null;
138 }
139 }
140
141 /// <summary>
142 ///
143 /// </summary>
144 /// <param name="row"></param>
145 /// <returns></returns>
146 public InventoryItemBase buildItem(DataRow row)
147 {
148 InventoryItemBase item = new InventoryItemBase();
149 item.ID = new UUID((string) row["UUID"]);
150 item.AssetID = new UUID((string) row["assetID"]);
151 item.AssetType = Convert.ToInt32(row["assetType"]);
152 item.InvType = Convert.ToInt32(row["invType"]);
153 item.Folder = new UUID((string) row["parentFolderID"]);
154 item.Owner = new UUID((string) row["avatarID"]);
155 item.CreatorId = (string)row["creatorsID"];
156 item.Name = (string) row["inventoryName"];
157 item.Description = (string) row["inventoryDescription"];
158
159 item.NextPermissions = Convert.ToUInt32(row["inventoryNextPermissions"]);
160 item.CurrentPermissions = Convert.ToUInt32(row["inventoryCurrentPermissions"]);
161 item.BasePermissions = Convert.ToUInt32(row["inventoryBasePermissions"]);
162 item.EveryOnePermissions = Convert.ToUInt32(row["inventoryEveryOnePermissions"]);
163 item.GroupPermissions = Convert.ToUInt32(row["inventoryGroupPermissions"]);
164
165 // new fields
166 if (!Convert.IsDBNull(row["salePrice"]))
167 item.SalePrice = Convert.ToInt32(row["salePrice"]);
168
169 if (!Convert.IsDBNull(row["saleType"]))
170 item.SaleType = Convert.ToByte(row["saleType"]);
171
172 if (!Convert.IsDBNull(row["creationDate"]))
173 item.CreationDate = Convert.ToInt32(row["creationDate"]);
174
175 if (!Convert.IsDBNull(row["groupID"]))
176 item.GroupID = new UUID((string)row["groupID"]);
177
178 if (!Convert.IsDBNull(row["groupOwned"]))
179 item.GroupOwned = Convert.ToBoolean(row["groupOwned"]);
180
181 if (!Convert.IsDBNull(row["Flags"]))
182 item.Flags = Convert.ToUInt32(row["Flags"]);
183
184 return item;
185 }
186
187 /// <summary>
188 /// Fill a database row with item data
189 /// </summary>
190 /// <param name="row"></param>
191 /// <param name="item"></param>
192 private static void fillItemRow(DataRow row, InventoryItemBase item)
193 {
194 row["UUID"] = item.ID.ToString();
195 row["assetID"] = item.AssetID.ToString();
196 row["assetType"] = item.AssetType;
197 row["invType"] = item.InvType;
198 row["parentFolderID"] = item.Folder.ToString();
199 row["avatarID"] = item.Owner.ToString();
200 row["creatorsID"] = item.CreatorId.ToString();
201 row["inventoryName"] = item.Name;
202 row["inventoryDescription"] = item.Description;
203
204 row["inventoryNextPermissions"] = item.NextPermissions;
205 row["inventoryCurrentPermissions"] = item.CurrentPermissions;
206 row["inventoryBasePermissions"] = item.BasePermissions;
207 row["inventoryEveryOnePermissions"] = item.EveryOnePermissions;
208 row["inventoryGroupPermissions"] = item.GroupPermissions;
209
210 // new fields
211 row["salePrice"] = item.SalePrice;
212 row["saleType"] = item.SaleType;
213 row["creationDate"] = item.CreationDate;
214 row["groupID"] = item.GroupID.ToString();
215 row["groupOwned"] = item.GroupOwned;
216 row["flags"] = item.Flags;
217 }
218
219 /// <summary>
220 /// Add inventory folder
221 /// </summary>
222 /// <param name="folder">Folder base</param>
223 /// <param name="add">true=create folder. false=update existing folder</param>
224 /// <remarks>nasty</remarks>
225 private void addFolder(InventoryFolderBase folder, bool add)
226 {
227 lock (ds)
228 {
229 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
230
231 DataRow inventoryRow = inventoryFolderTable.Rows.Find(folder.ID.ToString());
232 if (inventoryRow == null)
233 {
234 if (! add)
235 m_log.ErrorFormat("Interface Misuse: Attempting to Update non-existant inventory folder: {0}", folder.ID);
236
237 inventoryRow = inventoryFolderTable.NewRow();
238 fillFolderRow(inventoryRow, folder);
239 inventoryFolderTable.Rows.Add(inventoryRow);
240 }
241 else
242 {
243 if (add)
244 m_log.ErrorFormat("Interface Misuse: Attempting to Add inventory folder that already exists: {0}", folder.ID);
245
246 fillFolderRow(inventoryRow, folder);
247 }
248
249 invFoldersDa.Update(ds, "inventoryfolders");
250 }
251 }
252
253 /// <summary>
254 /// Move an inventory folder
255 /// </summary>
256 /// <param name="folder">folder base</param>
257 private void moveFolder(InventoryFolderBase folder)
258 {
259 lock (ds)
260 {
261 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
262
263 DataRow inventoryRow = inventoryFolderTable.Rows.Find(folder.ID.ToString());
264 if (inventoryRow == null)
265 {
266 inventoryRow = inventoryFolderTable.NewRow();
267 fillFolderRow(inventoryRow, folder);
268 inventoryFolderTable.Rows.Add(inventoryRow);
269 }
270 else
271 {
272 moveFolderRow(inventoryRow, folder);
273 }
274
275 invFoldersDa.Update(ds, "inventoryfolders");
276 }
277 }
278
279 /// <summary>
280 /// add an item in inventory
281 /// </summary>
282 /// <param name="item">the item</param>
283 /// <param name="add">true=add item ; false=update existing item</param>
284 private void addItem(InventoryItemBase item, bool add)
285 {
286 lock (ds)
287 {
288 DataTable inventoryItemTable = ds.Tables["inventoryitems"];
289
290 DataRow inventoryRow = inventoryItemTable.Rows.Find(item.ID.ToString());
291 if (inventoryRow == null)
292 {
293 if (!add)
294 m_log.ErrorFormat("[INVENTORY DB]: Interface Misuse: Attempting to Update non-existant inventory item: {0}", item.ID);
295
296 inventoryRow = inventoryItemTable.NewRow();
297 fillItemRow(inventoryRow, item);
298 inventoryItemTable.Rows.Add(inventoryRow);
299 }
300 else
301 {
302 if (add)
303 m_log.ErrorFormat("[INVENTORY DB]: Interface Misuse: Attempting to Add inventory item that already exists: {0}", item.ID);
304
305 fillItemRow(inventoryRow, item);
306 }
307
308 invItemsDa.Update(ds, "inventoryitems");
309
310 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
311
312 inventoryRow = inventoryFolderTable.Rows.Find(item.Folder.ToString());
313 if (inventoryRow != null) //MySQL doesn't throw an exception here, so sqlite shouldn't either.
314 inventoryRow["version"] = (int)inventoryRow["version"] + 1;
315
316 invFoldersDa.Update(ds, "inventoryfolders");
317 }
318 }
319
320 /// <summary>
321 /// TODO : DataSet commit
322 /// </summary>
323 public void Shutdown()
324 {
325 // TODO: DataSet commit
326 }
327
328 /// <summary>
329 /// The name of this DB provider
330 /// </summary>
331 /// <returns>Name of DB provider</returns>
332 public string Name
333 {
334 get { return "SQLite Inventory Data Interface"; }
335 }
336
337 /// <summary>
338 /// Returns the version of this DB provider
339 /// </summary>
340 /// <returns>A string containing the DB provider version</returns>
341 public string Version
342 {
343 get
344 {
345 Module module = GetType().Module;
346 // string dllName = module.Assembly.ManifestModule.Name;
347 Version dllVersion = module.Assembly.GetName().Version;
348
349
350 return
351 string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build,
352 dllVersion.Revision);
353 }
354 }
355
356 /// <summary>
357 /// Returns a list of inventory items contained within the specified folder
358 /// </summary>
359 /// <param name="folderID">The UUID of the target folder</param>
360 /// <returns>A List of InventoryItemBase items</returns>
361 public List<InventoryItemBase> getInventoryInFolder(UUID folderID)
362 {
363 lock (ds)
364 {
365 List<InventoryItemBase> retval = new List<InventoryItemBase>();
366 DataTable inventoryItemTable = ds.Tables["inventoryitems"];
367 string selectExp = "parentFolderID = '" + folderID + "'";
368 DataRow[] rows = inventoryItemTable.Select(selectExp);
369 foreach (DataRow row in rows)
370 {
371 retval.Add(buildItem(row));
372 }
373
374 return retval;
375 }
376 }
377
378 /// <summary>
379 /// Returns a list of the root folders within a users inventory
380 /// </summary>
381 /// <param name="user">The user whos inventory is to be searched</param>
382 /// <returns>A list of folder objects</returns>
383 public List<InventoryFolderBase> getUserRootFolders(UUID user)
384 {
385 return new List<InventoryFolderBase>();
386 }
387
388 // see InventoryItemBase.getUserRootFolder
389 public InventoryFolderBase getUserRootFolder(UUID user)
390 {
391 lock (ds)
392 {
393 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
394 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
395 string selectExp = "agentID = '" + user + "' AND parentID = '" + UUID.Zero + "'";
396 DataRow[] rows = inventoryFolderTable.Select(selectExp);
397 foreach (DataRow row in rows)
398 {
399 folders.Add(buildFolder(row));
400 }
401
402 // There should only ever be one root folder for a user. However, if there's more
403 // than one we'll simply use the first one rather than failing. It would be even
404 // nicer to print some message to this effect, but this feels like it's too low a
405 // to put such a message out, and it's too minor right now to spare the time to
406 // suitably refactor.
407 if (folders.Count > 0)
408 {
409 return folders[0];
410 }
411
412 return null;
413 }
414 }
415
416 /// <summary>
417 /// Append a list of all the child folders of a parent folder
418 /// </summary>
419 /// <param name="folders">list where folders will be appended</param>
420 /// <param name="parentID">ID of parent</param>
421 protected void getInventoryFolders(ref List<InventoryFolderBase> folders, UUID parentID)
422 {
423 lock (ds)
424 {
425 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
426 string selectExp = "parentID = '" + parentID + "'";
427 DataRow[] rows = inventoryFolderTable.Select(selectExp);
428 foreach (DataRow row in rows)
429 {
430 folders.Add(buildFolder(row));
431 }
432
433 }
434 }
435
436 /// <summary>
437 /// Returns a list of inventory folders contained in the folder 'parentID'
438 /// </summary>
439 /// <param name="parentID">The folder to get subfolders for</param>
440 /// <returns>A list of inventory folders</returns>
441 public List<InventoryFolderBase> getInventoryFolders(UUID parentID)
442 {
443 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
444 getInventoryFolders(ref folders, parentID);
445 return folders;
446 }
447
448 /// <summary>
449 /// See IInventoryDataPlugin
450 /// </summary>
451 /// <param name="parentID"></param>
452 /// <returns></returns>
453 public List<InventoryFolderBase> getFolderHierarchy(UUID parentID)
454 {
455 /* Note: There are subtle changes between this implementation of getFolderHierarchy and the previous one
456 * - We will only need to hit the database twice instead of n times.
457 * - We assume the database is well-formed - no stranded/dangling folders, all folders in heirarchy owned
458 * by the same person, each user only has 1 inventory heirarchy
459 * - The returned list is not ordered, instead of breadth-first ordered
460 There are basically 2 usage cases for getFolderHeirarchy:
461 1) Getting the user's entire inventory heirarchy when they log in
462 2) Finding a subfolder heirarchy to delete when emptying the trash.
463 This implementation will pull all inventory folders from the database, and then prune away any folder that
464 is not part of the requested sub-heirarchy. The theory is that it is cheaper to make 1 request from the
465 database than to make n requests. This pays off only if requested heirarchy is large.
466 By making this choice, we are making the worst case better at the cost of making the best case worse
467 - Francis
468 */
469
470 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
471 DataRow[] folderRows = null, parentRow;
472 InventoryFolderBase parentFolder = null;
473 lock (ds)
474 {
475 /* Fetch the parent folder from the database to determine the agent ID.
476 * Then fetch all inventory folders for that agent from the agent ID.
477 */
478 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
479 string selectExp = "UUID = '" + parentID + "'";
480 parentRow = inventoryFolderTable.Select(selectExp); // Assume at most 1 result
481 if (parentRow.GetLength(0) >= 1) // No result means parent folder does not exist
482 {
483 parentFolder = buildFolder(parentRow[0]);
484 UUID agentID = parentFolder.Owner;
485 selectExp = "agentID = '" + agentID + "'";
486 folderRows = inventoryFolderTable.Select(selectExp);
487 }
488
489 if (folderRows != null && folderRows.GetLength(0) >= 1) // No result means parent folder does not exist
490 { // or has no children
491 /* if we're querying the root folder, just return an unordered list of all folders in the user's
492 * inventory
493 */
494 if (parentFolder.ParentID == UUID.Zero)
495 {
496 foreach (DataRow row in folderRows)
497 {
498 InventoryFolderBase curFolder = buildFolder(row);
499 if (curFolder.ID != parentID) // Return all folders except the parent folder of heirarchy
500 folders.Add(buildFolder(row));
501 }
502 } // If requesting root folder
503 /* else we are querying a non-root folder. We currently have a list of all of the user's folders,
504 * we must construct a list of all folders in the heirarchy below parentID.
505 * Our first step will be to construct a hash table of all folders, indexed by parent ID.
506 * Once we have constructed the hash table, we will do a breadth-first traversal on the tree using the
507 * hash table to find child folders.
508 */
509 else
510 { // Querying a non-root folder
511
512 // Build a hash table of all user's inventory folders, indexed by each folder's parent ID
513 Dictionary<UUID, List<InventoryFolderBase>> hashtable =
514 new Dictionary<UUID, List<InventoryFolderBase>>(folderRows.GetLength(0));
515
516 foreach (DataRow row in folderRows)
517 {
518 InventoryFolderBase curFolder = buildFolder(row);
519 if (curFolder.ParentID != UUID.Zero) // Discard root of tree - not needed
520 {
521 if (hashtable.ContainsKey(curFolder.ParentID))
522 {
523 // Current folder already has a sibling - append to sibling list
524 hashtable[curFolder.ParentID].Add(curFolder);
525 }
526 else
527 {
528 List<InventoryFolderBase> siblingList = new List<InventoryFolderBase>();
529 siblingList.Add(curFolder);
530 // Current folder has no known (yet) siblings
531 hashtable.Add(curFolder.ParentID, siblingList);
532 }
533 }
534 } // For all inventory folders
535
536 // Note: Could release the ds lock here - we don't access folderRows or the database anymore.
537 // This is somewhat of a moot point as the callers of this function usually lock db anyways.
538
539 if (hashtable.ContainsKey(parentID)) // if requested folder does have children
540 folders.AddRange(hashtable[parentID]);
541
542 // BreadthFirstSearch build inventory tree **Note: folders.Count is *not* static
543 for (int i = 0; i < folders.Count; i++)
544 if (hashtable.ContainsKey(folders[i].ID))
545 folders.AddRange(hashtable[folders[i].ID]);
546
547 } // if requesting a subfolder heirarchy
548 } // if folder parentID exists and has children
549 } // lock ds
550 return folders;
551 }
552
553 /// <summary>
554 /// Returns an inventory item by its UUID
555 /// </summary>
556 /// <param name="item">The UUID of the item to be returned</param>
557 /// <returns>A class containing item information</returns>
558 public InventoryItemBase getInventoryItem(UUID item)
559 {
560 lock (ds)
561 {
562 DataRow row = ds.Tables["inventoryitems"].Rows.Find(item.ToString());
563 if (row != null)
564 {
565 return buildItem(row);
566 }
567 else
568 {
569 return null;
570 }
571 }
572 }
573
574 /// <summary>
575 /// Returns a specified inventory folder by its UUID
576 /// </summary>
577 /// <param name="folder">The UUID of the folder to be returned</param>
578 /// <returns>A class containing folder information</returns>
579 public InventoryFolderBase getInventoryFolder(UUID folder)
580 {
581 // TODO: Deep voodoo here. If you enable this code then
582 // multi region breaks. No idea why, but I figured it was
583 // better to leave multi region at this point. It does mean
584 // that you don't get to see system textures why creating
585 // clothes and the like. :(
586 lock (ds)
587 {
588 DataRow row = ds.Tables["inventoryfolders"].Rows.Find(folder.ToString());
589 if (row != null)
590 {
591 return buildFolder(row);
592 }
593 else
594 {
595 return null;
596 }
597 }
598 }
599
600 /// <summary>
601 /// Creates a new inventory item based on item
602 /// </summary>
603 /// <param name="item">The item to be created</param>
604 public void addInventoryItem(InventoryItemBase item)
605 {
606 addItem(item, true);
607 }
608
609 /// <summary>
610 /// Updates an inventory item with item (updates based on ID)
611 /// </summary>
612 /// <param name="item">The updated item</param>
613 public void updateInventoryItem(InventoryItemBase item)
614 {
615 addItem(item, false);
616 }
617
618 /// <summary>
619 /// Delete an inventory item
620 /// </summary>
621 /// <param name="item">The item UUID</param>
622 public void deleteInventoryItem(UUID itemID)
623 {
624 lock (ds)
625 {
626 DataTable inventoryItemTable = ds.Tables["inventoryitems"];
627
628 DataRow inventoryRow = inventoryItemTable.Rows.Find(itemID.ToString());
629 if (inventoryRow != null)
630 {
631 inventoryRow.Delete();
632 }
633
634 invItemsDa.Update(ds, "inventoryitems");
635 }
636 }
637
638 public InventoryItemBase queryInventoryItem(UUID itemID)
639 {
640 return getInventoryItem(itemID);
641 }
642
643 public InventoryFolderBase queryInventoryFolder(UUID folderID)
644 {
645 return getInventoryFolder(folderID);
646 }
647
648 /// <summary>
649 /// Delete all items in the specified folder
650 /// </summary>
651 /// <param name="folderId">id of the folder, whose item content should be deleted</param>
652 /// <todo>this is horribly inefficient, but I don't want to ruin the overall structure of this implementation</todo>
653 private void deleteItemsInFolder(UUID folderId)
654 {
655 List<InventoryItemBase> items = getInventoryInFolder(folderId);
656
657 foreach (InventoryItemBase i in items)
658 deleteInventoryItem(i.ID);
659 }
660
661 /// <summary>
662 /// Adds a new folder specified by folder
663 /// </summary>
664 /// <param name="folder">The inventory folder</param>
665 public void addInventoryFolder(InventoryFolderBase folder)
666 {
667 addFolder(folder, true);
668 }
669
670 /// <summary>
671 /// Updates a folder based on its ID with folder
672 /// </summary>
673 /// <param name="folder">The inventory folder</param>
674 public void updateInventoryFolder(InventoryFolderBase folder)
675 {
676 addFolder(folder, false);
677 }
678
679 /// <summary>
680 /// Moves a folder based on its ID with folder
681 /// </summary>
682 /// <param name="folder">The inventory folder</param>
683 public void moveInventoryFolder(InventoryFolderBase folder)
684 {
685 moveFolder(folder);
686 }
687
688 /// <summary>
689 /// Delete a folder
690 /// </summary>
691 /// <remarks>
692 /// This will clean-up any child folders and child items as well
693 /// </remarks>
694 /// <param name="folderID">the folder UUID</param>
695 public void deleteInventoryFolder(UUID folderID)
696 {
697 lock (ds)
698 {
699 List<InventoryFolderBase> subFolders = getFolderHierarchy(folderID);
700
701 DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
702 DataRow inventoryRow;
703
704 //Delete all sub-folders
705 foreach (InventoryFolderBase f in subFolders)
706 {
707 inventoryRow = inventoryFolderTable.Rows.Find(f.ID.ToString());
708 if (inventoryRow != null)
709 {
710 deleteItemsInFolder(f.ID);
711 inventoryRow.Delete();
712 }
713 }
714
715 //Delete the actual row
716 inventoryRow = inventoryFolderTable.Rows.Find(folderID.ToString());
717 if (inventoryRow != null)
718 {
719 deleteItemsInFolder(folderID);
720 inventoryRow.Delete();
721 }
722
723 invFoldersDa.Update(ds, "inventoryfolders");
724 }
725 }
726
727 /***********************************************************************
728 *
729 * Data Table definitions
730 *
731 **********************************************************************/
732
733 protected void CreateDataSetMapping(IDataAdapter da, string tableName)
734 {
735 ITableMapping dbMapping = da.TableMappings.Add(tableName, tableName);
736 foreach (DataColumn col in ds.Tables[tableName].Columns)
737 {
738 dbMapping.ColumnMappings.Add(col.ColumnName, col.ColumnName);
739 }
740 }
741
742 /// <summary>
743 /// Create the "inventoryitems" table
744 /// </summary>
745 private static DataTable createInventoryItemsTable()
746 {
747 DataTable inv = new DataTable("inventoryitems");
748
749 createCol(inv, "UUID", typeof (String)); //inventoryID
750 createCol(inv, "assetID", typeof (String));
751 createCol(inv, "assetType", typeof (Int32));
752 createCol(inv, "invType", typeof (Int32));
753 createCol(inv, "parentFolderID", typeof (String));
754 createCol(inv, "avatarID", typeof (String));
755 createCol(inv, "creatorsID", typeof (String));
756
757 createCol(inv, "inventoryName", typeof (String));
758 createCol(inv, "inventoryDescription", typeof (String));
759 // permissions
760 createCol(inv, "inventoryNextPermissions", typeof (Int32));
761 createCol(inv, "inventoryCurrentPermissions", typeof (Int32));
762 createCol(inv, "inventoryBasePermissions", typeof (Int32));
763 createCol(inv, "inventoryEveryOnePermissions", typeof (Int32));
764 createCol(inv, "inventoryGroupPermissions", typeof (Int32));
765
766 // sale info
767 createCol(inv, "salePrice", typeof(Int32));
768 createCol(inv, "saleType", typeof(Byte));
769
770 // creation date
771 createCol(inv, "creationDate", typeof(Int32));
772
773 // group info
774 createCol(inv, "groupID", typeof(String));
775 createCol(inv, "groupOwned", typeof(Boolean));
776
777 // Flags
778 createCol(inv, "flags", typeof(UInt32));
779
780 inv.PrimaryKey = new DataColumn[] { inv.Columns["UUID"] };
781 return inv;
782 }
783
784 /// <summary>
785 /// Creates the "inventoryfolders" table
786 /// </summary>
787 /// <returns></returns>
788 private static DataTable createInventoryFoldersTable()
789 {
790 DataTable fol = new DataTable("inventoryfolders");
791
792 createCol(fol, "UUID", typeof (String)); //folderID
793 createCol(fol, "name", typeof (String));
794 createCol(fol, "agentID", typeof (String));
795 createCol(fol, "parentID", typeof (String));
796 createCol(fol, "type", typeof (Int32));
797 createCol(fol, "version", typeof (Int32));
798
799 fol.PrimaryKey = new DataColumn[] {fol.Columns["UUID"]};
800 return fol;
801 }
802
803 /// <summary>
804 ///
805 /// </summary>
806 /// <param name="da"></param>
807 /// <param name="conn"></param>
808 private void setupItemsCommands(SqliteDataAdapter da, SqliteConnection conn)
809 {
810 lock (ds)
811 {
812 da.InsertCommand = createInsertCommand("inventoryitems", ds.Tables["inventoryitems"]);
813 da.InsertCommand.Connection = conn;
814
815 da.UpdateCommand = createUpdateCommand("inventoryitems", "UUID=:UUID", ds.Tables["inventoryitems"]);
816 da.UpdateCommand.Connection = conn;
817
818 SqliteCommand delete = new SqliteCommand("delete from inventoryitems where UUID = :UUID");
819 delete.Parameters.Add(createSqliteParameter("UUID", typeof(String)));
820 delete.Connection = conn;
821 da.DeleteCommand = delete;
822 }
823 }
824
825 /// <summary>
826 ///
827 /// </summary>
828 /// <param name="da"></param>
829 /// <param name="conn"></param>
830 private void setupFoldersCommands(SqliteDataAdapter da, SqliteConnection conn)
831 {
832 lock (ds)
833 {
834 da.InsertCommand = createInsertCommand("inventoryfolders", ds.Tables["inventoryfolders"]);
835 da.InsertCommand.Connection = conn;
836
837 da.UpdateCommand = createUpdateCommand("inventoryfolders", "UUID=:UUID", ds.Tables["inventoryfolders"]);
838 da.UpdateCommand.Connection = conn;
839
840 SqliteCommand delete = new SqliteCommand("delete from inventoryfolders where UUID = :UUID");
841 delete.Parameters.Add(createSqliteParameter("UUID", typeof(String)));
842 delete.Connection = conn;
843 da.DeleteCommand = delete;
844 }
845 }
846
847 /// <summary>
848 ///
849 /// </summary>
850 /// <param name="row"></param>
851 /// <returns></returns>
852 private static InventoryFolderBase buildFolder(DataRow row)
853 {
854 InventoryFolderBase folder = new InventoryFolderBase();
855 folder.ID = new UUID((string) row["UUID"]);
856 folder.Name = (string) row["name"];
857 folder.Owner = new UUID((string) row["agentID"]);
858 folder.ParentID = new UUID((string) row["parentID"]);
859 folder.Type = Convert.ToInt16(row["type"]);
860 folder.Version = Convert.ToUInt16(row["version"]);
861 return folder;
862 }
863
864 /// <summary>
865 ///
866 /// </summary>
867 /// <param name="row"></param>
868 /// <param name="folder"></param>
869 private static void fillFolderRow(DataRow row, InventoryFolderBase folder)
870 {
871 row["UUID"] = folder.ID.ToString();
872 row["name"] = folder.Name;
873 row["agentID"] = folder.Owner.ToString();
874 row["parentID"] = folder.ParentID.ToString();
875 row["type"] = folder.Type;
876 row["version"] = folder.Version;
877 }
878
879 /// <summary>
880 ///
881 /// </summary>
882 /// <param name="row"></param>
883 /// <param name="folder"></param>
884 private static void moveFolderRow(DataRow row, InventoryFolderBase folder)
885 {
886 row["UUID"] = folder.ID.ToString();
887 row["parentID"] = folder.ParentID.ToString();
888 }
889
890 public List<InventoryItemBase> fetchActiveGestures (UUID avatarID)
891 {
892 lock (ds)
893 {
894 List<InventoryItemBase> items = new List<InventoryItemBase>();
895
896 DataTable inventoryItemTable = ds.Tables["inventoryitems"];
897 string selectExp
898 = "avatarID = '" + avatarID + "' AND assetType = " + (int)AssetType.Gesture + " AND flags = 1";
899 //m_log.DebugFormat("[SQL]: sql = " + selectExp);
900 DataRow[] rows = inventoryItemTable.Select(selectExp);
901 foreach (DataRow row in rows)
902 {
903 items.Add(buildItem(row));
904 }
905 return items;
906 }
907 }
908 }
909}
diff --git a/OpenSim/Data/SQLiteNG/SQLiteRegionData.cs b/OpenSim/Data/SQLiteNG/SQLiteRegionData.cs
new file mode 100644
index 0000000..289d626
--- /dev/null
+++ b/OpenSim/Data/SQLiteNG/SQLiteRegionData.cs
@@ -0,0 +1,2321 @@
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.Sqlite;
36using OpenMetaverse;
37using OpenSim.Framework;
38using OpenSim.Region.Framework.Interfaces;
39using OpenSim.Region.Framework.Scenes;
40
41namespace OpenSim.Data.SQLiteNG
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 try
91 {
92 m_connectionString = connectionString;
93
94 ds = new DataSet("Region");
95
96 m_log.Info("[REGION DB]: Sqlite - connecting: " + connectionString);
97 m_conn = new SqliteConnection(m_connectionString);
98 m_conn.Open();
99
100 SqliteCommand primSelectCmd = new SqliteCommand(primSelect, m_conn);
101 primDa = new SqliteDataAdapter(primSelectCmd);
102
103 SqliteCommand shapeSelectCmd = new SqliteCommand(shapeSelect, m_conn);
104 shapeDa = new SqliteDataAdapter(shapeSelectCmd);
105 // SqliteCommandBuilder shapeCb = new SqliteCommandBuilder(shapeDa);
106
107 SqliteCommand itemsSelectCmd = new SqliteCommand(itemsSelect, m_conn);
108 itemsDa = new SqliteDataAdapter(itemsSelectCmd);
109
110 SqliteCommand terrainSelectCmd = new SqliteCommand(terrainSelect, m_conn);
111 terrainDa = new SqliteDataAdapter(terrainSelectCmd);
112
113 SqliteCommand landSelectCmd = new SqliteCommand(landSelect, m_conn);
114 landDa = new SqliteDataAdapter(landSelectCmd);
115
116 SqliteCommand landAccessListSelectCmd = new SqliteCommand(landAccessListSelect, m_conn);
117 landAccessListDa = new SqliteDataAdapter(landAccessListSelectCmd);
118
119 SqliteCommand regionSettingsSelectCmd = new SqliteCommand(regionSettingsSelect, m_conn);
120 regionSettingsDa = new SqliteDataAdapter(regionSettingsSelectCmd);
121 // This actually does the roll forward assembly stuff
122 Assembly assem = GetType().Assembly;
123 Migration m = new Migration(m_conn, assem, "RegionStore");
124 m.Update();
125
126 lock (ds)
127 {
128 ds.Tables.Add(createPrimTable());
129 setupPrimCommands(primDa, m_conn);
130
131 ds.Tables.Add(createShapeTable());
132 setupShapeCommands(shapeDa, m_conn);
133
134 ds.Tables.Add(createItemsTable());
135 setupItemsCommands(itemsDa, m_conn);
136
137 ds.Tables.Add(createTerrainTable());
138 setupTerrainCommands(terrainDa, m_conn);
139
140 ds.Tables.Add(createLandTable());
141 setupLandCommands(landDa, m_conn);
142
143 ds.Tables.Add(createLandAccessListTable());
144 setupLandAccessCommands(landAccessListDa, m_conn);
145
146 ds.Tables.Add(createRegionSettingsTable());
147 setupRegionSettingsCommands(regionSettingsDa, m_conn);
148
149 // WORKAROUND: This is a work around for sqlite on
150 // windows, which gets really unhappy with blob columns
151 // that have no sample data in them. At some point we
152 // need to actually find a proper way to handle this.
153 try
154 {
155 primDa.Fill(ds.Tables["prims"]);
156 }
157 catch (Exception)
158 {
159 m_log.Info("[REGION DB]: Caught fill error on prims table");
160 }
161
162 try
163 {
164 shapeDa.Fill(ds.Tables["primshapes"]);
165 }
166 catch (Exception)
167 {
168 m_log.Info("[REGION DB]: Caught fill error on primshapes table");
169 }
170
171 try
172 {
173 terrainDa.Fill(ds.Tables["terrain"]);
174 }
175 catch (Exception)
176 {
177 m_log.Info("[REGION DB]: Caught fill error on terrain table");
178 }
179
180 try
181 {
182 landDa.Fill(ds.Tables["land"]);
183 }
184 catch (Exception)
185 {
186 m_log.Info("[REGION DB]: Caught fill error on land table");
187 }
188
189 try
190 {
191 landAccessListDa.Fill(ds.Tables["landaccesslist"]);
192 }
193 catch (Exception)
194 {
195 m_log.Info("[REGION DB]: Caught fill error on landaccesslist table");
196 }
197
198 try
199 {
200 regionSettingsDa.Fill(ds.Tables["regionsettings"]);
201 }
202 catch (Exception)
203 {
204 m_log.Info("[REGION DB]: Caught fill error on regionsettings table");
205 }
206
207 // We have to create a data set mapping for every table, otherwise the IDataAdaptor.Update() will not populate rows with values!
208 // Not sure exactly why this is - this kind of thing was not necessary before - justincc 20100409
209 // Possibly because we manually set up our own DataTables before connecting to the database
210 CreateDataSetMapping(primDa, "prims");
211 CreateDataSetMapping(shapeDa, "primshapes");
212 CreateDataSetMapping(itemsDa, "primitems");
213 CreateDataSetMapping(terrainDa, "terrain");
214 CreateDataSetMapping(landDa, "land");
215 CreateDataSetMapping(landAccessListDa, "landaccesslist");
216 CreateDataSetMapping(regionSettingsDa, "regionsettings");
217 }
218 }
219 catch (Exception e)
220 {
221 m_log.Error(e);
222 Environment.Exit(23);
223 }
224
225 return;
226 }
227
228 public void Dispose()
229 {
230 if (m_conn != null)
231 {
232 m_conn.Close();
233 m_conn = null;
234 }
235 if (ds != null)
236 {
237 ds.Dispose();
238 ds = null;
239 }
240 if (primDa != null)
241 {
242 primDa.Dispose();
243 primDa = null;
244 }
245 if (shapeDa != null)
246 {
247 shapeDa.Dispose();
248 shapeDa = null;
249 }
250 if (itemsDa != null)
251 {
252 itemsDa.Dispose();
253 itemsDa = null;
254 }
255 if (terrainDa != null)
256 {
257 terrainDa.Dispose();
258 terrainDa = null;
259 }
260 if (landDa != null)
261 {
262 landDa.Dispose();
263 landDa = null;
264 }
265 if (landAccessListDa != null)
266 {
267 landAccessListDa.Dispose();
268 landAccessListDa = null;
269 }
270 if (regionSettingsDa != null)
271 {
272 regionSettingsDa.Dispose();
273 regionSettingsDa = null;
274 }
275 }
276
277 public void StoreRegionSettings(RegionSettings rs)
278 {
279 lock (ds)
280 {
281 DataTable regionsettings = ds.Tables["regionsettings"];
282
283 DataRow settingsRow = regionsettings.Rows.Find(rs.RegionUUID.ToString());
284 if (settingsRow == null)
285 {
286 settingsRow = regionsettings.NewRow();
287 fillRegionSettingsRow(settingsRow, rs);
288 regionsettings.Rows.Add(settingsRow);
289 }
290 else
291 {
292 fillRegionSettingsRow(settingsRow, rs);
293 }
294
295 Commit();
296 }
297 }
298 public RegionLightShareData LoadRegionWindlightSettings(UUID regionUUID)
299 {
300 //This connector doesn't support the windlight module yet
301 //Return default LL windlight settings
302 return new RegionLightShareData();
303 }
304 public void StoreRegionWindlightSettings(RegionLightShareData wl)
305 {
306 //This connector doesn't support the windlight module yet
307 }
308 public RegionSettings LoadRegionSettings(UUID regionUUID)
309 {
310 lock (ds)
311 {
312 DataTable regionsettings = ds.Tables["regionsettings"];
313
314 string searchExp = "regionUUID = '" + regionUUID.ToString() + "'";
315 DataRow[] rawsettings = regionsettings.Select(searchExp);
316 if (rawsettings.Length == 0)
317 {
318 RegionSettings rs = new RegionSettings();
319 rs.RegionUUID = regionUUID;
320 rs.OnSave += StoreRegionSettings;
321
322 StoreRegionSettings(rs);
323
324 return rs;
325 }
326 DataRow row = rawsettings[0];
327
328 RegionSettings newSettings = buildRegionSettings(row);
329 newSettings.OnSave += StoreRegionSettings;
330
331 return newSettings;
332 }
333 }
334
335 /// <summary>
336 /// Adds an object into region storage
337 /// </summary>
338 /// <param name="obj">the object</param>
339 /// <param name="regionUUID">the region UUID</param>
340 public void StoreObject(SceneObjectGroup obj, UUID regionUUID)
341 {
342 uint flags = obj.RootPart.GetEffectiveObjectFlags();
343
344 // Eligibility check
345 //
346 if ((flags & (uint)PrimFlags.Temporary) != 0)
347 return;
348 if ((flags & (uint)PrimFlags.TemporaryOnRez) != 0)
349 return;
350
351 lock (ds)
352 {
353 foreach (SceneObjectPart prim in obj.Children.Values)
354 {
355// m_log.Info("[REGION DB]: Adding obj: " + obj.UUID + " to region: " + regionUUID);
356 addPrim(prim, obj.UUID, regionUUID);
357 }
358 }
359
360 Commit();
361 // m_log.Info("[Dump of prims]: " + ds.GetXml());
362 }
363
364 /// <summary>
365 /// Removes an object from region storage
366 /// </summary>
367 /// <param name="obj">the object</param>
368 /// <param name="regionUUID">the region UUID</param>
369 public void RemoveObject(UUID obj, UUID regionUUID)
370 {
371 // m_log.InfoFormat("[REGION DB]: Removing obj: {0} from region: {1}", obj.Guid, regionUUID);
372
373 DataTable prims = ds.Tables["prims"];
374 DataTable shapes = ds.Tables["primshapes"];
375
376 string selectExp = "SceneGroupID = '" + obj + "' and RegionUUID = '" + regionUUID + "'";
377 lock (ds)
378 {
379 DataRow[] primRows = prims.Select(selectExp);
380 foreach (DataRow row in primRows)
381 {
382 // Remove shape rows
383 UUID uuid = new UUID((string) row["UUID"]);
384 DataRow shapeRow = shapes.Rows.Find(uuid.ToString());
385 if (shapeRow != null)
386 {
387 shapeRow.Delete();
388 }
389
390 RemoveItems(uuid);
391
392 // Remove prim row
393 row.Delete();
394 }
395 }
396
397 Commit();
398 }
399
400 /// <summary>
401 /// Remove all persisted items of the given prim.
402 /// The caller must acquire the necessrary synchronization locks and commit or rollback changes.
403 /// </summary>
404 /// <param name="uuid">The item UUID</param>
405 private void RemoveItems(UUID uuid)
406 {
407 DataTable items = ds.Tables["primitems"];
408
409 String sql = String.Format("primID = '{0}'", uuid);
410 DataRow[] itemRows = items.Select(sql);
411
412 foreach (DataRow itemRow in itemRows)
413 {
414 itemRow.Delete();
415 }
416 }
417
418 /// <summary>
419 /// Load persisted objects from region storage.
420 /// </summary>
421 /// <param name="regionUUID">The region UUID</param>
422 /// <returns>List of loaded groups</returns>
423 public List<SceneObjectGroup> LoadObjects(UUID regionUUID)
424 {
425 Dictionary<UUID, SceneObjectGroup> createdObjects = new Dictionary<UUID, SceneObjectGroup>();
426
427 List<SceneObjectGroup> retvals = new List<SceneObjectGroup>();
428
429 DataTable prims = ds.Tables["prims"];
430 DataTable shapes = ds.Tables["primshapes"];
431
432 string byRegion = "RegionUUID = '" + regionUUID + "'";
433
434 lock (ds)
435 {
436 DataRow[] primsForRegion = prims.Select(byRegion);
437 m_log.Info("[REGION DB]: Loaded " + primsForRegion.Length + " prims for region: " + regionUUID);
438
439 // First, create all groups
440 foreach (DataRow primRow in primsForRegion)
441 {
442 try
443 {
444 SceneObjectPart prim = null;
445
446 string uuid = (string) primRow["UUID"];
447 string objID = (string) primRow["SceneGroupID"];
448
449 if (uuid == objID) //is new SceneObjectGroup ?
450 {
451 prim = buildPrim(primRow);
452 DataRow shapeRow = shapes.Rows.Find(prim.UUID.ToString());
453 if (shapeRow != null)
454 {
455 prim.Shape = buildShape(shapeRow);
456 }
457 else
458 {
459 m_log.Info(
460 "[REGION DB]: No shape found for prim in storage, so setting default box shape");
461 prim.Shape = PrimitiveBaseShape.Default;
462 }
463
464 SceneObjectGroup group = new SceneObjectGroup(prim);
465 createdObjects.Add(group.UUID, group);
466 retvals.Add(group);
467 LoadItems(prim);
468 }
469 }
470 catch (Exception e)
471 {
472 m_log.Error("[REGION DB]: Failed create prim object in new group, exception and data follows");
473 m_log.Info("[REGION DB]: " + e.ToString());
474 foreach (DataColumn col in prims.Columns)
475 {
476 m_log.Info("[REGION DB]: Col: " + col.ColumnName + " => " + primRow[col]);
477 }
478 }
479 }
480
481 // Now fill the groups with part data
482 foreach (DataRow primRow in primsForRegion)
483 {
484 try
485 {
486 SceneObjectPart prim = null;
487
488 string uuid = (string) primRow["UUID"];
489 string objID = (string) primRow["SceneGroupID"];
490 if (uuid != objID) //is new SceneObjectGroup ?
491 {
492 prim = buildPrim(primRow);
493 DataRow shapeRow = shapes.Rows.Find(prim.UUID.ToString());
494 if (shapeRow != null)
495 {
496 prim.Shape = buildShape(shapeRow);
497 }
498 else
499 {
500 m_log.Warn(
501 "[REGION DB]: No shape found for prim in storage, so setting default box shape");
502 prim.Shape = PrimitiveBaseShape.Default;
503 }
504
505 createdObjects[new UUID(objID)].AddPart(prim);
506 LoadItems(prim);
507 }
508 }
509 catch (Exception e)
510 {
511 m_log.Error("[REGION DB]: Failed create prim object in group, exception and data follows");
512 m_log.Info("[REGION DB]: " + e.ToString());
513 foreach (DataColumn col in prims.Columns)
514 {
515 m_log.Info("[REGION DB]: Col: " + col.ColumnName + " => " + primRow[col]);
516 }
517 }
518 }
519 }
520 return retvals;
521 }
522
523 /// <summary>
524 /// Load in a prim's persisted inventory.
525 /// </summary>
526 /// <param name="prim">the prim</param>
527 private void LoadItems(SceneObjectPart prim)
528 {
529 //m_log.DebugFormat("[DATASTORE]: Loading inventory for {0}, {1}", prim.Name, prim.UUID);
530
531 DataTable dbItems = ds.Tables["primitems"];
532 String sql = String.Format("primID = '{0}'", prim.UUID.ToString());
533 DataRow[] dbItemRows = dbItems.Select(sql);
534 IList<TaskInventoryItem> inventory = new List<TaskInventoryItem>();
535
536 foreach (DataRow row in dbItemRows)
537 {
538 TaskInventoryItem item = buildItem(row);
539 inventory.Add(item);
540
541 //m_log.DebugFormat("[DATASTORE]: Restored item {0}, {1}", item.Name, item.ItemID);
542 }
543
544 prim.Inventory.RestoreInventoryItems(inventory);
545 }
546
547 /// <summary>
548 /// Store a terrain revision in region storage
549 /// </summary>
550 /// <param name="ter">terrain heightfield</param>
551 /// <param name="regionID">region UUID</param>
552 public void StoreTerrain(double[,] ter, UUID regionID)
553 {
554 lock (ds)
555 {
556 int revision = Util.UnixTimeSinceEpoch();
557
558 // This is added to get rid of the infinitely growing
559 // terrain databases which negatively impact on SQLite
560 // over time. Before reenabling this feature there
561 // needs to be a limitter put on the number of
562 // revisions in the database, as this old
563 // implementation is a DOS attack waiting to happen.
564
565 using (
566 SqliteCommand cmd =
567 new SqliteCommand("delete from terrain where RegionUUID=:RegionUUID and Revision <= :Revision",
568 m_conn))
569 {
570 cmd.Parameters.Add(new SqliteParameter(":RegionUUID", regionID.ToString()));
571 cmd.Parameters.Add(new SqliteParameter(":Revision", revision));
572 cmd.ExecuteNonQuery();
573 }
574
575 // the following is an work around for .NET. The perf
576 // issues associated with it aren't as bad as you think.
577 m_log.Info("[REGION DB]: Storing terrain revision r" + revision.ToString());
578 String sql = "insert into terrain(RegionUUID, Revision, Heightfield)" +
579 " values(:RegionUUID, :Revision, :Heightfield)";
580
581 using (SqliteCommand cmd = new SqliteCommand(sql, m_conn))
582 {
583 cmd.Parameters.Add(new SqliteParameter(":RegionUUID", regionID.ToString()));
584 cmd.Parameters.Add(new SqliteParameter(":Revision", revision));
585 cmd.Parameters.Add(new SqliteParameter(":Heightfield", serializeTerrain(ter)));
586 cmd.ExecuteNonQuery();
587 }
588 }
589 }
590
591 /// <summary>
592 /// Load the latest terrain revision from region storage
593 /// </summary>
594 /// <param name="regionID">the region UUID</param>
595 /// <returns>Heightfield data</returns>
596 public double[,] LoadTerrain(UUID regionID)
597 {
598 lock (ds)
599 {
600 double[,] terret = new double[(int)Constants.RegionSize, (int)Constants.RegionSize];
601 terret.Initialize();
602
603 String sql = "select RegionUUID, Revision, Heightfield from terrain" +
604 " where RegionUUID=:RegionUUID order by Revision desc";
605
606 using (SqliteCommand cmd = new SqliteCommand(sql, m_conn))
607 {
608 cmd.Parameters.Add(new SqliteParameter(":RegionUUID", regionID.ToString()));
609
610 using (IDataReader row = cmd.ExecuteReader())
611 {
612 int rev = 0;
613 if (row.Read())
614 {
615 // TODO: put this into a function
616 using (MemoryStream str = new MemoryStream((byte[])row["Heightfield"]))
617 {
618 using (BinaryReader br = new BinaryReader(str))
619 {
620 for (int x = 0; x < (int)Constants.RegionSize; x++)
621 {
622 for (int y = 0; y < (int)Constants.RegionSize; y++)
623 {
624 terret[x, y] = br.ReadDouble();
625 }
626 }
627 }
628 }
629 rev = Convert.ToInt32(row["Revision"]);
630 }
631 else
632 {
633 m_log.Info("[REGION DB]: No terrain found for region");
634 return null;
635 }
636
637 m_log.Info("[REGION DB]: Loaded terrain revision r" + rev.ToString());
638 }
639 }
640 return terret;
641 }
642 }
643
644 /// <summary>
645 ///
646 /// </summary>
647 /// <param name="globalID"></param>
648 public void RemoveLandObject(UUID globalID)
649 {
650 lock (ds)
651 {
652 // Can't use blanket SQL statements when using SqlAdapters unless you re-read the data into the adapter
653 // after you're done.
654 // replaced below code with the SqliteAdapter version.
655 //using (SqliteCommand cmd = new SqliteCommand("delete from land where UUID=:UUID", m_conn))
656 //{
657 // cmd.Parameters.Add(new SqliteParameter(":UUID", globalID.ToString()));
658 // cmd.ExecuteNonQuery();
659 //}
660
661 //using (SqliteCommand cmd = new SqliteCommand("delete from landaccesslist where LandUUID=:UUID", m_conn))
662 //{
663 // cmd.Parameters.Add(new SqliteParameter(":UUID", globalID.ToString()));
664 // cmd.ExecuteNonQuery();
665 //}
666
667 DataTable land = ds.Tables["land"];
668 DataTable landaccesslist = ds.Tables["landaccesslist"];
669 DataRow landRow = land.Rows.Find(globalID.ToString());
670 if (landRow != null)
671 {
672 land.Rows.Remove(landRow);
673 }
674 List<DataRow> rowsToDelete = new List<DataRow>();
675 foreach (DataRow rowToCheck in landaccesslist.Rows)
676 {
677 if (rowToCheck["LandUUID"].ToString() == globalID.ToString())
678 rowsToDelete.Add(rowToCheck);
679 }
680 for (int iter = 0; iter < rowsToDelete.Count; iter++)
681 {
682 landaccesslist.Rows.Remove(rowsToDelete[iter]);
683 }
684
685
686 }
687 Commit();
688 }
689
690 /// <summary>
691 ///
692 /// </summary>
693 /// <param name="parcel"></param>
694 public void StoreLandObject(ILandObject parcel)
695 {
696 lock (ds)
697 {
698 DataTable land = ds.Tables["land"];
699 DataTable landaccesslist = ds.Tables["landaccesslist"];
700
701 DataRow landRow = land.Rows.Find(parcel.LandData.GlobalID.ToString());
702 if (landRow == null)
703 {
704 landRow = land.NewRow();
705 fillLandRow(landRow, parcel.LandData, parcel.RegionUUID);
706 land.Rows.Add(landRow);
707 }
708 else
709 {
710 fillLandRow(landRow, parcel.LandData, parcel.RegionUUID);
711 }
712
713 // I know this caused someone issues before, but OpenSim is unusable if we leave this stuff around
714 //using (SqliteCommand cmd = new SqliteCommand("delete from landaccesslist where LandUUID=:LandUUID", m_conn))
715 //{
716 // cmd.Parameters.Add(new SqliteParameter(":LandUUID", parcel.LandData.GlobalID.ToString()));
717 // cmd.ExecuteNonQuery();
718
719// }
720
721 // This is the slower.. but more appropriate thing to do
722
723 // We can't modify the table with direct queries before calling Commit() and re-filling them.
724 List<DataRow> rowsToDelete = new List<DataRow>();
725 foreach (DataRow rowToCheck in landaccesslist.Rows)
726 {
727 if (rowToCheck["LandUUID"].ToString() == parcel.LandData.GlobalID.ToString())
728 rowsToDelete.Add(rowToCheck);
729 }
730 for (int iter = 0; iter < rowsToDelete.Count; iter++)
731 {
732 landaccesslist.Rows.Remove(rowsToDelete[iter]);
733 }
734 rowsToDelete.Clear();
735 foreach (ParcelManager.ParcelAccessEntry entry in parcel.LandData.ParcelAccessList)
736 {
737 DataRow newAccessRow = landaccesslist.NewRow();
738 fillLandAccessRow(newAccessRow, entry, parcel.LandData.GlobalID);
739 landaccesslist.Rows.Add(newAccessRow);
740 }
741 }
742
743 Commit();
744 }
745
746 /// <summary>
747 ///
748 /// </summary>
749 /// <param name="regionUUID"></param>
750 /// <returns></returns>
751 public List<LandData> LoadLandObjects(UUID regionUUID)
752 {
753 List<LandData> landDataForRegion = new List<LandData>();
754 lock (ds)
755 {
756 DataTable land = ds.Tables["land"];
757 DataTable landaccesslist = ds.Tables["landaccesslist"];
758 string searchExp = "RegionUUID = '" + regionUUID + "'";
759 DataRow[] rawDataForRegion = land.Select(searchExp);
760 foreach (DataRow rawDataLand in rawDataForRegion)
761 {
762 LandData newLand = buildLandData(rawDataLand);
763 string accessListSearchExp = "LandUUID = '" + newLand.GlobalID + "'";
764 DataRow[] rawDataForLandAccessList = landaccesslist.Select(accessListSearchExp);
765 foreach (DataRow rawDataLandAccess in rawDataForLandAccessList)
766 {
767 newLand.ParcelAccessList.Add(buildLandAccessData(rawDataLandAccess));
768 }
769
770 landDataForRegion.Add(newLand);
771 }
772 }
773 return landDataForRegion;
774 }
775
776 /// <summary>
777 ///
778 /// </summary>
779 public void Commit()
780 {
781 m_log.Debug("[SQLITE]: Starting commit");
782 lock (ds)
783 {
784 primDa.Update(ds, "prims");
785 shapeDa.Update(ds, "primshapes");
786
787 itemsDa.Update(ds, "primitems");
788
789 terrainDa.Update(ds, "terrain");
790 landDa.Update(ds, "land");
791 landAccessListDa.Update(ds, "landaccesslist");
792 try
793 {
794 regionSettingsDa.Update(ds, "regionsettings");
795 }
796 catch (SqliteException SqlEx)
797 {
798 throw new Exception(
799 "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!",
800 SqlEx);
801 }
802 ds.AcceptChanges();
803 }
804 }
805
806 /// <summary>
807 /// See <see cref="Commit"/>
808 /// </summary>
809 public void Shutdown()
810 {
811 Commit();
812 }
813
814 /***********************************************************************
815 *
816 * Database Definition Functions
817 *
818 * This should be db agnostic as we define them in ADO.NET terms
819 *
820 **********************************************************************/
821
822 protected void CreateDataSetMapping(IDataAdapter da, string tableName)
823 {
824 ITableMapping dbMapping = da.TableMappings.Add(tableName, tableName);
825 foreach (DataColumn col in ds.Tables[tableName].Columns)
826 {
827 dbMapping.ColumnMappings.Add(col.ColumnName, col.ColumnName);
828 }
829 }
830
831 /// <summary>
832 ///
833 /// </summary>
834 /// <param name="dt"></param>
835 /// <param name="name"></param>
836 /// <param name="type"></param>
837 private static void createCol(DataTable dt, string name, Type type)
838 {
839 DataColumn col = new DataColumn(name, type);
840 dt.Columns.Add(col);
841 }
842
843 /// <summary>
844 /// Creates the "terrain" table
845 /// </summary>
846 /// <returns>terrain table DataTable</returns>
847 private static DataTable createTerrainTable()
848 {
849 DataTable terrain = new DataTable("terrain");
850
851 createCol(terrain, "RegionUUID", typeof (String));
852 createCol(terrain, "Revision", typeof (Int32));
853 createCol(terrain, "Heightfield", typeof (Byte[]));
854
855 return terrain;
856 }
857
858 /// <summary>
859 /// Creates the "prims" table
860 /// </summary>
861 /// <returns>prim table DataTable</returns>
862 private static DataTable createPrimTable()
863 {
864 DataTable prims = new DataTable("prims");
865
866 createCol(prims, "UUID", typeof (String));
867 createCol(prims, "RegionUUID", typeof (String));
868 createCol(prims, "CreationDate", typeof (Int32));
869 createCol(prims, "Name", typeof (String));
870 createCol(prims, "SceneGroupID", typeof (String));
871 // various text fields
872 createCol(prims, "Text", typeof (String));
873 createCol(prims, "ColorR", typeof (Int32));
874 createCol(prims, "ColorG", typeof (Int32));
875 createCol(prims, "ColorB", typeof (Int32));
876 createCol(prims, "ColorA", typeof (Int32));
877 createCol(prims, "Description", typeof (String));
878 createCol(prims, "SitName", typeof (String));
879 createCol(prims, "TouchName", typeof (String));
880 // permissions
881 createCol(prims, "ObjectFlags", typeof (Int32));
882 createCol(prims, "CreatorID", typeof (String));
883 createCol(prims, "OwnerID", typeof (String));
884 createCol(prims, "GroupID", typeof (String));
885 createCol(prims, "LastOwnerID", typeof (String));
886 createCol(prims, "OwnerMask", typeof (Int32));
887 createCol(prims, "NextOwnerMask", typeof (Int32));
888 createCol(prims, "GroupMask", typeof (Int32));
889 createCol(prims, "EveryoneMask", typeof (Int32));
890 createCol(prims, "BaseMask", typeof (Int32));
891 // vectors
892 createCol(prims, "PositionX", typeof (Double));
893 createCol(prims, "PositionY", typeof (Double));
894 createCol(prims, "PositionZ", typeof (Double));
895 createCol(prims, "GroupPositionX", typeof (Double));
896 createCol(prims, "GroupPositionY", typeof (Double));
897 createCol(prims, "GroupPositionZ", typeof (Double));
898 createCol(prims, "VelocityX", typeof (Double));
899 createCol(prims, "VelocityY", typeof (Double));
900 createCol(prims, "VelocityZ", typeof (Double));
901 createCol(prims, "AngularVelocityX", typeof (Double));
902 createCol(prims, "AngularVelocityY", typeof (Double));
903 createCol(prims, "AngularVelocityZ", typeof (Double));
904 createCol(prims, "AccelerationX", typeof (Double));
905 createCol(prims, "AccelerationY", typeof (Double));
906 createCol(prims, "AccelerationZ", typeof (Double));
907 // quaternions
908 createCol(prims, "RotationX", typeof (Double));
909 createCol(prims, "RotationY", typeof (Double));
910 createCol(prims, "RotationZ", typeof (Double));
911 createCol(prims, "RotationW", typeof (Double));
912
913 // sit target
914 createCol(prims, "SitTargetOffsetX", typeof (Double));
915 createCol(prims, "SitTargetOffsetY", typeof (Double));
916 createCol(prims, "SitTargetOffsetZ", typeof (Double));
917
918 createCol(prims, "SitTargetOrientW", typeof (Double));
919 createCol(prims, "SitTargetOrientX", typeof (Double));
920 createCol(prims, "SitTargetOrientY", typeof (Double));
921 createCol(prims, "SitTargetOrientZ", typeof (Double));
922
923 createCol(prims, "PayPrice", typeof(Int32));
924 createCol(prims, "PayButton1", typeof(Int32));
925 createCol(prims, "PayButton2", typeof(Int32));
926 createCol(prims, "PayButton3", typeof(Int32));
927 createCol(prims, "PayButton4", typeof(Int32));
928
929 createCol(prims, "LoopedSound", typeof(String));
930 createCol(prims, "LoopedSoundGain", typeof(Double));
931 createCol(prims, "TextureAnimation", typeof(String));
932 createCol(prims, "ParticleSystem", typeof(String));
933
934 createCol(prims, "OmegaX", typeof(Double));
935 createCol(prims, "OmegaY", typeof(Double));
936 createCol(prims, "OmegaZ", typeof(Double));
937
938 createCol(prims, "CameraEyeOffsetX", typeof(Double));
939 createCol(prims, "CameraEyeOffsetY", typeof(Double));
940 createCol(prims, "CameraEyeOffsetZ", typeof(Double));
941
942 createCol(prims, "CameraAtOffsetX", typeof(Double));
943 createCol(prims, "CameraAtOffsetY", typeof(Double));
944 createCol(prims, "CameraAtOffsetZ", typeof(Double));
945
946 createCol(prims, "ForceMouselook", typeof(Int16));
947
948 createCol(prims, "ScriptAccessPin", typeof(Int32));
949
950 createCol(prims, "AllowedDrop", typeof(Int16));
951 createCol(prims, "DieAtEdge", typeof(Int16));
952
953 createCol(prims, "SalePrice", typeof(Int32));
954 createCol(prims, "SaleType", typeof(Int16));
955
956 // click action
957 createCol(prims, "ClickAction", typeof (Byte));
958
959 createCol(prims, "Material", typeof(Byte));
960
961 createCol(prims, "CollisionSound", typeof(String));
962 createCol(prims, "CollisionSoundVolume", typeof(Double));
963
964 createCol(prims, "VolumeDetect", typeof(Int16));
965
966 // Add in contraints
967 prims.PrimaryKey = new DataColumn[] {prims.Columns["UUID"]};
968
969 return prims;
970 }
971
972 /// <summary>
973 /// Creates "primshapes" table
974 /// </summary>
975 /// <returns>shape table DataTable</returns>
976 private static DataTable createShapeTable()
977 {
978 DataTable shapes = new DataTable("primshapes");
979 createCol(shapes, "UUID", typeof (String));
980 // shape is an enum
981 createCol(shapes, "Shape", typeof (Int32));
982 // vectors
983 createCol(shapes, "ScaleX", typeof (Double));
984 createCol(shapes, "ScaleY", typeof (Double));
985 createCol(shapes, "ScaleZ", typeof (Double));
986 // paths
987 createCol(shapes, "PCode", typeof (Int32));
988 createCol(shapes, "PathBegin", typeof (Int32));
989 createCol(shapes, "PathEnd", typeof (Int32));
990 createCol(shapes, "PathScaleX", typeof (Int32));
991 createCol(shapes, "PathScaleY", typeof (Int32));
992 createCol(shapes, "PathShearX", typeof (Int32));
993 createCol(shapes, "PathShearY", typeof (Int32));
994 createCol(shapes, "PathSkew", typeof (Int32));
995 createCol(shapes, "PathCurve", typeof (Int32));
996 createCol(shapes, "PathRadiusOffset", typeof (Int32));
997 createCol(shapes, "PathRevolutions", typeof (Int32));
998 createCol(shapes, "PathTaperX", typeof (Int32));
999 createCol(shapes, "PathTaperY", typeof (Int32));
1000 createCol(shapes, "PathTwist", typeof (Int32));
1001 createCol(shapes, "PathTwistBegin", typeof (Int32));
1002 // profile
1003 createCol(shapes, "ProfileBegin", typeof (Int32));
1004 createCol(shapes, "ProfileEnd", typeof (Int32));
1005 createCol(shapes, "ProfileCurve", typeof (Int32));
1006 createCol(shapes, "ProfileHollow", typeof (Int32));
1007 createCol(shapes, "State", typeof(Int32));
1008 // text TODO: this isn't right, but I'm not sure the right
1009 // way to specify this as a blob atm
1010 createCol(shapes, "Texture", typeof (Byte[]));
1011 createCol(shapes, "ExtraParams", typeof (Byte[]));
1012
1013 shapes.PrimaryKey = new DataColumn[] {shapes.Columns["UUID"]};
1014
1015 return shapes;
1016 }
1017
1018 /// <summary>
1019 /// creates "primitems" table
1020 /// </summary>
1021 /// <returns>item table DataTable</returns>
1022 private static DataTable createItemsTable()
1023 {
1024 DataTable items = new DataTable("primitems");
1025
1026 createCol(items, "itemID", typeof (String));
1027 createCol(items, "primID", typeof (String));
1028 createCol(items, "assetID", typeof (String));
1029 createCol(items, "parentFolderID", typeof (String));
1030
1031 createCol(items, "invType", typeof (Int32));
1032 createCol(items, "assetType", typeof (Int32));
1033
1034 createCol(items, "name", typeof (String));
1035 createCol(items, "description", typeof (String));
1036
1037 createCol(items, "creationDate", typeof (Int64));
1038 createCol(items, "creatorID", typeof (String));
1039 createCol(items, "ownerID", typeof (String));
1040 createCol(items, "lastOwnerID", typeof (String));
1041 createCol(items, "groupID", typeof (String));
1042
1043 createCol(items, "nextPermissions", typeof (UInt32));
1044 createCol(items, "currentPermissions", typeof (UInt32));
1045 createCol(items, "basePermissions", typeof (UInt32));
1046 createCol(items, "everyonePermissions", typeof (UInt32));
1047 createCol(items, "groupPermissions", typeof (UInt32));
1048 createCol(items, "flags", typeof (UInt32));
1049
1050 items.PrimaryKey = new DataColumn[] { items.Columns["itemID"] };
1051
1052 return items;
1053 }
1054
1055 /// <summary>
1056 /// Creates "land" table
1057 /// </summary>
1058 /// <returns>land table DataTable</returns>
1059 private static DataTable createLandTable()
1060 {
1061 DataTable land = new DataTable("land");
1062 createCol(land, "UUID", typeof (String));
1063 createCol(land, "RegionUUID", typeof (String));
1064 createCol(land, "LocalLandID", typeof (UInt32));
1065
1066 // Bitmap is a byte[512]
1067 createCol(land, "Bitmap", typeof (Byte[]));
1068
1069 createCol(land, "Name", typeof (String));
1070 createCol(land, "Desc", typeof (String));
1071 createCol(land, "OwnerUUID", typeof (String));
1072 createCol(land, "IsGroupOwned", typeof (Boolean));
1073 createCol(land, "Area", typeof (Int32));
1074 createCol(land, "AuctionID", typeof (Int32)); //Unemplemented
1075 createCol(land, "Category", typeof (Int32)); //Enum OpenMetaverse.Parcel.ParcelCategory
1076 createCol(land, "ClaimDate", typeof (Int32));
1077 createCol(land, "ClaimPrice", typeof (Int32));
1078 createCol(land, "GroupUUID", typeof (string));
1079 createCol(land, "SalePrice", typeof (Int32));
1080 createCol(land, "LandStatus", typeof (Int32)); //Enum. OpenMetaverse.Parcel.ParcelStatus
1081 createCol(land, "LandFlags", typeof (UInt32));
1082 createCol(land, "LandingType", typeof (Byte));
1083 createCol(land, "MediaAutoScale", typeof (Byte));
1084 createCol(land, "MediaTextureUUID", typeof (String));
1085 createCol(land, "MediaURL", typeof (String));
1086 createCol(land, "MusicURL", typeof (String));
1087 createCol(land, "PassHours", typeof (Double));
1088 createCol(land, "PassPrice", typeof (UInt32));
1089 createCol(land, "SnapshotUUID", typeof (String));
1090 createCol(land, "UserLocationX", typeof (Double));
1091 createCol(land, "UserLocationY", typeof (Double));
1092 createCol(land, "UserLocationZ", typeof (Double));
1093 createCol(land, "UserLookAtX", typeof (Double));
1094 createCol(land, "UserLookAtY", typeof (Double));
1095 createCol(land, "UserLookAtZ", typeof (Double));
1096 createCol(land, "AuthbuyerID", typeof(String));
1097 createCol(land, "OtherCleanTime", typeof(Int32));
1098 createCol(land, "Dwell", typeof(Int32));
1099
1100 land.PrimaryKey = new DataColumn[] {land.Columns["UUID"]};
1101
1102 return land;
1103 }
1104
1105 /// <summary>
1106 /// create "landaccesslist" table
1107 /// </summary>
1108 /// <returns>Landacceslist DataTable</returns>
1109 private static DataTable createLandAccessListTable()
1110 {
1111 DataTable landaccess = new DataTable("landaccesslist");
1112 createCol(landaccess, "LandUUID", typeof (String));
1113 createCol(landaccess, "AccessUUID", typeof (String));
1114 createCol(landaccess, "Flags", typeof (UInt32));
1115
1116 return landaccess;
1117 }
1118
1119 private static DataTable createRegionSettingsTable()
1120 {
1121 DataTable regionsettings = new DataTable("regionsettings");
1122 createCol(regionsettings, "regionUUID", typeof(String));
1123 createCol(regionsettings, "block_terraform", typeof (Int32));
1124 createCol(regionsettings, "block_fly", typeof (Int32));
1125 createCol(regionsettings, "allow_damage", typeof (Int32));
1126 createCol(regionsettings, "restrict_pushing", typeof (Int32));
1127 createCol(regionsettings, "allow_land_resell", typeof (Int32));
1128 createCol(regionsettings, "allow_land_join_divide", typeof (Int32));
1129 createCol(regionsettings, "block_show_in_search", typeof (Int32));
1130 createCol(regionsettings, "agent_limit", typeof (Int32));
1131 createCol(regionsettings, "object_bonus", typeof (Double));
1132 createCol(regionsettings, "maturity", typeof (Int32));
1133 createCol(regionsettings, "disable_scripts", typeof (Int32));
1134 createCol(regionsettings, "disable_collisions", typeof (Int32));
1135 createCol(regionsettings, "disable_physics", typeof (Int32));
1136 createCol(regionsettings, "terrain_texture_1", typeof(String));
1137 createCol(regionsettings, "terrain_texture_2", typeof(String));
1138 createCol(regionsettings, "terrain_texture_3", typeof(String));
1139 createCol(regionsettings, "terrain_texture_4", typeof(String));
1140 createCol(regionsettings, "elevation_1_nw", typeof (Double));
1141 createCol(regionsettings, "elevation_2_nw", typeof (Double));
1142 createCol(regionsettings, "elevation_1_ne", typeof (Double));
1143 createCol(regionsettings, "elevation_2_ne", typeof (Double));
1144 createCol(regionsettings, "elevation_1_se", typeof (Double));
1145 createCol(regionsettings, "elevation_2_se", typeof (Double));
1146 createCol(regionsettings, "elevation_1_sw", typeof (Double));
1147 createCol(regionsettings, "elevation_2_sw", typeof (Double));
1148 createCol(regionsettings, "water_height", typeof (Double));
1149 createCol(regionsettings, "terrain_raise_limit", typeof (Double));
1150 createCol(regionsettings, "terrain_lower_limit", typeof (Double));
1151 createCol(regionsettings, "use_estate_sun", typeof (Int32));
1152 createCol(regionsettings, "sandbox", typeof (Int32));
1153 createCol(regionsettings, "sunvectorx",typeof (Double));
1154 createCol(regionsettings, "sunvectory",typeof (Double));
1155 createCol(regionsettings, "sunvectorz",typeof (Double));
1156 createCol(regionsettings, "fixed_sun", typeof (Int32));
1157 createCol(regionsettings, "sun_position", typeof (Double));
1158 createCol(regionsettings, "covenant", typeof(String));
1159 regionsettings.PrimaryKey = new DataColumn[] { regionsettings.Columns["regionUUID"] };
1160 return regionsettings;
1161 }
1162
1163 /***********************************************************************
1164 *
1165 * Convert between ADO.NET <=> OpenSim Objects
1166 *
1167 * These should be database independant
1168 *
1169 **********************************************************************/
1170
1171 /// <summary>
1172 ///
1173 /// </summary>
1174 /// <param name="row"></param>
1175 /// <returns></returns>
1176 private SceneObjectPart buildPrim(DataRow row)
1177 {
1178 // Code commented. Uncomment to test the unit test inline.
1179
1180 // The unit test mentions this commented code for the purposes
1181 // of debugging a unit test failure
1182
1183 // SceneObjectGroup sog = new SceneObjectGroup();
1184 // SceneObjectPart sop = new SceneObjectPart();
1185 // sop.LocalId = 1;
1186 // sop.Name = "object1";
1187 // sop.Description = "object1";
1188 // sop.Text = "";
1189 // sop.SitName = "";
1190 // sop.TouchName = "";
1191 // sop.UUID = UUID.Random();
1192 // sop.Shape = PrimitiveBaseShape.Default;
1193 // sog.SetRootPart(sop);
1194 // Add breakpoint in above line. Check sop fields.
1195
1196 // TODO: this doesn't work yet because something more
1197 // interesting has to be done to actually get these values
1198 // back out. Not enough time to figure it out yet.
1199
1200 SceneObjectPart prim = new SceneObjectPart();
1201 prim.UUID = new UUID((String) row["UUID"]);
1202 // explicit conversion of integers is required, which sort
1203 // of sucks. No idea if there is a shortcut here or not.
1204 prim.CreationDate = Convert.ToInt32(row["CreationDate"]);
1205 prim.Name = row["Name"] == DBNull.Value ? string.Empty : (string)row["Name"];
1206 // various text fields
1207 prim.Text = (String) row["Text"];
1208 prim.Color = Color.FromArgb(Convert.ToInt32(row["ColorA"]),
1209 Convert.ToInt32(row["ColorR"]),
1210 Convert.ToInt32(row["ColorG"]),
1211 Convert.ToInt32(row["ColorB"]));
1212 prim.Description = (String) row["Description"];
1213 prim.SitName = (String) row["SitName"];
1214 prim.TouchName = (String) row["TouchName"];
1215 // permissions
1216 prim.ObjectFlags = Convert.ToUInt32(row["ObjectFlags"]);
1217 prim.CreatorID = new UUID((String) row["CreatorID"]);
1218 prim.OwnerID = new UUID((String) row["OwnerID"]);
1219 prim.GroupID = new UUID((String) row["GroupID"]);
1220 prim.LastOwnerID = new UUID((String) row["LastOwnerID"]);
1221 prim.OwnerMask = Convert.ToUInt32(row["OwnerMask"]);
1222 prim.NextOwnerMask = Convert.ToUInt32(row["NextOwnerMask"]);
1223 prim.GroupMask = Convert.ToUInt32(row["GroupMask"]);
1224 prim.EveryoneMask = Convert.ToUInt32(row["EveryoneMask"]);
1225 prim.BaseMask = Convert.ToUInt32(row["BaseMask"]);
1226 // vectors
1227 prim.OffsetPosition = new Vector3(
1228 Convert.ToSingle(row["PositionX"]),
1229 Convert.ToSingle(row["PositionY"]),
1230 Convert.ToSingle(row["PositionZ"])
1231 );
1232 prim.GroupPosition = new Vector3(
1233 Convert.ToSingle(row["GroupPositionX"]),
1234 Convert.ToSingle(row["GroupPositionY"]),
1235 Convert.ToSingle(row["GroupPositionZ"])
1236 );
1237 prim.Velocity = new Vector3(
1238 Convert.ToSingle(row["VelocityX"]),
1239 Convert.ToSingle(row["VelocityY"]),
1240 Convert.ToSingle(row["VelocityZ"])
1241 );
1242 prim.AngularVelocity = new Vector3(
1243 Convert.ToSingle(row["AngularVelocityX"]),
1244 Convert.ToSingle(row["AngularVelocityY"]),
1245 Convert.ToSingle(row["AngularVelocityZ"])
1246 );
1247 prim.Acceleration = new Vector3(
1248 Convert.ToSingle(row["AccelerationX"]),
1249 Convert.ToSingle(row["AccelerationY"]),
1250 Convert.ToSingle(row["AccelerationZ"])
1251 );
1252 // quaternions
1253 prim.RotationOffset = new Quaternion(
1254 Convert.ToSingle(row["RotationX"]),
1255 Convert.ToSingle(row["RotationY"]),
1256 Convert.ToSingle(row["RotationZ"]),
1257 Convert.ToSingle(row["RotationW"])
1258 );
1259
1260 prim.SitTargetPositionLL = new Vector3(
1261 Convert.ToSingle(row["SitTargetOffsetX"]),
1262 Convert.ToSingle(row["SitTargetOffsetY"]),
1263 Convert.ToSingle(row["SitTargetOffsetZ"]));
1264 prim.SitTargetOrientationLL = new Quaternion(
1265 Convert.ToSingle(
1266 row["SitTargetOrientX"]),
1267 Convert.ToSingle(
1268 row["SitTargetOrientY"]),
1269 Convert.ToSingle(
1270 row["SitTargetOrientZ"]),
1271 Convert.ToSingle(
1272 row["SitTargetOrientW"]));
1273
1274 prim.ClickAction = Convert.ToByte(row["ClickAction"]);
1275 prim.PayPrice[0] = Convert.ToInt32(row["PayPrice"]);
1276 prim.PayPrice[1] = Convert.ToInt32(row["PayButton1"]);
1277 prim.PayPrice[2] = Convert.ToInt32(row["PayButton2"]);
1278 prim.PayPrice[3] = Convert.ToInt32(row["PayButton3"]);
1279 prim.PayPrice[4] = Convert.ToInt32(row["PayButton4"]);
1280
1281 prim.Sound = new UUID(row["LoopedSound"].ToString());
1282 prim.SoundGain = Convert.ToSingle(row["LoopedSoundGain"]);
1283 prim.SoundFlags = 1; // If it's persisted at all, it's looped
1284
1285 if (!row.IsNull("TextureAnimation"))
1286 prim.TextureAnimation = Convert.FromBase64String(row["TextureAnimation"].ToString());
1287 if (!row.IsNull("ParticleSystem"))
1288 prim.ParticleSystem = Convert.FromBase64String(row["ParticleSystem"].ToString());
1289
1290 prim.AngularVelocity = new Vector3(
1291 Convert.ToSingle(row["OmegaX"]),
1292 Convert.ToSingle(row["OmegaY"]),
1293 Convert.ToSingle(row["OmegaZ"])
1294 );
1295
1296 prim.SetCameraEyeOffset(new Vector3(
1297 Convert.ToSingle(row["CameraEyeOffsetX"]),
1298 Convert.ToSingle(row["CameraEyeOffsetY"]),
1299 Convert.ToSingle(row["CameraEyeOffsetZ"])
1300 ));
1301
1302 prim.SetCameraAtOffset(new Vector3(
1303 Convert.ToSingle(row["CameraAtOffsetX"]),
1304 Convert.ToSingle(row["CameraAtOffsetY"]),
1305 Convert.ToSingle(row["CameraAtOffsetZ"])
1306 ));
1307
1308 if (Convert.ToInt16(row["ForceMouselook"]) != 0)
1309 prim.SetForceMouselook(true);
1310
1311 prim.ScriptAccessPin = Convert.ToInt32(row["ScriptAccessPin"]);
1312
1313 if (Convert.ToInt16(row["AllowedDrop"]) != 0)
1314 prim.AllowedDrop = true;
1315
1316 if (Convert.ToInt16(row["DieAtEdge"]) != 0)
1317 prim.DIE_AT_EDGE = true;
1318
1319 prim.SalePrice = Convert.ToInt32(row["SalePrice"]);
1320 prim.ObjectSaleType = Convert.ToByte(row["SaleType"]);
1321
1322 prim.Material = Convert.ToByte(row["Material"]);
1323
1324 prim.CollisionSound = new UUID(row["CollisionSound"].ToString());
1325 prim.CollisionSoundVolume = Convert.ToSingle(row["CollisionSoundVolume"]);
1326
1327 if (Convert.ToInt16(row["VolumeDetect"]) != 0)
1328 prim.VolumeDetectActive = true;
1329
1330 return prim;
1331 }
1332
1333 /// <summary>
1334 /// Build a prim inventory item from the persisted data.
1335 /// </summary>
1336 /// <param name="row"></param>
1337 /// <returns></returns>
1338 private static TaskInventoryItem buildItem(DataRow row)
1339 {
1340 TaskInventoryItem taskItem = new TaskInventoryItem();
1341
1342 taskItem.ItemID = new UUID((String)row["itemID"]);
1343 taskItem.ParentPartID = new UUID((String)row["primID"]);
1344 taskItem.AssetID = new UUID((String)row["assetID"]);
1345 taskItem.ParentID = new UUID((String)row["parentFolderID"]);
1346
1347 taskItem.InvType = Convert.ToInt32(row["invType"]);
1348 taskItem.Type = Convert.ToInt32(row["assetType"]);
1349
1350 taskItem.Name = (String)row["name"];
1351 taskItem.Description = (String)row["description"];
1352 taskItem.CreationDate = Convert.ToUInt32(row["creationDate"]);
1353 taskItem.CreatorID = new UUID((String)row["creatorID"]);
1354 taskItem.OwnerID = new UUID((String)row["ownerID"]);
1355 taskItem.LastOwnerID = new UUID((String)row["lastOwnerID"]);
1356 taskItem.GroupID = new UUID((String)row["groupID"]);
1357
1358 taskItem.NextPermissions = Convert.ToUInt32(row["nextPermissions"]);
1359 taskItem.CurrentPermissions = Convert.ToUInt32(row["currentPermissions"]);
1360 taskItem.BasePermissions = Convert.ToUInt32(row["basePermissions"]);
1361 taskItem.EveryonePermissions = Convert.ToUInt32(row["everyonePermissions"]);
1362 taskItem.GroupPermissions = Convert.ToUInt32(row["groupPermissions"]);
1363 taskItem.Flags = Convert.ToUInt32(row["flags"]);
1364
1365 return taskItem;
1366 }
1367
1368 /// <summary>
1369 /// Build a Land Data from the persisted data.
1370 /// </summary>
1371 /// <param name="row"></param>
1372 /// <returns></returns>
1373 private LandData buildLandData(DataRow row)
1374 {
1375 LandData newData = new LandData();
1376
1377 newData.GlobalID = new UUID((String) row["UUID"]);
1378 newData.LocalID = Convert.ToInt32(row["LocalLandID"]);
1379
1380 // Bitmap is a byte[512]
1381 newData.Bitmap = (Byte[]) row["Bitmap"];
1382
1383 newData.Name = (String) row["Name"];
1384 newData.Description = (String) row["Desc"];
1385 newData.OwnerID = (UUID)(String) row["OwnerUUID"];
1386 newData.IsGroupOwned = (Boolean) row["IsGroupOwned"];
1387 newData.Area = Convert.ToInt32(row["Area"]);
1388 newData.AuctionID = Convert.ToUInt32(row["AuctionID"]); //Unemplemented
1389 newData.Category = (ParcelCategory) Convert.ToInt32(row["Category"]);
1390 //Enum OpenMetaverse.Parcel.ParcelCategory
1391 newData.ClaimDate = Convert.ToInt32(row["ClaimDate"]);
1392 newData.ClaimPrice = Convert.ToInt32(row["ClaimPrice"]);
1393 newData.GroupID = new UUID((String) row["GroupUUID"]);
1394 newData.SalePrice = Convert.ToInt32(row["SalePrice"]);
1395 newData.Status = (ParcelStatus) Convert.ToInt32(row["LandStatus"]);
1396 //Enum. OpenMetaverse.Parcel.ParcelStatus
1397 newData.Flags = Convert.ToUInt32(row["LandFlags"]);
1398 newData.LandingType = (Byte) row["LandingType"];
1399 newData.MediaAutoScale = (Byte) row["MediaAutoScale"];
1400 newData.MediaID = new UUID((String) row["MediaTextureUUID"]);
1401 newData.MediaURL = (String) row["MediaURL"];
1402 newData.MusicURL = (String) row["MusicURL"];
1403 newData.PassHours = Convert.ToSingle(row["PassHours"]);
1404 newData.PassPrice = Convert.ToInt32(row["PassPrice"]);
1405 newData.SnapshotID = (UUID)(String) row["SnapshotUUID"];
1406 try
1407 {
1408
1409 newData.UserLocation =
1410 new Vector3(Convert.ToSingle(row["UserLocationX"]), Convert.ToSingle(row["UserLocationY"]),
1411 Convert.ToSingle(row["UserLocationZ"]));
1412 newData.UserLookAt =
1413 new Vector3(Convert.ToSingle(row["UserLookAtX"]), Convert.ToSingle(row["UserLookAtY"]),
1414 Convert.ToSingle(row["UserLookAtZ"]));
1415
1416 }
1417 catch (InvalidCastException)
1418 {
1419 m_log.ErrorFormat("[PARCEL]: unable to get parcel telehub settings for {1}", newData.Name);
1420 newData.UserLocation = Vector3.Zero;
1421 newData.UserLookAt = Vector3.Zero;
1422 }
1423 newData.ParcelAccessList = new List<ParcelManager.ParcelAccessEntry>();
1424 UUID authBuyerID = UUID.Zero;
1425
1426 UUID.TryParse((string)row["AuthbuyerID"], out authBuyerID);
1427
1428 newData.OtherCleanTime = Convert.ToInt32(row["OtherCleanTime"]);
1429 newData.Dwell = Convert.ToInt32(row["Dwell"]);
1430
1431 return newData;
1432 }
1433
1434 private RegionSettings buildRegionSettings(DataRow row)
1435 {
1436 RegionSettings newSettings = new RegionSettings();
1437
1438 newSettings.RegionUUID = new UUID((string) row["regionUUID"]);
1439 newSettings.BlockTerraform = Convert.ToBoolean(row["block_terraform"]);
1440 newSettings.AllowDamage = Convert.ToBoolean(row["allow_damage"]);
1441 newSettings.BlockFly = Convert.ToBoolean(row["block_fly"]);
1442 newSettings.RestrictPushing = Convert.ToBoolean(row["restrict_pushing"]);
1443 newSettings.AllowLandResell = Convert.ToBoolean(row["allow_land_resell"]);
1444 newSettings.AllowLandJoinDivide = Convert.ToBoolean(row["allow_land_join_divide"]);
1445 newSettings.BlockShowInSearch = Convert.ToBoolean(row["block_show_in_search"]);
1446 newSettings.AgentLimit = Convert.ToInt32(row["agent_limit"]);
1447 newSettings.ObjectBonus = Convert.ToDouble(row["object_bonus"]);
1448 newSettings.Maturity = Convert.ToInt32(row["maturity"]);
1449 newSettings.DisableScripts = Convert.ToBoolean(row["disable_scripts"]);
1450 newSettings.DisableCollisions = Convert.ToBoolean(row["disable_collisions"]);
1451 newSettings.DisablePhysics = Convert.ToBoolean(row["disable_physics"]);
1452 newSettings.TerrainTexture1 = new UUID((String) row["terrain_texture_1"]);
1453 newSettings.TerrainTexture2 = new UUID((String) row["terrain_texture_2"]);
1454 newSettings.TerrainTexture3 = new UUID((String) row["terrain_texture_3"]);
1455 newSettings.TerrainTexture4 = new UUID((String) row["terrain_texture_4"]);
1456 newSettings.Elevation1NW = Convert.ToDouble(row["elevation_1_nw"]);
1457 newSettings.Elevation2NW = Convert.ToDouble(row["elevation_2_nw"]);
1458 newSettings.Elevation1NE = Convert.ToDouble(row["elevation_1_ne"]);
1459 newSettings.Elevation2NE = Convert.ToDouble(row["elevation_2_ne"]);
1460 newSettings.Elevation1SE = Convert.ToDouble(row["elevation_1_se"]);
1461 newSettings.Elevation2SE = Convert.ToDouble(row["elevation_2_se"]);
1462 newSettings.Elevation1SW = Convert.ToDouble(row["elevation_1_sw"]);
1463 newSettings.Elevation2SW = Convert.ToDouble(row["elevation_2_sw"]);
1464 newSettings.WaterHeight = Convert.ToDouble(row["water_height"]);
1465 newSettings.TerrainRaiseLimit = Convert.ToDouble(row["terrain_raise_limit"]);
1466 newSettings.TerrainLowerLimit = Convert.ToDouble(row["terrain_lower_limit"]);
1467 newSettings.UseEstateSun = Convert.ToBoolean(row["use_estate_sun"]);
1468 newSettings.Sandbox = Convert.ToBoolean(row["sandbox"]);
1469 newSettings.SunVector = new Vector3 (
1470 Convert.ToSingle(row["sunvectorx"]),
1471 Convert.ToSingle(row["sunvectory"]),
1472 Convert.ToSingle(row["sunvectorz"])
1473 );
1474 newSettings.FixedSun = Convert.ToBoolean(row["fixed_sun"]);
1475 newSettings.SunPosition = Convert.ToDouble(row["sun_position"]);
1476 newSettings.Covenant = new UUID((String) row["covenant"]);
1477
1478 return newSettings;
1479 }
1480
1481 /// <summary>
1482 /// Build a land access entry from the persisted data.
1483 /// </summary>
1484 /// <param name="row"></param>
1485 /// <returns></returns>
1486 private static ParcelManager.ParcelAccessEntry buildLandAccessData(DataRow row)
1487 {
1488 ParcelManager.ParcelAccessEntry entry = new ParcelManager.ParcelAccessEntry();
1489 entry.AgentID = new UUID((string) row["AccessUUID"]);
1490 entry.Flags = (AccessList) row["Flags"];
1491 entry.Time = new DateTime();
1492 return entry;
1493 }
1494
1495 /// <summary>
1496 ///
1497 /// </summary>
1498 /// <param name="val"></param>
1499 /// <returns></returns>
1500 private static Array serializeTerrain(double[,] val)
1501 {
1502 MemoryStream str = new MemoryStream(((int)Constants.RegionSize * (int)Constants.RegionSize) *sizeof (double));
1503 BinaryWriter bw = new BinaryWriter(str);
1504
1505 // TODO: COMPATIBILITY - Add byte-order conversions
1506 for (int x = 0; x < (int)Constants.RegionSize; x++)
1507 for (int y = 0; y < (int)Constants.RegionSize; y++)
1508 bw.Write(val[x, y]);
1509
1510 return str.ToArray();
1511 }
1512
1513// private void fillTerrainRow(DataRow row, UUID regionUUID, int rev, double[,] val)
1514// {
1515// row["RegionUUID"] = regionUUID;
1516// row["Revision"] = rev;
1517
1518 // MemoryStream str = new MemoryStream(((int)Constants.RegionSize * (int)Constants.RegionSize)*sizeof (double));
1519// BinaryWriter bw = new BinaryWriter(str);
1520
1521// // TODO: COMPATIBILITY - Add byte-order conversions
1522 // for (int x = 0; x < (int)Constants.RegionSize; x++)
1523 // for (int y = 0; y < (int)Constants.RegionSize; y++)
1524// bw.Write(val[x, y]);
1525
1526// row["Heightfield"] = str.ToArray();
1527// }
1528
1529 /// <summary>
1530 ///
1531 /// </summary>
1532 /// <param name="row"></param>
1533 /// <param name="prim"></param>
1534 /// <param name="sceneGroupID"></param>
1535 /// <param name="regionUUID"></param>
1536 private static void fillPrimRow(DataRow row, SceneObjectPart prim, UUID sceneGroupID, UUID regionUUID)
1537 {
1538 row["UUID"] = prim.UUID.ToString();
1539 row["RegionUUID"] = regionUUID.ToString();
1540 row["CreationDate"] = prim.CreationDate;
1541 row["Name"] = prim.Name;
1542 row["SceneGroupID"] = sceneGroupID.ToString();
1543 // the UUID of the root part for this SceneObjectGroup
1544 // various text fields
1545 row["Text"] = prim.Text;
1546 row["Description"] = prim.Description;
1547 row["SitName"] = prim.SitName;
1548 row["TouchName"] = prim.TouchName;
1549 // permissions
1550 row["ObjectFlags"] = prim.ObjectFlags;
1551 row["CreatorID"] = prim.CreatorID.ToString();
1552 row["OwnerID"] = prim.OwnerID.ToString();
1553 row["GroupID"] = prim.GroupID.ToString();
1554 row["LastOwnerID"] = prim.LastOwnerID.ToString();
1555 row["OwnerMask"] = prim.OwnerMask;
1556 row["NextOwnerMask"] = prim.NextOwnerMask;
1557 row["GroupMask"] = prim.GroupMask;
1558 row["EveryoneMask"] = prim.EveryoneMask;
1559 row["BaseMask"] = prim.BaseMask;
1560 // vectors
1561 row["PositionX"] = prim.OffsetPosition.X;
1562 row["PositionY"] = prim.OffsetPosition.Y;
1563 row["PositionZ"] = prim.OffsetPosition.Z;
1564 row["GroupPositionX"] = prim.GroupPosition.X;
1565 row["GroupPositionY"] = prim.GroupPosition.Y;
1566 row["GroupPositionZ"] = prim.GroupPosition.Z;
1567 row["VelocityX"] = prim.Velocity.X;
1568 row["VelocityY"] = prim.Velocity.Y;
1569 row["VelocityZ"] = prim.Velocity.Z;
1570 row["AngularVelocityX"] = prim.AngularVelocity.X;
1571 row["AngularVelocityY"] = prim.AngularVelocity.Y;
1572 row["AngularVelocityZ"] = prim.AngularVelocity.Z;
1573 row["AccelerationX"] = prim.Acceleration.X;
1574 row["AccelerationY"] = prim.Acceleration.Y;
1575 row["AccelerationZ"] = prim.Acceleration.Z;
1576 // quaternions
1577 row["RotationX"] = prim.RotationOffset.X;
1578 row["RotationY"] = prim.RotationOffset.Y;
1579 row["RotationZ"] = prim.RotationOffset.Z;
1580 row["RotationW"] = prim.RotationOffset.W;
1581
1582 // Sit target
1583 Vector3 sitTargetPos = prim.SitTargetPositionLL;
1584 row["SitTargetOffsetX"] = sitTargetPos.X;
1585 row["SitTargetOffsetY"] = sitTargetPos.Y;
1586 row["SitTargetOffsetZ"] = sitTargetPos.Z;
1587
1588 Quaternion sitTargetOrient = prim.SitTargetOrientationLL;
1589 row["SitTargetOrientW"] = sitTargetOrient.W;
1590 row["SitTargetOrientX"] = sitTargetOrient.X;
1591 row["SitTargetOrientY"] = sitTargetOrient.Y;
1592 row["SitTargetOrientZ"] = sitTargetOrient.Z;
1593 row["ColorR"] = Convert.ToInt32(prim.Color.R);
1594 row["ColorG"] = Convert.ToInt32(prim.Color.G);
1595 row["ColorB"] = Convert.ToInt32(prim.Color.B);
1596 row["ColorA"] = Convert.ToInt32(prim.Color.A);
1597 row["PayPrice"] = prim.PayPrice[0];
1598 row["PayButton1"] = prim.PayPrice[1];
1599 row["PayButton2"] = prim.PayPrice[2];
1600 row["PayButton3"] = prim.PayPrice[3];
1601 row["PayButton4"] = prim.PayPrice[4];
1602
1603
1604 row["TextureAnimation"] = Convert.ToBase64String(prim.TextureAnimation);
1605 row["ParticleSystem"] = Convert.ToBase64String(prim.ParticleSystem);
1606
1607 row["OmegaX"] = prim.AngularVelocity.X;
1608 row["OmegaY"] = prim.AngularVelocity.Y;
1609 row["OmegaZ"] = prim.AngularVelocity.Z;
1610
1611 row["CameraEyeOffsetX"] = prim.GetCameraEyeOffset().X;
1612 row["CameraEyeOffsetY"] = prim.GetCameraEyeOffset().Y;
1613 row["CameraEyeOffsetZ"] = prim.GetCameraEyeOffset().Z;
1614
1615 row["CameraAtOffsetX"] = prim.GetCameraAtOffset().X;
1616 row["CameraAtOffsetY"] = prim.GetCameraAtOffset().Y;
1617 row["CameraAtOffsetZ"] = prim.GetCameraAtOffset().Z;
1618
1619
1620 if ((prim.SoundFlags & 1) != 0) // Looped
1621 {
1622 row["LoopedSound"] = prim.Sound.ToString();
1623 row["LoopedSoundGain"] = prim.SoundGain;
1624 }
1625 else
1626 {
1627 row["LoopedSound"] = UUID.Zero.ToString();
1628 row["LoopedSoundGain"] = 0.0f;
1629 }
1630
1631 if (prim.GetForceMouselook())
1632 row["ForceMouselook"] = 1;
1633 else
1634 row["ForceMouselook"] = 0;
1635
1636 row["ScriptAccessPin"] = prim.ScriptAccessPin;
1637
1638 if (prim.AllowedDrop)
1639 row["AllowedDrop"] = 1;
1640 else
1641 row["AllowedDrop"] = 0;
1642
1643 if (prim.DIE_AT_EDGE)
1644 row["DieAtEdge"] = 1;
1645 else
1646 row["DieAtEdge"] = 0;
1647
1648 row["SalePrice"] = prim.SalePrice;
1649 row["SaleType"] = Convert.ToInt16(prim.ObjectSaleType);
1650
1651 // click action
1652 row["ClickAction"] = prim.ClickAction;
1653
1654 row["SalePrice"] = prim.SalePrice;
1655 row["Material"] = prim.Material;
1656
1657 row["CollisionSound"] = prim.CollisionSound.ToString();
1658 row["CollisionSoundVolume"] = prim.CollisionSoundVolume;
1659 if (prim.VolumeDetectActive)
1660 row["VolumeDetect"] = 1;
1661 else
1662 row["VolumeDetect"] = 0;
1663
1664 }
1665
1666 /// <summary>
1667 ///
1668 /// </summary>
1669 /// <param name="row"></param>
1670 /// <param name="taskItem"></param>
1671 private static void fillItemRow(DataRow row, TaskInventoryItem taskItem)
1672 {
1673 row["itemID"] = taskItem.ItemID.ToString();
1674 row["primID"] = taskItem.ParentPartID.ToString();
1675 row["assetID"] = taskItem.AssetID.ToString();
1676 row["parentFolderID"] = taskItem.ParentID.ToString();
1677
1678 row["invType"] = taskItem.InvType;
1679 row["assetType"] = taskItem.Type;
1680
1681 row["name"] = taskItem.Name;
1682 row["description"] = taskItem.Description;
1683 row["creationDate"] = taskItem.CreationDate;
1684 row["creatorID"] = taskItem.CreatorID.ToString();
1685 row["ownerID"] = taskItem.OwnerID.ToString();
1686 row["lastOwnerID"] = taskItem.LastOwnerID.ToString();
1687 row["groupID"] = taskItem.GroupID.ToString();
1688 row["nextPermissions"] = taskItem.NextPermissions;
1689 row["currentPermissions"] = taskItem.CurrentPermissions;
1690 row["basePermissions"] = taskItem.BasePermissions;
1691 row["everyonePermissions"] = taskItem.EveryonePermissions;
1692 row["groupPermissions"] = taskItem.GroupPermissions;
1693 row["flags"] = taskItem.Flags;
1694 }
1695
1696 /// <summary>
1697 ///
1698 /// </summary>
1699 /// <param name="row"></param>
1700 /// <param name="land"></param>
1701 /// <param name="regionUUID"></param>
1702 private static void fillLandRow(DataRow row, LandData land, UUID regionUUID)
1703 {
1704 row["UUID"] = land.GlobalID.ToString();
1705 row["RegionUUID"] = regionUUID.ToString();
1706 row["LocalLandID"] = land.LocalID;
1707
1708 // Bitmap is a byte[512]
1709 row["Bitmap"] = land.Bitmap;
1710
1711 row["Name"] = land.Name;
1712 row["Desc"] = land.Description;
1713 row["OwnerUUID"] = land.OwnerID.ToString();
1714 row["IsGroupOwned"] = land.IsGroupOwned;
1715 row["Area"] = land.Area;
1716 row["AuctionID"] = land.AuctionID; //Unemplemented
1717 row["Category"] = land.Category; //Enum OpenMetaverse.Parcel.ParcelCategory
1718 row["ClaimDate"] = land.ClaimDate;
1719 row["ClaimPrice"] = land.ClaimPrice;
1720 row["GroupUUID"] = land.GroupID.ToString();
1721 row["SalePrice"] = land.SalePrice;
1722 row["LandStatus"] = land.Status; //Enum. OpenMetaverse.Parcel.ParcelStatus
1723 row["LandFlags"] = land.Flags;
1724 row["LandingType"] = land.LandingType;
1725 row["MediaAutoScale"] = land.MediaAutoScale;
1726 row["MediaTextureUUID"] = land.MediaID.ToString();
1727 row["MediaURL"] = land.MediaURL;
1728 row["MusicURL"] = land.MusicURL;
1729 row["PassHours"] = land.PassHours;
1730 row["PassPrice"] = land.PassPrice;
1731 row["SnapshotUUID"] = land.SnapshotID.ToString();
1732 row["UserLocationX"] = land.UserLocation.X;
1733 row["UserLocationY"] = land.UserLocation.Y;
1734 row["UserLocationZ"] = land.UserLocation.Z;
1735 row["UserLookAtX"] = land.UserLookAt.X;
1736 row["UserLookAtY"] = land.UserLookAt.Y;
1737 row["UserLookAtZ"] = land.UserLookAt.Z;
1738 row["AuthbuyerID"] = land.AuthBuyerID.ToString();
1739 row["OtherCleanTime"] = land.OtherCleanTime;
1740 row["Dwell"] = land.Dwell;
1741 }
1742
1743 /// <summary>
1744 ///
1745 /// </summary>
1746 /// <param name="row"></param>
1747 /// <param name="entry"></param>
1748 /// <param name="parcelID"></param>
1749 private static void fillLandAccessRow(DataRow row, ParcelManager.ParcelAccessEntry entry, UUID parcelID)
1750 {
1751 row["LandUUID"] = parcelID.ToString();
1752 row["AccessUUID"] = entry.AgentID.ToString();
1753 row["Flags"] = entry.Flags;
1754 }
1755
1756 private static void fillRegionSettingsRow(DataRow row, RegionSettings settings)
1757 {
1758 row["regionUUID"] = settings.RegionUUID.ToString();
1759 row["block_terraform"] = settings.BlockTerraform;
1760 row["block_fly"] = settings.BlockFly;
1761 row["allow_damage"] = settings.AllowDamage;
1762 row["restrict_pushing"] = settings.RestrictPushing;
1763 row["allow_land_resell"] = settings.AllowLandResell;
1764 row["allow_land_join_divide"] = settings.AllowLandJoinDivide;
1765 row["block_show_in_search"] = settings.BlockShowInSearch;
1766 row["agent_limit"] = settings.AgentLimit;
1767 row["object_bonus"] = settings.ObjectBonus;
1768 row["maturity"] = settings.Maturity;
1769 row["disable_scripts"] = settings.DisableScripts;
1770 row["disable_collisions"] = settings.DisableCollisions;
1771 row["disable_physics"] = settings.DisablePhysics;
1772 row["terrain_texture_1"] = settings.TerrainTexture1.ToString();
1773 row["terrain_texture_2"] = settings.TerrainTexture2.ToString();
1774 row["terrain_texture_3"] = settings.TerrainTexture3.ToString();
1775 row["terrain_texture_4"] = settings.TerrainTexture4.ToString();
1776 row["elevation_1_nw"] = settings.Elevation1NW;
1777 row["elevation_2_nw"] = settings.Elevation2NW;
1778 row["elevation_1_ne"] = settings.Elevation1NE;
1779 row["elevation_2_ne"] = settings.Elevation2NE;
1780 row["elevation_1_se"] = settings.Elevation1SE;
1781 row["elevation_2_se"] = settings.Elevation2SE;
1782 row["elevation_1_sw"] = settings.Elevation1SW;
1783 row["elevation_2_sw"] = settings.Elevation2SW;
1784 row["water_height"] = settings.WaterHeight;
1785 row["terrain_raise_limit"] = settings.TerrainRaiseLimit;
1786 row["terrain_lower_limit"] = settings.TerrainLowerLimit;
1787 row["use_estate_sun"] = settings.UseEstateSun;
1788 row["Sandbox"] = settings.Sandbox; // database uses upper case S for sandbox
1789 row["sunvectorx"] = settings.SunVector.X;
1790 row["sunvectory"] = settings.SunVector.Y;
1791 row["sunvectorz"] = settings.SunVector.Z;
1792 row["fixed_sun"] = settings.FixedSun;
1793 row["sun_position"] = settings.SunPosition;
1794 row["covenant"] = settings.Covenant.ToString();
1795 }
1796
1797 /// <summary>
1798 ///
1799 /// </summary>
1800 /// <param name="row"></param>
1801 /// <returns></returns>
1802 private PrimitiveBaseShape buildShape(DataRow row)
1803 {
1804 PrimitiveBaseShape s = new PrimitiveBaseShape();
1805 s.Scale = new Vector3(
1806 Convert.ToSingle(row["ScaleX"]),
1807 Convert.ToSingle(row["ScaleY"]),
1808 Convert.ToSingle(row["ScaleZ"])
1809 );
1810 // paths
1811 s.PCode = Convert.ToByte(row["PCode"]);
1812 s.PathBegin = Convert.ToUInt16(row["PathBegin"]);
1813 s.PathEnd = Convert.ToUInt16(row["PathEnd"]);
1814 s.PathScaleX = Convert.ToByte(row["PathScaleX"]);
1815 s.PathScaleY = Convert.ToByte(row["PathScaleY"]);
1816 s.PathShearX = Convert.ToByte(row["PathShearX"]);
1817 s.PathShearY = Convert.ToByte(row["PathShearY"]);
1818 s.PathSkew = Convert.ToSByte(row["PathSkew"]);
1819 s.PathCurve = Convert.ToByte(row["PathCurve"]);
1820 s.PathRadiusOffset = Convert.ToSByte(row["PathRadiusOffset"]);
1821 s.PathRevolutions = Convert.ToByte(row["PathRevolutions"]);
1822 s.PathTaperX = Convert.ToSByte(row["PathTaperX"]);
1823 s.PathTaperY = Convert.ToSByte(row["PathTaperY"]);
1824 s.PathTwist = Convert.ToSByte(row["PathTwist"]);
1825 s.PathTwistBegin = Convert.ToSByte(row["PathTwistBegin"]);
1826 // profile
1827 s.ProfileBegin = Convert.ToUInt16(row["ProfileBegin"]);
1828 s.ProfileEnd = Convert.ToUInt16(row["ProfileEnd"]);
1829 s.ProfileCurve = Convert.ToByte(row["ProfileCurve"]);
1830 s.ProfileHollow = Convert.ToUInt16(row["ProfileHollow"]);
1831 s.State = Convert.ToByte(row["State"]);
1832
1833 byte[] textureEntry = (byte[])row["Texture"];
1834 s.TextureEntry = textureEntry;
1835
1836 s.ExtraParams = (byte[]) row["ExtraParams"];
1837 return s;
1838 }
1839
1840 /// <summary>
1841 ///
1842 /// </summary>
1843 /// <param name="row"></param>
1844 /// <param name="prim"></param>
1845 private static void fillShapeRow(DataRow row, SceneObjectPart prim)
1846 {
1847 PrimitiveBaseShape s = prim.Shape;
1848 row["UUID"] = prim.UUID.ToString();
1849 // shape is an enum
1850 row["Shape"] = 0;
1851 // vectors
1852 row["ScaleX"] = s.Scale.X;
1853 row["ScaleY"] = s.Scale.Y;
1854 row["ScaleZ"] = s.Scale.Z;
1855 // paths
1856 row["PCode"] = s.PCode;
1857 row["PathBegin"] = s.PathBegin;
1858 row["PathEnd"] = s.PathEnd;
1859 row["PathScaleX"] = s.PathScaleX;
1860 row["PathScaleY"] = s.PathScaleY;
1861 row["PathShearX"] = s.PathShearX;
1862 row["PathShearY"] = s.PathShearY;
1863 row["PathSkew"] = s.PathSkew;
1864 row["PathCurve"] = s.PathCurve;
1865 row["PathRadiusOffset"] = s.PathRadiusOffset;
1866 row["PathRevolutions"] = s.PathRevolutions;
1867 row["PathTaperX"] = s.PathTaperX;
1868 row["PathTaperY"] = s.PathTaperY;
1869 row["PathTwist"] = s.PathTwist;
1870 row["PathTwistBegin"] = s.PathTwistBegin;
1871 // profile
1872 row["ProfileBegin"] = s.ProfileBegin;
1873 row["ProfileEnd"] = s.ProfileEnd;
1874 row["ProfileCurve"] = s.ProfileCurve;
1875 row["ProfileHollow"] = s.ProfileHollow;
1876 row["State"] = s.State;
1877
1878 row["Texture"] = s.TextureEntry;
1879 row["ExtraParams"] = s.ExtraParams;
1880 }
1881
1882 /// <summary>
1883 ///
1884 /// </summary>
1885 /// <param name="prim"></param>
1886 /// <param name="sceneGroupID"></param>
1887 /// <param name="regionUUID"></param>
1888 private void addPrim(SceneObjectPart prim, UUID sceneGroupID, UUID regionUUID)
1889 {
1890
1891 DataTable prims = ds.Tables["prims"];
1892 DataTable shapes = ds.Tables["primshapes"];
1893
1894 DataRow primRow = prims.Rows.Find(prim.UUID.ToString());
1895 if (primRow == null)
1896 {
1897 primRow = prims.NewRow();
1898 fillPrimRow(primRow, prim, sceneGroupID, regionUUID);
1899 prims.Rows.Add(primRow);
1900 }
1901 else
1902 {
1903 fillPrimRow(primRow, prim, sceneGroupID, regionUUID);
1904 }
1905
1906 DataRow shapeRow = shapes.Rows.Find(prim.UUID.ToString());
1907 if (shapeRow == null)
1908 {
1909 shapeRow = shapes.NewRow();
1910 fillShapeRow(shapeRow, prim);
1911 shapes.Rows.Add(shapeRow);
1912 }
1913 else
1914 {
1915 fillShapeRow(shapeRow, prim);
1916 }
1917 }
1918
1919 /// <summary>
1920 /// see IRegionDatastore
1921 /// </summary>
1922 /// <param name="primID"></param>
1923 /// <param name="items"></param>
1924 public void StorePrimInventory(UUID primID, ICollection<TaskInventoryItem> items)
1925 {
1926 m_log.InfoFormat("[REGION DB]: Entered StorePrimInventory with prim ID {0}", primID);
1927
1928 DataTable dbItems = ds.Tables["primitems"];
1929
1930 // For now, we're just going to crudely remove all the previous inventory items
1931 // no matter whether they have changed or not, and replace them with the current set.
1932 lock (ds)
1933 {
1934 RemoveItems(primID);
1935
1936 // repalce with current inventory details
1937 foreach (TaskInventoryItem newItem in items)
1938 {
1939// m_log.InfoFormat(
1940// "[DATASTORE]: ",
1941// "Adding item {0}, {1} to prim ID {2}",
1942// newItem.Name, newItem.ItemID, newItem.ParentPartID);
1943
1944 DataRow newItemRow = dbItems.NewRow();
1945 fillItemRow(newItemRow, newItem);
1946 dbItems.Rows.Add(newItemRow);
1947 }
1948 }
1949
1950 Commit();
1951 }
1952
1953 /***********************************************************************
1954 *
1955 * SQL Statement Creation Functions
1956 *
1957 * These functions create SQL statements for update, insert, and create.
1958 * They can probably be factored later to have a db independant
1959 * portion and a db specific portion
1960 *
1961 **********************************************************************/
1962
1963 /// <summary>
1964 /// Create an insert command
1965 /// </summary>
1966 /// <param name="table">table name</param>
1967 /// <param name="dt">data table</param>
1968 /// <returns>the created command</returns>
1969 /// <remarks>
1970 /// This is subtle enough to deserve some commentary.
1971 /// Instead of doing *lots* and *lots of hardcoded strings
1972 /// for database definitions we'll use the fact that
1973 /// realistically all insert statements look like "insert
1974 /// into A(b, c) values(:b, :c) on the parameterized query
1975 /// front. If we just have a list of b, c, etc... we can
1976 /// generate these strings instead of typing them out.
1977 /// </remarks>
1978 private static SqliteCommand createInsertCommand(string table, DataTable dt)
1979 {
1980 string[] cols = new string[dt.Columns.Count];
1981 for (int i = 0; i < dt.Columns.Count; i++)
1982 {
1983 DataColumn col = dt.Columns[i];
1984 cols[i] = col.ColumnName;
1985 }
1986
1987 string sql = "insert into " + table + "(";
1988 sql += String.Join(", ", cols);
1989 // important, the first ':' needs to be here, the rest get added in the join
1990 sql += ") values (:";
1991 sql += String.Join(", :", cols);
1992 sql += ")";
1993 m_log.DebugFormat("[SQLITE]: Created insert command {0}", sql);
1994 SqliteCommand cmd = new SqliteCommand(sql);
1995
1996 // this provides the binding for all our parameters, so
1997 // much less code than it used to be
1998 foreach (DataColumn col in dt.Columns)
1999 {
2000 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
2001 }
2002 return cmd;
2003 }
2004
2005
2006 /// <summary>
2007 /// create an update command
2008 /// </summary>
2009 /// <param name="table">table name</param>
2010 /// <param name="pk"></param>
2011 /// <param name="dt"></param>
2012 /// <returns>the created command</returns>
2013 private static SqliteCommand createUpdateCommand(string table, string pk, DataTable dt)
2014 {
2015 string sql = "update " + table + " set ";
2016 string subsql = String.Empty;
2017 foreach (DataColumn col in dt.Columns)
2018 {
2019 if (subsql.Length > 0)
2020 {
2021 // a map function would rock so much here
2022 subsql += ", ";
2023 }
2024 subsql += col.ColumnName + "= :" + col.ColumnName;
2025 }
2026 sql += subsql;
2027 sql += " where " + pk;
2028 SqliteCommand cmd = new SqliteCommand(sql);
2029
2030 // this provides the binding for all our parameters, so
2031 // much less code than it used to be
2032
2033 foreach (DataColumn col in dt.Columns)
2034 {
2035 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
2036 }
2037 return cmd;
2038 }
2039
2040 /// <summary>
2041 /// create an update command
2042 /// </summary>
2043 /// <param name="table">table name</param>
2044 /// <param name="pk"></param>
2045 /// <param name="dt"></param>
2046 /// <returns>the created command</returns>
2047 private static SqliteCommand createUpdateCommand(string table, string pk1, string pk2, DataTable dt)
2048 {
2049 string sql = "update " + table + " set ";
2050 string subsql = String.Empty;
2051 foreach (DataColumn col in dt.Columns)
2052 {
2053 if (subsql.Length > 0)
2054 {
2055 // a map function would rock so much here
2056 subsql += ", ";
2057 }
2058 subsql += col.ColumnName + "= :" + col.ColumnName;
2059 }
2060 sql += subsql;
2061 sql += " where " + pk1 + " and " + pk2;
2062 SqliteCommand cmd = new SqliteCommand(sql);
2063
2064 // this provides the binding for all our parameters, so
2065 // much less code than it used to be
2066
2067 foreach (DataColumn col in dt.Columns)
2068 {
2069 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
2070 }
2071 return cmd;
2072 }
2073
2074 /// <summary>
2075 ///
2076 /// </summary>
2077 /// <param name="dt">Data Table</param>
2078 /// <returns></returns>
2079 // private static string defineTable(DataTable dt)
2080 // {
2081 // string sql = "create table " + dt.TableName + "(";
2082 // string subsql = String.Empty;
2083 // foreach (DataColumn col in dt.Columns)
2084 // {
2085 // if (subsql.Length > 0)
2086 // {
2087 // // a map function would rock so much here
2088 // subsql += ",\n";
2089 // }
2090 // subsql += col.ColumnName + " " + sqliteType(col.DataType);
2091 // if (dt.PrimaryKey.Length > 0 && col == dt.PrimaryKey[0])
2092 // {
2093 // subsql += " primary key";
2094 // }
2095 // }
2096 // sql += subsql;
2097 // sql += ")";
2098 // return sql;
2099 // }
2100
2101 /***********************************************************************
2102 *
2103 * Database Binding functions
2104 *
2105 * These will be db specific due to typing, and minor differences
2106 * in databases.
2107 *
2108 **********************************************************************/
2109
2110 ///<summary>
2111 /// This is a convenience function that collapses 5 repetitive
2112 /// lines for defining SqliteParameters to 2 parameters:
2113 /// column name and database type.
2114 ///
2115 /// It assumes certain conventions like :param as the param
2116 /// name to replace in parametrized queries, and that source
2117 /// version is always current version, both of which are fine
2118 /// for us.
2119 ///</summary>
2120 ///<returns>a built sqlite parameter</returns>
2121 private static SqliteParameter createSqliteParameter(string name, Type type)
2122 {
2123 SqliteParameter param = new SqliteParameter();
2124 param.ParameterName = ":" + name;
2125 param.DbType = dbtypeFromType(type);
2126 param.SourceColumn = name;
2127 param.SourceVersion = DataRowVersion.Current;
2128 return param;
2129 }
2130
2131 /// <summary>
2132 ///
2133 /// </summary>
2134 /// <param name="da"></param>
2135 /// <param name="conn"></param>
2136 private void setupPrimCommands(SqliteDataAdapter da, SqliteConnection conn)
2137 {
2138 da.InsertCommand = createInsertCommand("prims", ds.Tables["prims"]);
2139 da.InsertCommand.Connection = conn;
2140
2141 da.UpdateCommand = createUpdateCommand("prims", "UUID=:UUID", ds.Tables["prims"]);
2142 da.UpdateCommand.Connection = conn;
2143
2144 SqliteCommand delete = new SqliteCommand("delete from prims where UUID = :UUID");
2145 delete.Parameters.Add(createSqliteParameter("UUID", typeof (String)));
2146 delete.Connection = conn;
2147 da.DeleteCommand = delete;
2148 }
2149
2150 /// <summary>
2151 ///
2152 /// </summary>
2153 /// <param name="da"></param>
2154 /// <param name="conn"></param>
2155 private void setupItemsCommands(SqliteDataAdapter da, SqliteConnection conn)
2156 {
2157 da.InsertCommand = createInsertCommand("primitems", ds.Tables["primitems"]);
2158 da.InsertCommand.Connection = conn;
2159
2160 da.UpdateCommand = createUpdateCommand("primitems", "itemID = :itemID", ds.Tables["primitems"]);
2161 da.UpdateCommand.Connection = conn;
2162
2163 SqliteCommand delete = new SqliteCommand("delete from primitems where itemID = :itemID");
2164 delete.Parameters.Add(createSqliteParameter("itemID", typeof (String)));
2165 delete.Connection = conn;
2166 da.DeleteCommand = delete;
2167 }
2168
2169 /// <summary>
2170 ///
2171 /// </summary>
2172 /// <param name="da"></param>
2173 /// <param name="conn"></param>
2174 private void setupTerrainCommands(SqliteDataAdapter da, SqliteConnection conn)
2175 {
2176 da.InsertCommand = createInsertCommand("terrain", ds.Tables["terrain"]);
2177 da.InsertCommand.Connection = conn;
2178 }
2179
2180 /// <summary>
2181 ///
2182 /// </summary>
2183 /// <param name="da"></param>
2184 /// <param name="conn"></param>
2185 private void setupLandCommands(SqliteDataAdapter da, SqliteConnection conn)
2186 {
2187 da.InsertCommand = createInsertCommand("land", ds.Tables["land"]);
2188 da.InsertCommand.Connection = conn;
2189
2190 da.UpdateCommand = createUpdateCommand("land", "UUID=:UUID", ds.Tables["land"]);
2191 da.UpdateCommand.Connection = conn;
2192
2193 SqliteCommand delete = new SqliteCommand("delete from land where UUID=:UUID");
2194 delete.Parameters.Add(createSqliteParameter("UUID", typeof(String)));
2195 da.DeleteCommand = delete;
2196 da.DeleteCommand.Connection = conn;
2197 }
2198
2199 /// <summary>
2200 ///
2201 /// </summary>
2202 /// <param name="da"></param>
2203 /// <param name="conn"></param>
2204 private void setupLandAccessCommands(SqliteDataAdapter da, SqliteConnection conn)
2205 {
2206 da.InsertCommand = createInsertCommand("landaccesslist", ds.Tables["landaccesslist"]);
2207 da.InsertCommand.Connection = conn;
2208
2209 da.UpdateCommand = createUpdateCommand("landaccesslist", "LandUUID=:landUUID", "AccessUUID=:AccessUUID", ds.Tables["landaccesslist"]);
2210 da.UpdateCommand.Connection = conn;
2211
2212 SqliteCommand delete = new SqliteCommand("delete from landaccesslist where LandUUID= :LandUUID and AccessUUID= :AccessUUID");
2213 delete.Parameters.Add(createSqliteParameter("LandUUID", typeof(String)));
2214 delete.Parameters.Add(createSqliteParameter("AccessUUID", typeof(String)));
2215 da.DeleteCommand = delete;
2216 da.DeleteCommand.Connection = conn;
2217
2218 }
2219
2220 private void setupRegionSettingsCommands(SqliteDataAdapter da, SqliteConnection conn)
2221 {
2222 da.InsertCommand = createInsertCommand("regionsettings", ds.Tables["regionsettings"]);
2223 da.InsertCommand.Connection = conn;
2224 da.UpdateCommand = createUpdateCommand("regionsettings", "regionUUID=:regionUUID", ds.Tables["regionsettings"]);
2225 da.UpdateCommand.Connection = conn;
2226 }
2227
2228 /// <summary>
2229 ///
2230 /// </summary>
2231 /// <param name="da"></param>
2232 /// <param name="conn"></param>
2233 private void setupShapeCommands(SqliteDataAdapter da, SqliteConnection conn)
2234 {
2235 da.InsertCommand = createInsertCommand("primshapes", ds.Tables["primshapes"]);
2236 da.InsertCommand.Connection = conn;
2237
2238 da.UpdateCommand = createUpdateCommand("primshapes", "UUID=:UUID", ds.Tables["primshapes"]);
2239 da.UpdateCommand.Connection = conn;
2240
2241 SqliteCommand delete = new SqliteCommand("delete from primshapes where UUID = :UUID");
2242 delete.Parameters.Add(createSqliteParameter("UUID", typeof (String)));
2243 delete.Connection = conn;
2244 da.DeleteCommand = delete;
2245 }
2246
2247 /***********************************************************************
2248 *
2249 * Type conversion functions
2250 *
2251 **********************************************************************/
2252
2253 /// <summary>
2254 /// Type conversion function
2255 /// </summary>
2256 /// <param name="type"></param>
2257 /// <returns></returns>
2258 private static DbType dbtypeFromType(Type type)
2259 {
2260 if (type == typeof (String))
2261 {
2262 return DbType.String;
2263 }
2264 else if (type == typeof (Int32))
2265 {
2266 return DbType.Int32;
2267 }
2268 else if (type == typeof (Double))
2269 {
2270 return DbType.Double;
2271 }
2272 else if (type == typeof (Byte))
2273 {
2274 return DbType.Byte;
2275 }
2276 else if (type == typeof (Double))
2277 {
2278 return DbType.Double;
2279 }
2280 else if (type == typeof (Byte[]))
2281 {
2282 return DbType.Binary;
2283 }
2284 else
2285 {
2286 return DbType.String;
2287 }
2288 }
2289
2290 static void PrintDataSet(DataSet ds)
2291 {
2292 // Print out any name and extended properties.
2293 Console.WriteLine("DataSet is named: {0}", ds.DataSetName);
2294 foreach (System.Collections.DictionaryEntry de in ds.ExtendedProperties)
2295 {
2296 Console.WriteLine("Key = {0}, Value = {1}", de.Key, de.Value);
2297 }
2298 Console.WriteLine();
2299 foreach (DataTable dt in ds.Tables)
2300 {
2301 Console.WriteLine("=> {0} Table:", dt.TableName);
2302 // Print out the column names.
2303 for (int curCol = 0; curCol < dt.Columns.Count; curCol++)
2304 {
2305 Console.Write(dt.Columns[curCol].ColumnName + "\t");
2306 }
2307 Console.WriteLine("\n----------------------------------");
2308 // Print the DataTable.
2309 for (int curRow = 0; curRow < dt.Rows.Count; curRow++)
2310 {
2311 for (int curCol = 0; curCol < dt.Columns.Count; curCol++)
2312 {
2313 Console.Write(dt.Rows[curRow][curCol].ToString() + "\t");
2314 }
2315 Console.WriteLine();
2316 }
2317 }
2318 }
2319
2320 }
2321}
diff --git a/OpenSim/Data/SQLiteNG/SQLiteUserAccountData.cs b/OpenSim/Data/SQLiteNG/SQLiteUserAccountData.cs
new file mode 100644
index 0000000..f77159c
--- /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.Sqlite;
35
36namespace OpenSim.Data.SQLiteNG
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..82a2e37
--- /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.Sqlite;
31
32namespace OpenSim.Data.SQLiteNG
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..a0c17f8
--- /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.Sqlite;
33using log4net;
34using OpenMetaverse;
35using OpenSim.Framework;
36
37namespace OpenSim.Data.SQLiteNG
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}