/// <summary>
/// Gets the SQL fragments.
/// </summary>
/// <param name="databaseStreamSpec">The database stream spec.</param>
/// <returns></returns>
private static IList<PlaceholderParser.Fragment> GetSqlFragments(
DBStatementStreamSpec databaseStreamSpec)
{
IList<PlaceholderParser.Fragment> sqlFragments;
try {
sqlFragments = PlaceholderParser.ParsePlaceholder(databaseStreamSpec.SqlWithSubsParams);
}
catch (PlaceholderParseException ex) {
const string text = "Error parsing SQL";
throw new ExprValidationException(text + ", reason: " + ex.Message, ex);
}
return sqlFragments;
}
public void TestDBStatementViewFactory()
{
DBStatementStreamSpec spec = new DBStatementStreamSpec("s0", ViewSpec.EMPTY_VIEWSPEC_ARRAY,
"mydb", "select * from mytesttable where mybigint=${idnum}", null);
EventCollection eventCollection = DatabasePollingViewableFactory.CreateDBStatementView(
"id", 1, spec,
SupportDatabaseService.MakeService(),
SupportEventAdapterService.Service, null, null, null, null, true);
Assert.AreEqual(typeof(long?), eventCollection.EventType.GetPropertyType("mybigint"));
Assert.AreEqual(typeof(string), eventCollection.EventType.GetPropertyType("myvarchar"));
Assert.AreEqual(typeof(bool?), eventCollection.EventType.GetPropertyType("mybool"));
Assert.AreEqual(typeof(decimal?), eventCollection.EventType.GetPropertyType("mynumeric"));
Assert.AreEqual(typeof(decimal?), eventCollection.EventType.GetPropertyType("mydecimal"));
}
public void TestDBStatementViewFactory()
{
var container = SupportContainer.Instance;
var spec = new DBStatementStreamSpec("s0", ViewSpec.EMPTY_VIEWSPEC_ARRAY,
"mydb", "select * from mytesttable where mybigint=${idnum}", null);
EventCollection eventCollection = DatabasePollingViewableFactory.CreateDBStatementView(
1, 1, spec,
SupportDatabaseService.MakeService(),
container.Resolve <EventAdapterService>(),
null, null, null, null, true, new DataCacheFactory(),
SupportStatementContextFactory.MakeContext(container));
Assert.AreEqual(typeof(long?), eventCollection.EventType.GetPropertyType("mybigint"));
Assert.AreEqual(typeof(string), eventCollection.EventType.GetPropertyType("myvarchar"));
Assert.AreEqual(typeof(bool?), eventCollection.EventType.GetPropertyType("mybool"));
Assert.AreEqual(typeof(decimal?), eventCollection.EventType.GetPropertyType("mynumeric"));
Assert.AreEqual(typeof(decimal?), eventCollection.EventType.GetPropertyType("mydecimal"));
}
/// <summary>
/// Creates the event type fields.
/// </summary>
/// <param name="databaseStreamSpec">The database stream spec.</param>
/// <param name="columnTypeConversionHook">The column type conversion hook.</param>
/// <param name="queryMetaData">The query meta data.</param>
/// <returns></returns>
private static IDictionary<string, object> CreateEventTypeFields(
DBStatementStreamSpec databaseStreamSpec,
SQLColumnTypeConversion columnTypeConversionHook,
QueryMetaData queryMetaData)
{
IDictionary<string, object> eventTypeFields = new Dictionary<string, object>();
var columnNum = 1;
foreach (var entry in queryMetaData.OutputParameters) {
var name = entry.Key;
var dbOutputDesc = entry.Value;
Type clazz;
if (dbOutputDesc.OptionalBinding != null) {
clazz = dbOutputDesc.OptionalBinding.DataType;
}
else {
clazz = dbOutputDesc.DataType;
}
if (columnTypeConversionHook != null) {
var newValue = columnTypeConversionHook.GetColumnType(
new SQLColumnTypeContext(
databaseStreamSpec.DatabaseName,
databaseStreamSpec.SqlWithSubsParams,
name,
clazz,
dbOutputDesc.SqlType,
columnNum));
if (newValue != null) {
clazz = newValue;
}
}
eventTypeFields.Put(name, clazz);
columnNum++;
}
return eventTypeFields;
}
/// <summary>
/// Creates the viewable for polling via database SQL query.
/// </summary>
/// <param name="statementId">The statement id.</param>
/// <param name="streamNumber">is the stream number of the view</param>
/// <param name="databaseStreamSpec">provides the SQL statement, database name and additional info</param>
/// <param name="databaseConfigService">for getting database connection and settings</param>
/// <param name="eventAdapterService">for generating event beans from database information</param>
/// <param name="epStatementAgentInstanceHandle">The ep statement agent instance handle.</param>
/// <param name="contextAttributes">The db attributes.</param>
/// <param name="columnTypeConversionHook">The column type conversion hook.</param>
/// <param name="outputRowConversionHook">The output row conversion hook.</param>
/// <param name="enableAdoLogging">if set to <c>true</c> [enable JDBC logging].</param>
/// <param name="dataCacheFactory">The data cache factory.</param>
/// <param name="statementContext">The statement context.</param>
/// <returns>
/// viewable providing poll functionality
/// </returns>
/// <exception cref="ExprValidationException">the validation failed</exception>
public static HistoricalEventViewable CreateDBStatementView(
int statementId,
int streamNumber,
DBStatementStreamSpec databaseStreamSpec,
DatabaseConfigService databaseConfigService,
EventAdapterService eventAdapterService,
EPStatementAgentInstanceHandle epStatementAgentInstanceHandle,
IEnumerable <Attribute> contextAttributes,
SQLColumnTypeConversion columnTypeConversionHook,
SQLOutputRowConversion outputRowConversionHook,
bool enableAdoLogging,
DataCacheFactory dataCacheFactory,
StatementContext statementContext)
{
// Parse the SQL for placeholders and text fragments
var sqlFragments = GetSqlFragments(databaseStreamSpec);
IList <String> invocationInputParameters = new List <string>();
foreach (var fragment in sqlFragments)
{
if ((fragment.IsParameter) && (fragment.Value != SAMPLE_WHERECLAUSE_PLACEHOLDER))
{
invocationInputParameters.Add(fragment.Value);
}
}
// Get the database information
var databaseName = databaseStreamSpec.DatabaseName;
var dbDriver = GetDatabaseConnectionFactory(databaseConfigService, databaseName).Driver;
var dbCommand = dbDriver.CreateCommand(
sqlFragments,
GetMetaDataSettings(databaseConfigService, databaseName),
contextAttributes);
if (Log.IsDebugEnabled)
{
Log.Debug(".CreateDBStatementView dbCommand=" + dbCommand);
}
var queryMetaData = GetQueryMetaData(
databaseStreamSpec,
databaseConfigService,
dbCommand,
contextAttributes);
Func <SQLColumnTypeContext, Type> columnTypeConversionFunc = null;
if (columnTypeConversionHook != null)
{
columnTypeConversionFunc = columnTypeConversionHook.GetColumnType;
}
Func <SQLOutputRowTypeContext, Type> outputRowConversionFunc = null;
if (outputRowConversionHook != null)
{
outputRowConversionFunc = outputRowConversionHook.GetOutputRowType;
}
// Construct an event type from SQL query result metadata
var eventType = CreateEventType(
statementId,
streamNumber,
queryMetaData,
eventAdapterService,
databaseStreamSpec,
columnTypeConversionFunc,
outputRowConversionFunc);
// Get a proper connection and data cache
ConnectionCache connectionCache;
DataCache dataCache;
try
{
connectionCache = databaseConfigService.GetConnectionCache(
databaseName, dbCommand.PseudoText, contextAttributes);
dataCache = databaseConfigService.GetDataCache(
databaseName, statementContext, epStatementAgentInstanceHandle, dataCacheFactory, streamNumber);
}
catch (DatabaseConfigException e)
{
const string text = "Error obtaining cache configuration";
Log.Error(text, e);
throw new ExprValidationException(text + ", reason: " + e.Message, e);
}
var dbPollStrategy = new PollExecStrategyDBQuery(
eventAdapterService,
eventType,
connectionCache,
dbCommand.CommandText,
queryMetaData.OutputParameters,
columnTypeConversionHook,
outputRowConversionHook);
return(new DatabasePollingViewable(
streamNumber,
invocationInputParameters,
dbPollStrategy,
dataCache,
eventType));
}
/// <summary>
/// Gets the query meta data.
/// </summary>
/// <param name="databaseStreamSpec">The database stream spec.</param>
/// <param name="databaseConfigService">The database config service.</param>
/// <param name="dbCommand">The database command.</param>
/// <param name="contextAttributes">The context attributes.</param>
/// <returns></returns>
private static QueryMetaData GetQueryMetaData(
DBStatementStreamSpec databaseStreamSpec,
DatabaseConfigService databaseConfigService,
DbDriverCommand dbCommand,
IEnumerable <Attribute> contextAttributes)
{
// Get a database connection
var databaseName = databaseStreamSpec.DatabaseName;
//DatabaseConnectionFactory databaseConnectionFactory = GetDatabaseConnectionFactory(databaseConfigService, databaseName);
var metadataSetting = dbCommand.MetaDataSettings;
QueryMetaData queryMetaData;
try
{
// On default setting, if we detect Oracle in the connection then don't query metadata from prepared statement
var metaOriginPolicy = metadataSetting.MetadataRetrievalEnum;
if (metaOriginPolicy == ConfigurationDBRef.MetadataOriginEnum.DEFAULT)
{
// Ask the driver how it interprets the default meta origin policy; the
// esper code has a specific hook for Oracle. We have moved this into
// the driver to avoid specifically coding behavior to a driver.
metaOriginPolicy = dbCommand.Driver.DefaultMetaOriginPolicy;
}
switch (metaOriginPolicy)
{
case ConfigurationDBRef.MetadataOriginEnum.METADATA:
case ConfigurationDBRef.MetadataOriginEnum.DEFAULT:
queryMetaData = dbCommand.MetaData;
break;
case ConfigurationDBRef.MetadataOriginEnum.SAMPLE:
{
var parameterDesc = dbCommand.ParameterDescription;
String sampleSQL;
if (databaseStreamSpec.MetadataSQL != null)
{
sampleSQL = databaseStreamSpec.MetadataSQL;
if (Log.IsInfoEnabled)
{
Log.Info(".GetQueryMetaData Using provided sample SQL '" + sampleSQL + "'");
}
}
else
{
// Create the sample SQL by replacing placeholders with null and
// SAMPLE_WHERECLAUSE_PLACEHOLDER with a "where 1=0" clause
sampleSQL = CreateSamplePlaceholderStatement(dbCommand.Fragments);
if (Log.IsInfoEnabled)
{
Log.Info(".GetQueryMetaData Using un-lexed sample SQL '" + sampleSQL + "'");
}
// If there is no SAMPLE_WHERECLAUSE_PLACEHOLDER, lexical analyse the SQL
// adding a "where 1=0" clause.
if (parameterDesc.BuiltinIdentifiers.Count != 1)
{
sampleSQL = LexSampleSQL(sampleSQL);
if (Log.IsInfoEnabled)
{
Log.Info(".GetQueryMetaData Using lexed sample SQL '" + sampleSQL + "'");
}
}
}
// finally get the metadata by firing the sample SQL
queryMetaData = GetExampleQueryMetaData(
dbCommand.Driver, sampleSQL, metadataSetting, contextAttributes);
}
break;
default:
throw new ArgumentException(
"MetaOriginPolicy contained an unhandled value: #" + metaOriginPolicy);
}
}
catch (DatabaseConfigException ex)
{
var text = "Error connecting to database '" + databaseName + '\'';
Log.Error(text, ex);
throw new ExprValidationException(text + ", reason: " + ex.Message, ex);
}
return(queryMetaData);
}
/// <summary>
/// Creates an event type from the query meta data.
/// </summary>
/// <param name="statementId">The statement id.</param>
/// <param name="streamNumber">The stream number.</param>
/// <param name="queryMetaData">The query meta data.</param>
/// <param name="eventAdapterService">The event adapter service.</param>
/// <param name="databaseStreamSpec">The database stream spec.</param>
/// <param name="columnTypeConversionHook">The column type conversion hook.</param>
/// <param name="outputRowConversionHook">The output row conversion hook.</param>
/// <returns></returns>
private static EventType CreateEventType(
int statementId,
int streamNumber,
QueryMetaData queryMetaData,
EventAdapterService eventAdapterService,
DBStatementStreamSpec databaseStreamSpec,
Func <SQLColumnTypeContext, Type> columnTypeConversionHook,
Func <SQLOutputRowTypeContext, Type> outputRowConversionHook)
{
var columnNum = 1;
var eventTypeFields = new Dictionary <String, Object>();
foreach (var entry in queryMetaData.OutputParameters)
{
var name = entry.Key;
var dbOutputDesc = entry.Value;
Type clazz;
if (dbOutputDesc.OptionalBinding != null)
{
clazz = dbOutputDesc.OptionalBinding.DataType;
}
else
{
clazz = dbOutputDesc.DataType;
}
if (columnTypeConversionHook != null)
{
var newValue = columnTypeConversionHook.Invoke(
new SQLColumnTypeContext(
databaseStreamSpec.DatabaseName,
databaseStreamSpec.SqlWithSubsParams,
name, clazz,
dbOutputDesc.SqlType,
columnNum));
if (newValue != null)
{
clazz = newValue;
}
}
eventTypeFields[name] = clazz.GetBoxedType();
columnNum++;
}
EventType eventType;
if (outputRowConversionHook == null)
{
var outputEventType = statementId + "_dbpoll_" + streamNumber;
eventType = eventAdapterService.CreateAnonymousMapType(outputEventType, eventTypeFields, true);
}
else
{
var carrierClass = outputRowConversionHook.Invoke(
new SQLOutputRowTypeContext(
databaseStreamSpec.DatabaseName,
databaseStreamSpec.SqlWithSubsParams,
eventTypeFields));
if (carrierClass == null)
{
throw new ExprValidationException("Output row conversion hook returned no type");
}
eventType = eventAdapterService.AddBeanType(carrierClass.FullName, carrierClass, false, false, false);
}
return(eventType);
}
public static HistoricalEventViewableDatabaseForge CreateDBStatementView(
int streamNum,
DBStatementStreamSpec databaseStreamSpec,
SQLColumnTypeConversion columnTypeConversionHook,
SQLOutputRowConversion outputRowConversionHook,
StatementBaseInfo statementBaseInfo,
StatementCompileTimeServices services,
IEnumerable<Attribute> contextAttributes)
{
// Parse the SQL for placeholders and text fragments
var sqlFragments = GetSqlFragments(databaseStreamSpec);
var invocationInputParameters = new List<string>();
foreach (var fragment in sqlFragments) {
if ((fragment.IsParameter) && (fragment.Value != SAMPLE_WHERECLAUSE_PLACEHOLDER)) {
invocationInputParameters.Add(fragment.Value);
}
}
// Assemble a PreparedStatement and parameter list
var preparedStatementText = CreatePreparedStatement(sqlFragments);
var parameterDesc = GetParameters(sqlFragments);
if (Log.IsDebugEnabled) {
Log.Debug(
".CreateDBStatementView preparedStatementText=" +
preparedStatementText +
" parameterDesc=" +
parameterDesc);
}
// Get a database connection
var databaseName = databaseStreamSpec.DatabaseName;
var dbDriver = services.DatabaseConfigServiceCompileTime
.GetConnectionFactory(databaseName)
.Driver;
var dbCommand = dbDriver.CreateCommand(
sqlFragments,
GetMetaDataSettings(services, databaseName),
contextAttributes);
if (Log.IsDebugEnabled) {
Log.Debug(".CreateDBStatementView dbCommand=" + dbCommand);
}
var queryMetaData = GetQueryMetaData(
databaseStreamSpec,
services,
dbCommand,
parameterDesc,
contextAttributes);
Func<SQLColumnTypeContext, Type> columnTypeConversionFunc =
columnTypeConversionHook != null
? columnTypeConversionHook.GetColumnType
: (Func<SQLColumnTypeContext, Type>) null;
Func<SQLOutputRowTypeContext, Type> outputRowConversionFunc =
outputRowConversionHook != null
? outputRowConversionHook.GetOutputRowType
: (Func<SQLOutputRowTypeContext, Type>) null;
// Construct an event type from SQL query result metadata
var eventType = CreateEventType(
streamNum,
queryMetaData,
services,
databaseStreamSpec,
columnTypeConversionHook,
outputRowConversionHook,
statementBaseInfo);
services.EventTypeCompileTimeRegistry.NewType(eventType);
return new HistoricalEventViewableDatabaseForge(
streamNum,
eventType,
databaseName,
queryMetaData.InputParameters.ToArray(),
preparedStatementText,
queryMetaData.OutputParameters);
}
/// <summary>
/// Gets the query meta data.
/// </summary>
/// <param name="databaseStreamSpec">The database stream spec.</param>
/// <param name="services"></param>
/// <param name="dbCommand">The database command.</param>
/// <param name="parameterDesc"></param>
/// <param name="contextAttributes">context attributes</param>
/// <returns></returns>
private static QueryMetaData GetQueryMetaData(
DBStatementStreamSpec databaseStreamSpec,
StatementCompileTimeServices services,
DbDriverCommand dbCommand,
SQLParameterDesc parameterDesc,
IEnumerable<Attribute> contextAttributes)
{
// On default setting, if we detect Oracle in the connection then don't query metadata from prepared statement
var metadataSetting = dbCommand.MetaDataSettings;
// On default setting, if we detect Oracle in the connection then don't query metadata from prepared statement
var metaOriginPolicy = metadataSetting.MetadataRetrievalEnum;
if (metaOriginPolicy == MetadataOriginEnum.DEFAULT) {
// Ask the driver how it interprets the default meta origin policy; the
// esper code has a specific hook for Oracle. We have moved this into
// the driver to avoid specifically coding behavior to a driver.
metaOriginPolicy = dbCommand.Driver.DefaultMetaOriginPolicy;
}
QueryMetaData queryMetaData;
switch (metaOriginPolicy) {
case MetadataOriginEnum.METADATA:
case MetadataOriginEnum.DEFAULT:
queryMetaData = dbCommand.MetaData;
// REWRITE: queryMetaData = GetPreparedStmtMetadata(
// connection, parameterDesc.Parameters, preparedStatementText, metadataSetting);
break;
case MetadataOriginEnum.SAMPLE: {
string sampleSQL;
if (databaseStreamSpec.MetadataSQL != null) {
sampleSQL = databaseStreamSpec.MetadataSQL;
if (Log.IsInfoEnabled) {
Log.Info(".GetQueryMetaData Using provided sample SQL '" + sampleSQL + "'");
}
}
else {
// Create the sample SQL by replacing placeholders with null and
// SAMPLE_WHERECLAUSE_PLACEHOLDER with a "where 1=0" clause
// REWRITE: sampleSQL = CreateSamplePlaceholderStatement(sqlFragments);
sampleSQL = CreateSamplePlaceholderStatement(dbCommand.Fragments);
if (Log.IsInfoEnabled) {
Log.Info(".GetQueryMetaData Using un-lexed sample SQL '" + sampleSQL + "'");
}
// If there is no SAMPLE_WHERECLAUSE_PLACEHOLDER, lexical analyse the SQL
// adding a "where 1=0" clause.
if (parameterDesc.BuiltinIdentifiers.Count != 1) {
sampleSQL = services.CompilerServices.LexSampleSQL(sampleSQL);
if (Log.IsInfoEnabled) {
Log.Info(".GetQueryMetaData Using lexed sample SQL '" + sampleSQL + "'");
}
}
}
// finally get the metadata by firing the sample SQL
queryMetaData = GetExampleQueryMetaData(
dbCommand.Driver,
sampleSQL,
metadataSetting,
contextAttributes);
break;
}
default:
throw new ArgumentException(
"MetaOriginPolicy Contained an unhandled value: #" + metaOriginPolicy);
}
return queryMetaData;
}
/// <summary>
/// Creates an event type from the query meta data.
/// </summary>
/// <param name="streamNum"></param>
/// <param name="queryMetaData">The query meta data.</param>
/// <param name="services"></param>
/// <param name="databaseStreamSpec">The database stream spec.</param>
/// <param name="columnTypeConversionHook">The column type conversion hook.</param>
/// <param name="outputRowConversionHook">The output row conversion hook.</param>
/// <param name="base"></param>
private static EventType CreateEventType(
int streamNum,
QueryMetaData queryMetaData,
StatementCompileTimeServices services,
DBStatementStreamSpec databaseStreamSpec,
SQLColumnTypeConversion columnTypeConversionHook,
SQLOutputRowConversion outputRowConversionHook,
StatementBaseInfo @base)
{
var eventTypeFields = CreateEventTypeFields(
databaseStreamSpec,
columnTypeConversionHook,
queryMetaData);
var eventTypeName = services.EventTypeNameGeneratorStatement.GetAnonymousDBHistorical(streamNum);
EventType eventType;
Func<EventTypeApplicationType, EventTypeMetadata> metadata = appType => new EventTypeMetadata(
eventTypeName,
@base.ModuleName,
EventTypeTypeClass.DBDERIVED,
appType,
NameAccessModifier.TRANSIENT,
EventTypeBusModifier.NONBUS,
false,
EventTypeIdPair.Unassigned());
if (outputRowConversionHook == null) {
eventType = BaseNestableEventUtil.MakeMapTypeCompileTime(
metadata.Invoke(EventTypeApplicationType.MAP),
eventTypeFields,
null,
null,
null,
null,
services.BeanEventTypeFactoryPrivate,
services.EventTypeCompileTimeResolver);
}
else {
var carrierClass = outputRowConversionHook.GetOutputRowType(
new SQLOutputRowTypeContext(
databaseStreamSpec.DatabaseName,
databaseStreamSpec.SqlWithSubsParams,
eventTypeFields));
if (carrierClass == null) {
throw new ExprValidationException("Output row conversion hook returned no type");
}
var stem = services.BeanEventTypeStemService.GetCreateStem(carrierClass, null);
eventType = new BeanEventType(
services.Container,
stem,
metadata.Invoke(EventTypeApplicationType.CLASS),
services.BeanEventTypeFactoryPrivate,
null,
null,
null,
null);
}
return eventType;
}
