I work with CAMWorks on an almost daily basis as part of my work as a manufacturing engineer at Metal Essence, Inc. One main feature of the software is the Technology Database, where a user can save machining strategies for later use. For example, if I figure out a good way to machine a tight tolerance hole or hold a good surface finish, I can save the parameters I used to program the CNC machine and bring them up later if I need to perform a similar process in the future. This improves workflow efficiency as I no longer need to reinvent the wheel every time I come across a particular set of geometries within a part.
One issue with the software is the inability to easily transfer certain strategies from one database to another. If I am collaborating with another machinist, there is no good way to give them a strategy I created or add one of their strategies to my database. Usually we end up having to use bizarre workarounds where we open the part that was programmed with the strategy in question and manipulate it in order to save it to a different database. This solution is irritating, wastes a lot of time, and is unintelligent. Certain strategies may have been programmed to use a specialized tool to perform the cuts in order to shape the part. Even with the workaround, we have no way of also adding the tooling to the database.
The ultimate solution to this issue would be an application that would allow the user to easily move data from one database to another. I saw this as an opportunity to practice programming and gain experience in application development for Windows. First, I had to open the database using a SQLite viewer to really understand where all of the data was kept and how it was all associated together. The technology database is an SQLite file, so I had to learn how SQL works in order to effectively manipulate it. Finally, I learned how to use Windows Forms to build a user interface that is simple and self-explanatory.
Analyzing the CAMWorks Technology Database
In order to effectively copy the strategy data, I first needed to know how the data was organized and how different areas of the database pointed to each other. I found a tool called DB Browser, which allows a user to easily interact with SQL databases with little effort. Link here: https://sqlitebrowser.org/.
In order to effectively copy the strategy data, I first needed to know how the data was organized and how different areas of the database pointed to each other. Using DB Browser, I was able to open the SQLite database and thoroughly examine each of the many tables and figure out how they were all inter-related. Basically, I “tracked” the various IDs to figure out how different tables used the IDs to pull data from other tables. This was a painstaking process as the database is complex as it is storing a large amount of data.
Using SQLite
Now that I was familiar with the data and figured out what I needed to manipulate, I needed to figure out how to get my application to actually perform the actions on the database. The app itself is written in C# and uses Windows Forms as the GUI. In the final form, I mostly use SELECT, DELETE, and INSERT SQLite commands to move the data around. Once I got used to how SQLite works, I found it to be really intuitive, requiring very little coding effort to achieve the results I needed.
The biggest coding challenge I had with SQLite was figuring out a way to parameterize table entry iteration. In order to effectively pull all of the data necessary for the app to function as conveniently as possible, the app needs to be able to correctly access about 100 tables, many of which have about 50 different columns of data that need to be recorded. Obviously, hard-coding the column names and indexes for all of these tables is not feasible, so I needed a way to get the computer to figure it out for itself. Eventually, I ended up using a PRAGMA statement to get the info for a specific table, which would get me the names of the columns, the indexes of these columns, and the total number of columns for any given table. My final solution was to have the computer calculate the column info for all relevant tables in the background as a parallel task while the user is making their selections.
In order to achieve this functionality, the app iterates over a set of data within a strategy node and builds a string of column names and values to insert. Once everything is collected into one string, the string gets passed to the SQLite command and is executed. This allows the app to assign the correct information to the corresponding columns on the fly without any hard-coding. Using these features means I can keep the app future-proof, because when CAMWorks adds more column entries to their database, these are captured on the fly by simply reading the database file. In addition, the app can write data from new databases to older ones, as it simply writes data until there are no more columns to write to. Finally, adding new features to the app is a breeze as a few function calls allows me to handle any table of data I want without extra coding work. In conclusion, parameterizing the manipulation of the SQLite tables is an incredible time saver and is the real power behind my application.
The GUI
I’ll be honest, most of my effort was directed towards the functionality of the app, not the GUI, so it is fairly plain. However, I made sure that it is easy-to-use and intuitive. I used basic Windows Forms elements, buttons, text boxes, list boxes, and list views. The user selects a source database to move data from. Then, they can pick what they want and add specific strategies, machines, or tool cribs (pieces of data) to the transfer list. Next, they have three options for a destination. They can either select another existing database to transfer the data to, or they can create a new database with the objects they have selected. The user has the choice of making a completely brand new database with only what has been selected or they can use the default database that comes with CAMWorks and add the customized selections. After the user makes this selection, they hit the Transfer button.
Once they attempt a transfer, the app performs a collision check to ensure that items with the same name do not already exist in the destination. If collisions are detected, a popup is provided to allow the user to fix these. They can rename the new objects to make them unique, they can decide to cancel the transfer for the conflicting items, or they can overwrite the old data with the new. After these have been handled, the data is copied over. Below is a gallery of some of the GUI features.
As of writing this, the app is being tested by CAMWorks developers and is receiving positive feedback. I am currently working on additional features and have started selling seats of the software. Please contact me if you are interested.