SQLiteUtils.cs 9.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269
  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 OpenSim 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.Framework.Data.SQLite
  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. public static void createCol(DataTable dt, string name, Type type)
  45. {
  46. DataColumn col = new DataColumn(name, type);
  47. dt.Columns.Add(col);
  48. }
  49. /***********************************************************************
  50. *
  51. * SQL Statement Creation Functions
  52. *
  53. * These functions create SQL statements for update, insert, and create.
  54. * They can probably be factored later to have a db independant
  55. * portion and a db specific portion
  56. *
  57. **********************************************************************/
  58. public static SqliteCommand createInsertCommand(string table, DataTable dt)
  59. {
  60. /**
  61. * This is subtle enough to deserve some commentary.
  62. * Instead of doing *lots* and *lots of hardcoded strings
  63. * for database definitions we'll use the fact that
  64. * realistically all insert statements look like "insert
  65. * into A(b, c) values(:b, :c) on the parameterized query
  66. * front. If we just have a list of b, c, etc... we can
  67. * generate these strings instead of typing them out.
  68. */
  69. string[] cols = new string[dt.Columns.Count];
  70. for (int i = 0; i < dt.Columns.Count; i++)
  71. {
  72. DataColumn col = dt.Columns[i];
  73. cols[i] = col.ColumnName;
  74. }
  75. string sql = "insert into " + table + "(";
  76. sql += String.Join(", ", cols);
  77. // important, the first ':' needs to be here, the rest get added in the join
  78. sql += ") values (:";
  79. sql += String.Join(", :", cols);
  80. sql += ")";
  81. SqliteCommand cmd = new SqliteCommand(sql);
  82. // this provides the binding for all our parameters, so
  83. // much less code than it used to be
  84. foreach (DataColumn col in dt.Columns)
  85. {
  86. cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
  87. }
  88. return cmd;
  89. }
  90. public static SqliteCommand createUpdateCommand(string table, string pk, DataTable dt)
  91. {
  92. string sql = "update " + table + " set ";
  93. string subsql = String.Empty;
  94. foreach (DataColumn col in dt.Columns)
  95. {
  96. if (subsql.Length > 0)
  97. {
  98. // a map function would rock so much here
  99. subsql += ", ";
  100. }
  101. subsql += col.ColumnName + "= :" + col.ColumnName;
  102. }
  103. sql += subsql;
  104. sql += " where " + pk;
  105. SqliteCommand cmd = new SqliteCommand(sql);
  106. // this provides the binding for all our parameters, so
  107. // much less code than it used to be
  108. foreach (DataColumn col in dt.Columns)
  109. {
  110. cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
  111. }
  112. return cmd;
  113. }
  114. public static string defineTable(DataTable dt)
  115. {
  116. string sql = "create table " + dt.TableName + "(";
  117. string subsql = String.Empty;
  118. foreach (DataColumn col in dt.Columns)
  119. {
  120. if (subsql.Length > 0)
  121. {
  122. // a map function would rock so much here
  123. subsql += ",\n";
  124. }
  125. subsql += col.ColumnName + " " + sqliteType(col.DataType);
  126. if (dt.PrimaryKey.Length > 0)
  127. {
  128. if (col == dt.PrimaryKey[0])
  129. {
  130. subsql += " primary key";
  131. }
  132. }
  133. }
  134. sql += subsql;
  135. sql += ")";
  136. return sql;
  137. }
  138. /***********************************************************************
  139. *
  140. * Database Binding functions
  141. *
  142. * These will be db specific due to typing, and minor differences
  143. * in databases.
  144. *
  145. **********************************************************************/
  146. ///<summary>
  147. /// This is a convenience function that collapses 5 repetitive
  148. /// lines for defining SqliteParameters to 2 parameters:
  149. /// column name and database type.
  150. ///
  151. /// It assumes certain conventions like :param as the param
  152. /// name to replace in parametrized queries, and that source
  153. /// version is always current version, both of which are fine
  154. /// for us.
  155. ///</summary>
  156. ///<returns>a built sqlite parameter</returns>
  157. public static SqliteParameter createSqliteParameter(string name, Type type)
  158. {
  159. SqliteParameter param = new SqliteParameter();
  160. param.ParameterName = ":" + name;
  161. param.DbType = dbtypeFromType(type);
  162. param.SourceColumn = name;
  163. param.SourceVersion = DataRowVersion.Current;
  164. return param;
  165. }
  166. /***********************************************************************
  167. *
  168. * Type conversion functions
  169. *
  170. **********************************************************************/
  171. public static DbType dbtypeFromType(Type type)
  172. {
  173. if (type == typeof (String))
  174. {
  175. return DbType.String;
  176. }
  177. else if (type == typeof (Int32))
  178. {
  179. return DbType.Int32;
  180. }
  181. else if (type == typeof (UInt32))
  182. {
  183. return DbType.UInt32;
  184. }
  185. else if (type == typeof (Int64))
  186. {
  187. return DbType.Int64;
  188. }
  189. else if (type == typeof (UInt64))
  190. {
  191. return DbType.UInt64;
  192. }
  193. else if (type == typeof (Double))
  194. {
  195. return DbType.Double;
  196. }
  197. else if (type == typeof (Boolean))
  198. {
  199. return DbType.Boolean;
  200. }
  201. else if (type == typeof (Byte[]))
  202. {
  203. return DbType.Binary;
  204. }
  205. else
  206. {
  207. return DbType.String;
  208. }
  209. }
  210. // this is something we'll need to implement for each db
  211. // slightly differently.
  212. public static string sqliteType(Type type)
  213. {
  214. if (type == typeof (String))
  215. {
  216. return "varchar(255)";
  217. }
  218. else if (type == typeof (Int32))
  219. {
  220. return "integer";
  221. }
  222. else if (type == typeof (UInt32))
  223. {
  224. return "integer";
  225. }
  226. else if (type == typeof (Int64))
  227. {
  228. return "varchar(255)";
  229. }
  230. else if (type == typeof (UInt64))
  231. {
  232. return "varchar(255)";
  233. }
  234. else if (type == typeof (Double))
  235. {
  236. return "float";
  237. }
  238. else if (type == typeof (Boolean))
  239. {
  240. return "integer";
  241. }
  242. else if (type == typeof (Byte[]))
  243. {
  244. return "blob";
  245. }
  246. else
  247. {
  248. return "string";
  249. }
  250. }
  251. }
  252. }