diff options
author | Fernando Oliveira | 2013-10-12 16:33:45 -0500 |
---|---|---|
committer | fernando | 2013-10-12 16:33:45 -0500 |
commit | ff8a76825841533bdc5d534b6f58b2ab964ea6c6 (patch) | |
tree | 4ddde40916db04c1506486c9e7ba88b37f53e23e /OpenSim/Data/PGSQL/PGSQLXInventoryData.cs | |
parent | * pushing test (diff) | |
download | opensim-SC_OLD-ff8a76825841533bdc5d534b6f58b2ab964ea6c6.zip opensim-SC_OLD-ff8a76825841533bdc5d534b6f58b2ab964ea6c6.tar.gz opensim-SC_OLD-ff8a76825841533bdc5d534b6f58b2ab964ea6c6.tar.bz2 opensim-SC_OLD-ff8a76825841533bdc5d534b6f58b2ab964ea6c6.tar.xz |
Fernando Oliveira's Postgress SQL Server Data Connector as a single commit.
* Added PostGreSQL support
* Added MySQL/MySQLXGroupData.cs
* PostgreSQL data access implementation
* PostgreSQL dll binarie and RegionStore.migrations
* Migrations Scripts from MSSQL to POSTGRES
* Postgres SQL Type fixes
* Postgres SQL Connection string
* Data type issues
* more fixes
* tests and +tests
* UUID x string - FIGHT!
* Fixed PG types to internal csharp types
* More data type fix (PostgreSQL fields are case sensitive) :(
* more field case sensitive fixes
* changed the migration files to be case sensitive for fields.
* fixed fields case
* finished converting, now search for hidden bugs.
* some more fixes
* bool type fixed
* more case fixes;
* creatorID case fixed
* case fields fixed
* fixed default now() for TMStamp fields with don't allow nulls.
* fix case sensitve for Region name and Estate name
* fixed case for names for search
* fix class name Error
* Bug fixed on select and migrations
* Un-Reverting my change due to Postgres issue with the ILIKE function
* Fixed some issued for Diva Distro
* Fixes for integration with Diva Distro
* Added System.Core to prebuild.xml for PG project
* Configured to make DIff for Push to OpenSim Project
* Diffs only to PostgreSQL mods.
Diffstat (limited to 'OpenSim/Data/PGSQL/PGSQLXInventoryData.cs')
-rw-r--r-- | OpenSim/Data/PGSQL/PGSQLXInventoryData.cs | 330 |
1 files changed, 330 insertions, 0 deletions
diff --git a/OpenSim/Data/PGSQL/PGSQLXInventoryData.cs b/OpenSim/Data/PGSQL/PGSQLXInventoryData.cs new file mode 100644 index 0000000..a22b882 --- /dev/null +++ b/OpenSim/Data/PGSQL/PGSQLXInventoryData.cs | |||
@@ -0,0 +1,330 @@ | |||
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 | |||
28 | using System; | ||
29 | using System.Collections; | ||
30 | using System.Collections.Generic; | ||
31 | using System.Data; | ||
32 | using OpenMetaverse; | ||
33 | using OpenSim.Framework; | ||
34 | using System.Reflection; | ||
35 | using System.Text; | ||
36 | using log4net; | ||
37 | using Npgsql; | ||
38 | using NpgsqlTypes; | ||
39 | |||
40 | namespace OpenSim.Data.PGSQL | ||
41 | { | ||
42 | public class PGSQLXInventoryData : IXInventoryData | ||
43 | { | ||
44 | // private static readonly ILog m_log = LogManager.GetLogger( | ||
45 | // MethodBase.GetCurrentMethod().DeclaringType); | ||
46 | |||
47 | private PGSQLFolderHandler m_Folders; | ||
48 | private PGSQLItemHandler m_Items; | ||
49 | |||
50 | public PGSQLXInventoryData(string conn, string realm) | ||
51 | { | ||
52 | m_Folders = new PGSQLFolderHandler( | ||
53 | conn, "inventoryfolders", "InventoryStore"); | ||
54 | m_Items = new PGSQLItemHandler( | ||
55 | conn, "inventoryitems", String.Empty); | ||
56 | } | ||
57 | |||
58 | public static UUID str2UUID(string strUUID) | ||
59 | { | ||
60 | UUID newUUID = UUID.Zero; | ||
61 | |||
62 | UUID.TryParse(strUUID, out newUUID); | ||
63 | |||
64 | return newUUID; | ||
65 | } | ||
66 | |||
67 | public XInventoryFolder[] GetFolders(string[] fields, string[] vals) | ||
68 | { | ||
69 | return m_Folders.Get(fields, vals); | ||
70 | } | ||
71 | |||
72 | public XInventoryItem[] GetItems(string[] fields, string[] vals) | ||
73 | { | ||
74 | return m_Items.Get(fields, vals); | ||
75 | } | ||
76 | |||
77 | public bool StoreFolder(XInventoryFolder folder) | ||
78 | { | ||
79 | if (folder.folderName.Length > 64) | ||
80 | folder.folderName = folder.folderName.Substring(0, 64); | ||
81 | return m_Folders.Store(folder); | ||
82 | } | ||
83 | |||
84 | public bool StoreItem(XInventoryItem item) | ||
85 | { | ||
86 | if (item.inventoryName.Length > 64) | ||
87 | item.inventoryName = item.inventoryName.Substring(0, 64); | ||
88 | if (item.inventoryDescription.Length > 128) | ||
89 | item.inventoryDescription = item.inventoryDescription.Substring(0, 128); | ||
90 | |||
91 | return m_Items.Store(item); | ||
92 | } | ||
93 | |||
94 | public bool DeleteFolders(string field, string val) | ||
95 | { | ||
96 | return m_Folders.Delete(field, val); | ||
97 | } | ||
98 | |||
99 | public bool DeleteFolders(string[] fields, string[] vals) | ||
100 | { | ||
101 | return m_Folders.Delete(fields, vals); | ||
102 | } | ||
103 | |||
104 | public bool DeleteItems(string field, string val) | ||
105 | { | ||
106 | return m_Items.Delete(field, val); | ||
107 | } | ||
108 | |||
109 | public bool DeleteItems(string[] fields, string[] vals) | ||
110 | { | ||
111 | return m_Items.Delete(fields, vals); | ||
112 | } | ||
113 | |||
114 | public bool MoveItem(string id, string newParent) | ||
115 | { | ||
116 | return m_Items.MoveItem(id, newParent); | ||
117 | } | ||
118 | |||
119 | public bool MoveFolder(string id, string newParent) | ||
120 | { | ||
121 | return m_Folders.MoveFolder(id, newParent); | ||
122 | } | ||
123 | |||
124 | public XInventoryItem[] GetActiveGestures(UUID principalID) | ||
125 | { | ||
126 | return m_Items.GetActiveGestures(principalID.ToString()); | ||
127 | } | ||
128 | |||
129 | public int GetAssetPermissions(UUID principalID, UUID assetID) | ||
130 | { | ||
131 | return m_Items.GetAssetPermissions(principalID, assetID); | ||
132 | } | ||
133 | } | ||
134 | |||
135 | public class PGSQLItemHandler : PGSQLInventoryHandler<XInventoryItem> | ||
136 | { | ||
137 | public PGSQLItemHandler(string c, string t, string m) : | ||
138 | base(c, t, m) | ||
139 | { | ||
140 | } | ||
141 | |||
142 | public bool MoveItem(string id, string newParent) | ||
143 | { | ||
144 | XInventoryItem[] retrievedItems = Get(new string[] { "inventoryID" }, new string[] { id }); | ||
145 | if (retrievedItems.Length == 0) | ||
146 | return false; | ||
147 | |||
148 | UUID oldParent = retrievedItems[0].parentFolderID; | ||
149 | |||
150 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) | ||
151 | { | ||
152 | using (NpgsqlCommand cmd = new NpgsqlCommand()) | ||
153 | { | ||
154 | cmd.CommandText = String.Format(@"update {0} set ""parentFolderID"" = :ParentFolderID where ""inventoryID"" = :InventoryID", m_Realm); | ||
155 | cmd.Parameters.Add(m_database.CreateParameter("ParentFolderID", newParent)); | ||
156 | cmd.Parameters.Add(m_database.CreateParameter("InventoryID", id )); | ||
157 | cmd.Connection = conn; | ||
158 | conn.Open(); | ||
159 | |||
160 | if (cmd.ExecuteNonQuery() == 0) | ||
161 | return false; | ||
162 | } | ||
163 | } | ||
164 | |||
165 | IncrementFolderVersion(oldParent); | ||
166 | IncrementFolderVersion(newParent); | ||
167 | |||
168 | return true; | ||
169 | } | ||
170 | |||
171 | public XInventoryItem[] GetActiveGestures(string principalID) | ||
172 | { | ||
173 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) | ||
174 | { | ||
175 | using (NpgsqlCommand cmd = new NpgsqlCommand()) | ||
176 | { | ||
177 | cmd.CommandText = String.Format(@"select * from inventoryitems where ""avatarID"" = :uuid and ""assetType"" = :type and ""flags"" = 1", m_Realm); | ||
178 | |||
179 | UUID princID = UUID.Zero; | ||
180 | UUID.TryParse(principalID, out princID); | ||
181 | |||
182 | cmd.Parameters.Add(m_database.CreateParameter("uuid", principalID)); | ||
183 | cmd.Parameters.Add(m_database.CreateParameter("type", (int)AssetType.Gesture)); | ||
184 | cmd.Connection = conn; | ||
185 | conn.Open(); | ||
186 | return DoQuery(cmd); | ||
187 | } | ||
188 | } | ||
189 | } | ||
190 | |||
191 | public int GetAssetPermissions(UUID principalID, UUID assetID) | ||
192 | { | ||
193 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) | ||
194 | { | ||
195 | using (NpgsqlCommand cmd = new NpgsqlCommand()) | ||
196 | { | ||
197 | cmd.CommandText = String.Format(@"select bit_or(""inventoryCurrentPermissions"") as ""inventoryCurrentPermissions"" | ||
198 | from inventoryitems | ||
199 | where ""avatarID"" = :PrincipalID | ||
200 | and ""assetID"" = :AssetID | ||
201 | group by ""assetID"" ", m_Realm); | ||
202 | |||
203 | cmd.Parameters.Add(m_database.CreateParameter("PrincipalID", principalID)); | ||
204 | cmd.Parameters.Add(m_database.CreateParameter("AssetID", assetID)); | ||
205 | cmd.Connection = conn; | ||
206 | conn.Open(); | ||
207 | using (NpgsqlDataReader reader = cmd.ExecuteReader()) | ||
208 | { | ||
209 | |||
210 | int perms = 0; | ||
211 | |||
212 | if (reader.Read()) | ||
213 | { | ||
214 | perms = Convert.ToInt32(reader["inventoryCurrentPermissions"]); | ||
215 | } | ||
216 | |||
217 | return perms; | ||
218 | } | ||
219 | |||
220 | } | ||
221 | } | ||
222 | } | ||
223 | |||
224 | public override bool Store(XInventoryItem item) | ||
225 | { | ||
226 | if (!base.Store(item)) | ||
227 | return false; | ||
228 | |||
229 | IncrementFolderVersion(item.parentFolderID); | ||
230 | |||
231 | return true; | ||
232 | } | ||
233 | } | ||
234 | |||
235 | public class PGSQLFolderHandler : PGSQLInventoryHandler<XInventoryFolder> | ||
236 | { | ||
237 | public PGSQLFolderHandler(string c, string t, string m) : | ||
238 | base(c, t, m) | ||
239 | { | ||
240 | } | ||
241 | |||
242 | public bool MoveFolder(string id, string newParentFolderID) | ||
243 | { | ||
244 | XInventoryFolder[] folders = Get(new string[] { "folderID" }, new string[] { id }); | ||
245 | |||
246 | if (folders.Length == 0) | ||
247 | return false; | ||
248 | |||
249 | UUID oldParentFolderUUID = folders[0].parentFolderID; | ||
250 | |||
251 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) | ||
252 | { | ||
253 | using (NpgsqlCommand cmd = new NpgsqlCommand()) | ||
254 | { | ||
255 | UUID foldID = UUID.Zero; | ||
256 | UUID.TryParse(id, out foldID); | ||
257 | |||
258 | UUID newPar = UUID.Zero; | ||
259 | UUID.TryParse(newParentFolderID, out newPar); | ||
260 | |||
261 | cmd.CommandText = String.Format(@"update {0} set ""parentFolderID"" = :ParentFolderID where ""folderID"" = :folderID", m_Realm); | ||
262 | cmd.Parameters.Add(m_database.CreateParameter("ParentFolderID", newPar)); | ||
263 | cmd.Parameters.Add(m_database.CreateParameter("folderID", foldID)); | ||
264 | cmd.Connection = conn; | ||
265 | conn.Open(); | ||
266 | |||
267 | if (cmd.ExecuteNonQuery() == 0) | ||
268 | return false; | ||
269 | } | ||
270 | } | ||
271 | |||
272 | IncrementFolderVersion(oldParentFolderUUID); | ||
273 | IncrementFolderVersion(newParentFolderID); | ||
274 | |||
275 | return true; | ||
276 | } | ||
277 | |||
278 | public override bool Store(XInventoryFolder folder) | ||
279 | { | ||
280 | if (!base.Store(folder)) | ||
281 | return false; | ||
282 | |||
283 | IncrementFolderVersion(folder.parentFolderID); | ||
284 | |||
285 | return true; | ||
286 | } | ||
287 | } | ||
288 | |||
289 | public class PGSQLInventoryHandler<T> : PGSQLGenericTableHandler<T> where T: class, new() | ||
290 | { | ||
291 | public PGSQLInventoryHandler(string c, string t, string m) : base(c, t, m) {} | ||
292 | |||
293 | protected bool IncrementFolderVersion(UUID folderID) | ||
294 | { | ||
295 | return IncrementFolderVersion(folderID.ToString()); | ||
296 | } | ||
297 | |||
298 | protected bool IncrementFolderVersion(string folderID) | ||
299 | { | ||
300 | // m_log.DebugFormat("[PGSQL ITEM HANDLER]: Incrementing version on folder {0}", folderID); | ||
301 | // Util.PrintCallStack(); | ||
302 | |||
303 | string sql = @"update inventoryfolders set version=version+1 where ""folderID"" = :folderID"; | ||
304 | |||
305 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) | ||
306 | { | ||
307 | using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) | ||
308 | { | ||
309 | UUID foldID = UUID.Zero; | ||
310 | UUID.TryParse(folderID, out foldID); | ||
311 | |||
312 | conn.Open(); | ||
313 | |||
314 | cmd.Parameters.Add( m_database.CreateParameter("folderID", foldID) ); | ||
315 | |||
316 | try | ||
317 | { | ||
318 | cmd.ExecuteNonQuery(); | ||
319 | } | ||
320 | catch (Exception) | ||
321 | { | ||
322 | return false; | ||
323 | } | ||
324 | } | ||
325 | } | ||
326 | |||
327 | return true; | ||
328 | } | ||
329 | } | ||
330 | } | ||