aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim')
-rw-r--r--OpenSim/Data/PGSQL/PGSQLFSAssetData.cs316
-rw-r--r--OpenSim/Data/PGSQL/Resources/FSAssetStore.migrations14
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
28using System;
29using System.Reflection;
30using System.Collections.Generic;
31using System.Data;
32using OpenSim.Framework;
33using OpenSim.Framework.Console;
34using log4net;
35using OpenMetaverse;
36using Npgsql;
37using NpgsqlTypes;
38
39namespace 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
3BEGIN TRANSACTION;
4
5CREATE 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
14COMMIT;