Skip to content

Instantly share code, notes, and snippets.

@EDDYMENS
Last active March 17, 2024 16:43
Show Gist options
  • Save EDDYMENS/1667e82f8da6b7c6ec8abd6f864ddda7 to your computer and use it in GitHub Desktop.
Save EDDYMENS/1667e82f8da6b7c6ec8abd6f864ddda7 to your computer and use it in GitHub Desktop.
Surreal DB PHP SDK

Here is an article explaining each method

<?php
/**
* Surreal fluent query
*
* @author EDDYMENS
* @license MIT (or other licence)
*/
class Surreal
{
private $url;
private $db;
private $nameSpace;
private $user;
private $pass;
private $query;
/**
* Constructor
*
* @param string $url DB URL
* @param string $user username
* @param string $pass password
* @param string $db DB
* @param string $nameSpace DB namespace
* @return object
*/
public function __construct($url, $user, $pass, $db, $nameSpace)
{
$this->url = $url;
$this->db = $db;
$this->nameSpace = $nameSpace;
$this->user = $user;
$this->pass = $pass;
}
/**
* rawQuery
* Run raw SQL
* @param string $query query statement to run
* @return array
*/
public function rawQuery($query)
{
return $this->requestProcessor($query);
}
/**
* exec
* Fluent query terminitor
* @return array
*/
public function exec()
{
return $this->rawQuery($this->query);
}
/**
* tables
* Specify table to run statement against
* @param string $tables list of tables
* @return $this
*/
public function tables($tables)
{
$tables = func_get_args();
$stringTables = implode(", ", $tables);
$this->query .= " " . $stringTables;
return $this;
}
/**
* select
* Select records
* @param string $fields list of fields to fetch
* @return $this
*/
public function select($fields = ' * ')
{
$providedFields = func_get_args();
$stringFields = implode(", ", $providedFields);
$this->query .= " SELECT " . $stringFields . " FROM";
return $this;
}
/**
* where
* Where statement
* @param string $statement fields and condition combo eg: age > 18
* @return $this
*/
public function where($statement)
{
$this->query .= " WHERE " . $statement;
return $this;
}
/**
* andWhere
* And Where statement
* @param string $statement fields and condition combo eg: age > 18
* @return $this
*/
public function andWhere($statement)
{
$this->query .= " AND " . $statement;
return $this;
}
/**
* orWhere
* Or Where statement
* @param string $statement fields and condition combo eg: age > 18
* @return $this
*/
public function orWhere($statement)
{
$this->query .= " OR " . $statement;
return $this;
}
/**
* groupBy
* Group records based on column
* @param string $field field to group by
* @return $this
*/
public function groupBy($field)
{
$this->query .= " GROUP BY " . $field;
return $this;
}
/**
* split
* Split records based on column
* @param string $field field to split by
* @return $this
*/
public function split($field)
{
$this->query .= " SPLIT " . $field;
return $this;
}
/**
* orderBy
* Order records based on column
* @param string $field field to order by
* @return $this
*/
public function orderBy($field)
{
$this->query .= " ORDER BY " . $field;
return $this;
}
/**
* start
* Specify index to start fetching records from
* @param integer $count index to start fetching from
* @return $this
*/
public function start($count)
{
$this->query .= " START " . $count;
return $this;
}
/**
* limit
* Number of records to return
* @param integer $count record size
* @return $this
*/
public function limit($count)
{
$this->query .= " LIMIT " . $count;
return $this;
}
/**
* timeout
* When to timeout request
* @param integer $time in seconds
* @return $this
*/
public function timeout($time)
{
$this->query .= " TIMEOUT " . $time . "s";
return $this;
}
/**
* parallel
* Fetch multiple records in parallel
* @return $this
*/
public function parallel()
{
$this->query .= " PARALLEL ";
return $this;
}
/**
* contains
* Fetch records if column contains provided word
* @param string $word word to search for
* @return $this
*/
public function contains($word)
{
$this->query .= " CONTAINS " . $word;
return $this;
}
/**
* delete
* delete a record
* @param string $table table to delete record from
* @return $this
*/
public function delete($tables)
{
$this->query .= " DELETE ";
$this->tables($tables);
return $this;
}
/**
* return
* Used to specify response type
* @param string $type NONE | BEFORE | AFTER | DIFF
* @return $this
*/
public function return($type)
{
$this->query .= " RETURN " . strtoupper($type);
return $this;
}
/**
* create
* Add new record
* @param string $table Table to insert record
* @return $this
*/
public function create($table)
{
$this->query .= " CREATE ";
$this->tables($table);
return $this;
}
/**
* update
* Update existing record
* @param string $table table to update record
* @return $this
*/
public function update($table)
{
$this->query .= " UPDATE ";
$this->tables($table);
return $this;
}
/**
* data
* Serialize data for create and update actions
* @param string $data data to serialize
* @return $this
*/
public function data($data)
{
$data = json_encode($data);
$this->query .= " CONTENT $data;";
return $this;
}
public function patch($data)
{
$data = json_encode($data);
$this->query .= " PATCH $data;";
return $this;
}
/**
* relate
* Create relationship between records in different tables
* @param string $table first table in relationship
* @return $this
*/
public function relate($table)
{
$this->query .= " RELATE ";
$this->tables($table);
return $this;
}
/**
* write
* Specify second table to use in relationship
* @param string $table second table to use in relationship
* @return $this
*/
public function write($table)
{
$this->query .= " ->write-> ";
$this->tables($table);
return $this;
}
/**
* merge
* Append data to existing record
* @param string $data new record to append
* @return $this
*/
public function merge($data)
{
$data = json_encode($data);
$this->query .= " MERGE $data;";
return $this;
}
/**
* toSQL
* Return generated SQL statement
* @return string
*/
public function toSQL()
{
return $this->query;
}
/**
* subQuery
* Add subquery to query
* @param func $funcQuery anonymous function containing subquery
* @return $this
*/
public function subQuery($funcQuery)
{
$this->query .= " (";
$funcQuery();
$this->query .= " )";
return $this;
}
/**
* begin
* Mark the start of a transaction
* @return $this
*/
public function begin()
{
$this->query .= " BEGIN TRANSACTION; ";
return $this;
}
/**
* commit
* Mark the end of a transaction
* @return $this
*/
public function commit()
{
$this->query .= " COMMIT TRANSACTION; ";
return $this;
}
/**
* cancel
* Cancel a transaction
* @return $this
*/
public function cancel()
{
$this->query .= " CANCEL TRANSACTION; ";
return $this;
}
private function requestProcessor($query)
{
$curl = curl_init();
curl_setopt_array($curl, array(
CURLOPT_URL => $this->url . '/sql',
CURLOPT_RETURNTRANSFER => true,
CURLOPT_ENCODING => '',
CURLOPT_MAXREDIRS => 10,
CURLOPT_TIMEOUT => 0,
CURLOPT_FOLLOWLOCATION => true,
CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
CURLOPT_CUSTOMREQUEST => 'POST',
CURLOPT_POSTFIELDS => $query,
CURLOPT_HTTPHEADER => array(
'Content-Type: application/json',
'NS: ' . $this->nameSpace,
'DB: ' . $this->db,
'Authorization: Basic ' . base64_encode($this->user . ':' . $this->pass),
),
));
$response = curl_exec($curl);
curl_close($curl);
return JSON_decode($response);
}
}
<?php
include('Surreal.php');
$surreal = new Surreal('http://localhost:8000', 'root', 'root', 'test', 'test');
// ****************************************
// Select records from employees table
// $results = $surreal->select('*')->tables('employees')->exec();
// ****************************************
// ****************************************
// Return SQL statement
// $results = $surreal->select('*')->tables('employees')->toSQL();
// ****************************************
// ****************************************
// Running sub queries
// $results = $surreal->select('*')->subQuery(function() use($surreal) {
// return $surreal->select('age >= 18 AS adult')->tables('author');
// })->exec();
// SELECT * FROM ( SELECT age >= 18 AS adult FROM author )
// ****************************************
// ****************************************
// Execute raw SQL statements
// $results = $surreal->sql(
// "
// SELECT * FROM article WHERE author.age < 30 FETCH author, account;
// "
// );
// ****************************************
// ****************************************
// andWhere statement
// $results = $surreal->select('age')->tables('author')->where('age < 1')->andWhere('age == 1')->orWhere('age > 1')->exec();
// ****************************************
// ****************************************
// Group records by a column value
// $results = $surreal->select('age')->tables('author')->groupBy('age')->exec();
// ****************************************
// ****************************************
// Split records by a column value
// $results = $surreal->select('age')->tables('author')->split('age')->exec();
// ****************************************
// ****************************************
// Order records by a column value
// $results = $surreal->select('age')->tables('author')->orderBy('age')->exec();
// ****************************************
// ****************************************
// Set a starting index and a number of records to fetch (pagination)
// $results = $surreal->select('age')->tables('author')->limit(2)->start(0)->exec();
// ****************************************
// ****************************************
// Set a request timeout after which if request is not completed should be terminated
// $results = $surreal->select('age')->tables('author')->timeout(2)->exec();
// ****************************************
// ****************************************
// Select related records in parallel
// $results = $surreal->select('->purchased->product<-purchased<-person->purchased->product')->table('author')->parallel()->exec();
// ****************************************
// ****************************************
// Delete a record
// $results = $surreal->delete('author')->where('author = 29')->exec();
// ****************************************
// ****************************************
// Delete a record. You can specify the type of response you want back using one of these
// NONE, DIFF, BEFORE, AFTER in the return statement
// $results = $surreal->delete('employees:mike')->return('DIFF')->exec();
// NONE, DIFF, BEFORE, AFTER
// ****************************************
// ****************************************
// Add a new record to a table
// $results = $surreal->create('author:eddy')->data([
// 'name' => 'Eddy',
// 'age' => 27,
// 'role' => 'Technical writer'
// ])->exec();
// ****************************************
// ****************************************
// Append new data set to all records in a table
// $results = $surreal->update('employees')->merge([
// 'company' => 'ACME'
// ])->exec();
// ****************************************
// ****************************************
// Update an existing record
// $results = $surreal->update('employees:1n5y7whusq5nf85h6qeo')->data([
// 'name' => 'Jeff',
// 'age' => 34,
// 'role' => 'Developer'
// ])->exec();
// ****************************************
// ****************************************
// Perform a transaction
// $surreal->begin();
// $results = $surreal->create('employees:mike')->data([
// 'name' => 'mike',
// 'age' => 19,
// 'role' => 'Dev Ops'
// ]);
// $results = $surreal->update('employees:mike')->merge([
// 'name' => 'mike Doe',
// ]);
// $surreal->commit()->exec();
// ****************************************
// ****************************************
// Create a relationship between records from two different tables
// $results = $surreal->relate('employees:mike')->write('author:eddy')->data([
// 'book' => 'cookbook',
// ])->return('after')->exec();
// ****************************************
// ****************************************
// Cancel a transaction
// $surreal->begin();
// $results = $surreal->create('employees:mike')->data([
// 'name' => 'Noah',
// 'age' => 19,
// 'role' => 'Dev Ops'
// ]);
// $results = $surreal->update('employees:mike')->merge([
// 'name' => 'Noah Doe',
// ]);
// $surreal->cancel()->exec();
// ****************************************
var_dump($results);
@moneya
Copy link

moneya commented Jan 7, 2023

is there a way to run Pagination?
i.e $results = $surreal->select('age')->tables('author')->orderBy('age')->paginate(5)->exec();

@EDDYMENS
Copy link
Author

EDDYMENS commented Jan 7, 2023

is there a way to run Pagination? i.e $results = $surreal->select('age')->tables('author')->orderBy('age')->paginate(5)->exec();

@moneya to paginate you need to use the combination of the start and limit methods eg: https://gist.github.com/EDDYMENS/1667e82f8da6b7c6ec8abd6f864ddda7#file-test-php-L63

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment