This is part of a series of articles aimed towards providing the basics of creating queries for SOLIDWORKS PDM vault databases. Administrators will be able to collect data for their own purposes that are not attainable from SOLIDWORKS PDM out-of-the-box search features.
This article will introduce a process of planning for creating basic SQL queries. There are many ways to plan and techniques vary with experience and training. However, this is a method that we have found to be more consumable and can be quickly put into practice.
This article is not intended to teach you how to query databases. Learning SQL is beyond the scope of this document. This document is intended for the PDM Administrator who would like to use his or her current SQL knowledge to query PDM vault databases. Moreover, advanced vault database queries would require advanced planning and experience and are also beyond the scope of this article.
If you would like to learn SQL, we recommend visiting w3schools for their free online training. GoEngineer is not affiliated with w3schools, we just think their website is awesome.
To have an idea of what tables to use the relation of, you need to first understand how the tables relate to one another. Once you have an idea of what relations you need, you will be able to create your query and dismiss unwanted relations to get the correct data. Luckily, Microsoft SQL Server Management Studio (SSMS) has a tool to create diagrams of the relating data between tables. Once you see a “path” of the relating data can use such path to build your query.
By the way, we have created a document that provides the steps of creating a database diagram and can be found here.
To see how the vault database tables relate to one another we will run through an example. For this to work, try to follow the image below as you read this scenario.
Suppose that based on a known file name you want to query for the corresponding path to that file. Through the creation of a diagram you find that the “File Name” column is part of the Documents table, “Path” is a column that is part of the Projects (folders) table, but you need DocumentsInProjects table to connect the other two tables. This is because the DocumentsinProjects contains both the DocumentID (which the Documents table has) and the ProjectID (which the Projects table has).
You then notice that LockProject in the Documents table has a direct connection to the Projects table. However, this relationship is not needed since LockProject is used for other purposes in PDM that is not what you are looking for. There is only one path from this point and you, therefore, plan your query as follows: (Green Arrows)
File Name (Documents table) > DocumentID (Documents table) > DocumentID (DocumentsInProjects table) > ProjectID (DocumentsInProjects table) > ProjectID (Projects Table) > Path (Projects Table)
Note: In the image above that the boxes are SQL tables, and every value listed per row are columns in the table.
Omitting how the query was created, for now, the plan in this example will produce the following query:
SELECT Projects . Path
FROM Documents INNER JOIN
DocumentsInProjects ON Documents.DocumentID = DocumentsInProjects.DocumentID INNER
Projects ON DocumentsInProjects.ProjectID = Projects.ProjectID
WHERE (Documents.Filename LIKE N'MySOLIDWORKSPart.SLDPRT’)
Notice the similarities between the query above and the planned path – food for thought.
In the next article, we will build the query above using a tool that will help you create most of your queries for SOLIDWORKS PDM vault databases.
About Francisco Guzman
Francisco Guzman is the PDM Technical Support Lead at GoEngineer, and is pursuing his degree in mechanical engineering at the University of Utah. In addition to providing guidance and support to SOLIDWORKS and SOLIDWORKS PDM customers, Francisco also provides support for DriveWorks design automation. He won the world-wide DriveWorks reseller CPD contest as the best DriveWorks AE for 2015. For fun, he designs, 3D-Prints, builds and races custom first-person-view (FPV) racing drone frames.
Get the latest articles delivered daily to your inbox, unsubscribe at any time.