示例#1
0
        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();
                    }
                }
            }
        }
示例#2
0
        /**
         * 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);
        }
示例#3
0
        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());
        }
示例#4
0
        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("");
        }
示例#5
0
        /**
         * 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);
        }
示例#6
0
        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();
                }
            }
        }