Driver/SpannerSchemaRetriever.cs
/// <summary>
/// Retrieves the table- and field meta data for the tables which names are in the passed in elementNames and which are in the schema specified.
/// </summary>
/// <param name="schemaToFill">The schema to fill.</param>
/// <param name="elementNames">The element names.</param>
/// <remarks>Implementers should add DBTable instances with the DBTableField instances to the DBSchema instance specified.
/// Default implementation is a no-op</remarks>
Retrieves the table- and field meta data for the tables which names are in the passed in elementNames and which are in the schema specified.
Retrieves the table - and field meta data for the tables which names are in the passed in elementNames and which are in the schema specified .
protected override void RetrieveTableAndFieldMetaData(DBSchema schemaToFill, IEnumerable<DBElementName> elementNames)
		{
			#region Descriptions of queries used.
			 ----------------------------------------------------------------
			 For the PK retrieval, this query is used:
			 SELECT I.TABLE_NAME, I.INDEX_NAME, I.PARENT_TABLE_NAME, IC.COLUMN_NAME, IC.ORDINAL_POSITION
			 FROM INFORMATION_SCHEMA.INDEXES I INNER JOIN INFORMATION_SCHEMA.INDEX_COLUMNS IC ON I.TABLE_SCHEMA=IC.TABLE_SCHEMA AND I.TABLE_NAME=IC.TABLE_NAME AND I.INDEX_NAME=IC.INDEX_NAME
			 WHERE I.TABLE_SCHEMA='' AND I.TABLE_NAME = @sTableName
			 ORDER BY I.TABLE_NAME ASC, I.INDEX_NAME ASC, IC.ORDINAL_POSITION ASC"
			#endregion
			string fieldQuery = "SELECT C.TABLE_NAME, C.COLUMN_NAME, C.ORDINAL_POSITION, C.IS_NULLABLE, C.SPANNER_TYPE, CO.OPTION_NAME, CO.OPTION_TYPE, CO.OPTION_VALUE FROM INFORMATION_SCHEMA.COLUMNS C LEFT JOIN INFORMATION_SCHEMA.COLUMN_OPTIONS CO ON C.TABLE_NAME=CO.TABLE_NAME AND C.COLUMN_NAME=CO.COLUMN_NAME WHERE C.TABLE_SCHEMA='' AND C.TABLE_NAME=@sTableName ORDER BY C.TABLE_NAME ASC, C.ORDINAL_POSITION";
			DbConnection connection = this.DriverToUse.CreateConnection();
			DbCommand fieldRetrievalCmd = this.DriverToUse.CreateCommand(connection, fieldQuery);
			DbParameter tableNameParameter = this.DriverToUse.CreateParameter(fieldRetrievalCmd, "@sTableName", DbType.String, 250);
			DbDataAdapter fieldRetrievalAdapter = this.DriverToUse.CreateDataAdapter(fieldRetrievalCmd);
			string pkRetrievalQuery = "SELECT I.TABLE_NAME, I.INDEX_NAME, I.PARENT_TABLE_NAME, IC.COLUMN_NAME, IC.ORDINAL_POSITION FROM INFORMATION_SCHEMA.INDEXES I INNER JOIN INFORMATION_SCHEMA.INDEX_COLUMNS IC ON I.TABLE_SCHEMA=IC.TABLE_SCHEMA AND I.TABLE_NAME=IC.TABLE_NAME AND I.INDEX_NAME=IC.INDEX_NAME WHERE I.TABLE_SCHEMA='' AND I.TABLE_NAME=@sTableName ORDER BY I.TABLE_NAME ASC, I.INDEX_NAME ASC, IC.ORDINAL_POSITION ASC";
			DbCommand pkRetrievalCmd = this.DriverToUse.CreateCommand(connection, pkRetrievalQuery);
			DbParameter pkRetrievalTableNameParam = this.DriverToUse.CreateParameter(pkRetrievalCmd, "@sTableName", DbType.String, 250);
			DbDataAdapter pkRetrievalAdapter = this.DriverToUse.CreateDataAdapter(pkRetrievalCmd);
			DataTable fieldsInTable = new DataTable();
			DataTable pkFieldsInTable = new DataTable();
			List<DBTable> tablesToRemove = new List<DBTable>();
			try
			{
				connection.Open();
				fieldRetrievalCmd.Prepare();
				pkRetrievalCmd.Prepare();
				 walk all tables in list of elementnames and pull information for this table out of the db.
				foreach(DBElementName tableName in elementNames)
				{
					if(tableName==null)
					{
						continue;
					}
					DBTable currentTable = new DBTable(schemaToFill, tableName);
					schemaToFill.Tables.Add(currentTable);
					tableNameParameter.Value = tableName.Name;
					 get the fields. 
					fieldsInTable.Clear();
					this.DriverToUse.ExecuteWithActiveRecoveryStrategy(() => fieldRetrievalAdapter.Fill(fieldsInTable));
					try
					{
						var fields = from row in fieldsInTable.AsEnumerable()
									 let typeDefinition = CreateTypeDefinition(row)
									 select new DBTableField(row.Value<string>("COLUMN_NAME"), typeDefinition)
									 {
										 OrdinalPosition =row.Value<int>("ORDINAL_POSITION"),
										 IsNullable = (row.Value<string>("IS_NULLABLE") == "YES"),
										  set iscomputed to true if the DB has to make sure the timestamp value is set. This will make the entity field become readonly.
										 DefaultValue = (typeDefinition.DBType == (int)SpannerDbTypes.Timestamp && row.Value<string>("OPTION_NAME")=="allow_commit_timestamp" && 
														row.Value<string>("OPTION_VALUE")=="TRUE") ? "allow_commit_timestamp" : string.Empty,
										 ParentTable = currentTable
									 };
						currentTable.Fields.AddRange(fields);
						 get Primary Key fields for this table
						pkRetrievalTableNameParam.Value = tableName.Name;
						pkFieldsInTable.Clear();
						this.DriverToUse.ExecuteWithActiveRecoveryStrategy(() => pkRetrievalAdapter.Fill(pkFieldsInTable));
						foreach(DataRow row in pkFieldsInTable.AsEnumerable())
						{
							DBTableField primaryKeyField = currentTable.FindFieldByName(row.Value<string>("COLUMN_NAME"));
							if(primaryKeyField != null)
							{
								primaryKeyField.IsPrimaryKey = true;
								currentTable.PkConstraintName = row.Value<string>("INDEX_NAME") ?? string.Empty;
							}
						}
					}
					catch(ApplicationException ex)
					{
						 non fatal error, remove the table, proceed 
						schemaToFill.LogError(ex, "Table '" + currentTable.Name + "' removed from list due to an internal exception in Field population: " + ex.Message, "SpannerSchemaRetriever::RetrieveTableAndFieldMetaData");
						tablesToRemove.Add(currentTable);
					}
					catch(InvalidCastException ex)
					{
						 non fatal error, remove the table, proceed 
						schemaToFill.LogError(ex, "Table '" + currentTable.Name + "' removed from list due to cast exception in Field population.", "SpannerSchemaRetriever::RetrieveTableAndFieldMetaData");
						tablesToRemove.Add(currentTable);
					}
				}
			}
			finally
			{
				connection.SafeClose(true);
			}
			foreach(DBTable toRemove in tablesToRemove)
			{
				schemaToFill.Tables.Remove(toRemove);
			}
		}
protected override void RetrieveTableAndFieldMetaData ( DBSchema schemaToFill , IEnumerable < DBElementName > elementNames ) { region  Descriptions of queries used. endregion string fieldQuery = " " ; DbConnection connection = this . DriverToUse . CreateConnection ( ) ; DbCommand fieldRetrievalCmd = this . DriverToUse . CreateCommand ( connection , fieldQuery ) ; DbParameter tableNameParameter = this . DriverToUse . CreateParameter ( fieldRetrievalCmd , " " , DbType . String , 250 ) ; DbDataAdapter fieldRetrievalAdapter = this . DriverToUse . CreateDataAdapter ( fieldRetrievalCmd ) ; string pkRetrievalQuery = " " ; DbCommand pkRetrievalCmd = this . DriverToUse . CreateCommand ( connection , pkRetrievalQuery ) ; DbParameter pkRetrievalTableNameParam = this . DriverToUse . CreateParameter ( pkRetrievalCmd , " " , DbType . String , 250 ) ; DbDataAdapter pkRetrievalAdapter = this . DriverToUse . CreateDataAdapter ( pkRetrievalCmd ) ; DataTable fieldsInTable = new DataTable ( ) ; DataTable pkFieldsInTable = new DataTable ( ) ; List < DBTable > tablesToRemove = new List < DBTable > ( ) ; try { connection . Open ( ) ; fieldRetrievalCmd . Prepare ( ) ; pkRetrievalCmd . Prepare ( ) ; foreach ( DBElementName tableName in elementNames ) { if ( tableName == null ) { continue ; } DBTable currentTable = new DBTable ( schemaToFill , tableName ) ; schemaToFill . Tables . Add ( currentTable ) ; tableNameParameter . Value = tableName . Name ; fieldsInTable . Clear ( ) ; this . DriverToUse . ExecuteWithActiveRecoveryStrategy ( ( ) => fieldRetrievalAdapter . Fill ( fieldsInTable ) ) ; try { var fields = from row in fieldsInTable . AsEnumerable ( ) let typeDefinition = CreateTypeDefinition ( row ) select new DBTableField ( row . Value < string > ( " " ) , typeDefinition ) { OrdinalPosition = row . Value < int > ( " " ) , IsNullable = ( row . Value < string > ( " " ) == " " ) , DefaultValue = ( typeDefinition . DBType == ( int ) SpannerDbTypes . Timestamp && row . Value < string > ( " " ) == " " && row . Value < string > ( " " ) == " " ) ? " " : string . Empty , ParentTable = currentTable } ; currentTable . Fields . AddRange ( fields ) ; pkRetrievalTableNameParam . Value = tableName . Name ; pkFieldsInTable . Clear ( ) ; this . DriverToUse . ExecuteWithActiveRecoveryStrategy ( ( ) => pkRetrievalAdapter . Fill ( pkFieldsInTable ) ) ; foreach ( DataRow row in pkFieldsInTable . AsEnumerable ( ) ) { DBTableField primaryKeyField = currentTable . FindFieldByName ( row . Value < string > ( " " ) ) ; if ( primaryKeyField != null ) { primaryKeyField . IsPrimaryKey = true ; currentTable . PkConstraintName = row . Value < string > ( " " ) ?? string . Empty ; } } } catch ( ApplicationException ex ) { schemaToFill . LogError ( ex , " " + currentTable . Name + " " + ex . Message , " " ) ; tablesToRemove . Add ( currentTable ) ; } catch ( InvalidCastException ex ) { schemaToFill . LogError ( ex , " " + currentTable . Name + " " , " " ) ; tablesToRemove . Add ( currentTable ) ; } } } finally { connection . SafeClose ( true ) ; } foreach ( DBTable toRemove in tablesToRemove ) { schemaToFill . Tables . Remove ( toRemove ) ; } }
Retrieves the table- and field meta data for the tables which names are in the passed in elementNames and which are in the schema specified.
Retrieves the table - and field meta data for the tables which names are in the passed in elementNames and which are in the schema specified .
// per table, get fields, populate field structure for table. // SELECT C.TABLE_NAME, C.COLUMN_NAME, C.ORDINAL_POSITION, C.IS_NULLABLE, C.SPANNER_TYPE, CO.OPTION_NAME, CO.OPTION_TYPE, CO.OPTION_VALUE // FROM INFORMATION_SCHEMA.COLUMNS C LEFT JOIN INFORMATION_SCHEMA.COLUMN_OPTIONS CO ON C.TABLE_NAME=CO.TABLE_NAME AND C.COLUMN_NAME=CO.COLUMN_NAME // WHERE C.TABLE_SCHEMA='' AND C.TABLE_NAME=@sTableName // ORDER BY C.TABLE_NAME ASC, C.ORDINAL_POSITION // // ---------------------------------------------------------------- // For the PK retrieval, this query is used: // SELECT I.TABLE_NAME, I.INDEX_NAME, I.PARENT_TABLE_NAME, IC.COLUMN_NAME, IC.ORDINAL_POSITION // FROM INFORMATION_SCHEMA.INDEXES I INNER JOIN INFORMATION_SCHEMA.INDEX_COLUMNS IC ON I.TABLE_SCHEMA=IC.TABLE_SCHEMA AND I.TABLE_NAME=IC.TABLE_NAME AND I.INDEX_NAME=IC.INDEX_NAME // WHERE I.TABLE_SCHEMA='' AND I.TABLE_NAME = @sTableName // ORDER BY I.TABLE_NAME ASC, I.INDEX_NAME ASC, IC.ORDINAL_POSITION ASC" // walk all tables in list of elementnames and pull information for this table out of the db. // get the fields.  // set iscomputed to true if the DB has to make sure the timestamp value is set. This will make the entity field become readonly. // get Primary Key fields for this table // non fatal error, remove the table, proceed  // non fatal error, remove the table, proceed 
schemaToFill
DBSchema
The schema to fill.
The schema to fill .
elementNames
IEnumerable<DBElementName>
The element names.
The element names .
remarks
Implementers should add DBTable instances with the DBTableField instances to the DBSchema instance specified.
Default implementation is a no-op
Implementers should add DBTable instances with the DBTableField instances to the DBSchema instance specified . Default implementation is a no - op