Starcounter
HomeDownloadDocsCommunity
2.3.1
2.3.1
  • Starcounter Documentation
  • Getting Started
  • Starcounter
    • Collapsing the Stack
      • Complexity and Scalability Tradeoff
      • The Future of Micro-Services
      • 10 Benefits of Collapsing the Stack
    • Integrated Database and Web Server
  • Hello World - Tutorial
    • Create a Database Class
    • Create a Real Time UI
    • First Interactive UI
    • Computed Properties
    • Expense Tracker
    • Cancel and Delete
    • The Next Step
  • Guides
    • Database
      • Database Classes
      • Data manipulation
      • Object Identity and Object References
      • Querying with SQL
      • Data Types
      • Relations
      • Inheritance
      • Sharing data
      • Database Configuration
      • Comparing Database Objects
      • Referential Integrity and Constraints
    • SQL
      • Identifiers
      • Path Expressions
      • Data operators
      • Joins
      • Aggregates
      • Comparisons and Logical Operators
      • Sorting
      • Fetch
      • Offset Key
      • Indexes
      • Literals
      • Query Plan Hints
      • Reserved words
      • Query for Database Classes
      • SQL Isolation Between Applications
    • Transactions
      • Short-Running Transactions
      • Long running transactions
      • Using Transactions
      • Running Background Jobs
      • Commit Hooks
    • Typed JSON
      • JSON-by-example
      • Code-Behind
      • Data Bindings
      • Callback Methods
      • Responding with JSON
      • Accepting JSON in Requests
      • Primitive Arrays and Single Value Types
      • Typed JSON Internals
    • Blendable Web Apps
      • Starcounter MVVM
      • Palindrom
      • Client-Side Stack
      • Sessions
      • HTML Views
      • App Shell
      • Web Components
      • View Attaching
      • View Composing
      • HTML Compositions
      • HTML Views Blending Guidelines
      • Avoiding CSS conflicts
      • Debugging
    • Network
      • HTTP
      • Internal Self Calls
      • Middleware
      • Anonymous or Substitute Handlers
      • URL Aliases and Redirects
      • Network Gateway
      • Static File Server
      • External HTTP Calls
      • WebSocket
      • Avoiding URI conflicts
      • TCP Sockets
      • UDP Sockets
    • Publishing Apps
    • Working with Starcounter
      • Release Channels
      • Starting and Stopping Apps
      • Administrator Web UI
      • Star CLI
      • StarAdmin CLI
      • StarDump CLI
      • Working in Visual Studio
      • Error Log
      • Using HTTPS on NGINX
      • Using HTTPS on IIS
      • Run Starcounter in Production
      • Weaver
      • Investigating App Crashes
      • Configuration Structure
      • Database Refactoring
      • Using Unload/Reload to Modify Database Schema
      • Kernel Questions and Answers
      • Log Files
  • Cookbook
    • Attach an HTTP Request to an Existing Long-Running Transaction
    • Cookie-Based Authentication
    • Timestamp on Object Creation
    • Creating Strongly Typed JSON Collections
    • Migrating From 2.2 to 2.3+
    • Multiple Pages
    • Icons
    • Proposed Project Structure
    • Acceptance Testing with Selenium
    • Requesting a User to Authenticate
    • How to delete unused tables and columns
Powered by GitBook
On this page
  • Introduction
  • Calling SQL
  • Object Extensions to SQL
  1. Guides

SQL

PreviousReferential Integrity and ConstraintsNextIdentifiers

Last updated 7 years ago

Introduction

Starcounter SQL follows the established standard SQL92 (ANSI INCITS 135-1992, R1998) to support easy data exchange with other databases and external tools.

Calling SQL

There are two to use Starcounter SQL:

  1. In programming code with Db.SQL as described in .

  2. In the interactive SQL browser of the .

The SQL in the Administrator and in code are not identical. For performance reasons, can't be used in the programming code with Db.SQL while it's available in the Administrator. Because of this, when using the examples in this documentation, remember to replace literals with in programming code.

Object Extensions to SQL

Starcounter SQL contains some extensions to the SQL92 standard to better deal with objects, since the standard SQL only supports relational databases. For these extensions we follow the Object Data Standard ODMG 3.0 (ISBN 1-55860-647-4). The object extensions in Starcounter SQL are:

  • object references,

  • .

In traditional SQL you can only refer to tables, columns, rows and fields of values. The concept of an "object" is represented by a row in a table. You refer to an "object" by values on its primary key which are some specified columns.

In Starcounter SQL you can refer to an object itself. For example, in query below the identifier e is an object reference.

SELECT e FROM Employee e

In a traditional SQL database, to get data from more than one type of object (table/class) you have to do a "join" of a number of tables. For example, query below gives you the names of the employees and the names of the departments where they work.

SELECT e.FirstName, d.Name FROM Employee e JOIN Department d ON e.DepartmentId = d.Id

In Starcounter SQL there is a more convenient way to get the same result by instead using a path expression as in query below. In that way, you can from one type of object (Employee) reach another type of object (Department) by object reference.

SELECT e.FirstName, e.Department.Name FROM Employee e

In object oriented programming the extent of a class is all object instances of that class. An extent of object instances corresponds to a table of rows in a relational database. Thus, Employee in the example queries above can either be regarded as the extent of the class Employee or as the table Employee.

Querying using SQL
Starcounter Administrator
literals
path expressions
variables