public void Test_ToString_ShouldUseAliases()
{
//---------------Set up test pack-------------------
const string sourceName = "mysource";
var source1 = new Source(sourceName);
Source field1Source = source1;
QueryField field1 = new QueryField("testfield", "testfield", field1Source);
Criteria criteria = new Criteria(field1, Criteria.ComparisonOp.Equals, "myvalue");
IDictionary <string, string> aliases = new Dictionary <string, string>()
{
{ source1.ToString(), "a1" }
};
CriteriaDB criteriaDb = new CriteriaDB(criteria);
SqlFormatter sqlFormatter = new SqlFormatter("[", "]", "", "LIMIT");
//---------------Execute Test ----------------------
string whereClause = criteriaDb.ToString(sqlFormatter, value => "Param", aliases);
//---------------Test Result -----------------------
StringAssert.AreEqualIgnoringCase("a1.[testfield] = Param", whereClause);
}
public void Test_CreateSqlFormatter_Firebird()
{
//---------------Set up test pack-------------------
IDatabaseConnection dbConn = new DatabaseConnectionFirebird("System.Data", "System.Data.SqlClient.SqlConnection");
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
ISqlFormatter defaultSqlFormatter = dbConn.SqlFormatter;
//---------------Test Result -----------------------
Assert.IsInstanceOf(typeof(SqlFormatter), defaultSqlFormatter);
SqlFormatter sqlFormatter = (SqlFormatter)defaultSqlFormatter;
Assert.IsNotNull(sqlFormatter);
Assert.AreEqual("\"", sqlFormatter.LeftFieldDelimiter);
Assert.AreEqual("\"", sqlFormatter.RightFieldDelimiter);
Assert.AreEqual("FIRST", sqlFormatter.LimitClauseAtBeginning);
Assert.AreEqual("", sqlFormatter.LimitClauseAtEnd);
Assert.AreEqual(sqlFormatter.LeftFieldDelimiter, dbConn.LeftFieldDelimiter);
Assert.AreEqual(sqlFormatter.RightFieldDelimiter, dbConn.RightFieldDelimiter);
}
public void Test_CreateSqlFormatter()
{
//---------------Set up test pack-------------------
IDatabaseConnection dbConn = new DatabaseConnection_Stub();
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
var defaultSqlFormatter = dbConn.SqlFormatter;
//---------------Test Result -----------------------
Assert.IsInstanceOf(typeof(SqlFormatter), defaultSqlFormatter);
SqlFormatter sqlFormatter = (SqlFormatter)defaultSqlFormatter;
Assert.IsNotNull(sqlFormatter);
Assert.AreEqual("[", sqlFormatter.LeftFieldDelimiter);
Assert.AreEqual("]", sqlFormatter.RightFieldDelimiter);
Assert.AreEqual("TOP", sqlFormatter.LimitClauseAtBeginning);
Assert.AreEqual("", sqlFormatter.LimitClauseAtEnd);
Assert.AreEqual("[", dbConn.LeftFieldDelimiter);
Assert.AreEqual("]", dbConn.RightFieldDelimiter);
}
/// <summary>
/// Wird aufgerufen, bevor der MainActivator mit der Aktivierung beginnt.
/// Hier sollten alle von einem DB-Objekt abhängige Initialisierungen
/// durchgeführt werden. Das Formular besitzt zu diesem Zeitpunkt die Connection
/// und alle davon abhängigen Komponenten (SqlFormatter, Preprocessor-Auswertung, ...)
/// </summary>
private void MainActivator_OnActivating(object sender, System.EventArgs e)
{
try
{
// Aktivierung mit <null> verhindern
ISingleDbObject dbobject = m_MainActivator.DbObject;
if (dbobject == null)
{
return;
}
// Inaktive dürfen nicht mehr zugeordnet werden
m_MemberRelation1.WhereClause = string.Format("{0} or {1} in (select {2} from {4} where {3})",
SqlFormatter.Comparison("IsInActive", false, ValType.Bool),
SqlFormatter.FormatColumnname("UID_Application", true, ValType.String, FormatterOptions.None),
SqlFormatter.FormatColumnname("UID_ApplicationParent", true, ValType.String, FormatterOptions.None),
SqlFormatter.UidComparison("UID_ApplicationChild", FormTool.GetValueSafe(dbobject, "UID_Application", "")),
"ApplicationDependsOnApp");
m_MemberRelation2.WhereClause = string.Format("{0} or {1} in (select {2} from {4} where {3})",
SqlFormatter.Comparison("IsInActive", false, ValType.Bool),
SqlFormatter.FormatColumnname("UID_Application", true, ValType.String, FormatterOptions.None),
SqlFormatter.FormatColumnname("UID_ApplicationChild", true, ValType.String, FormatterOptions.None),
SqlFormatter.UidComparison("UID_ApplicationParent", FormTool.GetValueSafe(dbobject, "UID_Application", "")),
"ApplicationDependsOnApp");
m_MemberRelation3.WhereClause = string.Format("{0} or {1} in (select {2} from {4} where {3})",
SqlFormatter.Comparison("IsInActive", false, ValType.Bool),
SqlFormatter.FormatColumnname("UID_Driver", true, ValType.String, FormatterOptions.None),
SqlFormatter.FormatColumnname("UID_DriverParent", true, ValType.String, FormatterOptions.None),
SqlFormatter.UidComparison("UID_ApplicationChild", FormTool.GetValueSafe(dbobject, "UID_Application", "")),
"ApplicationDependsOnDriver");
m_MemberRelationApplicationExcludeAppUIDApplication.WhereClause =
m_MemberRelationApplicationExcludeDriverUIDApplication.WhereClause = SqlFormatter.Comparison("IsInActive", false, ValType.Bool);
}
finally
{
}
}
public void TestCreateSQL_WithJoin_WithDelimiter()
{
//-------------Setup Test Pack ------------------
Source source = new Source("MySource", "MY_SOURCE");
Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE");
Source.Join join = CreateAndAddJoin(source, joinSource);
SourceDB sourceDB = new SourceDB(source);
//-------------Execute test ---------------------
SqlFormatter myFormatter = new SqlFormatter("[", "]", "TOP", "");
string sql = sourceDB.CreateSQL(myFormatter, CreateAliases(source, joinSource));
//-------------Test Result ----------------------
Source.Join.JoinField joinField = join.JoinFields[0];
string expectedSql = string.Format("([{0}] a1 JOIN [{1}] a2 ON a1.[{2}] = a2.[{3}])", source.EntityName, joinSource.EntityName,
joinField.FromField.FieldName, joinField.ToField.FieldName);
Assert.AreEqual(expectedSql, sql);
}
public void Test_CreateSqlFormatter_PostgreSql()
{
//---------------Set up test pack-------------------
IDatabaseConnection dbConn = new DatabaseConnectionPostgreSql("System.Data", "System.Data.SqlClient.SqlConnection");
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
ISqlFormatter defaultSqlFormatter = dbConn.SqlFormatter;
//---------------Test Result -----------------------
Assert.IsInstanceOf(typeof(SqlFormatter), defaultSqlFormatter);
SqlFormatter sqlFormatter = (SqlFormatter)defaultSqlFormatter;
Assert.IsNotNull(sqlFormatter);
Assert.AreEqual("\"", sqlFormatter.LeftFieldDelimiter);
Assert.AreEqual("\"", sqlFormatter.RightFieldDelimiter);
Assert.AreEqual("", sqlFormatter.LimitClauseAtBeginning);
Assert.AreEqual("limit", sqlFormatter.LimitClauseAtEnd);
Assert.AreEqual(sqlFormatter.LeftFieldDelimiter, dbConn.LeftFieldDelimiter);
Assert.AreEqual(sqlFormatter.RightFieldDelimiter, dbConn.RightFieldDelimiter);
// StringAssert.Contains(sqlFormatter.LimitClauseAtBeginning, dbConn.GetLimitClauseForBeginning(1));
// StringAssert.Contains(sqlFormatter.LimitClauseAtEnd, dbConn.GetLimitClauseForEnd(1));
}
public void Test_CreateSqlFormatter_SQLServerCe()
{
//---------------Set up test pack-------------------
IDatabaseConnection dbConn = new DatabaseConnectionSqlServerCe
("System.Data.SqlServerCe", "System.Data.SqlServerCe.SqlCeConnection");
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
ISqlFormatter defaultSqlFormatter = dbConn.SqlFormatter;
//---------------Test Result -----------------------
Assert.IsInstanceOf(typeof(SqlFormatterForSqlServerCe), defaultSqlFormatter);
SqlFormatter sqlFormatter = (SqlFormatter)defaultSqlFormatter;
Assert.IsNotNull(sqlFormatter);
Assert.AreEqual("[", sqlFormatter.LeftFieldDelimiter);
Assert.AreEqual("]", sqlFormatter.RightFieldDelimiter);
Assert.AreEqual("TOP", sqlFormatter.LimitClauseAtBeginning);
Assert.AreEqual("", sqlFormatter.LimitClauseAtEnd);
Assert.AreEqual("[", dbConn.LeftFieldDelimiter);
Assert.AreEqual("]", dbConn.RightFieldDelimiter);
// StringAssert.Contains("TOP", dbConn.GetLimitClauseForBeginning(1));
// Assert.AreEqual("", dbConn.GetLimitClauseForEnd(1));
}
/// <summary>
/// Wird aufgerufen, bevor der MainActivator aktiviert wird.
/// Hier sollten alle von einem DB-Objekt abhängige Initialisierungen
/// durchgeführt werden.
/// </summary>
private void MainActivator_OnActivating(object sender, System.EventArgs e)
{
try
{
// Daten holen und prüfen
ISingleDbObject dbobject = m_MainActivator.DbObject;
if (dbobject == null)
{
return;
}
m_MemberRelation1.WhereClause = !FormTool.CanSee(dbobject, "UID_OS") ? "1=2" :
string.Format("UID_WorkDesk in (select UID_Workdesk from Workdesk where {0})",
SqlFormatter.OrRelation(
SqlFormatter.UidComparison("UID_OS", dbobject["UID_OS"].New.String),
SqlFormatter.EmptyClause("UID_OS", ValType.String)));
}
finally
{
}
}
public void Test_CreateSQL_ShouldUseAliasesInJoins()
{
//---------------Set up test pack-------------------
var mysource = new Source("mysource");
QueryField fieldOnMySource = new QueryField("testfield", "testfield", mysource);
Source joinedTableSource = new Source("myjoinedtosource");
QueryField fieldOnJoinedTableSource = new QueryField("testfield", "testfield", joinedTableSource);
mysource.Joins.Add(new Source.Join(mysource, joinedTableSource));
mysource.Joins[0].JoinFields.Add(new Source.Join.JoinField(fieldOnMySource, fieldOnJoinedTableSource));
SourceDB sourceDB = new SourceDB(mysource);
SqlFormatter sqlFormatter = new SqlFormatter("[", "]", "", "LIMIT");
IDictionary <string, string> aliases = new Dictionary <string, string>()
{
{ mysource.ToString(), "a1" }, { joinedTableSource.ToString(), "a2" }
};
//---------------Execute Test ----------------------
string sql = sourceDB.CreateSQL(sqlFormatter, aliases);
//---------------Test Result -----------------------
StringAssert.AreEqualIgnoringCase(
"([mysource] a1 JOIN [myjoinedtosource] a2 on a1.[testfield] = a2.[testfield])", sql);
}
/// <summary>
/// Wird aufgerufen, bevor der MainActivator aktiviert wird.
/// Hier sollten alle von einem DB-Objekt abhängige Initialisierungen
/// durchgeführt werden.
/// </summary>
private void MainActivator_OnActivating(object sender, System.EventArgs e)
{
try
{
// Aktivierung mit <null> verhindern
if (m_MainActivator.DbObject == null)
{
return;
}
m_MemberRelation1.WhereClause = SqlFormatter.Comparison("IsApplicationGroup", true, ValType.Bool);
m_MemberRelation1.RootFilterMemberWhereClause =
string.Format(
"UID_ADSContainer in (select UID_ADSContainer from ADSContainer where {0})",
SqlFormatter.Comparison("Ident_Domain", "%Ident_Domain%", ValType.String));
m_MemberRelation1.RootFilterWhereClause = SqlFormatter.Comparison("Ident_DomainType", "ADS", ValType.String);
}
finally
{
}
}
/// <summary>
/// Wird aufgerufen, bevor der MainActivator aktiviert wird.
/// Hier sollten alle von einem DB-Objekt abhängige Initialisierungen
/// durchgeführt werden.
/// </summary>
private void MainActivator_OnActivating(object sender, System.EventArgs e)
{
// Aktivierung mit <null> verhindern
if (m_MainActivator.DbObject == null)
{
return;
}
string domaintrustsclause = ActiveDirectory.GetUserOrGroupDomainTrustWhereClause(m_MainActivator.DbObject);
m_MemberRelation1.WhereClause = SqlFormatter.AndRelation(
domaintrustsclause,
string.Format("UID_Person in (select UID_Person from Person where {0} AND {1})",
SqlFormatter.Comparison("IsDummyPerson", true, ValType.Bool),
SqlFormatter.Comparison("IsTASUser", true, ValType.Bool))
);
m_MemberRelation1.RootFilterMemberWhereClause =
SqlFormatter.UidComparison("UID_ADSDomain", "%UID_ADSDomain%");
m_MemberRelation1.RootFilterWhereClause = ActiveDirectory.GetDomainTrustWhereClause(m_MainActivator.DbObject);
}
/// <summary>
/// Wird aufgerufen, bevor der MainActivator aktiviert wird.
/// Hier sollten alle von einem DB-Objekt abhängige Initialisierungen
/// durchgeführt werden.
/// </summary>
private void MainActivator_OnActivating(object sender, System.EventArgs e)
{
try
{
// Daten holen und prüfen
ISingleDbObject dbobject = m_MainActivator.DbObject;
if (dbobject == null)
{
return;
}
// Inaktive dürfen nicht mehr zugeordnet werden
string isinactiveclause = string.Format("{0} or {1} in (select {1} from MachineHasDriver where {2})",
SqlFormatter.Comparison("IsInActive", false, ValType.Bool),
SqlFormatter.FormatColumnname("UID_Driver", true, ValType.String),
SqlFormatter.UidComparison("UID_Hardware", FormTool.GetValueSafe(dbobject, "UID_Hardware", "")));
if (Connection.GetConfigParm(@"Software\Driver\ShowWithoutProfile") == "1")
{
m_MemberRelation1.WhereClause = isinactiveclause;
}
else
{
m_MemberRelation1.WhereClause = SqlFormatter.AndRelation(
isinactiveclause, string.Format("{0} and {1} in (select {1} from driverprofile where {2} AND {3})",
SqlFormatter.UidComparison("UID_OS", FormTool.GetValueSafe(dbobject, "UID_OS", "")),
SqlFormatter.FormatColumnname("uid_driver", false, ValType.String),
SqlFormatter.UidComparison("UID_SDLDomainRD", FormTool.GetValueSafe(dbobject, "UID_SDLDomainRD", "")),
SqlFormatter.Comparison("ChgTest", 0, ValType.Int, CompareOperator.GreaterThan)));
}
}
finally
{
}
}
/// <summary>
/// Wird aufgerufen, bevor der MainActivator aktiviert wird.
/// Hier sollten alle von einem DB-Objekt abhängige Initialisierungen
/// durchgeführt werden.
/// </summary>
private void MainActivator_OnActivating(object sender, System.EventArgs e)
{
try
{
// Daten holen und prüfen
ISingleDbObject dbobject = m_MainActivator.DbObject;
if (dbobject == null)
{
return;
}
// Inaktive dürfen nicht mehr zugeordnet werden
string isinactiveclause = string.Format("{0} or {1} in (select {1} from DriverHasLicence where {2})",
SqlFormatter.Comparison("IsInActive", false, ValType.Bool),
SqlFormatter.FormatColumnname("UID_Driver", true, ValType.String, FormatterOptions.None),
SqlFormatter.UidComparison("UID_Licence", FormTool.GetValueSafe(dbobject, "UID_Licence", "")));
m_MemberRelation1.WhereClause = isinactiveclause;
}
finally
{
}
}
public SqlFormatProvider(ExecuteParameterCollection parameters, IDbEngineProvider engineProvider)
{
_formatter = new SqlFormatter(parameters, engineProvider);
}
/// <summary>
/// Constructor to initialise the connection object with an
/// assembly name, class name and connection string
/// </summary>
/// <param name="assemblyName">The assembly name</param>
/// <param name="className">The class name</param>
/// <param name="connectString">The connection string, which can be
/// generated using ConnectionStringSqlServerFactory.CreateConnectionString()
/// </param>
public DatabaseConnectionSQLite(string assemblyName, string className, string connectString)
: base(assemblyName, className, connectString)
{
_sqlFormatter = new SqlFormatter("\"", "\"", "", "limit");
}
/// <summary>
///
/// </summary>
private void MainActivator_OnActivated(object sender, System.EventArgs e)
{
try
{
m_BInitialised = true;
// Daten holen und prüfen
ISingleDbObject dbobject = m_MainActivator.DbObject;
if (dbobject != null)
{
var SDLDomaiDef = FormTool.GetValueSafe(dbobject, "UID_SDLDomain", "") != "";
m_TreeComboBoxUIDSDLDomain.EnabledByUser = !SDLDomaiDef;
if (SDLDomaiDef)
{
m_TreeComboBoxUIDParentApplicationServer.WhereClause = !FormTool.CanSee(dbobject, "UID_ApplicationServer") ? "1=2" :
// ReSharper disable once UseStringInterpolation
string.Format("{0} and not {1}",
SqlFormatter.UidComparison("UID_SDLDomain", dbobject["UID_SDLDomain"].New.String),
SqlFormatter.InClause("UID_ApplicationServer", ValType.String, FormTool.GetChildUuids(Connection,
"Applicationserver", "UID_Applicationserver",
dbobject["UID_ApplicationServer"].New.String, "UID_ParentApplicationserver")));
}
else
{
m_TreeComboBoxUIDParentApplicationServer.WhereClause = SqlFormatter.EmptyClause("UID_SDLDomain", ValType.String);
}
}
if (m_EditIdentApplicationServer.Enabled && m_EditIdentApplicationServer.Visible)
{
m_EditIdentApplicationServer.Select();
}
}
catch (Exception ex)
{
// Fehler melden
HandleException(ex);
}
}
protected override string GetSqlWhereClause(IEnumerable <string> tokens)
{
return(SqlFormatter.Conjunction(tokens.Select(t => SqlFormatter.FormatContains("SearchString", t))));
}
private void FormatSQL_Click(object sender, RoutedEventArgs e)
{
var formatter = new SqlFormatter();
this.TextToShow.Text = formatter.FormatString(this.TextToShow.Text);
}
/// <summary>
/// Constructor that initialises a new set of null connections
/// </summary>
protected DatabaseConnection()
{
_connections = new List <ManagedConnection>(5);
_sqlFormatter = new SqlFormatter("[", "]", "TOP", "");
}
/// <summary>
/// Wird aufgerufen, bevor der MainActivator aktiviert wird.
/// Hier sollten alle von einem DB-Objekt abhängige Initialisierungen
/// durchgeführt werden.
/// </summary>
private void MainActivator_OnActivating(object sender, System.EventArgs e)
{
try
{
// Daten holen und prüfen
ISingleDbObject dbobject = m_MainActivator.DbObject;
if (dbobject == null)
{
return;
}
string mnbasecolumn = m_MemberRelation1.MNBaseColumnName;
// Inaktive dürfen nicht mehr zugeordnet werden
string isinactiveclause = SqlFormatter.Comparison("IsInActive", false, ValType.Bool);
string osclause = dbobject.Columns.Contains("UID_OS") ?
(FormTool.CanSee(dbobject, "UID_OS") ? SqlFormatter.UidComparison("UID_OS", FormTool.GetValueSafe(dbobject, "UID_OS", "")) : "1=2")
: "";
if (Connection.GetConfigParm(@"Software\Driver\ShowWithoutProfile") == "1")
{
m_MemberRelation1.WhereClause = SqlFormatter.AndRelation(isinactiveclause, osclause);
}
else
{
m_MemberRelation1.WhereClause = SqlFormatter.AndRelation(
isinactiveclause,
string.Format("{0} in (select {0} from Driverprofile where {1})",
SqlFormatter.FormatColumnname("UID_Driver", true, ValType.String),
SqlFormatter.Comparison("ChgNumber", 0, ValType.Int, CompareOperator.GreaterThan)),
osclause
);
}
// and all already assigned ones.
m_MemberRelation1.WhereClause = SqlFormatter.OrRelation(m_MemberRelation1.WhereClause,
string.Format("UID_Driver in (select UID_Driver from {0} where {1})",
m_MemberRelation1.MNTableName,
SqlFormatter.Comparison(mnbasecolumn, dbobject[mnbasecolumn].New.String, ValType.String, CompareOperator.Equal, FormatterOptions.None)
));
}
finally
{
}
}
/// <summary>
/// Converts the query expression into text of this query language
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
public virtual string Format(Expression expression)
{
// use common SQL formatter by default
return(SqlFormatter.Format(expression));
}
public void SqlStartsWithoutEmptySpaceTest()
{
string inputSql = "exec sp_executesql N' SELECT TOP 1000 \"obj\".\"Actual\", \"doc\".\"DescrDoc\" AS \"Annotation\", \"doc\".\"Composition\", \"obj\".\"CreateDateTime\", \"obj\".\"EditorID\" AS \"CUREDITOR\", \"obj\".\"EditorID\" AS \"CurrentEditorID\", (select case when (select stateid from lderc where id=doc.id)=6 then (SELECT TOP 1 o.ExecDateTime FROM LDOPERATION o WHERE o.OperTypeID = 2142 AND o.ObjectID = doc.ID ORDER BY o.ID DESC)else null end) AS \"DeleteDate\", (select case when (select stateid from lderc where id=doc.id)=6 then (SELECT TOP 1 v.Name FROM LDOPERATION o JOIN LDVocabulary v ON o.MemberID = v.ID WHERE o.OperTypeID = 2142 AND o.ObjectID = doc.ID ORDER BY o.ID DESC) else '''' end) AS \"DeletedByUser\", \"abstDoc\".\"EntityName\", \"doc\".\"ID\", \"obj\".\"LastEditDateTime\" AS \"LastEditDate\", \"obj\".\"LastEditDateTime\", \"LASTEDITSESSION.USER.voc\".\"Name\" AS \"LastEditor\", \"abstDoc\".\"Name\", \"doc\".\"N\" AS \"Number\", \"obj\".\"ObjectTypeID\" AS \"ObjectType\", \"obj\".\"ObjID\", \"doc\".\"RegDate\" AS \"RegistrationDate\", \"doc\".\"DocN\" AS \"RegistrationNumber\", \"CHANGE.LDObjectChange\".\"LastChangedDate\" AS \"ServerLastUpdateTime\", \"doc\".\"SheafCount\", \"doc\".\"StateID\" AS \"State\", \"obj\".\"UID\", \"doc\".\"VersionCount\", \"obj\".\"VersionN\", \"doc\".\"Viewers\", charindex(@V0, \"doc\".\"Viewers\") AS \"ViewFlag\", \"doc\".\"N\" AS \"XNumber\", \"doc\".\"ID\" AS \"DatabaseID\", \"doc\".\"AccessID\", \"obj\".\"ObjectTypeID\" AS \"CardID\", \"doc\".\"JournalID\" AS \"ContainerID\", \"obj\".\"EditorID\" AS \"CurEditorID\", \"doc\".\"DeliveryTypeID\", \"doc\".\"StateDocumentID\" AS \"DocumentStateID\", \"doc\".\"DocumTypeID\", \"doc\".\"JournalID\" FROM LDERC \"doc\" INNER JOIN LDObject \"obj\" ON (\"doc\".\"ID\" = \"obj\".\"ID\") INNER JOIN VIEW30ABSTRACTDOC \"abstDoc\" ON (\"doc\".\"ID\" = \"abstDoc\".\"ID\") LEFT OUTER JOIN LDSession \"LASTEDITSESSION.sess\" ON (\"obj\".\"LastEditSessionID\" = \"LASTEDITSESSION.sess\".\"ID\") LEFT OUTER JOIN LDVocabulary \"LASTEDITSESSION.USER.voc\" ON (\"LASTEDITSESSION.sess\".\"UserID\" = \"LASTEDITSESSION.USER.voc\".\"ID\") LEFT OUTER JOIN LDObjectChange \"CHANGE.LDObjectChange\" ON (\"obj\".\"ID\" = \"CHANGE.LDObjectChange\".\"ObjectID\") LEFT OUTER JOIN LDJournal \"ContainerID.jrn\" ON (\"doc\".\"JournalID\" = \"ContainerID.jrn\".\"ID\") WHERE ( ( ( ( ( ( \"doc\".\"StateID\" <> @V1 ) AND \"doc\".\"JournalID\" = @V2 ) AND ( \"doc\".\"AccessID\" <= @V3 OR ( ( \"doc\".\"AccessID\" = 999 OR ( \"doc\".\"AccessID\" = 1000 AND ( @V4 = @V5 OR \"doc\".\"ID\" IN ( SELECT \"LDMemberObject\".\"ObjectID\" AS \"ID\" FROM LDMemberObject \"LDMemberObject\" WHERE \"LDMemberObject\".\"MemberID\" = @V6 ) ) ) ) AND \"doc\".\"ID\" IN ( SELECT \"LDMemberObject\".\"ObjectID\" AS \"ID\" FROM LDMemberObject \"LDMemberObject\" WHERE \"LDMemberObject\".\"MemberID\" IN ( select /*10*/ * from dbo.LD30_Str2List (@L7) ) ) ) ) ) ) AND ( \"doc\".\"StateID\" <> 6 OR \"doc\".\"JournalID\" IN ( SELECT \"LDRightObject\".\"ObjectID\" AS \"ID\" FROM LDRightObject \"LDRightObject\" WHERE ( ( \"LDRightObject\".\"RightID\" = 2145 AND \"LDRightObject\".\"MemberID\" IN ( select /*10*/ * from dbo.LD30_Str2List (@L8) ) ) OR ( \"LDRightObject\".\"RightID\" IN ( SELECT \"LDRight\".\"ID\" FROM LDRight \"LDRight\" WHERE ( \"LDRight\".\"ID\" = 2145 AND \"LDRight\".\"Enabled\" = ''-'' ) ) ) ) ) ) ) AND ( \"doc\".\"StateID\" <> 5 AND \"ContainerID.jrn\".\"Name\" <> ''Скрытый журнал для проектов'' ) ) ORDER BY \"doc\".\"ID\" DESC',N'@V0 nvarchar(6),@V1 bigint,@V2 bigint,@V3 bigint,@V4 bigint,@V5 bigint,@V6 bigint,@L7 varchar(28),@L8 varchar(28)',@V0=N',1000,',@V1=6,@V2=462022,@V3=1,@V4=1000,@V5=1000,@V6=1000,@L7='1000,1010,1011,356938,381781',@L8='1000,1010,1011,356938,381781'";
#region long output
string expectedOutputSql = @"SELECT TOP 1000 ""obj"".""Actual""
,""doc"".""DescrDoc"" AS ""Annotation""
,""doc"".""Composition""
,""obj"".""CreateDateTime""
,""obj"".""EditorID"" AS ""CUREDITOR""
,""obj"".""EditorID"" AS ""CurrentEditorID""
,(
SELECT CASE
WHEN (
SELECT stateid
FROM lderc
WHERE id = doc.id
) = 6
THEN (
SELECT TOP 1 o.ExecDateTime
FROM LDOPERATION o
WHERE o.OperTypeID = 2142
AND o.ObjectID = doc.ID
ORDER BY o.ID DESC
)
ELSE NULL
END
) AS ""DeleteDate""
,(
SELECT CASE
WHEN (
SELECT stateid
FROM lderc
WHERE id = doc.id
) = 6
THEN (
SELECT TOP 1 v.NAME
FROM LDOPERATION o
JOIN LDVocabulary v ON o.MemberID = v.ID
WHERE o.OperTypeID = 2142
AND o.ObjectID = doc.ID
ORDER BY o.ID DESC
)
ELSE ''
END
) AS ""DeletedByUser""
,""abstDoc"".""EntityName""
,""doc"".""ID""
,""obj"".""LastEditDateTime"" AS ""LastEditDate""
,""obj"".""LastEditDateTime""
,""LASTEDITSESSION.USER.voc"".""Name"" AS ""LastEditor""
,""abstDoc"".""Name""
,""doc"".""N"" AS ""Number""
,""obj"".""ObjectTypeID"" AS ""ObjectType""
,""obj"".""ObjID""
,""doc"".""RegDate"" AS ""RegistrationDate""
,""doc"".""DocN"" AS ""RegistrationNumber""
,""CHANGE.LDObjectChange"".""LastChangedDate"" AS ""ServerLastUpdateTime""
,""doc"".""SheafCount""
,""doc"".""StateID"" AS ""State""
,""obj"".""UID""
,""doc"".""VersionCount""
,""obj"".""VersionN""
,""doc"".""Viewers""
,charindex(N',1000,', ""doc"".""Viewers"") AS ""ViewFlag""
,""doc"".""N"" AS ""XNumber""
,""doc"".""ID"" AS ""DatabaseID""
,""doc"".""AccessID""
,""obj"".""ObjectTypeID"" AS ""CardID""
,""doc"".""JournalID"" AS ""ContainerID""
,""obj"".""EditorID"" AS ""CurEditorID""
,""doc"".""DeliveryTypeID""
,""doc"".""StateDocumentID"" AS ""DocumentStateID""
,""doc"".""DocumTypeID""
,""doc"".""JournalID""
FROM LDERC ""doc""
INNER JOIN LDObject ""obj"" ON (""doc"".""ID"" = ""obj"".""ID"")
INNER JOIN VIEW30ABSTRACTDOC ""abstDoc"" ON (""doc"".""ID"" = ""abstDoc"".""ID"")
LEFT OUTER JOIN LDSession ""LASTEDITSESSION.sess"" ON (""obj"".""LastEditSessionID"" = ""LASTEDITSESSION.sess"".""ID"")
LEFT OUTER JOIN LDVocabulary ""LASTEDITSESSION.USER.voc"" ON (""LASTEDITSESSION.sess"".""UserID"" = ""LASTEDITSESSION.USER.voc"".""ID"")
LEFT OUTER JOIN LDObjectChange ""CHANGE.LDObjectChange"" ON (""obj"".""ID"" = ""CHANGE.LDObjectChange"".""ObjectID"")
LEFT OUTER JOIN LDJournal ""ContainerID.jrn"" ON (""doc"".""JournalID"" = ""ContainerID.jrn"".""ID"")
WHERE (
(
(
(
(
(""doc"".""StateID"" <> 6)
AND ""doc"".""JournalID"" = 462022
)
AND (
""doc"".""AccessID"" <= 1
OR (
(
""doc"".""AccessID"" = 999
OR (
""doc"".""AccessID"" = 1000
AND (
1000 = 1000
OR ""doc"".""ID"" IN (
SELECT ""LDMemberObject"".""ObjectID"" AS ""ID""
FROM LDMemberObject ""LDMemberObject""
WHERE ""LDMemberObject"".""MemberID"" = 1000
)
)
)
)
AND ""doc"".""ID"" IN (
SELECT ""LDMemberObject"".""ObjectID"" AS ""ID""
FROM LDMemberObject ""LDMemberObject""
WHERE ""LDMemberObject"".""MemberID"" IN (
SELECT /*10*/ *
FROM dbo.LD30_Str2List('1000,1010,1011,356938,381781')
)
)
)
)
)
)
AND (
""doc"".""StateID"" <> 6
OR ""doc"".""JournalID"" IN (
SELECT ""LDRightObject"".""ObjectID"" AS ""ID""
FROM LDRightObject ""LDRightObject""
WHERE (
(
""LDRightObject"".""RightID"" = 2145
AND ""LDRightObject"".""MemberID"" IN (
SELECT /*10*/ *
FROM dbo.LD30_Str2List('1000,1010,1011,356938,381781')
)
)
OR (
""LDRightObject"".""RightID"" IN (
SELECT ""LDRight"".""ID""
FROM LDRight ""LDRight""
WHERE (
""LDRight"".""ID"" = 2145
AND ""LDRight"".""Enabled"" = '-'
)
)
)
)
)
)
)
AND (
""doc"".""StateID"" <> 5
AND ""ContainerID.jrn"".""Name"" <> 'Скрытый журнал для проектов'
)
)
ORDER BY ""doc"".""ID"" DESC
";
#endregion
var formatter = new SqlFormatter();
string outputSql;
Assert.IsTrue(formatter.FormatSql(inputSql, out outputSql));
Assert.AreEqual(expectedOutputSql, outputSql);
}
/// <summary>
/// Constructor to initialise the connection object with an
/// assembly name, class name and connection string
/// </summary>
/// <param name="assemblyName">The assembly name</param>
/// <param name="className">The class name</param>
/// <param name="connectString">The connection string, which can be
/// generated using ConnectionStringOracleFactory.CreateConnectionString()
/// </param>
public DatabaseConnectionOracle(string assemblyName, string className, string connectString)
: base(assemblyName, className, connectString)
{
_sqlFormatter = new SqlFormatter("", "", "", "ROWNUM <=");
}
/// <summary>
/// Wird aufgerufen, bevor der MainActivator aktiviert wird.
/// Hier sollten alle von einem DB-Objekt abhängige Initialisierungen
/// durchgeführt werden.
/// </summary>
private void MainActivator_OnActivating(object sender, System.EventArgs e)
{
try
{
// Daten holen und prüfen
ISingleDbObject dbobject = m_MainActivator.DbObject;
if (dbobject == null)
{
return;
}
ISqlFormatter f = dbobject.Connection.SqlFormatter;
string statement = string.Format("select UpdateWhereClause from DialogTableGroupRight where {0} and UID_DialogGroup in (select UID_DialogGroup from DialogUserInGroup where UID_DialogUser in (select UID_DialogUser from DialogUser where {1}))",
f.AndRelation(
f.Comparison("UID_DialogTable", "SDL-T-SDLDomain", ValType.String),
f.Comparison("CanEdit", true, ValType.Bool)
),
f.Comparison("username", dbobject.Connection.User.Identifier, ValType.String));
SqlExecutor exec = dbobject.Connection.CreateSqlExecutor((byte[])m_MainActivator.Variables[GenericPropertyDictionary.ConnectionPublicKey, null]);
IDataReader datareader = null;
using (datareader = exec.SqlExecute(statement))
{
statement = "select count(*) from SDLDOmain where " + SqlFormatter.UidComparison("UID_SDLDomain", dbobject["UID_SDLDomainRDOwner"].New.String);
string wherest = "";
while (datareader.Read())
{
string where = datareader.GetString(0).Trim();
if (where == "")
{
wherest = "";
break;
}
wherest = wherest.Length == 0 ? string.Format("({0})", where) :
string.Format("{0} or ({1})", wherest, where);
}
if (wherest != "")
{
statement = statement + string.Format(" and ({0})", wherest);
}
}
object result = exec.SqlExecuteScalar(Connection.Variables.Replace(statement));
if (result != null && Convert.ToInt32(result) > 0)
{
m_MemberRelation1.WhereClause = string.Format("(UID_SDLDomain in (select UID_SDLDomain from ApplicationServer) or {0}) and not (UID_SDLDomain in (select UID_SDLDomain from ApplicationServer Where {1}))",
SqlFormatter.UidComparison("UID_ServerTAS", "", CompareOperator.NotEqual),
SqlFormatter.Comparison("IsCentralLibrary", true, ValType.Bool));
m_LblHinweis.Visible = false;
}
else
{
m_MemberRelation1.WhereClause = string.Format("{0} = {1}", SqlFormatter.FormatValue(1, ValType.Int), SqlFormatter.FormatValue(2, ValType.Int));
m_LblHinweis.Visible = true;
}
}
finally
{
}
}
/// <summary>
///
/// </summary>
private void Combo1_OnSelectionChangeCommitted(object sender, System.EventArgs e)
{
try
{
// Daten holen
ISingleDbObject dbobject = m_MainActivator.DbObject;
m_Combo2.ComboItems.Clear();
if (m_Combo1.SelectedIndex == 0 || m_Combo1.SelectedIndex == 1 || m_Combo1.SelectedIndex == 4)
{
m_Combo2.Enabled = false;
}
else
{
m_ColAppServer = Connection.CreateCol("ApplicationServer");
m_ColAppServer.Prototype.WhereClause = FormTool.CanSee(dbobject, "UID_SDLDomainRD") ? SqlFormatter.AndRelation(
SqlFormatter.UidComparison("UID_SDLDomain", dbobject["UID_SDLDomainRD"].New.String),
" not " + SqlFormatter.EmptyClause("UID_ParentApplicationServer", ValType.String))
: "1=2";
m_ColAppServer.Prototype.Columns["UID_Server"].IsDisplayItem = true;
m_ColAppServer.Load();
if (m_ColAppServer.Count > 0)
{
foreach (IColElem elem in m_ColAppServer)
{
m_Combo2.ComboItems.Add(elem);
}
m_Combo2.Enabled = true;
}
else
{
FormTool.ShowMessage("SDL_FormApplicationProfileCopyAll_Message_NoChildServerActionNotPossible");
m_Combo2.Enabled = false;
}
}
}
catch (Exception ex)
{
// Fehler melden
ExceptionMgr.Instance.HandleException(ex, this, 100);
}
}
/// <summary>
///
/// </summary>
private void Combo1_OnSelectionChangeCommitted(object sender, System.EventArgs e)
{
try
{
m_Combo2.ComboItems.Clear();
if (m_Combo1.SelectedIndex == 2 || m_Combo1.SelectedIndex == 3)
{
m_Label1.Caption = "SDL_FormMachineTypeCopyToOtherSite_Label1";
m_ColAppServer = Connection.CreateCol("ApplicationServer");
m_ColAppServer.Prototype.WhereClause = !FormTool.CanSee(Connection, "ApplicationServer", "UID_Server", "UID_SDLDomain") ? "1=2" :
SqlFormatter.Comparison("IsCentralLibrary", true, ValType.Bool);
m_ColAppServer.Prototype.Columns["UID_Server"].IsDisplayItem = true;
m_ColAppServer.Prototype.Columns["UID_SDLDomain"].IsDisplayItem = true;
m_ColAppServer.Load();
switch (m_ColAppServer.Count)
{
// korrekt
case 1:
m_ColDestinationDomain = Connection.CreateCol("SDLDomain");
m_ColDestinationDomain.Prototype.WhereClause =
SqlFormatter.UidComparison("UID_SDLDomain", m_ColAppServer[0]["UID_SDLDomain"].ToString(), CompareOperator.NotEqual);
m_ColDestinationDomain.Load();
if (m_ColDestinationDomain.Count > 0)
{
foreach (IColElem elem in m_ColDestinationDomain)
{
m_Combo2.ComboItems.Add(elem);
}
// m_Combo2.Enabled = true;
}
else
{
FormTool.ShowMessage("SDL_FormMachineTypeCopyToOtherSite_Message_NoDomain");
// m_Combo2.Enabled = false;
}
break;
case 0:
FormTool.ShowMessage("SDL_FormMachineTypeCopyToOtherSite_Message_NoAppServer");
// m_Combo2.Enabled = false;
break;
default:
FormTool.ShowMessage("SDL_FormMachineTypeCopyToOtherSite_Message_MoreAppServer");
// m_Combo2.Enabled = false;
break;
}
}
else
{
// Daten holen und prüfen
ISingleDbObject dbobject = m_MainActivator.DbObject;
if (dbobject == null)
{
return;
}
m_Label1.Caption = "SDL_FormApplicationProfileCopyAll_ChildServer";
m_ColAppServer = Connection.CreateCol("ApplicationServer");
m_ColAppServer.Prototype.WhereClause = !FormTool.CanSee(dbobject, "UID_SDLDomain") ? "1=2" :
SqlFormatter.AndRelation(
SqlFormatter.Comparison("UID_SDLDomain", dbobject["UID_SDLDomain"].New.String, ValType.String),
" not " + SqlFormatter.EmptyClause("UID_ParentApplicationServer", ValType.String));
m_ColAppServer.Load();
if (m_ColAppServer.Count > 0)
{
foreach (IColElem elem in m_ColAppServer)
{
m_Combo2.ComboItems.Add(elem);
}
}
else
{
FormTool.ShowMessage("SDL_FormApplicationProfileCopyAll_Message_NoChildServerActionNotPossible");
}
}
// damit die neue Caption angezeigt wird
m_Label1.Invalidate();
}
catch (Exception ex)
{
// Fehler melden
VI.FormBase.ExceptionMgr.Instance.HandleException(ex, this, 100);
}
}
private void LoadData()
{
using (new VI.FormBase.UpdateHelper(m_TreeList))
{
m_TreeList.Nodes.Clear();
// Daten holen und prüfen
ISingleDbObject dbobject = m_MainActivator.DbObject;
if (dbobject == null || !dbobject.TableDef.CanSee)
{
return;
}
IColDbObject col = Connection.CreateCol(m_DataStore.String1);
col.Prototype.WhereClause = SqlFormatter.Comparison(m_DataStore.String2, FormTool.GetValueSafe(dbobject, m_DataStore.String2, ""), ValType.String);
col.Prototype.Columns["CountLicMacDirectTarget"].IsDisplayItem = true;
col.Prototype.Columns["CountLicMacIndirectTarget"].IsDisplayItem = true;
col.Prototype.Columns["CountLicUserTarget"].IsDisplayItem = true;
col.Prototype.Columns["CountLicMacPossTarget"].IsDisplayItem = true;
col.Prototype.Columns["CountLicMacDirectActual"].IsDisplayItem = true;
col.Prototype.Columns["CountLicMacDirectActual"].IsDisplayItem = true;
col.Prototype.Columns["CountLicMacIndirectActual"].IsDisplayItem = true;
col.Prototype.Columns["CountLicUserActual"].IsDisplayItem = true;
col.Prototype.Columns["CountLicMacPossActual"].IsDisplayItem = true;
col.Prototype.Columns["CountLicMacReal"].IsDisplayItem = true;
col.Prototype.Columns["CountLimit"].IsDisplayItem = true;
col.Load();
bool canedit = col.Prototype.Columns["CountLimit"].CanEdit;
bool[] cansees = new bool[]
{
col.Prototype.Columns["UID_Licence"].CanSee,
col.Prototype.Columns["CountLimit"].CanSee,
col.Prototype.Columns["CountLicMacDirectActual"].CanSee,
col.Prototype.Columns["CountLicMacDirectTarget"].CanSee,
col.Prototype.Columns["CountLicMacIndirectActual"].CanSee,
col.Prototype.Columns["CountLicMacIndirectTarget"].CanSee,
col.Prototype.Columns["CountLicMacPossActual"].CanSee,
col.Prototype.Columns["CountLicMacPossTarget"].CanSee,
col.Prototype.Columns["CountLicMacReal"].CanSee,
col.Prototype.Columns["CountLicUserActual"].CanSee,
col.Prototype.Columns["CountLicUserTarget"].CanSee,
};
// und Grid füllen
foreach (IColElem elem in col)
{
ISingleDbObject obj = elem.Create();
ISingleDbObject identfk = obj.GetFK("UID_Licence").Create();
string ident = identfk != null ? identfk["Ident_Licence"].New.String : "";
TreeListNode node = m_TreeList.Nodes.Add(cansees[0] ? ident : "", 0);
node.ForeColor = SystemColors.ControlDark;
ITreeListItem subitem = canedit ? new TreeListItemTextBox(cansees[1] ? elem["CountLimit"].ToString() : "", 8) :
new TreeListItem(cansees[1] ? elem["CountLimit"].ToString() : "");
node.SubItems.Add(subitem);
subitem.ForeColor = canedit ? SystemColors.ControlText : SystemColors.ControlDark;
subitem = new TreeListItem(cansees[2] ? elem["CountLicMacReal"].ToString() : "");
node.SubItems.Add(subitem);
subitem.ForeColor = SystemColors.ControlDark;
subitem = new TreeListItem(cansees[3] ? elem["CountLicUserActual"].ToString() : "");
node.SubItems.Add(subitem);
subitem.ForeColor = SystemColors.ControlDark;
subitem = new TreeListItem(cansees[4] ? elem["CountLicUserTarget"].ToString() : "");
node.SubItems.Add(subitem);
subitem.ForeColor = SystemColors.ControlDark;
subitem = new TreeListItem(cansees[5] ? elem["CountLicMacDirectActual"].ToString() : "");
node.SubItems.Add(subitem);
subitem.ForeColor = SystemColors.ControlDark;
subitem = new TreeListItem(cansees[6] ? elem["CountLicMacDirectTarget"].ToString() : "");
node.SubItems.Add(subitem);
subitem.ForeColor = SystemColors.ControlDark;
subitem = new TreeListItem(cansees[7] ? elem["CountLicMacIndirectActual"].ToString() : "");
node.SubItems.Add(subitem);
subitem.ForeColor = SystemColors.ControlDark;
subitem = new TreeListItem(cansees[8] ? elem["CountLicMacIndirectTarget"].ToString() : "");
node.SubItems.Add(subitem);
subitem.ForeColor = SystemColors.ControlDark;
subitem = new TreeListItem(cansees[9] ? elem["CountLicMacPossActual"].ToString() : "");
node.SubItems.Add(subitem);
subitem.ForeColor = SystemColors.ControlDark;
subitem = new TreeListItem(cansees[10] ? elem["CountLicMacPossTarget"].ToString() : "");
node.SubItems.Add(subitem);
subitem.ForeColor = SystemColors.ControlDark;
node.Tag = obj;
}
}
}
public SqlFormatProvider(ExecuteParameterCollection parameters, IDbEngineProvider engineProvider)
{
_formatter = new SqlFormatter(parameters, engineProvider);
}
/// <summary>
/// Formats the query.
/// </summary>
/// <param name="query">The query.</param>
/// <returns></returns>
protected virtual string FormatQuery(Expression query)
{
return(SqlFormatter.Format(query));
}
/// <summary>
/// Tranfors the <see cref="object"/> value to a sql formatted string
/// </summary>
/// <param name="value">The value to transformed</param>
/// <returns>The formatted sql string</returns>
public static string ToSql(this object value)
{
return(SqlFormatter.Format(null, value));
}
private void SetupSqlFormatter()
{
_sqlFormatter = new SqlFormatter("\"", "\"", "FIRST", "");
}
