Esempio n. 1
0
 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;
        }
Esempio n. 5
0
 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;

        }
Esempio n. 10
0
        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()+"'");
            }
            

        }