private void createExtraColumnsIfNeeded(QuandlDatasetData dataset)
{
string tableName = "quandl." + DatabaseCode;
// For each extra column
foreach (var column in dataset.ColumnNames)
{
string columnType = GetPostgresColumnType(GetSampleDataOnColumn(column), column).ToLower();
var _column = column.ToLower();
string query = @"DO $$
BEGIN
BEGIN
ALTER TABLE " + tableName + " ADD COLUMN " + _column + " " + columnType + @";
EXCEPTION
WHEN duplicate_column THEN
IF (SELECT format_type(atttypid, atttypmod) AS type
FROM pg_attribute
WHERE attrelid = '" + tableName + "'::regclass and attname = '" + _column + @"'
AND attnum > 0
AND NOT attisdropped) = '" + columnType + @"' THEN
RAISE NOTICE 'Has needed type.';
ELSE
IF '" + columnType + @"' = 'text' THEN
RAISE NOTICE 'New type is text, comes from a null most likely so ignoring';
ELSE
RAISE NOTICE 'Should alter.';
" + CreateCastQuery(tableName, column, columnType) + @"
END IF;
END IF;
END;
END;
$$";
//Execute query
using (var conn = new NpgsqlConnection(Utils.Constants.CONNECTION_STRING))
{
using (var cmd = new NpgsqlCommand())
{
// Open connection
// ===============================================================
conn.Open();
cmd.Connection = conn;
cmd.CommandText = query;
try { cmd.ExecuteNonQuery(); }
catch (PostgresException ex)
{
conn.Close();
Helpers.ExitWithError(ex.Message);
}
// Close connection
// ===============================================================
conn.Close();
}
}
}
}
/**
* Formats extra columns for formatted insert.
* Detects column type by checking it current postgres table data type
*/
private string FormatExtraGeneratedColumns(int fromNumber, QuandlDatasetData dataset)
{
string extraColumns = "";
for (int i = 0; i < dataset.ColumnNames.Count; i++)
{
string column = dataset.ColumnNames.ElementAt(i);
extraColumns += ", " + PrepareExtraColumnFormated(column, fromNumber);
fromNumber++;
}
return(extraColumns);
}
private Task[] CreateDataQueryThreads(QuandlDatasetData item, int elementsPerThread)
{
var tasks = new List <Task>();
int threadsNeeded = GetThreadsNeeded(item.Data.Count, elementsPerThread);
int remainingElements = item.Data.Count;
int from = 0;
for (int i = 0; i < threadsNeeded; i++)
{
int fromIndex = from;
int toIndex = fromIndex + elementsPerThread;
tasks.Add(Task.Factory.StartNew(() => CreatePartialDataQuery(item, fromIndex, toIndex)));
from += elementsPerThread;
}
return(tasks.ToArray());
}
private object GetSampleDataOnIndex(int index, QuandlDatasetData dataset)
{
// Check if this data is not null and if it has data
if (dataset == null || dataset.Data.Count > 0)
{
foreach (object[] data in dataset.Data)
{
// Verify if it contains and if the value is not null
if (data.Count() < index || data.ElementAt(index) == null)
{
continue;
}
return(data.ElementAt(index));
}
}
// Return text by default
return("");
}
/**
* Formats extra columns for formatted insert.
* Detects column type by checking @data argument data type
*/
private string FormatExtraColumns(int fromNumber, QuandlDatasetData dataset)
{
string extraColumns = "";
for (int i = 0; i < dataset.ColumnNames.Count; i++)
{
string column = dataset.ColumnNames.ElementAt(i);
// Here maybe should check if the value is null
// If its null then keep checking to the next data[] element
// F. ex: ((QuandlDatasetData)Datasets.ElementAt(0)).Data.ElementAt(1...inf).ElementAt(i)
// Until it get a non null value
//dynamic data = ((QuandlDatasetData)Datasets.ElementAt(0)).Data.ElementAt(0).ElementAt(i);
// Already done what above comment says
dynamic data = GetSampleDataOnColumn(column);
extraColumns += ", " + PrepareExtraColumnFormated(data, column, fromNumber);
fromNumber++;
}
return(extraColumns);
}
private void CreatePartialDataQuery(QuandlDatasetData dataset, int from, int to)
{
// Verify the columns exists
createExtraColumnsIfNeeded(dataset);
string query = @"WITH data(" + dataset.GetColumnsForInsertDataQuery() + @") as ( values";
if (to > dataset.Data.Count)
{
to = dataset.Data.Count;
}
for (int i = from; i < to; i++)
{
// Current
object[] data = dataset.Data[i];
// Base insert
query += String.Format(@"('{0}', '{1}', '{2}', '{3}', {4}, date_trunc('second', current_timestamp)",
dataset.DatasetCode, dataset.DatabaseCode, dataset.Name, // 0 - 2
dataset.Transform, // 3
dataset.DatabaseId); // 4
// Mod data
MakeDateTimeStamp(ref data);
// Extra columns
string format = FormatExtraGeneratedColumns(0, dataset);
query += String.Format(format + " ),", data);
}
// Remove last comma ","
query = query.Remove(query.Length - 1);
query += ")"; // Close (values ... )
query += "\nINSERT INTO quandl." + DatabaseCode + "(" + dataset.GetColumnsForInsertDataQuery() + ")" +
" SELECT " + dataset.GetColumnsForInsertDataQuery() +
" FROM data";
if (HasColumnDate())
{
query += @" WHERE NOT EXISTS (
SELECT 1 FROM quandl." + DatabaseCode + @" ds
WHERE ds." + GetColumnDate() + @" = data." + GetColumnDate() + @" AND
ds.datasetcode = data.datasetcode
)";
}
// Execute query
using (var conn = new NpgsqlConnection(Utils.Constants.CONNECTION_STRING))
{
using (var cmd = new NpgsqlCommand())
{
// Open connection
// ===============================================================
conn.Open();
cmd.Connection = conn;
cmd.CommandText = query;
try { cmd.ExecuteNonQuery(); }
catch (PostgresException ex)
{
// Data type mismatch
//if (ex.SqlState == "42804")
conn.Close();
Utils.ConsoleInformer.Inform("Some unexpected stuff happened. See the log for more info");
Utils.Helpers.Log("Failed to insert data chunk.\n-------------------\nStart query:\n" + query + "\n-------------------\nEnd query\n", ex.Message);
}
// Close connection
// ===============================================================
conn.Close();
}
}
}