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)
}
}
}
