/// <inheritdoc/>
protected override ISqlCompileUnit BuildExtractSequencesQuery(ExtractionContext context)
{
var sequenceMap = context.SequenceMap;
// select all the sequences registered in map
var tableRef = PgSequence;
var select = SqlDml.Select(tableRef);
select.Where = SqlDml.In(tableRef["seqrelid"], SqlDml.Array(sequenceMap.Keys.ToArray()));
return(select);
}
private SqlSelect BuildFilteredQuery(IndexInfo index)
{
var underlyingIndex = index.UnderlyingIndexes[0];
var baseQuery = BuildProviderQuery(underlyingIndex);
SqlExpression filter = null;
var type = index.ReflectedType;
var discriminatorMap = type.Hierarchy.TypeDiscriminatorMap;
var filterByTypes = index.FilterByTypes.ToList();
if (underlyingIndex.IsTyped && discriminatorMap != null)
{
var columnType = discriminatorMap.Column.ValueType;
var discriminatorColumnIndex = underlyingIndex.Columns
.Where(c => !c.Field.IsTypeId)
.Select((c, i) => new { c, i })
.Where(p => p.c == discriminatorMap.Column)
.Single().i;
var discriminatorColumn = baseQuery.From.Columns[discriminatorColumnIndex];
var containsDefault = filterByTypes.Contains(discriminatorMap.Default);
var values = filterByTypes
.Select(t => GetDiscriminatorValue(discriminatorMap, t.TypeDiscriminatorValue));
if (filterByTypes.Count == 1)
{
var discriminatorValue = GetDiscriminatorValue(discriminatorMap, filterByTypes.First().TypeDiscriminatorValue);
filter = discriminatorColumn == SqlDml.Literal(discriminatorValue);
}
else
{
filter = SqlDml.In(discriminatorColumn, SqlDml.Array(values));
if (containsDefault)
{
var allValues = discriminatorMap
.Select(p => GetDiscriminatorValue(discriminatorMap, p.First));
filter |= SqlDml.NotIn(discriminatorColumn, SqlDml.Array(allValues));
}
}
}
else
{
var typeIdColumn = baseQuery.Columns[Handlers.Domain.Handlers.NameBuilder.TypeIdColumnName];
var typeIds = filterByTypes.Select(t => TypeIdRegistry[t]).ToArray();
filter = filterByTypes.Count == 1
? typeIdColumn == TypeIdRegistry[filterByTypes.First()]
: SqlDml.In(typeIdColumn, SqlDml.Array(typeIds));
}
var query = SqlDml.Select(baseQuery.From);
query.Columns.AddRange(baseQuery.Columns);
query.Where = filter;
return(query);
}
public void SqlArrayCloneTest()
{
SqlArray <int> a = SqlDml.Array(new int[] { 1, 2, 4 });
SqlArray <int> aClone = (SqlArray <int>)a.Clone();
Assert.AreNotEqual(a, aClone);
Assert.IsTrue(a.Values != aClone.Values);
Assert.AreEqual(a.Values.Length, aClone.Values.Length);
for (int i = 0, l = a.Values.Length; i < l; i++)
{
Assert.AreEqual(a.Values[i], aClone.Values[i]);
}
Assert.AreEqual(a.NodeType, aClone.NodeType);
}
public static SqlExpression DateTimeDaysInMonth(
[Type(typeof(int))] SqlExpression year,
[Type(typeof(int))] SqlExpression month)
{
var februaryCase = SqlDml.Case();
februaryCase.Add(DateTimeIsLeapYear(year), 29);
februaryCase.Else = 28;
var result = SqlDml.Case();
result.Add(SqlDml.In(month, SqlDml.Array(1, 3, 5, 7, 8, 10, 12)), 31);
result.Add(month == 2, februaryCase);
result.Else = 30;
return(result);
}
public void ArrayTest()//TODO: Find reason why this pattern is structured like this.(Malisa)
{
SqlArray <int> i = SqlDml.Array(new int[] { 1, 2 });
i.Values[0] = 10;
SqlSelect select = SqlDml.Select();
select.Where = SqlDml.In(1, i);
MemoryStream ms = new MemoryStream();
BinaryFormatter bf = new BinaryFormatter();
bf.Serialize(ms, select);
ms.Seek(0, SeekOrigin.Begin);
select = (SqlSelect)bf.Deserialize(ms);
Console.WriteLine(SqlDriver.Compile(select).GetCommandText());
}
public void SqlArrayReplacingTest()
{
SqlArray <int> a = SqlDml.Array(new int[] { 1, 2, 4 });
SqlArray <int> aReplacing = SqlDml.Array(new int[] { 1, 2, 4, 8 });
a.ReplaceWith(aReplacing);
bool passed = false;
try {
a.ReplaceWith(1);
}
catch {
passed = true;
}
Assert.IsTrue(passed);
Assert.AreNotEqual(a, aReplacing);
Assert.AreEqual(a.NodeType, aReplacing.NodeType);
Assert.AreEqual(a.Values, aReplacing.Values);
}
protected override void ExtractSequenses(ExtractionContext context)
{
var sequenceMap = context.SequenceMap;
if (sequenceMap.Count > 0)
{
// select all the sequences registered in map
var tableRef = PgSequence;
var select = SqlDml.Select(tableRef);
select.Where = SqlDml.In(tableRef["seqrelid"], SqlDml.Array(sequenceMap.Keys.ToArray()));
using (DbCommand cmd = Connection.CreateCommand(select))
using (DbDataReader dr = cmd.ExecuteReader()) {
while (dr.Read())
{
var seqId = Convert.ToInt64(dr["seqrelid"]);
var sequence = context.SequenceMap[seqId];
ReadSequenceDescriptor(dr, sequence.SequenceDescriptor);
}
}
}
}
private void ProvideExtensionMetadataFilter(SqlDelete delete)
{
var knownExtensions = SqlDml.Array(WellKnown.DomainModelExtensionName, WellKnown.PartialIndexDefinitionsExtensionName);
delete.Where = SqlDml.In(delete.Delete[mapping.ExtensionName], knownExtensions);
}
// 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()));
}
}
}
}
}
}
}