.. _getting-started:

Getting Started with CoraDB
===========================

.. contents::
   :depth: 1
   :local:

Start with a New Database
--------------------------

Welcome to **CoraDB**, a unified database engine that blends relational SQL with native graph capabilities.  
Built on the **CUBRID** SQL engine, CoraDB lets you store, update, and query graph structures directly alongside relational data.

In this tutorial, you’ll build a **transportation network** where:
- **Cities**, **Companies**, and **Routes** are vertex labels
- **Connects** and **Operates** are edge labels
- Relational tables `Passenger` and `Ticket` extend the schema.

You’ll create data, explore graph queries, and execute hybrid SQL + MATCH queries.

The following steps show how to quickly set up and connect to a CoraDB database.

**Step 1: Create the Database**

Use the **coradb createdb** utility to create a new database. The **--db-volume-size** option sets the initial data volume size. The example below creates a database named *testdb* with a 20 MB volume using the **en_US** locale. ::

    % coradb createdb --db-volume-size=20M testdb en_US

**Step 2: Start the Server**

Start the CoraDB service and then start the *testdb* database server. ::

    % coradb service start
    % coradb server start testdb

**Step 3: Connect to the Database**

Use the **corasql** interpreter to connect to *testdb* as the **dba** user. ::

    % corasql -u dba testdb

1. Defining the Schema
~~~~~~~~~~~~~~~~~~~~~~

Let’s start by defining the graph and relational schema.

.. code-block:: sql

    -- Vertex labels
    CREATE VERTEX TABLE City (
        id INTEGER PRIMARY KEY,
        Name STRING,
        Country STRING,
        Population INTEGER
    );

    CREATE VERTEX TABLE Company (
        id INTEGER PRIMARY KEY,
        Name STRING,
        FleetSize INTEGER,
        Headquarters STRING
    );

    CREATE VERTEX TABLE Route (
        id INTEGER PRIMARY KEY,
        Code STRING,
        Type STRING,        -- "bus", "train", "flight"
        Distance INTEGER,
        Duration INTEGER
    );

    -- Edge labels
    CREATE EDGE TABLE Connects (
        Since INTEGER
    );

    CREATE EDGE TABLE Operates (
        Since INTEGER
    );

    -- Relational tables (non-graph)
    CREATE TABLE Passenger (
        id INTEGER PRIMARY KEY,
        Name STRING,
        HomeCity STRING
    );

    CREATE TABLE Ticket (
        id INTEGER PRIMARY KEY,
        PassengerID INTEGER,
        CompanyID INTEGER,
        RouteID INTEGER,
        Price INTEGER
    );


2. Inserting Data
~~~~~~~~~~~~~~~~~

**Insert vertices**

.. code-block:: sql

    INSERT INTO City VALUES
        (1, 'Bucharest', 'Romania', 1800000),
        (2, 'Sofia', 'Bulgaria', 1300000),
        (3, 'Belgrade', 'Serbia', 1200000),
        (4, 'Budapest', 'Hungary', 1750000),
        (5, 'Vienna', 'Austria', 1900000);

    INSERT INTO Company VALUES
        (10, 'EuroTrans', 150, 'Vienna'),
        (11, 'Danube Lines', 80, 'Budapest'),
        (12, 'Carpathia Express', 120, 'Bucharest');

    INSERT INTO Route VALUES
        (100, 'EUX-11', 'train', 240, 180),
        (101, 'EUX-12', 'bus', 370, 280),
        (102, 'EUX-13', 'bus', 380, 300),
        (103, 'EUX-14', 'train', 400, 310);


**Insert edges**

Each `INSERT EDGE` must connect exactly one source and one destination vertex.
Subqueries that return multiple rows are not allowed.

Connect routes to the cities they serve:

.. code-block:: sql

    -- Route EUX-12: Belgrade ↔ Budapest
    INSERT EDGE
        FROM (SELECT r FROM Route r WHERE r.Code='EUX-12')
        TO   (SELECT c FROM City c WHERE c.Name='Belgrade')
        INTO Connects VALUES (2020);

    INSERT EDGE
        FROM (SELECT r FROM Route r WHERE r.Code='EUX-12')
        TO   (SELECT c FROM City c WHERE c.Name='Budapest')
        INTO Connects VALUES (2020);

    -- Route EUX-13: Bucharest ↔ Sofia
    INSERT EDGE
        FROM (SELECT r FROM Route r WHERE r.Code='EUX-13')
        TO   (SELECT c FROM City c WHERE c.Name='Bucharest')
        INTO Connects VALUES (2021);

    INSERT EDGE
        FROM (SELECT r FROM Route r WHERE r.Code='EUX-13')
        TO   (SELECT c FROM City c WHERE c.Name='Sofia')
        INTO Connects VALUES (2021);

    -- Route EUX-14: Sofia ↔ Belgrade
    INSERT EDGE
        FROM (SELECT r FROM Route r WHERE r.Code='EUX-14')
        TO   (SELECT c FROM City c WHERE c.Name='Sofia')
        INTO Connects VALUES (2022);

    INSERT EDGE
        FROM (SELECT r FROM Route r WHERE r.Code='EUX-14')
        TO   (SELECT c FROM City c WHERE c.Name='Belgrade')
        INTO Connects VALUES (2022);

    -- Route EUX-11: Budapest ↔ Vienna
    INSERT EDGE
        FROM (SELECT r FROM Route r WHERE r.Code='EUX-11')
        TO   (SELECT c FROM City c WHERE c.Name='Budapest')
        INTO Connects VALUES (2023);

    INSERT EDGE
        FROM (SELECT r FROM Route r WHERE r.Code='EUX-11')
        TO   (SELECT c FROM City c WHERE c.Name='Vienna')
        INTO Connects VALUES (2023);
        
        
Assign operating companies to specific routes:

.. code-block:: sql

    INSERT EDGE
        FROM (SELECT comp FROM Company comp WHERE comp.Name='EuroTrans')
        TO   (SELECT r FROM Route r WHERE r.Code='EUX-11')
        INTO Operates VALUES (2015);

    INSERT EDGE
        FROM (SELECT comp FROM Company comp WHERE comp.Name='Danube Lines')
        TO   (SELECT r FROM Route r WHERE r.Code='EUX-12')
        INTO Operates VALUES (2018);

    INSERT EDGE
        FROM (SELECT comp FROM Company comp WHERE comp.Name='Carpathia Express')
        TO   (SELECT r FROM Route r WHERE r.Code='EUX-13')
        INTO Operates VALUES (2020);


**Insert relational data**

.. code-block:: sql

    INSERT INTO Passenger VALUES
        (1, 'Alice', 'Bucharest'),
        (2, 'Boris', 'Sofia'),
        (3, 'Clara', 'Vienna');

    INSERT INTO Ticket VALUES
        (101, 1, 12, 102, 120),
        (102, 2, 11, 101, 90),
        (103, 3, 10, 100, 140);


3. Querying the Graph
~~~~~~~~~~~~~~~~~~~~~

**Example 1 – Show all routes and their cities**

.. code-block:: sql

    SELECT r.Code, r.Type, c.Name AS City
    FROM MATCH (r:Route)-[e:Connects]->(c:City)
    ORDER BY r.Code, City;

**Example 2 – Companies and routes they operate**

.. code-block:: sql

    SELECT comp.Name AS Company, r.Code AS RouteCode, r.Type
    FROM MATCH (comp:Company)-[:Operates]->(r:Route)
    ORDER BY comp.Name;

**Example 3 – Variable-length traversal**

Find city-to-city connections reachable within 2 hops.

.. code-block:: sql

    SELECT start.Name AS StartCity, dest.Name AS Destination,
           horizontal_count(e) AS Steps
    FROM MATCH (start:City)<-[e:Connects{1,2}]-(r:Route), (r:Route)-[e2:Connects{1,2}]->(dest:City)
    WHERE start.Name='Bucharest';


4. Hybrid Graph–Relational Queries
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

**Example 1 – Join Ticket + Passenger with graph traversal**

.. code-block:: sql

    SELECT t.id AS TicketID,
           p.Name AS Passenger,
           r.Code AS RouteCode,
           comp.Name AS Operator,
           t.Price
    FROM Ticket t
         JOIN Passenger p ON p.id = t.PassengerID
         LEFT OUTER JOIN MATCH (comp:Company)-[:Operates]->(r:Route)
             ON comp.id = t.CompanyID AND r.id = t.RouteID
    ORDER BY Passenger;

**Example 2 – Link relational and graph data**

List all companies headquartered in cities that appear in any route.

.. code-block:: sql

    SELECT comp.Name, comp.Headquarters
    FROM Company comp
         LEFT OUTER JOIN MATCH (r:Route)-[:Connects]->(c:City)
             ON c.Name = comp.Headquarters
    ORDER BY comp.Name;

**Example 3 – Nested MATCH inside WHERE clause**

Find all routes that reach Vienna directly or through one intermediate city.

.. code-block:: sql

    SELECT rt.Code, rt.Type, rt.Distance, c2.Name AS Destination
    FROM Route rt,
         MATCH (r:Route)-[:Connects]->(c2:City)
    WHERE r.id = rt.id AND c2.Name = 'Vienna';

**Example 4 – Multi-root MATCH with relational filter**

Show all city pairs connected by a route operated by a company
with a fleet size greater than 100.

.. code-block:: sql

    SELECT c1.Name AS FromCity, c2.Name AS ToCity, comp.Name AS Operator
    FROM MATCH (comp:Company)-[:Operates]->(r:Route),
          (r:Route)-[:Connects]->(c1:City),
          (r:Route)-[:Connects]->(c2:City)
    WHERE comp.FleetSize > 100 AND c1.id < c2.id
    ORDER BY Operator;

**Example 5 – Cross-join between SQL and MATCH**

List passengers living in cities that have a route operated by EuroTrans.

.. code-block:: sql

    SELECT p.Name AS Passenger, p.HomeCity
    FROM Passenger p,
         MATCH (comp:Company)-[:Operates]->(r:Route), (r:Route)-[:Connects]->(c:City)
    WHERE comp.Name='EuroTrans' AND c.Name = p.HomeCity;


5. Inspecting the Graph
~~~~~~~~~~~~~~~~~~~~~~~

CoraDB provides JSON utilities for introspection:

.. code-block:: sql

    -- Export subgraph in JSON
    SELECT JSON_PRETTY (JSON_GRAPH(comp, r))
    FROM MATCH (comp:Company)-[:Operates]->(r:Route)
    WHERE comp.Name='Danube Lines';

    -- Display vertex label and properties
    SELECT LABEL(v), JSON_PRETTY (JSON_PROPERTIES(v))
    FROM MATCH (v:Route)
    WHERE v.Type='train';

    -- Inspect topology records
    SELECT JSON_PRETTY (JSON_TOPOLOGY_RECORD(c))
    FROM MATCH (c:City)
    WHERE c.Name='Budapest';


6. Summary
~~~~~~~~~~

You have now:

1. Created vertex and edge tables  
2. Inserted graph and relational data  
3. Queried graph relationships using `MATCH`  
4. Combined graph and relational joins  
5. Inspected graph structure with JSON tools  

CoraDB unifies tabular and connected data in one query engine.  
Try next:

- :ref:`reference-guide` for detailed syntax  
- Explore `OPTIONAL MATCH`, `ANY_SHORTEST`, and `aggregate_arr()`  
- Build more complex hybrid analytic queries  

Use our Demo Database
----------------------

CoraDB also ships with a ready-to-use demo database called **demodb** that you can explore right away — no setup required.

**Step 1: Start the Server**

Start the CoraDB service and then start the *demodb* database server. ::

    % coradb service start
    % coradb server start demodb

**Step 2: Connect to the Database**

Use the **corasql** interpreter to connect to *demodb* as the **dba** user. ::

    % corasql -u dba demodb

**Step 3: Explore the Database**

Once connected, you can explore the available tables using the **SHOW TABLES** statement. ::

    SHOW TABLES;

To view the definition of a specific table, use the **DESCRIBE** statement followed by the table name. For example, the following command shows the definition of the *customer* table. ::

    DESCRIBE customer;

**Step 4: Run Graph and Hybrid Queries**

The *demodb* database supports both pure graph queries and hybrid queries that combine graph traversal with relational SQL. The following examples illustrate what you can do.

**Example 1 – Graph: products with their category and available suppliers**

.. code-block:: sql

    SELECT p.productName, c.categoryName, s.companyName AS supplierName, a.since
    FROM MATCH (p:product)-[o:Part_Of]->(c:category), (p:product)-[a:Available_At]->(s:supplier)
    ORDER BY 1, 2, 3, 4
    LIMIT 10;

**Example 2 – Graph: brands located in the same city as customers who like their manufactured products**

.. code-block:: sql

    SELECT
      cu.companyName AS Customer,
      b.brandName AS Brand,
      p.productName AS Product,
      la.since AS BrandLocatedSince
    FROM MATCH
      (cu:customer)-[lk:Likes]->(p:product),
      (b:brand)-[m:Manufactures]->(p:product),
      (b:brand)-[la:Located_At]->(ci:city),
      (cu:customer)-[r:Resides_In]->(ci:city)
    ORDER BY 1, 2, 3, 4;

**Example 3 – Graph + relational: supplier revenue by city**

.. code-block:: sql

    SELECT
      ci.cityName AS City,
      s.companyName AS Supplier,
      SUM(p.unitPrice * oi.quantity * (1 - oi.discount)) AS TotalRevenue
    FROM MATCH (p:product)-[:Available_At]->(s:supplier),
               (s:supplier)-[:Located_In]->(ci:city)
    JOIN OrderItems oi ON oi.productID = p.productID
    GROUP BY ci.cityName, s.companyName
    ORDER BY 3 DESC, 1, 2;

**Example 4 – Graph + relational: products liked by customers with high total spending**

.. code-block:: sql

    SELECT
      p.productName,
      COUNT(DISTINCT cu.customerID) AS HighValueFans
    FROM MATCH (cu:customer)-[:Likes]->(p:product)
    JOIN (
      SELECT po.customerID
      FROM Orders po
      JOIN OrderItems oi ON oi.orderID = po.orderID
      JOIN product pr ON pr.productID = oi.productID
      GROUP BY po.customerID
      HAVING SUM(
        COALESCE(pr.unitPrice,0) * COALESCE(oi.quantity,0) * (1 - COALESCE(oi.discount,0))
      ) > 500
    ) hv ON hv.customerID = cu.customerID
    GROUP BY p.productName
    ORDER BY 2 DESC, 1;