Пример #1
0
        // Consructors

        protected override void ExtractTableIndexes(ExtractionContext context)
        {
            var tableMap     = context.TableMap;
            var tableColumns = context.TableColumnMap;

            if (tableMap.Count > 0)
            {
                var tableSpacesTable = PgTablespace;
                var relationsTable   = PgClass;
                var indexTable       = PgIndex;
                var dependencyTable  = PgDepend;

                //subselect that index was not created automatically
                var subSelect = SqlDml.Select(dependencyTable);
                subSelect.Where = dependencyTable["classid"] == PgClassOid &&
                                  dependencyTable["objid"] == indexTable["indexrelid"] &&
                                  dependencyTable["deptype"] == 'i';
                subSelect.Columns.Add(dependencyTable[0]);

                //not automatically created indexes of our tables
                var select = SqlDml.Select(indexTable
                                           .InnerJoin(relationsTable, relationsTable["oid"] == indexTable["indexrelid"])
                                           .LeftOuterJoin(tableSpacesTable, tableSpacesTable["oid"] == relationsTable["reltablespace"]));
                select.Where = SqlDml.In(indexTable["indrelid"], CreateOidRow(tableMap.Keys)) && !SqlDml.Exists(subSelect);
                select.Columns.Add(indexTable["indrelid"]);
                select.Columns.Add(indexTable["indexrelid"]);
                select.Columns.Add(relationsTable["relname"]);
                select.Columns.Add(indexTable["indisunique"]);
                select.Columns.Add(indexTable["indisclustered"]);
                select.Columns.Add(indexTable["indkey"]);
                select.Columns.Add(tableSpacesTable["spcname"]);
                select.Columns.Add(indexTable["indnatts"]);
                select.Columns.Add(SqlDml.FunctionCall("pg_get_expr", indexTable["indexprs"], indexTable["indrelid"], true), "indexprstext");
                select.Columns.Add(SqlDml.FunctionCall("pg_get_expr", indexTable["indpred"], indexTable["indrelid"], true), "indpredtext");
                select.Columns.Add(SqlDml.FunctionCall("pg_get_indexdef", indexTable["indexrelid"]), "inddef");
                AddSpecialIndexQueryColumns(select, tableSpacesTable, relationsTable, indexTable, dependencyTable);

                int maxColumnNumber = 0;
                using (var command = Connection.CreateCommand(select))
                    using (var dataReader = command.ExecuteReader()) {
                        while (dataReader.Read())
                        {
                            var tableIdentifier = Convert.ToInt64(dataReader["indrelid"]);
                            var indexIdentifier = Convert.ToInt64(dataReader["indexrelid"]);
                            var indexName       = dataReader["relname"].ToString();
                            var isUnique        = dataReader.GetBoolean(dataReader.GetOrdinal("indisunique"));
                            var isClustered     = dataReader.GetBoolean(dataReader.GetOrdinal("indisclustered"));
                            var indexKey        = (short[])dataReader["indkey"];

                            var tablespaceName   = (dataReader["spcname"] != DBNull.Value) ? dataReader["spcname"].ToString() : (string)null;
                            var filterExpression = (dataReader["indpredtext"] != DBNull.Value) ? dataReader["indpredtext"].ToString() : string.Empty;

                            var table = tableMap[tableIdentifier];

                            var fullTextRegex = @"(?<=CREATE INDEX \S+ ON \S+ USING (?:gist|gin)(?:\s|\S)*)to_tsvector\('(\w+)'::regconfig, \(*(?:(?:\s|\)|\(|\|)*(?:\(""(\S+)""\)|'\s')::text)+\)";
                            var indexScript   = dataReader["inddef"].ToString();
                            var matches       = Regex.Matches(indexScript, fullTextRegex, RegexOptions.Compiled);
                            if (matches.Count > 0)
                            {
                                // Fulltext index
                                var fullTextIndex = table.CreateFullTextIndex(indexName);
                                foreach (Match match in matches)
                                {
                                    var columnConfigurationName = match.Groups[1].Value;
                                    foreach (Capture capture in match.Groups[2].Captures)
                                    {
                                        var columnName     = capture.Value;
                                        var fullTextColumn = fullTextIndex.Columns[columnName] ?? fullTextIndex.CreateIndexColumn(table.Columns.Single(column => column.Name == columnName));
                                        if (fullTextColumn.Languages[columnConfigurationName] == null)
                                        {
                                            fullTextColumn.Languages.Add(new Language(columnConfigurationName));
                                        }
                                    }
                                }
                            }
                            else
                            {
                                //Regular index
                                var index = table.CreateIndex(indexName);
                                index.IsBitmap  = false;
                                index.IsUnique  = isUnique;
                                index.Filegroup = tablespaceName;
                                if (!string.IsNullOrEmpty(filterExpression))
                                {
                                    index.Where = SqlDml.Native(filterExpression);
                                }

                                // Expression-based index
                                var some = dataReader["indexprstext"];
                                if (some != DBNull.Value)
                                {
                                    context.ExpressionIndexMap[indexIdentifier] = new ExpressionIndexInfo(index, indexKey);
                                    int columnNumber = dataReader.GetInt16(dataReader.GetOrdinal("indnatts"));
                                    if (columnNumber > maxColumnNumber)
                                    {
                                        maxColumnNumber = columnNumber;
                                    }
                                }
                                else
                                {
                                    for (int j = 0; j < indexKey.Length; j++)
                                    {
                                        int colIndex = indexKey[j];
                                        if (colIndex > 0)
                                        {
                                            index.CreateIndexColumn(tableColumns[tableIdentifier][colIndex], true);
                                        }
                                        else
                                        {
                                            int z = 7;
                                            //column index is 0
                                            //this means that this index column is an expression
                                            //which is not possible with SqlDom tables
                                        }
                                    }
                                }
                                ReadSpecialIndexProperties(dataReader, index);
                            }
                        }
                    }

                var expressionIndexMap = context.ExpressionIndexMap;

                if (expressionIndexMap.Count > 0)
                {
                    select = SqlDml.Select(indexTable);
                    select.Columns.Add(indexTable["indrelid"]);
                    select.Columns.Add(indexTable["indexrelid"]);

                    for (int i = 1; i <= maxColumnNumber; i++)
                    {
                        select.Columns.Add(SqlDml.FunctionCall("pg_get_indexdef", indexTable["indexrelid"], i, true), i.ToString());
                    }
                    select.Where = SqlDml.In(indexTable["indexrelid"], SqlDml.Array(expressionIndexMap.Keys.ToArray()));

                    using (var command = Connection.CreateCommand(select))
                        using (var dataReader = command.ExecuteReader()) {
                            while (dataReader.Read())
                            {
                                var exprIndexInfo = expressionIndexMap[Convert.ToInt64(dataReader[1])];
                                for (int j = 0; j < exprIndexInfo.Columns.Length; j++)
                                {
                                    int colIndex = exprIndexInfo.Columns[j];
                                    if (colIndex > 0)
                                    {
                                        exprIndexInfo.Index.CreateIndexColumn(tableColumns[Convert.ToInt64(dataReader[0])][colIndex], true);
                                    }
                                    else
                                    {
                                        exprIndexInfo.Index.CreateIndexColumn(SqlDml.Native(dataReader[(j + 1).ToString()].ToString()));
                                    }
                                }
                            }
                        }
                }
            }
        }
Пример #2
0
 protected static SqlExpression NpgsqlPathAndPolygonCount(SqlExpression expression)
 {
     return(SqlDml.FunctionCall("NPOINTS", expression));
 }
Пример #3
0
 protected SqlExpression DateTimeOffsetExtractDate(SqlExpression timestamp)
 {
     return(SqlDml.FunctionCall("DATE", timestamp));
 }
Пример #4
0
 private static SqlExpression DateTimeToStringIso(SqlExpression dateTime)
 {
     return(SqlDml.FunctionCall("To_Char", dateTime, "YYYY-MM-DD\"T\"HH24:MI:SS"));
 }
Пример #5
0
 protected static SqlExpression NpgsqlBoxExtractWidth(SqlExpression expression)
 {
     return(SqlDml.FunctionCall("WIDTH", expression));
 }
Пример #6
0
 protected static SqlUserFunctionCall DateAddMinute(SqlExpression date, SqlExpression minutes)
 {
     return(SqlDml.FunctionCall("DATEADD", SqlDml.Native("MINUTE"), minutes, date));
 }
Пример #7
0
 protected static SqlUserFunctionCall DateAddMillisecond(SqlExpression date, SqlExpression milliseconds)
 {
     return(SqlDml.FunctionCall("DATEADD", SqlDml.Native("MS"), milliseconds, date));
 }
Пример #8
0
 private static SqlExpression Switchoffset(SqlExpression dateTimeOffset, SqlExpression offset) =>
 SqlDml.FunctionCall("SWITCHOFFSET", dateTimeOffset, offset);
Пример #9
0
 private static SqlUserFunctionCall DateTimeOffsetTimeZoneInMinutes(SqlExpression date) =>
 SqlDml.FunctionCall("DATEPART", SqlDml.Native("TZoffset"), date);
Пример #10
0
 protected static SqlUserFunctionCall DateAddNanosecond(SqlExpression date, SqlExpression nanoseconds) =>
 SqlDml.FunctionCall("DATEADD", SqlDml.Native("NS"), nanoseconds, date);
Пример #11
0
 private static SqlUserFunctionCall ToDateTimeOffset(SqlExpression dateTime, SqlExpression offsetInMinutes) =>
 SqlDml.FunctionCall("TODATETIMEOFFSET", dateTime, offsetInMinutes);
Пример #12
0
 protected static SqlUserFunctionCall BitNot(SqlExpression operand)
 {
     return(SqlDml.FunctionCall("BIN_NOT", operand));
 }
Пример #13
0
 protected static SqlUserFunctionCall BitXor(SqlExpression left, SqlExpression right)
 {
     return(SqlDml.FunctionCall("BIN_XOR", left, right));
 }
Пример #14
0
        public override void Visit(SqlFunctionCall node)
        {
            const double nanosecondsPerSecond = 1000000000.0;

            switch (node.FunctionType)
            {
            case SqlFunctionType.PadLeft:
            case SqlFunctionType.PadRight:
                SqlHelper.GenericPad(node).AcceptVisitor(this);
                return;

            case SqlFunctionType.Rand:
                SqlDml.FunctionCall(translator.Translate(SqlFunctionType.Rand)).AcceptVisitor(this);
                return;

            case SqlFunctionType.Square:
                SqlDml.Power(node.Arguments[0], 2).AcceptVisitor(this);
                return;

            case SqlFunctionType.IntervalConstruct:
                ((node.Arguments[0] / SqlDml.Literal(nanosecondsPerSecond)) * OneSecondInterval).AcceptVisitor(this);
                return;

            case SqlFunctionType.IntervalToMilliseconds:
                SqlHelper.IntervalToMilliseconds(node.Arguments[0]).AcceptVisitor(this);
                return;

            case SqlFunctionType.IntervalToNanoseconds:
                SqlHelper.IntervalToNanoseconds(node.Arguments[0]).AcceptVisitor(this);
                return;

            case SqlFunctionType.IntervalAbs:
                SqlHelper.IntervalAbs(node.Arguments[0]).AcceptVisitor(this);
                return;

            case SqlFunctionType.DateTimeConstruct:
                var newNode = (SqlDml.Literal(new DateTime(2001, 1, 1))
                               + OneYearInterval * (node.Arguments[0] - 2001)
                               + OneMonthInterval * (node.Arguments[1] - 1)
                               + OneDayInterval * (node.Arguments[2] - 1));
                newNode.AcceptVisitor(this);
                return;

            case SqlFunctionType.DateTimeTruncate:
                (SqlDml.FunctionCall("date_trunc", "day", node.Arguments[0])).AcceptVisitor(this);
                return;

            case SqlFunctionType.DateTimeAddMonths:
                (node.Arguments[0] + node.Arguments[1] * OneMonthInterval).AcceptVisitor(this);
                return;

            case SqlFunctionType.DateTimeAddYears:
                (node.Arguments[0] + node.Arguments[1] * OneYearInterval).AcceptVisitor(this);
                return;

            case SqlFunctionType.DateTimeToStringIso:
                DateTimeToStringIso(node.Arguments[0]).AcceptVisitor(this);
                return;

                /*
                 * case SqlFunctionType.DateTimeOffsetTimeOfDay:
                 * DateTimeOffsetTimeOfDay(node.Arguments[0]).AcceptVisitor(this);
                 * return;
                 * case SqlFunctionType.DateTimeOffsetAddMonths:
                 * SqlDml.Cast(node.Arguments[0] + node.Arguments[1] * OneMonthInterval, SqlType.DateTimeOffset).AcceptVisitor(this);
                 * return;
                 * case SqlFunctionType.DateTimeOffsetAddYears:
                 * SqlDml.Cast(node.Arguments[0] + node.Arguments[1] * OneYearInterval, SqlType.DateTimeOffset).AcceptVisitor(this);
                 * return;
                 * case SqlFunctionType.DateTimeOffsetConstruct:
                 * ConstructDateTimeOffset(node.Arguments[0], node.Arguments[1]).AcceptVisitor(this);
                 * return;
                 * case SqlFunctionType.DateTimeToDateTimeOffset:
                 * SqlDml.Cast(node.Arguments[0], SqlType.DateTimeOffset).AcceptVisitor(this);
                 * return;
                 */
            }
            base.Visit(node);
        }
Пример #15
0
 protected static SqlUserFunctionCall DateAddDay(SqlExpression date, SqlExpression days)
 {
     return(SqlDml.FunctionCall("DATEADD", SqlDml.Native("DAY"), days, date));
 }
Пример #16
0
 private static SqlExpression DateTimeToDateTimeOffset(SqlExpression dateTime) =>
 SqlDml.FunctionCall("TODATETIMEOFFSET",
                     dateTime,
                     SqlDml.FunctionCall("DATEPART",
                                         SqlDml.Native("TZoffset"),
                                         SqlDml.Native("SYSDATETIMEOFFSET()")));
Пример #17
0
 protected static SqlUserFunctionCall DateAddHour(SqlExpression date, SqlExpression hours)
 {
     return(SqlDml.FunctionCall("DATEADD", SqlDml.Native("HOUR"), hours, date));
 }
Пример #18
0
 protected static SqlUserFunctionCall DateDiffNanosecond(SqlExpression date1, SqlExpression date2) =>
 SqlDml.FunctionCall("DATEDIFF", SqlDml.Native("NS"), date1, date2);
Пример #19
0
 protected static SqlUserFunctionCall DateAddSecond(SqlExpression date, SqlExpression seconds)
 {
     return(SqlDml.FunctionCall("DATEADD", SqlDml.Native("SECOND"), seconds, date));
 }
Пример #20
0
        /// <inheritdoc/>
        public override void Visit(SqlFunctionCall node)
        {
            switch (node.FunctionType)
            {
            case SqlFunctionType.CharLength:
                (SqlDml.FunctionCall("DATALENGTH", node.Arguments) / 2).AcceptVisitor(this);
                return;

            case SqlFunctionType.PadLeft:
            case SqlFunctionType.PadRight:
                GenericPad(node).AcceptVisitor(this);
                return;

            case SqlFunctionType.Round:
                // Round should always be called with 2 arguments
                if (node.Arguments.Count == 1)
                {
                    Visit(SqlDml.FunctionCall(
                              translator.Translate(SqlFunctionType.Round),
                              node.Arguments[0],
                              SqlDml.Literal(0)));
                    return;
                }
                break;

            case SqlFunctionType.Truncate:
                // Truncate is implemented as round(arg, 0, 1) call in MSSQL.
                // It's stupid, isn't it?
                Visit(SqlDml.FunctionCall(
                          translator.Translate(SqlFunctionType.Round),
                          node.Arguments[0],
                          SqlDml.Literal(0),
                          SqlDml.Literal(1)));
                return;

            case SqlFunctionType.Substring:
                if (node.Arguments.Count == 2)
                {
                    node = SqlDml.Substring(node.Arguments[0], node.Arguments[1]);
                    SqlExpression len = SqlDml.CharLength(node.Arguments[0]);
                    node.Arguments.Add(len);
                    Visit(node);
                    return;
                }
                break;

            case SqlFunctionType.IntervalToMilliseconds:
                Visit(CastToLong(node.Arguments[0]) / NanosecondsPerMillisecond);
                return;

            case SqlFunctionType.IntervalConstruct:
            case SqlFunctionType.IntervalToNanoseconds:
                Visit(CastToLong(node.Arguments[0]));
                return;

            case SqlFunctionType.DateTimeAddMonths:
                Visit(DateAddMonth(node.Arguments[0], node.Arguments[1]));
                return;

            case SqlFunctionType.DateTimeAddYears:
                Visit(DateAddYear(node.Arguments[0], node.Arguments[1]));
                return;

            case SqlFunctionType.DateTimeTruncate:
                DateTimeTruncate(node.Arguments[0]).AcceptVisitor(this);
                return;

            case SqlFunctionType.DateTimeConstruct:
                Visit(DateAddDay(DateAddMonth(DateAddYear(SqlDml.Literal(new DateTime(2001, 1, 1)),
                                                          node.Arguments[0] - 2001),
                                              node.Arguments[1] - 1),
                                 node.Arguments[2] - 1));
                return;

            case SqlFunctionType.DateTimeToStringIso:
                Visit(DateTimeToStringIso(node.Arguments[0]));
                return;
            }

            base.Visit(node);
        }
Пример #21
0
 protected static SqlUserFunctionCall DateTimeToStringIso(SqlExpression dateTime)
 {
     return(SqlDml.FunctionCall("CONVERT", SqlDml.Native("NVARCHAR(19)"), dateTime, SqlDml.Native("126")));
 }
Пример #22
0
 protected static SqlUserFunctionCall DatePartWeekDay(SqlExpression date)
 {
     return(SqlDml.FunctionCall("DATEPART", SqlDml.Native("WEEKDAY"), date));
 }
Пример #23
0
 protected static SqlExpression NpgsqlBoxExtractHeight(SqlExpression expression)
 {
     return(SqlDml.FunctionCall("HEIGHT", expression));
 }
Пример #24
0
 protected static SqlUserFunctionCall DateDiffMillisecond(SqlExpression date1, SqlExpression date2)
 {
     return(SqlDml.FunctionCall("DATEDIFF", SqlDml.Native("MS"), date1, date2));
 }
Пример #25
0
 protected static SqlExpression NpgsqlCircleExtractRadius(SqlExpression expression)
 {
     return(SqlDml.FunctionCall("RADIUS", expression));
 }
Пример #26
0
 protected static SqlUserFunctionCall DateAddYear(SqlExpression date, SqlExpression years)
 {
     return(SqlDml.FunctionCall("DATEADD", SqlDml.Native("YEAR"), years, date));
 }
Пример #27
0
 protected static SqlExpression NpgsqlPathAndPolygonOpen(SqlExpression expression)
 {
     return(SqlDml.FunctionCall("ISOPEN", expression));
 }
Пример #28
0
 protected static SqlUserFunctionCall DateAddMonth(SqlExpression date, SqlExpression months)
 {
     return(SqlDml.FunctionCall("DATEADD", SqlDml.Native("MONTH"), months, date));
 }
Пример #29
0
 protected SqlExpression GetDateTimeAsStringExpression(SqlExpression dateTimeExpression)
 {
     return(SqlDml.FunctionCall("To_Char", dateTimeExpression, "YYYY-MM-DD\"T\"HH24:MI:SS.MS"));
 }
 private static SqlExpression OracleBlobCompare(SqlExpression left, SqlExpression right)
 {
     return(SqlDml.FunctionCall("dbms_lob.compare", left, right));
 }