Store, modify and retrieve ifc-data with ifcSQL

Developers Serializations
3

These are the secrets of ToIfcStepFromProjectId:

  1. Select the project from the Id-argument of ToIfcStepFromProjectId, so the view of the current project retrieves only the entities of this project
  2. Fill the step-header with the informations of the current-project-view [cp].[Project] and print it
  3. Iterate via CURSOR over all entities and call a function [ToIfcStepLine] für every entity
    3.1 Select the local ifc-id from the current entity for this project
    3.2 Select the name of the type of the current entity
    3.3.Select formatted all connected attributes for the current entity via UNION of all base-types
    3.4 Select all allowed attributes for the current entity from [ifcSchemaDerived].[EntityAttributeInstance] (G). If it match with 3.3, display the value and if not, display “$”

I have not considered the instance-type-name of a SELECT yet. But it is possible by ascing the table [ifcSchema].[EntityAttribute], if the current Attribute is a SELECT.

You see, to construct a single ifc-line have a lot of nested queries. That will not be fast with many entities. The stored procedure [app].[ToIfcStepFromProjectId] is just for demonstration an testing little examples.

The here used concept of Strategy no. 6 from the topic IFC for relational databases - ifcSQL is similar to key-value, but the values are assignet by a multiple key (e.g. entity-id and ordinal position).

List’s are stored in 2 tables for one-dimensional lists an 3 tables for two-dimesional lists.
Example: [ifcInstance].[EntityAttributeOfList] store [GlobalEntityInstanceId], [OrdinalPosition] and [TypeId] of the field-elements and [ifcInstance].[EntityAttributeListElementOfInteger] store [GlobalEntityInstanceId],[OrdinalPosition],[ListDim1Position] and [Value] for each list-lement.

For me the most important aspects of this SQL-descriptions are using schemata as namespaces for mastering complexity, the use of triggers for rule-checking (where referential integrity does not work) and user defined type-names which give the standard types a better readable and computer-interpretable sense like [Text].[ToString] or [Bool].[YesNo].
As far as I know the above mentioned aspects are possible with MsSqlServer, oracle and PostgreSQL.

To the speed by using large projects:
This will be tested now. I myself have experience with large water-networks and time-series in databases. I calculate with values between 1 and 10 seconds for select 1 million rows from server to client over a long distance.
More difficult are inserts. The classic insert-statements are very slow. You can speed it up by a factor of about 1000 by using bulk-funktions or other technologies.

1 Like