// 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()));
}
}
}
}
}
}
}
protected static SqlExpression NpgsqlPathAndPolygonCount(SqlExpression expression)
{
return(SqlDml.FunctionCall("NPOINTS", expression));
}
protected SqlExpression DateTimeOffsetExtractDate(SqlExpression timestamp)
{
return(SqlDml.FunctionCall("DATE", timestamp));
}
private static SqlExpression DateTimeToStringIso(SqlExpression dateTime)
{
return(SqlDml.FunctionCall("To_Char", dateTime, "YYYY-MM-DD\"T\"HH24:MI:SS"));
}
protected static SqlExpression NpgsqlBoxExtractWidth(SqlExpression expression)
{
return(SqlDml.FunctionCall("WIDTH", expression));
}
protected static SqlUserFunctionCall DateAddMinute(SqlExpression date, SqlExpression minutes)
{
return(SqlDml.FunctionCall("DATEADD", SqlDml.Native("MINUTE"), minutes, date));
}
protected static SqlUserFunctionCall DateAddMillisecond(SqlExpression date, SqlExpression milliseconds)
{
return(SqlDml.FunctionCall("DATEADD", SqlDml.Native("MS"), milliseconds, date));
}
private static SqlExpression Switchoffset(SqlExpression dateTimeOffset, SqlExpression offset) =>
SqlDml.FunctionCall("SWITCHOFFSET", dateTimeOffset, offset);
private static SqlUserFunctionCall DateTimeOffsetTimeZoneInMinutes(SqlExpression date) =>
SqlDml.FunctionCall("DATEPART", SqlDml.Native("TZoffset"), date);
protected static SqlUserFunctionCall DateAddNanosecond(SqlExpression date, SqlExpression nanoseconds) =>
SqlDml.FunctionCall("DATEADD", SqlDml.Native("NS"), nanoseconds, date);
private static SqlUserFunctionCall ToDateTimeOffset(SqlExpression dateTime, SqlExpression offsetInMinutes) =>
SqlDml.FunctionCall("TODATETIMEOFFSET", dateTime, offsetInMinutes);
protected static SqlUserFunctionCall BitNot(SqlExpression operand)
{
return(SqlDml.FunctionCall("BIN_NOT", operand));
}
protected static SqlUserFunctionCall BitXor(SqlExpression left, SqlExpression right)
{
return(SqlDml.FunctionCall("BIN_XOR", left, right));
}
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);
}
protected static SqlUserFunctionCall DateAddDay(SqlExpression date, SqlExpression days)
{
return(SqlDml.FunctionCall("DATEADD", SqlDml.Native("DAY"), days, date));
}
private static SqlExpression DateTimeToDateTimeOffset(SqlExpression dateTime) =>
SqlDml.FunctionCall("TODATETIMEOFFSET",
dateTime,
SqlDml.FunctionCall("DATEPART",
SqlDml.Native("TZoffset"),
SqlDml.Native("SYSDATETIMEOFFSET()")));
protected static SqlUserFunctionCall DateAddHour(SqlExpression date, SqlExpression hours)
{
return(SqlDml.FunctionCall("DATEADD", SqlDml.Native("HOUR"), hours, date));
}
protected static SqlUserFunctionCall DateDiffNanosecond(SqlExpression date1, SqlExpression date2) =>
SqlDml.FunctionCall("DATEDIFF", SqlDml.Native("NS"), date1, date2);
protected static SqlUserFunctionCall DateAddSecond(SqlExpression date, SqlExpression seconds)
{
return(SqlDml.FunctionCall("DATEADD", SqlDml.Native("SECOND"), seconds, date));
}
/// <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);
}
protected static SqlUserFunctionCall DateTimeToStringIso(SqlExpression dateTime)
{
return(SqlDml.FunctionCall("CONVERT", SqlDml.Native("NVARCHAR(19)"), dateTime, SqlDml.Native("126")));
}
protected static SqlUserFunctionCall DatePartWeekDay(SqlExpression date)
{
return(SqlDml.FunctionCall("DATEPART", SqlDml.Native("WEEKDAY"), date));
}
protected static SqlExpression NpgsqlBoxExtractHeight(SqlExpression expression)
{
return(SqlDml.FunctionCall("HEIGHT", expression));
}
protected static SqlUserFunctionCall DateDiffMillisecond(SqlExpression date1, SqlExpression date2)
{
return(SqlDml.FunctionCall("DATEDIFF", SqlDml.Native("MS"), date1, date2));
}
protected static SqlExpression NpgsqlCircleExtractRadius(SqlExpression expression)
{
return(SqlDml.FunctionCall("RADIUS", expression));
}
protected static SqlUserFunctionCall DateAddYear(SqlExpression date, SqlExpression years)
{
return(SqlDml.FunctionCall("DATEADD", SqlDml.Native("YEAR"), years, date));
}
protected static SqlExpression NpgsqlPathAndPolygonOpen(SqlExpression expression)
{
return(SqlDml.FunctionCall("ISOPEN", expression));
}
protected static SqlUserFunctionCall DateAddMonth(SqlExpression date, SqlExpression months)
{
return(SqlDml.FunctionCall("DATEADD", SqlDml.Native("MONTH"), months, date));
}
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));
}
