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

SQL V1 Rest
Table Relation
Schema Metadata
Field/Column Attribute

 

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

SQL V1 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.

728x90-orange-demo

VersionOne is a registered trademark of VersionOne Inc.

Join the Discussion

    There are currently no comments.

    + 61 = 71