buildingSMART Forums

Store, modify and retrieve ifc-data with ifcSQL

This is a picture of a prototype of ifcSQL:


ifc_SQL_tables_Bock_part2

(A) The table in namespace (schema) “ifcDocumentation” is currently a placeholder for aditional html-Documentation of Entities, Types, etc.

(B) The tables in namespace “ifcInstance” contains ifc-data from many projects or libraries with rule-control by DML-trigger. The table EntityVariableName is used for storing variables of programming-code like IfcScript from @jonm.

(C ) The tables in namespace “ifcProject” handle project-informations like the assignments of project-wise ifc-id’s to global ifc-ids

(D) The tables in namespace “ifcProperty” contains the property-defintions

(E) The tables in namespace “ifcQuantityTakeOff” contains the quantity-takeoff-definitions

(F) The tables in namespace “ifcSchema” contains the information of the EXPRESS-schema

(G) The table in namespace “ifcSchemaDerived” contains the allowed attributes for entities calulated from the inheritance and SELECT-Informations

(H) The table in namespace “ifcSchemaTool” contains every change of this database-definition (filled by a DDL-trigger)

(I) The tables in namespace “ifcUnit” handles Unit-Information with a unit-assignment to the attribute-types

(J) The tables in namespace “ifcUser” store user-informations and their current project

Example:
With this prototype let’s use the following smallest possible example - hello_emptiness.ifc:

ISO-10303-21;
HEADER;
FILE_DESCRIPTION (('hello emptiness minimal example'), '2;1');
FILE_NAME ('hello_emptiness.ifc', '2019-06-23T22:38:27', ('Bock'), (''), 'C#2Ifc2html2ifcSQL', '', '');
FILE_SCHEMA (('IFC4'));
ENDSEC;
DATA;
#1=IFCPROJECT('1XMjTL$x9AvxoNhhNGFKxn',$,'hello emptiness project',$,$,$,$,$,$);
ENDSEC;
END-ISO-10303-21;

Insert:
Let’s say, we have an empty project with ProjectId=3 and a next free GlobalEntityInstanceId #1437.

So we can store the entity “ifcProject” in two steps:

  1. Insert Entity of TypeId=938 (ifcProject) to the global entity-table:

INSERT INTO [ifcInstance].[Entity] ([GlobalEntityInstanceId], [EntityTypeId]) VALUES (1437, 938)

  1. assgins the local ifc-Id #1 to the global ifc-Id #1437 above for ProjectId=3:

INSERT INTO [cp].[EntityInstanceIdAssignment] ([ProjectId], [ProjectEntityInstanceId], [GlobalEntityInstanceId]) VALUES (3, 1, 1437)

Now we assign the two attributes, wich both have the basetype string. So we insert them in one step:

  1. assign the value “1XMjTL$x9AvxoNhhNGFKxn” of the attribute “GlobalId” (attribute-TypeId 170) at the ordinal position 1 to the entitity, that is stored with the global ifc-Id #1437

  2. assign the value “hello emptiness project” of the attribute “Name” (attribute-TypeId 181) at the ordinal position 3 to the entitity, that is stored with the global ifc-Id #1437

All other attributes shall not used for this example.

INSERT INTO [ifcInstance].[EntityAttributeOfString] ([GlobalEntityInstanceId], [OrdinalPosition], [TypeId], [Value]) VALUES
(1437, 1, 170, '1XMjTL$x9AvxoNhhNGFKxn'),
(1437, 3, 181, 'hello emptiness project')

Retrieve:
Now let’s check the result by calling the stored procedure “ToIfcStepFromProjectId” for ProjectId=3

exec [app].[ToIfcStepFromProjectId] 3

The printed output is:

ISO-10303-21;
HEADER;
FILE_DESCRIPTION (('hello emptiness minimal example'), '2;1');
FILE_NAME ('hello_emptiness.ifc', '2019-06-30T06:13:42', ('Bernhard Simon Bock'), (''), 'ifcSQL', '', '');
FILE_SCHEMA (('IFC4'));
ENDSEC;
DATA;
#1=IFCPROJECT('1XMjTL$x9AvxoNhhNGFKxn',$,'hello emptiness project',$,$,$,$,$,$)
ENDSEC;
END-ISO-10303-21;

From the DOS-prompt or from a batch-file you can direct the output to a file:

sqlcmd -S%SqlServer% -difcSQL -Q"app.ToIfcStepFromProjectId 3" > hello_emptiness.ifc

Modify:

We want to modify the name of the project

  1. Find out the global ifc-id of the entity-type “ifcProject” for our current project (view-namespace “cp”)

    SELECT [GlobalEntityInstanceId] FROM [ifcSQL].[cp].[Entity] E INNER JOIN [ifcSQL].[ifcSchema].[Type] T on (E.EntityTypeId=T.TypeId)`
    WHERE T.[TypeName]=‘Project’ – without ifc-prefix

The result is 1437

  1. Check, if the attribute “Name” is used (we need this information to decide, if we have to insert or update this attribute)

SELECT * FROM [ifcSQL].[cp].[EntityAttributeOfString] WHERE GlobalEntityInstanceId=1437 and OrdinalPosition=3

– Yes, we have an one row result

  1. Update the attribute “Name” project-entity in this project

UPDATE [ifcSQL].[cp].[EntityAttributeOfString] SET VALUE='my first ifcSQL project' WHERE GlobalEntityInstanceId=1437 and OrdinalPosition=3

  1. Display the step-output for the current project

exec [ifcSQL].[cp].[ToIfcStep]

Output:

#1=IFCPROJECT(‘1XMjTL$x9AvxoNhhNGFKxn’,,'my first ifcSQL project',,,,,,$)

Rule-checking:
For testing the type-rule-checking let’s try to insert a label-attribute for IfcProject on OrdinalPosition=2

INSERT INTO [ifcInstance].[EntityAttributeOfString] ([GlobalEntityInstanceId], [OrdinalPosition], [TypeId], [Value]) VALUES 
(1437, 2, 181, 'my error test-label')

This error occurs:

Msg 50000, Level 16, State 10, Procedure ErrorEntityAtrributeType, Line 16
TypeId=181 on OrdinalPosition=2 not allowed for EntityType ifcProject

This works, because a DML-Trigger watch the insert.

Summary:
This post showed a way how to store in one single database

  • ifc-data of multiple projects
  • the ifc-schema and documentation
  • property-definitions and quantity-takoffs

I hope i have given some inspiration.

5 Likes

Thanks for sharing, quite interesting. So, what’s this ToIfcStepFromProjectId then? Can you shed some light on the complexity of this procedure? It seems like you have opted basically for a key-value store, which I think is the sensible approach due to the combinatorial complexity of union types (SELECT) and subtypes. How do you handle aggregates like lists? How portable is your approach with regard to other SQL dialects? How is the scalability of your system for larger instance files?

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

Thanks for further elaborating on this. It might be interesting to compare eg. with the open source TNO Bimserver approach. It’s similar in a way, but a true key-value store with most of the typing in user code. The in process BerkelyDB has advantages and disadvantages in terms of latency and scalability. Or compare with ISO 10303 26, the binary HDF5 serialization. It’s also an approach that serializes to tabular structures, but HDF5 offers a wider set of primitives such as arrays and variable length lists. I did personally did extensive prototypes on the HDF5 serialization.

Please mind that we mostly have focused to develop IFC schema based on “Relational or Object-Relational” approach

And I think @bsbock helped a lot in this way

It’s NOT about the database “FROM” .ifc file which the majority solved issues here and today the majority of solutions in the market convert the .ifc file to SQL

RDF, OWL, BerkleyDB and anything these days the majority use are good, but not great

I think the majority didn’t think about “Smoothness” and “Performance”