private void CreatePartialQueryFile(int from, int to)
{
if (to > Datasets.Count)
{
to = Datasets.Count;
}
string query = "";
for (int i = from; i < to; i++)
{
// Current
QuandlDataset item = Datasets[i];
// Base insert
query += String.Format(@"({0}, '{1}', '{2}', '{3}', '{4}', to_date('{5}', 'YYYY-MM_DD'), to_date('{6}', 'YYYY-MM_DD'), '{7}', '{8}', '{9}', {10}, {11}, {12})",
item.Id, item.DatasetCode, item.DatabaseCode, item.Name, item.Description, // 0 - 4
item.NewestAvailableDate.GetValueOrDefault(DateTime.Now).ToString("yyyy-MM-dd"), item.OldestAvailableDate.GetValueOrDefault(DateTime.Now).ToString("yyyy-MM-dd"), // 5 - 6
string.Join(",", item.ColumnNames), // 7
item.Frequency, item.Type, // 8 - 9
item.Premium, item.DatabaseId, item.Import); // 10 - 12
query += ",";
}
// Write partial query to file
WriteToQueryFile(query);
}
public static QuandlDataset MakeQuandlDataset(NpgsqlDataReader row)
{
var dataset = new QuandlDataset()
{
Id = (long)row["id"],
DatasetCode = (string)row["datasetcode"],
DatabaseCode = (string)row["databasecode"],
Name = (string)row["name"],
Description = (string)row["description"],
NewestAvailableDate = row.GetDateTime(row.GetOrdinal("newestavailabledate")),
OldestAvailableDate = row.GetDateTime(row.GetOrdinal("oldestavailabledate")),
//NewestAvailableDate = (DateTime)row["newestavailabledate"],
//OldestAvailableDate = (DateTime)row["oldestavailabledate"],
ColumnNames = row["columnnames"].ToString()
.Split(',')
.Select(x => x.Trim())
.Where(x => !string.IsNullOrWhiteSpace(x))
.ToList(),
Frequency = (string)row["frequency"],
Type = (string)row["type"],
Premium = (bool)row["premium"],
DatabaseId = (long)row["databaseid"],
Import = (bool)row["import"]
};
return(dataset);
}
private void CreatePartialQuery(int from, int to)
{
if (to > Datasets.Count)
{
to = Datasets.Count;
}
string query = @"WITH data(" + QuandlDataset.GetColumnsForQuery() + @") as ( values";
//string query = "";
for (int i = from; i < to; i++)
{
// Current
QuandlDataset item = Datasets[i];
// Base insert
query += String.Format(@"({0}, '{1}', '{2}', '{3}', '{4}', to_date('{5}', 'YYYY-MM_DD'), to_date('{6}', 'YYYY-MM_DD'), '{7}', '{8}', '{9}', {10}, {11}, {12})",
item.Id, item.DatasetCode, item.DatabaseCode, item.Name, item.Description, // 0 - 4
item.NewestAvailableDate.GetValueOrDefault(DateTime.Now).ToString("yyyy-MM-dd"), item.OldestAvailableDate.GetValueOrDefault(DateTime.Now).ToString("yyyy-MM-dd"), // 5 - 6
string.Join(",", item.ColumnNames), // 7
item.Frequency, item.Type, // 8 - 9
item.Premium, item.DatabaseId, item.Import); // 10 - 12
query += ",";
}
// Remove last comma ","
query = query.Remove(query.Length - 1);
query += ")"; // Close (values ... )
query += "\nINSERT INTO quandl.datasets (" + QuandlDataset.GetColumnsForQuery() + ")" +
" SELECT " + QuandlDataset.GetColumnsForQuery() +
" FROM data" +
" WHERE NOT EXISTS (SELECT 1 FROM quandl.datasets ds WHERE ds.Id = data.Id)";
//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();
}
}
}
private void CreatePartialUpdateQuery(int from, int to)
{
if (to > Datasets.Count)
{
to = Datasets.Count;
}
string query = "";
for (int i = from; i < to; i++)
{
// Current
QuandlDataset item = Datasets[i];
// Base insert
query += String.Format("update quandl.datasets set " +
"NewestAvailableDate = to_date('{0}', 'YYYY-MM_DD'), " +
"OldestAvailableDate = to_date('{1}', 'YYYY-MM_DD'), " +
"date_insert = date_trunc('second', current_timestamp)" +
"where id = {2}",
item.NewestAvailableDate.GetValueOrDefault(DateTime.Now).ToString("yyyy-MM-dd"),
item.OldestAvailableDate.GetValueOrDefault(DateTime.Now).ToString("yyyy-MM-dd"),
item.Id);
query += ";\n";
}
//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();
}
}
}
// Creates query to insert dataset
public string MakeInsertQueryFile()
{
// Init bulk query file
InitFile();
// Inital part
string query = @"WITH data(" + QuandlDataset.GetColumnsForQuery() + @") as ( values";
WriteToQueryFile(query);
// Data elements to be formated for each thread
int elementsPerThread = 1000;
// Init where all queries generated will belong
//queries = new List<string>();
// Init taks
var tasks = new List <Task>();
// Create query only if needed
if (Datasets.Count > 0)
{
tasks.AddRange(CreateQueryThreadsFile(elementsPerThread));
}
else
{
Utils.ConsoleInformer.Inform("Database [" + DatabaseCode + "] is already in its last version");
}
// If nothing to do just skip
if (tasks.Count <= 0)
{
return("");
}
// Wait for all the threads to complete
Task.WaitAll(tasks.ToArray());
// Remove last comma ","
RemoveLastCommaInQueryFile();
WriteToQueryFile(")"); // Close (values ... )
WriteToQueryFile("\nINSERT INTO quandl.datasets (" + QuandlDataset.GetColumnsForQuery() + ")" +
" SELECT " + QuandlDataset.GetColumnsForQuery() +
" FROM data" +
" WHERE NOT EXISTS (SELECT 1 FROM quandl.datasets ds WHERE ds.Id = data.Id)");
return(queryFilePath);
}
public void SetBaseDataset(QuandlDataset dataset)
{
Id = dataset.Id;
DatabaseCode = dataset.DatabaseCode;
DatasetCode = dataset.DatasetCode;
Name = dataset.Name;
Description = dataset.Description;
NewestAvailableDate = dataset.NewestAvailableDate;
OldestAvailableDate = dataset.OldestAvailableDate;
// Ignore column names from database
// Use the ones fetched from quandl instead
// Uncomment to use database ones
//ColumnNames = dataset.ColumnNames;
Frequency = dataset.Frequency;
Type = dataset.Type;
Premium = dataset.Premium;
DatabaseId = dataset.DatabaseId;
Import = dataset.Import;
}