From c52c68f314c67c76c7181a6d0828f476290fbd66 Mon Sep 17 00:00:00 2001 From: Sean Dague Date: Wed, 2 Apr 2008 15:24:31 +0000 Subject: whole lot more moving --- OpenSim/Data/SQLite/Properties/AssemblyInfo.cs | 65 + OpenSim/Data/SQLite/Resources/001_AssetStore.sql | 13 + .../Data/SQLite/Resources/001_InventoryStore.sql | 26 + OpenSim/Data/SQLite/Resources/001_RegionStore.sql | 122 ++ OpenSim/Data/SQLite/Resources/001_UserStore.sql | 37 + OpenSim/Data/SQLite/SQLiteAssetData.cs | 301 ++++ OpenSim/Data/SQLite/SQLiteGridData.cs | 234 +++ OpenSim/Data/SQLite/SQLiteInventoryStore.cs | 664 ++++++++ OpenSim/Data/SQLite/SQLiteManager.cs | 282 ++++ OpenSim/Data/SQLite/SQLiteRegionData.cs | 1741 ++++++++++++++++++++ OpenSim/Data/SQLite/SQLiteUserData.cs | 821 +++++++++ OpenSim/Data/SQLite/SQLiteUtils.cs | 269 +++ 12 files changed, 4575 insertions(+) create mode 100644 OpenSim/Data/SQLite/Properties/AssemblyInfo.cs create mode 100644 OpenSim/Data/SQLite/Resources/001_AssetStore.sql create mode 100644 OpenSim/Data/SQLite/Resources/001_InventoryStore.sql create mode 100644 OpenSim/Data/SQLite/Resources/001_RegionStore.sql create mode 100644 OpenSim/Data/SQLite/Resources/001_UserStore.sql create mode 100644 OpenSim/Data/SQLite/SQLiteAssetData.cs create mode 100644 OpenSim/Data/SQLite/SQLiteGridData.cs create mode 100644 OpenSim/Data/SQLite/SQLiteInventoryStore.cs create mode 100644 OpenSim/Data/SQLite/SQLiteManager.cs create mode 100644 OpenSim/Data/SQLite/SQLiteRegionData.cs create mode 100644 OpenSim/Data/SQLite/SQLiteUserData.cs create mode 100644 OpenSim/Data/SQLite/SQLiteUtils.cs (limited to 'OpenSim/Data/SQLite') diff --git a/OpenSim/Data/SQLite/Properties/AssemblyInfo.cs b/OpenSim/Data/SQLite/Properties/AssemblyInfo.cs new file mode 100644 index 0000000..600405e --- /dev/null +++ b/OpenSim/Data/SQLite/Properties/AssemblyInfo.cs @@ -0,0 +1,65 @@ +/* + * Copyright (c) Contributors, http://opensimulator.org/ + * See CONTRIBUTORS.TXT for a full list of copyright holders. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions are met: + * * Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * * Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * * Neither the name of the OpenSim Project nor the + * names of its contributors may be used to endorse or promote products + * derived from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY + * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED + * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY + * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES + * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; + * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT + * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS + * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + */ + +using System.Reflection; +using System.Runtime.InteropServices; + +// General Information about an assembly is controlled through the following +// set of attributes. Change these attribute values to modify the information +// associated with an assembly. + +[assembly : AssemblyTitle("OpenSim.Framework.Data.SQLite")] +[assembly : AssemblyDescription("")] +[assembly : AssemblyConfiguration("")] +[assembly : AssemblyCompany("")] +[assembly : AssemblyProduct("OpenSim.Framework.Data.SQLite")] +[assembly : AssemblyCopyright("Copyright (c) OpenSimulator.org Developers 2007-2008")] +[assembly : AssemblyTrademark("")] +[assembly : AssemblyCulture("")] + +// Setting ComVisible to false makes the types in this assembly not visible +// to COM components. If you need to access a type in this assembly from +// COM, set the ComVisible attribute to true on that type. + +[assembly : ComVisible(false)] + +// The following GUID is for the ID of the typelib if this project is exposed to COM + +[assembly : Guid("6113d5ce-4547-49f4-9236-0dcc503457b1")] + +// Version information for an assembly consists of the following four values: +// +// Major Version +// Minor Version +// Build Number +// Revision +// +// You can specify all the values or you can default the Revision and Build Numbers +// by using the '*' as shown below: + +[assembly : AssemblyVersion("1.0.0.0")] +[assembly : AssemblyFileVersion("1.0.0.0")] diff --git a/OpenSim/Data/SQLite/Resources/001_AssetStore.sql b/OpenSim/Data/SQLite/Resources/001_AssetStore.sql new file mode 100644 index 0000000..1dc05d8 --- /dev/null +++ b/OpenSim/Data/SQLite/Resources/001_AssetStore.sql @@ -0,0 +1,13 @@ +BEGIN TRANSACTION; + +CREATE TABLE assets( + UUID varchar(255) primary key, + Name varchar(255), + Description varchar(255), + Type integer, + InvType integer, + Local integer, + Temporary integer, + Data blob); + +COMMIT; diff --git a/OpenSim/Data/SQLite/Resources/001_InventoryStore.sql b/OpenSim/Data/SQLite/Resources/001_InventoryStore.sql new file mode 100644 index 0000000..e4951b0 --- /dev/null +++ b/OpenSim/Data/SQLite/Resources/001_InventoryStore.sql @@ -0,0 +1,26 @@ +BEGIN TRANSACTION; + +CREATE TABLE inventoryitems( + UUID varchar(255) primary key, + assetID varchar(255), + assetType integer, + invType integer, + parentFolderID varchar(255), + avatarID varchar(255), + creatorsID varchar(255), + inventoryName varchar(255), + inventoryDescription varchar(255), + inventoryNextPermissions integer, + inventoryCurrentPermissions integer, + inventoryBasePermissions integer, + inventoryEveryOnePermissions integer); + +CREATE TABLE inventoryfolders( + UUID varchar(255) primary key, + name varchar(255), + agentID varchar(255), + parentID varchar(255), + type integer, + version integer); + +COMMIT; diff --git a/OpenSim/Data/SQLite/Resources/001_RegionStore.sql b/OpenSim/Data/SQLite/Resources/001_RegionStore.sql new file mode 100644 index 0000000..15f3d9f --- /dev/null +++ b/OpenSim/Data/SQLite/Resources/001_RegionStore.sql @@ -0,0 +1,122 @@ +BEGIN TRANSACTION; + +CREATE TABLE prims( + UUID varchar(255) primary key, + RegionUUID varchar(255), + ParentID integer, + CreationDate integer, + Name varchar(255), + SceneGroupID varchar(255), + Text varchar(255), + Description varchar(255), + SitName varchar(255), + TouchName varchar(255), + CreatorID varchar(255), + OwnerID varchar(255), + GroupID varchar(255), + LastOwnerID varchar(255), + OwnerMask integer, + NextOwnerMask integer, + GroupMask integer, + EveryoneMask integer, + BaseMask integer, + PositionX float, + PositionY float, + PositionZ float, + GroupPositionX float, + GroupPositionY float, + GroupPositionZ float, + VelocityX float, + VelocityY float, + VelocityZ float, + AngularVelocityX float, + AngularVelocityY float, + AngularVelocityZ float, + AccelerationX float, + AccelerationY float, + AccelerationZ float, + RotationX float, + RotationY float, + RotationZ float, + RotationW float, + ObjectFlags integer, + SitTargetOffsetX float NOT NULL default 0, + SitTargetOffsetY float NOT NULL default 0, + SitTargetOffsetZ float NOT NULL default 0, + SitTargetOrientW float NOT NULL default 0, + SitTargetOrientX float NOT NULL default 0, + SitTargetOrientY float NOT NULL default 0, + SitTargetOrientZ float NOT NULL default 0); + +CREATE TABLE primshapes(UUID varchar(255) primary key, + Shape integer, + ScaleX float, + ScaleY float, + ScaleZ float, + PCode integer, + PathBegin integer, + PathEnd integer, + PathScaleX integer, + PathScaleY integer, + PathShearX integer, + PathShearY integer, + PathSkew integer, + PathCurve integer, + PathRadiusOffset integer, + PathRevolutions integer, + PathTaperX integer, + PathTaperY integer, + PathTwist integer, + PathTwistBegin integer, + ProfileBegin integer, + ProfileEnd integer, + ProfileCurve integer, + ProfileHollow integer, + State integer, + Texture blob, + ExtraParams blob); + +CREATE TABLE terrain( + RegionUUID varchar(255), + Revision integer, + Heightfield blob); + +CREATE TABLE land( + UUID varchar(255) primary key, + RegionUUID varchar(255), + LocalLandID string, + Bitmap blob, + Name varchar(255), + Desc varchar(255), + OwnerUUID varchar(36), + IsGroupOwned string, + Area integer, + AuctionID integer, + Category integer, + ClaimDate integer, + ClaimPrice integer, + GroupUUID varchar(255), + SalePrice integer, + LandStatus integer, + LandFlags string, + LandingType string, + MediaAutoScale string, + MediaTextureUUID varchar(255), + MediaURL varchar(255), + MusicURL varchar(255), + PassHours float, + PassPrice string, + SnapshotUUID varchar(255), + UserLocationX float, + UserLocationY float, + UserLocationZ float, + UserLookAtX float, + UserLookAtY float, + UserLookAtZ float); + +CREATE TABLE landaccesslist( + LandUUID varchar(255), + AccessUUID varchar(255), + Flags string); + +COMMIT; diff --git a/OpenSim/Data/SQLite/Resources/001_UserStore.sql b/OpenSim/Data/SQLite/Resources/001_UserStore.sql new file mode 100644 index 0000000..070e340 --- /dev/null +++ b/OpenSim/Data/SQLite/Resources/001_UserStore.sql @@ -0,0 +1,37 @@ +BEGIN TRANSACTION; + +CREATE TABLE users( + UUID varchar(255) primary key, + username varchar(255), + surname varchar(255), + passwordHash varchar(255), + passwordSalt varchar(255), + homeRegionX integer, + homeRegionY integer, + homeLocationX float, + homeLocationY float, + homeLocationZ float, + homeLookAtX float, + homeLookAtY float, + homeLookAtZ float, + created integer, + lastLogin integer, + rootInventoryFolderID varchar(255), + userInventoryURI varchar(255), + userAssetURI varchar(255), + profileCanDoMask integer, + profileWantDoMask integer, + profileAboutText varchar(255), + profileFirstText varchar(255), + profileImage varchar(255), + profileFirstImage varchar(255), + webLoginKey text default '00000000-0000-0000-0000-000000000000'); + +CREATE TABLE userfriends( + ownerID varchar(255), + friendID varchar(255), + friendPerms integer, + ownerPerms integer, + datetimestamp integer); + +COMMIT; diff --git a/OpenSim/Data/SQLite/SQLiteAssetData.cs b/OpenSim/Data/SQLite/SQLiteAssetData.cs new file mode 100644 index 0000000..afa73b1 --- /dev/null +++ b/OpenSim/Data/SQLite/SQLiteAssetData.cs @@ -0,0 +1,301 @@ +/* + * Copyright (c) Contributors, http://opensimulator.org/ + * See CONTRIBUTORS.TXT for a full list of copyright holders. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions are met: + * * Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * * Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * * Neither the name of the OpenSim Project nor the + * names of its contributors may be used to endorse or promote products + * derived from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY + * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED + * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY + * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES + * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; + * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT + * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS + * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + */ + +using System; +using System.Data; +using System.Reflection; +using libsecondlife; +using Mono.Data.SqliteClient; +using OpenSim.Framework.Console; + +namespace OpenSim.Framework.Data.SQLite +{ + /// + /// A User storage interface for the DB4o database system + /// + public class SQLiteAssetData : AssetDataBase + { + private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); + + /// + /// The database manager + /// + /// + /// Artificial constructor called upon plugin load + /// + private const string SelectAssetSQL = "select * from assets where UUID=:UUID"; + private const string DeleteAssetSQL = "delete from assets where UUID=:UUID"; + private const string InsertAssetSQL = "insert into assets(UUID, Name, Description, Type, InvType, Local, Temporary, Data) values(:UUID, :Name, :Description, :Type, :InvType, :Local, :Temporary, :Data)"; + private const string UpdateAssetSQL = "update assets set Name=:Name, Description=:Description, Type=:Type, InvType=:InvType, Local=:Local, Temporary=:Temporary, Data=:Data where UUID=:UUID"; + private const string assetSelect = "select * from assets"; + + private SqliteConnection m_conn; + + public void Initialise(string dbfile, string dbname) + { + m_conn = new SqliteConnection("URI=file:" + dbfile + ",version=3"); + m_conn.Open(); + TestTables(m_conn); + return; + } + + override public AssetBase FetchAsset(LLUUID uuid) + { + + using (SqliteCommand cmd = new SqliteCommand(SelectAssetSQL, m_conn)) + { + cmd.Parameters.Add(new SqliteParameter(":UUID", Util.ToRawUuidString(uuid))); + using (IDataReader reader = cmd.ExecuteReader()) + { + if (reader.Read()) + { + AssetBase asset = buildAsset(reader); + reader.Close(); + return asset; + } + else + { + reader.Close(); + return null; + } + } + } + } + + override public void CreateAsset(AssetBase asset) + { + m_log.Info("[SQLITE]: Creating Asset " + Util.ToRawUuidString(asset.FullID)); + if (ExistsAsset(asset.FullID)) + { + m_log.Info("[SQLITE]: Asset exists already, ignoring."); + } + else + { + using (SqliteCommand cmd = new SqliteCommand(InsertAssetSQL, m_conn)) + { + cmd.Parameters.Add(new SqliteParameter(":UUID", Util.ToRawUuidString(asset.FullID))); + cmd.Parameters.Add(new SqliteParameter(":Name", asset.Name)); + cmd.Parameters.Add(new SqliteParameter(":Description", asset.Description)); + cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type)); + cmd.Parameters.Add(new SqliteParameter(":InvType", asset.InvType)); + cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local)); + cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary)); + cmd.Parameters.Add(new SqliteParameter(":Data", asset.Data)); + + cmd.ExecuteNonQuery(); + } + } + } + + override public void UpdateAsset(AssetBase asset) + { + LogAssetLoad(asset); + + using (SqliteCommand cmd = new SqliteCommand(UpdateAssetSQL, m_conn)) + { + cmd.Parameters.Add(new SqliteParameter(":UUID", Util.ToRawUuidString(asset.FullID))); + cmd.Parameters.Add(new SqliteParameter(":Name", asset.Name)); + cmd.Parameters.Add(new SqliteParameter(":Description", asset.Description)); + cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type)); + cmd.Parameters.Add(new SqliteParameter(":InvType", asset.InvType)); + cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local)); + cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary)); + cmd.Parameters.Add(new SqliteParameter(":Data", asset.Data)); + + cmd.ExecuteNonQuery(); + } + + } + + private void LogAssetLoad(AssetBase asset) + { + string temporary = asset.Temporary ? "Temporary" : "Stored"; + string local = asset.Local ? "Local" : "Remote"; + + int assetLength = (asset.Data != null) ? asset.Data.Length : 0; + + m_log.Info("[SQLITE]: " + + string.Format("Loaded {6} {5} Asset: [{0}][{3}/{4}] \"{1}\":{2} ({7} bytes)", + asset.FullID, asset.Name, asset.Description, asset.Type, + asset.InvType, temporary, local, assetLength)); + } + + override public bool ExistsAsset(LLUUID uuid) + { + using (SqliteCommand cmd = new SqliteCommand(SelectAssetSQL, m_conn)) + { + cmd.Parameters.Add(new SqliteParameter(":UUID", Util.ToRawUuidString(uuid))); + using (IDataReader reader = cmd.ExecuteReader()) + { + if(reader.Read()) + { + reader.Close(); + return true; + } + else + { + reader.Close(); + return false; + } + } + } + } + + public void DeleteAsset(LLUUID uuid) + { + using (SqliteCommand cmd = new SqliteCommand(DeleteAssetSQL, m_conn)) + { + cmd.Parameters.Add(new SqliteParameter(":UUID", Util.ToRawUuidString(uuid))); + + cmd.ExecuteNonQuery(); + } + } + + override public void CommitAssets() // force a sync to the database + { + m_log.Info("[SQLITE]: Attempting commit"); + // lock (ds) + // { + // da.Update(ds, "assets"); + // ds.AcceptChanges(); + // } + } + + /*********************************************************************** + * + * Database Definition Functions + * + * This should be db agnostic as we define them in ADO.NET terms + * + **********************************************************************/ + + private DataTable createAssetsTable() + { + DataTable assets = new DataTable("assets"); + + SQLiteUtil.createCol(assets, "UUID", typeof (String)); + SQLiteUtil.createCol(assets, "Name", typeof (String)); + SQLiteUtil.createCol(assets, "Description", typeof (String)); + SQLiteUtil.createCol(assets, "Type", typeof (Int32)); + SQLiteUtil.createCol(assets, "InvType", typeof (Int32)); + SQLiteUtil.createCol(assets, "Local", typeof (Boolean)); + SQLiteUtil.createCol(assets, "Temporary", typeof (Boolean)); + SQLiteUtil.createCol(assets, "Data", typeof (Byte[])); + // Add in contraints + assets.PrimaryKey = new DataColumn[] {assets.Columns["UUID"]}; + return assets; + } + + /*********************************************************************** + * + * Convert between ADO.NET <=> OpenSim Objects + * + * These should be database independant + * + **********************************************************************/ + + private AssetBase buildAsset(IDataReader row) + { + // TODO: this doesn't work yet because something more + // interesting has to be done to actually get these values + // back out. Not enough time to figure it out yet. + AssetBase asset = new AssetBase(); + + asset.FullID = new LLUUID((String) row["UUID"]); + asset.Name = (String) row["Name"]; + asset.Description = (String) row["Description"]; + asset.Type = Convert.ToSByte(row["Type"]); + asset.InvType = Convert.ToSByte(row["InvType"]); + asset.Local = Convert.ToBoolean(row["Local"]); + asset.Temporary = Convert.ToBoolean(row["Temporary"]); + asset.Data = (byte[]) row["Data"]; + return asset; + } + + + /*********************************************************************** + * + * Database Binding functions + * + * These will be db specific due to typing, and minor differences + * in databases. + * + **********************************************************************/ + + private void InitDB(SqliteConnection conn) + { + string createAssets = SQLiteUtil.defineTable(createAssetsTable()); + SqliteCommand pcmd = new SqliteCommand(createAssets, conn); + pcmd.ExecuteNonQuery(); + } + + private bool TestTables(SqliteConnection conn) + { + SqliteCommand cmd = new SqliteCommand(assetSelect, conn); + SqliteDataAdapter pDa = new SqliteDataAdapter(cmd); + DataSet tmpDS = new DataSet(); + try + { + pDa.Fill(tmpDS, "assets"); + } + catch (SqliteSyntaxException) + { + m_log.Info("[SQLITE]: SQLite Database doesn't exist... creating"); + InitDB(conn); + } + return true; + } + + #region IPlugin interface + + override public string Version + { + get + { + Module module = GetType().Module; + string dllName = module.Assembly.ManifestModule.Name; + Version dllVersion = module.Assembly.GetName().Version; + + return + string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build, + dllVersion.Revision); + } + } + + override public void Initialise() + { + Initialise("AssetStorage.db", ""); + } + + override public string Name + { + get { return "SQLite Asset storage engine"; } + } + + #endregion + } +} diff --git a/OpenSim/Data/SQLite/SQLiteGridData.cs b/OpenSim/Data/SQLite/SQLiteGridData.cs new file mode 100644 index 0000000..94e8e50 --- /dev/null +++ b/OpenSim/Data/SQLite/SQLiteGridData.cs @@ -0,0 +1,234 @@ +/* + * Copyright (c) Contributors, http://opensimulator.org/ + * See CONTRIBUTORS.TXT for a full list of copyright holders. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions are met: + * * Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * * Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * * Neither the name of the OpenSim Project nor the + * names of its contributors may be used to endorse or promote products + * derived from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY + * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED + * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY + * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES + * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; + * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT + * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS + * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + */ + +using System; +using System.Collections.Generic; +using System.Data; +using System.Security.Cryptography; +using System.Text; +using libsecondlife; + +namespace OpenSim.Framework.Data.SQLite +{ + /// + /// A Grid Interface to the SQLite database + /// + public class SQLiteGridData : GridDataBase + { + /// + /// A database manager + /// + private SQLiteManager database; + + /// + /// Initialises the Grid Interface + /// + override public void Initialise() + { + database = new SQLiteManager("localhost", "db", "user", "password", "false"); + } + + /// + /// Shuts down the grid interface + /// + override public void Close() + { + database.Close(); + } + + /// + /// Returns the name of this grid interface + /// + /// A string containing the grid interface + override public string getName() + { + return "SQLite OpenGridData"; + } + + /// + /// Returns the version of this grid interface + /// + /// A string containing the version + override public string getVersion() + { + return "0.1"; + } + + /// + /// Returns a list of regions within the specified ranges + /// + /// minimum X coordinate + /// minimum Y coordinate + /// maximum X coordinate + /// maximum Y coordinate + /// An array of region profiles + override public RegionProfileData[] GetProfilesInRange(uint a, uint b, uint c, uint d) + { + return null; + } + + /// + /// Returns a sim profile from it's location + /// + /// Region location handle + /// Sim profile + override public RegionProfileData GetProfileByHandle(ulong handle) + { + Dictionary param = new Dictionary(); + param["handle"] = handle.ToString(); + + IDbCommand result = database.Query("SELECT * FROM regions WHERE handle = @handle", param); + IDataReader reader = result.ExecuteReader(); + + RegionProfileData row = database.getRow(reader); + reader.Close(); + result.Dispose(); + + return row; + } + + /// + /// Returns a sim profile from it's Region name string + /// + /// The region name search query + /// The sim profile + override public RegionProfileData GetProfileByString(string regionName) + { + if (regionName.Length > 2) + { + Dictionary param = new Dictionary(); + // Add % because this is a like query. + param["?regionName"] = regionName + "%"; + // Only returns one record or no record. + IDbCommand result = database.Query("SELECT * FROM regions WHERE regionName like ?regionName LIMIT 1", param); + IDataReader reader = result.ExecuteReader(); + + RegionProfileData row = database.getRow(reader); + reader.Close(); + result.Dispose(); + + return row; + } + else + { + //m_log.Error("[DATABASE]: Searched for a Region Name shorter then 3 characters"); + return null; + } + } + + /// + /// Returns a sim profile from it's UUID + /// + /// The region UUID + /// The sim profile + override public RegionProfileData GetProfileByLLUUID(LLUUID uuid) + { + Dictionary param = new Dictionary(); + param["uuid"] = uuid.ToString(); + + IDbCommand result = database.Query("SELECT * FROM regions WHERE uuid = @uuid", param); + IDataReader reader = result.ExecuteReader(); + + RegionProfileData row = database.getRow(reader); + reader.Close(); + result.Dispose(); + + return row; + } + + /// + /// // Returns a list of avatar and UUIDs that match the query + /// + public List GeneratePickerResults(LLUUID queryID, string query) + { + //Do nothing yet + List returnlist = new List(); + return returnlist; + } + + /// + /// Adds a new specified region to the database + /// + /// The profile to add + /// A dataresponse enum indicating success + override public DataResponse AddProfile(RegionProfileData profile) + { + if (database.insertRow(profile)) + { + return DataResponse.RESPONSE_OK; + } + else + { + return DataResponse.RESPONSE_ERROR; + } + } + + /// + /// DEPRECATED. Attempts to authenticate a region by comparing a shared secret. + /// + /// The UUID of the challenger + /// The attempted regionHandle of the challenger + /// The secret + /// Whether the secret and regionhandle match the database entry for UUID + override public bool AuthenticateSim(LLUUID uuid, ulong handle, string authkey) + { + bool throwHissyFit = false; // Should be true by 1.0 + + if (throwHissyFit) + throw new Exception("CRYPTOWEAK AUTHENTICATE: Refusing to authenticate due to replay potential."); + + RegionProfileData data = GetProfileByLLUUID(uuid); + + return (handle == data.regionHandle && authkey == data.regionSecret); + } + + /// + /// NOT YET FUNCTIONAL. Provides a cryptographic authentication of a region + /// + /// This requires a security audit. + /// + /// + /// + /// + /// + public bool AuthenticateSim(LLUUID uuid, ulong handle, string authhash, string challenge) + { + SHA512Managed HashProvider = new SHA512Managed(); + ASCIIEncoding TextProvider = new ASCIIEncoding(); + + byte[] stream = TextProvider.GetBytes(uuid.ToString() + ":" + handle.ToString() + ":" + challenge); + byte[] hash = HashProvider.ComputeHash(stream); + + return false; + } + + override public ReservationData GetReservationAtPoint(uint x, uint y) + { + return null; + } + } +} diff --git a/OpenSim/Data/SQLite/SQLiteInventoryStore.cs b/OpenSim/Data/SQLite/SQLiteInventoryStore.cs new file mode 100644 index 0000000..d31863f --- /dev/null +++ b/OpenSim/Data/SQLite/SQLiteInventoryStore.cs @@ -0,0 +1,664 @@ +/* + * Copyright (c) Contributors, http://opensimulator.org/ + * See CONTRIBUTORS.TXT for a full list of copyright holders. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions are met: + * * Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * * Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * * Neither the name of the OpenSim Project nor the + * names of its contributors may be used to endorse or promote products + * derived from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY + * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED + * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY + * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES + * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; + * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT + * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS + * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + */ + +using System; +using System.Collections.Generic; +using System.Data; +using System.Reflection; +using libsecondlife; +using Mono.Data.SqliteClient; +using OpenSim.Framework.Console; + +namespace OpenSim.Framework.Data.SQLite +{ + public class SQLiteInventoryStore : SQLiteUtil, IInventoryData + { + private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); + + private const string invItemsSelect = "select * from inventoryitems"; + private const string invFoldersSelect = "select * from inventoryfolders"; + + private DataSet ds; + private SqliteDataAdapter invItemsDa; + private SqliteDataAdapter invFoldersDa; + + /// + /// Initialises the interface + /// + public void Initialise() + { + Initialise("inventoryStore.db", "inventoryDatabase"); + } + + public void Initialise(string dbfile, string dbname) + { + string connectionString = "URI=file:" + dbfile + ",version=3"; + + m_log.Info("[Inventory]: Sqlite - connecting: " + dbfile); + SqliteConnection conn = new SqliteConnection(connectionString); + + conn.Open(); + + TestTables(conn); + + SqliteCommand itemsSelectCmd = new SqliteCommand(invItemsSelect, conn); + invItemsDa = new SqliteDataAdapter(itemsSelectCmd); + // SqliteCommandBuilder primCb = new SqliteCommandBuilder(primDa); + + SqliteCommand foldersSelectCmd = new SqliteCommand(invFoldersSelect, conn); + invFoldersDa = new SqliteDataAdapter(foldersSelectCmd); + + ds = new DataSet(); + + ds.Tables.Add(createInventoryFoldersTable()); + invFoldersDa.Fill(ds.Tables["inventoryfolders"]); + setupFoldersCommands(invFoldersDa, conn); + m_log.Info("[DATASTORE]: Populated Intentory Folders Definitions"); + + ds.Tables.Add(createInventoryItemsTable()); + invItemsDa.Fill(ds.Tables["inventoryitems"]); + setupItemsCommands(invItemsDa, conn); + m_log.Info("[DATASTORE]: Populated Intentory Items Definitions"); + + ds.AcceptChanges(); + } + + public InventoryItemBase buildItem(DataRow row) + { + InventoryItemBase item = new InventoryItemBase(); + item.inventoryID = new LLUUID((string) row["UUID"]); + item.assetID = new LLUUID((string) row["assetID"]); + item.assetType = Convert.ToInt32(row["assetType"]); + item.invType = Convert.ToInt32(row["invType"]); + item.parentFolderID = new LLUUID((string) row["parentFolderID"]); + item.avatarID = new LLUUID((string) row["avatarID"]); + item.creatorsID = new LLUUID((string) row["creatorsID"]); + item.inventoryName = (string) row["inventoryName"]; + item.inventoryDescription = (string) row["inventoryDescription"]; + + item.inventoryNextPermissions = Convert.ToUInt32(row["inventoryNextPermissions"]); + item.inventoryCurrentPermissions = Convert.ToUInt32(row["inventoryCurrentPermissions"]); + item.inventoryBasePermissions = Convert.ToUInt32(row["inventoryBasePermissions"]); + item.inventoryEveryOnePermissions = Convert.ToUInt32(row["inventoryEveryOnePermissions"]); + return item; + } + + private void fillItemRow(DataRow row, InventoryItemBase item) + { + row["UUID"] = Util.ToRawUuidString(item.inventoryID); + row["assetID"] = Util.ToRawUuidString(item.assetID); + row["assetType"] = item.assetType; + row["invType"] = item.invType; + row["parentFolderID"] = Util.ToRawUuidString(item.parentFolderID); + row["avatarID"] = Util.ToRawUuidString(item.avatarID); + row["creatorsID"] = Util.ToRawUuidString(item.creatorsID); + row["inventoryName"] = item.inventoryName; + row["inventoryDescription"] = item.inventoryDescription; + + row["inventoryNextPermissions"] = item.inventoryNextPermissions; + row["inventoryCurrentPermissions"] = item.inventoryCurrentPermissions; + row["inventoryBasePermissions"] = item.inventoryBasePermissions; + row["inventoryEveryOnePermissions"] = item.inventoryEveryOnePermissions; + } + + private void addFolder(InventoryFolderBase folder) + { + lock (ds) + { + DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; + + DataRow inventoryRow = inventoryFolderTable.Rows.Find(Util.ToRawUuidString(folder.folderID)); + if (inventoryRow == null) + { + inventoryRow = inventoryFolderTable.NewRow(); + fillFolderRow(inventoryRow, folder); + inventoryFolderTable.Rows.Add(inventoryRow); + } + else + { + fillFolderRow(inventoryRow, folder); + } + + invFoldersDa.Update(ds, "inventoryfolders"); + } + } + + private void moveFolder(InventoryFolderBase folder) + { + lock (ds) + { + DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; + + DataRow inventoryRow = inventoryFolderTable.Rows.Find(Util.ToRawUuidString(folder.folderID)); + if (inventoryRow == null) + { + inventoryRow = inventoryFolderTable.NewRow(); + fillFolderRow(inventoryRow, folder); + inventoryFolderTable.Rows.Add(inventoryRow); + } + else + { + moveFolderRow(inventoryRow, folder); + } + + invFoldersDa.Update(ds, "inventoryfolders"); + } + } + + private void addItem(InventoryItemBase item) + { + lock (ds) + { + DataTable inventoryItemTable = ds.Tables["inventoryitems"]; + + DataRow inventoryRow = inventoryItemTable.Rows.Find(Util.ToRawUuidString(item.inventoryID)); + if (inventoryRow == null) + { + inventoryRow = inventoryItemTable.NewRow(); + fillItemRow(inventoryRow, item); + inventoryItemTable.Rows.Add(inventoryRow); + } + else + { + fillItemRow(inventoryRow, item); + } + invItemsDa.Update(ds, "inventoryitems"); + } + } + + public void Shutdown() + { + // TODO: DataSet commit + } + + /// + /// Closes the interface + /// + public void Close() + { + } + + /// + /// The plugin being loaded + /// + /// A string containing the plugin name + public string getName() + { + return "SQLite Inventory Data Interface"; + } + + /// + /// The plugins version + /// + /// A string containing the plugin version + public string getVersion() + { + Module module = GetType().Module; + string dllName = module.Assembly.ManifestModule.Name; + Version dllVersion = module.Assembly.GetName().Version; + + + return + string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build, + dllVersion.Revision); + } + + /// + /// Returns a list of inventory items contained within the specified folder + /// + /// The UUID of the target folder + /// A List of InventoryItemBase items + public List getInventoryInFolder(LLUUID folderID) + { + lock (ds) + { + List retval = new List(); + DataTable inventoryItemTable = ds.Tables["inventoryitems"]; + string selectExp = "parentFolderID = '" + Util.ToRawUuidString(folderID) + "'"; + DataRow[] rows = inventoryItemTable.Select(selectExp); + foreach (DataRow row in rows) + { + retval.Add(buildItem(row)); + } + + return retval; + } + } + + /// + /// Returns a list of the root folders within a users inventory + /// + /// The user whos inventory is to be searched + /// A list of folder objects + public List getUserRootFolders(LLUUID user) + { + return new List(); + } + + // see InventoryItemBase.getUserRootFolder + public InventoryFolderBase getUserRootFolder(LLUUID user) + { + lock (ds) + { + List folders = new List(); + DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; + string selectExp = "agentID = '" + Util.ToRawUuidString(user) + "' AND parentID = '" + + Util.ToRawUuidString(LLUUID.Zero) + "'"; + DataRow[] rows = inventoryFolderTable.Select(selectExp); + foreach (DataRow row in rows) + { + folders.Add(buildFolder(row)); + } + + // There should only ever be one root folder for a user. However, if there's more + // than one we'll simply use the first one rather than failing. It would be even + // nicer to print some message to this effect, but this feels like it's too low a + // to put such a message out, and it's too minor right now to spare the time to + // suitably refactor. + if (folders.Count > 0) + { + return folders[0]; + } + + return null; + } + } + + /// + /// Append a list of all the child folders of a parent folder + /// + /// list where folders will be appended + /// ID of parent + protected void getInventoryFolders(ref List folders, LLUUID parentID) + { + lock (ds) + { + DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; + string selectExp = "parentID = '" + Util.ToRawUuidString(parentID) + "'"; + DataRow[] rows = inventoryFolderTable.Select(selectExp); + foreach (DataRow row in rows) + { + folders.Add(buildFolder(row)); + } + } + } + + /// + /// Returns a list of inventory folders contained in the folder 'parentID' + /// + /// The folder to get subfolders for + /// A list of inventory folders + public List getInventoryFolders(LLUUID parentID) + { + List folders = new List(); + getInventoryFolders(ref folders, Util.ToRawUuidString(parentID)); + return folders; + } + + // See IInventoryData + public List getFolderHierarchy(LLUUID parentID) + { + List folders = new List(); + getInventoryFolders(ref folders, Util.ToRawUuidString(parentID)); + + for (int i = 0; i < folders.Count; i++) + getInventoryFolders(ref folders, Util.ToRawUuidString(folders[i].folderID)); + + return folders; + } + + /// + /// Returns an inventory item by its UUID + /// + /// The UUID of the item to be returned + /// A class containing item information + public InventoryItemBase getInventoryItem(LLUUID item) + { + lock (ds) + { + DataRow row = ds.Tables["inventoryitems"].Rows.Find(Util.ToRawUuidString(item)); + if (row != null) + { + return buildItem(row); + } + else + { + return null; + } + } + } + + /// + /// Returns a specified inventory folder by its UUID + /// + /// The UUID of the folder to be returned + /// A class containing folder information + public InventoryFolderBase getInventoryFolder(LLUUID folder) + { + // TODO: Deep voodoo here. If you enable this code then + // multi region breaks. No idea why, but I figured it was + // better to leave multi region at this point. It does mean + // that you don't get to see system textures why creating + // clothes and the like. :( + lock (ds) + { + DataRow row = ds.Tables["inventoryfolders"].Rows.Find(Util.ToRawUuidString(folder)); + if (row != null) + { + return buildFolder(row); + } + else + { + return null; + } + } + } + + /// + /// Creates a new inventory item based on item + /// + /// The item to be created + public void addInventoryItem(InventoryItemBase item) + { + addItem(item); + } + + /// + /// Updates an inventory item with item (updates based on ID) + /// + /// The updated item + public void updateInventoryItem(InventoryItemBase item) + { + addItem(item); + } + + /// + /// + /// + /// + public void deleteInventoryItem(LLUUID itemID) + { + lock (ds) + { + DataTable inventoryItemTable = ds.Tables["inventoryitems"]; + + DataRow inventoryRow = inventoryItemTable.Rows.Find(Util.ToRawUuidString(itemID)); + if (inventoryRow != null) + { + inventoryRow.Delete(); + } + + invItemsDa.Update(ds, "inventoryitems"); + } + } + + /// + /// Delete all items in the specified folder + /// + /// id of the folder, whose item content should be deleted + //!TODO, this is horribly inefficient, but I don't want to ruin the overall structure of this implementation + private void deleteItemsInFolder(LLUUID folderId) + { + List items = getInventoryInFolder(Util.ToRawUuidString(folderId)); + + foreach (InventoryItemBase i in items) + deleteInventoryItem(Util.ToRawUuidString(i.inventoryID)); + } + + /// + /// Adds a new folder specified by folder + /// + /// The inventory folder + public void addInventoryFolder(InventoryFolderBase folder) + { + addFolder(folder); + } + + /// + /// Updates a folder based on its ID with folder + /// + /// The inventory folder + public void updateInventoryFolder(InventoryFolderBase folder) + { + addFolder(folder); + } + + /// + /// Moves a folder based on its ID with folder + /// + /// The inventory folder + public void moveInventoryFolder(InventoryFolderBase folder) + { + moveFolder(folder); + } + + /// + /// Delete a folder + /// + /// + /// This will clean-up any child folders and child items as well + /// + /// + public void deleteInventoryFolder(LLUUID folderID) + { + lock (ds) + { + List subFolders = getFolderHierarchy(Util.ToRawUuidString(folderID)); + + DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; + DataRow inventoryRow; + + //Delete all sub-folders + foreach (InventoryFolderBase f in subFolders) + { + inventoryRow = inventoryFolderTable.Rows.Find(Util.ToRawUuidString(f.folderID)); + if (inventoryRow != null) + { + deleteItemsInFolder(Util.ToRawUuidString(f.folderID)); + inventoryRow.Delete(); + } + } + + //Delete the actual row + inventoryRow = inventoryFolderTable.Rows.Find(Util.ToRawUuidString(folderID)); + if (inventoryRow != null) + { + deleteItemsInFolder(Util.ToRawUuidString(folderID)); + inventoryRow.Delete(); + } + + invFoldersDa.Update(ds, "inventoryfolders"); + } + } + + /*********************************************************************** + * + * Data Table definitions + * + **********************************************************************/ + + private static DataTable createInventoryItemsTable() + { + DataTable inv = new DataTable("inventoryitems"); + + createCol(inv, "UUID", typeof (String)); //inventoryID + createCol(inv, "assetID", typeof (String)); + createCol(inv, "assetType", typeof (Int32)); + createCol(inv, "invType", typeof (Int32)); + createCol(inv, "parentFolderID", typeof (String)); + createCol(inv, "avatarID", typeof (String)); + createCol(inv, "creatorsID", typeof (String)); + + createCol(inv, "inventoryName", typeof (String)); + createCol(inv, "inventoryDescription", typeof (String)); + // permissions + createCol(inv, "inventoryNextPermissions", typeof (Int32)); + createCol(inv, "inventoryCurrentPermissions", typeof (Int32)); + createCol(inv, "inventoryBasePermissions", typeof (Int32)); + createCol(inv, "inventoryEveryOnePermissions", typeof (Int32)); + + inv.PrimaryKey = new DataColumn[] {inv.Columns["UUID"]}; + return inv; + } + + private DataTable createInventoryFoldersTable() + { + DataTable fol = new DataTable("inventoryfolders"); + + createCol(fol, "UUID", typeof (String)); //folderID + createCol(fol, "name", typeof (String)); + createCol(fol, "agentID", typeof (String)); + createCol(fol, "parentID", typeof (String)); + createCol(fol, "type", typeof (Int32)); + createCol(fol, "version", typeof (Int32)); + + fol.PrimaryKey = new DataColumn[] {fol.Columns["UUID"]}; + return fol; + } + + private void setupItemsCommands(SqliteDataAdapter da, SqliteConnection conn) + { + lock (ds) + { + da.InsertCommand = createInsertCommand("inventoryitems", ds.Tables["inventoryitems"]); + da.InsertCommand.Connection = conn; + + da.UpdateCommand = createUpdateCommand("inventoryitems", "UUID=:UUID", ds.Tables["inventoryitems"]); + da.UpdateCommand.Connection = conn; + + SqliteCommand delete = new SqliteCommand("delete from inventoryitems where UUID = :UUID"); + delete.Parameters.Add(createSqliteParameter("UUID", typeof(String))); + delete.Connection = conn; + da.DeleteCommand = delete; + } + } + + private void setupFoldersCommands(SqliteDataAdapter da, SqliteConnection conn) + { + lock (ds) + { + da.InsertCommand = createInsertCommand("inventoryfolders", ds.Tables["inventoryfolders"]); + da.InsertCommand.Connection = conn; + + da.UpdateCommand = createUpdateCommand("inventoryfolders", "UUID=:UUID", ds.Tables["inventoryfolders"]); + da.UpdateCommand.Connection = conn; + + SqliteCommand delete = new SqliteCommand("delete from inventoryfolders where UUID = :UUID"); + delete.Parameters.Add(createSqliteParameter("UUID", typeof(String))); + delete.Connection = conn; + da.DeleteCommand = delete; + } + } + + private InventoryFolderBase buildFolder(DataRow row) + { + InventoryFolderBase folder = new InventoryFolderBase(); + folder.folderID = new LLUUID((string) row["UUID"]); + folder.name = (string) row["name"]; + folder.agentID = new LLUUID((string) row["agentID"]); + folder.parentID = new LLUUID((string) row["parentID"]); + folder.type = Convert.ToInt16(row["type"]); + folder.version = Convert.ToUInt16(row["version"]); + return folder; + } + + private void fillFolderRow(DataRow row, InventoryFolderBase folder) + { + row["UUID"] = Util.ToRawUuidString(folder.folderID); + row["name"] = folder.name; + row["agentID"] = Util.ToRawUuidString(folder.agentID); + row["parentID"] = Util.ToRawUuidString(folder.parentID); + row["type"] = folder.type; + row["version"] = folder.version; + } + + private void moveFolderRow(DataRow row, InventoryFolderBase folder) + { + row["UUID"] = Util.ToRawUuidString(folder.folderID); + row["parentID"] = Util.ToRawUuidString(folder.parentID); + } + + /*********************************************************************** + * + * Test and Initialization code + * + **********************************************************************/ + + private void InitDB(SqliteConnection conn) + { + string createInventoryItems = defineTable(createInventoryItemsTable()); + string createInventoryFolders = defineTable(createInventoryFoldersTable()); + + SqliteCommand pcmd = new SqliteCommand(createInventoryItems, conn); + SqliteCommand scmd = new SqliteCommand(createInventoryFolders, conn); + + pcmd.ExecuteNonQuery(); + scmd.ExecuteNonQuery(); + } + + private bool TestTables(SqliteConnection conn) + { + SqliteCommand invItemsSelectCmd = new SqliteCommand(invItemsSelect, conn); + SqliteDataAdapter pDa = new SqliteDataAdapter(invItemsSelectCmd); + SqliteCommand invFoldersSelectCmd = new SqliteCommand(invFoldersSelect, conn); + SqliteDataAdapter sDa = new SqliteDataAdapter(invFoldersSelectCmd); + + DataSet tmpDS = new DataSet(); + try + { + pDa.Fill(tmpDS, "inventoryitems"); + sDa.Fill(tmpDS, "inventoryfolders"); + } + catch (SqliteSyntaxException) + { + m_log.Info("[DATASTORE]: SQLite Database doesn't exist... creating"); + InitDB(conn); + } + + pDa.Fill(tmpDS, "inventoryitems"); + sDa.Fill(tmpDS, "inventoryfolders"); + + foreach (DataColumn col in createInventoryItemsTable().Columns) + { + if (! tmpDS.Tables["inventoryitems"].Columns.Contains(col.ColumnName)) + { + m_log.Info("[DATASTORE]: Missing required column:" + col.ColumnName); + return false; + } + } + foreach (DataColumn col in createInventoryFoldersTable().Columns) + { + if (! tmpDS.Tables["inventoryfolders"].Columns.Contains(col.ColumnName)) + { + m_log.Info("[DATASTORE]: Missing required column:" + col.ColumnName); + return false; + } + } + return true; + } + } +} diff --git a/OpenSim/Data/SQLite/SQLiteManager.cs b/OpenSim/Data/SQLite/SQLiteManager.cs new file mode 100644 index 0000000..b383b0d --- /dev/null +++ b/OpenSim/Data/SQLite/SQLiteManager.cs @@ -0,0 +1,282 @@ +/* + * Copyright (c) Contributors, http://opensimulator.org/ + * See CONTRIBUTORS.TXT for a full list of copyright holders. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions are met: + * * Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * * Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * * Neither the name of the OpenSim Project nor the + * names of its contributors may be used to endorse or promote products + * derived from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY + * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED + * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY + * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES + * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; + * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT + * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS + * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + */ + +using System; +using System.Collections.Generic; +using System.Data; +using System.Data.SQLite; +using libsecondlife; +using Mono.Data.SqliteClient; +using OpenSim.Framework.Console; + +namespace OpenSim.Framework.Data.SQLite +{ + internal class SQLiteManager : SQLiteUtil + { + private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); + + private IDbConnection dbcon; + + /// + /// Initialises and creates a new SQLite connection and maintains it. + /// + /// The SQLite server being connected to + /// The name of the SQLite database being used + /// The username logging into the database + /// The password for the user logging in + /// Whether to use connection pooling or not, can be one of the following: 'yes', 'true', 'no' or 'false', if unsure use 'false'. + public SQLiteManager(string hostname, string database, string username, string password, string cpooling) + { + try + { + string connectionString = "URI=file:GridServerSqlite.db;"; + dbcon = new SQLiteConnection(connectionString); + + dbcon.Open(); + } + catch (Exception e) + { + throw new Exception("Error initialising SQLite Database: " + e.ToString()); + } + } + + /// + /// Shuts down the database connection + /// + public void Close() + { + dbcon.Close(); + dbcon = null; + } + + /// + /// Runs a query with protection against SQL Injection by using parameterised input. + /// + /// The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y + /// The parameters - index so that @y is indexed as 'y' + /// A SQLite DB Command + public IDbCommand Query(string sql, Dictionary parameters) + { + SQLiteCommand dbcommand = (SQLiteCommand) dbcon.CreateCommand(); + dbcommand.CommandText = sql; + foreach (KeyValuePair param in parameters) + { + SQLiteParameter paramx = new SQLiteParameter(param.Key, param.Value); + dbcommand.Parameters.Add(paramx); + } + + return (IDbCommand) dbcommand; + } + +// TODO: unused +// private bool TestTables(SQLiteConnection conn) +// { +// SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM regions", conn); +// SQLiteDataAdapter pDa = new SQLiteDataAdapter(cmd); +// DataSet tmpDS = new DataSet(); +// try +// { +// pDa.Fill(tmpDS, "regions"); +// } +// catch (SqliteSyntaxException) +// { +// m_log.Info("[DATASTORE]: SQLite Database doesn't exist... creating"); +// InitDB(conn); +// } +// return true; +// } + +// TODO: unused +// private DataTable createRegionsTable() +// { +// DataTable regions = new DataTable("regions"); + +// createCol(regions, "regionHandle", typeof (ulong)); +// createCol(regions, "regionName", typeof (String)); +// createCol(regions, "uuid", typeof (String)); + +// createCol(regions, "regionRecvKey", typeof (String)); +// createCol(regions, "regionSecret", typeof (String)); +// createCol(regions, "regionSendKey", typeof (String)); + +// createCol(regions, "regionDataURI", typeof (String)); +// createCol(regions, "serverIP", typeof (String)); +// createCol(regions, "serverPort", typeof (String)); +// createCol(regions, "serverURI", typeof (String)); + + +// createCol(regions, "locX", typeof (uint)); +// createCol(regions, "locY", typeof (uint)); +// createCol(regions, "locZ", typeof (uint)); + +// createCol(regions, "eastOverrideHandle", typeof (ulong)); +// createCol(regions, "westOverrideHandle", typeof (ulong)); +// createCol(regions, "southOverrideHandle", typeof (ulong)); +// createCol(regions, "northOverrideHandle", typeof (ulong)); + +// createCol(regions, "regionAssetURI", typeof (String)); +// createCol(regions, "regionAssetRecvKey", typeof (String)); +// createCol(regions, "regionAssetSendKey", typeof (String)); + +// createCol(regions, "regionUserURI", typeof (String)); +// createCol(regions, "regionUserRecvKey", typeof (String)); +// createCol(regions, "regionUserSendKey", typeof (String)); + +// // Add in contraints +// regions.PrimaryKey = new DataColumn[] {regions.Columns["UUID"]}; +// return regions; +// } + +// TODO: unused +// private void InitDB(SQLiteConnection conn) +// { +// string createUsers = defineTable(createRegionsTable()); +// SQLiteCommand pcmd = new SQLiteCommand(createUsers, conn); +// conn.Open(); +// pcmd.ExecuteNonQuery(); +// conn.Close(); +// } + + /// + /// Reads a region row from a database reader + /// + /// An active database reader + /// A region profile + public RegionProfileData getRow(IDataReader reader) + { + RegionProfileData retval = new RegionProfileData(); + + if (reader.Read()) + { + // Region Main + retval.regionHandle = (ulong) reader["regionHandle"]; + retval.regionName = (string) reader["regionName"]; + retval.UUID = new LLUUID((string) reader["uuid"]); + + // Secrets + retval.regionRecvKey = (string) reader["regionRecvKey"]; + retval.regionSecret = (string) reader["regionSecret"]; + retval.regionSendKey = (string) reader["regionSendKey"]; + + // Region Server + retval.regionDataURI = (string) reader["regionDataURI"]; + retval.regionOnline = false; // Needs to be pinged before this can be set. + retval.serverIP = (string) reader["serverIP"]; + retval.serverPort = (uint) reader["serverPort"]; + retval.serverURI = (string) reader["serverURI"]; + + // Location + retval.regionLocX = (uint) ((int) reader["locX"]); + retval.regionLocY = (uint) ((int) reader["locY"]); + retval.regionLocZ = (uint) ((int) reader["locZ"]); + + // Neighbours - 0 = No Override + retval.regionEastOverrideHandle = (ulong) reader["eastOverrideHandle"]; + retval.regionWestOverrideHandle = (ulong) reader["westOverrideHandle"]; + retval.regionSouthOverrideHandle = (ulong) reader["southOverrideHandle"]; + retval.regionNorthOverrideHandle = (ulong) reader["northOverrideHandle"]; + + // Assets + retval.regionAssetURI = (string) reader["regionAssetURI"]; + retval.regionAssetRecvKey = (string) reader["regionAssetRecvKey"]; + retval.regionAssetSendKey = (string) reader["regionAssetSendKey"]; + + // Userserver + retval.regionUserURI = (string) reader["regionUserURI"]; + retval.regionUserRecvKey = (string) reader["regionUserRecvKey"]; + retval.regionUserSendKey = (string) reader["regionUserSendKey"]; + } + else + { + throw new Exception("No rows to return"); + } + return retval; + } + + /// + /// Inserts a new region into the database + /// + /// The region to insert + /// Success? + public bool insertRow(RegionProfileData profile) + { + string sql = + "REPLACE INTO regions VALUES (regionHandle, regionName, uuid, regionRecvKey, regionSecret, regionSendKey, regionDataURI, "; + sql += + "serverIP, serverPort, serverURI, locX, locY, locZ, eastOverrideHandle, westOverrideHandle, southOverrideHandle, northOverrideHandle, regionAssetURI, regionAssetRecvKey, "; + sql += "regionAssetSendKey, regionUserURI, regionUserRecvKey, regionUserSendKey) VALUES "; + + sql += "(@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI, "; + sql += + "@serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle, @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, "; + sql += "@regionAssetSendKey, @regionUserURI, @regionUserRecvKey, @regionUserSendKey);"; + + Dictionary parameters = new Dictionary(); + + parameters["regionHandle"] = profile.regionHandle.ToString(); + parameters["regionName"] = profile.regionName; + parameters["uuid"] = profile.UUID.ToString(); + parameters["regionRecvKey"] = profile.regionRecvKey; + parameters["regionSendKey"] = profile.regionSendKey; + parameters["regionDataURI"] = profile.regionDataURI; + parameters["serverIP"] = profile.serverIP; + parameters["serverPort"] = profile.serverPort.ToString(); + parameters["serverURI"] = profile.serverURI; + parameters["locX"] = profile.regionLocX.ToString(); + parameters["locY"] = profile.regionLocY.ToString(); + parameters["locZ"] = profile.regionLocZ.ToString(); + parameters["eastOverrideHandle"] = profile.regionEastOverrideHandle.ToString(); + parameters["westOverrideHandle"] = profile.regionWestOverrideHandle.ToString(); + parameters["northOverrideHandle"] = profile.regionNorthOverrideHandle.ToString(); + parameters["southOverrideHandle"] = profile.regionSouthOverrideHandle.ToString(); + parameters["regionAssetURI"] = profile.regionAssetURI; + parameters["regionAssetRecvKey"] = profile.regionAssetRecvKey; + parameters["regionAssetSendKey"] = profile.regionAssetSendKey; + parameters["regionUserURI"] = profile.regionUserURI; + parameters["regionUserRecvKey"] = profile.regionUserRecvKey; + parameters["regionUserSendKey"] = profile.regionUserSendKey; + + bool returnval = false; + + try + { + IDbCommand result = Query(sql, parameters); + + if (result.ExecuteNonQuery() == 1) + returnval = true; + + result.Dispose(); + } + catch (Exception) + { + return false; + } + + return returnval; + } + } +} diff --git a/OpenSim/Data/SQLite/SQLiteRegionData.cs b/OpenSim/Data/SQLite/SQLiteRegionData.cs new file mode 100644 index 0000000..77161a4 --- /dev/null +++ b/OpenSim/Data/SQLite/SQLiteRegionData.cs @@ -0,0 +1,1741 @@ +/* + * Copyright (c) Contributors, http://opensimulator.org/ + * See CONTRIBUTORS.TXT for a full list of copyright holders. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions are met: + * * Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * * Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * * Neither the name of the OpenSim Project nor the + * names of its contributors may be used to endorse or promote products + * derived from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY + * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED + * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY + * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES + * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; + * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT + * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS + * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + */ + +using System; +using System.Collections.Generic; +using System.Data; +using System.IO; +using libsecondlife; +using Mono.Data.SqliteClient; +using OpenSim.Framework; +using OpenSim.Framework.Console; +using OpenSim.Region.Environment.Interfaces; +using OpenSim.Region.Environment.Scenes; + +namespace OpenSim.Framework.Data.SQLite +{ + public class SQLiteRegionData : IRegionDataStore + { + private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); + + private const string primSelect = "select * from prims"; + private const string shapeSelect = "select * from primshapes"; + private const string itemsSelect = "select * from primitems"; + private const string terrainSelect = "select * from terrain limit 1"; + private const string landSelect = "select * from land"; + private const string landAccessListSelect = "select distinct * from landaccesslist"; + + private DataSet ds; + private SqliteDataAdapter primDa; + private SqliteDataAdapter shapeDa; + private SqliteDataAdapter itemsDa; + private SqliteDataAdapter terrainDa; + private SqliteDataAdapter landDa; + private SqliteDataAdapter landAccessListDa; + + private SqliteConnection m_conn; + + private String m_connectionString; + + // Temporary attribute while this is experimental + private bool persistPrimInventories; + + /*********************************************************************** + * + * Public Interface Functions + * + **********************************************************************/ + + // see IRegionDataStore + public void Initialise(string connectionString, bool persistPrimInventories) + { + m_connectionString = connectionString; + this.persistPrimInventories = persistPrimInventories; + + ds = new DataSet(); + + m_log.Info("[DATASTORE]: Sqlite - connecting: " + connectionString); + m_conn = new SqliteConnection(m_connectionString); + m_conn.Open(); + + SqliteCommand primSelectCmd = new SqliteCommand(primSelect, m_conn); + primDa = new SqliteDataAdapter(primSelectCmd); + // SqliteCommandBuilder primCb = new SqliteCommandBuilder(primDa); + + SqliteCommand shapeSelectCmd = new SqliteCommand(shapeSelect, m_conn); + shapeDa = new SqliteDataAdapter(shapeSelectCmd); + // SqliteCommandBuilder shapeCb = new SqliteCommandBuilder(shapeDa); + + SqliteCommand itemsSelectCmd = new SqliteCommand(itemsSelect, m_conn); + itemsDa = new SqliteDataAdapter(itemsSelectCmd); + + SqliteCommand terrainSelectCmd = new SqliteCommand(terrainSelect, m_conn); + terrainDa = new SqliteDataAdapter(terrainSelectCmd); + + SqliteCommand landSelectCmd = new SqliteCommand(landSelect, m_conn); + landDa = new SqliteDataAdapter(landSelectCmd); + + SqliteCommand landAccessListSelectCmd = new SqliteCommand(landAccessListSelect, m_conn); + landAccessListDa = new SqliteDataAdapter(landAccessListSelectCmd); + + // We fill the data set, now we've got copies in memory for the information + // TODO: see if the linkage actually holds. + // primDa.FillSchema(ds, SchemaType.Source, "PrimSchema"); + TestTables(m_conn); + + lock (ds) + { + ds.Tables.Add(createPrimTable()); + setupPrimCommands(primDa, m_conn); + primDa.Fill(ds.Tables["prims"]); + + ds.Tables.Add(createShapeTable()); + setupShapeCommands(shapeDa, m_conn); + + if (persistPrimInventories) + { + ds.Tables.Add(createItemsTable()); + setupItemsCommands(itemsDa, m_conn); + itemsDa.Fill(ds.Tables["primitems"]); + } + + ds.Tables.Add(createTerrainTable()); + setupTerrainCommands(terrainDa, m_conn); + + ds.Tables.Add(createLandTable()); + setupLandCommands(landDa, m_conn); + + ds.Tables.Add(createLandAccessListTable()); + setupLandAccessCommands(landAccessListDa, m_conn); + + // WORKAROUND: This is a work around for sqlite on + // windows, which gets really unhappy with blob columns + // that have no sample data in them. At some point we + // need to actually find a proper way to handle this. + try + { + shapeDa.Fill(ds.Tables["primshapes"]); + } + catch (Exception) + { + m_log.Info("[DATASTORE]: Caught fill error on primshapes table"); + } + + try + { + terrainDa.Fill(ds.Tables["terrain"]); + } + catch (Exception) + { + m_log.Info("[DATASTORE]: Caught fill error on terrain table"); + } + + try + { + landDa.Fill(ds.Tables["land"]); + } + catch (Exception) + { + m_log.Info("[DATASTORE]: Caught fill error on land table"); + } + + try + { + landAccessListDa.Fill(ds.Tables["landaccesslist"]); + } + catch (Exception) + { + m_log.Info("[DATASTORE]: Caught fill error on landaccesslist table"); + } + return; + } + } + + public void StoreObject(SceneObjectGroup obj, LLUUID regionUUID) + { + lock (ds) + { + foreach (SceneObjectPart prim in obj.Children.Values) + { + if ((prim.ObjectFlags & (uint) LLObject.ObjectFlags.Physics) == 0) + { + m_log.Info("[DATASTORE]: Adding obj: " + obj.UUID + " to region: " + regionUUID); + addPrim(prim, Util.ToRawUuidString(obj.UUID), Util.ToRawUuidString(regionUUID)); + } + else if (prim.Stopped) + { + //m_log.Info("[DATASTORE]: " + + //"Adding stopped obj: " + obj.UUID + " to region: " + regionUUID); + //addPrim(prim, Util.ToRawUuidString(obj.UUID), Util.ToRawUuidString(regionUUID)); + } + else + { + // m_log.Info("[DATASTORE]: Ignoring Physical obj: " + obj.UUID + " in region: " + regionUUID); + } + } + } + + Commit(); + // m_log.Info("[Dump of prims]: " + ds.GetXml()); + } + + public void RemoveObject(LLUUID obj, LLUUID regionUUID) + { + m_log.InfoFormat("[DATASTORE]: Removing obj: {0} from region: {1}", obj.UUID, regionUUID); + + DataTable prims = ds.Tables["prims"]; + DataTable shapes = ds.Tables["primshapes"]; + + string selectExp = "SceneGroupID = '" + Util.ToRawUuidString(obj) + "'"; + lock (ds) + { + DataRow[] primRows = prims.Select(selectExp); + foreach (DataRow row in primRows) + { + // Remove shape rows + LLUUID uuid = new LLUUID((string) row["UUID"]); + DataRow shapeRow = shapes.Rows.Find(Util.ToRawUuidString(uuid)); + if (shapeRow != null) + { + shapeRow.Delete(); + } + + if (persistPrimInventories) + { + RemoveItems(uuid); + } + + // Remove prim row + row.Delete(); + } + } + + Commit(); + } + + /// + /// Remove all persisted items of the given prim. + /// The caller must acquire the necessrary synchronization locks and commit or rollback changes. + /// + private void RemoveItems(LLUUID uuid) + { + DataTable items = ds.Tables["primitems"]; + + String sql = String.Format("primID = '{0}'", uuid); + DataRow[] itemRows = items.Select(sql); + + foreach (DataRow itemRow in itemRows) + { + itemRow.Delete(); + } + } + + /// + /// Load persisted objects from region storage. + /// + /// + /// List of loaded groups + public List LoadObjects(LLUUID regionUUID) + { + Dictionary createdObjects = new Dictionary(); + + List retvals = new List(); + + DataTable prims = ds.Tables["prims"]; + DataTable shapes = ds.Tables["primshapes"]; + + string byRegion = "RegionUUID = '" + Util.ToRawUuidString(regionUUID) + "'"; + string orderByParent = "ParentID ASC"; + + lock (ds) + { + DataRow[] primsForRegion = prims.Select(byRegion, orderByParent); + m_log.Info("[DATASTORE]: " + + "Loaded " + primsForRegion.Length + " prims for region: " + regionUUID); + + foreach (DataRow primRow in primsForRegion) + { + try + { + SceneObjectPart prim = null; + + string uuid = (string) primRow["UUID"]; + string objID = (string) primRow["SceneGroupID"]; + if (uuid == objID) //is new SceneObjectGroup ? + { + SceneObjectGroup group = new SceneObjectGroup(); + prim = buildPrim(primRow); + DataRow shapeRow = shapes.Rows.Find(Util.ToRawUuidString(prim.UUID)); + if (shapeRow != null) + { + prim.Shape = buildShape(shapeRow); + } + else + { + m_log.Info( + "No shape found for prim in storage, so setting default box shape"); + prim.Shape = PrimitiveBaseShape.Default; + } + group.AddPart(prim); + group.RootPart = prim; + + createdObjects.Add(Util.ToRawUuidString(group.UUID), group); + retvals.Add(group); + } + else + { + prim = buildPrim(primRow); + DataRow shapeRow = shapes.Rows.Find(Util.ToRawUuidString(prim.UUID)); + if (shapeRow != null) + { + prim.Shape = buildShape(shapeRow); + } + else + { + m_log.Info( + "No shape found for prim in storage, so setting default box shape"); + prim.Shape = PrimitiveBaseShape.Default; + } + createdObjects[new LLUUID(objID)].AddPart(prim); + } + + if (persistPrimInventories) + { + LoadItems(prim); + } + } + catch (Exception e) + { + m_log.Error("[DATASTORE]: Failed create prim object, exception and data follows"); + m_log.Info("[DATASTORE]: " + e.ToString()); + foreach (DataColumn col in prims.Columns) + { + m_log.Info("[DATASTORE]: Col: " + col.ColumnName + " => " + primRow[col]); + } + } + } + } + return retvals; + } + + /// + /// Load in a prim's persisted inventory. + /// + /// + private void LoadItems(SceneObjectPart prim) + { + //m_log.DebugFormat("[DATASTORE]: Loading inventory for {0}, {1}", prim.Name, prim.UUID); + + DataTable dbItems = ds.Tables["primitems"]; + + String sql = String.Format("primID = '{0}'", prim.UUID.ToString()); + DataRow[] dbItemRows = dbItems.Select(sql); + + IList inventory = new List(); + + foreach (DataRow row in dbItemRows) + { + TaskInventoryItem item = buildItem(row); + inventory.Add(item); + + //m_log.DebugFormat("[DATASTORE]: Restored item {0}, {1}", item.Name, item.ItemID); + } + + prim.RestoreInventoryItems(inventory); + + // XXX A nasty little hack to recover the folder id for the prim (which is currently stored in + // every item). This data should really be stored in the prim table itself. + if (dbItemRows.Length > 0) + { + prim.FolderID = inventory[0].ParentID; + } + } + + public void StoreTerrain(double[,] ter, LLUUID regionID) + { + lock (ds) + { + int revision = Util.UnixTimeSinceEpoch(); + + // the following is an work around for .NET. The perf + // issues associated with it aren't as bad as you think. + m_log.Info("[DATASTORE]: Storing terrain revision r" + revision.ToString()); + String sql = "insert into terrain(RegionUUID, Revision, Heightfield)" + + " values(:RegionUUID, :Revision, :Heightfield)"; + + using (SqliteCommand cmd = new SqliteCommand(sql, m_conn)) + { + cmd.Parameters.Add(new SqliteParameter(":RegionUUID", Util.ToRawUuidString(regionID))); + cmd.Parameters.Add(new SqliteParameter(":Revision", revision)); + cmd.Parameters.Add(new SqliteParameter(":Heightfield", serializeTerrain(ter))); + cmd.ExecuteNonQuery(); + } + + // This is added to get rid of the infinitely growing + // terrain databases which negatively impact on SQLite + // over time. Before reenabling this feature there + // needs to be a limitter put on the number of + // revisions in the database, as this old + // implementation is a DOS attack waiting to happen. + + using ( + SqliteCommand cmd = + new SqliteCommand("delete from terrain where RegionUUID=:RegionUUID and Revision < :Revision", + m_conn)) + { + cmd.Parameters.Add(new SqliteParameter(":RegionUUID", Util.ToRawUuidString(regionID))); + cmd.Parameters.Add(new SqliteParameter(":Revision", revision)); + cmd.ExecuteNonQuery(); + } + } + } + + public double[,] LoadTerrain(LLUUID regionID) + { + lock (ds) + { + double[,] terret = new double[256,256]; + terret.Initialize(); + + String sql = "select RegionUUID, Revision, Heightfield from terrain" + + " where RegionUUID=:RegionUUID order by Revision desc"; + + using (SqliteCommand cmd = new SqliteCommand(sql, m_conn)) + { + cmd.Parameters.Add(new SqliteParameter(":RegionUUID", Util.ToRawUuidString(regionID))); + + using (IDataReader row = cmd.ExecuteReader()) + { + int rev = 0; + if (row.Read()) + { + // TODO: put this into a function + MemoryStream str = new MemoryStream((byte[]) row["Heightfield"]); + BinaryReader br = new BinaryReader(str); + for (int x = 0; x < 256; x++) + { + for (int y = 0; y < 256; y++) + { + terret[x, y] = br.ReadDouble(); + } + } + rev = (int) row["Revision"]; + } + else + { + m_log.Info("[DATASTORE]: No terrain found for region"); + return null; + } + + m_log.Info("[DATASTORE]: Loaded terrain revision r" + rev.ToString()); + } + } + return terret; + } + } + + public void RemoveLandObject(LLUUID globalID) + { + lock (ds) + { + using (SqliteCommand cmd = new SqliteCommand("delete from land where UUID=:UUID", m_conn)) + { + cmd.Parameters.Add(new SqliteParameter(":UUID", Util.ToRawUuidString(globalID))); + cmd.ExecuteNonQuery(); + } + + using (SqliteCommand cmd = new SqliteCommand("delete from landaccesslist where LandUUID=:UUID", m_conn)) + { + cmd.Parameters.Add(new SqliteParameter(":UUID", Util.ToRawUuidString(globalID))); + cmd.ExecuteNonQuery(); + } + } + } + + public void StoreLandObject(ILandObject parcel) + { + lock (ds) + { + DataTable land = ds.Tables["land"]; + DataTable landaccesslist = ds.Tables["landaccesslist"]; + + DataRow landRow = land.Rows.Find(Util.ToRawUuidString(parcel.landData.globalID)); + if (landRow == null) + { + landRow = land.NewRow(); + fillLandRow(landRow, parcel.landData, parcel.regionUUID); + land.Rows.Add(landRow); + } + else + { + fillLandRow(landRow, parcel.landData, parcel.regionUUID); + } + + // I know this caused someone issues before, but OpenSim is unusable if we leave this stuff around + using (SqliteCommand cmd = new SqliteCommand("delete from landaccesslist where LandUUID=:LandUUID", m_conn)) + { + cmd.Parameters.Add(new SqliteParameter(":LandUUID", Util.ToRawUuidString(parcel.landData.globalID))); + cmd.ExecuteNonQuery(); + } + + foreach (ParcelManager.ParcelAccessEntry entry in parcel.landData.parcelAccessList) + { + DataRow newAccessRow = landaccesslist.NewRow(); + fillLandAccessRow(newAccessRow, entry, parcel.landData.globalID); + landaccesslist.Rows.Add(newAccessRow); + } + } + + Commit(); + } + + public List LoadLandObjects(LLUUID regionUUID) + { + List landDataForRegion = new List(); + lock (ds) + { + DataTable land = ds.Tables["land"]; + DataTable landaccesslist = ds.Tables["landaccesslist"]; + string searchExp = "RegionUUID = '" + Util.ToRawUuidString(regionUUID) + "'"; + DataRow[] rawDataForRegion = land.Select(searchExp); + foreach (DataRow rawDataLand in rawDataForRegion) + { + LandData newLand = buildLandData(rawDataLand); + string accessListSearchExp = "LandUUID = '" + Util.ToRawUuidString(newLand.globalID) + "'"; + DataRow[] rawDataForLandAccessList = landaccesslist.Select(accessListSearchExp); + foreach (DataRow rawDataLandAccess in rawDataForLandAccessList) + { + newLand.parcelAccessList.Add(buildLandAccessData(rawDataLandAccess)); + } + + landDataForRegion.Add(newLand); + } + } + return landDataForRegion; + } + + public void Commit() + { + lock (ds) + { + primDa.Update(ds, "prims"); + shapeDa.Update(ds, "primshapes"); + + if (persistPrimInventories) + { + itemsDa.Update(ds, "primitems"); + } + + terrainDa.Update(ds, "terrain"); + landDa.Update(ds, "land"); + landAccessListDa.Update(ds, "landaccesslist"); + ds.AcceptChanges(); + } + } + + public void Shutdown() + { + Commit(); + } + + /*********************************************************************** + * + * Database Definition Functions + * + * This should be db agnostic as we define them in ADO.NET terms + * + **********************************************************************/ + + private void createCol(DataTable dt, string name, Type type) + { + DataColumn col = new DataColumn(name, type); + dt.Columns.Add(col); + } + + private DataTable createTerrainTable() + { + DataTable terrain = new DataTable("terrain"); + + createCol(terrain, "RegionUUID", typeof (String)); + createCol(terrain, "Revision", typeof (Int32)); + createCol(terrain, "Heightfield", typeof (Byte[])); + + return terrain; + } + + private DataTable createPrimTable() + { + DataTable prims = new DataTable("prims"); + + createCol(prims, "UUID", typeof (String)); + createCol(prims, "RegionUUID", typeof (String)); + createCol(prims, "ParentID", typeof (Int32)); + createCol(prims, "CreationDate", typeof (Int32)); + createCol(prims, "Name", typeof (String)); + createCol(prims, "SceneGroupID", typeof (String)); + // various text fields + createCol(prims, "Text", typeof (String)); + createCol(prims, "Description", typeof (String)); + createCol(prims, "SitName", typeof (String)); + createCol(prims, "TouchName", typeof (String)); + // permissions + createCol(prims, "ObjectFlags", typeof (Int32)); + createCol(prims, "CreatorID", typeof (String)); + createCol(prims, "OwnerID", typeof (String)); + createCol(prims, "GroupID", typeof (String)); + createCol(prims, "LastOwnerID", typeof (String)); + createCol(prims, "OwnerMask", typeof (Int32)); + createCol(prims, "NextOwnerMask", typeof (Int32)); + createCol(prims, "GroupMask", typeof (Int32)); + createCol(prims, "EveryoneMask", typeof (Int32)); + createCol(prims, "BaseMask", typeof (Int32)); + // vectors + createCol(prims, "PositionX", typeof (Double)); + createCol(prims, "PositionY", typeof (Double)); + createCol(prims, "PositionZ", typeof (Double)); + createCol(prims, "GroupPositionX", typeof (Double)); + createCol(prims, "GroupPositionY", typeof (Double)); + createCol(prims, "GroupPositionZ", typeof (Double)); + createCol(prims, "VelocityX", typeof (Double)); + createCol(prims, "VelocityY", typeof (Double)); + createCol(prims, "VelocityZ", typeof (Double)); + createCol(prims, "AngularVelocityX", typeof (Double)); + createCol(prims, "AngularVelocityY", typeof (Double)); + createCol(prims, "AngularVelocityZ", typeof (Double)); + createCol(prims, "AccelerationX", typeof (Double)); + createCol(prims, "AccelerationY", typeof (Double)); + createCol(prims, "AccelerationZ", typeof (Double)); + // quaternions + createCol(prims, "RotationX", typeof (Double)); + createCol(prims, "RotationY", typeof (Double)); + createCol(prims, "RotationZ", typeof (Double)); + createCol(prims, "RotationW", typeof (Double)); + + // sit target + createCol(prims, "SitTargetOffsetX", typeof (Double)); + createCol(prims, "SitTargetOffsetY", typeof (Double)); + createCol(prims, "SitTargetOffsetZ", typeof (Double)); + + createCol(prims, "SitTargetOrientW", typeof (Double)); + createCol(prims, "SitTargetOrientX", typeof (Double)); + createCol(prims, "SitTargetOrientY", typeof (Double)); + createCol(prims, "SitTargetOrientZ", typeof (Double)); + + // Add in contraints + prims.PrimaryKey = new DataColumn[] {prims.Columns["UUID"]}; + + return prims; + } + + private DataTable createShapeTable() + { + DataTable shapes = new DataTable("primshapes"); + createCol(shapes, "UUID", typeof (String)); + // shape is an enum + createCol(shapes, "Shape", typeof (Int32)); + // vectors + createCol(shapes, "ScaleX", typeof (Double)); + createCol(shapes, "ScaleY", typeof (Double)); + createCol(shapes, "ScaleZ", typeof (Double)); + // paths + createCol(shapes, "PCode", typeof (Int32)); + createCol(shapes, "PathBegin", typeof (Int32)); + createCol(shapes, "PathEnd", typeof (Int32)); + createCol(shapes, "PathScaleX", typeof (Int32)); + createCol(shapes, "PathScaleY", typeof (Int32)); + createCol(shapes, "PathShearX", typeof (Int32)); + createCol(shapes, "PathShearY", typeof (Int32)); + createCol(shapes, "PathSkew", typeof (Int32)); + createCol(shapes, "PathCurve", typeof (Int32)); + createCol(shapes, "PathRadiusOffset", typeof (Int32)); + createCol(shapes, "PathRevolutions", typeof (Int32)); + createCol(shapes, "PathTaperX", typeof (Int32)); + createCol(shapes, "PathTaperY", typeof (Int32)); + createCol(shapes, "PathTwist", typeof (Int32)); + createCol(shapes, "PathTwistBegin", typeof (Int32)); + // profile + createCol(shapes, "ProfileBegin", typeof (Int32)); + createCol(shapes, "ProfileEnd", typeof (Int32)); + createCol(shapes, "ProfileCurve", typeof (Int32)); + createCol(shapes, "ProfileHollow", typeof (Int32)); + createCol(shapes, "State", typeof(Int32)); + // text TODO: this isn't right, but I'm not sure the right + // way to specify this as a blob atm + createCol(shapes, "Texture", typeof (Byte[])); + createCol(shapes, "ExtraParams", typeof (Byte[])); + + shapes.PrimaryKey = new DataColumn[] {shapes.Columns["UUID"]}; + + return shapes; + } + + private DataTable createItemsTable() + { + DataTable items = new DataTable("primitems"); + + createCol(items, "itemID", typeof (String)); + createCol(items, "primID", typeof (String)); + createCol(items, "assetID", typeof (String)); + createCol(items, "parentFolderID", typeof (String)); + + createCol(items, "invType", typeof (Int32)); + createCol(items, "assetType", typeof (Int32)); + + createCol(items, "name", typeof (String)); + createCol(items, "description", typeof (String)); + + createCol(items, "creationDate", typeof (Int64)); + createCol(items, "creatorID", typeof (String)); + createCol(items, "ownerID", typeof (String)); + createCol(items, "lastOwnerID", typeof (String)); + createCol(items, "groupID", typeof (String)); + + createCol(items, "nextPermissions", typeof (UInt32)); + createCol(items, "currentPermissions", typeof (UInt32)); + createCol(items, "basePermissions", typeof (UInt32)); + createCol(items, "everyonePermissions", typeof (UInt32)); + createCol(items, "groupPermissions", typeof (UInt32)); + + items.PrimaryKey = new DataColumn[] {items.Columns["itemID"]}; + + return items; + } + + private DataTable createLandTable() + { + DataTable land = new DataTable("land"); + createCol(land, "UUID", typeof (String)); + createCol(land, "RegionUUID", typeof (String)); + createCol(land, "LocalLandID", typeof (UInt32)); + + // Bitmap is a byte[512] + createCol(land, "Bitmap", typeof (Byte[])); + + createCol(land, "Name", typeof (String)); + createCol(land, "Desc", typeof (String)); + createCol(land, "OwnerUUID", typeof (String)); + createCol(land, "IsGroupOwned", typeof (Boolean)); + createCol(land, "Area", typeof (Int32)); + createCol(land, "AuctionID", typeof (Int32)); //Unemplemented + createCol(land, "Category", typeof (Int32)); //Enum libsecondlife.Parcel.ParcelCategory + createCol(land, "ClaimDate", typeof (Int32)); + createCol(land, "ClaimPrice", typeof (Int32)); + createCol(land, "GroupUUID", typeof (string)); + createCol(land, "SalePrice", typeof (Int32)); + createCol(land, "LandStatus", typeof (Int32)); //Enum. libsecondlife.Parcel.ParcelStatus + createCol(land, "LandFlags", typeof (UInt32)); + createCol(land, "LandingType", typeof (Byte)); + createCol(land, "MediaAutoScale", typeof (Byte)); + createCol(land, "MediaTextureUUID", typeof (String)); + createCol(land, "MediaURL", typeof (String)); + createCol(land, "MusicURL", typeof (String)); + createCol(land, "PassHours", typeof (Double)); + createCol(land, "PassPrice", typeof (UInt32)); + createCol(land, "SnapshotUUID", typeof (String)); + createCol(land, "UserLocationX", typeof (Double)); + createCol(land, "UserLocationY", typeof (Double)); + createCol(land, "UserLocationZ", typeof (Double)); + createCol(land, "UserLookAtX", typeof (Double)); + createCol(land, "UserLookAtY", typeof (Double)); + createCol(land, "UserLookAtZ", typeof (Double)); + + land.PrimaryKey = new DataColumn[] {land.Columns["UUID"]}; + + return land; + } + + private DataTable createLandAccessListTable() + { + DataTable landaccess = new DataTable("landaccesslist"); + createCol(landaccess, "LandUUID", typeof (String)); + createCol(landaccess, "AccessUUID", typeof (String)); + createCol(landaccess, "Flags", typeof (UInt32)); + + return landaccess; + } + + /*********************************************************************** + * + * Convert between ADO.NET <=> OpenSim Objects + * + * These should be database independant + * + **********************************************************************/ + + private SceneObjectPart buildPrim(DataRow row) + { + // TODO: this doesn't work yet because something more + // interesting has to be done to actually get these values + // back out. Not enough time to figure it out yet. + SceneObjectPart prim = new SceneObjectPart(); + prim.UUID = new LLUUID((String) row["UUID"]); + // explicit conversion of integers is required, which sort + // of sucks. No idea if there is a shortcut here or not. + prim.ParentID = Convert.ToUInt32(row["ParentID"]); + prim.CreationDate = Convert.ToInt32(row["CreationDate"]); + prim.Name = (String) row["Name"]; + // various text fields + prim.Text = (String) row["Text"]; + prim.Description = (String) row["Description"]; + prim.SitName = (String) row["SitName"]; + prim.TouchName = (String) row["TouchName"]; + // permissions + prim.ObjectFlags = Convert.ToUInt32(row["ObjectFlags"]); + prim.CreatorID = new LLUUID((String) row["CreatorID"]); + prim.OwnerID = new LLUUID((String) row["OwnerID"]); + prim.GroupID = new LLUUID((String) row["GroupID"]); + prim.LastOwnerID = new LLUUID((String) row["LastOwnerID"]); + prim.OwnerMask = Convert.ToUInt32(row["OwnerMask"]); + prim.NextOwnerMask = Convert.ToUInt32(row["NextOwnerMask"]); + prim.GroupMask = Convert.ToUInt32(row["GroupMask"]); + prim.EveryoneMask = Convert.ToUInt32(row["EveryoneMask"]); + prim.BaseMask = Convert.ToUInt32(row["BaseMask"]); + // vectors + prim.OffsetPosition = new LLVector3( + Convert.ToSingle(row["PositionX"]), + Convert.ToSingle(row["PositionY"]), + Convert.ToSingle(row["PositionZ"]) + ); + prim.GroupPosition = new LLVector3( + Convert.ToSingle(row["GroupPositionX"]), + Convert.ToSingle(row["GroupPositionY"]), + Convert.ToSingle(row["GroupPositionZ"]) + ); + prim.Velocity = new LLVector3( + Convert.ToSingle(row["VelocityX"]), + Convert.ToSingle(row["VelocityY"]), + Convert.ToSingle(row["VelocityZ"]) + ); + prim.AngularVelocity = new LLVector3( + Convert.ToSingle(row["AngularVelocityX"]), + Convert.ToSingle(row["AngularVelocityY"]), + Convert.ToSingle(row["AngularVelocityZ"]) + ); + prim.Acceleration = new LLVector3( + Convert.ToSingle(row["AccelerationX"]), + Convert.ToSingle(row["AccelerationY"]), + Convert.ToSingle(row["AccelerationZ"]) + ); + // quaternions + prim.RotationOffset = new LLQuaternion( + Convert.ToSingle(row["RotationX"]), + Convert.ToSingle(row["RotationY"]), + Convert.ToSingle(row["RotationZ"]), + Convert.ToSingle(row["RotationW"]) + ); + + try + { + prim.SetSitTargetLL(new LLVector3( + Convert.ToSingle(row["SitTargetOffsetX"]), + Convert.ToSingle(row["SitTargetOffsetY"]), + Convert.ToSingle(row["SitTargetOffsetZ"])), new LLQuaternion( + Convert.ToSingle( + row["SitTargetOrientX"]), + Convert.ToSingle( + row["SitTargetOrientY"]), + Convert.ToSingle( + row["SitTargetOrientZ"]), + Convert.ToSingle( + row["SitTargetOrientW"]))); + } + catch (InvalidCastException) + { + // Database table was created before we got here and now has null values :P + m_conn.Open(); + SqliteCommand cmd = + new SqliteCommand("ALTER TABLE prims ADD COLUMN SitTargetOffsetX float NOT NULL default 0;", m_conn); + cmd.ExecuteNonQuery(); + cmd = + new SqliteCommand("ALTER TABLE prims ADD COLUMN SitTargetOffsetY float NOT NULL default 0;", m_conn); + cmd.ExecuteNonQuery(); + cmd = + new SqliteCommand("ALTER TABLE prims ADD COLUMN SitTargetOffsetZ float NOT NULL default 0;", m_conn); + cmd.ExecuteNonQuery(); + cmd = + new SqliteCommand("ALTER TABLE prims ADD COLUMN SitTargetOrientW float NOT NULL default 0;", m_conn); + cmd.ExecuteNonQuery(); + cmd = + new SqliteCommand("ALTER TABLE prims ADD COLUMN SitTargetOrientX float NOT NULL default 0;", m_conn); + cmd.ExecuteNonQuery(); + cmd = + new SqliteCommand("ALTER TABLE prims ADD COLUMN SitTargetOrientY float NOT NULL default 0;", m_conn); + cmd.ExecuteNonQuery(); + cmd = + new SqliteCommand("ALTER TABLE prims ADD COLUMN SitTargetOrientZ float NOT NULL default 0;", m_conn); + cmd.ExecuteNonQuery(); + } + + return prim; + } + + /// + /// Build a prim inventory item from the persisted data. + /// + /// + /// + private TaskInventoryItem buildItem(DataRow row) + { + TaskInventoryItem taskItem = new TaskInventoryItem(); + + taskItem.ItemID = new LLUUID((String)row["itemID"]); + taskItem.ParentPartID = new LLUUID((String)row["primID"]); + taskItem.AssetID = new LLUUID((String)row["assetID"]); + taskItem.ParentID = new LLUUID((String)row["parentFolderID"]); + + taskItem.InvType = Convert.ToInt32(row["invType"]); + taskItem.Type = Convert.ToInt32(row["assetType"]); + + taskItem.Name = (String)row["name"]; + taskItem.Description = (String)row["description"]; + taskItem.CreationDate = Convert.ToUInt32(row["creationDate"]); + taskItem.CreatorID = new LLUUID((String)row["creatorID"]); + taskItem.OwnerID = new LLUUID((String)row["ownerID"]); + taskItem.LastOwnerID = new LLUUID((String)row["lastOwnerID"]); + taskItem.GroupID = new LLUUID((String)row["groupID"]); + + taskItem.NextOwnerMask = Convert.ToUInt32(row["nextPermissions"]); + taskItem.OwnerMask = Convert.ToUInt32(row["currentPermissions"]); + taskItem.BaseMask = Convert.ToUInt32(row["basePermissions"]); + taskItem.EveryoneMask = Convert.ToUInt32(row["everyonePermissions"]); + taskItem.GroupMask = Convert.ToUInt32(row["groupPermissions"]); + + return taskItem; + } + + private LandData buildLandData(DataRow row) + { + LandData newData = new LandData(); + + newData.globalID = new LLUUID((String) row["UUID"]); + newData.localID = Convert.ToInt32(row["LocalLandID"]); + + // Bitmap is a byte[512] + newData.landBitmapByteArray = (Byte[]) row["Bitmap"]; + + newData.landName = (String) row["Name"]; + newData.landDesc = (String) row["Desc"]; + newData.ownerID = (String) row["OwnerUUID"]; + newData.isGroupOwned = (Boolean) row["IsGroupOwned"]; + newData.area = Convert.ToInt32(row["Area"]); + newData.auctionID = Convert.ToUInt32(row["AuctionID"]); //Unemplemented + newData.category = (Parcel.ParcelCategory) Convert.ToInt32(row["Category"]); + //Enum libsecondlife.Parcel.ParcelCategory + newData.claimDate = Convert.ToInt32(row["ClaimDate"]); + newData.claimPrice = Convert.ToInt32(row["ClaimPrice"]); + newData.groupID = new LLUUID((String) row["GroupUUID"]); + newData.salePrice = Convert.ToInt32(row["SalePrice"]); + newData.landStatus = (Parcel.ParcelStatus) Convert.ToInt32(row["LandStatus"]); + //Enum. libsecondlife.Parcel.ParcelStatus + newData.landFlags = Convert.ToUInt32(row["LandFlags"]); + newData.landingType = (Byte) row["LandingType"]; + newData.mediaAutoScale = (Byte) row["MediaAutoScale"]; + newData.mediaID = new LLUUID((String) row["MediaTextureUUID"]); + newData.mediaURL = (String) row["MediaURL"]; + newData.musicURL = (String) row["MusicURL"]; + newData.passHours = Convert.ToSingle(row["PassHours"]); + newData.passPrice = Convert.ToInt32(row["PassPrice"]); + newData.snapshotID = (String) row["SnapshotUUID"]; + + newData.userLocation = + new LLVector3(Convert.ToSingle(row["UserLocationX"]), Convert.ToSingle(row["UserLocationY"]), + Convert.ToSingle(row["UserLocationZ"])); + newData.userLookAt = + new LLVector3(Convert.ToSingle(row["UserLookAtX"]), Convert.ToSingle(row["UserLookAtY"]), + Convert.ToSingle(row["UserLookAtZ"])); + newData.parcelAccessList = new List(); + + return newData; + } + + private ParcelManager.ParcelAccessEntry buildLandAccessData(DataRow row) + { + ParcelManager.ParcelAccessEntry entry = new ParcelManager.ParcelAccessEntry(); + entry.AgentID = new LLUUID((string) row["AccessUUID"]); + entry.Flags = (ParcelManager.AccessList) row["Flags"]; + entry.Time = new DateTime(); + return entry; + } + + private Array serializeTerrain(double[,] val) + { + MemoryStream str = new MemoryStream(65536*sizeof (double)); + BinaryWriter bw = new BinaryWriter(str); + + // TODO: COMPATIBILITY - Add byte-order conversions + for (int x = 0; x < 256; x++) + for (int y = 0; y < 256; y++) + bw.Write(val[x, y]); + + return str.ToArray(); + } + +// private void fillTerrainRow(DataRow row, LLUUID regionUUID, int rev, double[,] val) +// { +// row["RegionUUID"] = regionUUID; +// row["Revision"] = rev; + +// MemoryStream str = new MemoryStream(65536*sizeof (double)); +// BinaryWriter bw = new BinaryWriter(str); + +// // TODO: COMPATIBILITY - Add byte-order conversions +// for (int x = 0; x < 256; x++) +// for (int y = 0; y < 256; y++) +// bw.Write(val[x, y]); + +// row["Heightfield"] = str.ToArray(); +// } + + private void fillPrimRow(DataRow row, SceneObjectPart prim, LLUUID sceneGroupID, LLUUID regionUUID) + { + row["UUID"] = Util.ToRawUuidString(prim.UUID); + row["RegionUUID"] = Util.ToRawUuidString(regionUUID); + row["ParentID"] = prim.ParentID; + row["CreationDate"] = prim.CreationDate; + row["Name"] = prim.Name; + row["SceneGroupID"] = Util.ToRawUuidString(sceneGroupID); + // the UUID of the root part for this SceneObjectGroup + // various text fields + row["Text"] = prim.Text; + row["Description"] = prim.Description; + row["SitName"] = prim.SitName; + row["TouchName"] = prim.TouchName; + // permissions + row["ObjectFlags"] = prim.ObjectFlags; + row["CreatorID"] = Util.ToRawUuidString(prim.CreatorID); + row["OwnerID"] = Util.ToRawUuidString(prim.OwnerID); + row["GroupID"] = Util.ToRawUuidString(prim.GroupID); + row["LastOwnerID"] = Util.ToRawUuidString(prim.LastOwnerID); + row["OwnerMask"] = prim.OwnerMask; + row["NextOwnerMask"] = prim.NextOwnerMask; + row["GroupMask"] = prim.GroupMask; + row["EveryoneMask"] = prim.EveryoneMask; + row["BaseMask"] = prim.BaseMask; + // vectors + row["PositionX"] = prim.OffsetPosition.X; + row["PositionY"] = prim.OffsetPosition.Y; + row["PositionZ"] = prim.OffsetPosition.Z; + row["GroupPositionX"] = prim.GroupPosition.X; + row["GroupPositionY"] = prim.GroupPosition.Y; + row["GroupPositionZ"] = prim.GroupPosition.Z; + row["VelocityX"] = prim.Velocity.X; + row["VelocityY"] = prim.Velocity.Y; + row["VelocityZ"] = prim.Velocity.Z; + row["AngularVelocityX"] = prim.AngularVelocity.X; + row["AngularVelocityY"] = prim.AngularVelocity.Y; + row["AngularVelocityZ"] = prim.AngularVelocity.Z; + row["AccelerationX"] = prim.Acceleration.X; + row["AccelerationY"] = prim.Acceleration.Y; + row["AccelerationZ"] = prim.Acceleration.Z; + // quaternions + row["RotationX"] = prim.RotationOffset.X; + row["RotationY"] = prim.RotationOffset.Y; + row["RotationZ"] = prim.RotationOffset.Z; + row["RotationW"] = prim.RotationOffset.W; + + // Sit target + LLVector3 sitTargetPos = prim.GetSitTargetPositionLL(); + row["SitTargetOffsetX"] = sitTargetPos.X; + row["SitTargetOffsetY"] = sitTargetPos.Y; + row["SitTargetOffsetZ"] = sitTargetPos.Z; + + LLQuaternion sitTargetOrient = prim.GetSitTargetOrientationLL(); + row["SitTargetOrientW"] = sitTargetOrient.W; + row["SitTargetOrientX"] = sitTargetOrient.X; + row["SitTargetOrientY"] = sitTargetOrient.Y; + row["SitTargetOrientZ"] = sitTargetOrient.Z; + } + + private void fillItemRow(DataRow row, TaskInventoryItem taskItem) + { + row["itemID"] = taskItem.ItemID; + row["primID"] = taskItem.ParentPartID; + row["assetID"] = taskItem.AssetID; + row["parentFolderID"] = taskItem.ParentID; + + row["invType"] = taskItem.InvType; + row["assetType"] = taskItem.Type; + + row["name"] = taskItem.Name; + row["description"] = taskItem.Description; + row["creationDate"] = taskItem.CreationDate; + row["creatorID"] = taskItem.CreatorID; + row["ownerID"] = taskItem.OwnerID; + row["lastOwnerID"] = taskItem.LastOwnerID; + row["groupID"] = taskItem.GroupID; + row["nextPermissions"] = taskItem.NextOwnerMask; + row["currentPermissions"] = taskItem.OwnerMask; + row["basePermissions"] = taskItem.BaseMask; + row["everyonePermissions"] = taskItem.EveryoneMask; + row["groupPermissions"] = taskItem.GroupMask; + } + + private void fillLandRow(DataRow row, LandData land, LLUUID regionUUID) + { + row["UUID"] = Util.ToRawUuidString(land.globalID); + row["RegionUUID"] = Util.ToRawUuidString(regionUUID); + row["LocalLandID"] = land.localID; + + // Bitmap is a byte[512] + row["Bitmap"] = land.landBitmapByteArray; + + row["Name"] = land.landName; + row["Desc"] = land.landDesc; + row["OwnerUUID"] = Util.ToRawUuidString(land.ownerID); + row["IsGroupOwned"] = land.isGroupOwned; + row["Area"] = land.area; + row["AuctionID"] = land.auctionID; //Unemplemented + row["Category"] = land.category; //Enum libsecondlife.Parcel.ParcelCategory + row["ClaimDate"] = land.claimDate; + row["ClaimPrice"] = land.claimPrice; + row["GroupUUID"] = Util.ToRawUuidString(land.groupID); + row["SalePrice"] = land.salePrice; + row["LandStatus"] = land.landStatus; //Enum. libsecondlife.Parcel.ParcelStatus + row["LandFlags"] = land.landFlags; + row["LandingType"] = land.landingType; + row["MediaAutoScale"] = land.mediaAutoScale; + row["MediaTextureUUID"] = Util.ToRawUuidString(land.mediaID); + row["MediaURL"] = land.mediaURL; + row["MusicURL"] = land.musicURL; + row["PassHours"] = land.passHours; + row["PassPrice"] = land.passPrice; + row["SnapshotUUID"] = Util.ToRawUuidString(land.snapshotID); + row["UserLocationX"] = land.userLocation.X; + row["UserLocationY"] = land.userLocation.Y; + row["UserLocationZ"] = land.userLocation.Z; + row["UserLookAtX"] = land.userLookAt.X; + row["UserLookAtY"] = land.userLookAt.Y; + row["UserLookAtZ"] = land.userLookAt.Z; + } + + private void fillLandAccessRow(DataRow row, ParcelManager.ParcelAccessEntry entry, LLUUID parcelID) + { + row["LandUUID"] = Util.ToRawUuidString(parcelID); + row["AccessUUID"] = Util.ToRawUuidString(entry.AgentID); + row["Flags"] = entry.Flags; + } + + private PrimitiveBaseShape buildShape(DataRow row) + { + PrimitiveBaseShape s = new PrimitiveBaseShape(); + s.Scale = new LLVector3( + Convert.ToSingle(row["ScaleX"]), + Convert.ToSingle(row["ScaleY"]), + Convert.ToSingle(row["ScaleZ"]) + ); + // paths + s.PCode = Convert.ToByte(row["PCode"]); + s.PathBegin = Convert.ToUInt16(row["PathBegin"]); + s.PathEnd = Convert.ToUInt16(row["PathEnd"]); + s.PathScaleX = Convert.ToByte(row["PathScaleX"]); + s.PathScaleY = Convert.ToByte(row["PathScaleY"]); + s.PathShearX = Convert.ToByte(row["PathShearX"]); + s.PathShearY = Convert.ToByte(row["PathShearY"]); + s.PathSkew = Convert.ToSByte(row["PathSkew"]); + s.PathCurve = Convert.ToByte(row["PathCurve"]); + s.PathRadiusOffset = Convert.ToSByte(row["PathRadiusOffset"]); + s.PathRevolutions = Convert.ToByte(row["PathRevolutions"]); + s.PathTaperX = Convert.ToSByte(row["PathTaperX"]); + s.PathTaperY = Convert.ToSByte(row["PathTaperY"]); + s.PathTwist = Convert.ToSByte(row["PathTwist"]); + s.PathTwistBegin = Convert.ToSByte(row["PathTwistBegin"]); + // profile + s.ProfileBegin = Convert.ToUInt16(row["ProfileBegin"]); + s.ProfileEnd = Convert.ToUInt16(row["ProfileEnd"]); + s.ProfileCurve = Convert.ToByte(row["ProfileCurve"]); + s.ProfileHollow = Convert.ToUInt16(row["ProfileHollow"]); + try + { + s.State = Convert.ToByte(row["State"]); + } + catch (InvalidCastException) + { + m_conn.Open(); + SqliteCommand cmd = + new SqliteCommand("ALTER TABLE primshapes ADD COLUMN State Integer NOT NULL default 0;", m_conn); + cmd.ExecuteNonQuery(); + } + // text TODO: this isn't right] = but I'm not sure the right + // way to specify this as a blob atm + + byte[] textureEntry = (byte[]) row["Texture"]; + s.TextureEntry = textureEntry; + + s.ExtraParams = (byte[]) row["ExtraParams"]; + // System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding(); + // string texture = encoding.GetString((Byte[])row["Texture"]); + // if (!texture.StartsWith("<")) + // { + // //here so that we can still work with old format database files (ie from before I added xml serialization) + // LLObject.TextureEntry textureEntry = null; + // textureEntry = new LLObject.TextureEntry(new LLUUID(texture)); + // s.TextureEntry = textureEntry.ToBytes(); + // } + // else + // { + // TextureBlock textureEntry = TextureBlock.FromXmlString(texture); + // s.TextureEntry = textureEntry.TextureData; + // s.ExtraParams = textureEntry.ExtraParams; + // } + + return s; + } + + private void fillShapeRow(DataRow row, SceneObjectPart prim) + { + PrimitiveBaseShape s = prim.Shape; + row["UUID"] = Util.ToRawUuidString(prim.UUID); + // shape is an enum + row["Shape"] = 0; + // vectors + row["ScaleX"] = s.Scale.X; + row["ScaleY"] = s.Scale.Y; + row["ScaleZ"] = s.Scale.Z; + // paths + row["PCode"] = s.PCode; + row["PathBegin"] = s.PathBegin; + row["PathEnd"] = s.PathEnd; + row["PathScaleX"] = s.PathScaleX; + row["PathScaleY"] = s.PathScaleY; + row["PathShearX"] = s.PathShearX; + row["PathShearY"] = s.PathShearY; + row["PathSkew"] = s.PathSkew; + row["PathCurve"] = s.PathCurve; + row["PathRadiusOffset"] = s.PathRadiusOffset; + row["PathRevolutions"] = s.PathRevolutions; + row["PathTaperX"] = s.PathTaperX; + row["PathTaperY"] = s.PathTaperY; + row["PathTwist"] = s.PathTwist; + row["PathTwistBegin"] = s.PathTwistBegin; + // profile + row["ProfileBegin"] = s.ProfileBegin; + row["ProfileEnd"] = s.ProfileEnd; + row["ProfileCurve"] = s.ProfileCurve; + row["ProfileHollow"] = s.ProfileHollow; + row["State"] = s.State; + + row["Texture"] = s.TextureEntry; + row["ExtraParams"] = s.ExtraParams; + } + + private void addPrim(SceneObjectPart prim, LLUUID sceneGroupID, LLUUID regionUUID) + { + DataTable prims = ds.Tables["prims"]; + DataTable shapes = ds.Tables["primshapes"]; + + DataRow primRow = prims.Rows.Find(Util.ToRawUuidString(prim.UUID)); + if (primRow == null) + { + primRow = prims.NewRow(); + fillPrimRow(primRow, prim, sceneGroupID, regionUUID); + prims.Rows.Add(primRow); + } + else + { + fillPrimRow(primRow, prim, sceneGroupID, regionUUID); + } + + DataRow shapeRow = shapes.Rows.Find(Util.ToRawUuidString(prim.UUID)); + if (shapeRow == null) + { + shapeRow = shapes.NewRow(); + fillShapeRow(shapeRow, prim); + shapes.Rows.Add(shapeRow); + } + else + { + fillShapeRow(shapeRow, prim); + } + } + + // see IRegionDatastore + public void StorePrimInventory(LLUUID primID, ICollection items) + { + if (!persistPrimInventories) + return; + + m_log.InfoFormat("[DATASTORE]: Entered StorePrimInventory with prim ID {0}", primID); + + DataTable dbItems = ds.Tables["primitems"]; + + // For now, we're just going to crudely remove all the previous inventory items + // no matter whether they have changed or not, and replace them with the current set. + lock (ds) + { + RemoveItems(primID); + + // repalce with current inventory details + foreach (TaskInventoryItem newItem in items) + { +// m_log.InfoFormat( +// "[DATASTORE]: ", +// "Adding item {0}, {1} to prim ID {2}", +// newItem.Name, newItem.ItemID, newItem.ParentPartID); + + DataRow newItemRow = dbItems.NewRow(); + fillItemRow(newItemRow, newItem); + dbItems.Rows.Add(newItemRow); + } + } + + Commit(); + } + + /*********************************************************************** + * + * SQL Statement Creation Functions + * + * These functions create SQL statements for update, insert, and create. + * They can probably be factored later to have a db independant + * portion and a db specific portion + * + **********************************************************************/ + + private SqliteCommand createInsertCommand(string table, DataTable dt) + { + /** + * This is subtle enough to deserve some commentary. + * Instead of doing *lots* and *lots of hardcoded strings + * for database definitions we'll use the fact that + * realistically all insert statements look like "insert + * into A(b, c) values(:b, :c) on the parameterized query + * front. If we just have a list of b, c, etc... we can + * generate these strings instead of typing them out. + */ + string[] cols = new string[dt.Columns.Count]; + for (int i = 0; i < dt.Columns.Count; i++) + { + DataColumn col = dt.Columns[i]; + cols[i] = col.ColumnName; + } + + string sql = "insert into " + table + "("; + sql += String.Join(", ", cols); + // important, the first ':' needs to be here, the rest get added in the join + sql += ") values (:"; + sql += String.Join(", :", cols); + sql += ")"; + SqliteCommand cmd = new SqliteCommand(sql); + + // this provides the binding for all our parameters, so + // much less code than it used to be + foreach (DataColumn col in dt.Columns) + { + cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType)); + } + return cmd; + } + + private SqliteCommand createUpdateCommand(string table, string pk, DataTable dt) + { + string sql = "update " + table + " set "; + string subsql = String.Empty; + foreach (DataColumn col in dt.Columns) + { + if (subsql.Length > 0) + { + // a map function would rock so much here + subsql += ", "; + } + subsql += col.ColumnName + "= :" + col.ColumnName; + } + sql += subsql; + sql += " where " + pk; + SqliteCommand cmd = new SqliteCommand(sql); + + // this provides the binding for all our parameters, so + // much less code than it used to be + + foreach (DataColumn col in dt.Columns) + { + cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType)); + } + return cmd; + } + + + private string defineTable(DataTable dt) + { + string sql = "create table " + dt.TableName + "("; + string subsql = String.Empty; + foreach (DataColumn col in dt.Columns) + { + if (subsql.Length > 0) + { + // a map function would rock so much here + subsql += ",\n"; + } + subsql += col.ColumnName + " " + sqliteType(col.DataType); + if (dt.PrimaryKey.Length > 0 && col == dt.PrimaryKey[0]) + { + subsql += " primary key"; + } + } + sql += subsql; + sql += ")"; + return sql; + } + + /*********************************************************************** + * + * Database Binding functions + * + * These will be db specific due to typing, and minor differences + * in databases. + * + **********************************************************************/ + + /// + /// This is a convenience function that collapses 5 repetitive + /// lines for defining SqliteParameters to 2 parameters: + /// column name and database type. + /// + /// It assumes certain conventions like :param as the param + /// name to replace in parametrized queries, and that source + /// version is always current version, both of which are fine + /// for us. + /// + ///a built sqlite parameter + private SqliteParameter createSqliteParameter(string name, Type type) + { + SqliteParameter param = new SqliteParameter(); + param.ParameterName = ":" + name; + param.DbType = dbtypeFromType(type); + param.SourceColumn = name; + param.SourceVersion = DataRowVersion.Current; + return param; + } + + private void setupPrimCommands(SqliteDataAdapter da, SqliteConnection conn) + { + da.InsertCommand = createInsertCommand("prims", ds.Tables["prims"]); + da.InsertCommand.Connection = conn; + + da.UpdateCommand = createUpdateCommand("prims", "UUID=:UUID", ds.Tables["prims"]); + da.UpdateCommand.Connection = conn; + + SqliteCommand delete = new SqliteCommand("delete from prims where UUID = :UUID"); + delete.Parameters.Add(createSqliteParameter("UUID", typeof (String))); + delete.Connection = conn; + da.DeleteCommand = delete; + } + + private void setupItemsCommands(SqliteDataAdapter da, SqliteConnection conn) + { + da.InsertCommand = createInsertCommand("primitems", ds.Tables["primitems"]); + da.InsertCommand.Connection = conn; + + da.UpdateCommand = createUpdateCommand("primitems", "itemID = :itemID", ds.Tables["primitems"]); + da.UpdateCommand.Connection = conn; + + SqliteCommand delete = new SqliteCommand("delete from primitems where itemID = :itemID"); + delete.Parameters.Add(createSqliteParameter("itemID", typeof (String))); + delete.Connection = conn; + da.DeleteCommand = delete; + } + + private void setupTerrainCommands(SqliteDataAdapter da, SqliteConnection conn) + { + da.InsertCommand = createInsertCommand("terrain", ds.Tables["terrain"]); + da.InsertCommand.Connection = conn; + } + + private void setupLandCommands(SqliteDataAdapter da, SqliteConnection conn) + { + da.InsertCommand = createInsertCommand("land", ds.Tables["land"]); + da.InsertCommand.Connection = conn; + + da.UpdateCommand = createUpdateCommand("land", "UUID=:UUID", ds.Tables["land"]); + da.UpdateCommand.Connection = conn; + } + + private void setupLandAccessCommands(SqliteDataAdapter da, SqliteConnection conn) + { + da.InsertCommand = createInsertCommand("landaccesslist", ds.Tables["landaccesslist"]); + da.InsertCommand.Connection = conn; + } + + private void setupShapeCommands(SqliteDataAdapter da, SqliteConnection conn) + { + da.InsertCommand = createInsertCommand("primshapes", ds.Tables["primshapes"]); + da.InsertCommand.Connection = conn; + + da.UpdateCommand = createUpdateCommand("primshapes", "UUID=:UUID", ds.Tables["primshapes"]); + da.UpdateCommand.Connection = conn; + + SqliteCommand delete = new SqliteCommand("delete from primshapes where UUID = :UUID"); + delete.Parameters.Add(createSqliteParameter("UUID", typeof (String))); + delete.Connection = conn; + da.DeleteCommand = delete; + } + + /// + /// Create the necessary database tables. + /// + /// + private void InitDB(SqliteConnection conn) + { + string createPrims = defineTable(createPrimTable()); + string createShapes = defineTable(createShapeTable()); + string createItems = defineTable(createItemsTable()); + string createTerrain = defineTable(createTerrainTable()); + string createLand = defineTable(createLandTable()); + string createLandAccessList = defineTable(createLandAccessListTable()); + + SqliteCommand pcmd = new SqliteCommand(createPrims, conn); + SqliteCommand scmd = new SqliteCommand(createShapes, conn); + SqliteCommand icmd = new SqliteCommand(createItems, conn); + SqliteCommand tcmd = new SqliteCommand(createTerrain, conn); + SqliteCommand lcmd = new SqliteCommand(createLand, conn); + SqliteCommand lalcmd = new SqliteCommand(createLandAccessList, conn); + + try + { + pcmd.ExecuteNonQuery(); + } + catch (SqliteSyntaxException) + { + m_log.Warn("[SQLITE]: Primitives Table Already Exists"); + } + + try + { + scmd.ExecuteNonQuery(); + } + catch (SqliteSyntaxException) + { + m_log.Warn("[SQLITE]: Shapes Table Already Exists"); + } + + if (persistPrimInventories) + { + try + { + icmd.ExecuteNonQuery(); + } + catch (SqliteSyntaxException) + { + m_log.Warn("[SQLITE]: Primitives Inventory Table Already Exists"); + } + } + + try + { + tcmd.ExecuteNonQuery(); + } + catch (SqliteSyntaxException) + { + m_log.Warn("[SQLITE]: Terrain Table Already Exists"); + } + + try + { + lcmd.ExecuteNonQuery(); + } + catch (SqliteSyntaxException) + { + m_log.Warn("[SQLITE]: Land Table Already Exists"); + } + + try + { + lalcmd.ExecuteNonQuery(); + } + catch (SqliteSyntaxException) + { + m_log.Warn("[SQLITE]: LandAccessList Table Already Exists"); + } + } + + private bool TestTables(SqliteConnection conn) + { + SqliteCommand primSelectCmd = new SqliteCommand(primSelect, conn); + SqliteDataAdapter pDa = new SqliteDataAdapter(primSelectCmd); + + SqliteCommand shapeSelectCmd = new SqliteCommand(shapeSelect, conn); + SqliteDataAdapter sDa = new SqliteDataAdapter(shapeSelectCmd); + + SqliteCommand itemsSelectCmd = new SqliteCommand(itemsSelect, conn); + SqliteDataAdapter iDa = new SqliteDataAdapter(itemsSelectCmd); + + SqliteCommand terrainSelectCmd = new SqliteCommand(terrainSelect, conn); + SqliteDataAdapter tDa = new SqliteDataAdapter(terrainSelectCmd); + + SqliteCommand landSelectCmd = new SqliteCommand(landSelect, conn); + SqliteDataAdapter lDa = new SqliteDataAdapter(landSelectCmd); + + SqliteCommand landAccessListSelectCmd = new SqliteCommand(landAccessListSelect, conn); + SqliteDataAdapter lalDa = new SqliteDataAdapter(landAccessListSelectCmd); + + DataSet tmpDS = new DataSet(); + try + { + pDa.Fill(tmpDS, "prims"); + sDa.Fill(tmpDS, "primshapes"); + + if (persistPrimInventories) + iDa.Fill(tmpDS, "primitems"); + + tDa.Fill(tmpDS, "terrain"); + lDa.Fill(tmpDS, "land"); + lalDa.Fill(tmpDS, "landaccesslist"); + } + catch (SqliteSyntaxException) + { + m_log.Info("[DATASTORE]: SQLite Database doesn't exist... creating"); + InitDB(conn); + } + + pDa.Fill(tmpDS, "prims"); + sDa.Fill(tmpDS, "primshapes"); + + if (persistPrimInventories) + iDa.Fill(tmpDS, "primitems"); + + tDa.Fill(tmpDS, "terrain"); + lDa.Fill(tmpDS, "land"); + lalDa.Fill(tmpDS, "landaccesslist"); + + foreach (DataColumn col in createPrimTable().Columns) + { + if (!tmpDS.Tables["prims"].Columns.Contains(col.ColumnName)) + { + m_log.Info("[DATASTORE]: Missing required column:" + col.ColumnName); + return false; + } + } + + foreach (DataColumn col in createShapeTable().Columns) + { + if (!tmpDS.Tables["primshapes"].Columns.Contains(col.ColumnName)) + { + m_log.Info("[DATASTORE]: Missing required column:" + col.ColumnName); + return false; + } + } + + // XXX primitems should probably go here eventually + + foreach (DataColumn col in createTerrainTable().Columns) + { + if (!tmpDS.Tables["terrain"].Columns.Contains(col.ColumnName)) + { + m_log.Info("[DATASTORE]: Missing require column:" + col.ColumnName); + return false; + } + } + + foreach (DataColumn col in createLandTable().Columns) + { + if (!tmpDS.Tables["land"].Columns.Contains(col.ColumnName)) + { + m_log.Info("[DATASTORE]: Missing require column:" + col.ColumnName); + return false; + } + } + + foreach (DataColumn col in createLandAccessListTable().Columns) + { + if (!tmpDS.Tables["landaccesslist"].Columns.Contains(col.ColumnName)) + { + m_log.Info("[DATASTORE]: Missing require column:" + col.ColumnName); + return false; + } + } + + return true; + } + + /*********************************************************************** + * + * Type conversion functions + * + **********************************************************************/ + + private DbType dbtypeFromType(Type type) + { + if (type == typeof (String)) + { + return DbType.String; + } + else if (type == typeof (Int32)) + { + return DbType.Int32; + } + else if (type == typeof (Double)) + { + return DbType.Double; + } + else if (type == typeof (Byte)) + { + return DbType.Byte; + } + else if (type == typeof (Double)) + { + return DbType.Double; + } + else if (type == typeof (Byte[])) + { + return DbType.Binary; + } + else + { + return DbType.String; + } + } + + // this is something we'll need to implement for each db + // slightly differently. + private string sqliteType(Type type) + { + if (type == typeof (String)) + { + return "varchar(255)"; + } + else if (type == typeof (Int32)) + { + return "integer"; + } + else if (type == typeof (Int64)) + { + return "integer"; + } + else if (type == typeof (Double)) + { + return "float"; + } + else if (type == typeof (Byte[])) + { + return "blob"; + } + else + { + return "string"; + } + } + } +} diff --git a/OpenSim/Data/SQLite/SQLiteUserData.cs b/OpenSim/Data/SQLite/SQLiteUserData.cs new file mode 100644 index 0000000..2efd4aa --- /dev/null +++ b/OpenSim/Data/SQLite/SQLiteUserData.cs @@ -0,0 +1,821 @@ +/* + * Copyright (c) Contributors, http://opensimulator.org/ + * See CONTRIBUTORS.TXT for a full list of copyright holders. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions are met: + * * Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * * Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * * Neither the name of the OpenSim Project nor the + * names of its contributors may be used to endorse or promote products + * derived from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY + * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED + * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY + * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES + * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; + * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT + * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS + * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + */ + +using System; +using System.Collections.Generic; +using System.Data; +using libsecondlife; +using Mono.Data.SqliteClient; +using OpenSim.Framework.Console; + +namespace OpenSim.Framework.Data.SQLite +{ + /// + /// A User storage interface for the SQLite database system + /// + public class SQLiteUserData : UserDataBase + { + private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); + + /// + /// The database manager + /// + /// + /// Artificial constructor called upon plugin load + /// + private const string SelectUserByUUID = "select * from users where UUID=:UUID"; + private const string SelectUserByName = "select * from users where username=:username and surname=:surname"; + private const string SelectFriendsByUUID = "select a.friendID, a.friendPerms, b.friendPerms from userfriends as a, userfriends as b where a.ownerID=:ownerID and b.ownerID=a.friendID and b.friendID=a.ownerID"; + + private const string userSelect = "select * from users"; + private const string userFriendsSelect = "select a.ownerID as ownerID,a.friendID as friendID,a.friendPerms as friendPerms,b.friendPerms as ownerperms, b.ownerID as fownerID, b.friendID as ffriendID from userfriends as a, userfriends as b"; + + private const string AvatarPickerAndSQL = "select * from users where username like :username and surname like :surname"; + private const string AvatarPickerOrSQL = "select * from users where username like :username or surname like :surname"; + + private DataSet ds; + private SqliteDataAdapter da; + private SqliteDataAdapter daf; + SqliteConnection g_conn; + + override public void Initialise() + { + SqliteConnection conn = new SqliteConnection("URI=file:userprofiles.db,version=3"); + TestTables(conn); + + // This sucks, but It doesn't seem to work with the dataset Syncing :P + g_conn = conn; + g_conn.Open(); + + ds = new DataSet(); + da = new SqliteDataAdapter(new SqliteCommand(userSelect, conn)); + daf = new SqliteDataAdapter(new SqliteCommand(userFriendsSelect, conn)); + + lock (ds) + { + ds.Tables.Add(createUsersTable()); + ds.Tables.Add(createUserAgentsTable()); + ds.Tables.Add(createUserFriendsTable()); + + setupUserCommands(da, conn); + da.Fill(ds.Tables["users"]); + + setupUserFriendsCommands(daf, conn); + try + { + daf.Fill(ds.Tables["userfriends"]); + } + catch (SqliteSyntaxException) + { + m_log.Info("[SQLITE]: userfriends table not found, creating.... "); + InitDB(conn); + daf.Fill(ds.Tables["userfriends"]); + } + + } + + return; + } + + // see IUserData + override public UserProfileData GetUserByUUID(LLUUID uuid) + { + lock (ds) + { + DataRow row = ds.Tables["users"].Rows.Find(Util.ToRawUuidString(uuid)); + if (row != null) + { + UserProfileData user = buildUserProfile(row); + row = ds.Tables["useragents"].Rows.Find(Util.ToRawUuidString(uuid)); + if (row != null) + { + user.currentAgent = buildUserAgent(row); + } + return user; + } + else + { + return null; + } + } + } + + // see IUserData + override public UserProfileData GetUserByName(string fname, string lname) + { + string select = "surname = '" + lname + "' and username = '" + fname + "'"; + lock (ds) + { + DataRow[] rows = ds.Tables["users"].Select(select); + if (rows.Length > 0) + { + UserProfileData user = buildUserProfile(rows[0]); + DataRow row = ds.Tables["useragents"].Rows.Find(Util.ToRawUuidString(user.UUID)); + if (row != null) + { + user.currentAgent = buildUserAgent(row); + } + return user; + } + else + { + return null; + } + } + } + + #region User Friends List Data + + override public void AddNewUserFriend(LLUUID friendlistowner, LLUUID friend, uint perms) + { + string InsertFriends = "insert into userfriends(ownerID, friendID, friendPerms) values(:ownerID, :friendID, :perms)"; + + using (SqliteCommand cmd = new SqliteCommand(InsertFriends, g_conn)) + { + cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.UUID.ToString())); + cmd.Parameters.Add(new SqliteParameter(":friendID", friend.UUID.ToString())); + cmd.Parameters.Add(new SqliteParameter(":perms", perms)); + cmd.ExecuteNonQuery(); + } + using (SqliteCommand cmd = new SqliteCommand(InsertFriends, g_conn)) + { + cmd.Parameters.Add(new SqliteParameter(":ownerID", friend.UUID.ToString())); + cmd.Parameters.Add(new SqliteParameter(":friendID", friendlistowner.UUID.ToString())); + cmd.Parameters.Add(new SqliteParameter(":perms", perms)); + cmd.ExecuteNonQuery(); + } + } + + override public void RemoveUserFriend(LLUUID friendlistowner, LLUUID friend) + { + string DeletePerms = "delete from friendlist where (ownerID=:ownerID and friendID=:friendID) or (ownerID=:friendID and friendID=:ownerID)"; + using (SqliteCommand cmd = new SqliteCommand(DeletePerms, g_conn)) + { + cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.UUID.ToString())); + cmd.Parameters.Add(new SqliteParameter(":friendID", friend.UUID.ToString())); + cmd.ExecuteNonQuery(); + } + } + + override public void UpdateUserFriendPerms(LLUUID friendlistowner, LLUUID friend, uint perms) + { + string UpdatePerms = "update friendlist set perms=:perms where ownerID=:ownerID and friendID=:friendID"; + using (SqliteCommand cmd = new SqliteCommand(UpdatePerms, g_conn)) + { + cmd.Parameters.Add(new SqliteParameter(":perms", perms)); + cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.UUID.ToString())); + cmd.Parameters.Add(new SqliteParameter(":friendID", friend.UUID.ToString())); + cmd.ExecuteNonQuery(); + } + } + + override public List GetUserFriendList(LLUUID friendlistowner) + { + List returnlist = new List(); + + using (SqliteCommand cmd = new SqliteCommand(SelectFriendsByUUID, g_conn)) + { + cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.UUID.ToString())); + + try + { + using (IDataReader reader = cmd.ExecuteReader()) + { + while (reader.Read()) + { + FriendListItem user = new FriendListItem(); + user.FriendListOwner = friendlistowner; + user.Friend = new LLUUID((string)reader[0]); + user.FriendPerms = Convert.ToUInt32(reader[1]); + user.FriendListOwnerPerms = Convert.ToUInt32(reader[2]); + returnlist.Add(user); + } + reader.Close(); + } + } + catch (Exception ex) + { + m_log.Error("[USER]: Exception getting friends list for user: " + ex.ToString()); + } + } + + return returnlist; + } + + + + + #endregion + + override public void UpdateUserCurrentRegion(LLUUID avatarid, LLUUID regionuuid) + { + m_log.Info("[USER]: Stub UpdateUserCUrrentRegion called"); + } + + + override public List GeneratePickerResults(LLUUID queryID, string query) + { + List returnlist = new List(); + string[] querysplit; + querysplit = query.Split(' '); + if (querysplit.Length == 2) + { + using (SqliteCommand cmd = new SqliteCommand(AvatarPickerAndSQL, g_conn)) + { + cmd.Parameters.Add(new SqliteParameter(":username", querysplit[0] + "%")); + cmd.Parameters.Add(new SqliteParameter(":surname", querysplit[1] + "%")); + + using (IDataReader reader = cmd.ExecuteReader()) + { + while (reader.Read()) + { + Framework.AvatarPickerAvatar user = new Framework.AvatarPickerAvatar(); + user.AvatarID = new LLUUID((string) reader["UUID"]); + user.firstName = (string) reader["username"]; + user.lastName = (string) reader["surname"]; + returnlist.Add(user); + } + reader.Close(); + } + } + } + else if (querysplit.Length == 1) + { + using (SqliteCommand cmd = new SqliteCommand(AvatarPickerOrSQL, g_conn)) + { + cmd.Parameters.Add(new SqliteParameter(":username", querysplit[0] + "%")); + cmd.Parameters.Add(new SqliteParameter(":surname", querysplit[0] + "%")); + + using (IDataReader reader = cmd.ExecuteReader()) + { + while (reader.Read()) + { + Framework.AvatarPickerAvatar user = new Framework.AvatarPickerAvatar(); + user.AvatarID = new LLUUID((string) reader["UUID"]); + user.firstName = (string) reader["username"]; + user.lastName = (string) reader["surname"]; + returnlist.Add(user); + } + reader.Close(); + } + } + } + return returnlist; + } + + /// + /// Returns a user by UUID direct + /// + /// The user's account ID + /// A matching user profile + override public UserAgentData GetAgentByUUID(LLUUID uuid) + { + try + { + return GetUserByUUID(uuid).currentAgent; + } + catch (Exception) + { + return null; + } + } + + /// + /// Returns a session by account name + /// + /// The account name + /// The user's session agent + override public UserAgentData GetAgentByName(string name) + { + return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]); + } + + /// + /// Returns a session by account name + /// + /// The first part of the user's account name + /// The second part of the user's account name + /// A user agent + override public UserAgentData GetAgentByName(string fname, string lname) + { + try + { + return GetUserByName(fname, lname).currentAgent; + } + catch (Exception) + { + return null; + } + } + + + override public void StoreWebLoginKey(LLUUID AgentID, LLUUID WebLoginKey) + { + DataTable users = ds.Tables["users"]; + lock (ds) + { + DataRow row = users.Rows.Find(Util.ToRawUuidString(AgentID)); + if (row == null) + { + m_log.Warn("[WEBLOGIN]: Unable to store new web login key for non-existant user"); + } + else + { + UserProfileData user = GetUserByUUID(AgentID); + user.webLoginKey = WebLoginKey; + fillUserRow(row, user); + da.Update(ds, "users"); + + } + } + + } + + /// + /// Creates a new user profile + /// + /// The profile to add to the database + override public void AddNewUserProfile(UserProfileData user) + { + DataTable users = ds.Tables["users"]; + lock (ds) + { + DataRow row = users.Rows.Find(Util.ToRawUuidString(user.UUID)); + if (row == null) + { + row = users.NewRow(); + fillUserRow(row, user); + users.Rows.Add(row); + } + else + { + fillUserRow(row, user); + + } + // This is why we're getting the 'logins never log-off'.. because It isn't clearing the + // useragents table once the useragent is null + // + // A database guy should look at this and figure out the best way to clear the useragents table. + if (user.currentAgent != null) + { + DataTable ua = ds.Tables["useragents"]; + row = ua.Rows.Find(Util.ToRawUuidString(user.UUID)); + if (row == null) + { + row = ua.NewRow(); + fillUserAgentRow(row, user.currentAgent); + ua.Rows.Add(row); + } + else + { + fillUserAgentRow(row, user.currentAgent); + } + } + else + { + // I just added this to help the standalone login situation. + //It still needs to be looked at by a Database guy + DataTable ua = ds.Tables["useragents"]; + row = ua.Rows.Find(Util.ToRawUuidString(user.UUID)); + + if (row == null) + { + // do nothing + } + else + { + row.Delete(); + ua.AcceptChanges(); + } + } + + m_log.Info("[SQLITE]: " + + "Syncing user database: " + ds.Tables["users"].Rows.Count + " users stored"); + // save changes off to disk + da.Update(ds, "users"); + } + } + + /// + /// Creates a new user profile + /// + /// The profile to add to the database + /// True on success, false on error + override public bool UpdateUserProfile(UserProfileData user) + { + try + { + AddNewUserProfile(user); + return true; + } + catch (Exception) + { + return false; + } + } + + /// + /// Creates a new user agent + /// + /// The agent to add to the database + override public void AddNewUserAgent(UserAgentData agent) + { + // Do nothing. yet. + } + + /// + /// Transfers money between two user accounts + /// + /// Starting account + /// End account + /// The amount to move + /// Success? + override public bool MoneyTransferRequest(LLUUID from, LLUUID to, uint amount) + { + return true; + } + + /// + /// Transfers inventory between two accounts + /// + /// Move to inventory server + /// Senders account + /// Receivers account + /// Inventory item + /// Success? + override public bool InventoryTransferRequest(LLUUID from, LLUUID to, LLUUID item) + { + return true; + } + + /// + /// Returns the name of the storage provider + /// + /// Storage provider name + override public string getName() + { + return "Sqlite Userdata"; + } + + /// + /// Returns the version of the storage provider + /// + /// Storage provider version + override public string GetVersion() + { + return "0.1"; + } + + /*********************************************************************** + * + * DataTable creation + * + **********************************************************************/ + /*********************************************************************** + * + * Database Definition Functions + * + * This should be db agnostic as we define them in ADO.NET terms + * + **********************************************************************/ + + private static DataTable createUsersTable() + { + DataTable users = new DataTable("users"); + + SQLiteUtil.createCol(users, "UUID", typeof (String)); + SQLiteUtil.createCol(users, "username", typeof (String)); + SQLiteUtil.createCol(users, "surname", typeof (String)); + SQLiteUtil.createCol(users, "passwordHash", typeof (String)); + SQLiteUtil.createCol(users, "passwordSalt", typeof (String)); + + SQLiteUtil.createCol(users, "homeRegionX", typeof (Int32)); + SQLiteUtil.createCol(users, "homeRegionY", typeof (Int32)); + SQLiteUtil.createCol(users, "homeLocationX", typeof (Double)); + SQLiteUtil.createCol(users, "homeLocationY", typeof (Double)); + SQLiteUtil.createCol(users, "homeLocationZ", typeof (Double)); + SQLiteUtil.createCol(users, "homeLookAtX", typeof (Double)); + SQLiteUtil.createCol(users, "homeLookAtY", typeof (Double)); + SQLiteUtil.createCol(users, "homeLookAtZ", typeof (Double)); + SQLiteUtil.createCol(users, "created", typeof (Int32)); + SQLiteUtil.createCol(users, "lastLogin", typeof (Int32)); + SQLiteUtil.createCol(users, "rootInventoryFolderID", typeof (String)); + SQLiteUtil.createCol(users, "userInventoryURI", typeof (String)); + SQLiteUtil.createCol(users, "userAssetURI", typeof (String)); + SQLiteUtil.createCol(users, "profileCanDoMask", typeof (Int32)); + SQLiteUtil.createCol(users, "profileWantDoMask", typeof (Int32)); + SQLiteUtil.createCol(users, "profileAboutText", typeof (String)); + SQLiteUtil.createCol(users, "profileFirstText", typeof (String)); + SQLiteUtil.createCol(users, "profileImage", typeof (String)); + SQLiteUtil.createCol(users, "profileFirstImage", typeof (String)); + SQLiteUtil.createCol(users, "webLoginKey", typeof(String)); + // Add in contraints + users.PrimaryKey = new DataColumn[] {users.Columns["UUID"]}; + return users; + } + + private static DataTable createUserAgentsTable() + { + DataTable ua = new DataTable("useragents"); + // this is the UUID of the user + SQLiteUtil.createCol(ua, "UUID", typeof (String)); + SQLiteUtil.createCol(ua, "agentIP", typeof (String)); + SQLiteUtil.createCol(ua, "agentPort", typeof (Int32)); + SQLiteUtil.createCol(ua, "agentOnline", typeof (Boolean)); + SQLiteUtil.createCol(ua, "sessionID", typeof (String)); + SQLiteUtil.createCol(ua, "secureSessionID", typeof (String)); + SQLiteUtil.createCol(ua, "regionID", typeof (String)); + SQLiteUtil.createCol(ua, "loginTime", typeof (Int32)); + SQLiteUtil.createCol(ua, "logoutTime", typeof (Int32)); + SQLiteUtil.createCol(ua, "currentRegion", typeof (String)); + SQLiteUtil.createCol(ua, "currentHandle", typeof (String)); + // vectors + SQLiteUtil.createCol(ua, "currentPosX", typeof (Double)); + SQLiteUtil.createCol(ua, "currentPosY", typeof (Double)); + SQLiteUtil.createCol(ua, "currentPosZ", typeof (Double)); + // constraints + ua.PrimaryKey = new DataColumn[] {ua.Columns["UUID"]}; + + return ua; + } + + private static DataTable createUserFriendsTable() + { + DataTable ua = new DataTable("userfriends"); + // table contains user <----> user relationship with perms + SQLiteUtil.createCol(ua, "ownerID", typeof(String)); + SQLiteUtil.createCol(ua, "friendID", typeof(String)); + SQLiteUtil.createCol(ua, "friendPerms", typeof(Int32)); + SQLiteUtil.createCol(ua, "ownerPerms", typeof(Int32)); + SQLiteUtil.createCol(ua, "datetimestamp", typeof(Int32)); + + return ua; + } + + /*********************************************************************** + * + * Convert between ADO.NET <=> OpenSim Objects + * + * These should be database independant + * + **********************************************************************/ + + private static UserProfileData buildUserProfile(DataRow row) + { + // TODO: this doesn't work yet because something more + // interesting has to be done to actually get these values + // back out. Not enough time to figure it out yet. + UserProfileData user = new UserProfileData(); + LLUUID.TryParse((String)row["UUID"], out user.UUID); + user.username = (String) row["username"]; + user.surname = (String) row["surname"]; + user.passwordHash = (String) row["passwordHash"]; + user.passwordSalt = (String) row["passwordSalt"]; + + user.homeRegionX = Convert.ToUInt32(row["homeRegionX"]); + user.homeRegionY = Convert.ToUInt32(row["homeRegionY"]); + user.homeLocation = new LLVector3( + Convert.ToSingle(row["homeLocationX"]), + Convert.ToSingle(row["homeLocationY"]), + Convert.ToSingle(row["homeLocationZ"]) + ); + user.homeLookAt = new LLVector3( + Convert.ToSingle(row["homeLookAtX"]), + Convert.ToSingle(row["homeLookAtY"]), + Convert.ToSingle(row["homeLookAtZ"]) + ); + user.created = Convert.ToInt32(row["created"]); + user.lastLogin = Convert.ToInt32(row["lastLogin"]); + user.rootInventoryFolderID = new LLUUID((String) row["rootInventoryFolderID"]); + user.userInventoryURI = (String) row["userInventoryURI"]; + user.userAssetURI = (String) row["userAssetURI"]; + user.profileCanDoMask = Convert.ToUInt32(row["profileCanDoMask"]); + user.profileWantDoMask = Convert.ToUInt32(row["profileWantDoMask"]); + user.profileAboutText = (String) row["profileAboutText"]; + user.profileFirstText = (String) row["profileFirstText"]; + LLUUID.TryParse((String)row["profileImage"], out user.profileImage); + LLUUID.TryParse((String)row["profileFirstImage"], out user.profileFirstImage); + user.webLoginKey = new LLUUID((String) row["webLoginKey"]); + + return user; + } + + private void fillUserRow(DataRow row, UserProfileData user) + { + row["UUID"] = Util.ToRawUuidString(user.UUID); + row["username"] = user.username; + row["surname"] = user.surname; + row["passwordHash"] = user.passwordHash; + row["passwordSalt"] = user.passwordSalt; + + + row["homeRegionX"] = user.homeRegionX; + row["homeRegionY"] = user.homeRegionY; + row["homeLocationX"] = user.homeLocation.X; + row["homeLocationY"] = user.homeLocation.Y; + row["homeLocationZ"] = user.homeLocation.Z; + row["homeLookAtX"] = user.homeLookAt.X; + row["homeLookAtY"] = user.homeLookAt.Y; + row["homeLookAtZ"] = user.homeLookAt.Z; + + row["created"] = user.created; + row["lastLogin"] = user.lastLogin; + row["rootInventoryFolderID"] = user.rootInventoryFolderID; + row["userInventoryURI"] = user.userInventoryURI; + row["userAssetURI"] = user.userAssetURI; + row["profileCanDoMask"] = user.profileCanDoMask; + row["profileWantDoMask"] = user.profileWantDoMask; + row["profileAboutText"] = user.profileAboutText; + row["profileFirstText"] = user.profileFirstText; + row["profileImage"] = user.profileImage; + row["profileFirstImage"] = user.profileFirstImage; + row["webLoginKey"] = user.webLoginKey; + + // ADO.NET doesn't handle NULL very well + foreach (DataColumn col in ds.Tables["users"].Columns) + { + if (row[col] == null) + { + row[col] = String.Empty; + } + } + } + + private static UserAgentData buildUserAgent(DataRow row) + { + UserAgentData ua = new UserAgentData(); + + ua.UUID = new LLUUID((String) row["UUID"]); + ua.agentIP = (String) row["agentIP"]; + ua.agentPort = Convert.ToUInt32(row["agentPort"]); + ua.agentOnline = Convert.ToBoolean(row["agentOnline"]); + ua.sessionID = new LLUUID((String) row["sessionID"]); + ua.secureSessionID = new LLUUID((String) row["secureSessionID"]); + ua.regionID = new LLUUID((String) row["regionID"]); + ua.loginTime = Convert.ToInt32(row["loginTime"]); + ua.logoutTime = Convert.ToInt32(row["logoutTime"]); + ua.currentRegion = new LLUUID((String) row["currentRegion"]); + ua.currentHandle = Convert.ToUInt64(row["currentHandle"]); + ua.currentPos = new LLVector3( + Convert.ToSingle(row["currentPosX"]), + Convert.ToSingle(row["currentPosY"]), + Convert.ToSingle(row["currentPosZ"]) + ); + return ua; + } + + private static void fillUserAgentRow(DataRow row, UserAgentData ua) + { + row["UUID"] = ua.UUID; + row["agentIP"] = ua.agentIP; + row["agentPort"] = ua.agentPort; + row["agentOnline"] = ua.agentOnline; + row["sessionID"] = ua.sessionID; + row["secureSessionID"] = ua.secureSessionID; + row["regionID"] = ua.regionID; + row["loginTime"] = ua.loginTime; + row["logoutTime"] = ua.logoutTime; + row["currentRegion"] = ua.currentRegion; + row["currentHandle"] = ua.currentHandle.ToString(); + // vectors + row["currentPosX"] = ua.currentPos.X; + row["currentPosY"] = ua.currentPos.Y; + row["currentPosZ"] = ua.currentPos.Z; + } + + /*********************************************************************** + * + * Database Binding functions + * + * These will be db specific due to typing, and minor differences + * in databases. + * + **********************************************************************/ + + private void setupUserCommands(SqliteDataAdapter da, SqliteConnection conn) + { + da.InsertCommand = SQLiteUtil.createInsertCommand("users", ds.Tables["users"]); + da.InsertCommand.Connection = conn; + + da.UpdateCommand = SQLiteUtil.createUpdateCommand("users", "UUID=:UUID", ds.Tables["users"]); + da.UpdateCommand.Connection = conn; + + SqliteCommand delete = new SqliteCommand("delete from users where UUID = :UUID"); + delete.Parameters.Add(SQLiteUtil.createSqliteParameter("UUID", typeof(String))); + delete.Connection = conn; + da.DeleteCommand = delete; + } + + private void setupUserFriendsCommands(SqliteDataAdapter daf, SqliteConnection conn) + { + daf.InsertCommand = SQLiteUtil.createInsertCommand("userfriends", ds.Tables["userfriends"]); + daf.InsertCommand.Connection = conn; + + daf.UpdateCommand = SQLiteUtil.createUpdateCommand("userfriends", "ownerID=:ownerID and friendID=:friendID", ds.Tables["userfriends"]); + daf.UpdateCommand.Connection = conn; + + SqliteCommand delete = new SqliteCommand("delete from userfriends where ownerID=:ownerID and friendID=:friendID"); + delete.Parameters.Add(SQLiteUtil.createSqliteParameter("ownerID", typeof(String))); + delete.Parameters.Add(SQLiteUtil.createSqliteParameter("friendID", typeof(String))); + delete.Connection = conn; + daf.DeleteCommand = delete; + + } + + private void InitDB(SqliteConnection conn) + { + string createUsers = SQLiteUtil.defineTable(createUsersTable()); + string createFriends = SQLiteUtil.defineTable(createUserFriendsTable()); + + SqliteCommand pcmd = new SqliteCommand(createUsers, conn); + SqliteCommand fcmd = new SqliteCommand(createFriends, conn); + + conn.Open(); + + try + { + + pcmd.ExecuteNonQuery(); + } + catch (System.Exception) + { + m_log.Info("[USERS]: users table already exists"); + } + + try + { + fcmd.ExecuteNonQuery(); + } + catch (System.Exception) + { + m_log.Info("[USERS]: userfriends table already exists"); + } + + conn.Close(); + } + + private bool TestTables(SqliteConnection conn) + { + SqliteCommand cmd = new SqliteCommand(userSelect, conn); + SqliteCommand fcmd = new SqliteCommand(userFriendsSelect, conn); + SqliteDataAdapter pDa = new SqliteDataAdapter(cmd); + SqliteDataAdapter fDa = new SqliteDataAdapter(cmd); + + DataSet tmpDS = new DataSet(); + DataSet tmpDS2 = new DataSet(); + + try + { + pDa.Fill(tmpDS, "users"); + fDa.Fill(tmpDS2, "userfriends"); + } + catch (SqliteSyntaxException) + { + m_log.Info("[DATASTORE]: SQLite Database doesn't exist... creating"); + InitDB(conn); + } + conn.Open(); + try + { + cmd = new SqliteCommand("select webLoginKey from users limit 1;", conn); + cmd.ExecuteNonQuery(); + } + catch (SqliteSyntaxException) + { + cmd = new SqliteCommand("alter table users add column webLoginKey text default '00000000-0000-0000-0000-000000000000';", conn); + cmd.ExecuteNonQuery(); + pDa.Fill(tmpDS, "users"); + } + finally + { + conn.Close(); + } + + return true; + } + } +} diff --git a/OpenSim/Data/SQLite/SQLiteUtils.cs b/OpenSim/Data/SQLite/SQLiteUtils.cs new file mode 100644 index 0000000..1334e53 --- /dev/null +++ b/OpenSim/Data/SQLite/SQLiteUtils.cs @@ -0,0 +1,269 @@ +/* + * Copyright (c) Contributors, http://opensimulator.org/ + * See CONTRIBUTORS.TXT for a full list of copyright holders. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions are met: + * * Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * * Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * * Neither the name of the OpenSim Project nor the + * names of its contributors may be used to endorse or promote products + * derived from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY + * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED + * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY + * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES + * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; + * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT + * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS + * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + */ + +using System; +using System.Data; +using Mono.Data.SqliteClient; + +namespace OpenSim.Framework.Data.SQLite +{ + /// + /// A base class for methods needed by all SQLite database classes + /// + public class SQLiteUtil + { + /*********************************************************************** + * + * Database Definition Helper Functions + * + * This should be db agnostic as we define them in ADO.NET terms + * + **********************************************************************/ + + public static void createCol(DataTable dt, string name, Type type) + { + DataColumn col = new DataColumn(name, type); + dt.Columns.Add(col); + } + + /*********************************************************************** + * + * SQL Statement Creation Functions + * + * These functions create SQL statements for update, insert, and create. + * They can probably be factored later to have a db independant + * portion and a db specific portion + * + **********************************************************************/ + + public static SqliteCommand createInsertCommand(string table, DataTable dt) + { + /** + * This is subtle enough to deserve some commentary. + * Instead of doing *lots* and *lots of hardcoded strings + * for database definitions we'll use the fact that + * realistically all insert statements look like "insert + * into A(b, c) values(:b, :c) on the parameterized query + * front. If we just have a list of b, c, etc... we can + * generate these strings instead of typing them out. + */ + string[] cols = new string[dt.Columns.Count]; + for (int i = 0; i < dt.Columns.Count; i++) + { + DataColumn col = dt.Columns[i]; + cols[i] = col.ColumnName; + } + + string sql = "insert into " + table + "("; + sql += String.Join(", ", cols); + // important, the first ':' needs to be here, the rest get added in the join + sql += ") values (:"; + sql += String.Join(", :", cols); + sql += ")"; + SqliteCommand cmd = new SqliteCommand(sql); + + // this provides the binding for all our parameters, so + // much less code than it used to be + foreach (DataColumn col in dt.Columns) + { + cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType)); + } + return cmd; + } + + public static SqliteCommand createUpdateCommand(string table, string pk, DataTable dt) + { + string sql = "update " + table + " set "; + string subsql = String.Empty; + foreach (DataColumn col in dt.Columns) + { + if (subsql.Length > 0) + { + // a map function would rock so much here + subsql += ", "; + } + subsql += col.ColumnName + "= :" + col.ColumnName; + } + sql += subsql; + sql += " where " + pk; + SqliteCommand cmd = new SqliteCommand(sql); + + // this provides the binding for all our parameters, so + // much less code than it used to be + + foreach (DataColumn col in dt.Columns) + { + cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType)); + } + return cmd; + } + + + public static string defineTable(DataTable dt) + { + string sql = "create table " + dt.TableName + "("; + string subsql = String.Empty; + foreach (DataColumn col in dt.Columns) + { + if (subsql.Length > 0) + { + // a map function would rock so much here + subsql += ",\n"; + } + subsql += col.ColumnName + " " + sqliteType(col.DataType); + if (dt.PrimaryKey.Length > 0) + { + if (col == dt.PrimaryKey[0]) + { + subsql += " primary key"; + } + } + } + sql += subsql; + sql += ")"; + return sql; + } + + /*********************************************************************** + * + * Database Binding functions + * + * These will be db specific due to typing, and minor differences + * in databases. + * + **********************************************************************/ + + /// + /// This is a convenience function that collapses 5 repetitive + /// lines for defining SqliteParameters to 2 parameters: + /// column name and database type. + /// + /// It assumes certain conventions like :param as the param + /// name to replace in parametrized queries, and that source + /// version is always current version, both of which are fine + /// for us. + /// + ///a built sqlite parameter + public static SqliteParameter createSqliteParameter(string name, Type type) + { + SqliteParameter param = new SqliteParameter(); + param.ParameterName = ":" + name; + param.DbType = dbtypeFromType(type); + param.SourceColumn = name; + param.SourceVersion = DataRowVersion.Current; + return param; + } + + /*********************************************************************** + * + * Type conversion functions + * + **********************************************************************/ + + public static DbType dbtypeFromType(Type type) + { + if (type == typeof (String)) + { + return DbType.String; + } + else if (type == typeof (Int32)) + { + return DbType.Int32; + } + else if (type == typeof (UInt32)) + { + return DbType.UInt32; + } + else if (type == typeof (Int64)) + { + return DbType.Int64; + } + else if (type == typeof (UInt64)) + { + return DbType.UInt64; + } + else if (type == typeof (Double)) + { + return DbType.Double; + } + else if (type == typeof (Boolean)) + { + return DbType.Boolean; + } + else if (type == typeof (Byte[])) + { + return DbType.Binary; + } + else + { + return DbType.String; + } + } + + // this is something we'll need to implement for each db + // slightly differently. + public static string sqliteType(Type type) + { + if (type == typeof (String)) + { + return "varchar(255)"; + } + else if (type == typeof (Int32)) + { + return "integer"; + } + else if (type == typeof (UInt32)) + { + return "integer"; + } + else if (type == typeof (Int64)) + { + return "varchar(255)"; + } + else if (type == typeof (UInt64)) + { + return "varchar(255)"; + } + else if (type == typeof (Double)) + { + return "float"; + } + else if (type == typeof (Boolean)) + { + return "integer"; + } + else if (type == typeof (Byte[])) + { + return "blob"; + } + else + { + return "string"; + } + } + } +} -- cgit v1.1