public static List<Guid> GetGuidListStatic(string sqlStatement) {
DBConnect conn = new DBConnect();
conn.Open();
List<Guid> list = conn.GetGuidList(sqlStatement);
conn.Close();
return list;
}
public SQL_XML_IO_statuses CreateInitialDatapack()
{
EFDbContext.SaveChgs();
RefTree.Clear();
string path = FileService.GetMainDataDirectory()+"Resources\\InitialData.xml";
XmlWriter xmlWriter = XmlWriter.Create(path,new XmlWriterSettings() {Encoding = Encoding.UTF8 });
DBConnect dbconnect = new DBConnect();
dbconnect.Open();
xmlWriter.WriteStartDocument();
xmlWriter.WriteStartElement(SQL_XML_IOSystem.StartElementName);
xmlWriter.WriteAttributeString("version", Data_Versions.version_1_1.ToString());
xmlWriter.WriteAttributeString("MainElement_Guid", Guid.NewGuid().ToString());
xmlWriter.WriteAttributeString("MainElement_Name", "DataPack");
logging = true;
PathList = new List<string>();
//
schema.ClearRules();
schema.GetTableByName(Language.TableName).DontProcessingBackwardReferences = true;
//schema.GetTableByName(Language.TableName).DontProcessingField("FrequencyDictionary_id");
//schema.GetTableByName(Dictionary.TableName).DontProcessing = true;
//schema.GetTableByName(ProcessingWordsRules.TableName).DontProcessing = true;
//schema.GetTableByName(Subtitles.TableName).DontProcessing = true;
// upload the languages
foreach(Language lang in ViewModelLocator.Instance.Languages)
{
SaveTable(xmlWriter,dbconnect,Language.TableName,lang.id);
}
// upload the LearningWordStrategy
//schema.GetTableByName(LearningWordStrategy.TableName).GetFieldByName("")
schema.GetTableByName(AppSetting.TableName).DontProcessing = true;
schema.GetTableByName(LearningItem.TableName).DontProcessing = true;
schema.GetTableByName(SubtitleItem.TableName).DontProcessing = true;
foreach (LearningWordStrategy lws in EFDbContext.DataBase.LearningWordStrategy)
{
SaveTable(xmlWriter, dbconnect, LearningWordStrategy.TableName, lws.id);
}
xmlWriter.WriteEndDocument();
xmlWriter.Close();
// Save the
dbconnect.Close();
return SQL_XML_IO_statuses.Ok;
}
public SQL_XML_IO_statuses DeleteLearningItem(LearningItem learningitem)
{
if (learningitem == null) return SQL_XML_IO_statuses.OtherError;
RefTree.Clear();
DBConnect dbconnect = new DBConnect();
dbconnect.Open();
//logging = true;
//FindAllReferences_("LearningItems",learningitem.id);
if(ViewModelLocator.Instance.AppSetting.CurrentLearningItem.id == learningitem.id)
{
ViewModelLocator.Instance.AppSetting.CurrentLearningItem = null;
}
EFDbContext.SaveChgs();
// Rules
schema.ClearRules();
schema.GetTableByName(AppSetting.TableName).DontProcessing = true;
schema.GetTableByName(Dictionary.TableName).DontProcessing = true;
schema.GetTableByName(Language.TableName).DontProcessing = true;
schema.GetTableByName(WordOfDictionary.TableName).DontProcessing = true;
// optimization for speed
DBConnect.Query query = new DBConnect.Query();
query.sql = SQLqueries.Delete_Update_Subtitle_Items_Words;
query.AddParameter("id", learningitem.id);
dbconnect.ExecuteSQL(query);
query.sql = SQLqueries.Delete_Subtitile_Items_Words;
query.AddParameter("id", learningitem.id);
dbconnect.ExecuteSQL(query);
query.sql = SQLqueries.Delete_Subtitle_Items;
query.AddParameter("id", learningitem.id);
dbconnect.ExecuteSQL(query);
PathList.Clear();
DeleteTable(dbconnect,"LearningItems",learningitem.id,true);
dbconnect.Close();
((IObjectContextAdapter)ViewModelLocator.Instance.DataBase).ObjectContext.Detach(learningitem);
EFDbContext.SaveChgs();
return SQL_XML_IO_statuses.Ok;
}
public SQL_XML_IO_statuses Load(string path,out Guid guid)
{
RefTree.Clear();
guid = Guid.Empty;
FakeElements = new List<Ref>();
if (!FileService.FileExists(path)) return SQL_XML_IO_statuses.NoFile;
DBConnect dbconnect = new DBConnect();
dbconnect.Open();
using (XmlReader reader = XmlReader.Create(path))
{
int MaxElements = 1;
XML_Element element;
List<XML_Element> xml_elements = new List<XML_Element>();
element = ReadElement(reader,Data_Versions.version_1_0,"xml");
element = ReadElement(reader,Data_Versions.version_1_0,StartElementName);
guid = new Guid(element.GetAttibute("MainElement_Guid"));
string version_string = element.GetAttibute("version");
if (version_string.Contains(".")) version_string = version_string.Replace(".0","");
short VersionOfData = short.Parse(version_string);
int Counter = 0;
while (true)
{
element = ReadElement(reader,VersionOfData);
if (element.Name == StartElementName) break;
AddToRefList(element.Name, Guid.Parse(element.guid), "", true);
xml_elements.Add(element);
Counter++;
if(Counter == MaxElements)
{
LoadElements(dbconnect,xml_elements,VersionOfData);
xml_elements.Clear();
Counter = 0;
}
}
LoadElements(dbconnect,xml_elements,VersionOfData);
}
if (FakeElements.Count > 0)
{
foreach (var elm in FakeElements)
{
dbconnect.ExecuteSQL("DELETE FROM ["+elm.tablename+"] WHERE id = '"+elm.guid+"'");
}
}
dbconnect.Close();
return SQL_XML_IO_statuses.Ok;
}
public static bool ObjectExistStatic(string table_name,Guid id)
{
DBConnect conn = new DBConnect();
conn.Open();
bool res = conn.ObjectExist(table_name, id);
conn.Close();
return res;
}
private string GetFakeValue(DBConnect dbconnect,Table tbl, Field field,short version_of_data)
{
switch (field.type)
{
case DBTypes.String:
return "";
case DBTypes.Datetime:
return DateTime.Now.ToString(CultureInfo.InvariantCulture);
case DBTypes.Integer:
case DBTypes.Boolean:
case DBTypes.Double:
return "0";
case DBTypes.Guid:
ForeignKey key = schema.ForeignKey.FirstOrDefault(x => x.Table_str == tbl.Name && x.field_str == field.Name);
if (key == null) throw new ArgumentNullException(nameof(key));
Guid id = Guid.Empty;
var felm = FakeElements.FirstOrDefault(x=> x.tablename == key.PrimaryTable_str);
if (felm != null)
{
id = felm.guid;
}
else
{
string flds = "", values="";
foreach (var fld in key.PrimaryTable.Fields)
{
if (fld.Value.IsNullable) continue;
string value = "";
if (fld.Key == "id")
{
id = Guid.NewGuid();
value = id.ToString();
} else
{
value = GetFakeValue(dbconnect, key.PrimaryTable, fld.Value, version_of_data);
}
flds += (flds == "" ? "":",")+"["+fld.Key+"]"+Environment.NewLine;
values += (values==""?"":",") + FormatValueForSQL(fld.Value,value,version_of_data);
}
string sql = "INSERT INTO ["+key.PrimaryTable_str+"] ("+flds+" ) VALUES ("+values+")";
dbconnect.ExecuteSQL(sql);
FakeElements.Add(new Ref {guid=id,tablename = key.PrimaryTable_str});
}
return id.ToString();
default:
throw new NotImplementedException();
}
}
private void LoadElements(DBConnect dbconnect,List<XML_Element> xml_elements,short version_of_data)
{
if (xml_elements.Count == 0) return;
// aggregates set of xml_elements, groups it by tables
Dictionary<string, List<string>> table_aggregator = new Dictionary<string, List<string>>();
foreach(XML_Element elm in xml_elements) {
if (elm.Name == "Update") continue;
List<string> cur_table;
if(!table_aggregator.TryGetValue(elm.Name, out cur_table))
{
cur_table = new List<string>();
table_aggregator.Add(elm.Name, cur_table);
}
if (elm.guid == "") throw new NotImplementedException();
cur_table.Add(elm.guid);
}
// build the query to the id's for testing is elements in the database
StringBuilder sql = new StringBuilder();
sql.Append("Select tmp_tbl.id from (" + Environment.NewLine);
List<Guid> ids = new List<Guid>();
if(table_aggregator.Count > 0)
{
bool firsttable = true;
foreach (var tbl in table_aggregator)
{
sql.Append((firsttable ? "" : " UNION ALL "+Environment.NewLine) + "select id from [" + tbl.Key + "] where id in (");
bool firstelm = true;
foreach (var elm in tbl.Value)
{
sql.Append((firstelm ? "":",") +"'"+elm+"'");
firstelm = false;
}
sql.Append(")" + Environment.NewLine );
firsttable = false;
}
sql.Append(") as tmp_tbl");
ids = dbconnect.GetGuidList(sql.ToString());
}
// build the main query insert/update
sql.Clear();
foreach(XML_Element elm in xml_elements)
{
bool isInsert = !ids.Contains(new Guid(elm.guid));
if (!isInsert && elm.Attributes.Count == 1) continue; // no need to update because element haven't any attributes
string names = "", values = "",names_values = "",name,value,table_name;
Table tbl = schema.GetTableByName(elm.Name);
bool FirstElement = true;
if (!elm.IsForwardRef) // if it isn't forward references update
{
table_name = elm.Name;
List<string> ListOfProcessedFields = new List<string>();
foreach (var attr in elm.Attributes)
{
Field fld = tbl.GetFieldByName(attr.Key);
if (fld == null) continue;
name = attr.Key;
ListOfProcessedFields.Add(name);
//value = fld.FormatValueForInsert(attr.Value);
value = FormatValueForSQL(fld,attr.Value,version_of_data);
if (value == null) continue;
if (isInsert)
{
names += (FirstElement ? "" : ",") + "[" + name + "]" + Environment.NewLine;
values += (FirstElement ? "" : ",") + value + Environment.NewLine;
}
else // update
{
if (name == "id") continue;
names_values += (FirstElement ? "" : ",") + "[" + name + "]=" + value + Environment.NewLine;
}
FirstElement = false;
}
// testing is there empty non-nullable field
foreach (var fld in tbl.Fields)
{
if (!fld.Value.IsNullable && !ListOfProcessedFields.Contains(fld.Key))
{
string fakevalue = GetFakeValue(dbconnect,tbl,fld.Value,version_of_data);
names += (FirstElement ? "" : ",") + "[" + fld.Key + "]" + Environment.NewLine;
values += (FirstElement ? "" : ",") + FormatValueForSQL(fld.Value,fakevalue,version_of_data) + Environment.NewLine;
}
}
} else
{
table_name = elm.GetAttibute("table_name");
foreach (var fld in elm.fields)
{
names_values += (FirstElement ? "" : ",") + "[" + fld.Field + "]='" + fld.Value + "'"+ Environment.NewLine;
FirstElement = false;
}
}
if (isInsert && !elm.IsForwardRef) {
string insert =
"INSERT INTO [" + table_name + "] (" + Environment.NewLine +
names + ") VALUES (" + Environment.NewLine +
values + ") "+Environment.NewLine+Environment.NewLine;
sql.Append(insert);
UpdateOnLoad.UpdateAfterLoading(elm, version_of_data, false);
} else
{
if(names_values != "")
{
string update =
"UPDATE [" + table_name + "] SET" + Environment.NewLine +
names_values + "WHERE id='" + elm.guid+"'"+Environment.NewLine+Environment.NewLine;
sql.Append(update);
UpdateOnLoad.UpdateAfterLoading(elm, version_of_data, true);
}
}
}
string sql_string = sql.ToString();
if(sql_string.Length != 0)
{
dbconnect.ExecuteSQL(sql);
}
}
protected void SaveTable(XmlWriter xmlWriter,DBConnect dbconnect,string table_name,Guid id,string path = "",int parent_key_number =0 )
{
if (path == "") path = table_name;
if (PathList != null && !PathList.Contains(path)) PathList.Add(path);
if (RefTreeContains(table_name, id)) return;
Dictionary<string,Ref> ForwardRefList = new Dictionary<string,Ref>();
// find forward references
var foreign_keys = schema.ForeignKey.Where(x => x.Table_str == table_name && x.number != parent_key_number);
if(foreign_keys.Count() > 0)
{
foreach (ForeignKey key in foreign_keys) {
if (key.field.DontProcessing) continue;
if (key.PrimaryTable.DontProcessing) continue;
Guid? id_ = dbconnect.GetGuid("SELECT [" + key.field_str + "] as id FROM [" + key.Table_str + "] where id='"+id.ToString()+"'");
if (id_ == null) continue;
// is it a forward reference
if (!RefTreeContains(key.PrimaryTable_str, (Guid)id_)){
ForwardRefList.Add(key.field_str,new Ref() {tablename = key.PrimaryTable_str,guid = (Guid)id_,number = key.number});
}
}
}
// generate and execute the sql query
Dictionary<int, Field> fields = new Dictionary<int, Field>();
string sql = "SELECT ";
int index = 0;
foreach (var fld in schema.GetTableByName(table_name).Fields)
{
if (fld.Value.DontProcessing) continue;
if (ForwardRefList.ContainsKey(fld.Value.Name)) continue;
sql += (index == 0 ? "" : ",") + "[" + fld.Value.Name + "]";
fields.Add(index, fld.Value);
index++;
}
sql += " FROM ["+table_name+"] WHERE id='"+id.ToString()+"'";
DBConnect.Element obj = dbconnect.GetObject(sql);
xmlWriter.WriteStartElement(table_name);
index = 0;
foreach (var elm in obj.Attributes)
{
Field fld = fields[index];
switch (fld.type)
{
case DBTypes.Boolean:
xmlWriter.WriteAttributeString(elm.Key,elm.Value == "False" ? "0" : "1");
break;
case DBTypes.Double:
xmlWriter.WriteAttributeString(elm.Key,elm.Value.Replace(",","."));
break;
case DBTypes.Datetime:
xmlWriter.WriteAttributeString(elm.Key,elm.Value.Replace(".","-"));
break;
default:
xmlWriter.WriteAttributeString(elm.Key,elm.Value);
break;
}
index++;
}
xmlWriter.WriteEndElement();
AddToRefList(table_name, id,path);
// upload objects by forward references
foreach (var elm in ForwardRefList)
{
SaveTable(xmlWriter,dbconnect,elm.Value.tablename, elm.Value.guid,path + "/" +elm.Value.tablename,elm.Value.number);
}
// write section of xml where contains forward references info
if (ForwardRefList.Count > 0)
{
xmlWriter.WriteStartElement("Update");
xmlWriter.WriteAttributeString("table_name", table_name);
xmlWriter.WriteAttributeString("id", id.ToString());
xmlWriter.WriteAttributeString("ref_count", ForwardRefList.Count.ToString());
foreach (var dr in ForwardRefList)
{
xmlWriter.WriteStartElement("Ref");
xmlWriter.WriteAttributeString("field", dr.Key);
xmlWriter.WriteAttributeString("value", dr.Value.guid.ToString());
xmlWriter.WriteEndElement();
}
xmlWriter.WriteEndElement();
}
// upload backward references
if (!schema.GetTableByName(table_name).DontProcessingBackwardReferences)
{
foreign_keys = schema.ForeignKey.Where((x) => x.PrimaryTable_str == table_name && x.number != parent_key_number);
if(foreign_keys.Count() > 0)
{
foreach (ForeignKey key in foreign_keys) {
if (key.Table.DontProcessing) continue;
List<Guid> id_list = dbconnect.GetGuidList("SELECT id FROM [" + key.Table_str + "] where [" + key.field_str + "]='"+id.ToString()+"'");
string ids = "";
foreach (Guid id_ in id_list)
{
ids += (ids.Count() == 0 ? "":",")+id_.ToString();
if (!RefTreeContains(key.Table_str, id_))
{
SaveTable(xmlWriter,dbconnect, key.Table_str, id_, path + "/" + key.Table_str, key.number);
}
}
}
}
}
}
public SQL_XML_IO_statuses Save(string path, string table_name, Guid guid)
{
EFDbContext.SaveChgs();
RefTree.Clear();
XmlWriter xmlWriter = XmlWriter.Create(path,new XmlWriterSettings() {Encoding = Encoding.UTF8 });
DBConnect dbconnect = new DBConnect();
dbconnect.Open();
xmlWriter.WriteStartDocument();
xmlWriter.WriteStartElement(StartElementName);
xmlWriter.WriteAttributeString("version", Data_Versions.version_1_1.ToString());
xmlWriter.WriteAttributeString("MainElement_Guid", guid.ToString());
xmlWriter.WriteAttributeString("MainElement_Name", table_name);
logging = true;
//PathList = null;
PathList = new List<string>();
SaveTable(xmlWriter,dbconnect,table_name,guid);
dbconnect.Close();
xmlWriter.WriteEndDocument();
xmlWriter.Close();
return SQL_XML_IO_statuses.Ok;
}
public void DeleteTable(DBConnect dbconnect,string table_name,Guid id,bool remove,string path = "",int parent_key_number =0)
{
string sql;
if (path == "") path = table_name;
if (!PathList.Contains(path)) PathList.Add(path);
if (RefTreeContains(table_name, id)) return;
AddToRefList(table_name, id,path);
Dictionary<string,Ref> ForwardRefList = new Dictionary<string,Ref>();
// find forward references
var foreign_keys = schema.ForeignKey.Where((x) => x.Table_str == table_name && x.number != parent_key_number);
if(foreign_keys.Count() > 0)
{
foreach (ForeignKey key in foreign_keys) {
if (key.field.DontProcessing) continue;
if (key.PrimaryTable.DontProcessing) continue;
DBConnect.Query query = new DBConnect.Query();
query.sql = "SELECT [" + key.field_str + "] as id FROM [" + key.Table_str + "] where id=@id";
query.AddParameter("id", id);
Guid? res = dbconnect.GetGuid("SELECT [" + key.field_str + "] as id FROM [" + key.Table_str + "] where id='"+id.ToString()+"'");
if (res == null) continue;
Guid id_ = (Guid)res;
// is it a forward reference
if (!RefTreeContains(key.PrimaryTable_str, id_)){
ForwardRefList.Add(key.field_str,new Ref {tablename = key.PrimaryTable_str,guid = id_,number = key.number});
}
}
}
// Clearing the reference fields
if(ForwardRefList.Count > 0 && remove)
{
sql = "UPDATE ["+table_name+"] SET"+Environment.NewLine;
bool FirstElement = true;
foreach(var elm in ForwardRefList)
{
sql += (FirstElement ? "" : ",") + "[" + elm.Key + "]=null"+Environment.NewLine;
FirstElement = false;
}
sql += "WHERE id='"+id+"'";
dbconnect.ExecuteSQL(sql);
}
// delete objects by forward references
foreach(var elm in ForwardRefList)
{
DeleteTable(dbconnect,elm.Value.tablename, elm.Value.guid,remove,path + "->" +elm.Value.tablename,elm.Value.number);
}
// delete objects by backward references
foreign_keys = schema.ForeignKey.Where((x) => x.PrimaryTable_str == table_name && x.number != parent_key_number);
if(foreign_keys.Count() > 0)
{
foreach (ForeignKey key in foreign_keys) {
if (key.Table.DontProcessing) continue;
List<Guid> id_list = dbconnect.GetGuidList("SELECT id FROM [" + key.Table_str + "] where [" + key.field_str + "]='"+id.ToString()+"'");
string ids = "";
foreach (Guid id_ in id_list)
{
ids += (ids.Count() == 0 ? "":",")+id_;
if (!RefTreeContains(key.Table_str, id_))
{
DeleteTable(dbconnect,key.Table_str, id_,remove, path + "->" + key.Table_str, key.number);
}
}
}
}
if (remove)
{
dbconnect.ExecuteSQL("DELETE FROM ["+table_name+"] WHERE id='"+id.ToString()+"'");
}
}