public static void InitDataSet()
{
if (_config == null)
{
return; //avoid loading config problem
}
SimpleLog.Log("MySQL Database initialisation", SimpleLog.Severity.Info);
initQualifier();
_dset = new DataSet("analyse");
if (_config.Bdd)
{
_insertParam = new string[] { "Fichier", "Chemin", "Taille", "Modified", "Created", "AnalysisPath", "lastAccess", "user", "status" };
_insertType = new DbType[] { DbType.String, DbType.String, DbType.Int64, DbType.DateTime, DbType.DateTime, DbType.Int64, DbType.DateTime, DbType.String, DbType.Int16 };
conn = DataTools.DataTools.SetConnection(_config.ConnectionString);
adapter = DataTools.DataTools.getAdapter("select * from analyse", conn);
adapter.SelectCommand.CommandTimeout = 180;
adapter.UpdateCommand.CommandTimeout = 180;
adapter.DeleteCommand.CommandTimeout = 180;
adapter.InsertCommand.CommandTimeout = 180;
userAdapter = DataTools.DataTools.getAdapter("select * from user", conn);
adapter.UpdateCommand = conn.CreateCommand();
adapter.UpdateCommand.CommandText = "UPDATE `analyse` SET `Fichier` = @p1,`Hash` = @p2,`Modified` = @p3,`isDuplicate` = @p4,`Taille` = @p5,`Erreur` = @p6,`Chemin` = @p7,`analysispath` = @p8,`Status` = @p9,`lastAccess` = @p10,`user` = @p11 ,`created` = @p12,`isOriginal` = @p14";
adapter.UpdateCommand.CommandType = CommandType.Text;
adapter.UpdateCommand.Parameters.Clear();
adapter.UpdateCommand.Parameters.AddRange(new DbParameter[] {
GenericParameter.Get("@p0", DbType.Int32, 300, "id"),
GenericParameter.Get("@p1", DbType.String, 300, "Fichier"),
GenericParameter.Get("@p2", DbType.String, 45, "Hash"),
GenericParameter.Get("@p3", DbType.DateTime, 300, "Modified"),
GenericParameter.Get("@p4", DbType.Int16, 1, "isDuplicate"),
GenericParameter.Get("@p14", DbType.Int16, 1, "isOriginal"),
GenericParameter.Get("@p5", DbType.Int64, 300, "Taille"),
GenericParameter.Get("@p6", DbType.String, 45, "Erreur"),
GenericParameter.Get("@p7", DbType.Int16, 300, "Chemin"),
GenericParameter.Get("@p8", DbType.Int16, 300, "analysispath"),
GenericParameter.Get("@p9", DbType.Int16, 1, "Status"),
GenericParameter.Get("@p10", DbType.DateTime, 300, "lastAccess"),
GenericParameter.Get("@p11", DbType.String, 45, "user"),
GenericParameter.Get("@p12", DbType.DateTime, 45, "created")
});
//adapter.ContinueUpdateOnError=true;//TO do
for (int i = 0; i < _qualifiers.Count; i++)
{
var q = _qualifiers[i];
var u = i + 20;
adapter.UpdateCommand.Parameters.Add(GenericParameter.Get("@p" + u, DbType.Int16, 45, q.ColumnName));
adapter.UpdateCommand.CommandText += $",`{q.ColumnName}` = @p{u}";
}
if (_qualifiers.Count > 0)
{
adapter.UpdateCommand.Parameters.Add(GenericParameter.Get("@pQ", DbType.Int16, 45, "q"));
adapter.UpdateCommand.CommandText += $",`q` = @pQ";
}
//build insert command
adapter.InsertCommand.CommandText = $"Insert into analyse ({String.Join(",", _insertParam)}) values (";
adapter.InsertCommand.Parameters.Clear();
for (int i = 0; i < _insertParam.Length; i++)
{
var name = "@p" + i;
if (i > 0)
{
adapter.InsertCommand.CommandText += ",";
}
adapter.InsertCommand.CommandText += name;
var p = GenericParameter.Get(name, _insertType[i], 45, _insertParam[i]);
adapter.InsertCommand.Parameters.Add(p);
}
adapter.InsertCommand.CommandText += ")";
//adapter.UpdateCommand.Parameters.Add(GenericParameter.Get("@debug", DbType.String, 45, "debug"));
//adapter.UpdateCommand.CommandText += $",`debug` = @debug";
adapter.UpdateCommand.CommandText += " WHERE `id` = @p0";
loadStoredDataTable();
_storedFileList = new DataView(_dset.Tables[0], null, "Fichier", DataViewRowState.CurrentRows);//"Status=0"
_storedFileListeIsEmpty = _storedFileList.Count == 0;
}
else
{
var dtDoublons = new DataTable("analyse");
dtDoublons.Columns.Add("Fichier", typeof(string));
dtDoublons.Columns.Add("user", typeof(string));
dtDoublons.Columns.Add("Taille", typeof(long));
dtDoublons.Columns.Add("Modified", typeof(DateTime));
dtDoublons.Columns.Add("lastAccess", typeof(DateTime));
dtDoublons.Columns.Add("created", typeof(DateTime));
dtDoublons.Columns.Add("Hash", typeof(string));
dtDoublons.Columns.Add("Status", typeof(int));
dtDoublons.Columns.Add("Chemin", typeof(int));
dtDoublons.Columns.Add("isDuplicate", typeof(bool));
dtDoublons.Columns.Add("isOriginal", typeof(bool));
dtDoublons.Columns.Add("AnalysisPath", typeof(int));
dtDoublons.Columns.Add("Erreur", typeof(string));
var dtUser = new DataTable("user");
dtUser.Columns.Add("login", typeof(string));
dtUser.Columns.Add("email", typeof(string));
dtUser.Columns.Add("title", typeof(string));
foreach (var q in _qualifiers)
{
dtDoublons.Columns.Add(q.ColumnName, typeof(int));
}
if (_qualifiers.Count > 0)
{
dtDoublons.Columns.Add("q", typeof(int));
}
//dtDoublons.Columns.Add("debug", typeof(string));
_dset.Tables.Add(dtDoublons);
_dset.Tables.Add(dtUser);
}
}
void Button_end(object sender, PageEventArgs e)
{
var schema = txtSchema.Text == "" ? "public" : txtSchema.Text;
var importdestinationPath = "";
var isMySql = cmbType.SelectedIndex == 0;
string[] param = { txtHote.Text, txtUtil.Text, txtMdp.Text, txtBase.Text, schema };
var mainConn = new ConnectionProperties
{
Host = txtHote.Text,
User = txtUtil.Text,
Password = txtMdp.Text,
Database = txtBase.Text,
SearchPath = new string[] { schema, "public" }
};
//var connectionString = cmbType.SelectedIndex == 0
// ? string.Format("Datasource={0};uid={1};pwd={2};", param)
// : string.Format("User ID={1};Password={2};Host={0};Port=5432;Database={3};SearchPath={4},public;", param);
var tempPath = Path.GetTempPath() + Guid.NewGuid();// used for import only
if (Program.AppSet == null)
{
Program.AppSet = new AppSetting(); //if no default properties
}
if (_fromImport) //unzip file to temp file and load exported config
{
// select file to import
var fd = new OpenFileDialog
{
Title = Resources.SelectImportFile,
CheckFileExists = true,
CheckPathExists = true,
DefaultExt = "twz",
Filter = "twz files (*.twz)|*.twz",
FilterIndex = 2,
RestoreDirectory = true,
ReadOnlyChecked = true,
ShowReadOnly = true
};
var result = fd.ShowDialog();
if (result != DialogResult.OK)
{
return;
}
// select destination folder
var folderDlg = new FolderBrowserDialog
{
Description = "Selectionnez le répertoire de destination du sit web",
ShowNewFolderButton = true
};
result = folderDlg.ShowDialog();
if (result != DialogResult.OK)
{
return;
}
importdestinationPath = folderDlg.SelectedPath;
_waitingForm = new WaitingForm(PrepareImportWorker)
{
Text = Resources.ImportPrepare,
progressBar = { Style = ProgressBarStyle.Marquee }
};
_waitingForm.Wr.RunWorkerAsync(new object[] { fd.FileName, tempPath });
_waitingForm.ShowDialog();
mainConn.SearchPath = new string[] { Program.AppSet.Schema, "public" };
}
//set app setting parameter
if (!_fromImport)
{
Program.AppSet.Titre = cmbType.SelectedIndex == 0 ? txtBase.Text : schema;
}
Program.AppSet.ConnectionString = mainConn.GetConnectionString(isMySql);//connectionString;
Program.AppSet.ProviderType = cmbType.SelectedIndex == 0 ? Provider.MySql : Provider.Postgres;
Program.AppSet.identParam = cmbType.SelectedIndex == 0 ? "?" : "@";
Program.AppSet.sqlDebug = "oui";
Tools.SetDefaultProviderFromAppSet();
SqlCommands.Provider = cmbType.SelectedIndex == 0 ? Provider.MySql : Provider.Postgres;
//for postgres verify database exist if not create
if (Program.AppSet.ProviderType == Provider.Postgres)
{
var cs = string.Format("User ID={1};Password={2};Host={0};Port=5432;", param);
if (!dbExist(txtBase.Text, cs))
{
if (MetroMessageBox.Show(this, Resources.DataBaseNotExist, Resources.Erreur, MessageBoxButtons.YesNoCancel, MessageBoxIcon.Error)
!= DialogResult.Yes)
{
return;
}
dbCreate(txtBase.Text, cs);
}
}
//Add schema to database
if (!_fromImport && (Program.AppSet.ProviderType == Provider.Postgres && schemaExist(schema)))
{
string err;
if (MetroMessageBox.Show(this, Resources.SchemaAlreadyExist, Resources.Erreur, MessageBoxButtons.YesNoCancel, MessageBoxIcon.Error)
!= DialogResult.Yes)
{
return;
}
if (MetroMessageBox.Show(this, Resources.ConfirmDelete, Resources.Confirmation, MessageBoxButtons.YesNo, MessageBoxIcon.Question)
!= DialogResult.Yes)
{
return;
}
DataTools.Command($"DROP SCHEMA {schema} CASCADE;", null, Program.AppSet.ConnectionString, out err);
if (!string.IsNullOrEmpty(err))
{
MetroMessageBox.Show(this, string.Format(Resources.ErrorMessage, err), Resources.Erreur, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
var connectionString = mainConn.GetConnectionString(isMySql);
if (!_fromImport)
{
Program.AppSet.Schema = schema;
}
if (Program.AppSet.ProviderType == Provider.Postgres)
{//postgres
param = new string[] { txtSchema.Text };
e.Cancel = !WizardSQLHelper.ExecuteFromFile("schema.sql", param, connectionString, this);
if (e.Cancel)
{
return; // stop on sql error
}
}
else
{//mysql
param = new string[] { txtBase.Text };
e.Cancel = !WizardSQLHelper.ExecuteFromFile("schema.sql", param, mainConn.GetConnectionString(isMySql), this);
if (e.Cancel)
{
return; // stop on sql error
}
Program.AppSet.ConnectionString = connectionString + "Database=" + txtBase.Text + ";";;
}
// add tabloid table to database
try
{
if (_fromImport)//unzip file to temp file and load exported config
{
_waitingForm = new WaitingForm(ImportWorker)
{
Text = Resources.dbImport,
progressBar = { Style = ProgressBarStyle.Marquee }
};
var conn = new ConnectionProperties
{
Host = txtHote.Text,
Port = "5432",
User = txtUtil.Text,
Password = txtMdp.Text,
Database = txtBase.Text
};
_waitingForm.Wr.RunWorkerAsync(new object[] { tempPath, conn, importdestinationPath });
_waitingForm.ShowDialog();
}
else
if (!TabloidTables.CreateTable(TabloidTables.TabloidTablesArray, connectionString, this))//create tabloid table in base
{
return;
}
}
catch (Exception ex)
{
MetroMessageBox.Show(this, ex.ToString(), Resources.Error, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
//add user
if (_fromImport)
{
var utilFrm = new UtilEditor();
utilFrm.btnCancel.Enabled = false;
utilFrm.cmbAuth.SelectedIndex = 1;
utilFrm.cmbAuth.Enabled = false;
if (utilFrm.ShowDialog() == DialogResult.OK)//for user table need to ask for user 0
{
TabloidConfig.Config.updateCurrentKey(Program.AppSet.grainDeSable);
var keyParam = GenericParameter.Get("id_utilisateur", DbType.Int32, null);
var sql = $"INSERT INTO utilisateurs(logon, nom, prenom, mail, password, auteur_utilisateurs, datemaj_utilisateurs, deleted_utilisateurs, debutsession, finsession) VALUES('{utilFrm.txtLogin.Text}', '{utilFrm.txtNom.Text}', '{utilFrm.txtPrenom.Text}', '{utilFrm.txtMail.Text}', '{Tabloid.Classes.Tools.SecurityHelper.EncryptPassword(utilFrm.txtMdp1.Text)}', NULL, NULL, 0, now(), NULL)";
sql = sql + string.Format(SqlConverter.GetSql(SqlConverter.SqlType.InsertCommandKeyOut), "id_utilisateur");
var id = DataTools.ScalarCommand(sql, new[] { keyParam }, mainConn.GetConnectionString(isMySql));
sql = $"INSERT INTO lst_roles(utilisateurs_id, roles_id) VALUES({id}, 1);";
DataTools.Command(sql, null, mainConn.GetConnectionString(isMySql));
}
}
else
{
//open project properties form
Tools.EditSetting(_configFiles);
}
}
