public void Add(Insert insertCommand)
{
if (this.InsertCommands.Count > 0) {
if (this.Tables != insertCommand.Tables)
throw new ArgumentException("qGen: BulkInsert requiere que todas las inserciones sean en la misma tabla y con los mismos campos");
} else {
this.Tables = insertCommand.Tables;
this.Fields = insertCommand.Fields;
this.DataBase = insertCommand.DataBase;
this.WhereClause = insertCommand.WhereClause;
this.m_Mode = insertCommand.m_Mode;
}
this.InsertCommands.Add(insertCommand);
}
public void Add(Insert insertCommand)
{
if (this.InsertCommands.Count > 0)
{
if (this.Tables != insertCommand.Tables)
{
throw new ArgumentException("qGen: BulkInsert requiere que todas las inserciones sean en la misma tabla y con los mismos campos");
}
}
else
{
this.Tables = insertCommand.Tables;
this.Fields = insertCommand.Fields;
this.DataBase = insertCommand.DataBase;
this.WhereClause = insertCommand.WhereClause;
this.m_Mode = insertCommand.m_Mode;
}
this.InsertCommands.Add(insertCommand);
}
public void Add(Insert insertCommand)
{
if (this.InsertCommands.Count > 0)
{
if (this.Tables.Count != insertCommand.Tables.Count)
{
throw new ArgumentException("qGen: BulkInsert requires all Insert to be on the same tables");
}
if (this.Tables[0].PrefixAndName != insertCommand.Tables[0].PrefixAndName)
{
// TODO: check all tables
throw new ArgumentException("qGen: BulkInsert requires all Insert to be on the same table");
}
}
else
{
this.Tables = insertCommand.Tables;
//this.Fields = insertCommand.Fields.GetFieldNames();
//this.WhereClause = insertCommand.WhereClause;
}
this.InsertCommands.Add(insertCommand);
}
public System.Data.IDbCommand SetupDbCommand(Insert insertCommand, IConnection connection)
{
var DbCommand = connection.DbConnection.CreateCommand();
DbCommand.CommandText =
@"INSERT INTO "
+ this.SqlText(insertCommand.Tables)
+ " "
+ this.SqlText(insertCommand.ColumnValues, ColumnValueFormatStyles.InsertStyle, true, 0);
if (insertCommand.WhereClause != null)
{
DbCommand.CommandText += " WHERE " + this.SqlText(insertCommand.WhereClause);
}
if (insertCommand.OnDuplicateKeyUpdate)
{
var UpdateClause = new StringBuilder();
foreach (IColumnValue ThisField in insertCommand.ColumnValues)
{
if (UpdateClause.Length == 0)
{
UpdateClause.Append(" ON DUPLICATE KEY UPDATE ");
}
else
{
UpdateClause.Append(", ");
}
UpdateClause.Append(this.SqlText(ThisField.ColumnIdentifier) + @"=VALUES(" + this.SqlText(ThisField.ColumnIdentifier) + @")");
}
DbCommand.CommandText += UpdateClause.ToString();
}
this.PopulateParameters(connection, DbCommand.Parameters, insertCommand.ColumnValues, 0);
return(DbCommand);
}
public void Restore(string backupName)
{
string Carpeta = backupName + System.IO.Path.DirectorySeparatorChar;
Lfx.Environment.Folders.EnsurePathExists(this.BackupPath);
if (Carpeta != null && Carpeta.Length > 0 && System.IO.Directory.Exists(this.BackupPath + Carpeta))
{
bool UsandoArchivoComprimido = false;
Lfx.Types.OperationProgress Progreso = new Lfx.Types.OperationProgress("Restaurando copia de seguridad", "Este proceso va a demorar varios minutos. Por favor no lo interrumpa");
Progreso.Modal = true;
/* Progreso.ChangeStatus("Descomprimiendo");
* // Descomprimir backup si está comprimido
* if (System.IO.File.Exists(BackupPath + Carpeta + "backup.7z")) {
* Lfx.FileFormats.Compression.Archive ArchivoComprimido = new Lfx.FileFormats.Compression.Archive(BackupPath + Carpeta + "backup.7z");
* ArchivoComprimido.ExtractAll(BackupPath + Carpeta);
* UsandoArchivoComprimido = true;
* } */
Progreso.ChangeStatus("Eliminando datos actuales");
using (Lfx.Data.IConnection ConnRestaurar = Lfx.Workspace.Master.GetNewConnection("Restauración de copia de seguridad") as Lfx.Data.IConnection) {
Progreso.ChangeStatus("Acomodando estructuras");
Lfx.Workspace.Master.Structure.TagList.Clear();
Lfx.Workspace.Master.Structure.LoadFromFile(this.BackupPath + Carpeta + "dbstruct.xml");
//Lfx.Workspace.Master.CheckAndUpdateDatabaseVersion(true, true);
using (BackupReader Lector = new BackupReader(this.BackupPath + Carpeta + "dbdata.lbd"))
using (IDbTransaction Trans = ConnRestaurar.BeginTransaction()) {
ConnRestaurar.EnableConstraints(false);
Progreso.ChangeStatus("Incorporando tablas de datos");
Progreso.Max = (int)(Lector.Length / 1024);
string TablaActual = null;
string[] ListaCampos = null;
object[] ValoresCampos = null;
int CampoActual = 0;
bool EndTable = false;
qGen.BuilkInsert Insertador = new qGen.BuilkInsert();
do
{
string Comando = Lector.ReadString(4);
switch (Comando)
{
case ":TBL":
TablaActual = Lector.ReadPrefixedString4();
string NombreTabla;
if (Lfx.Workspace.Master.Structure.Tables.ContainsKey(TablaActual) && Lfx.Workspace.Master.Structure.Tables[TablaActual].Label != null)
{
NombreTabla = Lfx.Workspace.Master.Structure.Tables[TablaActual].Label;
}
else
{
NombreTabla = TablaActual.ToTitleCase();
}
EndTable = false;
Progreso.ChangeStatus("Cargando " + NombreTabla);
qGen.Delete DelCmd = new qGen.Delete(TablaActual);
DelCmd.EnableDeleleteWithoutWhere = true;
ConnRestaurar.ExecuteNonQuery(DelCmd);
break;
case ":FDL":
ListaCampos = Lector.ReadPrefixedString4().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
ValoresCampos = new object[ListaCampos.Length];
CampoActual = 0;
break;
case ":FLD":
ValoresCampos[CampoActual++] = Lector.ReadField();
break;
case ".ROW":
qGen.Insert Insertar = new qGen.Insert(TablaActual);
for (int i = 0; i < ListaCampos.Length; i++)
{
Insertar.ColumnValues.AddWithValue(ListaCampos[i], ValoresCampos[i]);
}
Insertador.Add(Insertar);
ValoresCampos = new object[ListaCampos.Length];
CampoActual = 0;
break;
case ":REM":
Lector.ReadPrefixedString4();
break;
case ".TBL":
EndTable = true;
break;
}
if (EndTable || Insertador.Count >= 1000)
{
if (Insertador.Count > 0)
{
ConnRestaurar.ExecuteNonQuery(Insertador);
}
Insertador.Clear();
Progreso.Value = (int)(Lector.Position / 1024);
}
} while (Lector.Position < Lector.Length);
Lector.Close();
if (Lfx.Workspace.Master.MasterConnection.SqlMode == qGen.SqlModes.PostgreSql)
{
// PostgreSql: Tengo que actualizar las secuencias
Progreso.ChangeStatus("Actualizando secuencias");
string PatronSecuencia = @"nextval\(\'(.+)\'(.*)\)";
foreach (string Tabla in Lfx.Data.DatabaseCache.DefaultCache.GetTableNames())
{
string OID = ConnRestaurar.FieldString("SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace WHERE pg_catalog.pg_table_is_visible(c.oid) AND c.relname ~ '^" + Tabla + "$'");
System.Data.DataTable Campos = ConnRestaurar.Select("SELECT a.attname,pg_catalog.format_type(a.atttypid, a.atttypmod),(SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, a.attnum FROM pg_catalog.pg_attribute a WHERE a.attrelid = '" + OID + "' AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum");
foreach (System.Data.DataRow Campo in Campos.Rows)
{
if (Campo[2] != DBNull.Value && Campo[2] != null)
{
string DefaultCampo = System.Convert.ToString(Campo[2]);
if (Regex.IsMatch(DefaultCampo, PatronSecuencia))
{
string NombreCampo = System.Convert.ToString(Campo[0]);
foreach (System.Text.RegularExpressions.Match Ocurrencia in Regex.Matches(DefaultCampo, PatronSecuencia))
{
string Secuencia = Ocurrencia.Groups[1].ToString();
int MaxId = ConnRestaurar.FieldInt("SELECT MAX(" + NombreCampo + ") FROM " + Tabla) + 1;
ConnRestaurar.ExecuteNonQuery("ALTER SEQUENCE " + Secuencia + " RESTART WITH " + MaxId.ToString());
}
}
}
}
}
}
if (System.IO.File.Exists(this.BackupPath + Carpeta + "blobs.lst"))
{
// Incorporar Blobs
Progreso.ChangeStatus("Incorporando imágenes");
System.IO.StreamReader LectorBlobs = new System.IO.StreamReader(this.BackupPath + Carpeta + "blobs.lst", System.Text.Encoding.Default);
string InfoImagen = null;
do
{
InfoImagen = LectorBlobs.ReadLine();
if (InfoImagen != null && InfoImagen.Length > 0)
{
string Tabla = Lfx.Types.Strings.GetNextToken(ref InfoImagen, ",");
string Campo = Lfx.Types.Strings.GetNextToken(ref InfoImagen, ",");
string CampoId = Lfx.Types.Strings.GetNextToken(ref InfoImagen, ",");
string NombreArchivoImagen = Lfx.Types.Strings.GetNextToken(ref InfoImagen, ",");
// Guardar blob nuevo
qGen.Update ActualizarBlob = new qGen.Update(Tabla);
ActualizarBlob.WhereClause = new qGen.Where(Campo, CampoId);
System.IO.FileStream ArchivoImagen = new System.IO.FileStream(this.BackupPath + Carpeta + NombreArchivoImagen, System.IO.FileMode.Open, System.IO.FileAccess.Read);
byte[] Contenido = new byte[System.Convert.ToInt32(ArchivoImagen.Length) - 1 + 1];
ArchivoImagen.Read(Contenido, 0, System.Convert.ToInt32(ArchivoImagen.Length));
ArchivoImagen.Close();
ActualizarBlob.ColumnValues.AddWithValue(Campo, Contenido);
ConnRestaurar.ExecuteNonQuery(ActualizarBlob);
}
}while (InfoImagen != null);
LectorBlobs.Close();
}
if (UsandoArchivoComprimido)
{
Progreso.ChangeStatus("Eliminando archivos temporales");
// Borrar los archivos que descomprim temporalmente
System.IO.DirectoryInfo Dir = new System.IO.DirectoryInfo(this.BackupPath + Carpeta);
foreach (System.IO.FileInfo DirItem in Dir.GetFiles())
{
if (DirItem.Name != "backup.7z" && DirItem.Name != "info.txt")
{
System.IO.File.Delete(this.BackupPath + Carpeta + DirItem.Name);
}
}
}
Progreso.ChangeStatus("Terminando transacción");
Trans.Commit();
}
Progreso.End();
}
Lfx.Workspace.Master.RunTime.Toast("La copia de seguridad se restauró con éxito. A continuación se va a reiniciar la aplicación.", "Copia Restaurada");
}
}
public System.Data.IDbCommand SetupDbCommand(Insert insertCommand, IConnection connection)
{
var FieldList = new System.Text.StringBuilder();
var ParamList = new System.Text.StringBuilder();
var DbCommand = connection.DbConnection.CreateCommand();
foreach (IColumnValue ThisField in insertCommand.ColumnValues)
{
if (FieldList.Length == 0)
{
FieldList.Append(@"""" + ThisField.ColumnName + @"""");
}
else
{
FieldList.Append(@", """ + ThisField.ColumnName + @"""");
}
string FieldParam;
if (ThisField.Value is qGen.SqlFunctions)
{
switch (((qGen.SqlFunctions)(ThisField.Value)))
{
case SqlFunctions.Now:
FieldParam = "NOW()";
break;
default:
throw new NotImplementedException();
}
}
else if (ThisField.Value is qGen.SqlExpression)
{
FieldParam = ThisField.Value.ToString();
}
else
{
if (connection.DbConnection is System.Data.Odbc.OdbcConnection)
{
FieldParam = "?";
}
else
{
FieldParam = "@" + ThisField.ColumnName;
}
}
if (ParamList.Length == 0)
{
ParamList.Append(FieldParam);
}
else
{
ParamList.Append(", " + FieldParam);
}
if (FieldParam == "?" || FieldParam.Substring(0, 1) == "@")
{
var Param = connection.Factory.Driver.GetParameter();
Param.ParameterName = "@" + ThisField.ColumnName;
if (ThisField.Value is DbDateTime && ThisField.Value != null)
{
Param.Value = ((DbDateTime)(ThisField.Value)).Value;
}
else
{
Param.Value = ThisField.Value;
}
if (ThisField.DataType == Lazaro.Orm.ColumnTypes.Blob)
{
Param.DbType = System.Data.DbType.Binary;
}
// FIXME: no debería hacer una excepción para OdbcDriver
if (connection.Factory.Driver is OdbcDriver && ThisField.DataType == Lazaro.Orm.ColumnTypes.Blob)
{
((System.Data.Odbc.OdbcParameter)Param).OdbcType = System.Data.Odbc.OdbcType.VarBinary;
}
DbCommand.Parameters.Add(Param);
}
}
DbCommand.CommandText += @"INSERT INTO """ + string.Join <string>(",", insertCommand.Tables) + @""" (" + FieldList.ToString() + ") VALUES (" + ParamList.ToString() + ")";
if (insertCommand.OnDuplicateKeyUpdate)
{
var UpdateClause = new StringBuilder();
foreach (IColumnValue ThisField in insertCommand.ColumnValues)
{
if (UpdateClause.Length == 0)
{
UpdateClause.Append(@" ON DUPLICATE KEY UPDATE """ + ThisField.ColumnName + @"""=VALUES(""" + ThisField.ColumnName + @""")");
}
else
{
UpdateClause.Append(@", """ + ThisField.ColumnName + @"""=VALUES(""" + ThisField.ColumnName + @""")");
}
}
DbCommand.CommandText += UpdateClause.ToString();
}
return(DbCommand);
}
public string SqlText(Insert insert, bool valuesOnly)
{
var FieldList = new System.Text.StringBuilder();
var ParamList = new System.Text.StringBuilder();
foreach (IColumnValue ThisField in insert.ColumnValues)
{
if (FieldList.Length == 0)
{
FieldList.Append(@"""" + ThisField.ColumnName + @"""");
}
else
{
FieldList.Append(@", """ + ThisField.ColumnName + @"""");
}
string ParamValue;
if (ThisField.Value == null || ThisField.Value == DBNull.Value)
{
ParamValue = "NULL";
}
else
{
var Tipo = ThisField.Value.GetType().Name.Replace("System.", "");
switch (Tipo)
{
case "SqlFunctions":
case "qGen.SqlFunctions":
switch (((qGen.SqlFunctions)(ThisField.Value)))
{
case SqlFunctions.Now:
ParamValue = "NOW()";
break;
default:
throw new NotImplementedException();
}
break;
case "SqlExpression":
case "qGen.SqlExpression":
ParamValue = this.EscapeString(ThisField.Value.ToString());
break;
case "Lfx.Data.SqlLiteral":
ParamValue = ThisField.Value.ToString();
break;
case "Lfx.Data.LDateTime":
ParamValue = "'" + ((DbDateTime)(ThisField.Value)).Value.ToString(SqlDateTimeFormat) + "'";
break;
case "DateTime":
ParamValue = "'" + System.Convert.ToDateTime(ThisField.Value).ToString(SqlDateTimeFormat) + "'";
break;
case "Single":
case "Double":
case "Decimal":
ParamValue = this.FormatDecimal(System.Convert.ToDecimal(ThisField.Value), 8);
break;
case "Integer":
case "Int16":
case "Int32":
case "Int64":
ParamValue = System.Convert.ToInt32(ThisField.Value).ToString();
break;
default:
ParamValue = "'" + this.EscapeString(ThisField.Value.ToString()) + "'";
break;
}
}
if (ParamList.Length == 0)
{
ParamList.Append(ParamValue);
}
else
{
ParamList.Append(", " + ParamValue);
}
}
if (valuesOnly)
{
return("(" + ParamList.ToString() + ")");
}
else
{
var Res = @"INSERT INTO """ + string.Join <string>(",", insert.Tables) + @""" (" + FieldList.ToString() + ") VALUES (" + ParamList.ToString() + ")";
if (insert.OnDuplicateKeyUpdate)
{
string UpdateClause = null;
foreach (IColumnValue ThisField in insert.ColumnValues)
{
if (UpdateClause == null)
{
UpdateClause = @" ON DUPLICATE KEY UPDATE """ + ThisField.ColumnName + @"""=VALUES(""" + ThisField.ColumnName + @""")";
}
else
{
UpdateClause += @", """ + ThisField.ColumnName + @"""=VALUES(""" + ThisField.ColumnName + @""")";
}
}
Res += UpdateClause;
}
return(Res);
}
}
public string SqlText(Insert insert)
{
return(this.SqlText(insert, false));
}
