connection = $conn; } catch (\ErrorException $ex) { throw new DatabaseConnectionException($ex->getMessage(), $ex->getCode(), $ex); } } public static function getConnectionString(string $host, string $user, string $password, string $dbname, int $port = 5432): string { return self::buildConnectionString([ "host" => $host, "dbname" => $dbname, "user" => $user, "password" => $password, "port" => strval($port), ]); } public static function buildConnectionString(array $params): string { $items = []; foreach ($params as $key => $value) { $items []= $key . "='" . self::escapeConnectionStringValue($value) . "'"; } return implode(" ", $items); } private static function escapeConnectionStringValue(string $value): string { return str_replace([ '\\', "'", ], [ '\\\\', "\\'", ], $value); } private static function getTableName(string $entityClassName): string { $reflClass = new ReflectionClass($entityClassName); $attrs = $reflClass->getAttributes(Table::class); foreach ($attrs as $attr) return $attr->newInstance()->tableName; return "public." . StringUtils::camelToSnake($reflClass->getShortName()); } private static function getColumnName(ReflectionProperty &$prop): string { $attrs = $prop->getAttributes(Column::class); foreach ($attrs as $attr) { $name = $attr->newInstance()->columnName; if ($name !== null) return $name; } return StringUtils::camelToSnake($prop->getName()); } private static function getColumnType(ReflectionProperty &$prop): string { $attrs = $prop->getAttributes(Column::class); foreach ($attrs as $attr) { $type = $attr->newInstance()->columnType; if ($type !== null) return $type; } $type = $prop->getType(); if (!($type instanceof ReflectionNamedType)) throw new \RuntimeException("Union types or intersection types cannot be converted to a " . "database type. Please specify one manually using the #[Column] attribute!"); $typeName = $type->getName(); if (!$type->isBuiltin() && $typeName !== \DateTime::class && $typeName !== \DateTimeImmutable::class) throw new \RuntimeException("User-defined types cannot be converted to a " . "database type. Please specify one manually using the #[Column] attribute!"); switch ($typeName) { case \DateTime::class: case \DateTimeImmutable::class: return "timestamp with time zone"; case "true": case "false": case "bool": return "boolean"; case "float": return "double precision"; case "int": return "bigint"; case "string": case "null": return "text"; case "object": case "array": case "iterable": case "callable": case "mixed": case "never": case "void": default: throw new \RuntimeException("The type \"$typeName\" cannot be stored in the database"); } } private static function getColumnDefinitions(string $entityClassName): array { $statements = []; // TODO Foreign keys $rflEntity = new \ReflectionClass($entityClassName); $cols = self::getColumns($rflEntity); foreach ($cols as $colName => $colInfo) { $rflProp = new \ReflectionProperty($entityClassName, $colInfo["propertyName"]); $colType = self::getColumnType($rflProp); $statement = "$colName $colType"; if (($colInfo["flags"] & self::DEFAULT) !== 0) { $statement .= " DEFAULT " . $colInfo["defaultValue"]; } if (($colInfo["flags"] & self::UNIQUE) !== 0) { $statement .= " UNIQUE"; } if (($colInfo["flags"] & self::NOT_NULL) !== 0) { $statement .= " NOT NULL"; } if (($colInfo["flags"] & self::PRIMARY_KEY) !== 0) { $statement .= " PRIMARY KEY"; } if (($colInfo["flags"] & self::REFERENCES) !== 0) { $statement .= " REFERENCES " . $colInfo["reference"]; } $statements []= $statement; } return $statements; } private static function getColumns(ReflectionClass &$rflEntity): array { return ArrayUtils::assocFromPairs(array_map(function(ReflectionProperty $prop): array { $flags = 0; $attrs = $prop->getAttributes(); $reference = ""; $defaultValue = null; foreach ($attrs as $attr) { $attrName = $attr->getName(); if ($attrName === PrimaryKey::class) { $flags |= self::PRIMARY_KEY; } elseif ($attrName === NotNull::class) { $flags |= self::NOT_NULL; } elseif ($attrName === Unique::class) { $flags |= self::UNIQUE; } elseif ($attrName === References::class) { $flags |= self::REFERENCES; $reference = $attr->newInstance()->__toString(); } elseif ($attrName === DefaultValue::class) { $flags |= self::DEFAULT; $defaultValue = $attr->newInstance()->defaultValue; } } return [self::getColumnName($prop), [ "propertyName" => $prop->getName(), "flags" => $flags, "reference" => $reference, "defaultValue" => $defaultValue, "columnType" => self::getColumnType($prop), ]]; }, $rflEntity->getProperties(ReflectionProperty::IS_PUBLIC))); } private static function getPrimaryKeyColumn(array &$cols): ?string { foreach ($cols as $col => ["flags" => $flags]) if (($flags & self::PRIMARY_KEY) !== 0) return $col; return null; } private static function stringifyValue(mixed $value, string $columnType): ?string { if ($columnType === "bytea" && is_string($value)) return self::encodeBinary($value); elseif (is_null($value)) return null; elseif (is_bool($value)) return $value ? "true" : "false"; elseif (is_scalar($value)) return strval($value); elseif (is_a($value, \DateTimeInterface::class)) return $value->format("c"); else throw new \RuntimeException("Don't know how to stringify " . is_object($value) ? get_class($value) : gettype($value)); } private static function assignValue(Entity &$entity, array $colProps, ?string $value): void { $propName = $colProps["propertyName"]; $colType = $colProps["columnType"]; $prop = new \ReflectionProperty($entity, $propName); $type = $prop->getType(); $typedValue = null; if (!($type instanceof ReflectionNamedType)) throw new \RuntimeException("Union types or intersection types cannot be converted to from a database type."); $typeName = $type->getName(); if (!$type->isBuiltin() && $typeName !== \DateTime::class && $typeName !== \DateTimeImmutable::class) throw new \RuntimeException("User-defined types cannot be converted to from a database type."); if ($value !== null) switch ($typeName) { case \DateTime::class: $typedValue = new \DateTime($value); break; case \DateTimeImmutable::class: $typedValue = new \DateTimeImmutable($value); break; case "true": case "false": case "bool": $typedValue = in_array(strtolower($value), ["true","t","yes","on","1"]) ? true : (in_array(strtolower($value), ["false","f","no","off","0"]) ? false : null); break; case "float": $typedValue = floatval($value); break; case "int": $typedValue = intval($value); break; case "string": case "null": if ($colType === "bytea") { $typedValue = self::decodeBinary(strval($value)); } else { $typedValue = strval($value); } break; case "object": case "array": case "iterable": case "callable": case "mixed": case "never": case "void": default: throw new \RuntimeException("The type \"$typeName\" cannot be restored from the database"); } $entity->{$propName} = $typedValue; } private static function getColumnValues(Entity &$entity, array &$cols): array { $values = []; foreach ($cols as $colInfo) { $values []= self::stringifyValue($entity->{$colInfo["propertyName"]} ?? null, $colInfo["columnType"]); } return $values; } public function insert(Entity &$entity): void { $entityClassName = get_class($entity); $tableName = self::getTableName($entityClassName); $reflClass = new ReflectionClass($entityClassName); $cols = self::getColumns($reflClass); $query = "INSERT INTO $tableName VALUES (" . implode(",", ArrayUtils::fill(fn($i) => "$" . ($i + 1), count($cols))) . ");"; $result = \pg_query_params($this->connection, $query, self::getColumnValues($entity, $cols)); if ($result === false) throw new \RuntimeException("Insert failed: " . \pg_last_error($this->connection)); \pg_free_result($result); } public function fetch(Entity &$entity): bool { $entityClassName = get_class($entity); $tableName = self::getTableName($entityClassName); $reflClass = new ReflectionClass($entityClassName); $cols = self::getColumns($reflClass); $primaryCol = self::getPrimaryKeyColumn($cols); if ($primaryCol === null) throw new \RuntimeException("Fetching an entity requires a primary key column to be specified"); $query = "SELECT * FROM $tableName WHERE $primaryCol = \$1 LIMIT 1;"; $result = \pg_query_params($this->connection, $query, [ $entity->{$cols[$primaryCol]["propertyName"]} ]); if ($result === false) throw new \RuntimeException("Fetch failed: " . \pg_last_error($this->connection)); $row = \pg_fetch_assoc($result); \pg_free_result($result); if ($row === false) return false; foreach ($cols as $colName => $colProps) self::assignValue($entity, $colProps, $row[$colName]); return true; } public function fetchWhere(Entity &$entity, string|array $columnNames): bool { $entityClassName = get_class($entity); $tableName = self::getTableName($entityClassName); $reflClass = new ReflectionClass($entityClassName); $cols = self::getColumns($reflClass); if (!is_array($columnNames)) { $columnNames = [ $columnNames ]; } $whereClause = []; $columnValues = []; $count = 0; foreach ($columnNames as $columnName) { ++$count; if (!isset($cols[$columnName])) throw new \RuntimeException("Column $columnName does not exist!"); $whereClause []= "$columnName = \$$count"; $columnValues []= self::stringifyValue($entity->{$cols[$columnName]["propertyName"]}, $cols[$columnName]["columnType"]); } $whereClause = implode(" AND ", $whereClause); $query = "SELECT * FROM $tableName WHERE $whereClause LIMIT 1;"; $result = \pg_query_params($this->connection, $query, $columnValues); if ($result === false) throw new \RuntimeException("Fetch failed: " . \pg_last_error($this->connection)); $row = \pg_fetch_assoc($result); \pg_free_result($result); if ($row === false) return false; foreach ($cols as $colName => $colProps) self::assignValue($entity, $colProps, $row[$colName]); return true; } public function fetchAll(string $entityClassName): array { $tableName = self::getTableName($entityClassName); $reflClass = new ReflectionClass($entityClassName); $cols = self::getColumns($reflClass); $query = "SELECT * FROM $tableName;"; $result = \pg_query($this->connection, $query); if ($result === false) throw new \RuntimeException("Fetch failed: " . \pg_last_error($this->connection)); $items = []; while (($row = \pg_fetch_assoc($result)) !== false) { $entity = new $entityClassName(); foreach ($cols as $colName => $colProps) self::assignValue($entity, $colProps, $row[$colName]); $items []= $entity; } \pg_free_result($result); return $items; } public function fetchCustom(string $entityClassName, string $customQuery, ?array $customQueryParams = null): array { $tableName = self::getTableName($entityClassName); $reflClass = new ReflectionClass($entityClassName); $cols = self::getColumns($reflClass); $query = "SELECT * FROM $tableName $customQuery;"; if ($customQueryParams === null) $result = \pg_query($this->connection, $query); else $result = \pg_query_params($this->connection, $query, $customQueryParams); if ($result === false) throw new \RuntimeException("Fetch failed: " . \pg_last_error($this->connection)); $items = []; while (($row = \pg_fetch_assoc($result)) !== false) { $entity = new $entityClassName(); foreach ($cols as $colName => $colProps) self::assignValue($entity, $colProps, $row[$colName]); $items []= $entity; } \pg_free_result($result); return $items; } public function delete(Entity &$entity): bool { $entityClassName = get_class($entity); $tableName = self::getTableName($entityClassName); $reflClass = new ReflectionClass($entityClassName); $cols = self::getColumns($reflClass); $primaryCol = self::getPrimaryKeyColumn($cols); if ($primaryCol === null) throw new \RuntimeException("Deleting an entity requires a primary key column to be specified"); $query = "DELETE FROM $tableName WHERE $primaryCol = \$1;"; $result = \pg_query_params($this->connection, $query, [ $entity->{$cols[$primaryCol]["propertyName"]} ]); if ($result === false) throw new \RuntimeException("Deletion failed: " . \pg_last_error($this->connection)); $num_affected_rows = \pg_affected_rows($result); \pg_free_result($result); return $num_affected_rows >= 1; } public function update(Entity &$entity): bool { $tableName = self::getTableName(get_class($entity)); $reflClass = new ReflectionClass($entity); $cols = self::getColumns($reflClass); $primaryCol = self::getPrimaryKeyColumn($cols); if ($primaryCol === null) throw new \RuntimeException("Updating an entity requires a primary key column to be specified"); $filteredCols = array_values(array_filter(ArrayUtils::asPairs($cols), function($pair) { return ($pair[1]["flags"] & self::PRIMARY_KEY) === 0; })); // echo "
";
        // var_dump($filteredCols);
        // exit;
        $qCols = [];
        for ($i = 0; $i < count($filteredCols); ++$i) {
            $qCols []= implode("=", [ $filteredCols[$i][0], '$' . ($i + 2) ]);
        }
        $colDef = implode(", ", $qCols);
        $query = "UPDATE $tableName SET $colDef WHERE $primaryCol = \$1;";
        //$theCols = ArrayUtils::assocFromPairs($filteredCols);
        $result = \pg_query_params($this->connection, $query, self::getColumnValues($entity, $cols));
        if ($result === false)
            throw new \RuntimeException("Update failed: " . \pg_last_error($this->connection));
        $num_affected_rows = \pg_affected_rows($result);
        \pg_free_result($result);
        return $num_affected_rows >= 1;
    }

    public function ensureTable(string $entityClassName): void {
        $tableName = self::getTableName($entityClassName);
        $colDefs = self::getColumnDefinitions($entityClassName);
        $query = "CREATE TABLE IF NOT EXISTS $tableName ();";
        foreach ($colDefs as $colDef)
            $query .= "ALTER TABLE $tableName ADD COLUMN IF NOT EXISTS $colDef;";
        $result = \pg_query($this->connection, $query);
        if ($result === false)
            throw new \RuntimeException("Table creation failed: " . \pg_last_error($this->connection));
        \pg_free_result($result);
    }
}