¶ FlexVertex SQL Guide
This document provides a comprehensive guide and reference for the SQL syntax used in our API code examples. It is structured to help users understand and utilize various SQL commands and their applications.
¶ Table of Contents
- Introduction
- When Not to Use SQL
- Ideal Use Cases for SQL
- About the examples
- Basic queries
- Sorting and grouping
- Filtering
- Creating data
- Modifying data
- Removing data
¶ Introduction
Many next-generation information management platforms force developers to learn cryptic languages to work with their data. Not only is this time-consuming, there’s also a real risk that important techniques might get missed, and thus lead to a less than optimal experience.
We felt it would be better to let people use familiar techniques to find and manipulate their data. That’s why we chose to provide a robust SQL implementation that not only lets you search for data, but also interact with it.
Rather than just being a bolt-on afterthought, SQL is integral to everything in FlexVertex. For example, SQL seamlessly works with our groundbreaking Voyager multiverse navigation language. However, even though we offer a powerful SQL option, you’re always free to use our APIs instead, or in combination with it.
FlexVertex SQL provides full create, read, update, and delete capabilities on both standard and embedded objects, all supervised by our fine-grained authorization system. You can choose from our rich function library to streamline interactions with your data, as well as manage result sets. Named parameters, Boolean conditions, list filtering, and arrays are just a few examples of the productivity tools that you can take advantage of when writing your SQL.
¶ When Not to Use SQL
While SQL in FlexVertex is powerful, it has its limitations and is not suitable for certain tasks:
- Data Definition (DDL) tasks: Defining new classes should be handled by Voyager or other FlexVertex APIs.
- Data Manipulation (DML) tasks: Establishing connections between objects should also be conducted outside of SQL.
- Complex relational operations: Including identifying intricate relationships through joins, are typically managed using Voyager, which is better suited for these tasks due to its specialized capabilities.
¶ Ideal Use Cases for SQL
SQL is particularly useful for:
- Interactive queries and modifications: It provides a user-friendly way to interact with data.
- Navigating the FlexVertex Data Multiverse: It serves as a starting point for data exploration, enabling users to traverse and discover relationships and dependencies across data sets.
¶ About the examples
To help you visualize what's possible with FlexVertex and its SQL implementation, imagine you’re building a new app to identify prospective customers for a new travel promotion. You have data about people, their friends, their interests, and places they’ve visited. This small, sample collection of information is known as a constellation. Each script in this guide will utilize all or part of this constellation.
¶ Constellation data
This following small, sample collection of information is known as a constellation. Each script in this guide will evaluate all or part of the contents of this constellation.
¶ People
¶ Friends
¶ Interests
¶ Destinations
Whenever feasible, we keep the examples as small and focused as possible.
¶ Basic queries
For this section, we'll examine a collection of the simplest queries.
¶ Select all data
select * from Destination
Retrieves all properties in all Destination objects.
select Location, Type from Destination
Retrieves two properties in all Destination objects. Note that if you want to display results graphically and have appropriate assets be shown, be sure to include the ImageKey in the query:
select Location, Type, ImageKey from Destination
¶ Sorting and grouping
¶ Ascending sort
select * from Person order by InfluencerRating
Retrieve all properties from all Person objects, sort by InfluencerRating
select Name from Person order by InfluencerRating
Retrieve just the Name property from all Person objects, sort by InfluencerRating
¶ Descending sort
select * from Person order by DOB desc
Retrieve all properties from all Person objects, descending sort by DOB
select Name, InfluencerRating from Person order by DOB desc
Retrieve just the Name and InfluencerRating properties from all Person objects, descending sort by DOB
¶ Filtering
Filtering in SQL is achieved using the where clause, which allows users to specify conditions that the returned rows must meet. This mechanism helps in refining the query results by including only the data that matches the criteria specified in the where clause. In this section, we examine a diverse collection of filtered queries.
¶ Simple where filter
select * from Person where Name='Marco'
Retrieves all properties in Person objects where the Name property equals 'Marco'.
¶ Multiple where filters using or
select * from Person where Name='Marco' or Name='Danielle'
Retrieves all properties from all Person objects where the Name property is either 'Marco' or 'Danielle'.
¶ Multiple where filters using and
select * from Person where Name='Prasad' and Age=25
Retrieves all properties from all Person objects where the Name property is 'Prasad' and the Age property is 25.
select * from Person where Name='Prasad' and (Age > 20 and Age < 80)
Retrieves all properties from all Person objects where the Name property is 'Prasad' and the Age property is between 21 and 79.
¶ Multiple where filters using and combined with or
select * from Person where Name='Prasad' and (Age > 30 or InfluencerRating > 80)
Retrieves all properties from all Person objects where the Name property is 'Prasad' and the Age property is greater than 30 or the InfluencerRating is greater than 80.
¶ In usage with integers
select * from Person where Age in (25, 26, 75, 76)
Retrieves all properties for Person objects where the Age property is in the list of provided values.
select * from Person where Age in !(25, 26, 75, 76)
Retrieves all propertieds for all Person objects where the Age property is not in the list of provided values.
¶ In usage with strings
select * from Person where Name in ('Marco', 'Nicole', 'Danielle', 'Kublai', 'إبراهيم')
Retrieves all properties in all Person objects where the Name property is in the list of provided values.
select * from Person where Name in !('Marco', 'Nicole', 'Danielle', 'Kublai', 'إبراهيم')
Retrieves all properties in all Person objects where the Name property is not in the list of provided values.
¶ Count(*) function
select count(*) from Interest
Returns a count of all Interest objects.
¶ Count function with property
select count(Age) from Person
Returns a count of all Person objects with a non-null Age property.
¶ Limit number of objects
select * from Person limit 5
Return all properties from no more than five Person objects.
¶ Limit return all objects
select * from Person limit -1
Return all properties from all Person objects.
¶ Creating data
¶ Insert with values
insert into Person (Name,DOB,InfluencerRating) values ('Walter', date('1970-10-26'),25)
Create a new Person object using values.
¶ Insert with set
insert into Person set Name = 'Maude', DOB = date('1983-05-17'), InfluencerRating = 89
Create a new Person object using set.
¶ Modifying data
¶ Update using a filter
update Person set InfluencerRating = 99 where Name = 'Maude'
Update an existing Person object using a where clause
¶ Update and create a new property
update Person set NewProperty = 'New data'
Update all Person objects, creating a new property named NewProperty and setting a value for it
¶ Update all objects
update Person set NewProperty = 'Replaced data`
Update all the NewProperty property for all Person objects
¶ Removing data
¶ Delete using a filter
delete from Person where Name = 'Walter'
Delete any Person objects using the Name property to filter