diff options
author | TomTheDragon | 2017-11-02 23:53:19 -0400 |
---|---|---|
committer | UbitUmarov | 2017-11-03 05:10:37 +0000 |
commit | 56d2db3a188461758dd1e4cec4536cf4f7bef684 (patch) | |
tree | 821888697c2cf92e3bfde8ea23100cc9698be393 | |
parent | mantis 8258: also limit mesh basic shape torus hole sizeY on (some) existent... (diff) | |
download | opensim-SC_OLD-56d2db3a188461758dd1e4cec4536cf4f7bef684.zip opensim-SC_OLD-56d2db3a188461758dd1e4cec4536cf4f7bef684.tar.gz opensim-SC_OLD-56d2db3a188461758dd1e4cec4536cf4f7bef684.tar.bz2 opensim-SC_OLD-56d2db3a188461758dd1e4cec4536cf4f7bef684.tar.xz |
Add a new experimental implementation of PGSQL FSAssets
Signed-off-by: UbitUmarov <ajlduarte@sapo.pt>
-rw-r--r-- | OpenSim/Data/PGSQL/PGSQLFSAssetData.cs | 316 | ||||
-rw-r--r-- | OpenSim/Data/PGSQL/Resources/FSAssetStore.migrations | 14 |
2 files changed, 330 insertions, 0 deletions
diff --git a/OpenSim/Data/PGSQL/PGSQLFSAssetData.cs b/OpenSim/Data/PGSQL/PGSQLFSAssetData.cs new file mode 100644 index 0000000..59b857c --- /dev/null +++ b/OpenSim/Data/PGSQL/PGSQLFSAssetData.cs | |||
@@ -0,0 +1,316 @@ | |||
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.Reflection; | ||
30 | using System.Collections.Generic; | ||
31 | using System.Data; | ||
32 | using OpenSim.Framework; | ||
33 | using OpenSim.Framework.Console; | ||
34 | using log4net; | ||
35 | using OpenMetaverse; | ||
36 | using Npgsql; | ||
37 | using NpgsqlTypes; | ||
38 | |||
39 | namespace OpenSim.Data.PGSQL | ||
40 | { | ||
41 | public class PGSQLFSAssetData : IFSAssetDataPlugin | ||
42 | { | ||
43 | private const string _migrationStore = "FSAssetStore"; | ||
44 | private static string m_Table = "fsassets"; | ||
45 | private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); | ||
46 | private long m_ticksToEpoch; | ||
47 | |||
48 | private PGSQLManager m_database; | ||
49 | private string m_connectionString; | ||
50 | |||
51 | public PGSQLFSAssetData() | ||
52 | { | ||
53 | } | ||
54 | |||
55 | public void Initialise(string connect, string realm, int UpdateAccessTime) | ||
56 | { | ||
57 | DaysBetweenAccessTimeUpdates = UpdateAccessTime; | ||
58 | |||
59 | m_ticksToEpoch = new System.DateTime(1970, 1, 1).Ticks; | ||
60 | |||
61 | m_connectionString = connect; | ||
62 | m_database = new PGSQLManager(m_connectionString); | ||
63 | |||
64 | //New migration to check for DB changes | ||
65 | m_database.CheckMigration(_migrationStore); | ||
66 | } | ||
67 | |||
68 | public void Initialise() | ||
69 | { | ||
70 | throw new NotImplementedException(); | ||
71 | } | ||
72 | |||
73 | /// <summary> | ||
74 | /// Number of days that must pass before we update the access time on an asset when it has been fetched | ||
75 | /// Config option to change this is "DaysBetweenAccessTimeUpdates" | ||
76 | /// </summary> | ||
77 | private int DaysBetweenAccessTimeUpdates = 0; | ||
78 | |||
79 | protected virtual Assembly Assembly | ||
80 | { | ||
81 | get { return GetType().Assembly; } | ||
82 | } | ||
83 | |||
84 | #region IPlugin Members | ||
85 | |||
86 | public string Version { get { return "1.0.0.0"; } } | ||
87 | |||
88 | public void Dispose() { } | ||
89 | |||
90 | public string Name | ||
91 | { | ||
92 | get { return "PGSQL FSAsset storage engine"; } | ||
93 | } | ||
94 | |||
95 | #endregion | ||
96 | |||
97 | #region IFSAssetDataPlugin Members | ||
98 | |||
99 | public AssetMetadata Get(string id, out string hash) | ||
100 | { | ||
101 | hash = String.Empty; | ||
102 | AssetMetadata meta = null; | ||
103 | UUID uuid = new UUID(id); | ||
104 | |||
105 | string query = String.Format("select \"id\", \"type\", \"hash\", \"create_time\", \"access_time\", \"asset_flags\" from {0} where \"id\" = :id", m_Table); | ||
106 | using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString)) | ||
107 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | ||
108 | { | ||
109 | dbcon.Open(); | ||
110 | cmd.Parameters.Add(m_database.CreateParameter("id", uuid)); | ||
111 | using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default)) | ||
112 | { | ||
113 | if (reader.Read()) | ||
114 | { | ||
115 | meta = new AssetMetadata(); | ||
116 | hash = reader["hash"].ToString(); | ||
117 | meta.ID = id; | ||
118 | meta.FullID = uuid; | ||
119 | meta.Name = String.Empty; | ||
120 | meta.Description = String.Empty; | ||
121 | meta.Type = (sbyte)Convert.ToInt32(reader["type"]); | ||
122 | meta.ContentType = SLUtil.SLAssetTypeToContentType(meta.Type); | ||
123 | meta.CreationDate = Util.ToDateTime(Convert.ToInt32(reader["create_time"])); | ||
124 | meta.Flags = (AssetFlags)Convert.ToInt32(reader["asset_flags"]); | ||
125 | int atime = Convert.ToInt32(reader["access_time"]); | ||
126 | UpdateAccessTime(atime, uuid); | ||
127 | } | ||
128 | } | ||
129 | } | ||
130 | |||
131 | return meta; | ||
132 | } | ||
133 | |||
134 | private void UpdateAccessTime(int AccessTime, UUID id) | ||
135 | { | ||
136 | // Reduce DB work by only updating access time if asset hasn't recently been accessed | ||
137 | // 0 By Default, Config option is "DaysBetweenAccessTimeUpdates" | ||
138 | if (DaysBetweenAccessTimeUpdates > 0 && (DateTime.UtcNow - Utils.UnixTimeToDateTime(AccessTime)).TotalDays < DaysBetweenAccessTimeUpdates) | ||
139 | return; | ||
140 | |||
141 | string query = String.Format("UPDATE {0} SET \"access_time\" = :access_time WHERE \"id\" = :id", m_Table); | ||
142 | using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString)) | ||
143 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | ||
144 | { | ||
145 | dbcon.Open(); | ||
146 | int now = (int)((System.DateTime.Now.Ticks - m_ticksToEpoch) / 10000000); | ||
147 | cmd.Parameters.Add(m_database.CreateParameter("id", id)); | ||
148 | cmd.Parameters.Add(m_database.CreateParameter("access_time", now)); | ||
149 | cmd.ExecuteNonQuery(); | ||
150 | } | ||
151 | } | ||
152 | |||
153 | public bool Store(AssetMetadata meta, string hash) | ||
154 | { | ||
155 | try | ||
156 | { | ||
157 | bool found = false; | ||
158 | string oldhash; | ||
159 | AssetMetadata existingAsset = Get(meta.ID, out oldhash); | ||
160 | |||
161 | string query = String.Format("UPDATE {0} SET \"access_time\" = :access_time WHERE \"id\" = :id", m_Table); | ||
162 | if (existingAsset == null) | ||
163 | { | ||
164 | query = String.Format("insert into {0} (\"id\", \"type\", \"hash\", \"asset_flags\", \"create_time\", \"access_time\") values ( :id, :type, :hash, :asset_flags, :create_time, :access_time)", m_Table); | ||
165 | found = true; | ||
166 | } | ||
167 | |||
168 | using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString)) | ||
169 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | ||
170 | { | ||
171 | dbcon.Open(); | ||
172 | int now = (int)((System.DateTime.Now.Ticks - m_ticksToEpoch) / 10000000); | ||
173 | cmd.Parameters.Add(m_database.CreateParameter("id", meta.FullID)); | ||
174 | cmd.Parameters.Add(m_database.CreateParameter("type", meta.Type)); | ||
175 | cmd.Parameters.Add(m_database.CreateParameter("hash", hash)); | ||
176 | cmd.Parameters.Add(m_database.CreateParameter("asset_flags", Convert.ToInt32(meta.Flags))); | ||
177 | cmd.Parameters.Add(m_database.CreateParameter("create_time", now)); | ||
178 | cmd.Parameters.Add(m_database.CreateParameter("access_time", now)); | ||
179 | cmd.ExecuteNonQuery(); | ||
180 | } | ||
181 | return found; | ||
182 | } | ||
183 | catch(Exception e) | ||
184 | { | ||
185 | m_log.Error("[PGSQL FSASSETS] Failed to store asset with ID " + meta.ID); | ||
186 | m_log.Error(e.ToString()); | ||
187 | return false; | ||
188 | } | ||
189 | } | ||
190 | |||
191 | /// <summary> | ||
192 | /// Check if the assets exist in the database. | ||
193 | /// </summary> | ||
194 | /// <param name="uuids">The asset UUID's</param> | ||
195 | /// <returns>For each asset: true if it exists, false otherwise</returns> | ||
196 | public bool[] AssetsExist(UUID[] uuids) | ||
197 | { | ||
198 | if (uuids.Length == 0) | ||
199 | return new bool[0]; | ||
200 | |||
201 | HashSet<UUID> exists = new HashSet<UUID>(); | ||
202 | |||
203 | string ids = "'" + string.Join("','", uuids) + "'"; | ||
204 | string query = string.Format("select \"id\" from {1} where id in ({0})", ids, m_Table); | ||
205 | using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString)) | ||
206 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | ||
207 | { | ||
208 | dbcon.Open(); | ||
209 | using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default)) | ||
210 | { | ||
211 | while (reader.Read()) | ||
212 | { | ||
213 | UUID id = DBGuid.FromDB(reader["id"]);; | ||
214 | exists.Add(id); | ||
215 | } | ||
216 | } | ||
217 | } | ||
218 | |||
219 | bool[] results = new bool[uuids.Length]; | ||
220 | for (int i = 0; i < uuids.Length; i++) | ||
221 | results[i] = exists.Contains(uuids[i]); | ||
222 | return results; | ||
223 | } | ||
224 | |||
225 | public int Count() | ||
226 | { | ||
227 | int count = 0; | ||
228 | string query = String.Format("select count(*) as count from {0}", m_Table); | ||
229 | using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString)) | ||
230 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | ||
231 | { | ||
232 | dbcon.Open(); | ||
233 | IDataReader reader = cmd.ExecuteReader(); | ||
234 | reader.Read(); | ||
235 | count = Convert.ToInt32(reader["count"]); | ||
236 | reader.Close(); | ||
237 | } | ||
238 | |||
239 | return count; | ||
240 | } | ||
241 | |||
242 | public bool Delete(string id) | ||
243 | { | ||
244 | string query = String.Format("delete from {0} where \"id\" = :id", m_Table); | ||
245 | using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString)) | ||
246 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | ||
247 | { | ||
248 | dbcon.Open(); | ||
249 | cmd.Parameters.Add(m_database.CreateParameter("id", new UUID(id))); | ||
250 | cmd.ExecuteNonQuery(); | ||
251 | } | ||
252 | |||
253 | return true; | ||
254 | } | ||
255 | |||
256 | public void Import(string conn, string table, int start, int count, bool force, FSStoreDelegate store) | ||
257 | { | ||
258 | int imported = 0; | ||
259 | string limit = String.Empty; | ||
260 | if(count != -1) | ||
261 | { | ||
262 | limit = String.Format(" limit {0} offset {1}", start, count); | ||
263 | } | ||
264 | string query = String.Format("select * from {0}{1}", table, limit); | ||
265 | try | ||
266 | { | ||
267 | using (NpgsqlConnection remote = new NpgsqlConnection(conn)) | ||
268 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, remote)) | ||
269 | { | ||
270 | remote.Open(); | ||
271 | MainConsole.Instance.Output("Querying database"); | ||
272 | MainConsole.Instance.Output("Reading data"); | ||
273 | using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default)) | ||
274 | { | ||
275 | while (reader.Read()) | ||
276 | { | ||
277 | if ((imported % 100) == 0) | ||
278 | { | ||
279 | MainConsole.Instance.Output(String.Format("{0} assets imported so far", imported)); | ||
280 | } | ||
281 | |||
282 | AssetBase asset = new AssetBase(); | ||
283 | AssetMetadata meta = new AssetMetadata(); | ||
284 | |||
285 | meta.ID = reader["id"].ToString(); | ||
286 | meta.FullID = new UUID(meta.ID); | ||
287 | |||
288 | meta.Name = String.Empty; | ||
289 | meta.Description = String.Empty; | ||
290 | meta.Type = (sbyte)Convert.ToInt32(reader["assetType"]); | ||
291 | meta.ContentType = SLUtil.SLAssetTypeToContentType(meta.Type); | ||
292 | meta.CreationDate = Util.ToDateTime(Convert.ToInt32(reader["create_time"])); | ||
293 | |||
294 | asset.Metadata = meta; | ||
295 | asset.Data = (byte[])reader["data"]; | ||
296 | |||
297 | store(asset, force); | ||
298 | |||
299 | imported++; | ||
300 | } | ||
301 | } | ||
302 | } | ||
303 | } | ||
304 | catch (Exception e) | ||
305 | { | ||
306 | m_log.ErrorFormat("[PGSQL FSASSETS]: Error importing assets: {0}", | ||
307 | e.Message.ToString()); | ||
308 | return; | ||
309 | } | ||
310 | |||
311 | MainConsole.Instance.Output(String.Format("Import done, {0} assets imported", imported)); | ||
312 | } | ||
313 | |||
314 | #endregion | ||
315 | } | ||
316 | } | ||
diff --git a/OpenSim/Data/PGSQL/Resources/FSAssetStore.migrations b/OpenSim/Data/PGSQL/Resources/FSAssetStore.migrations new file mode 100644 index 0000000..3a072e5 --- /dev/null +++ b/OpenSim/Data/PGSQL/Resources/FSAssetStore.migrations | |||
@@ -0,0 +1,14 @@ | |||
1 | :VERSION 1 | ||
2 | |||
3 | BEGIN TRANSACTION; | ||
4 | |||
5 | CREATE TABLE fsassets ( | ||
6 | "id" uuid NOT NULL PRIMARY KEY, | ||
7 | "type" integer NOT NULL, | ||
8 | "hash" char(64) NOT NULL, | ||
9 | "create_time" integer NOT NULL DEFAULT '0', | ||
10 | "access_time" integer NOT NULL DEFAULT '0', | ||
11 | "asset_flags" integer NOT NULL DEFAULT '0' | ||
12 | ); | ||
13 | |||
14 | COMMIT; | ||