private static void CreateQuandlSchema()
{
using (var conn = new NpgsqlConnection(Constants.CONNECTION_STRING))
{
using (var cmd = new NpgsqlCommand())
{
// Open connection
// ===============================================================
conn.Open();
// Query
string query = @"CREATE SCHEMA quandl;";
cmd.Connection = conn;
cmd.CommandText = query;
try { cmd.ExecuteNonQuery(); }
catch (PostgresException ex)
{
if (ex.SqlState == "42P06")
{
ConsoleInformer.Inform("Quandl shcema already exists. Using it");
}
else
{
conn.Close(); Helpers.ExitWithError(ex.Message);
}
}
// Close connection
// ===============================================================
conn.Close();
}
}
}
static void Main(string[] args)
{
IVisualizer visualizator = new ConsoleInformer();
Controller cntrl = new Controller(args, visualizator);
cntrl.Start();//.
}
public static void InsertQuandlDatabases(List <QuandlDatabase> databases)
{
using (var conn = new NpgsqlConnection(Utils.Constants.CONNECTION_STRING))
{
using (var cmd = new NpgsqlCommand())
{
// Open connection
// ===============================================================
conn.Open();
// Query
string query = QuandlDatabasesInsertQuery(databases);
cmd.Connection = conn;
cmd.CommandText = query;
try { cmd.ExecuteNonQuery(); }
catch (PostgresException ex)
{
conn.Close();
Helpers.ExitWithError(ex.Message);
}
ConsoleInformer.PrintProgress("2A", "Inserting new quandl databases: ", "100%");
// Close connection
// ===============================================================
conn.Close();
}
}
}
public static void MakeDatabase()
{
Console.WriteLine("Creating inital schema\n---------------------------------------");
InitPostgresDB();
ConsoleInformer.PrintProgress("0A", "Creating schema: ", "50%");
CreateQuandlSchema();
ConsoleInformer.PrintProgress("0A", "Creating schema: ", "75%");
CreateQuandlDatabasesTable();
ConsoleInformer.PrintProgress("0A", "Creating schema: ", "100%");
}
// Where all the datasets data will go
public static void CreateQuandlDatasetDataTable(QuandlDatasetGroup datasetGroup)
{
// Do not make data tables without date colum
if (!datasetGroup.HasColumnDate())
{
return;
}
using (var conn = new NpgsqlConnection(Constants.CONNECTION_STRING))
{
using (var cmd = new NpgsqlCommand())
{
// Open connection
// ===============================================================
conn.Open();
// Query
string query = @"CREATE TABLE quandl." + datasetGroup.DatabaseCode + @"(
DatasetCode TEXT,
DatabaseCode TEXT,
Name TEXT NOT NULL,
Transform TEXT,
DatabaseId BIGINT,
date_insert timestamptz," +
// Column names [specific data]
//datasetGroup.MakeDatasetsExtraColumnsWithDataType() + @",
datasetGroup.MakePrimaryKeysForCreate() + @",
PRIMARY KEY(" + string.Join(", ", datasetGroup.PrimaryKeys()) + @")
);";
cmd.Connection = conn;
cmd.CommandText = query;
try { cmd.ExecuteNonQuery(); }
catch (PostgresException ex)
{
//Console.WriteLine(ex.Message);
if (ex.SqlState == "42P07")
{
createDateInsertColumnIfNeeded("quandl." + datasetGroup.DatabaseCode);
ConsoleInformer.Inform("Table model [" + datasetGroup.DatabaseCode + "] already exists. Using it");
}
else
{
conn.Close(); Helpers.ExitWithError(ex.Message);
}
}
ConsoleInformer.PrintProgress("2C", "[" + datasetGroup.DatabaseCode + "] Creating table model: ", "100%");
// Close connection
// ===============================================================
conn.Close();
}
}
}
private static void CreateQuandlDatabasesTable()
{
using (var conn = new NpgsqlConnection(Constants.CONNECTION_STRING))
{
using (var cmd = new NpgsqlCommand())
{
// Open connection
// ===============================================================
conn.Open();
// Query
string query = @"CREATE TABLE quandl.Databases(
Id BIGINT PRIMARY KEY NOT NULL,
Name TEXT NOT NULL,
DatabaseCode TEXT,
Description TEXT,
DatasetsCount BIGINT,
Downloads BIGINT,
Premium BOOL DEFAULT FALSE,
Image TEXT,
Favorite BOOL DEFAULT FALSE,
Import BOOL DEFAULT FALSE,
date_insert timestamptz
);";
cmd.Connection = conn;
cmd.CommandText = query;
try { cmd.ExecuteNonQuery(); }
catch (PostgresException ex)
{
//Console.WriteLine(ex.Message);
if (ex.SqlState == "42P07")
{
createDateInsertColumnIfNeeded("quandl.Databases");
ConsoleInformer.Inform("QuandlDatabases table already exists. Using it");
//cmd.CommandText = "TRUNCATE TABLE databases";
//try { cmd.ExecuteNonQuery(); }
//catch (PostgresException exception)
//{
// conn.Close(); Helpers.ExitWithError(exception.Message);
//}
}
else
{
conn.Close(); Helpers.ExitWithError(ex.Message);
}
}
// Close connection
// ===============================================================
conn.Close();
}
}
}
// Where all the datasets will go
public static void CreateQuandlDatasetTable()
{
using (var conn = new NpgsqlConnection(Constants.CONNECTION_STRING))
{
using (var cmd = new NpgsqlCommand())
{
// Open connection
// ===============================================================
conn.Open();
// Query
string query = @"CREATE TABLE quandl.datasets (
Id BIGINT PRIMARY KEY NOT NULL,
DatasetCode TEXT,
DatabaseCode TEXT,
Name TEXT NOT NULL,
Description TEXT,
NewestAvailableDate DATE,
OldestAvailableDate DATE,
ColumnNames TEXT,
Frequency TEXT,
Type TEXT,
Premium BOOL DEFAULT FALSE,
DatabaseId BIGINT,
Import BOOL DEFAULT FALSE,
date_insert timestamptz
);";
cmd.Connection = conn;
cmd.CommandText = query;
try { cmd.ExecuteNonQuery(); }
catch (PostgresException ex)
{
//Console.WriteLine(ex.Message);
if (ex.SqlState == "42P07")
{
createDateInsertColumnIfNeeded("quandl.datasets");
ConsoleInformer.Inform("Datasets table model already exists. Using it");
}
else
{
conn.Close(); Helpers.ExitWithError(ex.Message);
}
}
ConsoleInformer.PrintProgress("2B", "Creating datasets table: ", "100%");
// Close connection
// ===============================================================
conn.Close();
}
}
}
// Create datatables data model
public static void CreateQuandlDatatableModelTable(QuandlDatatable datatable)
{
// Do not make data tables without name
if (String.IsNullOrWhiteSpace(datatable.Name))
{
return;
}
using (var conn = new NpgsqlConnection(Constants.CONNECTION_STRING))
{
using (var cmd = new NpgsqlCommand())
{
// Open connection
// ===============================================================
conn.Open();
// Query
string query = @"CREATE TABLE quandl.""" + datatable.Name + @"""(" +
// Column names [specific data]
datatable.MakeExtraColumnsWithDataType() + @",
date_insert timestamptz
);";
cmd.Connection = conn;
cmd.CommandText = query;
try { cmd.ExecuteNonQuery(); }
catch (PostgresException ex)
{
//Console.WriteLine(ex.Message);
if (ex.SqlState == "42P07")
{
createDateInsertColumnIfNeeded(@"quandl.""" + datatable.Name + @"""");
ConsoleInformer.Inform("Table model [" + datatable.Name + "] already exists. Truncating...");
cmd.CommandText = @"TRUNCATE TABLE quandl.""" + datatable.Name + @"""";
try { cmd.ExecuteNonQuery(); }
catch (PostgresException exception) { conn.Close(); Helpers.ExitWithError(exception.Message); }
}
else
{
conn.Close(); Helpers.ExitWithError(ex.Message);
}
}
ConsoleInformer.PrintProgress("3D", "[" + datatable.Name + "] Creating table model: ", "100%");
// Close connection
// ===============================================================
conn.Close();
}
}
}
// Метод главного меню программы
internal static void MainMenu()
{
while (true)
{
Console.Clear();
Console.WriteLine("(i) Input the menu item number.\n1. Send message via console\n2. Bot info\n3. Log\n4. Exit");
Console.Write("Input: ");
int menuItem;
while (true)
{
bool isCorrect = Int32.TryParse(Console.ReadLine(), out menuItem);
if (isCorrect)
{
break;
}
ConsoleInformer.ErrorMessage("Incorrect input. Please try again.");
Console.Write("Input: ");
}
Console.Clear();
switch (menuItem)
{
case 1:
Program.SendConsoleMessage();
Console.ReadKey();
break;
case 2:
ConsoleInformer.PrintBotInfo(Program.Bot);
Console.ReadKey();
break;
case 3:
LogMenu();
break;
case 4:
return; // Выход из программы
default:
ConsoleInformer.ErrorMessage("Non-existent menu item.");
Console.ReadKey();
break;
}
}
}
// Метод меню для взаимодействия с журналом
internal static void LogMenu()
{
while (true)
{
Console.Clear();
Console.WriteLine("(i) Input the menu item number.\n1. Get log\n2. Clear log\n3. Back");
Console.Write("Input: ");
int menuItem;
while (true)
{
bool isCorrect = Int32.TryParse(Console.ReadLine(), out menuItem);
if (isCorrect)
{
break;
}
ConsoleInformer.ErrorMessage("Incorrect input. Please try again.");
Console.Write("Input: ");
}
Console.Clear();
switch (menuItem)
{
case 1:
Logger.ReadLog();
Console.WriteLine("(i) Press 'Enter' to go back...\n");
Console.ReadKey();
break;
case 2:
Logger.ClearLog();
Console.WriteLine("(i) Log cleared.");
Console.WriteLine("(i) Press 'Enter' to go back...\n");
Console.ReadKey();
break;
case 3:
return;
default:
ConsoleInformer.ErrorMessage("Non-existent menu item.");
Console.ReadKey();
break;
}
}
}
public static void InsertQuandlDatasetGroup(QuandlDatasetGroup datasetGroup)
{
try
{
datasetGroup.MakeInsertQuery();
ConsoleInformer.PrintProgress("3B", "Inserting datasets for [" + datasetGroup.DatabaseCode + "]");
}
catch (Exception ex)
{
// Write
Utils.ConsoleInformer.Inform("Some unexpected stuff happened when inserting [" + datasetGroup.DatabaseCode + "]. See the log for more info");
Utils.Helpers.Log("Something worng happened when trying to insert [" + datasetGroup.DatabaseCode + "] datasets. Check log", ex.Message);
}
}
// Create datatable table
public static void CreateQuandlDatatablesTable()
{
using (var conn = new NpgsqlConnection(Constants.CONNECTION_STRING))
{
using (var cmd = new NpgsqlCommand())
{
// Open connection
// ===============================================================
conn.Open();
// Query
string query = @"CREATE TABLE quandl.datatables(
Name TEXT PRIMARY KEY NOT NULL,
Import BOOL DEFAULT FALSE,
date_insert timestamptz
);";
cmd.Connection = conn;
cmd.CommandText = query;
try { cmd.ExecuteNonQuery(); }
catch (PostgresException ex)
{
//Console.WriteLine(ex.Message);
if (ex.SqlState == "42P07")
{
createDateInsertColumnIfNeeded("quandl.datatables");
ConsoleInformer.Inform("QuandlDatatables table already exists. Using it");
//cmd.CommandText = "TRUNCATE TABLE databases";
//try { cmd.ExecuteNonQuery(); }
//catch (PostgresException exception)
//{
// conn.Close(); Helpers.ExitWithError(exception.Message);
//}
}
else
{
conn.Close(); Helpers.ExitWithError(ex.Message);
}
}
// Close connection
// ===============================================================
conn.Close();
// Inform
ConsoleInformer.PrintProgress("0D", "Creating Datatables Table schema", "100%");
}
}
}
public static List <QuandlDatabase> GetImportedDatabases()
{
// Query
string query = @"SELECT id, name, databasecode, description, datasetscount, downloads, premium, image, favorite, import
FROM quandl.databases
WHERE import = true";
List <QuandlDatabase> databases = new List <QuandlDatabase>();
using (var conn = new NpgsqlConnection(Constants.CONNECTION_STRING))
{
using (var cmd = new NpgsqlCommand(query))
{
// Open connection
// ===============================================================
conn.Open();
cmd.Connection = conn;
try
{
// Execute the query and obtain a result set
NpgsqlDataReader dr = cmd.ExecuteReader();
// Each row
while (dr.Read())
{
databases.Add(QuandlDatabase.MakeQuandlDatabase(dr));
}
}
catch (Exception ex)
{
conn.Close();
Helpers.ExitWithError(ex.Message);
}
ConsoleInformer.PrintProgress("0B", "Querying imported databases: ", "100%");
// Close connection
// ===============================================================
conn.Close();
}
}
return(databases);
}
private static void InitPostgresDB()
{
using (var conn = new NpgsqlConnection(Constants.BASE_CONNECTION_STRING))
{
using (var cmd = new NpgsqlCommand())
{
// Open connection
// ===============================================================
conn.Open();
// Query
string query = @"CREATE DATABASE quandl
WITH
OWNER = postgres
ENCODING = 'UTF8'
CONNECTION LIMIT = -1;
COMMENT ON DATABASE quandl
IS '
';";
cmd.Connection = conn;
cmd.CommandText = query;
try { cmd.ExecuteNonQuery(); }
catch (PostgresException ex)
{
if (ex.SqlState == "42P04")
{
ConsoleInformer.Inform("Database already exist. Using it");
}
else
{
conn.Close(); Helpers.ExitWithError(ex.Message);
}
}
// Close connection
// ===============================================================
conn.Close();
}
}
}
/**
* Dataset info methos
*/
public static void InsertQuandlDatasets(List <QuandlDatasetGroup> datasetsGroups)
{
// Make datasets model tables
SchemaActions.CreateQuandlDatasetTable();
// Insert datasets
int count = 0;
foreach (QuandlDatasetGroup datasetGroup in datasetsGroups)
{
count++;
try
{
datasetGroup.MakeInsertQuery();
ConsoleInformer.PrintProgress("3B", "Inserting [" + datasetGroup.DatabaseCode + "] datasets: ", Utils.Helpers.GetPercent(count, datasetsGroups.Count).ToString() + "%");
}
catch (Exception ex)
{
// Write
Utils.ConsoleInformer.Inform("Some unexpected stuff happened. See the log for more info");
Utils.Helpers.Log("Something worng happened when trying to insert [" + datasetGroup.DatabaseCode + "] datasets. Check log", ex.Message);
}
}
}
/**
* Dataset data methos
*/
public static List <QuandlDatasetGroup> GetImportedDatasets()
{
// This query does not takes in count if the de dataset's database is imported too
//string query = @"SELECT " + QuandlDataset.GetColumnsForQuery() + " " +
// @"FROM quandl.datasets
// WHERE import = true";
// Query
string query = @"SELECT " + QuandlDataset.GetColumnsForQuerySuffixed("ds") + @"
FROM quandl.databases INNER JOIN quandl.datasets ds ON (quandl.databases.databasecode = ds.databasecode)
WHERE quandl.databases.import = true AND ds.import = true";
List <QuandlDatasetGroup> datasetsGroups = new List <QuandlDatasetGroup>();
using (var conn = new NpgsqlConnection(Constants.CONNECTION_STRING))
{
using (var cmd = new NpgsqlCommand(query))
{
// Open connection
// ===============================================================
conn.Open();
cmd.Connection = conn;
try
{
// Execute the query and obtain a result set
NpgsqlDataReader dr = cmd.ExecuteReader();
// Each row
while (dr.Read())
{
// Add each dataset to its own group (Database code)
QuandlDataset dataset = QuandlDataset.MakeQuandlDataset(dr);
// If group doesnt exists, create it
if (!datasetsGroups.Exists(d => d.DatabaseCode == dataset.DatabaseCode))
{
datasetsGroups.Add(new QuandlDatasetGroup()
{
DatabaseCode = dataset.DatabaseCode, Datasets = new List <QuandlDataset>()
});
}
datasetsGroups.Find(d => d.DatabaseCode == dataset.DatabaseCode).Datasets.Add(dataset);
}
}
catch (Exception ex)
{
conn.Close();
Helpers.ExitWithError(ex.Message);
}
ConsoleInformer.PrintProgress("0C", "Querying imported datasets: ", "100%");
// Close connection
// ===============================================================
conn.Close();
}
}
return(datasetsGroups);
}
