OrientDB

-- OrientDB SQL

-- 1. Opret classes (lignende tables/collections)
CREATE CLASS Person EXTENDS V;
CREATE CLASS Company EXTENDS V;
CREATE CLASS WorksFor EXTENDS E;

-- 2. Opret properties
CREATE PROPERTY Person.name STRING;
CREATE PROPERTY Person.email STRING;
CREATE PROPERTY Person.age INTEGER;
CREATE PROPERTY Company.name STRING;

-- 3. Opret indexes
CREATE INDEX Person.email UNIQUE;

-- 4. Indsæt vertices (nodes)
INSERT INTO Person SET 
  name = 'Peter Hansen',
  email = 'peter@email.dk',
  age = 30;

INSERT INTO Company SET 
  name = 'TechCorp';

-- 5. Opret edge (relationship)
CREATE EDGE WorksFor FROM 
  (SELECT FROM Person WHERE email = 'peter@email.dk') TO
  (SELECT FROM Company WHERE name = 'TechCorp')
SET since = date('2020-01-01');

-- 6. Query vertices
SELECT * FROM Person WHERE age > 25;

-- 7. Graph traversal (OUTGOING)
SELECT expand(out('WorksFor')) FROM Person 
WHERE name = 'Peter Hansen';

-- 8. Graph traversal (INCOMING)
SELECT expand(in('WorksFor')) FROM Company 
WHERE name = 'TechCorp';

-- 9. Traverse multiple levels
SELECT FROM (
  TRAVERSE out('WorksFor'), out('ParentCompany') FROM 
  (SELECT FROM Person WHERE name = 'Peter Hansen')
  WHILE $depth <= 3
);

-- 10. Shortest path
SELECT shortestPath(
  (SELECT FROM Person WHERE name = 'Peter Hansen'),
  (SELECT FROM Person WHERE name = 'Maria Nielsen'),
  'BOTH'
);

-- 11. Pattern matching
MATCH 
  {class: Person, as: person}-WorksFor->{class: Company, as: company}
RETURN person.name, company.name;

-- 12. Aggregation
SELECT age, count(*) as total 
FROM Person 
GROUP BY age 
ORDER BY total DESC;

-- 13. UPDATE
UPDATE Person SET age = 31 WHERE email = 'peter@email.dk';

-- 14. DELETE
DELETE VERTEX Person WHERE email = 'old@email.dk';
DELETE EDGE WorksFor WHERE since < date('2015-01-01');

// Java API eksempel
import com.orientechnologies.orient.core.db.*;
import com.tinkerpop.blueprints.impls.orient.*;

// Connect
OrientDB orientDB = new OrientDB("remote:localhost", OrientDBConfig.defaultConfig());
ODatabaseSession db = orientDB.open("mydb", "admin", "admin");

// Create vertex
OVertex person = db.newVertex("Person");
person.setProperty("name", "John Doe");
person.setProperty("email", "john@email.com");
person.setProperty("age", 28);
person.save();

// Create edge
OVertex company = db.query(
  "SELECT FROM Company WHERE name = ?", 
  "TechCorp"
).get(0);

OEdge worksFor = person.addEdge(company, "WorksFor");
worksFor.setProperty("since", new Date());
worksFor.save();

// Query
OResultSet rs = db.query(
  "SELECT * FROM Person WHERE age > ?", 
  25
);

while (rs.hasNext()) {
  OResult result = rs.next();
  System.out.println(result.getProperty("name"));
}

// Graph traversal
OResultSet traversal = db.query(
  "SELECT expand(out('WorksFor')) FROM Person WHERE name = ?",
  "John Doe"
);

// Transaction
db.begin();
try {
  OVertex v1 = db.newVertex("Person");
  v1.setProperty("name", "Transaction Person");
  v1.save();
  
  OVertex v2 = db.newVertex("Company");
  v2.setProperty("name", "Transaction Corp");
  v2.save();
  
  db.commit();
} catch (Exception e) {
  db.rollback();
}

// Close
db.close();
orientDB.close();

// Node.js eksempel
const OrientDB = require('orientjs');

const server = OrientDB({
  host: 'localhost',
  port: 2424,
  username: 'root',
  password: 'root'
});

const db = server.use('mydb');

// Insert
await db.query(
  'INSERT INTO Person SET name = :name, email = :email',
  { params: { name: 'Node User', email: 'node@email.com' } }
);

// Select
const results = await db.query(
  'SELECT * FROM Person WHERE age > :age',
  { params: { age: 25 } }
);

console.log(results);

// Graph traversal
const companies = await db.query(
  "SELECT expand(out('WorksFor')) FROM Person WHERE name = :name",
  { params: { name: 'Node User' } }
);