private static void walkFoldersSql(DirectoryInfo disql, Sqlcreds sc)
{
int lc = 0;
string sqlcommand = "";
Console.WriteLine("walkFoldersSql: Versuche Verbindung ...");
try
{
// Alle Verzeichnisse rekursiv durchlaufen
foreach (DirectoryInfo subdir in disql.GetDirectories())
{
walkFoldersSql(subdir, sc);
}
// Alle Dateien des Verzeichnisses durchlaufen
foreach (FileInfo fi in disql.GetFiles())
{
++gc;
++lc;
MySqlConnection connection = new MySqlConnection(sc.getMysqlConStr());
MySqlCommand command = connection.CreateCommand();
String output = DateTime.Now + ": add Nr." + gc + " [" + hrs(fi.Length) + "/" + hrs(globalsize) + " ges]: " + fi.FullName;
Console.WriteLine(output);
FileAppend(logfilename, output + "\n");
connection.Open();
command.CommandText = "INSERT INTO `" + sc.getDatabase() + "`.`" + sc.getTableName()
+ "` (`name`, `path`, `loc`, `size`, `csum`, `dom`, `owner`, `group`, `stime`, `atime`, `ctime`, `mtime`, `dups`) VALUES ('"
+ fi.Name + "', '" + fi.FullName.Replace("\\", "\\\\") + "', '" + fi.FullName.Split('\\')[0] + "', '" + fi.Length + "', '"
+ Datei2SHA(fi.FullName)/*"--not computed--"*/ + "', '" + File.GetAccessControl(@fi.FullName).GetOwner(typeof(NTAccount)).ToString().Split('\\')[0]
+ "', '" + File.GetAccessControl(@fi.FullName).GetOwner(typeof(NTAccount)).ToString().Split('\\')[1] + "', '"
+ File.GetAccessControl(@fi.FullName).GetGroup(typeof(NTAccount)).ToString().Split('\\')[1] + "', '" + UnixTime(DateTime.Now) + "', '" + UnixTime(fi.LastAccessTime) + "', '"
+ UnixTime(fi.CreationTime) + "', '" + UnixTime(fi.LastWriteTime) + "', '1');";
MySqlDataReader Reader;
Reader = command.ExecuteReader();
globalsize += fi.Length;
connection.Close();
}
Console.WriteLine("walkFoldersSql: " + lc + " Zeilen hinzugefuegt.");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
public static bool mysql_copy_table(Sqlcreds src_sc, Sqlcreds dst_sc)
{
bool erg = false;
Console.WriteLine("mysql_copy_table: Versuche Verbindung zu: " + src_sc.getMysqlConStr());
MySqlConnection connection = new MySqlConnection(src_sc.getMysqlConStr());
MySqlCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO " + dst_sc.getTableName() + " SELECT * FROM " + src_sc.getTableName() + ";";
MySqlDataReader Reader;
connection.Open();
try
{
Reader = command.ExecuteReader();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine("Tabelle konnte nicht kopiert werden: " + src_sc.getTableName());
}
connection.Close();
Console.WriteLine("mysql_copy_table: Trenne Verbindung...");
return erg;
}
private static void mssql_trunc_table(Sqlcreds sc)
{
// Neue Datenbankverbindung
SqlConnection sqlCon = new SqlConnection();
// Connectionstring wird sqlConnection zugewiesen
sqlCon.ConnectionString = sc.getMssqlConStr();
// Verbindung zur Datenbank herstellen
sqlCon.Open();
// Querystring
string strSqlQuery = "TRUNCATE TABLE "+sc.getTableName()+";";
// SQLCommand
SqlCommand sqlCmd = new SqlCommand(strSqlQuery, sqlCon);
int intCheckQuery = sqlCmd.ExecuteNonQuery();
Console.WriteLine("Tabelle geleert: "+ sc.getDatabase()+"."+sc.getTableName());
sqlCon.Close();
}
private static void walkFoldersSql(string DirectorySql, Sqlcreds sc)
{
walkFoldersSql(new DirectoryInfo(DirectorySql), sc);
}
public static bool mysql_trunc_table(Sqlcreds sc)
{
bool erg = true;
Console.WriteLine("sql_trunc_table: Versuche Verbindung zu: " + sc.getMysqlConStr());
MySqlConnection connection = new MySqlConnection(sc.getMysqlConStr());
MySqlCommand command = connection.CreateCommand();
command.CommandText = "TRUNCATE TABLE " + sc.getTableName() + ";";
MySqlDataReader Reader;
connection.Open();
Reader = command.ExecuteReader();
connection.Close();
Console.WriteLine("sql_trunc_table: Trenne Verbindung...");
return erg;
}
private static void mssql_create_table(Sqlcreds sc)
{
SqlConnection sqlCon = new SqlConnection(); // Neue Datenbankverbindung
sqlCon.ConnectionString = sc.getMssqlConStr(); // Connectionstring wird sqlConnection zugewiesen
sqlCon.Open(); // Verbindung zur Datenbank herstellen
// Querystring
string strSqlQuery = "CREATE TABLE " + sc.getTableName()
+ "(istFirma nvarchar(256), VIPFlag bit, Kundennummer nvarchar(256), Vorname nvarchar(256), Name nvarchar(256), "
+ "Firmenname nvarchar(256), Strasse nvarchar(256), PLZ nvarchar(256), Ort nvarchar(256), Teilnehmer nvarchar(1024), "
+ "PRIMARY KEY (Kundennummer),UNIQUE (Kundennummer))";
SqlCommand sqlCmd = new SqlCommand(strSqlQuery, sqlCon); // SQLCommand
int intCheckQuery = sqlCmd.ExecuteNonQuery();
Console.WriteLine("Tabelle angelegt: " + sc.getTableName() + "\n");
sqlCon.Close();
}
public static bool mysql_insert_row(Sqlcreds sc, String filepath)
{
bool erg = true;
Console.WriteLine("mysql_insert_row: Versuche Verbindung zu: " + sc.getMysqlConStr());
MySqlConnection connection = new MySqlConnection(sc.getMysqlConStr());
MySqlCommand command = connection.CreateCommand();
//Testdaten
command.CommandText = "INSERT INTO `"+sc.getDatabase()+"`.`"
+sc.getTableName()+"` (`name`, `path`, `loc`, `size`, `csum`, `owner`, `group`, `stime`, `atime`, `ctime`, `mtime`, `dups`) VALUES ('"
+ "" + "', '"+filepath+"', 'c:', '1234', 'mmmmfc92da241694750979ee6cf582f2d5d7d28e18335de05abc54d0560e0f5302860c652bf08d560252aa5e74210546f369fbbbce8c12cfc7957b2652fe9a75',"
+ " 'm', 'm', CURRENT_TIMESTAMP, '1983-10-10 22:11:02', '1983-01-01 00:11:02', '1983-10-10 22:11:11', '1');";
MySqlDataReader Reader;
connection.Open();
Reader = command.ExecuteReader();
while (Reader.Read())
{
string row = "";
for (int i = 0; i < Reader.FieldCount; i++)
row += Reader.GetValue(i).ToString() + ", ";
Console.WriteLine(row);
}
connection.Close();
Console.WriteLine("Trenne Verbindung...");
return erg;
}
/*http://ryepup.unwashedmeme.com/blog/2007/07/05/reading-passwords-from-the-console-in-c/*/
public static Sqlcreds mysql_get_creds()
{
Sqlcreds sc = new Sqlcreds();
/* Anmerkung: Zugriffsdaten im Testbetrieb hardcodiert,
* später Auslagerung in Konfigurationsdatei/Registry geplant
*/
Console.Write("Hostname: ");
//sc.setHost(Console.ReadLine());
sc.setHost("53.100.11.229");
Console.Write("Datenbank: ");
//sc.setDatabase(Console.ReadLine());
sc.setDatabase("workerdb");
Console.Write("Tabelle: ");
//sc.setTable(Console.ReadLine());
sc.setTable("dbworker");
Console.Write("Benutzer: ");
//sc.setUsername(Console.ReadLine());
sc.setUsername("dbworker");
Console.Write("Pfad: ");
//sc.setPath(Console.ReadLine());
sc.setPath("Q:\\");
Console.Write("Passwort: ");
//sc.setPassword(ReadPassword());
sc.setPassword("");
try
{
MySqlConnection connection = new MySqlConnection(sc.getMysqlConStr());
MySqlCommand command = connection.CreateCommand();
connection.Open();
connection.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine("\n\n*******************************************");
if (ex.ToString().Contains("Access denied")) Console.WriteLine("FEHLER: Zugriff verweigert für Benutzer " + sc.getUsername());
else Console.WriteLine("FEHLER: " + ex);
}
return sc;
}
public static bool mysql_dump_table(Sqlcreds sc)
{
bool erg = true;
int counter = 0;
Console.WriteLine("sql_dump: Versuche Verbindung zu: " + sc.getMysqlConStr());
MySqlConnection connection = new MySqlConnection(sc.getMysqlConStr());
MySqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM "+sc.getTableName()+";";
MySqlDataReader Reader;
connection.Open();
Reader = command.ExecuteReader();
while (Reader.Read())
{
string row = "";
for (int i = 0; i < Reader.FieldCount; i++)
row += Reader.GetValue(i).ToString() + ", ";
Console.WriteLine(row);
}
connection.Close();
Console.WriteLine("sql_dump: Trenne Verbindung...");
return erg;
}
public static bool mysql_drop_table(Sqlcreds sc)
{
bool erg = true;
Console.WriteLine("mysql_drop_table: Versuche Verbindung zu: " + sc.getMysqlConStr());
MySqlConnection connection = new MySqlConnection(sc.getMysqlConStr());
MySqlCommand command = connection.CreateCommand();
command.CommandText = "DROP TABLE " + sc.getTableName() + ";";
MySqlDataReader Reader;
connection.Open();
try {
Reader = command.ExecuteReader();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine("Tabelle konnte nicht geloescht werden: " + sc.getTableName());
}
connection.Close();
Console.WriteLine("mysql_drop_table: Trenne Verbindung...");
return erg;
}
/*INODB: performant, benutzt transaktionen*/
public static bool mysql_create_table(Sqlcreds sc)
{
bool erg = true;
Console.WriteLine("sql_trunc_table: Versuche Verbindung zu: " + sc.getMysqlConStr());
MySqlConnection connection = new MySqlConnection(sc.getMysqlConStr());
MySqlCommand command = connection.CreateCommand();
command.CommandText = "CREATE TABLE IF NOT EXISTS `"+sc.getTableName()+"` ("+
"`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'Dateiname',"+
"`path` varchar(255) COLLATE utf8_unicode_ci NOT NULL,"+
"`loc` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Ort ',"+
"`size` bigint(14) unsigned NOT NULL COMMENT 'Dateigroesse in Byte',"+
"`csum` varchar(129) CHARACTER SET ascii NOT NULL COMMENT 'SHA512-Pruefsumme',"+
"`dom` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Benutzerdomaene',"+
"`owner` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Eigentuemer der Datei',"+
"`group` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Gruppe der datei',"+
"`stime` int(10) unsigned NOT NULL DEFAULT '1' COMMENT 'snapshottime im unixtimeformat',"+
"`atime` int(10) unsigned NOT NULL DEFAULT '1' COMMENT 'accesstime',"+
"`ctime` int(10) unsigned NOT NULL DEFAULT '1' COMMENT 'createtime',"+
"`mtime` int(10) unsigned NOT NULL DEFAULT '1' COMMENT 'modifytime',"+
"`dups` int(10) unsigned NOT NULL DEFAULT '1' COMMENT 'Duplikate',"+
"PRIMARY KEY (`path`,`csum`)"+
") ENGINE=INODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='workertable';";
MySqlDataReader Reader;
connection.Open();
Reader = command.ExecuteReader();
connection.Close();
Console.WriteLine("sql_trunc_table: Trenne Verbindung...");
return erg;
}
