00001
00002 using System;
00003 using System.Text;
00004 using System.Data.Common;
00005 using System.Collections.Generic;
00006 using System.Data.SqlTypes;
00007 using Foodolini.Database.Sql;
00008
00009 namespace Foodolini.Database.Sql
00010 {
00014 public class Sqlite : SqlStrategy
00015 {
00016
00023 public override string TableQuery(){
00024 return "SELECT name FROM sqlite_master WHERE type = 'table'";
00025 }
00026
00030 public override string CreateTableQuery(string table, IDictionary<string, Type> columns, string primaryKey){
00031 StringBuilder sql = new StringBuilder();
00032 sql.AppendFormat("CREATE TABLE {0} ({1} INTEGER PRIMARY KEY, ", table, primaryKey);
00033 foreach(KeyValuePair<string, Type> col in columns){
00034 sql.AppendFormat("{0} {1}, ", col.Key, this.GetTypeAffinity(col.Value));
00035 }
00036 sql.Remove(sql.Length - 2, 2);
00037 sql.Append(")");
00038 return sql.ToString();
00039 }
00040
00053 private string GetTypeAffinity(Type type){
00054 if(type == typeof(string))
00055 return "TEXT";
00056 if(type == typeof(long) ||
00057 type == typeof(int) ||
00058 type == typeof(Enum))
00059 return "INTEGER NOT NULL";
00060 if(type == typeof(long?) ||
00061 type == typeof(int?))
00062 return "INTEGER";
00063
00064 if(type == typeof(byte[]))
00065 return "BLOB";
00066 if(type == typeof(double))
00067 return "FLOAT NOT NULL";
00068 if(type == typeof(double?))
00069 return "FLOAT";
00070 if(type == typeof(float))
00071 return "REAL NOT NULL";
00072 if(type == typeof(float?))
00073 return "REAL";
00074 if(type == typeof(DateTime))
00075 return "DATETIME NOT NULL";
00076 if(type == typeof(DateTime?))
00077 return "DATETIME";
00078 if(type == typeof(TimeSpan))
00079 return "INTEGER NOT NULL";
00080 if(type == typeof(TimeSpan?))
00081 return "INTEGER";
00082 if(type == typeof(bool))
00083 return "BIT NOT NULL";
00084 if(type == typeof(bool?))
00085 return "BIT";
00086 throw new SqlTypeException("Type of " + type.ToString() + " cannot be saved to an Sqlite database");
00087 }
00088
00092 public override string InsertQuery(string table, ICollection<string> parameters){
00093 StringBuilder sql = new StringBuilder();
00094 sql.AppendFormat("INSERT INTO {0} (", table);
00095 StringBuilder param = new StringBuilder();
00096 foreach(string p in parameters){
00097 sql.AppendFormat("{0}, ", p);
00098 param.AppendFormat("@{0}, ", p);
00099 }
00100 sql.Remove(sql.Length - 2, 2);
00101 param.Remove(param.Length - 2, 2);
00102 sql.AppendFormat(") VALUES ({0}); SELECT last_insert_rowid()", param);
00103 return sql.ToString();
00104 }
00105
00109 public override string UpdateQuery(string table, ICollection<string> parameters, string identifier){
00110 StringBuilder sql = new StringBuilder();
00111 sql.AppendFormat("UPDATE {0} SET ", table);
00112 foreach(string p in parameters){
00113 sql.AppendFormat("{0}=@{0}, ", p);
00114 }
00115 sql.Remove(sql.Length - 2, 2);
00116 sql.AppendFormat(" WHERE {0}=@{0}", identifier);
00117 return sql.ToString();
00118 }
00119
00123 public override string WhereQuery(string table, string condition){
00124 StringBuilder sql = new StringBuilder();
00125 sql.AppendFormat("SELECT * FROM {0} WHERE {1}", table, condition);
00126 return sql.ToString();
00127 }
00128
00132 public override string PagedWhereQuery(string table, string condition, int page, int pageSize){
00133 StringBuilder sql = new StringBuilder();
00134 sql.AppendFormat("SELECT * FROM {0} WHERE {1} LIMIT {2} OFFSET {3}", table, condition, pageSize, page * pageSize);
00135 return sql.ToString();
00136 }
00137
00141 public override string AllQuery(string table){
00142 StringBuilder sql = new StringBuilder();
00143 sql.AppendFormat("SELECT * FROM {0}", table);
00144 return sql.ToString();
00145 }
00146
00150 public override string PagedQuery(string table, int page, int pageSize){
00151 StringBuilder sql = new StringBuilder();
00152 sql.AppendFormat("SELECT * FROM {0} LIMIT {1} OFFSET {2}", table, pageSize, page * pageSize);
00153 return sql.ToString();
00154 }
00155
00159 public override string DeleteQuery(string table, string identifier){
00160 StringBuilder sql = new StringBuilder();
00161 sql.AppendFormat("DELETE FROM {0} WHERE {1}=@{1}", table, identifier);
00162 return sql.ToString();
00163 }
00164
00177 public override string DeleteWhereQuery(string table, string condition){
00178 return string.Format("DELETE FROM {0} WHERE {1}", table, condition);
00179 }
00180 }
00181 }