ifcSQL is on github

[bsbock]

ifcSQL is able to manage multiple schemas and multiple models or building component libraries in a single database.

Now it is open source on github: See IfcSharp/IfcSQL (github.com)

These are the capabilities of ifcSQL:

[Moult]

Amazing work!

Would it be appropriate to rename this to IfcMSSQL? SQL unfortunately is not compatible between vendors and this IfcSQL only applies to the proprietary MSSQL vendor? It will not work for MySQL, PostgreSQL for example from my understanding.

It would be good if buildingSMART had an initiative such that a RBDMS solution worked across both proprietary and non-proprietary vendors.

[bsbock]

Thank you for your feedback, @Moult !

I think, the following aspects make (in this case) the difference between the different database vendors:

1. • The use of schemata (as namespace)
2. • The use of user defined types
3. • Case sentitive names of tables and views (maybe)
4. • UTF-8 for multilingual text (maybe)

All of these aspects were chosen for mastering complexity. That’s the philosophy behind. Gathering all possible methods for making complexity simple.

Sure, you can rename

• ifcInstance.Entity to
• dbo.ifcInstance_Entity or
• ifcInstance_Entity or
• IFCINSTANCE_ENTITY

But it’s a step back from the best possible.

The same with user defined types:
A type like ifcInstance.Id allows you to change the base-type (for example) from int to bigint for all occurrences in the database schema in a single step.

Maybe a matrix of capabilities from the different database vendors will help, to get an overview.

[Moult]

Agreed, it would be good to define a matrix of capabilities, and decide how to gracefully switch between SQL vendors, to make IfcSQL truly agnostic and not tied to a single proprietary vendor.

[Mok]

Hi Bernhard! I have try to use ifcSQL with middle size FacetedBrep object. Project inserting and ifc file generation work extremly slow. Have You ideas how this processes can bi speed up.

[bsbock]

Here you find some explanations how the stored procedures [app].[ToIfcStepFromProjectId] or [cp].[ToIfcStep] works:

Store, modify and retrieve ifc-data with ifcSQL

These are the secrets of ToIfcStepFromProjectId: Select the project from the Id-argument of ToIfcStepFromProjectId, so the view of the current project retrieves only the entities of this project Fill the step-header with the informations of the current-project-view [cp].[Project] and print it 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 curr…

Creating a Stepfile with the above mentioned stored procedures use a lot of nestet queries. So the time-costs increase exponential with the model size.
Maybe you can speed it up with temporary tables and other methods. If you set some timestamp-outputs, you will find out where the bottle necks are. The point is, that all this methods ends in a procedural way.
The fast procedural way is used in IFC#. See https://github.com/IfcSharp/IfcSharpApps/tree/master/IfcSql/from_ifcSQL

To the importing speed: The IFC#-Api use a bulk-Insert method, wich is much faster than INSERT-statements.
See CurrentModel.ToSql(…) in https://github.com/IfcSharp/IfcSharpApps/tree/master/IfcSharp/hello_project

If this is not fast enough, you can try to work with temporay tables without restrictions and transfer it on server-side to the ifcSQL-tables in a second step.
Hope this helps.

[PaulBurrows]

I have had a look at the IfcSQL code today and I am impressed! The techniques used to generalise Ifc are very nice IMO. I have some experience with DAGs (directed acyclic graphs) in databases and my first thought was that this must be very slow when importing/exporting large amounts of data. I have solved this in the past by maintaining a transitive closure on the trees to make the ancestor relationship explicit. I assume they would be project trees in this case? Closures do take up space and can slow down imports/writes but they can also speed up queries and exports by orders of magnitude. If I’m interpreting correctly, I was surprised not to see GUIDs used as a Domain Key in the database but perhaps there is a practical reason for this? I also encountered a few glitches because my SQLServer has a default binary collation and thus, complained when casing of variable names were inconsistent in the build scripts. With a binary collation on master, “TableName” is not equal to “Tablename” but that is just a quirk of my setup and easily rectified. Overall though, this is a fantastic and useful piece of work and I’m finding it very helpful to my understanding.

[gschleusner]

For reference this is the approach that Speckle is using on Postgress to store similar model data.