/* * 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 OpenSimulator 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.Sqlite; namespace OpenSim.Data.SQLite { /// <summary> /// A base class for methods needed by all SQLite database classes /// </summary> public class SQLiteUtil { /*********************************************************************** * * Database Definition Helper Functions * * This should be db agnostic as we define them in ADO.NET terms * **********************************************************************/ /// <summary> /// /// </summary> /// <param name="dt"></param> /// <param name="name"></param> /// <param name="type"></param> 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 * **********************************************************************/ /// <summary> /// Create an insert command /// </summary> /// <param name="table">table name</param> /// <param name="dt">data table</param> /// <returns>the created command</returns> /// <remarks> /// 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. /// </remarks> public static SqliteCommand createInsertCommand(string table, DataTable dt) { 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; } /// <summary> /// create an update command /// </summary> /// <param name="table">table name</param> /// <param name="pk"></param> /// <param name="dt"></param> /// <returns>the created command</returns> 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; } /// <summary> /// /// </summary> /// <param name="dt">Data Table</param> /// <returns></returns> 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. * **********************************************************************/ ///<summary> /// <para> /// This is a convenience function that collapses 5 repetitive /// lines for defining SqliteParameters to 2 parameters: /// column name and database type. /// </para> /// /// <para> /// 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. /// </para> ///</summary> /// <param name="name"></param> /// <param name="type"></param> ///<returns>a built sqlite parameter</returns> 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 * **********************************************************************/ /// <summary> /// Type conversion function /// </summary> /// <param name="type">a type</param> /// <returns>a DbType</returns> 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; } } /// <summary> /// </summary> /// <param name="type">a Type</param> /// <returns>a string</returns> /// <remarks>this is something we'll need to implement for each db slightly differently.</remarks> 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"; } } } }