public HttpResponseMessage DownloadAsCsv([FromUri] string query_id, [FromUri] string name)
{
var query = SqlQueryRunner.GetFinishedSqlQueryResult(query_id);
var sb = new StringBuilder();
sb.AppendLine(String.Join(",", query.Columns));
foreach (var row in query.data)
{
sb.AppendLine(String.Join(
",",
row.Select(JsonConvert.SerializeObject)
));
}
var stream = new MemoryStream();
var bytes = Encoding.UTF8.GetBytes(sb.ToString());
var result = new HttpResponseMessage(HttpStatusCode.OK)
{
Content = new ByteArrayContent(bytes)
};
result.Content.Headers.ContentDisposition =
new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment")
{
FileName = name + ".csv"
};
result.Content.Headers.ContentType =
new MediaTypeHeaderValue("application/octet-stream");
return(result);
}
public void MigrateDown(SqlQueryRunner runner)
{
if (AppliedMigrations.Count == 0)
{
return;
}
var lastMigration = Migrations[AppliedMigrations.Count - 1];
var appliedMigration = AppliedMigrations.Last();
if (appliedMigration.Name != lastMigration.Name)
{
throw new InvalidOperationException("Have applied migration " + appliedMigration.Name + ", expected " + lastMigration.Name);
}
lastMigration.Down(runner);
AppliedMigrations.Remove(appliedMigration);
var result = runner.Delete(Context.Migrations.Where(m => m.Name == appliedMigration.Name && m.Version == appliedMigration.Version));
if (result != 1)
{
throw new InvalidOperationException("Migrations out of sync");
}
}
public async Task <SqlQueryRunner.RunQueryResults> RunQuery([FromBody] RunQueryParams param)
{
var cs = ConnectionStringsProvider.GetById(param.conn_string_id);
var query = await SqlQueryRunner.RunQuery(cs, param.query_text, param.slow);
return(query);
}
public ASAHost(string sql)
{
//Console.WriteLine("The sql query is: " + sql);
// Console.WriteLine(inputSchema);
this.outputs = SqlQueryRunner.Query(
sql,
new CompilerConfig()
{
SqlCompatibility = new SqlCompatibility()
},
ClrFramework.NetStandard20,
QueryHelper.BinLocations[ClrFramework.NetStandard20],
new Dictionary <string, Subject <IRecord> >()
{
{ "input", this.input }
});
if (this.outputs.Count != 1)
{
throw new ArgumentException("Query: '" + sql + "' returned 0 or more than 1 output: " + this.outputs.Count);
}
this.outputs.First().Value.Subscribe(r => this.outputRecords.Enqueue(r));
this.memoryAllocator = new NativeMemoryAllocator(alignment: 64);
}
static void Main(string[] args)
{
var argsParsed = ArgumentsParsed.FromArgs(args);
var sqlQuery = SqlQuery.FromProgramParams(argsParsed.ProgramParams);
var sqlData = new SqlQueryRunner(sqlQuery.Query, argsParsed.QueryParams).Run();
CsvData
.FromSqlData(sqlData)
.ToFile(argsParsed.GetValue(string.Concat(ArgumentsParsed.PROGRAM_PARAM_PREFIX, "outFilePath")));
}
protected void UpDb(SqlQueryRunner runner)
{
try
{
var migrator = new Migration.Migrator();
migrator.ReadAssemblyMigrations(GetType().Assembly);
migrator.ReadAppliedMigrations(runner);
migrator.MigrateToLatest(runner);
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
public void ReadAppliedMigrations(SqlQueryRunner runner)
{
var stmtList = new StatementList();
var select = stmtList.Select(Context.Migrations.OrderBy(builder => builder.Value(m => m.Name, true)));
try
{
AppliedMigrations = runner.ExecuteQuery(select).ToList();
}
catch (Exception)
{
// Ignore exception when the table does not exist.
// Should ideally not ignore more serious errors, or even more ideally
// check db schema if table exists
}
}
public void EnsureMigrationTable(SqlQueryRunner runner)
{
var parser = new SqlSchemaParser();
var createTable = parser.ParseCreateTable(Context.Migrations);
try
{
runner.ExecuteNonQuery(new List <SqlStatement>()
{
createTable
}, new List <KeyValuePair <string, object> >());
}
catch (Exception)
{
// Ignore exception when the table already exists.
}
}
protected void DownDb(SqlQueryRunner runner)
{
try
{
var migrator = new Migration.Migrator();
migrator.ReadAssemblyMigrations(GetType().Assembly);
migrator.ReadAppliedMigrations(runner);
while (migrator.AppliedMigrations.Count > 0)
{
migrator.MigrateDown(runner);
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
public void Down(SqlQueryRunner runner)
{
var stmtList = new List <SqlStatement>();
stmtList.Add(new SqlDropForeignKey()
{
TableName = "Unit",
ForeignKeyName = "fk_unit_product",
});
stmtList.Add(new SqlDropForeignKey()
{
TableName = "inventory_db",
ForeignKeyName = "fk_inventory_unit",
});
stmtList.Add(new SqlDropTable()
{
TableName = "Product",
});
stmtList.Add(new SqlDropTable()
{
TableName = "Unit",
});
stmtList.Add(new SqlDropTable()
{
TableName = "inventory_db",
});
stmtList.Add(new SqlDropTable()
{
TableName = "TypeValue",
});
stmtList.Add(new SqlDropTable()
{
TableName = "AttributeValue",
});
runner.ExecuteNonQuery(stmtList, new List <KeyValuePair <string, object> >());
}
public void MigrateToLatest(SqlQueryRunner runner)
{
if (AppliedMigrations.Count > Migrations.Count)
{
throw new InvalidOperationException("There are more applied migrations than actual migrations.");
}
if (AppliedMigrations.Count == 0)
{
EnsureMigrationTable(runner);
}
for (var i = 0; i < Migrations.Count; i++)
{
var assemblyMigration = Migrations[i];
if (i < AppliedMigrations.Count)
{
var appliedMigration = AppliedMigrations[i];
if (appliedMigration.Name != assemblyMigration.Name)
{
throw new InvalidOperationException("Have applied migration " + appliedMigration.Name + ", expected " + assemblyMigration.Name);
}
continue;
}
assemblyMigration.Up(runner);
var stmtList = new StatementList();
stmtList.Insert(Context.Migrations,
insert => insert
.Value(m => m.Name, assemblyMigration.Name)
.Value(m => m.Version, "Version X"));
runner.ExecuteNonQuery(stmtList);
}
}
public SqlQuery CancelQuery([FromUri] Guid query_id)
{
return(SqlQueryRunner.CancelQuery(query_id));
}
public async Task <SqlQuery> GetQueryResults([FromUri] Guid query_id)
{
var result = await SqlQueryRunner.GetQueryResult(query_id);
return(result);
}
public void Up(SqlQueryRunner runner)
{
var stmtList = new List <SqlStatement>();
stmtList.Add(new SqlCreateTable()
{
TableName = "Product",
Columns = new List <SqlColumn>()
{
new SqlColumn()
{
Name = "ProductId",
Type = typeof(Int32),
PrimaryKey = true,
PrimaryKeyAutoIncrement = true,
SqlType = new SqlTypeInfo()
{
},
},
new SqlColumn()
{
Name = "Name",
Type = typeof(String),
SqlType = new SqlTypeInfo()
{
},
},
},
});
stmtList.Add(new SqlCreateTable()
{
TableName = "Unit",
Columns = new List <SqlColumn>()
{
new SqlColumn()
{
Name = "UnitId",
Type = typeof(Int32),
PrimaryKey = true,
PrimaryKeyAutoIncrement = true,
SqlType = new SqlTypeInfo()
{
},
},
new SqlColumn()
{
Name = "ProductId",
Type = typeof(Int32),
SqlType = new SqlTypeInfo()
{
},
},
new SqlColumn()
{
Name = "UnitCode",
Type = typeof(String),
SqlType = new SqlTypeInfo()
{
StringLength = 16,
},
},
new SqlColumn()
{
Name = "Name",
Type = typeof(String),
SqlType = new SqlTypeInfo()
{
},
},
new SqlColumn()
{
Name = "Price",
Type = typeof(Int32),
SqlType = new SqlTypeInfo()
{
},
},
},
});
stmtList.Add(new SqlAddIndex()
{
TableName = "Unit",
Index = new SqlIndex()
{
Name = "ix_sku",
Unique = true,
Columns = new List <String>()
{
"UnitCode",
},
},
});
stmtList.Add(new SqlCreateTable()
{
TableName = "inventory_db",
Columns = new List <SqlColumn>()
{
new SqlColumn()
{
Name = "inventory_id",
Type = typeof(Int32),
PrimaryKey = true,
PrimaryKeyAutoIncrement = true,
SqlType = new SqlTypeInfo()
{
},
},
new SqlColumn()
{
Name = "unit_id",
Type = typeof(Int32),
SqlType = new SqlTypeInfo()
{
},
},
new SqlColumn()
{
Name = "stock",
Type = typeof(Int32),
SqlType = new SqlTypeInfo()
{
},
},
},
});
stmtList.Add(new SqlCreateTable()
{
TableName = "TypeValue",
Columns = new List <SqlColumn>()
{
new SqlColumn()
{
Name = "BoolValue",
Type = typeof(Boolean),
SqlType = new SqlTypeInfo()
{
},
},
new SqlColumn()
{
Name = "ByteValue",
Type = typeof(Byte),
SqlType = new SqlTypeInfo()
{
},
},
new SqlColumn()
{
Name = "ShortValue",
Type = typeof(Int16),
SqlType = new SqlTypeInfo()
{
},
},
new SqlColumn()
{
Name = "IntValue",
Type = typeof(Int32),
SqlType = new SqlTypeInfo()
{
},
},
new SqlColumn()
{
Name = "NullableIntValue",
Type = typeof(Int32),
Nullable = true,
SqlType = new SqlTypeInfo()
{
},
},
new SqlColumn()
{
Name = "FloatValue",
Type = typeof(Single),
SqlType = new SqlTypeInfo()
{
},
},
new SqlColumn()
{
Name = "LongValue",
Type = typeof(Int64),
SqlType = new SqlTypeInfo()
{
},
},
new SqlColumn()
{
Name = "DecimalValue",
Type = typeof(Decimal),
SqlType = new SqlTypeInfo()
{
DecimalPrecision = 13,
DecimalScale = 5,
},
},
new SqlColumn()
{
Name = "DoubleValue",
Type = typeof(Double),
SqlType = new SqlTypeInfo()
{
},
},
new SqlColumn()
{
Name = "DateTimeValue",
Type = typeof(DateTime),
SqlType = new SqlTypeInfo()
{
},
},
new SqlColumn()
{
Name = "NullableDateTimeValue",
Type = typeof(DateTime),
Nullable = true,
SqlType = new SqlTypeInfo()
{
},
},
new SqlColumn()
{
Name = "StringValue",
Type = typeof(String),
SqlType = new SqlTypeInfo()
{
},
},
new SqlColumn()
{
Name = "NullableStringValue",
Type = typeof(String),
Nullable = true,
SqlType = new SqlTypeInfo()
{
},
},
new SqlColumn()
{
Name = "IntEnumValue",
Type = typeof(Int32),
SqlType = new SqlTypeInfo()
{
},
},
new SqlColumn()
{
Name = "BlobValue",
Type = typeof(Byte[]),
SqlType = new SqlTypeInfo()
{
},
},
},
});
stmtList.Add(new SqlCreateTable()
{
TableName = "AttributeValue",
Columns = new List <SqlColumn>()
{
new SqlColumn()
{
Name = "Length100Unicode",
Type = typeof(String),
SqlType = new SqlTypeInfo()
{
StringLength = 100,
StringNVarChar = true,
},
},
new SqlColumn()
{
Name = "DecimalPrecision",
Type = typeof(Decimal),
SqlType = new SqlTypeInfo()
{
DecimalPrecision = 10,
DecimalScale = 7,
},
},
},
});
stmtList.Add(new SqlAddForeignKey()
{
TableName = "Unit",
ForeignKey = new SqlForeignKey()
{
Name = "fk_unit_product",
ReferenceTableName = "Product",
Columns = new List <String>()
{
"ProductId",
},
ReferenceColumns = new List <String>()
{
"ProductId",
},
},
});
stmtList.Add(new SqlAddForeignKey()
{
TableName = "inventory_db",
ForeignKey = new SqlForeignKey()
{
Name = "fk_inventory_unit",
ReferenceTableName = "Unit",
Columns = new List <String>()
{
"unit_id",
},
ReferenceColumns = new List <String>()
{
"UnitId",
},
},
});
runner.ExecuteNonQuery(stmtList, new List <KeyValuePair <string, object> >());
}
