/// <summary>
/// Parse the specified SQL string and creates the corresponding
/// DbSchema
/// </summary>
/// <param name="sql">The SQL string to parse</param>
/// <returns>The DB schema that corresponds to the SQL string.</returns>
public static DbSchema ParseSql(string sql)
{
DbSchema schema = new DbSchema();
string newSQL = sql.Replace("IF NOT EXISTS", ""); //remove from query of checking to exist the table in DB
do
{
Match m1 = _tableHeader.Match(newSQL);
Match m2 = _indexHeader.Match(newSQL);
if (m1.Success && (!m2.Success || m2.Success && m2.Index > m1.Index))
{
DbTable table = ParseDbTable(ref newSQL);
schema._tables.Add(table.TableName, table);
}
else
{
if (m2.Success)
{
DbIndex index = ParseDbIndex(ref newSQL);
schema._indexes.Add(index.IndexName, index);
}
else
{
break;
}
} // else
} while (true);
return(schema);
}
/// <summary>
/// Loads the DB schema of the specified DB file
/// </summary>
/// <param name="dbPath">The path to the DB file to load</param>
/// <returns>The DB schema of that file</returns>
public static DbSchema LoadDB(string dbPath)
{
DbSchema schema = new DbSchema();
SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder
{
DataSource = dbPath,
PageSize = 4096,
UseUTF16Encoding = true
};
using (SQLiteConnection conn = new SQLiteConnection(builder.ConnectionString))
{
conn.Open();
using (SQLiteCommand query = new SQLiteCommand(@"SELECT * FROM SQLITE_MASTER", conn))
{
using (SQLiteDataReader reader = query.ExecuteReader())
{
while (reader.Read())
{
string type = (string)reader["type"];
string name = (string)reader["name"];
string tblName = (string)reader["tbl_name"];
// Ignore SQLite internal indexes and tables
if (name.StartsWith("sqlite_"))
{
continue;
}
if (reader["sql"] == DBNull.Value)
{
continue;
}
string sql = (string)reader["sql"];
if (type == "table")
{
schema._tables.Add(tblName, ParseDbTable(ref sql));
}
else if (type == "index")
{
schema._indexes.Add(name, ParseDbIndex(ref sql));
}
else
{
throw DbUpgradeException.SchemaIsNotSupported();
}
} // while
} // using
} // using
} // using
return(schema);
}
public static bool Check(string filePath)
{
bool isImported = false;
DbSchema schemaDB = null;
try { schemaDB = DbSchema.LoadDB(filePath); }
catch { return(false); }
bool isHasImportedTables =
schemaDB.Tables.Values.Any(x => x.TableName.Equals("MainData")) &&
schemaDB.Tables.Values.Any(x => x.TableName.Equals("ColumnNameAndAlias"));
if (!isHasImportedTables)
{
return(false);
}
IList <string> nameColumns = new List <string>();
string query = "SELECT ColumnName FROM 'ColumnNameAndAlias';";
string connString = $"Data Source = {filePath}; Version=3;";
try
{
using SqLiteDbWrapper readData = new SqLiteDbWrapper(connString);
using (DataTable dt = readData?.GetQueryResultAsTable(query))
{
if (dt?.Rows.Count > 0)
{
foreach (DataRow r in dt?.Rows)
{
nameColumns.Add(r[0]?.ToString());
}
}
}
}
catch { return(false); }
if (nameColumns?.Count > 0)
{
var colunmsInMainData = schemaDB.Tables.Values.FirstOrDefault(x => x.TableName.Equals("MainData")).Columns;
foreach (var column in colunmsInMainData)
{
nameColumns.Remove(column?.ColumnName);
}
}
if (nameColumns?.Count == 0 && isHasImportedTables)
{
isImported = true;
}
return(isImported);
}
/// <summary>
/// Check SQLite DB structure
/// </summary>
/// <param name="TableName"></param>
/// <returns></returns>
private bool CheckUpDBStructure(string TableName)
{
bool isGood = false;
DbSchema schemaDB = null;
string errors = string.Empty;
try
{
schemaDB = DbSchema.LoadDB(settings.Database);
foreach (var table in schemaDB?.Tables)
{
if (TableName != null && table.Value.TableName.Equals(TableName))
{
isGood = true;
break;
}
if (!(table.Value.Columns?.Count > 0))
{
EvntInfoMessage?.Invoke(this, new TextEventArgs($"Ошибка в таблице: {table.Value.TableName} - отсутствуют колонки и структура данных в таблице."));
}
}
}
catch (Exception err)
{ EvntInfoMessage?.Invoke(this, new TextEventArgs($"Ошибка БД - {err.Message}:{Environment.NewLine}{err.ToString()}")); }
finally
{
if (!(schemaDB?.Tables?.Count > 0))
{
EvntInfoMessage?.Invoke(this, new TextEventArgs(
$"Подключенная база данных пустая или же в ней отсутствуют какие-либо таблицы с данными!{Environment.NewLine}" +
$"Предварительно создайте базу данных, таблицы и импортируйте/добавьте в них данные..."));
isGood = false;
}
schemaDB = null;
}
if (isGood)
{
if (string.IsNullOrWhiteSpace(TableName))
{
EvntInfoMessage?.Invoke(this, new TextEventArgs($"В базе данных {settings.Database} со структурой все в порядке"));
}
else
{
EvntInfoMessage?.Invoke(this, new TextEventArgs($"В базе данных {settings.Database} со структурой таблицы '{TableName}' все в порядке"));
}
}
return(isGood);
}
private ISQLConnectionSettings CheckSQLiteDB()
{
string db = null;
using (OpenFileDialog ofd = new OpenFileDialog())
{ db = ofd.OpenFileDialogReturnPath(Properties.Resources.DialogDbFile, "Выберите файл с БД SQLite:"); }
ISQLConnectionSettings newSettings = new SQLConnectionSettings
{
Database = db,
ProviderName = SQLProvider.SQLite
};
if (!string.IsNullOrWhiteSpace(newSettings.Database) && File.Exists(newSettings.Database))
{
DbSchema schemaDb = null;
try
{
schemaDb = DbSchema.LoadDB(newSettings.Database);
IList <string> tablesDb = schemaDb.Tables.Select(tbl => tbl.Value.TableName).ToList();
if (tablesDb?.Count > 0)
{
newSettings.Table = tablesDb[0];
cmbDataBases.DataSource = new List <string> {
newSettings.Database
};
cmbTables.DataSource = tablesDb;
tbResultShow.AppendLine("The inputed data are correct.");
}
}
catch (Exception e)
{
newSettings = null;
tbResultShow.AppendLine($"Ошибка в БД: {e.Message}");
tbResultShow.AppendLine($"{e}");
}
finally
{
if (schemaDb?.Tables?.Count == 0)
{
newSettings = null;
tbResultShow.AppendLine("Подключенная база данных пустая или же в ней отсутствуют какие-либо таблицы с данными!");
tbResultShow.AppendLine("Предварительно создайте базу данных, таблицы и импортируйте/добавьте в них данные...");
tbResultShow.AppendLine("Заблокирован функционал по получению данных из таблиц...");
}
}
}
return(newSettings);
}
public ModelCommonStringStore GetColumns(string table)
{
ModelCommonStringStore models = new ModelCommonStringStore();
IModel model;
if (SQLiteCheckImportedDB.Check(settings.Database) && table.Equals("MainData"))
{
string query = "SELECT ColumnName, ColumnAlias FROM 'ColumnNameAndAlias';";
using (DataTable dt = GetTable(query))
{
if (dt?.Rows.Count > 0)
{
foreach (DataRow r in dt?.Rows)
{
model = new ModelCommon
{
Name = $"{r.Field<string>("ColumnName")}",
Alias = $"{r.Field<string>("ColumnAlias")}"
};
models.Add(model);
}
}
}
}
else
{
DbSchema schemaDB = DbSchema.LoadDB(settings.Database);
if (schemaDB?.Tables?.Count > 0)
{
foreach (var _table in schemaDB.Tables)
{
if (_table.Value.TableName.Equals(table))
{
foreach (var column in _table.Value.Columns)
{
model = new ModelCommon
{
Name = column.ColumnName,
Alias = column.ColumnName
};
models.Add(model);
}
}
}
}
}
return(models);
}
private ISQLConnectionSettings CheckSchemaSQLite(ISQLConnectionSettings tmpSettings)
{
ISQLConnectionSettings newSettings = new SQLConnectionSettings(tmpSettings);
DbSchema schemaDB = null;
IList <string> tablesDB;
DBModel db = new DBModel();
try
{
schemaDB = DbSchema.LoadDB(tmpSettings.Database);
tablesDB = new List <string>();
foreach (var tbl in schemaDB.Tables)
{
tablesDB.Add(tbl.Value.TableName);
}
if (tablesDB?.Count > 0)
{
newSettings.Table = tablesDB[0];
newSettings.ProviderName = SQLProvider.SQLite;
cmbDataBases.DataSource = new List <string>()
{
tmpSettings.Database
};
cmbTables.DataSource = tablesDB;
tbResultShow.AppendLine("The inputed data are correct.");
}
}
catch (Exception e)
{
tbResultShow.AppendLine($"Ошибка в БД: {e.Message}");
tbResultShow.AppendLine($"{e.ToString()}");
}
finally
{
if (schemaDB?.Tables?.Count == 0)
{
tbResultShow.AppendLine("Подключенная база данных пустая или же в ней отсутствуют какие-либо таблицы с данными!");
tbResultShow.AppendLine("Предварительно создайте базу данных, таблицы и импортируйте/добавьте в них данные...");
tbResultShow.AppendLine("Заблокирован функционал по получению данных из таблиц...");
}
}
return(newSettings);
}
private bool CheckUpDBStructure()
{
bool isGood = true;
DbSchema schemaDB = null;
string errors = string.Empty;
try
{
schemaDB = DbSchema.LoadDB(settings.Database);
foreach (var table in schemaDB.Tables)
{
if (table.Value.Columns.Count == 0)
{
EvntInfoMessage?.Invoke(this, new TextEventArgs($"Ошибка в таблице: {table.Value.TableName} - отсутствуют колонки и структура данных в таблице."));
}
}
}
catch (Exception e)
{
EvntInfoMessage?.Invoke(this, new TextEventArgs($"Ошибка в БД: {e.Message}:\r\n{e.ToString()}"));
isGood = false;
}
finally
{
if (schemaDB?.Tables?.Count == 0)
{
EvntInfoMessage?.Invoke(this, new TextEventArgs("Подключенная база данных пустая или же в ней отсутствуют какие-либо таблицы с данными!"));
EvntInfoMessage?.Invoke(this, new TextEventArgs("Предварительно создайте базу данных, таблицы и импортируйте/добавьте в них данные..."));
isGood = false;
}
schemaDB = null;
}
if (isGood)
{
EvntInfoMessage?.Invoke(this, new TextEventArgs($"В базе данных {settings.Database} со структурой все в порядке"));
}
return(isGood);
}
//добавить задачу в конце операции
// импорт =>сжатие базы (выбор отдельных дополнительных колонок для удаления)=> добавление региона принадлежности
private async Task CheckTableForPlates(string pathToSqliteDB)
{
MakeTextBoxResultShow();
txtbResultShow.ReadOnly = true;
if (SQLiteCheckImportedDB.Check(pathToSqliteDB))
{
DbSchema schemaDB = DbSchema.LoadDB(pathToSqliteDB);
IList <string> tablesDB = new List <string>();
IList <string> columns = new List <string>();
string queryCheck = null;
AddLineAtTextBoxResultShow(Properties.Resources.SymbolsDashed);
AddLineAtTextBoxResultShow($"- Selected DB: '{pathToSqliteDB}'");
string columnsLeave = string.Empty;
string columnsDelete = string.Empty;
int totalRows, rowsWithDataInColumn, rowsWithCorrectDataInColumn;
//check if it is existed 'ColumnPlateDistrict' in the table of the localDB
bool isColumnDistrictPlate = false; //Column 'ColumnPlateDistrict' is not existed in the table 'MainData'
bool isExistedColumnDistrictPlate = false; //row 'ColumnPlateDistrict is not existed in the table 'ColumnNameAndAlias'
string columnWithPlate = string.Empty;
bool isImportedDB = SQLiteCheckImportedDB.Check(pathToSqliteDB);
if (isImportedDB)
{
foreach (var table in schemaDB.Tables)
{
rowsWithDataInColumn = 0;
totalRows = 0;
if (table.Value.TableName.Equals("MainData"))
{
foreach (var column in table.Value.Columns)
{
if (column.ColumnName == CommonConst.ColumnPlateDistrict)
{
isColumnDistrictPlate = true;
}
rowsWithCorrectDataInColumn = 0;
if (totalRows == 0)
{
queryCheck = $"SELECT COUNT(*) FROM '{table.Value.TableName}'";
await Task.Run(() => totalRows = CountRowsInTable(queryCheck));
}
if (rowsWithDataInColumn == 0)
{
queryCheck = $"SELECT COUNT(*) FROM {table.Value.TableName} WHERE LENGTH(TRIM({column.ColumnName})) > 0;";
await Task.Run(() => rowsWithDataInColumn = CountRowsInTable(queryCheck));
}
queryCheck =
$"SELECT COUNT(*) " +
$"FROM {table.Value.TableName} " +
$"WHERE LENGTH(TRIM({column.ColumnName})) > 6 AND LENGTH(TRIM({column.ColumnName})) < 9 " +
$"AND ( " +
$"{column.ColumnName} LIKE 'А%' OR " +
$"{column.ColumnName} LIKE 'В%' OR " +
$"{column.ColumnName} LIKE 'С%' OR " +
$"{column.ColumnName} LIKE 'І%' OR " +
$"{column.ColumnName} LIKE 'К%' OR " +
$"{column.ColumnName} LIKE 'Н%' OR " +
$"{column.ColumnName} LIKE 'A%' OR " +
$"{column.ColumnName} LIKE 'B%' OR " +
$"{column.ColumnName} LIKE 'C%' OR " +
$"{column.ColumnName} LIKE 'I%' OR " +
$"{column.ColumnName} LIKE 'K%' OR " +
$"{column.ColumnName} LIKE 'H%' " +
$");";
//////////////////
//do correct in DB
//PRAGMA case_sensitive_like = false;
//////////////////
await Task.Run(() => rowsWithCorrectDataInColumn = CountRowsInTable(queryCheck));
bool isPlateColumn = false;
string results = string.Empty;
if (rowsWithCorrectDataInColumn > 0 && rowsWithDataInColumn > 0)
{
isPlateColumn =
Math.Round((decimal)rowsWithDataInColumn / (decimal)rowsWithCorrectDataInColumn, 1) > (decimal)0.5 &&
Math.Round((decimal)rowsWithDataInColumn / (decimal)rowsWithCorrectDataInColumn, 1) < (decimal)1.5;
}
if (isPlateColumn)
{
columns.Add(
$"Column '{column.ColumnName}' has auto plates: {isPlateColumn}, {Environment.NewLine}" +
$"Full total rows: {totalRows}, {Environment.NewLine}" +
$"Column total rows: {column.ColumnName} = {rowsWithDataInColumn}, {Environment.NewLine}" +
$"Count right rows: {column.ColumnName} = {rowsWithCorrectDataInColumn}, " +
$"Rows with correct form plates = {(Math.Round((decimal)rowsWithDataInColumn / (decimal)rowsWithCorrectDataInColumn, 1)) * 100}%");
columnWithPlate = column.ColumnName;
}
}
}
else if (table.Value.TableName.Equals("ColumnNameAndAlias")) //SpecialColumn for districtPalte //ColumnPlateDistrict
{
queryCheck = $"SELECT ColumnName FROM '{table.Value.TableName}'";
using (DataTable dt = GetTable(queryCheck))
{
foreach (DataRow r in dt.Rows)
{
if (r[0].ToString() == CommonConst.ColumnPlateDistrict)
{
isExistedColumnDistrictPlate = true;
break;
}
}
}
}
}
AddLineAtTextBoxResultShow(Properties.Resources.SymbolsDashed);
AddLineAtTextBoxResultShow(columns.ToStringNewLine());
AddLineAtTextBoxResultShow(Properties.Resources.SymbolsDashed);
//update table 'ColumnNameAndAlias'
if (!isExistedColumnDistrictPlate)
{
queryCheck = $"INSERT INTO 'ColumnNameAndAlias' ('ColumnName', 'ColumnAlias') " +
$"VALUES ('{CommonConst.ColumnPlateDistrict}', 'Принадлежность номера региону');";
try { dBOperations.DoQuery(queryCheck); } catch (Exception err) { AddLineAtTextBoxResultShow(err.ToString()); }
}
//ADD COLUMN in the table 'MainData'
if (!isColumnDistrictPlate)
{
queryCheck = $"ALTER TABLE 'MainData' ADD COLUMN '{CommonConst.ColumnPlateDistrict}' TEXT;";
try { dBOperations.DoQuery(queryCheck); } catch (Exception err) { AddLineAtTextBoxResultShow(err.ToString()); }
}
//update table 'MainData'
if (columns.Count == 1)
{
//Start transaction
dBOperations.DoQuery("BEGIN TRANSACTION;", false);
foreach (var plate in CommonConst.plateDistrict)
{
queryCheck =
$"UPDATE 'MainData' " +
$"SET '{CommonConst.ColumnPlateDistrict}' = '" + plate.Value.ToString() + "' " +
$" WHERE {columnWithPlate} LIKE '{plate.Key}%';";
try { dBOperations.DoQuery(queryCheck, false); } catch (Exception err) { AddLineAtTextBoxResultShow(err.ToString()); }
}
dBOperations.DoQuery("COMMIT;", false);
//Finished commit transaction
}
else
{
AddLineAtTextBoxResultShow(Properties.Resources.SymbolsDashed);
AddLineAtTextBoxResultShow("Данная база подготовлена в другой программе и не подлежит оптимизации");
AddLineAtTextBoxResultShow(Properties.Resources.SymbolsDashed);
}
}
}
AddLineAtTextBoxResultShow(Properties.Resources.SymbolsDashed);
AddLineAtTextBoxResultShow("Все задачи по обновлению принадлежности номеров а/м регионам завершены");
AddLineAtTextBoxResultShow(Properties.Resources.SymbolsDashed);
ShowMessage("Готово!");
HideTextBoxResultShow();
}
private async Task DeleteColumnsInDB(ISQLConnectionSettings settings, IList <string> columns = null)
{
if (settings.ProviderName == SQLProvider.SQLite)
{
DbSchema schemaDB = DbSchema.LoadDB(settings.Database);
IList <string> tablesDB = new List <string>();
DataTable dtForStore = null;
string queryCheck;
AddLineAtTextBoxResultShow(Properties.Resources.SymbolsDashed);
AddLineAtTextBoxResultShow($"- Selected DB: '{settings.Database}'");
IList <string> columnsLeave = new List <string>();
IList <string> columnsDelete = new List <string>();
bool isImportedDB = SQLiteCheckImportedDB.Check(settings.Database);
if (isImportedDB)
{
DbTable db = schemaDB.Tables.Values.First(x => x.TableName.Equals("MainData"));
var cancellationToken = new System.Threading.CancellationTokenSource(10000).Token;
if (columns?.Count > 0)
{
columnsDelete = columns;
columnsLeave = db.Columns.Where(x => columns.Contains(x.ColumnName) == false).Select(x => x.ColumnName).ToList();
}
else
{
foreach (var column in db.Columns)
{
queryCheck = $"SELECT {column.ColumnName} FROM {db.TableName} WHERE LENGTH(TRIM({column.ColumnName})) > 0 LIMIT 10;";
AddLineAtTextBoxResultShow(queryCheck);
cancellationToken = new System.Threading.CancellationTokenSource(10000).Token;
dtForStore = (dBOperations as SQLiteModelDBOperations).GetTable(queryCheck);
if (dtForStore.Rows.Count > 0)
{
columnsLeave.Add($"{column.ColumnName}");
}
else
{
columnsDelete.Add($"{column.ColumnName}");
}
}
}
AddLineAtTextBoxResultShow(Properties.Resources.SymbolsSos);
AddLineAtTextBoxResultShow(Properties.Resources.SymbolsSos);
(dBOperations as SQLiteModelDBOperations).ClearDataTable("MainData", columnsLeave); // delete columns in the table 'MainData' except columns - columnsLeave
(dBOperations as SQLiteModelDBOperations).ClearDataTable("ColumnNameAndAlias", "ColumnName", columnsDelete); // delete from 'ColumnNameAndAlias' where ColumnName contained columnsDelete
}
else
{
DbTable db = schemaDB.Tables.Values.First(x => x.TableName.Equals(settings.Table));
if (columns?.Count > 0)
{
columnsDelete = columns;
columnsLeave = db.Columns.Where(x => columns.Contains(x.ColumnName) == false).Select(x => x.ColumnName).ToList();
(dBOperations as SQLiteModelDBOperations).ClearDataTable(settings.Table, columnsLeave); // delete columns in the table 'MainData' except columns - columnsLeave
}
}
AddLineAtTextBoxResultShow($"- The End -:");
AddLineAtTextBoxResultShow(Properties.Resources.SymbolsDashed);
AddLineAtTextBoxResultShow();
}
else
{
AddLineAtTextBoxResultShow($"{Properties.Resources.SymbolsSosSlash}{Properties.Resources.SymbolsSosSlash}");
AddLineAtTextBoxResultShow($"На данный момент настроена очистка только локальных SQLite БД");
AddLineAtTextBoxResultShow($"{Properties.Resources.SymbolsSosSlash}{Properties.Resources.SymbolsSosSlash}");
}
}