public void DeleteDataSet(string datasetname)
{
var db = new PostgreSQLDBInterface(new Settings().warehousedb);
db.Execute(" drop materialized view \"" + datasetname + "\"");
}
public string CreateDataSet()
{
try
{
var data = Request.Form["datasetdata"];
var moduleid = Request.Form["module"].toInt();
var datasetobject = JsonConvert.DeserializeObject <List <DatasetObject> >(data);
bool isduplicateTable = datasetobject.GroupBy(d => d.TableName).Count() < datasetobject.Count;
if (isduplicateTable)
{
return("1");
}
StringBuilder sb = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
int tcounter = 0;
int ccounter = 0;
string[] tags = { "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z" };
foreach (DatasetObject tbitem in datasetobject)
{
if (tcounter == 0) //handling the first table
{
sb2.Append(" from \"" + tbitem.TableName + "\" " + tags[0]);
}
else
{
var pretag = tags[tcounter - 1]; //setting previous table values
var pretable = datasetobject[tcounter - 1];
sb2.Append(" inner join \"" + tbitem.TableName + "\" " + tags[tcounter] + " on (" + pretag + ".\"" + pretable.NxtTable + "\"=" + tags[tcounter] + ".\"" + tbitem.PreTable + "\") ");
}
ccounter = 0;
if (tbitem.Columns.isNull())
{
sb.Append(tags[tcounter] + ".*");
}
else
{
foreach (string column in tbitem.Columns)
{
ccounter++;
sb.Append(tags[tcounter] + ".\"" + column + "\"");
if (ccounter < tbitem.Columns.Length)
{
sb.Append(",");
}
}
}
tcounter++;
if (tcounter < datasetobject.Count)
{
sb.Append(",");
}
}
string cols = sb.ToString();
string tabs = sb2.ToString();
var db = new InlaksBIContext();
var script = "CREATE MATERIALIZED VIEW public." + datasetobject[0].DataSetName + " AS Select ";
var dataset = new DataSetDetail();
dataset.Script = script + cols + tabs;
dataset.DataSetName = datasetobject[0].DataSetName;
DBInterface dbinterface = new PostgreSQLDBInterface(new Settings().warehousedb);
int resp = dbinterface.Execute(dataset.Script);
var sql = "select * from \"" + dataset.DataSetName + "\" LIMIT 1";
var dt = dbinterface.getData(sql);
dataset.Module = db.Modules.Where(m => m.ModuleID == moduleid).FirstOrDefault();
db.DataSets.Add(dataset);
db.SaveChanges();
if (dt.Rows.Count > 0)
{
return("0");
}
else
{
return("DataSet Created sucessfully but has no data");
}
}
catch (Exception e)
{
return(e.Message);
}
}