Skip to main content
Enterprise Agile Planning icon with arrows

This post is from the Collabnet VersionOne blog and has not been updated since the original publish date.

Last Updated Dec 06, 2016 — Enterprise Agile Planning expert

VersionOne Integration Mapping SQL to the Rest/1.v1 API: Part 1

Enterprise Agile Planning

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.


SQLV1 Rest


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.


SQLV1 Rest
<> or !=!=

Sample Mappings

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.

More from the Blog

View more Government Cloud
Apr 12, 2022 Government Cloud receives FedRAMP Authorization through sponsorship from the United States Department of Veterans Affairs

Enterprise Agile Planning
Flagship Agility solutions can effectively scale agile deve ...
Read More
Nov 22, 2021

What are the qualities of highly effective agile teams?

Enterprise Agile Planning
A team is the core unit of productivity in an agile organization. Wher ...
Read More
Nov 15, 2021

How an open-first attitude revolutionized government tech development

Enterprise Agile Planning
Public perception of government is often that it is slow-moving, reluc ...
Read More
cross functional
Nov 08, 2021

6 best practices for building resilient cross-functional teams

Enterprise Agile Planning
Agile frameworks prize the quality of resilience within every facet of ...
Read More
Contact Us