This topic is to try and document the Projdata database that is inherent in all AutoPLANT projects and also PlantSpace P&ID projects. While the Projdata database has been around for some time (circa 1996 .. but ScottA can verify that … I think P&ID 12.5 was the first to use it), and the documentation for it is much better (Program Files\Bentley\Plant XM\Help\AutoPLANT er diagram 8.5.vsd & Program Files\Bentley\Plant XM\Help\AutoPLANT er diagram v8.5.pdf) and Carl Brock is bringing this up to date with XM enhancements. However, there are still a lot of questions around field names and what can be a tag register etc and what can be a source etc, so as part of distributing information, this WIKI will look to document the PROJDATA.MDB (and perhaps another WIKI for the SCHEMA.MDB), so by the time OpenPlant PID comes out we will have a structure for documenting the database structure that it will use.
We will not be defining the required length of fields etc as that is available and is of little relevance. We will be documenting, descriptions and how the tables are related.
DefinitionsSome definitions before we begin
[database] : this will refer to the database that a table or field lives in. In the case of this WIKI this will generally always be [projdata] as that is what we are describing. However, there may be times when we want to reference [schema] tables etc. A section will define the main databases used by the ProjectDB.
[table] : this is a set of records that all have the same attributes. This WIKI will identify all tables [comment]That is the aim at the outset[/comment] in the projdata database and how they are used.
[record] : a specific row in a table which should generally be unique in some way. A well structured database would not allow any duplicate entries. Within the Bentley projdata database there should be NO tables with duplicate records.
[field] : a column which identifies a specific data value in a record.
If we consider the projdata database to identify the field keytag in the tag_reg table we would write [projdata].[tag_reg].[keytag]
Generally it is common practice to work backwards so the term
[tag_reg].[keytag] could be classed as the same as above, IF the user knew that the domain was referring to databases and specifically [projdata] or there was only one [tag_reg].[keytag]. However, this is lazy in my opinion and with multiple paste options in MS Office, it is easy to be explicit for every reference and this will be used within this WIKI.
Tag : an item which has some importance to the project. Generally these are physical items, such as valves, equipment etc, but can be tracked items, such as process lines or software tags. Note that a pipe run, which is part of a process line is NOT a tagged item as it does not have a tag register.
Databases
There are 4 main databases using when working with the [projdata] database
PROJDATA : the main database where all project data is stored. With XM can be an Access .mdb (2003, 2002, 2003), SQL Server (2000 and 2005) or Oracle (8.1.6 & 8.1.7, 9i, 10g) [comment]The latest readme indicates that SQL Server 2003 is supported. Not sure which version that is[/comment] This WIKI will identify all tables and fields in the PROJDATA database. The structure of this table is based upon the database projdata.mdb in Program Files\Bentley\Plant XM\Base\Projdata. Therefore if you want ALL new projects to have a specific field or table, this database can be updated and all derived databases will use the new information, regardless of the target database engine.
SCHEMA : generally this holds all information about how to access the PROJDATA database. I say generally as the SCHEMA3D.mdb tables are stored in the PROJDATA database and as such the SCHEMA only tends to have the PIW reference information. Hopefully this WIKI will highlight those differences and assist you in using the data wisely.
PROJECT : not a real database, as there are no tables or records. This is only a storage location for reports and forms (yes forms) required for the project. All reports are registered in the SCHEMA database.
MODEL : a theoretical database as this can belong to a PDW model (DWG) or can be part of the PROJDATA database when using CENTRAL mode. This will be indicated in the WIKI, so I the database you are looking at does not have the table, then either there are no PDW models, or the project is a distributed project.
TablesTAG_REG :
TAG_REG is one of the most important tables in PROJDATA and stores most of the tags in the project. However, the tag register for any tag is actually stored in [schema].[tag_type].[tagreg_tab]. All tag registers are required to have certain fields for the tag object functionality to work. So while we can create our own tag registers, they must conform to certain rules.
When uniqueness is enabled for a tag type, ALL entries in the [projdata].[tag_reg].[tag_no] field are checked for uniqueness. Therefore if uniqueness is enabled for AT_HVALVE, all tags in the [projdata].[tag_reg] table are checked for duplication. A powerful concept.
While this table is linked to by many other tables, the relationships from this table are many and are best found by searching the [schema].[tag_type].[tagreg_tab] field for the value “TAG_REG”, then the related tables will be found in the [schema].[tag_type].[source_tab] field.
[projdata].[tag_reg] is not a library table and will be cleared of all records at project creation time.
|
Field [projdata].[tag_reg] |
Description |
|
[keytag] |
The unique identifier of a tag. This value will never change for a record and allows the tag to be tracked through the project. Enables the [projdata].[tag_reg].[tag_no] field to be changed many times without impacting any linked data. The value of [keytag] is derived from [projdata].[last_id].[key_id] where the [projdata].[last_id].[key_type] = “KEYTAG”. It is a common mistake to forget to update the [projdata].[last_id] table when importing records. |
|
[tag_type] |
Holds the tag type for the tag. Must be a value in [schema].[tag_type].[tag_type]. |
|
[tag_code] |
Stores the tag structure of the tag. Must be a value in [schema].[tag_type].[tag_codes] or [schema].[tag_code].[tag_codes]. |
|
[tag_no] |
The Tag number for the tag. Generally will be unique for most tag types. If uniqueness is enable for the tag, then every tag in the tag register will be checked for duplication. |
|
[tag_noa] |
An alternate tag number for tags. Allows for client tags to be stored. Will not use the tag code options and is free text entry. |
|
[prev_tag] |
If the [projdata].[tag_reg].[tag_no] field is modified, then the previous value of the [projdata].[tag_reg].[tag_no] field is stored here. |
|
[proj_stat] |
Generally a free text field which can hold the project status. However the P&ID application can preset this if the fpjcat variable is set. (setq fpjcat "NEW") Renaming a tag, sets the status to “REN” Deleting a tag when the [schema].[tag_type].[always_del] field is not set and the project.ini setting [GENERAL] Remove tags on document deletion=No Will change the value to “DEL” |
|
[create_tm] |
The time the tag was created in yyyymmddhhmmss format. This is a text field so determining dates etc from this is not so easy. |
|
[last_mod] |
The time the tag was modified in yyyymmddhhmmss format. Generally this is always maintained and is getting better. There are a few places where tags can be updated and this field is not updated. DataManager -] properties pane DataManager -] Import But these are being closed |
|
[lastrev_id] |
Not maintained in the tag_reg table and unsure of where this is used. |
| [taginsert] |
Indicates a T or F value if the tag is inserted on the P&ID. A duplicate value as this can also be tracked through the [projdata].[key_link] table. SELECT KEYTAG FROM KEY_LINK WHERE KEYTAG = ‘[projdata].[tag_reg].[keytag]’ |
| [Insertedin3d] |
Indicates a T or F value if the tag is inserted in a 3D model (Piping or Equipment). Another duplicate value as this can also be tracked through the [projdata].[relationshipinstance] table. SELECT ID1 FROM RELATIONSHIPINSTANCE WHERE ID1 = ‘[projdata].[tag_reg].[keytag]’ Note : the above will return multiple hits as it should be qualified with a [projdata].[relationshipinstance].[relationshiptype], but as the value of the relationship would change based upon the tag type, then the above is generic and checks if the tag is in any 3D model |
| [datein3D] |
The time the tag was inserted into a 3D model in yyyymmddhhmmss format. |
| [ds_id] |
The internal datasheet id of the template that the tag is inserted onto. This is not a link to the [projdata].[doc_reg].[doc_id], but a link to [projdata].[ds_type].[ds_id]. |