/// <summary>
/// API constructor, instead use <see cref="DiscoveredServer.ExpectDatabase"/> instead.
/// </summary>
/// <param name="server"></param>
/// <param name="database"></param>
/// <param name="querySyntaxHelper"></param>
internal DiscoveredDatabase(DiscoveredServer server, string database, IQuerySyntaxHelper querySyntaxHelper)
{
Server = server;
_database = database;
_querySyntaxHelper = querySyntaxHelper;
Helper = server.Helper.GetDatabaseHelper();
}
private string WriteContainers(CohortAggregateContainer container, IQuerySyntaxHelper syntaxHelper,
Dictionary <CohortQueryBuilderDependency, string> sqlDictionary, int tabs)
{
string sql = "";
//Things we need to output
var toWriteOut = container.GetOrderedContents().Where(IsEnabled).ToArray();
if (toWriteOut.Any())
{
sql += Environment.NewLine + TabIn("(", tabs) + Environment.NewLine;
}
else
{
throw new QueryBuildingException($"Container '{container}' is empty, Disable it if you don't want it run'");
}
bool firstEntityWritten = false;
foreach (IOrderable toWrite in toWriteOut)
{
if (firstEntityWritten)
{
sql += Environment.NewLine + TabIn(GetSetOperationSql(container.Operation, syntaxHelper.DatabaseType) + Environment.NewLine + Environment.NewLine, tabs);
}
if (toWrite is AggregateConfiguration)
{
sql += TabIn(sqlDictionary.Single(kvp => Equals(kvp.Key.CohortSet, toWrite)).Value, tabs);
}
if (toWrite is CohortAggregateContainer sub)
{
sql += WriteContainers(sub, syntaxHelper, sqlDictionary, tabs + 1);
}
//we have now written the first thing at this level of recursion - all others will need to be separated by the OPERATION e.g. UNION
firstEntityWritten = true;
if (StopContainerWhenYouReach != null && StopContainerWhenYouReach.Equals(toWrite))
{
if (tabs != 0)
{
throw new NotSupportedException("Stopping prematurely only works when the aggregate to stop at is in the top level container");
}
else
{
break;
}
}
}
//if we outputted anything
if (toWriteOut.Any())
{
sql += Environment.NewLine + TabIn(")", tabs) + Environment.NewLine;
}
return(sql);
}
/// <summary>
/// Attempts to parse the provided <paramref name="sql"/> text into a <see cref="ConstantParameter"/>
/// </summary>
/// <param name="sql"></param>
/// <param name="syntaxHelper"></param>
/// <returns></returns>
public static ConstantParameter Parse(string sql, IQuerySyntaxHelper syntaxHelper)
{
string[] lines = sql.Split(new[] { '\n' }, StringSplitOptions.RemoveEmptyEntries);
string comment = null;
Regex commentRegex = new Regex(@"/\*(.*)\*/");
var matchComment = commentRegex.Match(lines[0]);
if (lines.Length >= 3 && matchComment.Success)
{
comment = matchComment.Groups[1].Value;
}
string declaration = comment == null ? lines[0] : lines[1];
declaration = declaration.TrimEnd(new[] { '\r' });
string valueLine = comment == null ? lines[1] : lines[2];
if (!valueLine.StartsWith("SET"))
{
throw new Exception("Value line did not start with SET:" + sql);
}
var valueLineSplit = valueLine.Split(new[] { '=' });
var value = valueLineSplit[1].TrimEnd(new[] { ';', '\r' });
return(new ConstantParameter(declaration.Trim(), value.Trim(), comment, syntaxHelper));
}
public static DbParameter GetParameter(string paramName, IQuerySyntaxHelper syntaxHelper, DiscoveredColumn discoveredColumn, object value)
{
var p = GetParameter(paramName, syntaxHelper.DatabaseType);
var tt = syntaxHelper.TypeTranslater;
p.DbType = tt.GetDbTypeForSQLDBType(discoveredColumn.DataType.SQLType);
var cSharpType = tt.GetCSharpTypeForSQLDBType(discoveredColumn.DataType.SQLType);
if (syntaxHelper.IsBasicallyNull(value))
{
p.Value = DBNull.Value;
}
else
if (value is string && typeDeciderFactory.IsSupported(cSharpType)) //if the input is a string and it's for a hard type e.g. TimeSpan
{
var o = typeDeciderFactory.Create(cSharpType).Parse((string)value);
//Apparently everyone in Microsoft hates TimeSpans - see test MicrosoftHatesDbTypeTime
if (o is TimeSpan && syntaxHelper.DatabaseType == DatabaseType.MicrosoftSQLServer)
{
o = Convert.ToDateTime(o.ToString());
}
p.Value = o;
}
else
{
p.Value = value;
}
return(p);
}
public ParameterEditorScintillaSection(ParameterRefactorer refactorer, int lineStart, int lineEnd, ISqlParameter parameter, bool editable, string originalText)
{
_refactorer = refactorer;
LineStart = lineStart;
LineEnd = lineEnd;
Parameter = parameter;
Editable = editable;
_querySyntaxHelper = parameter.GetQuerySyntaxHelper();
var prototype = ConstantParameter.Parse(originalText, _querySyntaxHelper);
if (prototype.Value != parameter.Value)
{
throw new ArgumentException("Parameter " + parameter + " was inconsistent with the SQL passed to us based on QueryBuilder.DeconstructStringIntoParameter, they had different Values");
}
if (prototype.ParameterSQL != parameter.ParameterSQL)
{
throw new ArgumentException("Parameter " + parameter + " was inconsistent with the SQL passed to us based on QueryBuilder.DeconstructStringIntoParameter, they had different ParameterSQL");
}
if (prototype.Comment != parameter.Comment)
{
throw new ArgumentException("Parameter " + parameter + " was inconsistent with the SQL passed to us based on QueryBuilder.DeconstructStringIntoParameter, they had different Comment");
}
}
public static List <ConstantParameter> GetConstantParameters(IQuerySyntaxHelper syntaxHelper, IExtractionConfiguration configuration, IExtractableCohort extractableCohort)
{
List <ConstantParameter> toReturn = new List <ConstantParameter>();
if (syntaxHelper.DatabaseType == FAnsi.DatabaseType.Oracle)
{
return(toReturn);
}
IProject project = configuration.Project;
if (project.ProjectNumber == null)
{
throw new ProjectNumberException("Project number has not been entered, cannot create constant paramaters");
}
if (extractableCohort == null)
{
throw new Exception("Cohort has not been selected, cannot create constant parameters");
}
IExternalCohortTable externalCohortTable = extractableCohort.ExternalCohortTable;
var declarationSqlCohortId = syntaxHelper.GetParameterDeclaration("@CohortDefinitionID", new DatabaseTypeRequest(typeof(int)));
var declarationSqlProjectNumber = syntaxHelper.GetParameterDeclaration("@ProjectNumber", new DatabaseTypeRequest(typeof(int)));
toReturn.Add(new ConstantParameter(declarationSqlCohortId, extractableCohort.OriginID.ToString(), "The ID of the cohort in " + externalCohortTable.TableName, syntaxHelper));
toReturn.Add(new ConstantParameter(declarationSqlProjectNumber, project.ProjectNumber.ToString(), "The project number of project " + project.Name, syntaxHelper));
return(toReturn);
}
protected virtual void AddValidFrom(DiscoveredTable table, IQuerySyntaxHelper syntaxHelper)
{
var dateTimeDatatype = syntaxHelper.TypeTranslater.GetSQLDBTypeForCSharpType(new DatabaseTypeRequest(typeof(DateTime)));
var nowFunction = syntaxHelper.GetScalarFunctionSql(MandatoryScalarFunctions.GetTodaysDate);
_table.AddColumn(SpecialFieldNames.ValidFrom, string.Format(" {0} DEFAULT {1}", dateTimeDatatype, nowFunction), true, UserSettings.ArchiveTriggerTimeout);
}
public SpontaneouslyInventedSqlParameter(MemoryRepository repo, string declarationSql, string value, string comment, IQuerySyntaxHelper syntaxHelper) : base(repo)
{
_syntaxHelper = syntaxHelper;
ParameterSQL = declarationSql;
Value = value;
Comment = comment;
}
protected override string BuildPivotAndAxisAggregate(AggregateCustomLineCollection query)
{
IQuerySyntaxHelper syntaxHelper = query.SyntaxHelper;
string pivotAlias;
string countAlias;
string axisColumnAlias;
var part1 = GetPivotPart1(query, out pivotAlias, out countAlias, out axisColumnAlias);
//The dynamic query in which we assemble a query string and EXECUTE it
string part2 = string.Format(@"
/*DYNAMIC PIVOT*/
declare @Query varchar(MAX)
SET @Query = '
{0}
{1}
/*Would normally be Select * but must make it IsNull to ensure we see 0s instead of null*/
select '+@FinalSelectList+'
from
(
SELECT
{5} as joinDt,
{4},
{3}
FROM
@dateAxis axis
LEFT JOIN
(
{2}
)ds
on {5} = ds.{6}
) s
PIVOT
(
sum({3})
for {4} in ('+@Columns+') --The dynamic Column list we just fetched at top of query
) piv'
EXECUTE(@Query)
",
syntaxHelper.Escape(string.Join(Environment.NewLine, query.Lines.Where(c => c.LocationToInsert < QueryComponent.SELECT))),
syntaxHelper.Escape(GetDateAxisTableDeclaration(query.Axis)),
//the entire select query up to the end of the group by (omitting any Top X)
syntaxHelper.Escape(string.Join(Environment.NewLine, query.Lines.Where(c =>
c.LocationToInsert >= QueryComponent.SELECT &&
c.LocationToInsert < QueryComponent.OrderBy &&
c.Role != CustomLineRole.TopX))),
syntaxHelper.Escape(countAlias),
syntaxHelper.Escape(pivotAlias),
syntaxHelper.Escape(GetDatePartOfColumn(query.Axis.AxisIncrement, "axis.dt")),
axisColumnAlias
);
return(part1 + part2);
}
/// <summary>
/// Creates a new unchangeable always available parameter in a query being built.
/// </summary>
/// <param name="parameterSQL">The declaration sql e.g. DECLARE @bob as int</param>
/// <param name="value">The value to set the paramater e.g. 1</param>
/// <param name="comment">Some text to appear above the parameter, explaining its purpose</param>
/// <param name="syntaxHelper"></param>
public ConstantParameter(string parameterSQL, string value, string comment, IQuerySyntaxHelper syntaxHelper)
{
_syntaxHelper = syntaxHelper;
Value = value;
Comment = comment;
ParameterSQL = parameterSQL;
}
public AggregateCustomLineCollection(List <CustomLine> queryLines, IQueryAxis axisIfAny, IQuerySyntaxHelper querySyntaxHelper)
{
Lines = queryLines;
Axis = axisIfAny;
SyntaxHelper = querySyntaxHelper;
Validate();
}
///<inheritdoc/>
public IQuerySyntaxHelper GetQuerySyntaxHelper()
{
if (_cachedQuerySyntaxHelper == null)
{
_cachedQuerySyntaxHelper = TableInfo.GetQuerySyntaxHelper();
}
return(_cachedQuerySyntaxHelper);
}
/// <inheritdoc/>
public IQuerySyntaxHelper GetQuerySyntaxHelper()
{
if (_cachedQuerySyntaxHelper == null)
{
_cachedQuerySyntaxHelper = ExternalCohortTable.GetQuerySyntaxHelper();
}
return(_cachedQuerySyntaxHelper);
}
/// <summary>
/// Internal API constructor intended for Implementation classes, instead use <see cref="DiscoveredTable.DiscoverColumn"/> instead.
/// </summary>
/// <param name="table"></param>
/// <param name="name"></param>
/// <param name="allowsNulls"></param>
public DiscoveredColumn(DiscoveredTable table, string name, bool allowsNulls)
{
Table = table;
Helper = table.Helper.GetColumnHelper();
_name = name;
_querySyntaxHelper = table.Database.Server.GetQuerySyntaxHelper();
AllowNulls = allowsNulls;
}
/// <summary>
/// Returns the line of SELECT Sql for this column that will appear in the final query
/// </summary>
/// <param name="hashingPattern"></param>
/// <param name="salt"></param>
/// <param name="syntaxHelper"></param>
/// <returns></returns>
public string GetSelectSQL(string hashingPattern, string salt, IQuerySyntaxHelper syntaxHelper)
{
string toReturn = this.IColumn.SelectSQL;
//deal with hashing
if (string.IsNullOrWhiteSpace(salt) == false && this.IColumn.HashOnDataRelease)
{
if (string.IsNullOrWhiteSpace(this.IColumn.Alias))
{
throw new ArgumentException("IExtractableColumn " + this.IColumn + " is missing an Alias (required for hashing)");
}
//if there is no custom hashing pattern
if (string.IsNullOrWhiteSpace(hashingPattern))
{
toReturn = syntaxHelper.HowDoWeAchieveMd5(toReturn); //use the DBMS specific one
}
else
{
toReturn = string.Format(hashingPattern, toReturn, salt); //otherwise use the custom one
}
}
// the SELECT SQL may span multiple lines, so collapse it to a single line cleaning up any whitespace issues, e.g. to avoid double spaces in the collapsed version
var trimmedSelectSQL =
toReturn.Split(new[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries)
.Select(s => s.Trim());
toReturn = string.Join(" ", trimmedSelectSQL);
//append alias to the end of the line if there is an alias
if (!string.IsNullOrWhiteSpace(this.IColumn.Alias))
{
toReturn += syntaxHelper.AliasPrefix + this.IColumn.Alias.Trim();
}
//cannot be both, we check for this earlier (see SetLookupStatus)
Debug.Assert(!(IsLookupDescription && IsLookupForeignKey));
//replace table name with table alias if it is a LookupDescription
if (IsLookupDescription)
{
string tableName = LookupTable.PrimaryKey.TableInfo.Name;
if (!toReturn.Contains(tableName))
{
throw new Exception("Column \"" + toReturn + "\" is a Lookup Description but it's SELECT SQL does not include the Lookup table name \"" + tableName + "\"");
}
toReturn = toReturn.Replace(tableName, JoinHelper.GetLookupTableAlias(LookupTableAlias));
}
//actually dont need to do anything special for LookupForeignKeys
return(toReturn);
}
/// <summary>
/// Internal API constructor intended for Implementation classes, instead use <see cref="DiscoveredDatabase.ExpectTable"/> instead.
/// </summary>
/// <param name="database"></param>
/// <param name="table"></param>
/// <param name="querySyntaxHelper"></param>
/// <param name="schema"></param>
/// <param name="tableType"></param>
public DiscoveredTable(DiscoveredDatabase database, string table, IQuerySyntaxHelper querySyntaxHelper, string schema = null, TableType tableType = TableType.Table)
{
_table = table;
Helper = database.Helper.GetTableHelper();
Database = database;
Schema = schema;
TableType = tableType;
QuerySyntaxHelper = querySyntaxHelper;
}
public void Initialize(DiscoveredDatabase dbInfo, LoadStage loadStage)
{
_raw = dbInfo;
_syntaxHelper = _raw.Server.GetQuerySyntaxHelper();
if (loadStage != LoadStage.AdjustRaw)
{
throw new Exception("This component should only run in AdjustRaw");
}
}
private string GetEntityForMatch(Match match, IQuerySyntaxHelper syntaxHelper)
{
if (match.Groups.Count != 3)
{
throw new ExecuteSqlFileRuntimeTaskException("Regex Match in Sql File had " + match.Groups.Count + " Groups, expected 3, Match was:'" + match.Value + "'");
}
char entity;
int id;
try
{
entity = match.Groups[1].Value.ToUpper()[0];
id = int.Parse(match.Groups[2].Value);
}
catch (Exception e)
{
throw new ExecuteSqlFileRuntimeTaskException("Error performing substitution in Sql File, Failed to replace match " + match.Value + " due to parse expectations", e);
}
var tables = _job.RegularTablesToLoad.Union(_job.LookupTablesToLoad);
var namer = _job.Configuration.DatabaseNamer;
switch (entity)
{
case 'T':
var toReturnTable = tables.SingleOrDefault(t => t.ID == id);
if (toReturnTable == null)
{
throw new ExecuteSqlFileRuntimeTaskException("Failed to find a TableInfo in the load with ID " + id + ". All TableInfo IDs referenced in script must be part of the LoadMetadata");
}
return(toReturnTable.GetRuntimeName(_loadStage, namer));
case 'C':
var toReturnColumn = tables.SelectMany(t => t.ColumnInfos).SingleOrDefault(t => t.ID == id);
if (toReturnColumn == null)
{
throw new ExecuteSqlFileRuntimeTaskException("Failed to find a ColumnInfo in the load with ID " + id + ". All ColumnInfo IDs referenced in script must be part of the LoadMetadata");
}
var db = toReturnColumn.TableInfo.GetDatabaseRuntimeName(_loadStage, namer);
var tbl = toReturnColumn.TableInfo.GetRuntimeName(_loadStage, namer);
var col = toReturnColumn.GetRuntimeName(_loadStage);
return(syntaxHelper.EnsureFullyQualified(db, null, tbl, col));
default:
throw new ExecuteSqlFileRuntimeTaskException("Error performing substitution in Sql File, Unexpected Type char in regex:" + entity);
}
}
/// <summary>
/// Applies <paramref name="topX"/> to the <see cref="ISqlQueryBuilder"/> as a <see cref="CustomLine"/> based on the database engine syntax e.g. LIMIT vs TOP
/// and puts in in the correct location in the query (<see cref="QueryComponent"/>)
/// </summary>
/// <param name="queryBuilder"></param>
/// <param name="syntaxHelper"></param>
/// <param name="topX"></param>
public static void HandleTopX(ISqlQueryBuilder queryBuilder, IQuerySyntaxHelper syntaxHelper, int topX)
{
//if we have a lingering custom line from last time
ClearTopX(queryBuilder);
//if we are expected to have a topx
var response = syntaxHelper.HowDoWeAchieveTopX(topX);
queryBuilder.TopXCustomLine = AddCustomLine(queryBuilder, response.SQL, response.Location);
queryBuilder.TopXCustomLine.Role = CustomLineRole.TopX;
}
public AutoCompleteProvider Create(IQuerySyntaxHelper helper)
{
var provider = new AutoCompleteProvider(_activator);
if (helper != null)
{
provider.AddSQLKeywords(helper);
}
return(provider);
}
private void SetSQLHighlighting(Scintilla scintilla, IQuerySyntaxHelper syntaxHelper)
{
// Reset the styles
scintilla.StyleResetDefault();
scintilla.Styles[Style.Default].Font = "Courier New";
scintilla.Styles[Style.Default].Size = 10;
scintilla.StyleClearAll();
// Set the SQL Lexer
scintilla.Lexer = Lexer.Sql;
// Show line numbers
scintilla.Margins[0].Width = 20;
// Set the Styles
scintilla.Styles[Style.LineNumber].ForeColor = Color.FromArgb(255, 128, 128, 128); //Dark Gray
scintilla.Styles[Style.LineNumber].BackColor = Color.FromArgb(255, 228, 228, 228); //Light Gray
scintilla.Styles[Style.Sql.Comment].ForeColor = Color.Green;
scintilla.Styles[Style.Sql.CommentLine].ForeColor = Color.Green;
scintilla.Styles[Style.Sql.CommentLineDoc].ForeColor = Color.Green;
scintilla.Styles[Style.Sql.Number].ForeColor = Color.Maroon;
scintilla.Styles[Style.Sql.Word].ForeColor = Color.Blue;
scintilla.Styles[Style.Sql.Word2].ForeColor = Color.Fuchsia;
scintilla.Styles[Style.Sql.User1].ForeColor = Color.Gray;
scintilla.Styles[Style.Sql.User2].ForeColor = Color.FromArgb(255, 00, 128, 192); //Medium Blue-Green
scintilla.Styles[Style.Sql.String].ForeColor = Color.Red;
scintilla.Styles[Style.Sql.Character].ForeColor = Color.Red;
scintilla.Styles[Style.Sql.Operator].ForeColor = Color.Black;
// Set keyword lists
// Word = 0
scintilla.SetKeywords(0, @"add alter as authorization backup begin bigint binary bit break browse bulk by cascade case catch check checkpoint close clustered column commit compute constraint containstable continue create current cursor cursor database date datetime datetime2 datetimeoffset dbcc deallocate decimal declare default delete deny desc disk distinct distributed double drop dump else end errlvl escape except exec execute exit external fetch file fillfactor float for foreign freetext freetexttable from full function goto grant group having hierarchyid holdlock identity identity_insert identitycol if image index insert int intersect into key kill lineno load merge money national nchar nocheck nocount nolock nonclustered ntext numeric nvarchar of off offsets on open opendatasource openquery openrowset openxml option order over percent plan precision primary print proc procedure public raiserror read readtext real reconfigure references replication restore restrict return revert revoke rollback rowcount rowguidcol rule save schema securityaudit select set setuser shutdown smalldatetime smallint smallmoney sql_variant statistics table table tablesample text textsize then time timestamp tinyint to top tran transaction trigger truncate try union unique uniqueidentifier update updatetext use user values varbinary varchar varying view waitfor when where while with writetext xml go ");
string word2 =
@"ascii cast char charindex ceiling coalesce collate contains convert current_date current_time current_timestamp current_user floor isnull max min nullif object_id session_user substring system_user tsequal";
if (syntaxHelper != null)
{
foreach (var kvp in syntaxHelper.GetSQLFunctionsDictionary())
{
word2 += " " + kvp.Key;
}
}
// Word2 = 1
scintilla.SetKeywords(1, word2);
// User1 = 4
scintilla.SetKeywords(4, @"all and any between cross exists in inner is join left like not null or outer pivot right some unpivot ( ) * ");
// User2 = 5
scintilla.SetKeywords(5, @"sys objects sysobjects ");
}
/// <summary>
/// Here we are migrating a Catalogue but some of the TableInfos have already been migrated e.g. lookup tables as part of migrating another Catalogue. We are
/// now trying to find one of those 'not migrated' ColumnInfos by name without knowing whether the user has since deleted the reference or worse introduced
/// duplicate references to the same TableInfo/ColumnInfos.
/// </summary>
/// <param name="syntaxHelper"></param>
/// <param name="col"></param>
/// <param name="expectedName"></param>
/// <param name="isOptional"></param>
/// <returns></returns>
private ColumnInfo FindNewColumnNamed(IQuerySyntaxHelper syntaxHelper, ColumnInfo col, string expectedName, bool isOptional)
{
string expectedNewName = syntaxHelper.EnsureFullyQualified(
_planManager.TargetDatabase.GetRuntimeName(),
null,
col.TableInfo.GetRuntimeName(),
expectedName);
var columns = _allColumnsInfos.Where(c => c.Name.Equals(expectedNewName, StringComparison.CurrentCultureIgnoreCase)).ToArray();
bool failedANOToo = false;
//maybe it was anonymised in the other configuration?
if (columns.Length == 0 && !expectedName.StartsWith("ANO", StringComparison.CurrentCultureIgnoreCase))
{
try
{
return(FindNewColumnNamed(syntaxHelper, col, "ANO" + expectedName, isOptional));
}
catch (Exception)
{
//oh well couldnt find it
failedANOToo = true;
}
}
if (columns.Length == 1)
{
return(columns[0]);
}
var columnsFromCorrectServer = columns.Where(c => c.TableInfo.Server.Equals(_planManager.TargetDatabase.Server.Name)).ToArray();
if (columnsFromCorrectServer.Length == 1)
{
return(columnsFromCorrectServer[0]);
}
var columnsFromCorrectServerThatAreaAlsoLocalImports = columnsFromCorrectServer.Where(_shareManager.IsImportedObject).ToArray();
if (columnsFromCorrectServerThatAreaAlsoLocalImports.Length == 1)
{
return(columnsFromCorrectServerThatAreaAlsoLocalImports[0]);
}
if (isOptional)
{
return(null);
}
throw new Exception("Found '" + columns.Length + "' ColumnInfos called '" + expectedNewName + "'" + (failedANOToo ? " (Or 'ANO" + expectedName + "')" : ""));
}
protected override void AddValidFrom(DiscoveredTable table, IQuerySyntaxHelper syntaxHelper)
{
// MySql changed how they do default date fields between 5.5 and 5.6
//https://dba.stackexchange.com/a/132954
if (UseOldDateTimeDefaultMethod(table))
{
table.AddColumn(SpecialFieldNames.ValidFrom, "TIMESTAMP DEFAULT CURRENT_TIMESTAMP", true, UserSettings.ArchiveTriggerTimeout);
}
else
{
table.AddColumn(SpecialFieldNames.ValidFrom, "DATETIME DEFAULT CURRENT_TIMESTAMP", true, UserSettings.ArchiveTriggerTimeout);
}
}
/// <summary>
/// Initializes the <see cref="IQuerySyntaxHelper"/> for the column and optionally ensures that it has an alias. If no <see cref="Alias"/> has
/// been specified or was found in the current sql then <see cref="DefaultAliasName"/> is set.
/// </summary>
/// <param name="syntaxHelper"></param>
/// <param name="ensureAliasExists"></param>
public void SetQuerySyntaxHelper(IQuerySyntaxHelper syntaxHelper, bool ensureAliasExists)
{
_syntaxHelper = syntaxHelper;
string select;
string alias;
//if alias exists
if (_syntaxHelper.SplitLineIntoSelectSQLAndAlias(_sql, out select, out alias))
{
Alias = alias; //use the users explicit alias
}
else
{
Alias = ensureAliasExists ? DefaultAliasName : null;//set an alias of MyCount
}
SelectSQL = select;
}
private string GetForeignKeyConstraintSql(string tableName, IQuerySyntaxHelper syntaxHelper, Dictionary <DatabaseColumnRequest, DiscoveredColumn> foreignKeyPairs, bool cascadeDelete)
{
//@" CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) on delete cascade";
var otherTable = foreignKeyPairs.Values.Select(v => v.Table).Distinct().Single();
string constraintName = MakeSaneConstraintName("FK_", tableName + "_" + otherTable);
return(string.Format(
@"CONSTRAINT {0} FOREIGN KEY ({1})
REFERENCES {2}({3}) {4}",
constraintName,
string.Join(",", foreignKeyPairs.Keys.Select(k => syntaxHelper.EnsureWrapped(k.ColumnName))),
otherTable.GetFullyQualifiedName(),
string.Join(",", foreignKeyPairs.Values.Select(v => syntaxHelper.EnsureWrapped(v.GetRuntimeName()))),
cascadeDelete ? " on delete cascade": ""
));
}
public void AddSQLKeywords(IQuerySyntaxHelper syntaxHelper)
{
if (syntaxHelper == null)
{
return;
}
foreach (KeyValuePair <string, string> kvp in syntaxHelper.GetSQLFunctionsDictionary())
{
var snip = new SubstringAutocompleteItem(kvp.Key);
snip.MenuText = kvp.Key;
snip.Text = kvp.Value;
snip.Tag = kvp;
snip.ImageIndex = GetIndexFor(null, RDMPConcept.SQL.ToString());//sql icon
AddUnlessDuplicate(snip);
}
}
private void Setup(ExtractionInformation extractionInformation)
{
ExtractionInformation = extractionInformation;
if (isFirstTimeSetupCalled)
{
//if the catalogue item has same name as the extraction information (alias)
if (ExtractionInformation.CatalogueItem.Name.Equals(ExtractionInformation.ToString()))
{
_namesMatchedWhenDialogWasLaunched = true;
}
_querySyntaxHelper = ExtractionInformation.GetQuerySyntaxHelper();
QueryEditor = new ScintillaTextEditorFactory().Create(new RDMPCombineableFactory(), SyntaxLanguage.SQL, _querySyntaxHelper);
QueryEditor.TextChanged += QueryEditorOnTextChanged;
var autoComplete = new AutoCompleteProviderWin(_querySyntaxHelper);
autoComplete.Add(ExtractionInformation.CatalogueItem.Catalogue);
autoComplete.RegisterForEvents(QueryEditor);
isFirstTimeSetupCalled = false;
}
var colInfo = ExtractionInformation.ColumnInfo;
//deal with empty values in database (shouldn't be any but could be)
if (string.IsNullOrWhiteSpace(ExtractionInformation.SelectSQL) && colInfo != null)
{
ExtractionInformation.SelectSQL = colInfo.Name.Trim();
ExtractionInformation.SaveToDatabase();
}
QueryEditor.Text = ExtractionInformation.SelectSQL + (!string.IsNullOrWhiteSpace(ExtractionInformation.Alias) ? _querySyntaxHelper.AliasPrefix + ExtractionInformation.Alias : "");
lblFromTable.Text = colInfo == null?"MISSING ColumnInfo":colInfo.TableInfo.Name;
if (!pSql.Controls.Contains(QueryEditor))
{
pSql.Controls.Add(QueryEditor);
}
}
public virtual string GetForeignKeyConstraintSql(string foreignTable, IQuerySyntaxHelper syntaxHelper,
Dictionary <IHasRuntimeName, DiscoveredColumn> foreignKeyPairs, bool cascadeDelete, string constraintName)
{
var primaryKeyTable = foreignKeyPairs.Values.Select(v => v.Table).Distinct().Single();
if (constraintName == null)
{
constraintName = GetForeignKeyConstraintNameFor(foreignTable, primaryKeyTable.GetRuntimeName());
}
//@" CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) on delete cascade";
return(string.Format(
@"CONSTRAINT {0} FOREIGN KEY ({1})
REFERENCES {2}({3}) {4}",
constraintName,
string.Join(",", foreignKeyPairs.Keys.Select(k => syntaxHelper.EnsureWrapped(k.GetRuntimeName()))),
primaryKeyTable.GetFullyQualifiedName(),
string.Join(",", foreignKeyPairs.Values.Select(v => syntaxHelper.EnsureWrapped(v.GetRuntimeName()))),
cascadeDelete ? " on delete cascade": ""
));
}
/// <summary>
/// Provides useful bits of SQL you need to build an axis only aggregate
/// </summary>
/// <param name="syntaxHelper">Your DBMS specific syntax helper</param>
/// <param name="lines">The lines you were given in <see cref="BuildAggregate"/></param>
/// <param name="countSelectLine">The single aggregate function line e.g. "count(distinct chi) as Fish,"</param>
/// <param name="countSqlWithoutAlias">The portion of <paramref name="countSelectLine"/> which excludes the alias e.g. "count(distinct chi)"</param>
/// <param name="countAlias">The portion of <paramref name="countSelectLine"/> which is the alias e.g. "Fish" (or null if no AS is specified)</param>
/// <param name="axisColumn">The single line of SELECT SQL which is the Axis join column e.g. "[MyDb]..[mytbl].[AdmissionDate] as Admt,"</param>
/// <param name="axisColumnWithoutAlias">The portion of <paramref name="axisColumn"/> which excludes the alias e.g. "[MyDb]..[mytbl].[AdmissionDate]"</param>
/// <param name="axisColumnAlias">The portion of <paramref name="axisColumn"/> which is the alias e.g. "Admt" (or "joinDt" if no AS is specified)</param>
protected void GetAggregateAxisBits(IQuerySyntaxHelper syntaxHelper, List <CustomLine> lines,
out CustomLine countSelectLine,
out string countSqlWithoutAlias,
out string countAlias,
out CustomLine axisColumn,
out string axisColumnWithoutAlias,
out string axisColumnAlias)
{
countSelectLine = lines.Single(l => l.LocationToInsert == QueryComponent.QueryTimeColumn && l.Role == CustomLineRole.CountFunction);
syntaxHelper.SplitLineIntoSelectSQLAndAlias(countSelectLine.Text, out countSqlWithoutAlias, out countAlias);
//Deal with the axis dimension which is currently `mydb`.`mytbl`.`mycol` and needs to become YEAR(`mydb`.`mytbl`.`mycol`) As joinDt
axisColumn = lines.Single(l => l.LocationToInsert == QueryComponent.QueryTimeColumn && l.Role == CustomLineRole.Axis);
syntaxHelper.SplitLineIntoSelectSQLAndAlias(axisColumn.Text, out axisColumnWithoutAlias, out axisColumnAlias);
if (string.IsNullOrWhiteSpace(axisColumnAlias))
{
axisColumnAlias = "joinDt";
}
}
private void AdjustAxisQueryLines(List <CustomLine> lines, IQueryAxis axis, IQuerySyntaxHelper syntaxHelper, out CustomLine axisColumn, out string axisColumnWithoutAlias, out string axisColumnAlias, out CustomLine axisGroupBy)
{
//Deal with the axis dimension which is currently [mydb].[mytbl].[mycol] and needs to become YEAR([mydb].[mytbl].[mycol]) As joinDt
axisColumn = lines.Single(l => l.LocationToInsert == QueryComponent.QueryTimeColumn && l.Role == CustomLineRole.Axis);
syntaxHelper.SplitLineIntoSelectSQLAndAlias(axisColumn.Text, out axisColumnWithoutAlias, out axisColumnAlias);
axisGroupBy = lines.Single(l => l.LocationToInsert == QueryComponent.GroupBy && l.Role == CustomLineRole.Axis);
if (string.IsNullOrWhiteSpace(axisColumnAlias))
{
axisColumnAlias = "joinDt";
}
var axisColumnEndedWithComma = axisColumn.Text.EndsWith(",");
axisColumn.Text = GetDatePartOfColumn(axis.AxisIncrement, axisColumnWithoutAlias) + " AS " + axisColumnAlias +
(axisColumnEndedWithComma ? "," : "");
var groupByEndedWithComma = axisGroupBy.Text.EndsWith(",");
axisGroupBy.Text = GetDatePartOfColumn(axis.AxisIncrement, axisColumnWithoutAlias) +
(groupByEndedWithComma ? "," : "");
}