Hiperspace.SQL 2.4.6

Prefix Reserved
.NET 8.0 This package targets .NET 8.0. The package is compatible with this framework or higher. 
dotnet add package Hiperspace.SQL --version 2.4.6
                    


                    

                
NuGet\Install-Package Hiperspace.SQL -Version 2.4.6
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package. 
<PackageReference Include="Hiperspace.SQL" Version="2.4.6" />
For projects that support PackageReference, copy this XML node into the project file to reference the package. 
<PackageVersion Include="Hiperspace.SQL" Version="2.4.6" />
                    


                            Directory.Packages.props
                        

                    

                
<PackageReference Include="Hiperspace.SQL" />
                    


                            Project file
For projects that support Central Package Management (CPM), copy this XML node into the solution Directory.Packages.props file to version the package. 
paket add Hiperspace.SQL --version 2.4.6
                    


                    

                
#r "nuget: Hiperspace.SQL, 2.4.6"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package. 
#:package Hiperspace.SQL@2.4.6
#:package directive can be used in C# file-based apps starting in .NET 10 preview 4. Copy this into a .cs file before any lines of code to reference the package. 
#addin nuget:?package=Hiperspace.SQL&version=2.4.6
                    


                            Install as a Cake Addin
                        

                    

                
#tool nuget:?package=Hiperspace.SQL&version=2.4.6
                    


                            Install as a Cake Tool

Hiperspace.SQL

Hiperspace.SQL is a full SQL query engine for Hiperspace, supporting the full range of joins, aggregations, and subqueries.

Hiperspace.SQL provides the same query functionality as a .NET client can use with LINQ queries, but without the need to write code in C#/F#

Hiperspace fully supports point-in-time "time travel" queries that are not possible with Python Data-Frames or DuckDB

Features

  • Hiperspace.SQL is not limited to queries of columns within a table, but supports the full navigation of properties of Hiperspace elements
  • Where a column is a complex object it is returned as a JSON object
  • Executing a batch of SQL statements return columnar data frames (dictionary of column-name and array of values)
  • Explain SQL returns the execution plan, detailing the SetSPaces accessed and keys used for search (Key, Index, Scan)
  • The Parquet method returns a Parquet file that can be used with any Apache Parquet library, or added to DuckDB OLAP store

Data Dictionary

SCHEMA_TABLE

Column Name Data Type Description
TABLE_NAME string The name of the table
TABLE_TYPE string The type of the table in SCHEMA_PROPERTY

SCHEMA_COLUMN

Column Name Data Type Description
TABLE_NAME string The name of the table
COLUMN_NAME string The name of the column
COLUMN_TYPE string The type of the table in SCHEMA_PROPERTY

SCHEMA_PROPERTY

Column Name Data Type Description
TYPE_NAME string The Type Name
PROPERTY_NAME string The name of each property
PROPERTY_TYPE string reference to SCHEMA_PROPERTY.TYPE_NAME

Examples

Simple query

SELECT p.Name, p.Gender FROM Persons as p WHERE p.Name = 'Lucy'

Query parameters

SELECT p.Name, p.Gender FROM Persons as p WHERE p.Name = :name

Query batches

SELECT p.Name, p.Gender FROM Persons as p WHERE p.Name = :name;
SELECT Name as name, Father as father from   Persons ;

Joins

SELECT  p.Name, f.Name as Father, f.Father as GrandFather
FROM    Persons as p 
join    Persons as f on p.Father.Name = f.Name
WHERE   p.Name = :name

Aggregates

select  p.Father.Name, count(p.Name) as Children
from    Persons as p
group by p.Father.Name as f
having count(*) > 1;

Like expressions

select  p.Father.Name, count(p.Name) as Children
from    Persons as p
where   Name like 'L%' and Name like '%y' or (Name like '%u%' and Name like '_uc_')
group by p.Father.Name as f
having count(*) > 1;

Null handling

select  p.Name, p.Father.Name
from    Persons as p
where   Name is not null

in query

SELECT p.Name, p.Gender 
FROM Persons as p 
WHERE p.Gender in (select p2.Gender from Persons as p2 where p2.Name = 'Lucy')

union

SELECT p.Name, p.Gender 
FROM Persons as p 
WHERE p.Name in ('Lucy', 'Mark')
union 
SELECT p.Name, p.Gender 
FROM Persons as p 
WHERE p.Name in ('Eve', 'Mary')

inline view

SELECT p.Name, p.Gender 
FROM Persons as p 
join (select p2.Gender from Persons as p2 where p2.Name = 'Lucy') as p3 on p.Gender = p3.Gender

dictionary query

select * from SCHEMA_TABLES;
select * from SCHEMA_COLUMNS;
select * from SCHEMA_PROPERTIES;

API

The Hiperspace.SQL API can be called from any language that supports DOTNET interop, including Python (using pythonnet). Access via the Hiperspace.SQL.Engine object that is constructed with a reference to any domain space.

Explain

Provides a detailed breakdown of the query execution plan

member engine.Explain (source, parameters : IDictionary<string,obj>) : string array =

Execute

Executes the SQL queries and returns an array of Data Frames

member engine.Execute (source , parameters : IDictionary<string,obj>) : IDictionary<string, obj array> array  =

Parquet

Executes the SQL queries will a n array of filenames (one for each statement) and returns the filenames after writing the results to the Apache Parquet files.

member this.Parquet (source, fileNames, parameters : IDictionary<string,obj>): string array =
Product Compatible and additional computed target framework versions.
.NET net8.0 is compatible.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed.  net9.0 is compatible.  net9.0-android was computed.  net9.0-browser was computed.  net9.0-ios was computed.  net9.0-maccatalyst was computed.  net9.0-macos was computed.  net9.0-tvos was computed.  net9.0-windows was computed.  net10.0 was computed.  net10.0-android was computed.  net10.0-browser was computed.  net10.0-ios was computed.  net10.0-maccatalyst was computed.  net10.0-macos was computed.  net10.0-tvos was computed.  net10.0-windows was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last Updated
2.4.6 162 9/23/2025
2.4.4 246 8/7/2025
2.4.2 134 7/28/2025
2.4.0 166 7/10/2025
2.3.8 155 7/1/2025
2.3.4 155 6/5/2025
2.2.2 175 5/5/2025
2.2.1 227 4/14/2025
2.2.0 113 3/29/2025
2.1.9 243 3/5/2025
2.1.6 141 2/15/2025
2.0.0 97 1/14/2025
1.0.5 136 11/15/2024
1.0.2 134 11/1/2024
1.0.1 172 10/18/2024

https://www.cepheis.com/hiperspace/20250923
## Overview

This release introduces three new features for references to views, the `Any` polymorphic union type and `Get<TEntity>(string sid)` function.   Together they provide the `Hiperspace.DB` functionality to combine any number of Hiperspaces in a single  Graph view without duplication or copying.


-----


### View References

Views in Hiperspace are generated to combine all *entities, segments, aspects and views* that can be projected as the view, but also provide an additional role for sub-types and complex associations.

All references in `Hiperspace` can be navigated to either *elements* key or an index that matches the predicate used to reference it. The [last release](https://www.cepheis.com/hiperspace/20250807) completed the optimization of view access to parts, this release adds indexing to efficiently access view items with criteria other than the key.

#### Indexed views
Consider the example of a Trading problem where **Trades** are booked in  a **Book**, but each trade type {Fixed Income, Forex, Equity}  can have specific properties for the product traded.   When the view is referenced by a member other than the key (*Book in this example*), an index is created to efficiently access the set of matching values.
```
view Banking.Trade (Id : String) {Book : Banking.Book};
entity Banking.FI.Trade : Banking.Trade = Banking.Trade();
entity Banking.FX.Trade : Banking.Trade = Banking.Trade();
entity Banking.EQ.Trade : Banking.Trade = Banking.Trade();
entity Banking.Book (Id : String) [Trades : Banking.Trade (Book = this)];
```
`Trades : Trade (Book = this)`selects the set of Trade that have a reference to `this` Book.  This access causes an index to be created in the view `Trade` that is inherited by each implementation.

#### Sub type
[image]Sites/hiperspace/diagrams/trade.svg[/image]

This [UML](https://en.wikipedia.org/wiki/Unified_Modeling_Language) diagram represents the logical view of the `.hilang` schema above.  When a relational database is used, one of three strategies are normally followed:
* **Direct**: Each entity {Trade, FI_Trade, FX_Trade, EQ_Trade} is mapped separately, and joined as needed
* **Down** denormalization: Properties of the parent are duplicated in each of the sub-types and with context specific joining
* **Up** denormalization: Properties of each sub-type are added to the base type (optionally with *aliases* for duplicate names)

Hiperspace does not need denormalization the elements because types can inherit from a base type.  The code `entity FX_Trade : Trade = Trade;` states that the *entity* **FX_Trade** *inherits*(:) from **Trade** *and can be viewed*(=) as a **Trade**

In the example above, each sub-type will be queried in parallel using the index on the **Book** reference

##### Modelled as Aspect
The schema could also have been modelled using *aspect* rather than *realization*, depending on business  requirement, e.g.
* *Convertible* where final exchange is either equity or cash
* *Hybrid* where cashflow is calculated from either from a yield-curve or equity price
* *Participation* where the equity price uses FX exchange rates

[image]Sites/hiperspace/diagrams/trade2.svg[/image]
```
entity Banking.Trade (Id : String) {Book : Banking.Book}
[FI : Banking.FI.Trade, FX : Banking.FX.Trade, EQ : Banking.EQ.Trade];
aspect Banking.FI.Trade;
aspect Banking.FX.Trade;
aspect Banking.EQ.Trade;
entity Banking.Book (Id : String) [Trades : Banking.Trade (Book = this)];
```
**Aspects** *appear* to be properties of the *owning*  entity,  so the choice has minimal impact on usage ( `Banking.FI.Trade { YieldCurve = ... }` vs `Banking.Trade { FI = new Banking.FI.Trade { YieldCurve = ...}}`)


#### Segment / Aspect reference
Segments and Aspects are *owned* by the `entity, segment, aspect` that references them as an extension.  They appear (*to users *) as a set property for segments, and  value property for aspects, with the `owner` added *transparently* when a value is assigned.

[image]Sites/hiperspace/diagrams/segment.svg[/image]

```
entity Customer [Accounts : Has];
segment Has (Account : Account);
entity Account [HasCustomer : Has (Account = this)];
```
`HiLang` translates the segment `Has` into an element `CustomerHas` (adding the key `owner : Customer`), and the segment `Has` is transformed into a *view* that `CustomerHas` provides.  If there are no other implementations of `Has` (*e.g. for a sub-account of Account*) the view is *pruned* and not generated.

In this example `Has` is referenced by `Account` to provide a link from `Account` to `Customer` using the `HasCustomerIndex`.  As a `segment`/`aspect` can be applied to any stored element, `Has` view includes  the key `owner : Any` which in this case can be *cast* to `Customer`.

-----

### Any
The new type `Any` has been introduced to support `segment`/`aspect` views, and can be converted to any of the domain types like a `union` in F#.  Unlike `object`, `Any` can be serialized and used anywhere in the schema, when a generic reference is required.  `Any` is code generated by `HiLang` to include a constructor for the each of the domain types, together will *cast* operators to convert to each of the domain types.  Elements also include a *cast* operator to convert to an `Any` when assigned.

`Any` uses constructors and conversion operators  to behave like a base type for any domain element `Any value = customer` *will use Customer’s operator Any function*, `Customer? value = any` *will use Any’s operator Customer function* to return the value or `null`.  Explicit cast functions are crerted for each *element* that could be stored.. for the *Customer* type it includes
* **Customer()** : cast the Any to a customer or null
* **CustomerAsync()** : async function to return the value (*especially usefull for WebAssembly where network read may be required*)

`Any` includes the functions
* `Type AnyType()`to inspect the content of the `Any` class for match/switch statement
* `bool Is<T>()` : returns true if the content of the `Any` is of type `T`
* `T? As<T>()` : returns the value of the `Any` cast to the type `T`
* `Task<T?> AsAsync<T>()` : returns the value of the `Any` cast to the type `T` using an async network call if necassary

-----

### Get(string sid)
`SubSpace` provides an `object? Get (string sid)` function that can be used to get an element in Hiperspace without knowing the type, just using the stringified key of the element.  This function is now obsolete and has been replaced by `TEntity Get<TEntity>(string sid)` that allows the desired type to be retrieved.

This method was added for `Hiperspace.DB` that includes `GraphSpace` to aggregate `Node`/`Edge`/`HiperEdge` across domains.  For `GraphSpace` it is necessary to call `Get()` with the `SKey` of a `Node` and then cast to `Node` which was not possible with `object` because *elements* do not need be viewable as a `Node`.  `GraphSpace` uses `Get<Node>(skey)` for the lookup.