This is a picture of a prototype of ifcSQL:
(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:
- Insert Entity of TypeId=938 (ifcProject) to the global entity-table:
INSERT INTO [ifcInstance].[Entity] ([GlobalEntityInstanceId], [EntityTypeId]) VALUES (1437, 938)
- 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:
-
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
-
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
-
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
- 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
- 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
- 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.