public DBTable(string tableName) { // this base constructor was very expensive // we now cache the result for all objects : dbtable and only redo it if an alter table occured (this drastically cut down the number of sql statements) // this piece of code alone took over 30% of the time on my machine when entering config and newing up all the episodes Dictionary<string, DBFieldType> cachedForTable; m_tableName = tableName; //m_fields = new Dictionary<string, DBField>(); if (fields.TryGetValue(tableName, out cachedForTable)) // good, cached, this happens 99% of the time { foreach (KeyValuePair<string, DBFieldType> entry in cachedForTable) if (!m_fields.ContainsKey(entry.Key)) m_fields.Add(entry.Key, new DBField(entry.Value)); } else // we have to get it, happens when the first object is created or after an alter table { cachedForTable = new Dictionary<string, DBFieldType>(); // load up fields from the table SQLiteResultSet results = DBTVSeries.Execute("SELECT sql FROM sqlite_master WHERE name='" + m_tableName + "'"); if (results != null && results.Rows.Count > 0) { // we have the table definition, parse it for names/types String sCreateTable = results.Rows[0].fields[0]; String RegExp = @"CREATE TABLE .*?\((.*?)\)"; Regex Engine = new Regex(RegExp, RegexOptions.IgnoreCase); Match tablematch = Engine.Match(sCreateTable); if (tablematch.Success) { String sParameters = tablematch.Groups[1].Value + ','; //trailng comma make the regex below find the last column // we have the list of parameters, parse them // group 1: fieldname, group2: type, group3: test for primary key, group4: any thing else until a comma (not null, Default value etc.) RegExp = @"([^\s]+)\s+([^\s,]+)(\s+primary key)?([^,]+)?,"; Engine = new Regex(RegExp, RegexOptions.IgnoreCase); MatchCollection matches = Engine.Matches(sParameters); foreach (Match parammatch in matches) { String sName = parammatch.Groups[1].Value; // could be either "int" or "integer" bool bIntType = parammatch.Groups[2].Value.StartsWith("int", StringComparison.InvariantCultureIgnoreCase); bool bPrimary = parammatch.Groups[3].Success; // In Sqlite an "integer" (but not "int") Primary Key is an alias for the sqlite rowid, and therefore auto increments bool bAutoIncrement = (bPrimary && parammatch.Groups[2].Value.Equals("integer", StringComparison.InvariantCultureIgnoreCase)) || // or a column can be set as autoincrement parammatch.Groups[4].Value.ToLowerInvariant().Contains("autoincrement"); DBFieldType cachedInfo = new DBFieldType { Primary = bPrimary, Type = (bIntType ? DBField.cTypeInt : DBField.cType.String), AutoIncrement = bAutoIncrement }; if (!m_fields.ContainsKey(sName)) { m_fields.Add(sName, new DBField(cachedInfo)); } cachedForTable.Add(sName, cachedInfo); } lock (fields) fields.Add(tableName, cachedForTable); } else { MPTVSeriesLog.Write("parsing of CREATE TABLE failed!!!"); } } else { // no tables, assume it's going to be created later (using AddColumn) } } }
public DBTable(string tableName) { // this base constructor was very expensive // we now cache the result for all objects : dbtable and only redo it if an alter table occured (this drastically cut down the number of sql statements) // this piece of code alone took over 30% of the time on my machine when entering config and newing up all the episodes Dictionary <string, DBFieldType> cachedForTable; m_tableName = tableName; //m_fields = new Dictionary<string, DBField>(); if (fields.TryGetValue(tableName, out cachedForTable)) // good, cached, this happens 99% of the time { foreach (KeyValuePair <string, DBFieldType> entry in cachedForTable) { if (!m_fields.ContainsKey(entry.Key)) { m_fields.Add(entry.Key, new DBField(entry.Value)); } } } else // we have to get it, happens when the first object is created or after an alter table { cachedForTable = new Dictionary <string, DBFieldType>(); // load up fields from the table SQLiteResultSet results = DBTVSeries.Execute("SELECT sql FROM sqlite_master WHERE name='" + m_tableName + "'"); if (results != null && results.Rows.Count > 0) { // we have the table definition, parse it for names/types String sCreateTable = results.Rows[0].fields[0]; String RegExp = @"CREATE TABLE .*?\((.*?)\)"; Regex Engine = new Regex(RegExp, RegexOptions.IgnoreCase); Match tablematch = Engine.Match(sCreateTable); if (tablematch.Success) { String sParameters = tablematch.Groups[1].Value + ','; //trailng comma make the regex below find the last column // we have the list of parameters, parse them // group 1: fieldname, group2: type, group3: test for primary key, group4: any thing else until a comma (not null, Default value etc.) RegExp = @"([^\s]+)\s+([^\s,]+)(\s+primary key)?([^,]+)?,"; Engine = new Regex(RegExp, RegexOptions.IgnoreCase); MatchCollection matches = Engine.Matches(sParameters); foreach (Match parammatch in matches) { String sName = parammatch.Groups[1].Value; // could be either "int" or "integer" bool bIntType = parammatch.Groups[2].Value.StartsWith("int", StringComparison.InvariantCultureIgnoreCase); bool bPrimary = parammatch.Groups[3].Success; // In Sqlite an "integer" (but not "int") Primary Key is an alias for the sqlite rowid, and therefore auto increments bool bAutoIncrement = (bPrimary && parammatch.Groups[2].Value.Equals("integer", StringComparison.InvariantCultureIgnoreCase)) || // or a column can be set as autoincrement parammatch.Groups[4].Value.ToLowerInvariant().Contains("autoincrement"); DBFieldType cachedInfo = new DBFieldType { Primary = bPrimary, Type = (bIntType ? DBField.cTypeInt : DBField.cType.String), AutoIncrement = bAutoIncrement }; if (!m_fields.ContainsKey(sName)) { m_fields.Add(sName, new DBField(cachedInfo)); } cachedForTable.Add(sName, cachedInfo); } lock (fields) fields.Add(tableName, cachedForTable); } else { MPTVSeriesLog.Write("parsing of CREATE TABLE failed!!!"); } } else { // no tables, assume it's going to be created later (using AddColumn) } } }