SQLiteUtils.cs 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307
  1. /*
  2. * Copyright (c) Contributors, http://opensimulator.org/
  3. * See CONTRIBUTORS.TXT for a full list of copyright holders.
  4. *
  5. * Redistribution and use in source and binary forms, with or without
  6. * modification, are permitted provided that the following conditions are met:
  7. * * Redistributions of source code must retain the above copyright
  8. * notice, this list of conditions and the following disclaimer.
  9. * * Redistributions in binary form must reproduce the above copyright
  10. * notice, this list of conditions and the following disclaimer in the
  11. * documentation and/or other materials provided with the distribution.
  12. * * Neither the name of the OpenSimulator Project nor the
  13. * names of its contributors may be used to endorse or promote products
  14. * derived from this software without specific prior written permission.
  15. *
  16. * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
  17. * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
  18. * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
  19. * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
  20. * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
  21. * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
  22. * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
  23. * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  24. * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
  25. * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  26. */
  27. using System;
  28. using System.Data;
  29. using Mono.Data.SqliteClient;
  30. namespace OpenSim.Data.SQLiteLegacy
  31. {
  32. /// <summary>
  33. /// A base class for methods needed by all SQLite database classes
  34. /// </summary>
  35. public class SQLiteUtil
  36. {
  37. /***********************************************************************
  38. *
  39. * Database Definition Helper Functions
  40. *
  41. * This should be db agnostic as we define them in ADO.NET terms
  42. *
  43. **********************************************************************/
  44. /// <summary>
  45. ///
  46. /// </summary>
  47. /// <param name="dt"></param>
  48. /// <param name="name"></param>
  49. /// <param name="type"></param>
  50. public static void createCol(DataTable dt, string name, Type type)
  51. {
  52. DataColumn col = new DataColumn(name, type);
  53. dt.Columns.Add(col);
  54. }
  55. /***********************************************************************
  56. *
  57. * SQL Statement Creation Functions
  58. *
  59. * These functions create SQL statements for update, insert, and create.
  60. * They can probably be factored later to have a db independant
  61. * portion and a db specific portion
  62. *
  63. **********************************************************************/
  64. /// <summary>
  65. /// Create an insert command
  66. /// </summary>
  67. /// <param name="table">table name</param>
  68. /// <param name="dt">data table</param>
  69. /// <returns>the created command</returns>
  70. /// <remarks>
  71. /// This is subtle enough to deserve some commentary.
  72. /// Instead of doing *lots* and *lots of hardcoded strings
  73. /// for database definitions we'll use the fact that
  74. /// realistically all insert statements look like "insert
  75. /// into A(b, c) values(:b, :c) on the parameterized query
  76. /// front. If we just have a list of b, c, etc... we can
  77. /// generate these strings instead of typing them out.
  78. /// </remarks>
  79. public static SqliteCommand createInsertCommand(string table, DataTable dt)
  80. {
  81. string[] cols = new string[dt.Columns.Count];
  82. for (int i = 0; i < dt.Columns.Count; i++)
  83. {
  84. DataColumn col = dt.Columns[i];
  85. cols[i] = col.ColumnName;
  86. }
  87. string sql = "insert into " + table + "(";
  88. sql += String.Join(", ", cols);
  89. // important, the first ':' needs to be here, the rest get added in the join
  90. sql += ") values (:";
  91. sql += String.Join(", :", cols);
  92. sql += ")";
  93. SqliteCommand cmd = new SqliteCommand(sql);
  94. // this provides the binding for all our parameters, so
  95. // much less code than it used to be
  96. foreach (DataColumn col in dt.Columns)
  97. {
  98. cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
  99. }
  100. return cmd;
  101. }
  102. /// <summary>
  103. /// create an update command
  104. /// </summary>
  105. /// <param name="table">table name</param>
  106. /// <param name="pk"></param>
  107. /// <param name="dt"></param>
  108. /// <returns>the created command</returns>
  109. public static SqliteCommand createUpdateCommand(string table, string pk, DataTable dt)
  110. {
  111. string sql = "update " + table + " set ";
  112. string subsql = String.Empty;
  113. foreach (DataColumn col in dt.Columns)
  114. {
  115. if (subsql.Length > 0)
  116. {
  117. // a map function would rock so much here
  118. subsql += ", ";
  119. }
  120. subsql += col.ColumnName + "= :" + col.ColumnName;
  121. }
  122. sql += subsql;
  123. sql += " where " + pk;
  124. SqliteCommand cmd = new SqliteCommand(sql);
  125. // this provides the binding for all our parameters, so
  126. // much less code than it used to be
  127. foreach (DataColumn col in dt.Columns)
  128. {
  129. cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
  130. }
  131. return cmd;
  132. }
  133. /// <summary>
  134. ///
  135. /// </summary>
  136. /// <param name="dt">Data Table</param>
  137. /// <returns></returns>
  138. public static string defineTable(DataTable dt)
  139. {
  140. string sql = "create table " + dt.TableName + "(";
  141. string subsql = String.Empty;
  142. foreach (DataColumn col in dt.Columns)
  143. {
  144. if (subsql.Length > 0)
  145. {
  146. // a map function would rock so much here
  147. subsql += ",\n";
  148. }
  149. subsql += col.ColumnName + " " + sqliteType(col.DataType);
  150. if (dt.PrimaryKey.Length > 0)
  151. {
  152. if (col == dt.PrimaryKey[0])
  153. {
  154. subsql += " primary key";
  155. }
  156. }
  157. }
  158. sql += subsql;
  159. sql += ")";
  160. return sql;
  161. }
  162. /***********************************************************************
  163. *
  164. * Database Binding functions
  165. *
  166. * These will be db specific due to typing, and minor differences
  167. * in databases.
  168. *
  169. **********************************************************************/
  170. ///<summary>
  171. /// <para>
  172. /// This is a convenience function that collapses 5 repetitive
  173. /// lines for defining SqliteParameters to 2 parameters:
  174. /// column name and database type.
  175. /// </para>
  176. ///
  177. /// <para>
  178. /// It assumes certain conventions like :param as the param
  179. /// name to replace in parametrized queries, and that source
  180. /// version is always current version, both of which are fine
  181. /// for us.
  182. /// </para>
  183. ///</summary>
  184. /// <param name="name"></param>
  185. /// <param name="type"></param>
  186. ///<returns>a built sqlite parameter</returns>
  187. public static SqliteParameter createSqliteParameter(string name, Type type)
  188. {
  189. SqliteParameter param = new SqliteParameter();
  190. param.ParameterName = ":" + name;
  191. param.DbType = dbtypeFromType(type);
  192. param.SourceColumn = name;
  193. param.SourceVersion = DataRowVersion.Current;
  194. return param;
  195. }
  196. /***********************************************************************
  197. *
  198. * Type conversion functions
  199. *
  200. **********************************************************************/
  201. /// <summary>
  202. /// Type conversion function
  203. /// </summary>
  204. /// <param name="type">a type</param>
  205. /// <returns>a DbType</returns>
  206. public static DbType dbtypeFromType(Type type)
  207. {
  208. if (type == typeof (String))
  209. {
  210. return DbType.String;
  211. }
  212. else if (type == typeof (Int32))
  213. {
  214. return DbType.Int32;
  215. }
  216. else if (type == typeof (UInt32))
  217. {
  218. return DbType.UInt32;
  219. }
  220. else if (type == typeof (Int64))
  221. {
  222. return DbType.Int64;
  223. }
  224. else if (type == typeof (UInt64))
  225. {
  226. return DbType.UInt64;
  227. }
  228. else if (type == typeof (Double))
  229. {
  230. return DbType.Double;
  231. }
  232. else if (type == typeof (Boolean))
  233. {
  234. return DbType.Boolean;
  235. }
  236. else if (type == typeof (Byte[]))
  237. {
  238. return DbType.Binary;
  239. }
  240. else
  241. {
  242. return DbType.String;
  243. }
  244. }
  245. /// <summary>
  246. /// </summary>
  247. /// <param name="type">a Type</param>
  248. /// <returns>a string</returns>
  249. /// <remarks>this is something we'll need to implement for each db slightly differently.</remarks>
  250. public static string sqliteType(Type type)
  251. {
  252. if (type == typeof (String))
  253. {
  254. return "varchar(255)";
  255. }
  256. else if (type == typeof (Int32))
  257. {
  258. return "integer";
  259. }
  260. else if (type == typeof (UInt32))
  261. {
  262. return "integer";
  263. }
  264. else if (type == typeof (Int64))
  265. {
  266. return "varchar(255)";
  267. }
  268. else if (type == typeof (UInt64))
  269. {
  270. return "varchar(255)";
  271. }
  272. else if (type == typeof (Double))
  273. {
  274. return "float";
  275. }
  276. else if (type == typeof (Boolean))
  277. {
  278. return "integer";
  279. }
  280. else if (type == typeof (Byte[]))
  281. {
  282. return "blob";
  283. }
  284. else
  285. {
  286. return "string";
  287. }
  288. }
  289. }
  290. }