• 1 Introduction
  • 1.1 Why Databases
  • 1.2 Background
• 2 Requirements
• 3 Installation
  • 3.1 Server-Installation
  • 3.2 Database-Creation
  • 3.3 Schema-Installation
  • 3.4 Loading the IFC Schema Information
• 4 ifcSQL Documentation
  • 4.1 ifcSQL Schema Namespaces
  • 4.2 ifcSQL Data Types
  • 4.3 ifcSQL Tables
  • 4.4 ifcSQL Views
  • 4.5 ifcSQL Functions
  • 4.6 ifcSQL Procedures
• 5 Working with ifcSQL
  • 5.1 ifcSQL User- and Project-Management
  • 5.2 Exploring the IFC-schema
    • 5.2.1 Stored IFC-Releases
    • 5.2.2 Compare IFC-Releases
    • 5.2.3 Entity Inheritance
  • 5.3 Working with Projects
    • 5.3.1 Project Types and Project-Hierarchy
    • 5.3.2 IFC-Release-Assignment
    • 5.3.3 Project Management Tool
  • 5.4 Retrieving IFC-Data
  • 5.5 Storing IFC-Data
• 6 Use Cases
  • 6.1 Managing IFC-Schema-Information
  • 6.2 Single Source Container
  • 6.3 Building Parts Library
• 7 Software Eco System

Strategy no. 1:
Store every IFC-File in a binary large object field.
(-) no advantage over files
(-) not searchable
(-) no rule checking

Strategy no. 2:
Store every IFC-STEP-line in a text-field.
(-) not searchable
(-) no rule checking

Strategy no. 3:
Store every non abstract Entity-classes (over 600) in its own table. Same with enumerations.
(+) high recognition value in tablenames
(-) no rule checking because you can reference only to one other table, not to all inherited tables
(-) too much tables (confusing)

Strategy no. 4:
Store every inheritance-step in its own table (over 750).
(+) recognition value
(+) rule checking on base-classes possible
(-) don’t work with SELECT-Types
(-) too much tables (more confusing than strategy no. 3)

Strategy no. 5:
Divide between entities and their attributes. One table for entities and one table for attributes.
(+) only 2 tables (totaly not confusing)
(o) rule checking with triggers
(-) attribute values not typesave

Strategy no. 6:
Divide between Entities and their attributes. One table for Entities and for each basetype a table for attributes.
(+) just a few tables (not confusing)
(o) rule checking with triggers
(+) typesave attribute values

Strategy no. 6 is the strategy of ifcSQL.

Create a new database with the name "ifcSQL". After creation edit the properties. Under "options" you must change the collation to "LATIN1_GENERAL_100_CS_AS_SC_UTF8" for UTF-8-Support. If you don't do this, the IFC-PropertySets will not be multilingual.

if you want to change the name of the underlaying database-files, you can use the folowing script. You need to do this, if you have installed this database before and the files allready exist. Then you have to change the filenames.

CREATE DATABASE [ifcSQL]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'ifcSQL', FILENAME = N'C:\Program Files\Microsoft SQL Server 2019 Express\MSSQL15.SQLEXPRESS\MSSQL\DATA\ifcSQL4.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'ifcSQL_log', FILENAME = N'C:\Program Files\Microsoft SQL Server 2019 Express\MSSQL15.SQLEXPRESS\MSSQL\DATA\ifcSQL4_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
COLLATE LATIN1_GENERAL_100_CS_AS_SC_UTF8
 WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO

• ifcAPI for assigment of the IFC-schema to computer languages.
• ifcDocumentation for assigment of the IFC-schema to natural languages.
• ifcInstance for container-tables of the IFC-model-data
• ifcProject for handling project-informations
• ifcProperty for property-defintions (like bSDD)
• ifcQuantityTakeOff for quantity-takeoff-definitions
• ifcSchema for all (most) informations of the different EXPRESS-schemata
• ifcSchemaDerived contains the allowed attributes for entities calulated from the inheritance and SELECT-Informations
• ifcSchemaTool contains every change of this database-definition (filled by a DDL-trigger)
• ifcSpecification contains the different IFC-specifications like 2x3 or 4.3
• ifcSQL contains informations about ifcSQL itself
• ifcUnit handles Unit-Information with a unit-assignment to the attribute-types
• ifcUser store user-informations and their current project

• cp (current project) is a naming convention for current project
• cs (current schema) is a naming convention for current IFC-schema like 2x3 or 4.3

• _____ TEST_____ is a naming convention for test-procedures
• app is a naming convention for application-procedures like NewProject
• ExpressImport is used for EXPRESS-import tools
• ifcSchemaEvaluation for schema evaluation

• Bool for the YesNo type
• IO for the types FileName, PathName and URL
• Text for the types Description, Email, Expression, Html, Key, Login, Name, ToString
• Vector3d for the component types X, Y, Z
• Vector2d for the component types X, Y
• Point3d for the component types X, Y, Z
• Direction3d for the component types X, Y, Z
• ifcOrder for the position type
• ifcEnum for the Id of enums
• ifcLayer for the Id of layers
• ifcType for the types ifcBINARY, ifcBOOLEAN, ifcINTEGER, ifcREAL, ifcSTRING, and IfdGuid

• ifcAPI.ComputerLanguage contains the from IfcSharp supported computer-languages for accessing ifcSQL, currently only c#
• ifcAPI.TypeComputerLanguageAssignment contains the assignment of the schema-types to the type of a computer-language

• ifcDocumentation.NaturalLanguage contains the natural languages of the property-decriptions
• ifcDocumentation.Type is currently not used

• ifcInstance.Entity contains all Entity instances with their GlobalId
• ifcInstance.EntityAttributeListElementOfBinary contains attributes of an entity
• ifcInstance.EntityAttributeListElementOfEntityRef contains attributes of an entity
• ifcInstance.EntityAttributeListElementOfFloat contains attributes of an entity
• ifcInstance.EntityAttributeListElementOfInteger contains attributes of an entity
• ifcInstance.EntityAttributeListElementOfList contains attributes of an entity
• ifcInstance.EntityAttributeListElementOfListElementOfEntityRef contains attributes of an entity
• ifcInstance.EntityAttributeListElementOfListElementOfFloat contains attributes of an entity
• ifcInstance.EntityAttributeListElementOfListElementOfInteger contains attributes of an entity
• ifcInstance.EntityAttributeListElementOfString contains attributes of an entity
• ifcInstance.EntityAttributeOfBinary contains attributes of an entity
• ifcInstance.EntityAttributeOfBoolean contains attributes of an entity
• ifcInstance.EntityAttributeOfEntityRef contains attributes of an entity
• ifcInstance.EntityAttributeOfEnum contains attributes of an entity
• ifcInstance.EntityAttributeOfFloat contains attributes of an entity
• ifcInstance.EntityAttributeOfInteger contains attributes of an entity
• ifcInstance.EntityAttributeOfList contains attributes of an entity
• ifcInstance.EntityAttributeOfString contains attributes of an entity
• ifcInstance.EntityAttributeOfVector contains attributes of an entity
• ifcInstance.EntityVariableName contains the variable-name of an entity using the API (optional for code-generation)

• ifcProject.EntityInstanceIdAssignment contains the EntityInstanceId's that are assigned to a project
• ifcProject.LastGlobalEntityInstanceId contains LastGlobalEntityInstanceId used by projects
• ifcProject.ProjectGroupType contains the project-group-types
• ifcProject.ProjectGroup contains project-groups
• ifcProject.Project contains the project-informations

• ifcProperty.SetDef contains the definition of the propertyset
• ifcProperty.Def contains the property definition
• ifcProperty.DefAlias contains contains the alias of the definition in an other language
• ifcProperty.SetDefAlias contains contains the alias of the definition-set in an other language
• ifcProperty.SetDefApplicable contains the applicable entities
• ifcProperty.TypePropertyReferenceValue contains reference values
• ifcProperty.TypePropertySingleValue contains single values

• ifcQuantityTakeOff.Def contains the definition
• ifcQuantityTakeOff.DefAlias contains the alias of the definition in an other language
• ifcQuantityTakeOff.SetDef contains the sets of QuantityTakeOff
• ifcQuantityTakeOff.SetDefApplicableClass contains applicable classes
• ifcQuantityTakeOff.Type contains the QuantityTakeOff-type

• ifcSchema.TypeGroup contains the type-groups like ENTITY, SELECT or ENUM
• ifcSchema.LayerGroup contains the layer-groups like core-layers
• ifcSchema.Layer contains the layer of the schema
• ifcSchema.Type contains all types. This is the main schema-table
• ifcSchema.EntityAttribute contains the attributes assigned to an entity
• ifcSchema.EntityInverseAssignment contains the EntityInverseAssignment
• ifcSchema.EnumItem contains all enum items
• ifcSchema.SelectItem contains all possible SELECT-instances

• ifcSchemaDerived.EntityAttributeInstance contains all possible attributes incl. SELECT-instances

• ifcSchemaTool.ChangeLog contains ChangeLog-script
• ifcSchemaTool.ChangeLogType contains the ChangeLogType

• ifcSpecification.SpecificationGroup contains SpecificationGroup, e.g. MVD
• ifcSpecification.Specification contains the specifications, that are stored in ifcSQL
• ifcSpecification.TypeSpecificationAssignment contains assignment of types to specification

• ifcSQL.Licence contains the licence-text
• ifcSQL.Release contains the releases of ifcSQL
• ifcSQL.Issues contains Issues of of ifcSQL
• ifcSQL.BaseTypeGroup contains the BaseTypeGroup of ifcSQL
• ifcSQL.EntityAttributeTable contains EntityAttributeTable-names of ifcSQL

• ifcUnit.Unit contains the Units of the IFC-schema
• ifcUnit.SIUnitNameUnitOfMeasureEnumAssignment contains SIUnitNameUnitOfMeasureEnumAssignment
• ifcUnit.SIUnitNameEnumDimensionsExponentAssignment contains SIUnitNameEnumDimensionsExponentAssignment
• ifcUnit.SIPrefixEnumExponentAssigment contains SIPrefixEnumExponentAssigment

• ifcUser.User contains the user-data
• ifcUser.Login contains the login-strings of users
• ifcUser.UserProjectAssignment contains all ProjectId's assigned to a user

• cp.EntityAttribute select all entries of the current project
• cp.EntityAttribute_WithProjectEntityRef select all entries of the current project
• cp.EntityAttributeFormatted select all entries of the current project
• cp.EntityAttributeListElement select all entries of the current project
• cp.EntityAttributeListElementOfBinary select all entries of the current project
• cp.EntityAttributeListElementOfEntityRef select all entries of the current project
• cp.EntityAttributeListElementOfFloat select all entries of the current project
• cp.EntityAttributeListElementOfInteger select all entries of the current project
• cp.EntityAttributeListElementOfList select all entries of the current project
• cp.EntityAttributeListElementOfListElement select all entries of the current project
• cp.EntityAttributeListElementOfListElementOfEntityRef select all entries of the current project
• cp.EntityAttributeListElementOfListElementOfFloat select all entries of the current project
• cp.EntityAttributeListElementOfListElementOfInteger select all entries of the current project
• cp.EntityAttributeListElementOfString select all entries of the current project
• cp.EntityAttributeOfBinary select all entries of the current project
• cp.EntityAttributeOfBoolean select all entries of the current project
• cp.EntityAttributeOfEntityRef select all entries of the current project
• cp.EntityAttributeOfEntityRef_WithProjectEntityRef select all entries of the current project
• cp.EntityAttributeOfEnum select all entries of the current project
• cp.EntityAttributeOfFloat select all entries of the current project
• cp.EntityAttributeOfInteger select all entries of the current project
• cp.EntityAttributeOfList select all entries of the current project
• cp.EntityAttributeOfString select all entries of the current project
• cp.EntityAttributeOfVector select all entries of the current project
• cp.EntityInstanceIdAssignment select all entries of the current project
• cp.EntityVariableName select all entries of the current project
• cp.Login select all entries of the current project
• cp.Project select all entries of the current project
• cp.User select all entries of the current project
• cp.UserProjectAssignment select all entries of the current project

• cs.EntityAttribute select all entries from the schema of the current specification
• cs.EntityInverseAssignment select all entries from the schema of the current specification
• cs.EntityInverseAssignment_not_OfEntityTypeId_bound select all entries from the schema of the current specification
• cs.EnumItem select all entries from the schema of the current specification
• cs.SelectItem select all entries from the schema of the current specification
• cs.SelectItem_not_SelectTypeId_bound select all entries from the schema of the current specification
• cs.Type select all entries from the schema of the current specification

• ifcSchemaTool.ChangeLogScript_for_order_by_Date_and_use_ifcSQL_go_before select all entries of the current project
• ifcSchemaTool.CheckBaseTypesOfTYPE select all entries of the current project
• ifcSchemaTool.CheckBaseTypeSpecificationAssigment select all entries of the current project
• ifcSchemaTool.CheckEntityAttributeTypeSpecificationAssigment select all entries of the current project
• ifcSchemaTool.CheckEntityInverseAssignmentOfEntityTypeSpecificationAssigment select all entries of the current project
• ifcSchemaTool.CheckParentTypeSpecificationAssigment select all entries of the current project
• ifcSchemaTool.CheckSelectItemSpecificationAssigment select all entries of the current project
• ifcSchemaTool.SelectTypes select all entries of the current project
• ifcSchemaTool.SelectTypesAndSelectedTypeGroups select all entries of the current project
• ifcSchemaTool.SelectTypesAndSelectedTypeGroupsWithDifferentSelectedTypes select all entries of the current project
• ifcSchemaTool.SelectTypesAndSelectedTypeGroupsWithSingleSelectedType select all entries of the current project
• ifcSchemaTool.SelectTypesWithDerivedAttributes select all entries of the current project

• ifcSpecification.Current select the current specification
• ifcSpecification.TypeSpecificationMatrix select a matrix of all types of all specifications
• ifcSpecification.TypeSpecificationMatrix_IFC4X1_to_IFC4X3_RC2 select a matrix of all types of the named specifications
• ifcSpecification.UnUsedTypes select all types, that are not used in any specification

• ifcSQL.CurrentRelease select the current release of ifcSQL

• int cp.ProjectId() returns the current ProjectId that is assigned to the UserProjectOrdinalPosition=0 of the current UserId
• int cp.UserId() returns the UserId of the current Login
• int cs.SpecificationId() returns the current SpecificationId of the current project

• TABLE ifcInstance.EntityAttributeFormatted(@GlobalEntityInstanceId as bigint,@OrdinalPosition as int,@TypeId as int,@ValueStr as nvarchar) returns a formatted attribute for ToIfcStepLine
• TABLE ifcInstance.EntityAttributeListElementFormatted(@GlobalEntityInstanceId as bigint,@OrdinalPosition as int,@ValueStr as nvarchar) returns a formatted attributelist for ToIfcStepLine
• varchar ifcInstance.EntityAttributeListElementFormattedString() returns a formatted attributelist for ToIfcStepLine
• TABLE ifcInstance.EntityAttributeListElementOfListElementFormatted(@GlobalEntityInstanceId as bigint,@OrdinalPosition as int,@ValueStr as nvarchar) returns a formatted attributelist for ToIfcStepLine
• varchar ifcInstance.ToIfcStepLine() returns a STEP-formatted IFC-line (only for small tests)

• bigint ifcProject.LastGlobalId() returns the last GlobalId of overall Entities

• varchar ifcSchema.AttributeNames(@EntityTypeId as [ifcSchema].[Id]) returns all attribute names of an entity-type by EntityTypeId
• varchar ifcSchema.Attributes(@EntityTypeId as [ifcSchema].[Id]) returns all name-value-pairs of the attributes of an entity-type by EntityTypeId
• varchar ifcSchema.AttributeTypes(@EntityTypeId as [ifcSchema].[Id]) returns all typename-value-pairs of the attributes of an entity-type by EntityTypeId
• varchar ifcSchema.DerivedAttributeNames(@EntityTypeId as [ifcSchema].[Id]) returns all attribute names of an entity-type by EntityTypeId
• varchar ifcSchema.DerivedAttributes(@EntityTypeId as [ifcSchema].[Id]) returns all name-value-pairs of the attributes of an entity-type by EntityTypeId
• varchar ifcSchema.DerivedAttributeTypes(@EntityTypeId as [ifcSchema].[Id]) returns all typename-value-pairs of the attributes of an entity-type by EntityTypeId
• varchar ifcSchema.NonDerivedAttributeNames(@EntityTypeId as [ifcSchema].[Id]) returns returns all attribute names of an entity-type by EntityTypeId
• varchar ifcSchema.NonDerivedAttributes(@EntityTypeId as [ifcSchema].[Id]) returns all name-value-pairs of the attributes of an entity-type by EntityTypeId
• varchar ifcSchema.NonDerivedAttributeTypes(@EntityTypeId as [ifcSchema].[Id]) returns all typename-value-pairs of the attributes of an entity-type by EntityTypeId
• TABLE ifcSchema.Type_Ancestor(@TypeId as int,@TypeName as nvarchar,@ParentTypeId as int,@NestLevel as int) returns all parents an other ancestors
• TABLE ifcSchema.Type_AncestorByName(@TypeId as int,@TypeName as nvarchar,@ParentTypeId as int,@NestLevel as int) returns all names of parents an other ancestors
• TABLE ifcSchema.Type_AncestorOrSelf(@TypeId as int,@TypeName as nvarchar,@ParentTypeId as int,@NestLevel as int) returns all parents an other ancestors and itself
• TABLE ifcSchema.Type_AncestorOrSelfByName(@TypeId as int,@TypeName as nvarchar,@ParentTypeId as int,@NestLevel as int) returns all names of parents an other ancestors and itself
• TABLE ifcSchema.Type_Descendant(@TypeId as int,@TypeName as nvarchar,@ParentTypeId as int,@Abstract as bit,@NestLevel as int) returns all childs and other descendants
• TABLE ifcSchema.Type_DescendantByName(@TypeId as int,@TypeName as nvarchar,@ParentTypeId as int,@Abstract as bit,@NestLevel as int) returns all names of childs and other descendants
• TABLE ifcSchema.Type_DescendantOrSelf(@TypeId as int,@TypeName as nvarchar,@ParentTypeId as int,@Abstract as bit,@NestLevel as int) returns all childs and other descendants and itself
• TABLE ifcSchema.Type_DescendantOrSelfByName(@TypeId as int,@TypeName as nvarchar,@ParentTypeId as int,@Abstract as bit,@NestLevel as int) returns all names of childs and other descendants and itself
• bit ifcSchema.Type_IsSubclassOf(SubClassTypeId AS INT,@ParentTypeId AS INT) returns true if subclass
• bit ifcSchema.Type_IsSubclassOfByName(@SubClassTypeName AS [ifcSchema].[IndexableName],@ParentTypeName AS [ifcSchema].[IndexableName]) returns true if subclass
• TABLE ifcSchema.Type_Root(@TypeId as int,@TypeName as nvarchar,@ParentTypeId as int,@NestLevel as int) returns the typoe root
• TABLE ifcSchema.Type_RootEntity(@TypeId as int,@TypeName as nvarchar,@ParentTypeId as int,@NestLevel as int) returns the entity root
• TABLE ifcSchema.Type_Tree(@TypeId as int,@TypeName as nvarchar,@ParentTypeId as int,@Abstract as bit,@NestLevel as int,@sort as varchar,@CountedInsertString as varchar) returns the type tree
• TABLE ifcSchema.Type_TreeByName(@TypeId as int,@TypeName as nvarchar,@Attributes as varchar,@AttributeTypes as varchar,@AttributeNames as varchar,@ParentTypeId as int,@Abstract as bit) returns the type tree by name
• int ifcSchema.TypeId(@TypeName AS [ifcSchema].[IndexableName]) returns the TypeId by name

• bit ifcSchemaTool.ValidEntityAtrributeType(@GlobalEntityInstanceId [ifcInstance].[Id],@OrdinalPosition [ifcOrder].[Position],@TypeId [ifcSchema].[Id]) returns true if the EntityAtrributeType is valid

• float ifcSQL.CurrentReleaseId() returns the current ReleaseId
• nvarchar ifcSQL.CurrentReleaseKey() returns returns the current ReleaseKey

• _____ TEST_____.exec_ToIfcStepFromProjectName() execute ToIfcStepFromProjectName
• _____ TEST_____.InheritanceOfEntity() select-example for inhertitance of an entity
• _____ TEST_____.InheritanceOfRelationship() select-example for inhertitance of a relationship
• _____ TEST_____.Store_modify_and_retrieve_IFC_data() example for creating a model with SQL

• cp.ToIfcStep() execute XXX

• ExpressImport.BaseTypeId_Update(@MinExclTypeId as int,@MaxInclTypeId as int) is used during importing a new EXPRESS-schema
• ExpressImport.EntityAttributeTableId_Update(@MinExclTypeId as int,@MaxInclTypeId as int) is used during importing a new EXPRESS-schema
• ExpressImport.SelectItem_Insert_Nested(@BaseNestLevel as int,@Current_SpecificationId as int) is used during importing a new EXPRESS-schema
• ExpressImport.SelectItem_Insert_NestedSelectItem(@BaseNestLevel as int,@MinExclTypeId as int,@MaxInclTypeId as int) is used during importing a new EXPRESS-schema
• ExpressImport.SelectItem_Update_Abstract(@Current_SpecificationId as int) is used during importing a new EXPRESS-schema

• ifcInstance.InsertAttribute(@GlobalEntityInstanceId as bigint,@EntityTypeId as int,@OrdinalPosition as int,@AttributeTypeId as int,@Dim1Position as int,@Dim2Position as int,@AttributeValue as nvarchar) is a tool for inserting attributes (still under construction)
• ifcInstance.InsertEntity(@GlobalEntityInstanceId as bigint,@ProjectId as int,@ProjectEntityInstanceId as bigint,@EntityTypeId as int) is a tool for inserting entities (still under construction)

• ifcInstanceTool.CreateAllDmlTriggers(@TableSchema as nvarchar) creates all DML-triggers

• ifcProject.NewLastGlobalId(@ProjectId as int,@IdCount as bigint) creates a new LastGlobalId an retrivies it

• ifcSchemaTool.AfterDeleteProc(@TableSchema as nvarchar,@TableName as nvarchar) is used in a DML a trigger
• ifcSchemaTool.AfterInsertProc(@TableSchema as nvarchar,@TableName as nvarchar) is used in a DML a trigger
• ifcSchemaTool.AfterUpdateProc(@TableSchema as nvarchar,@TableName as nvarchar) is used in a DML a trigger
• ifcSchemaTool.ChangeLogScriptCreate(@MinDateTime as datetime) create a log-script
• ifcSchemaTool.ChangeLogScriptDelete(@BeforeDateTime as datetime) delete a log-script
• ifcSchemaTool.CreateAllDmlTriggers(@TableSchema as nvarchar) creates all dml-triggers of all tables
• ifcSchemaTool.CreateDmlTriggers(@TableSchema as nvarchar,@TableName as nvarchar) creates all dml-triggers of a single table
• ifcSchemaTool.DropAllSchemaDmlTriggers(@TableSchema as nvarchar) drops all schema-dml-triggers
• ifcSchemaTool.DropAndCreateAllSchemaDmlTriggers(@TableSchema as nvarchar) drops and create all schema-dml-triggers
• ifcSchemaTool.DropAndCreateAllSchemaDmlTriggers_Template() is a template for drop and create schema-dml-trigger
• ifcSchemaTool.DropAndCreateDmlTriggers(@TableSchema as nvarchar,@TableName as nvarchar) drops and create all dml-triggers
• ifcSchemaTool.DropDmlTriggers(@TableSchema as nvarchar,@TableName as nvarchar) drops a dml-triggers
• ifcSchemaTool.ErrorEntityAtrributeType(@GlobalEntityInstanceId as bigint,@OrdinalPosition as int,@TypeId as int) display an error-message
• ifcSchemaTool.PrintChangeLog(@USER as nvarchar) display the change-log
• ifcSchemaTool.ReFill_ifcSchemaDerived_EntityAttributeInstance() is used by a new EXPRESS-schema-import

• ifcSpecification.CheckAllDependencies() Checks all dependencies