Docsvision database structure. From developer to developer!

    Docsvision is not just a program, it is a platform that allows you to create your own solutions for electronic document management. An article by our colleague, Docsvision developer Dima Leikin, is intended just for developers of such solutions, to which we include our company partners and employees of IT departments of our customer companies.

    The material, divided into 5 logical parts, contains basic information about how the Docsvision system works. In addition, for developers who want to get a job with us, this knowledge will be an additional plus.


    In general, Docsvision is a client-server system, and the development of its solution comes down to the development of a set of cards (that is, a library).

    Card- The basic concept in the Docsvision system. From the client’s point of view, the card is the UI that he sees when he is working with a document or task. From the point of view of the client side programmer, a card is an object model that allows you to save information to a server. From the point of view of the server side programmer, a card is a set of tables and stored procedures for accessing them. From the point of view of the card developer, a card is, first of all, metadata. According to this metadata, tables and stored procedures of the card are generated, they contain attributes that are responsible for security, the way the card data is downloaded to the client, and many others.

    DirectoryIs another important concept. A directory is a card that exists in a single copy. For example, a directory of employees, a directory of saved searches, a directory of roles, etc.
    With the system itself comes several already written library of cards. These are the Platform, ManagedPlatform, Takeoffice, Workflow, Backoffice libraries.
    The first series of articles is devoted to the basis of the system - the Docsvision database. Of course, this is far from a complete description. The information is mainly devoted to the principles of the system, so many details had to be sacrificed for the sake of simplicity.

    Part 1. Section tables

    When developing your library of cards using the CardManager utility, xml with metadata descriptions for cards is created. Then, based on this metadata, tables and stored procedures are generated in the database, allowing you to work with these cards. Metadata is essentially a description of the type of card.

    Briefly about metadata

    In terms of metadata, a card is a collection of sections. The section can be imagined as a table in the database, so it will be clearer. A section is a collection of fields. A field can be imagined as a column of this table. A section can be a collection, wooden, or struct type. A section can have child sections, I will write about this in more detail below.

    Each card has a unique identifier - a card type identifier. Similarly, sections and fields have unique identifiers. Section identifiers are very important, because currently the names of section tables are generated as follows: [dbo]. [Dvtable_ {id}], where id is the section identifier.

    Beginners are often confused with types and instances. For example, in the table [dbo]. [Dvsys_instances], where information about card instances is stored, in the InstanceID field (card instance identifier) ​​they begin to search for the card type identifier for the document card. And they are very surprised when they do not find him there. (Here it should be noted that in the latest versions of Docsvision for directories, the card identifier is equal to the type identifier).

    Section in terms of storing information in the database

    From the point of view of storing information, a section is a database table. The section field in the database corresponds to the table column. In addition to columns for fields, in each "sectional" table there are so-called system columns (RowID, InstanceID, ParentRowID, ParentTreeRowID, etc.)

    InstanceID and RowID columns
    RowID is a unique identifier (Guid) of a section table row (primary key).
    InstanceID - identifier (Guid) of the instance of the card to which this line belongs. Corresponds to the identifier of the card from the table [dbo]. [Dvsys_instances].

    You can imagine a section table as follows:

    The same table stores information about section lines for all instances of cards of this type.

    Representation of sections of different types in the database
    A section can be collection, wooden and type struct. The name of the sections came, apparently, from the data structures for which these types of sections are intended to be stored.

    Collection sections The
    collection section is designed to store a collection of objects. From the point of view of the database, this means that among the rows of the sectional table there may be rows with the same InstanceID.

    Sections of type struct
    A section of type struct is intended to store a data structure. In fact, this is the same collection section, but only a collection can have a maximum of one object. From the point of view of the database, this means that in a section table there cannot be two rows with the same InstanceID. The columns of the section table correspond to the fields of the structure.

    Wooden sections
    A wooden section is intended for storing trees. Trees are stored as follows: there is a system column ParentTreeRowID, in which for each row the identifier of the parent row in the tree is written, or Guid.Empty if there is no parent row. In the wooden section you can store several trees for one copy of the card, there is no restriction.
    When deleting a line in the tree, all its child lines will be deleted, this is provided by the stored procedures for deleting lines.

    Child sections
    Wooden sections allow you to store a collection of child objects of the same type for an object. But what if we want to store a collection of children of a different type? For this, child sections are intended. The “classic” example of the parent and child sections is the sections of departments and employees in the employee directory. Each unit has a collection of employees. In the database, it looks like this: in the section table for employees in the system column ParentRowID (not to be confused with ParentTreeRowID) the identifier of the parent unit is indicated.


    For the sectional table of the child section, a foreign key is generated on the parent table (from ParentRowID of the child to the RowID of the parent table) with cascading deletion. That is, for example, when a unit is deleted, all its employees will be deleted.


    How can this knowledge be put into practice? Suppose we have a Docsvision database, and we want to see which employees are registered in the employee directory. First, we need to find out the identifier of the section of the employees directory employees. The easiest way, of course, is to look in CardManager or in xml, but if they are not at hand, it does not matter:

    We find the identifier of the employee directory:
    select * from [dbo].[dvsys_carddefs]
    where Alias like '%Staff%'

    We get a list of its sections:
    select * from [dbo].[dvsys_sectiondefs]
    where CardTypeID = '6710B92A-E148-4363-8A6F-1AA0EB18936C'

    We see in the SectionTypeID field for the section with the Employees alias the employee section identifier 'DBC8AE9D-C1D2-4D5E-978B-339D22B32482'. We make a request from the sectional table of employees:
    select * from [dbo].[dvtable_{DBC8AE9D-C1D2-4D5E-978B-339D22B32482}]

    The list of employees in front of us. Now, let's say we want to see what employees are in the unit named Test. Similarly, we find the identifier of the unit section and write:
    select Emp.* 
    from [dvtable_{DBC8AE9D-C1D2-4D5E-978B-339D22B32482}] Emp
    join [dvtable_{7473F07F-11ED-4762-9F1E-7FF10808DDD1}] Company ON Emp.ParentRowID = Company.RowID
    where Company.Name = 'Test'

    Here we used the fact that the employee section is a subsidiary of the division section. Now let's try to derive the Test unit and all its child (in the tree) units:
    ;WITH Companies (RowID, ParentTreeRowID, Name)
      SELECT Company.RowID, Company.ParentTreeRowID, Company.Name
      FROM [dvtable_{7473F07F-11ED-4762-9F1E-7FF10808DDD1}] Company
      WHERE Company.Name = 'Test'
      SELECT Company.RowID, Company.ParentTreeRowID, Company.Name
      FROM [dvtable_{7473F07F-11ED-4762-9F1E-7FF10808DDD1}] Company
      JOIN Companies ON Company.ParentTreeRowID = Companies.RowID
    SELECT RowID, ParentTreeRowID, Name
    FROM Companies 

    Here we used the fact that the unit section is a wooden section. 

    Part 2. A little more about metadata

    The Xml with the metadata of the cards is an xml file with the metadata of the VersionedFile card of the Platform library:


    Metadata is, in fact, a type definition for card instances. Very often, metadata and card instances in the database are called cards, which is somewhat confusing. When we talk about metadata, a card will always mean a card type, a card identifier will mean a card type identifier, etc.
    The metadata defines the name and identifier of the card, its attributes, and the section tree. For each section, the name and identifier of the section, its attributes and fields are set. For each field, the name and identifier of the field, as well as its type, are specified.
    Among the attributes of the card, it is worth noting, first of all, the attribute responsible for whether the card is a reference book. Among the attributes of a section, the most important attribute is the type of section (collectible, wooden, or struct type).
    Child sections are defined in the xml file as follows. The element has a child element, where elements for child sections are specified. The child section, in turn, may also have child sections. Thus, the tree of sections turns out.

    CardManager Utility

    Docsvision uses a utility for developers of CardManager to work with metadata. The utility allows you to automate the creation and editing of metadata cards.

    The screenshot shows the CardManager utility with the Backoffice library open.

    The screenshot shows the metadata of the Backoffice library staff reference. The AlternativeHierarchy wooden section has been opened for storing user groups. The child sections of the Group and GrpViewFields are visible in it, as well as the Name, Comments, AccountName and others.

    Part 3. References

    Fields can be of different types, including links to strings (refid) and links to instances of cards (refcardid).


    In the case of a link to a row, the identifier of the row to which this row refers refers to in the table cell. But in which table to search for a row by this identifier? This is determined by the metadata of the field that is intended to store the link (in the case in the figure, by the metadata of the MyReference field).
    When storing a link to a card, the InstanceID card instance identifier from the [dbo]. [Dvsys_instances] table is registered in the table cell. The metadata indicates which type of card the link is stored.


    Link Types Hard, Weak, Auto

    Link to the card can be of type Hard, Weak, Auto.
    Hard , or hard link, means the following: when the last hard link to the card is deleted, the card will be deleted.
    Weak , or a weak link, does not affect the removal of the card.
    Auto - an automatic link also does not affect card deletion, but when you delete a card, the link itself will be reset.

    Table [dbo]. [Dvsys_links]

    To store links, use the [dbo]. [Dvsys_links] table. This table brings together the link information that is scattered across different section tables. Disynchronization cannot occur here, since adding / deleting records to the table provides the same stored procedures that work with sectional fields.

    The table has the following useful columns:
    • RowID - the line that refers
    • FieldID - identifier of the type of field intended for storing the value of the link
    • SourceCardID - identifier of the instance of the card that refers
    • DestinationTypeID - identifier of the instance of the card to which we refer
    • Type - type of link

    What are links for?

    Links make the card a reusable component. For example, if you want the card you create to contain a collection of files that support versioning, then just make a field with a link to the FileList card of the Backoffice system library.

    Part 4. System tables and libraries

    Basic system tables

    [dbo]. [dvsys_globalinfo] - the table contains information about the version of the Docsvision database. This table has a useful Version field containing the current version of the database.
    Sometimes it is required to update versions for all libraries so that they correspond to the database version, but you don’t want to immerse new versions of libraries in the database. To do this, there is the following way: look at the version in [dbo]. [Dvsys_globalinfo] and then call the stored procedure:
    exec [dbo].[dvsys_help_set_version] @Version = номер версии

    Metadata Tables:
    • [dbo]. [dvsys_libraries] - information about installed card libraries
    • [dbo]. [dvsys_carddefs] - information about the installed cards
    • [dbo]. [dvsys_sectiondefs] - section information
    • [dbo]. [dvsys_fielddefs] - field information

    Other important tables
    • [dbo]. [dvsys_instances] table of card instances
    • [dbo]. [dvsys_links] link table
    • [dbo]. [dvsys_locks] lock table
    • [dbo]. [dvsys_security] security
    • [dbo]. [dvsys_crypto] crypto objects
    • [dbo]. [dvsys_log], [dbo]. [dvsys_log_application], [dbo]. [dvsys_log_security] log tables

    For storing files, 2 tables are important:
    • [dbo] .dvsys_files
    • [dbo] .dvsys_binaries

    The dvsys_files table stores information about the file, and the dvsys_binaries contains its contents.
    It is also important to mention that there is a fileId type for card fields. This type of field will store the file identifier from the dvsys_files table.

    Platform Library

    Folder Directory
    If you have ever opened Docsvision Navigator, the first thing you saw is a folder tree and a grid with a view of the cards contained in the folder. In fact, the folder does not contain the cards themselves, but labels on them.

    Folder Directory is the FoldersCard directory of the Platform library, which stores information about folders, as well as labels on cards located in folders. Folders are stored in the wooden Folders section.

    Shortcuts are stored in the child section of the Shortcuts section. This section has the HardCardID and CardID fields. Both that and another field is a link to a card. The only difference is that the HardCardID field is a hard link, and the CardID is an Auto link. Thus, labels on the same card can be located in different folders, and the card will be deleted when the last label with a hard link to it is deleted.

    Folders can be of the following types: regular folders, virtual folders, delegate folders, and system folders. Virtual folders differ in that a search query is used to return information about the labels in them. Delegate folders are essentially pointers to other folders. System folders are usually some special folders, for example, a folder of search results or a folder “basket”. Many system folders are invisible to the user. You can view system folders like this:
    select Name 
    from [dbo].[dvtable_{FE27631D-EEEA-4E2E-A04C-D4351282FB55}] Folders
    where [Type] = 16 -- System

    Version file card
    The Platform library has a VersionedFile card. This card is convenient to use in cases where you need to store several versions of a single file. The card has a wooden section Versions, which allows you to store a tree of versions of the file. In the Versions section, there is a FileID field of type fileID where the link to the file is stored. The MainInfo section has a useful CurrentID field (of type refid) containing a link to the version that is considered current.

    card The numbering card is used to issue numbers to documents and other cards.

    Views Card The card is used to store information about user views - those views that the Docsvision Navigator grid shows.

    Saved search query
    card A card is used to store information about user search queries.

    Backoffice library

    Species guide
    It so happened that the creators of the Backoffice library considered the separation of cards into types insufficient, and decided to specify the type of card within the library by adding a view to it. Let's say there is a document, and there is an incoming document - this is a type of document. A view can have child views that inherit some of its features, while some have their own. And so the species tree turned out. All these species are stored in the species directory.

    Document cards, tasks, task groups, approvals
    These are the main working cards of the Docsvision system. They correspond to the main entities of the workflow. They provide a large number of different services and participate in various scenarios.

    State Reference
    The state directory stores the state machine of cards. Upon transition from state to state, operations are performed.

    Role Directory The Role
    Directory stores role security information. In fact, this is a three-dimensional matrix of the role - state - available operations. The UI of the directory allows you to see sections of this three-dimensional matrix.

    reference In the markup reference you can configure the appearance of the card, add controls to it, subscribe to their events, define dynamic fields and associate them with the controls.

    Script Reference The script
    reference allows you to write your own scripts for cards.

    Employee Directory
    The employee directory stores employees and units, as well as groups and roles.

    Counterparty Directory The counterparty
    directory contains organizations and employees - counterparties.

    Part 5. Search and submission subsystem

    Search and submissions are not much different from each other. The main idea: using the UI or using code to create a description of the search / presentation. According to this description, the server will generate a stored procedure that will return a selection with the results. The search description is stored in the search queries directory, and the views are stored in the stored views directory. The search / view must have an identifier in order to invoke an existing stored procedure when calling again, which affects performance.

    A view is what the user sees in the navigator grid. Firstly, there is a system view (digest), which is used by default. Ordinary and page-by-page views (so-called keyset views) also differ. A view can use a regular folder, or search results, or something else as its data source. Search and views support parameters that are set at the time of the call, either by the user through the UI, or programmatically. As parameters, search words can be set (I, Today, My alternates, and many others). Collectible parameters are allowed.

    The definition of search and views is essentially a small language with xml syntax, and this language supports many different features. The system for generating stored procedures on the server is a translator from Xml to Sql.

    The search subsystem supports 2 types of search - attributive and full-text . Full-text search allows you to search for cards or files containing a specific string. Attribute search allows you to impose conditions on the values ​​of the fields of the displayed cards. A condition on a field value can use operations equal, not equal, greater, less, and others, depending on the type of field. Conditions can be combined using AND or OR. Thus, a condition tree is obtained. When a stored procedure is generated, conditions in WHERE are generated by them.

    Representations use a similar approach, since they, too, in the general case, should not display all cards. Unlike search, views support so-called computed fields. The generation of a calculated field can be thought of as adding another expression to the list of SELECT statements of the resulting selection of a stored procedure. The expressions for the calculated field resemble expressions for the condition tree. In fact, this is the same tree, only instead of comparison operations, it most often uses arithmetic operations, as well as CASE WHEN expressions (resembling switch in C #).

    For simplicity, the sampling scheme in the representations can be represented as follows:
    SELECT обычные поля	Вычисляемые поля
    FROM основная таблица
    JOIN  список присоединенных таблиц
    WHERE условия

    In fact, of course, everything is more complicated. First, a view may contain not one such selection, but several, and these samples are combined using UNION ALL. This can be useful to display different types of cards in one view. Secondly, in page views, reading all the data for all pages from the database would lead to a significant decrease in performance, so the corresponding magic is used in the generator; one stored sample generates several stored procedures.

    Representations also provide advanced features, including the use of aggregations, concatenations, the disclosure of wooden sections. Sorting for views is supported. And the grouping is already done on the client.

    A more detailed model of the search and submissions can be considered in one of the future articles, and basic information is presented in this block.

    Also popular now: