Skip to main content
Enterprise Agile Planning Image

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.

Prerequisites

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.

 Terminology

SQLV1 Rest
TableRelation
SchemaMetadata
Field/ColumnAttribute

 

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.

Operators

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

Selecting

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

Sorting

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

Boolean

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')

Paging

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
May 31, 2021

Agile change management processes are key to delivering software faster

Enterprise Agile Planning
With its emphasis on delivery value faster, agile product management s ...
Read More
May 03, 2021

Bringing the agile planning approach to your whole business

Enterprise Agile Planning
The events of the last 12 months have demonstrated that the only sure ...
Read More
Apr 08, 2021

Making IT services more agile

Enterprise Agile Planning
The agile revolution completely transformed how we create digital prod ...
Read More
Feb 14, 2021

Reflecting on the 20th anniversary of the Agile Manifesto

Enterprise Agile Planning
Over the past 20 years, it’s been amazing to watch an idea from ...
Read More
Contact Us