This post is from the Collabnet VersionOne blog and has not been updated since the original publish date.
VersionOne Integration Mapping SQL to the Rest/1.v1 API: Part 1
Some years ago, I witnessed an my ex's 70/year/old mother use her computer to get online, communicate with people, navigate through spam and viruses, debug issues, recognize the screen relationships, and just try and 'own' her computer. I noticed her deep focus and unrelenting will to abstract things out, push ideas to a mental stack and get to the next step of the problem. It was impressive because I recognized the methods of survival that she employed. She was a retired world/class studio vocalist, and the sister of a famous saxophone player. She quickly, improvised, transposed, harmonized, and absorbed unfamiliar music using her deep repertoire of audio tools. It seems to me that she applied her tenacious audio problem solving skills to her computing problems. VersionOne has many tech savvy customers that are not developers. They improvise, employ mental mapping and abstracting of ideas while trying to get a leg up on the API. Sometimes their model or perception of some features or processes might be a little bit off but that is fine, I love their creative efforts to solve problems. As humans we create abstractions of things in our world then we use them. One of most common ways to get a grip on learning new things quickly is to draw parallels against a known, similar concept. While the parallels between SQL and VersionOne Rest API are not perfect, it is enough to satisfy those who lean heavily towards SQL and don’t have the time to learn Http or Rest. As with any kind of mental re/mapping against computational processes or techniques, there is a risk of losing some of the unique parts of the vernacular or some of the beauty of original intent. I say let's just get started and focus on the set of mappings that work for us.
For the sake of this mapping exercise, imagine that for every asset in VersionOne that we can access via a query, we have a SQL table called that name. For example, if we have assets such as Story, Defect, and Test. Just imagine that we have a SQL database with tables named Story, Defect, and Test.
Standard Tools to Access Data
SQL: If you are using SQL Server, you will probably do basic queries using Management Studio or if Oracle then SQL Developer. VersionOne Rest: You can use any browser or http client.
|<> or !=||!=|
Query all Stories SQL: Select * from Story VersionOne Rest: http://Myv1/rest/1.v1/Data/Story Note: In VersionOne, this will only return a subset of the available the most important attributes (columns) and it won't return custom fields. The most important attributes are determined by development and there is usually a remaining subset
Query all Stories with specific columns returned SQL: Select Name, Description from Story VersionOne Rest: http://Myv1/rest/1.v1/Data/Story?sel=Name,Description Query all Stories and show name where the Owner's name is George Jetson SQL: Select Name from Story where Owner='George Jetson' VersionOne Rest: http://MyV1/rest/1.v1/Data/Story?sel=Name&where= Owner.Name= ‘George Jetson' Query all Stories and show custom fields SQL: Select MyCustomFieldName from Story VersionOne Rest: http://MyV1/rest/1.v1/Data/Story?sel=Custom_MyCustomFieldName
Query all Stories and Sort by Name Ascending SQL: Select *from Story order by Name VersionOne Rest: http://MyV1/rest/1.v1/Data/Story?sort=Name Query all Stories and Sort by Name Descending SQL: Select *from Story order by Name Descending VersionOne Rest: http://MyV1/rest/1.v1/Data/Story?sort=/Name
Query all Stories where Name = Cool Story OR Hot Story SQL: Select *from Story where Name='Cool Story' or Name='Hot Story' VersionOne Rest: http://MyV1/rest/1.v1/Data/Story?where=(Name='Cool">http://MyV1/rest/1.v1/Data/Story?where=(Name='Cool Story'|Name='Hot Story') Query all Stories where Name = Cool Story AND Value > 4 SQL: Select *from Story where Name='Cool Story' AND Value > 4 VersionOne Rest: http://MyV1/rest/1.v1/Data/Story?where=(Name='Cool">http://MyV1/rest/1.v1/Data/Story?where=(Name='Cool Story';Value>'4')
Query all Stories but show only 5 rows SQL: Select * from Story where ROWNUM <= 5 VersionOne Rest: http://MyV1/rest/1.v1/Data/Story?page=5,0 Well that's it! Thanks for being patient enough to read through these VersionOne integration mappings. In part 2, I will address more complex relationships so stay tuned. If you have any questions or suggestions, feel free to reach out to me. VersionOne is a registered trademark of VersionOne Inc.