public SyncItem CreateSyncItem(SyncItem newItem)
{
using(SqliteConnection con = _provider.GetConnection())
{
con.Open();
using (SqliteCommand cmd = new SqliteCommand(con))
{
cmd.CommandText = string.Format(
@"INSERT INTO {0}(
Id,
FileKey,
CloudAssetId,
CloudLastUpdatedDate,
CloudCreatedDate,
IsDeleted,
Size)
VALUES(
@Id,
@FileKey,
@CloudAssetId,
@CloudLastUpdatedDate,
@CloudCreatedDate,
@IsDeleted,
@Size)", SyncItem.TableName);
cmd.Prepare();
cmd.Parameters.AddWithValue("@Id", newItem.Id);
cmd.Parameters.AddWithValue("@FileKey", newItem.FileKey);
cmd.Parameters.AddWithValue("@CloudAssetId", newItem.CloudAssetId);
cmd.Parameters.AddWithValue("@CloudLastUpdatedDate", newItem.CloudLastUpdated.Ticks);
cmd.Parameters.AddWithValue("@CloudCreatedDate", newItem.CloudCreated.Ticks);
cmd.Parameters.AddWithValue("@IsDeleted", newItem.IsDeleted);
cmd.Parameters.AddWithValue("@Size", newItem.Size);
cmd.ExecuteNonQuery();
}
con.Close();
}
return newItem;
}
//public static Dictionary<string, SpeciesData> spNameUpperList = new Dictionary<string, SpeciesData>();
public static void Initialize()
{
SqliteConnection con = new SqliteConnection("URI=file:" + Application.dataPath + "/Database/WoB_DB.db");
con.Open();
SqliteCommand cmd = new SqliteCommand(con);
cmd.CommandText = "" +
"SELECT *" +
" FROM `species`";
cmd.Prepare();
cmd.ExecuteNonQuery();
SqliteDataReader reader = cmd.ExecuteReader();
while (reader.Read()) {
int species_id = reader.GetInt32(0);
SpeciesData species = new SpeciesData(species_id);
species.name = reader.GetString(1);
species.description = reader.GetString(2);
species.biomass = reader.GetInt32(3);
species.diet_type = reader.GetInt16(4);
species.trophic_level = reader.GetFloat(5);
species.level = reader.GetInt16(6);
SqliteCommand subcmd = new SqliteCommand(con);
subcmd.CommandText = "" +
"SELECT `predator_id`" +
" FROM `pp_relations`" +
" WHERE `prey_id` = @species_id";
subcmd.Parameters.Add(new SqliteParameter("@species_id", species.species_id));
subcmd.Prepare();
subcmd.ExecuteNonQuery();
SqliteDataReader subreader = subcmd.ExecuteReader();
while (subreader.Read()) {
int predator_id = subreader.GetInt32(0);
if (!species.predatorList.ContainsKey(predator_id)) {
species.predatorList.Add(predator_id, "");
}
}
subcmd = new SqliteCommand(con);
subcmd.CommandText = "" +
"SELECT `prey_id`" +
" FROM `pp_relations`" +
" WHERE `predator_id` = @species_id";
subcmd.Parameters.Add(new SqliteParameter("@species_id", species.species_id));
subcmd.Prepare();
subcmd.ExecuteNonQuery();
subreader = subcmd.ExecuteReader();
while (subreader.Read()) {
int prey_id = subreader.GetInt32(0);
if (!species.preyList.ContainsKey(prey_id)) {
species.preyList.Add(subreader.GetInt32(0), "");
}
}
species.name = Functions.NormalizeSpeciesName (species.name);
speciesList.Add(species.species_id, species);
spNameList.Add(species.name, species);
//spNameUpperList.Add(species.name.ToUpper (), species);
}
foreach (SpeciesData species in speciesList.Values) {
foreach (int predator_id in new List<int>(species.predatorList.Keys)) {
if (SpeciesTable.speciesList.ContainsKey(predator_id)) {
species.predatorList[predator_id] = SpeciesTable.speciesList[predator_id].name;
} else {
species.predatorList.Remove(predator_id);
}
}
foreach (int prey_id in new List<int>(species.preyList.Keys)) {
if (SpeciesTable.speciesList.ContainsKey(prey_id)) {
species.preyList[prey_id] = SpeciesTable.speciesList[prey_id].name;
} else {
species.preyList.Remove(prey_id);
}
}
}
reader.Close();
con.Close();
}
public static void Update(Dictionary<int, SpeciesData> updateList)
{
SqliteConnection con = new SqliteConnection("URI=file:" + Application.dataPath + "/Database/WoB_DB.db");
con.Open();
SqliteCommand cmd = new SqliteCommand(con);
cmd.CommandText = "" +
"DELETE FROM `pp_relations`" +
" WHERE `predator_id` > 0 OR `prey_id` > 0";
cmd.Prepare();
cmd.ExecuteNonQuery();
foreach (KeyValuePair<int, SpeciesData> entry in updateList) {
int species_id = entry.Key;
SpeciesData species = entry.Value;
if (speciesList.ContainsKey(species_id)) { // If Exists, Delete Record
cmd.CommandText = "" +
"DELETE FROM `species`" +
" WHERE `species_id` = @species_id";
cmd.Parameters.Add(new SqliteParameter("@species_id", species.species_id));
cmd.Prepare();
cmd.ExecuteNonQuery();
}
cmd.CommandText = "" +
"INSERT INTO `species` (`species_id`, `name`, `description`, `biomass`, `diet_type`, `trophic_level`, 'level')" +
" VALUES (@species_id, @name, @description, @biomass, @diet_type, @trophic_level, @level)";
cmd.Parameters.Add(new SqliteParameter("@species_id", species.species_id));
cmd.Parameters.Add(new SqliteParameter("@name", species.name));
cmd.Parameters.Add(new SqliteParameter("@description", species.description));
cmd.Parameters.Add(new SqliteParameter("@biomass", species.biomass));
cmd.Parameters.Add(new SqliteParameter("@diet_type", species.diet_type));
cmd.Parameters.Add(new SqliteParameter("@trophic_level", species.trophic_level));
cmd.Parameters.Add(new SqliteParameter("@level", species.level));
cmd.Prepare();
cmd.ExecuteNonQuery();
foreach (int predator_id in species.predatorList.Keys) {
cmd.CommandText = "" +
"INSERT INTO `pp_relations` (`predator_id`, `prey_id`)" +
" VALUES (@predator_id, @prey_id)";
cmd.Parameters.Add(new SqliteParameter("@predator_id", predator_id));
cmd.Parameters.Add(new SqliteParameter("@prey_id", species.species_id));
cmd.Prepare();
cmd.ExecuteNonQuery();
}
foreach (int prey_id in species.preyList.Keys) {
cmd.CommandText = "" +
"INSERT INTO `pp_relations` (`predator_id`, `prey_id`)" +
" VALUES (@predator_id, @prey_id)";
cmd.Parameters.Add(new SqliteParameter("@predator_id", species.species_id));
cmd.Parameters.Add(new SqliteParameter("@prey_id", prey_id));
cmd.Prepare();
cmd.ExecuteNonQuery();
}
}
con.Close();
}
public IEnumerable<IEntity> Select(EntityType type, bool useView, String where, params object[] arguments)
{
string[] columns = GetColumns(type);
int n;
String cmdKey = type.Name + where + useView;
SqliteCommand cmd;
if (!_commands.TryGetValue(cmdKey, out cmd))
{
String tableName = type.TableName;
String columnNames = "";
foreach (string column in columns)
columnNames = columnNames + String.Format("{0}{1}", String.IsNullOrEmpty(columnNames) ? "" : ",", column);
if (!useView)
columnNames = columnNames + ",IsTombstone";
columnNames = columnNames + ",IsDirty";
cmd = new SqliteCommand
{
Connection = ActiveConnection,
CommandText =
String.Format("SELECT {0} FROM [{1}]{2}", columnNames, useView ? tableName : "_" + tableName,
String.IsNullOrEmpty(@where) ? "" : " " + @where)
};
n = 1;
foreach (object p in arguments)
{
cmd.Parameters.AddWithValue(String.Format("@p{0}", n), p);
n++;
}
cmd.Prepare();
_commands.Add(cmdKey, cmd);
}
else
{
for (int i = 0; i < arguments.Length; i++)
{
cmd.Parameters[i].Value = arguments[i];
}
}
var list = new List<IEntity>();
using (SqliteDataReader r = cmd.ExecuteReader())
{
if (r.HasRows)
{
while (r.Read())
{
IEntity entity = EntityFactory.CreateInstance(type);
n = 0;
foreach (string column in columns)
{
Type t = type.GetPropertyType(column);
if (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>))
t = Nullable.GetUnderlyingType(t);
object value = r[n];
if (value.GetType().Equals(typeof(DBNull)))
value = null;
else
{
if (t.Equals(typeof(IDbRef)))
value = DbRef.FromString(value.ToString());
else
{
if (t == typeof(Guid))
{
Guid g;
if (!Guid.TryParse(value.ToString(), out g))
{
if (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>))
value = null;
else
throw new ArgumentException(String.Format("Can't convert value '{0}' to System.Guid", value));
}
else
value = g;
}
else
value = Convert.ChangeType(value, t);
}
}
entity.SetValue(column, value);
n++;
}
bool isTombstone = !useView && (long)r["IsTombstone"] != 0;
(entity as ISqliteEntity).IsTombstone = isTombstone;
(entity as ISqliteEntity).Load((long)r["IsDirty"] != 0);
list.Add(entity);
}
}
}
return list;
}
protected void PutParameters(SqliteCommand cmd, object[] args)
{
for(int i = 0; i < args.Length; i++)
{
string p_name = "@p" + i;
SqliteParameter p = new SqliteParameter(p_name, args[i]);
cmd.Parameters.Add(p);
cmd.Prepare();
}
}
public SyncItem UpdateSyncItem(SyncItem updatedItem)
{
using(SqliteConnection con = _provider.GetConnection())
{
con.Open();
using (SqliteCommand cmd = new SqliteCommand(con))
{
cmd.CommandText = string.Format(@"UPDATE {0} SET
FileKey = @FileKey,
CloudAssetId = @CloudAssetId,
CloudLastUpdatedDate = @CloudLastUpdatedDate,
CloudCreatedDate = @CloudCreatedDate,
IsDeleted = @IsDeleted,
Size = @Size WHERE
Id = {1}", SyncItem.TableName, updatedItem.Id);
cmd.Prepare();
cmd.Parameters.AddWithValue("@FileKey", updatedItem.FileKey);
cmd.Parameters.AddWithValue("@CloudAssetId", updatedItem.CloudAssetId);
cmd.Parameters.AddWithValue("@CloudLastUpdatedDate", updatedItem.CloudLastUpdated.Ticks);
cmd.Parameters.AddWithValue("@CloudCreatedDate", updatedItem.CloudCreated.Ticks);
cmd.Parameters.AddWithValue("@IsDeleted", updatedItem.IsDeleted);
cmd.Parameters.AddWithValue("@Size", updatedItem.Size);
cmd.ExecuteNonQuery();
}
con.Close();
}
return updatedItem;
}
private void InsertDummyData(SyncItem dummyData, SqliteCommand cmd)
{
cmd.CommandText = string.Format(
@"INSERT INTO {0}(
FileKey,
CloudAssetId,
CloudLastUpdatedDate,
CloudCreatedDate,
IsDeleted,
Size)
VALUES(
@FileKey,
@CloudAssetId,
@CloudLastUpdated,
@CloudCreated,
@IsDeleted,
@Size)", SyncItem.TableName);
cmd.Prepare();
cmd.Parameters.AddWithValue("@FileKey", dummyData.FileKey);
cmd.Parameters.AddWithValue("@CloudAssetId", dummyData.CloudAssetId);
cmd.Parameters.AddWithValue("@CloudLastUpdated", dummyData.CloudLastUpdated.Ticks);
cmd.Parameters.AddWithValue("@CloudCreated", dummyData.CloudCreated.Ticks);
cmd.Parameters.AddWithValue("@IsDeleted", dummyData.IsDeleted);
cmd.Parameters.AddWithValue("@Size", dummyData.Size);
cmd.ExecuteNonQuery();
}
