Beispiel #1
0
        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);
        }
Beispiel #2
0
        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);
        }
Beispiel #3
0
        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();
                }
            }
        }
Beispiel #5
0
        // 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;
        }