/// <summary> /// Gets all of the schema in one call. /// </summary> /// <returns></returns> public DatabaseSchema ReadAll(List <string> lstLoadType) { _fixUp = false; DataTypes(); if (lstLoadType.Contains("用户")) { AllUsers(); } if (lstLoadType.Contains("表")) { AllTables(); } if (lstLoadType.Contains("视图")) { AllViews(); } if (lstLoadType.Contains("存储过程&函数")) { AllStoredProcedures(); } //oracle extra DatabaseSchema.Sequences.Clear(); var sequences = _schemaReader.Sequences(); DatabaseSchema.Sequences.AddRange(SchemaProcedureConverter.Sequences(sequences)); _fixUp = true; UpdateReferences(); return(_db); }
/// <summary> /// Gets all stored procedures (no arguments, for Oracle no packages) /// </summary> public IList <DatabaseStoredProcedure> StoredProcedureList() { DataTable dt = _sr.StoredProcedures(); SchemaProcedureConverter.StoredProcedures(DatabaseSchema, dt); return(DatabaseSchema.StoredProcedures); }
/// <summary> /// Gets all stored procedures (and functions) with their arguments /// </summary> /// <remarks> /// <para>We also get the source (if available)</para> /// <para>We don't get functions here.</para> /// <para>In Oracle stored procedures are often in packages. We read the non-packaged stored procedures, then add packaged stored procedures if they have arguments. If they don't have arguments, they are not found.</para> /// </remarks> public IList <DatabaseStoredProcedure> AllStoredProcedures() { try { DataTable functions = _sr.Functions(); DatabaseSchema.Functions.Clear(); DatabaseSchema.Functions.AddRange(SchemaProcedureConverter.Functions(functions)); } catch (DbException ex) { Debug.WriteLine("Cannot read functions - database security may prevent access to DDL\n" + ex.Message); throw; //or suppress if not applicable } DataTable dt = _sr.StoredProcedures(); SchemaProcedureConverter.StoredProcedures(DatabaseSchema, dt); DatabaseSchema.Packages.Clear(); DatabaseSchema.Packages.AddRange(SchemaProcedureConverter.Packages(_sr.Packages())); //do all the arguments as one call and sort them out. //NB: This is often slow on Oracle DataTable args = _sr.StoredProcedureArguments(null); if (args.Rows.Count == 0) { //MySql v6 won't do all stored procedures. So we have to do them individually. foreach (var sproc in DatabaseSchema.StoredProcedures) { args = _sr.StoredProcedureArguments(sproc.Name); SchemaProcedureConverter.UpdateArguments(DatabaseSchema, args); } foreach (var function in DatabaseSchema.Functions) { args = _sr.StoredProcedureArguments(function.Name); SchemaProcedureConverter.UpdateArguments(DatabaseSchema, args); } } //arguments could be for functions too SchemaProcedureConverter.UpdateArguments(DatabaseSchema, args); foreach (var function in DatabaseSchema.Functions) { //return types are assigned as arguments (in most platforms). Move them to return type. function.CheckArgumentsForReturnType(); } //procedure, function and view source sql DataTable srcs = _sr.ProcedureSource(null); SchemaSourceConverter.AddSources(DatabaseSchema, srcs); UpdateReferences(); return(DatabaseSchema.StoredProcedures); }
private void AllSequences() { RaiseReadingProgress(SchemaObjectType.Sequences); var sequences = _schemaReader.Sequences(); ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.Sequences); DatabaseSchema.Sequences.Clear(); DatabaseSchema.Sequences.AddRange(SchemaProcedureConverter.Sequences(sequences)); }
public override IList <DatabasePackage> Packages(string name) { var packages = SchemaProcedureConverter.Packages(_schemaReader.Packages()); if (Parameters.SqlType == SqlType.Oracle && !string.IsNullOrEmpty(Owner)) { packages.RemoveAll(x => x.SchemaOwner != Owner); } return(packages); }
public override IList <DatabaseStoredProcedure> StoredProcedures(string name) { var dt = _schemaReader.StoredProcedures(); SchemaProcedureConverter.StoredProcedures(Parameters.DatabaseSchema, dt); if (Parameters.SqlType == SqlType.Oracle && !string.IsNullOrEmpty(Owner)) { Parameters.DatabaseSchema.StoredProcedures.RemoveAll(x => x.SchemaOwner != Owner); } return(Parameters.DatabaseSchema.StoredProcedures); }
public override IList <DatabaseArgument> ProcedureArguments(string name) { var dt = _schemaReader.StoredProcedureArguments(name); var converter = new SchemaProcedureConverter { PackageFilter = Parameters.Exclusions.PackageFilter, StoredProcedureFilter = Parameters.Exclusions.StoredProcedureFilter }; return(converter.Arguments(dt)); }
/// <summary> /// Gets all of the schema in one call. /// </summary> /// <returns></returns> public DatabaseSchema ReadAll() { _fixUp = false; DataTypes(); AllUsers(); AllTables(); AllViews(); AllStoredProcedures(); //oracle extra DatabaseSchema.Sequences.Clear(); var sequences = _sr.Sequences(); DatabaseSchema.Sequences.AddRange(SchemaProcedureConverter.Sequences(sequences)); _fixUp = true; UpdateReferences(); return(_db); }
public override IList <DatabaseSequence> Sequences(string name) { var sequences = _schemaReader.Sequences(); return(SchemaProcedureConverter.Sequences(sequences)); }
public override IList <DatabaseFunction> Functions(string name) { var dt = _schemaReader.Functions(); return(SchemaProcedureConverter.Functions(dt)); }
public void Load(CancellationToken ct) { if (ct.IsCancellationRequested) return; try { ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.ReadingSchema, SchemaObjectType.Functions); var functions = _schemaReader.Functions(); ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.Functions); _databaseSchema.Functions.Clear(); _databaseSchema.Functions.AddRange(SchemaProcedureConverter.Functions(functions)); } catch (DbException ex) { Debug.WriteLine("Cannot read functions - database security may prevent access to DDL\n" + ex.Message); throw; //or suppress if not applicable } if (ct.IsCancellationRequested) return; ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.ReadingSchema, SchemaObjectType.StoredProcedure); var dt = _schemaReader.StoredProcedures(); if (ct.IsCancellationRequested) return; ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.StoredProcedure); SchemaProcedureConverter.StoredProcedures(_databaseSchema, dt); var procFilter = _exclusions.StoredProcedureFilter; if (procFilter != null) { _databaseSchema.StoredProcedures.RemoveAll(p => procFilter.Exclude(p.Name)); } _databaseSchema.Packages.Clear(); _databaseSchema.Packages.AddRange(SchemaProcedureConverter.Packages(_schemaReader.Packages())); var packFilter = _exclusions.PackageFilter; if (packFilter != null) { _databaseSchema.Packages.RemoveAll(p => packFilter.Exclude(p.Name)); } if (ct.IsCancellationRequested) return; //do all the arguments as one call and sort them out. //NB: This is often slow on Oracle ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.ReadingSchema, SchemaObjectType.ProcedureArguments); var args = _schemaReader.StoredProcedureArguments(null); ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.ProcedureArguments); var converter = new SchemaProcedureConverter(); converter.PackageFilter = _exclusions.PackageFilter; converter.StoredProcedureFilter = _exclusions.StoredProcedureFilter; if (args.Rows.Count == 0) { //MySql v6 won't do all stored procedures. So we have to do them individually. foreach (var sproc in _databaseSchema.StoredProcedures) { if (ct.IsCancellationRequested) return; args = _schemaReader.StoredProcedureArguments(sproc.Name); converter.UpdateArguments(_databaseSchema, args); } foreach (var function in _databaseSchema.Functions) { if (ct.IsCancellationRequested) return; args = _schemaReader.StoredProcedureArguments(function.Name); converter.UpdateArguments(_databaseSchema, args); } } if (ct.IsCancellationRequested) return; //arguments could be for functions too converter.UpdateArguments(_databaseSchema, args); foreach (var function in _databaseSchema.Functions) { //return types are assigned as arguments (in most platforms). Move them to return type. function.CheckArgumentsForReturnType(); } if (ct.IsCancellationRequested) return; //procedure, function and view source sql ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.ReadingSchema, SchemaObjectType.ProcedureSource); var srcs = _schemaReader.ProcedureSource(null); if (ct.IsCancellationRequested) return; ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.ProcedureSource); SchemaSourceConverter.AddSources(_databaseSchema, srcs); }
public void UpdateArgumentsOracleTest() { //arrange var converter = new SchemaProcedureConverter(); var schema = new DatabaseSchema(null, null); #region deserialize datatable //from Oracle HR. Captured from var datatable.WriteXml(StringWriter) const string data = @"<DocumentElement> <ProcedureParameters> <Schema>HR</Schema> <Procedure>GET_CURRENT_TIME</Procedure> <Name>P_DATE</Name> <Position>1</Position> <Sequence>1</Sequence> <DataType>DATE</DataType> <Direction>IN/OUT</Direction> </ProcedureParameters> <ProcedureParameters> <Schema>HR</Schema> <Procedure>ADD_JOB_HISTORY</Procedure> <Name>P_EMP_ID</Name> <Position>1</Position> <Sequence>1</Sequence> <DataType>NUMBER</DataType> <Direction>IN</Direction> <Length>22</Length> <Precision>6</Precision> </ProcedureParameters> <ProcedureParameters> <Schema>HR</Schema> <Procedure>ADD_JOB_HISTORY</Procedure> <Name>P_START_DATE</Name> <Position>2</Position> <Sequence>2</Sequence> <DataType>DATE</DataType> <Direction>IN</Direction> </ProcedureParameters> <ProcedureParameters> <Schema>HR</Schema> <Procedure>ADD_JOB_HISTORY</Procedure> <Name>P_END_DATE</Name> <Position>3</Position> <Sequence>3</Sequence> <DataType>DATE</DataType> <Direction>IN</Direction> </ProcedureParameters> <ProcedureParameters> <Schema>HR</Schema> <Procedure>ADD_JOB_HISTORY</Procedure> <Name>P_JOB_ID</Name> <Position>4</Position> <Sequence>4</Sequence> <DataType>VARCHAR2</DataType> <Direction>IN</Direction> <Length>10</Length> <Charset>CHAR_CS</Charset> </ProcedureParameters> <ProcedureParameters> <Schema>HR</Schema> <Procedure>ADD_JOB_HISTORY</Procedure> <Name>P_DEPARTMENT_ID</Name> <Position>5</Position> <Sequence>5</Sequence> <DataType>NUMBER</DataType> <Direction>IN</Direction> <Length>22</Length> <Precision>4</Precision> </ProcedureParameters> </DocumentElement> "; var sr = new StringReader(data); var ds = new DataSet(); ds.ReadXml(sr); var dataTable = ds.Tables[0]; #endregion //act converter.UpdateArguments(schema, dataTable); //assert var sprocs = schema.StoredProcedures; Assert.AreEqual(2, sprocs.Count); var addHistory = sprocs.Find(x => x.Name == "ADD_JOB_HISTORY"); Assert.AreEqual(5, addHistory.Arguments.Count); var empId = addHistory.Arguments.Find(x => x.Name == "P_EMP_ID"); Assert.AreEqual(1, empId.Ordinal); Assert.AreEqual("NUMBER", empId.DatabaseDataType); Assert.AreEqual(true, empId.In); Assert.AreEqual(false, empId.Out); var currentTime = sprocs.Find(x => x.Name == "GET_CURRENT_TIME"); var date = currentTime.Arguments.Find(x => x.Name == "P_DATE"); Assert.AreEqual(1, date.Ordinal); Assert.AreEqual("DATE", date.DatabaseDataType); //inout type! Assert.AreEqual(true, date.In); Assert.AreEqual(true, date.Out); }
public void UpdateArgumentsSqlServerTest() { //arrange var converter = new SchemaProcedureConverter(); var schema = new DatabaseSchema(null, null); #region deserialize datatable //from SqlServer Northwind. Captured from var datatable.WriteXml(StringWriter) const string data = @"<DocumentElement> <ProcedureParameters> <SPECIFIC_CATALOG>Northwind</SPECIFIC_CATALOG> <SPECIFIC_SCHEMA>dbo</SPECIFIC_SCHEMA> <SPECIFIC_NAME>GetWeekDay</SPECIFIC_NAME> <ORDINAL_POSITION>0</ORDINAL_POSITION> <PARAMETER_MODE>OUT</PARAMETER_MODE> <IS_RESULT>YES</IS_RESULT> <AS_LOCATOR>NO</AS_LOCATOR> <PARAMETER_NAME /> <DATA_TYPE>int</DATA_TYPE> <NUMERIC_PRECISION>10</NUMERIC_PRECISION> <NUMERIC_PRECISION_RADIX>10</NUMERIC_PRECISION_RADIX> <NUMERIC_SCALE>0</NUMERIC_SCALE> </ProcedureParameters> <ProcedureParameters> <SPECIFIC_CATALOG>Northwind</SPECIFIC_CATALOG> <SPECIFIC_SCHEMA>dbo</SPECIFIC_SCHEMA> <SPECIFIC_NAME>GetWeekDay</SPECIFIC_NAME> <ORDINAL_POSITION>1</ORDINAL_POSITION> <PARAMETER_MODE>IN</PARAMETER_MODE> <IS_RESULT>NO</IS_RESULT> <AS_LOCATOR>NO</AS_LOCATOR> <PARAMETER_NAME>@Date</PARAMETER_NAME> <DATA_TYPE>datetime</DATA_TYPE> <DATETIME_PRECISION>3</DATETIME_PRECISION> </ProcedureParameters> <ProcedureParameters> <SPECIFIC_CATALOG>Northwind</SPECIFIC_CATALOG> <SPECIFIC_SCHEMA>dbo</SPECIFIC_SCHEMA> <SPECIFIC_NAME>GetCurrentDate</SPECIFIC_NAME> <ORDINAL_POSITION>1</ORDINAL_POSITION> <PARAMETER_MODE>INOUT</PARAMETER_MODE> <IS_RESULT>NO</IS_RESULT> <AS_LOCATOR>NO</AS_LOCATOR> <PARAMETER_NAME>@p1</PARAMETER_NAME> <DATA_TYPE>datetime2</DATA_TYPE> <DATETIME_PRECISION>7</DATETIME_PRECISION> </ProcedureParameters> <ProcedureParameters> <SPECIFIC_CATALOG>Northwind</SPECIFIC_CATALOG> <SPECIFIC_SCHEMA>dbo</SPECIFIC_SCHEMA> <SPECIFIC_NAME>Sales by Year</SPECIFIC_NAME> <ORDINAL_POSITION>1</ORDINAL_POSITION> <PARAMETER_MODE>IN</PARAMETER_MODE> <IS_RESULT>NO</IS_RESULT> <AS_LOCATOR>NO</AS_LOCATOR> <PARAMETER_NAME>@Beginning_Date</PARAMETER_NAME> <DATA_TYPE>datetime</DATA_TYPE> <DATETIME_PRECISION>3</DATETIME_PRECISION> </ProcedureParameters> <ProcedureParameters> <SPECIFIC_CATALOG>Northwind</SPECIFIC_CATALOG> <SPECIFIC_SCHEMA>dbo</SPECIFIC_SCHEMA> <SPECIFIC_NAME>Sales by Year</SPECIFIC_NAME> <ORDINAL_POSITION>2</ORDINAL_POSITION> <PARAMETER_MODE>IN</PARAMETER_MODE> <IS_RESULT>NO</IS_RESULT> <AS_LOCATOR>NO</AS_LOCATOR> <PARAMETER_NAME>@Ending_Date</PARAMETER_NAME> <DATA_TYPE>datetime</DATA_TYPE> <DATETIME_PRECISION>3</DATETIME_PRECISION> </ProcedureParameters> </DocumentElement>"; var sr = new StringReader(data); var ds = new DataSet(); ds.ReadXml(sr); var dataTable = ds.Tables[0]; #endregion //act converter.UpdateArguments(schema, dataTable); //assert var sprocs = schema.StoredProcedures; Assert.AreEqual(2, sprocs.Count); //function GetWeekDay is ignored Assert.AreEqual(1, schema.Functions.Count); //here's where GetWeekDay is var salesByYear = sprocs.Find(x => x.Name == "Sales by Year"); Assert.AreEqual(2, salesByYear.Arguments.Count); var date = salesByYear.Arguments.Find(x => x.Name == "@Beginning_Date"); Assert.AreEqual(1, date.Ordinal); Assert.AreEqual("datetime", date.DatabaseDataType); Assert.AreEqual(true, date.In); Assert.AreEqual(false, date.Out); var currentTime = sprocs.Find(x => x.Name == "GetCurrentDate"); Assert.AreEqual(1, currentTime.Arguments.Count); var p1 = currentTime.Arguments.Find(x => x.Name == "@p1"); Assert.AreEqual(1, p1.Ordinal); Assert.AreEqual("datetime2", p1.DatabaseDataType); //in-out type! Assert.AreEqual(true, p1.In); Assert.AreEqual(true, p1.Out); var fun = schema.Functions[0]; Assert.AreEqual("int", fun.ReturnType); Assert.AreEqual(1, fun.Arguments.Count); //even though we had 2 in the data table var input = fun.Arguments[0]; Assert.AreEqual("@Date", input.Name); Assert.AreEqual("datetime", input.DatabaseDataType); Assert.AreEqual(true, input.In); }
public void Load(CancellationToken ct) { if (ct.IsCancellationRequested) { return; } try { ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.ReadingSchema, SchemaObjectType.Functions); var functions = _schemaReader.Functions(); ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.Functions); _databaseSchema.Functions.Clear(); _databaseSchema.Functions.AddRange(SchemaProcedureConverter.Functions(functions)); } catch (DbException ex) { Debug.WriteLine("Cannot read functions - database security may prevent access to DDL\n" + ex.Message); throw; //or suppress if not applicable } if (ct.IsCancellationRequested) { return; } ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.ReadingSchema, SchemaObjectType.StoredProcedure); var dt = _schemaReader.StoredProcedures(); if (ct.IsCancellationRequested) { return; } ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.StoredProcedure); SchemaProcedureConverter.StoredProcedures(_databaseSchema, dt); var procFilter = _exclusions.StoredProcedureFilter; if (procFilter != null) { _databaseSchema.StoredProcedures.RemoveAll(p => procFilter.Exclude(p.Name)); } _databaseSchema.Packages.Clear(); _databaseSchema.Packages.AddRange(SchemaProcedureConverter.Packages(_schemaReader.Packages())); var packFilter = _exclusions.PackageFilter; if (packFilter != null) { _databaseSchema.Packages.RemoveAll(p => packFilter.Exclude(p.Name)); } if (ct.IsCancellationRequested) { return; } //do all the arguments as one call and sort them out. //NB: This is often slow on Oracle ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.ReadingSchema, SchemaObjectType.ProcedureArguments); var args = _schemaReader.StoredProcedureArguments(null); ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.ProcedureArguments); var converter = new SchemaProcedureConverter(); converter.PackageFilter = _exclusions.PackageFilter; converter.StoredProcedureFilter = _exclusions.StoredProcedureFilter; if (args.Rows.Count == 0) { //MySql v6 won't do all stored procedures. So we have to do them individually. foreach (var sproc in _databaseSchema.StoredProcedures) { if (ct.IsCancellationRequested) { return; } args = _schemaReader.StoredProcedureArguments(sproc.Name); converter.UpdateArguments(_databaseSchema, args); } foreach (var function in _databaseSchema.Functions) { if (ct.IsCancellationRequested) { return; } args = _schemaReader.StoredProcedureArguments(function.Name); converter.UpdateArguments(_databaseSchema, args); } } if (ct.IsCancellationRequested) { return; } //arguments could be for functions too converter.UpdateArguments(_databaseSchema, args); foreach (var function in _databaseSchema.Functions) { //return types are assigned as arguments (in most platforms). Move them to return type. function.CheckArgumentsForReturnType(); } if (ct.IsCancellationRequested) { return; } //procedure, function and view source sql ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.ReadingSchema, SchemaObjectType.ProcedureSource); var srcs = _schemaReader.ProcedureSource(null); if (ct.IsCancellationRequested) { return; } ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.ProcedureSource); SchemaSourceConverter.AddSources(_databaseSchema, srcs); }