summaryrefslogtreecommitdiff
path: root/libs/picodb/lib/PicoDb/Builder
diff options
context:
space:
mode:
Diffstat (limited to 'libs/picodb/lib/PicoDb/Builder')
-rw-r--r--libs/picodb/lib/PicoDb/Builder/BaseBuilder.php86
-rw-r--r--libs/picodb/lib/PicoDb/Builder/ConditionBuilder.php377
-rw-r--r--libs/picodb/lib/PicoDb/Builder/InsertBuilder.php36
-rw-r--r--libs/picodb/lib/PicoDb/Builder/OrConditionBuilder.php43
-rw-r--r--libs/picodb/lib/PicoDb/Builder/UpdateBuilder.php56
5 files changed, 598 insertions, 0 deletions
diff --git a/libs/picodb/lib/PicoDb/Builder/BaseBuilder.php b/libs/picodb/lib/PicoDb/Builder/BaseBuilder.php
new file mode 100644
index 00000000..e075ae3c
--- /dev/null
+++ b/libs/picodb/lib/PicoDb/Builder/BaseBuilder.php
@@ -0,0 +1,86 @@
+<?php
+
+namespace PicoDb\Builder;
+
+use PicoDb\Database;
+
+/**
+ * Class InsertBuilder
+ *
+ * @package PicoDb\Builder
+ * @author Frederic Guillot
+ */
+abstract class BaseBuilder
+{
+ /**
+ * @var Database
+ */
+ protected $db;
+
+ /**
+ * @var ConditionBuilder
+ */
+ protected $conditionBuilder;
+
+ /**
+ * @var string
+ */
+ protected $table = '';
+
+ /**
+ * @var string[]
+ */
+ protected $columns = array();
+
+ /**
+ * InsertBuilder constructor
+ *
+ * @param Database $db
+ * @param ConditionBuilder $condition
+ */
+ public function __construct(Database $db, ConditionBuilder $condition)
+ {
+ $this->db = $db;
+ $this->conditionBuilder = $condition;
+ }
+
+ /**
+ * Get object instance
+ *
+ * @static
+ * @access public
+ * @param Database $db
+ * @param ConditionBuilder $condition
+ * @return static
+ */
+ public static function getInstance(Database $db, ConditionBuilder $condition)
+ {
+ return new static($db, $condition);
+ }
+
+ /**
+ * Set table name
+ *
+ * @access public
+ * @param string $table
+ * @return $this
+ */
+ public function withTable($table)
+ {
+ $this->table = $table;
+ return $this;
+ }
+
+ /**
+ * Set columns name
+ *
+ * @access public
+ * @param string[] $columns
+ * @return $this
+ */
+ public function withColumns(array $columns)
+ {
+ $this->columns = $columns;
+ return $this;
+ }
+}
diff --git a/libs/picodb/lib/PicoDb/Builder/ConditionBuilder.php b/libs/picodb/lib/PicoDb/Builder/ConditionBuilder.php
new file mode 100644
index 00000000..b0465b6e
--- /dev/null
+++ b/libs/picodb/lib/PicoDb/Builder/ConditionBuilder.php
@@ -0,0 +1,377 @@
+<?php
+
+namespace PicoDb\Builder;
+
+use PicoDb\Database;
+use PicoDb\Table;
+
+/**
+ * Handle SQL conditions
+ *
+ * @package PicoDb\Builder
+ * @author Frederic Guillot
+ */
+class ConditionBuilder
+{
+ /**
+ * Database instance
+ *
+ * @access private
+ * @var Database
+ */
+ private $db;
+
+ /**
+ * Condition values
+ *
+ * @access private
+ * @var array
+ */
+ private $values = array();
+
+ /**
+ * SQL AND conditions
+ *
+ * @access private
+ * @var string[]
+ */
+ private $conditions = array();
+
+ /**
+ * SQL OR conditions
+ *
+ * @access private
+ * @var OrConditionBuilder[]
+ */
+ private $orConditions = array();
+
+ /**
+ * SQL condition offset
+ *
+ * @access private
+ * @var int
+ */
+ private $orConditionOffset = 0;
+
+ /**
+ * Constructor
+ *
+ * @access public
+ * @param Database $db
+ */
+ public function __construct(Database $db)
+ {
+ $this->db = $db;
+ }
+
+ /**
+ * Build the SQL condition
+ *
+ * @access public
+ * @return string
+ */
+ public function build()
+ {
+ return empty($this->conditions) ? '' : ' WHERE '.implode(' AND ', $this->conditions);
+ }
+
+ /**
+ * Get condition values
+ *
+ * @access public
+ * @return array
+ */
+ public function getValues()
+ {
+ return $this->values;
+ }
+
+ /**
+ * Returns true if there is some conditions
+ *
+ * @access public
+ * @return boolean
+ */
+ public function hasCondition()
+ {
+ return ! empty($this->conditions);
+ }
+
+ /**
+ * Add custom condition
+ *
+ * @access public
+ * @param string $sql
+ */
+ public function addCondition($sql)
+ {
+ if ($this->orConditionOffset > 0) {
+ $this->orConditions[$this->orConditionOffset]->withCondition($sql);
+ }
+ else {
+ $this->conditions[] = $sql;
+ }
+ }
+
+ /**
+ * Start OR condition
+ *
+ * @access public
+ */
+ public function beginOr()
+ {
+ $this->orConditionOffset++;
+ $this->orConditions[$this->orConditionOffset] = new OrConditionBuilder();
+ }
+
+ /**
+ * Close OR condition
+ *
+ * @access public
+ */
+ public function closeOr()
+ {
+ $condition = $this->orConditions[$this->orConditionOffset]->build();
+ $this->orConditionOffset--;
+
+ if ($this->orConditionOffset > 0) {
+ $this->orConditions[$this->orConditionOffset]->withCondition($condition);
+ } else {
+ $this->conditions[] = $condition;
+ }
+ }
+
+ /**
+ * Equal condition
+ *
+ * @access public
+ * @param string $column
+ * @param mixed $value
+ */
+ public function eq($column, $value)
+ {
+ $this->addCondition($this->db->escapeIdentifier($column).' = ?');
+ $this->values[] = $value;
+ }
+
+ /**
+ * Not equal condition
+ *
+ * @access public
+ * @param string $column
+ * @param mixed $value
+ */
+ public function neq($column, $value)
+ {
+ $this->addCondition($this->db->escapeIdentifier($column).' != ?');
+ $this->values[] = $value;
+ }
+
+ /**
+ * IN condition
+ *
+ * @access public
+ * @param string $column
+ * @param array $values
+ */
+ public function in($column, array $values)
+ {
+ if (! empty($values)) {
+ $this->addCondition($this->db->escapeIdentifier($column).' IN ('.implode(', ', array_fill(0, count($values), '?')).')');
+ $this->values = array_merge($this->values, $values);
+ }
+ }
+
+ /**
+ * IN condition with a subquery
+ *
+ * @access public
+ * @param string $column
+ * @param Table $subquery
+ */
+ public function inSubquery($column, Table $subquery)
+ {
+ $this->addCondition($this->db->escapeIdentifier($column).' IN ('.$subquery->buildSelectQuery().')');
+ $this->values = array_merge($this->values, $subquery->getConditionBuilder()->getValues());
+ }
+
+ /**
+ * NOT IN condition
+ *
+ * @access public
+ * @param string $column
+ * @param array $values
+ */
+ public function notIn($column, array $values)
+ {
+ if (! empty($values)) {
+ $this->addCondition($this->db->escapeIdentifier($column).' NOT IN ('.implode(', ', array_fill(0, count($values), '?')).')');
+ $this->values = array_merge($this->values, $values);
+ }
+ }
+
+ /**
+ * NOT IN condition with a subquery
+ *
+ * @access public
+ * @param string $column
+ * @param Table $subquery
+ */
+ public function notInSubquery($column, Table $subquery)
+ {
+ $this->addCondition($this->db->escapeIdentifier($column).' NOT IN ('.$subquery->buildSelectQuery().')');
+ $this->values = array_merge($this->values, $subquery->getConditionBuilder()->getValues());
+ }
+
+ /**
+ * LIKE condition
+ *
+ * @access public
+ * @param string $column
+ * @param mixed $value
+ */
+ public function like($column, $value)
+ {
+ $this->addCondition($this->db->escapeIdentifier($column).' '.$this->db->getDriver()->getOperator('LIKE').' ?');
+ $this->values[] = $value;
+ }
+
+ /**
+ * ILIKE condition
+ *
+ * @access public
+ * @param string $column
+ * @param mixed $value
+ */
+ public function ilike($column, $value)
+ {
+ $this->addCondition($this->db->escapeIdentifier($column).' '.$this->db->getDriver()->getOperator('ILIKE').' ?');
+ $this->values[] = $value;
+ }
+
+ /**
+ * Greater than condition
+ *
+ * @access public
+ * @param string $column
+ * @param mixed $value
+ */
+ public function gt($column, $value)
+ {
+ $this->addCondition($this->db->escapeIdentifier($column).' > ?');
+ $this->values[] = $value;
+ }
+
+ /**
+ * Greater than condition with subquery
+ *
+ * @access public
+ * @param string $column
+ * @param Table $subquery
+ */
+ public function gtSubquery($column, Table $subquery)
+ {
+ $this->addCondition($this->db->escapeIdentifier($column).' > ('.$subquery->buildSelectQuery().')');
+ $this->values = array_merge($this->values, $subquery->getConditionBuilder()->getValues());
+ }
+
+ /**
+ * Lower than condition
+ *
+ * @access public
+ * @param string $column
+ * @param mixed $value
+ */
+ public function lt($column, $value)
+ {
+ $this->addCondition($this->db->escapeIdentifier($column).' < ?');
+ $this->values[] = $value;
+ }
+
+ /**
+ * Lower than condition with subquery
+ *
+ * @access public
+ * @param string $column
+ * @param Table $subquery
+ */
+ public function ltSubquery($column, Table $subquery)
+ {
+ $this->addCondition($this->db->escapeIdentifier($column).' < ('.$subquery->buildSelectQuery().')');
+ $this->values = array_merge($this->values, $subquery->getConditionBuilder()->getValues());
+ }
+
+ /**
+ * Greater than or equals condition
+ *
+ * @access public
+ * @param string $column
+ * @param mixed $value
+ */
+ public function gte($column, $value)
+ {
+ $this->addCondition($this->db->escapeIdentifier($column).' >= ?');
+ $this->values[] = $value;
+ }
+
+ /**
+ * Greater than or equal condition with subquery
+ *
+ * @access public
+ * @param string $column
+ * @param Table $subquery
+ */
+ public function gteSubquery($column, Table $subquery)
+ {
+ $this->addCondition($this->db->escapeIdentifier($column).' >= ('.$subquery->buildSelectQuery().')');
+ $this->values = array_merge($this->values, $subquery->getConditionBuilder()->getValues());
+ }
+
+ /**
+ * Lower than or equals condition
+ *
+ * @access public
+ * @param string $column
+ * @param mixed $value
+ */
+ public function lte($column, $value)
+ {
+ $this->addCondition($this->db->escapeIdentifier($column).' <= ?');
+ $this->values[] = $value;
+ }
+
+ /**
+ * Lower than or equal condition with subquery
+ *
+ * @access public
+ * @param string $column
+ * @param Table $subquery
+ */
+ public function lteSubquery($column, Table $subquery)
+ {
+ $this->addCondition($this->db->escapeIdentifier($column).' <= ('.$subquery->buildSelectQuery().')');
+ $this->values = array_merge($this->values, $subquery->getConditionBuilder()->getValues());
+ }
+
+ /**
+ * IS NULL condition
+ *
+ * @access public
+ * @param string $column
+ */
+ public function isNull($column)
+ {
+ $this->addCondition($this->db->escapeIdentifier($column).' IS NULL');
+ }
+
+ /**
+ * IS NOT NULL condition
+ *
+ * @access public
+ * @param string $column
+ */
+ public function notNull($column)
+ {
+ $this->addCondition($this->db->escapeIdentifier($column).' IS NOT NULL');
+ }
+}
diff --git a/libs/picodb/lib/PicoDb/Builder/InsertBuilder.php b/libs/picodb/lib/PicoDb/Builder/InsertBuilder.php
new file mode 100644
index 00000000..9d06c405
--- /dev/null
+++ b/libs/picodb/lib/PicoDb/Builder/InsertBuilder.php
@@ -0,0 +1,36 @@
+<?php
+
+namespace PicoDb\Builder;
+
+/**
+ * Class InsertBuilder
+ *
+ * @package PicoDb\Builder
+ * @author Frederic Guillot
+ */
+class InsertBuilder extends BaseBuilder
+{
+ /**
+ * Build SQL
+ *
+ * @access public
+ * @return string
+ */
+ public function build()
+ {
+ $columns = array();
+ $placeholders = array();
+
+ foreach ($this->columns as $column) {
+ $columns[] = $this->db->escapeIdentifier($column);
+ $placeholders[] = ':'.$column;
+ }
+
+ return sprintf(
+ 'INSERT INTO %s (%s) VALUES (%s)',
+ $this->db->escapeIdentifier($this->table),
+ implode(', ', $columns),
+ implode(', ', $placeholders)
+ );
+ }
+}
diff --git a/libs/picodb/lib/PicoDb/Builder/OrConditionBuilder.php b/libs/picodb/lib/PicoDb/Builder/OrConditionBuilder.php
new file mode 100644
index 00000000..0defeaf4
--- /dev/null
+++ b/libs/picodb/lib/PicoDb/Builder/OrConditionBuilder.php
@@ -0,0 +1,43 @@
+<?php
+
+namespace PicoDb\Builder;
+
+/**
+ * Class OrConditionBuilder
+ *
+ * @package PicoDb\Builder
+ * @author Frederic Guillot
+ */
+class OrConditionBuilder
+{
+ /**
+ * List of SQL conditions
+ *
+ * @access protected
+ * @var string[]
+ */
+ protected $conditions = array();
+
+ /**
+ * Add new condition
+ *
+ * @access public
+ * @param string $condition
+ * @return $this
+ */
+ public function withCondition($condition) {
+ $this->conditions[] = $condition;
+ return $this;
+ }
+
+ /**
+ * Build SQL
+ *
+ * @access public
+ * @return string
+ */
+ public function build()
+ {
+ return '('.implode(' OR ', $this->conditions).')';
+ }
+}
diff --git a/libs/picodb/lib/PicoDb/Builder/UpdateBuilder.php b/libs/picodb/lib/PicoDb/Builder/UpdateBuilder.php
new file mode 100644
index 00000000..300ea9b0
--- /dev/null
+++ b/libs/picodb/lib/PicoDb/Builder/UpdateBuilder.php
@@ -0,0 +1,56 @@
+<?php
+
+namespace PicoDb\Builder;
+
+/**
+ * Class UpdateBuilder
+ *
+ * @package PicoDb\Builder
+ * @author Frederic Guillot
+ */
+class UpdateBuilder extends BaseBuilder
+{
+ /**
+ * @var string[]
+ */
+ protected $sumColumns = array();
+
+ /**
+ * Set columns name
+ *
+ * @access public
+ * @param string[] $columns
+ * @return $this
+ */
+ public function withSumColumns(array $columns)
+ {
+ $this->sumColumns = $columns;
+ return $this;
+ }
+
+ /**
+ * Build SQL
+ *
+ * @access public
+ * @return string
+ */
+ public function build()
+ {
+ $columns = array();
+
+ foreach ($this->columns as $column) {
+ $columns[] = $this->db->escapeIdentifier($column).'=?';
+ }
+
+ foreach ($this->sumColumns as $column) {
+ $columns[] = $this->db->escapeIdentifier($column).'='.$this->db->escapeIdentifier($column).' + ?';
+ }
+
+ return sprintf(
+ 'UPDATE %s SET %s %s',
+ $this->db->escapeIdentifier($this->table),
+ implode(', ', $columns),
+ $this->conditionBuilder->build()
+ );
+ }
+}
generated by cgit v1.2.3 (git 2.46.0) at 2024-11-26 20:23:54 +0000