$ composer require nagyatka/pandabaseWe recommend that use version above v0.20.0, because of significant API and performance changes.
You can reach the ConnectionManager singleton instance globally via getInstance() method
$connectionManager = ConnectionManager::getInstance();You can easily set a new database connection in Pandabase.
$connectionManager->initializeConnection([
"name" => "test_connection", // Connection's name.
"driver" => "mysql", // Same as PDO parameter
"dbname" => "test_dbname", // Same as PDO parameter
"host" => "127.0.0.1", // Same as PDO parameter
"user" => "root", // Same as PDO parameter
"password" => "" // Same as PDO parameter
"attributes"=> [
attributeName => value,
...
] // Optional, PDO attributes
]);ConnectionManager is able to handle more connection at time. The connections can be distinguished via the name parameter,
for example you can use "test_connection1" and "test_connection2" in the following example:
$connectionManager->initializeConnections(
[
[
"name" => "test_connection1", // Connection's name.
"driver" => "mysql", // Same as PDO parameter
"dbname" => "test_dbname1", // Same as PDO parameter
"host" => "127.0.0.1", // Same as PDO parameter
"user" => "root", // Same as PDO parameter
"password" => "" // Same as PDO parameter
],
[
"name" => "test_connection2", // Connection's name.
"driver" => "mysql", // Same as PDO parameter
"dbname" => "test_dbname2", // Same as PDO parameter
"host" => "127.0.0.1", // Same as PDO parameter
"user" => "root", // Same as PDO parameter
"password" => "" // Same as PDO parameter
],
]
);The getConnection method returns with the default connection if you leave the name parameter empty. The default connection
will be the firstly set connections.
$connection = $connectionManager->getConnection();$connection = $connectionManager->getConnection("test_connection2");// Set the 'test_connection2' Connection instance as the default
$connectionManager->setDefault("test_connection2");
// Returns with the instance of 'test_connection2' if exists
$connection = $connectionManager->getConnection();// Fetch a result row as an associative array
$queryResult = ConnectionManager::fetchAssoc("SELECT * FROM table1 WHERE table_id = :_id", [
"_id" => 11
]);
// Fetch result from default connection
$queryResult1 = ConnectionManager::fetchAll("SELECT * FROM table1");
// Fetch result from default connection with parameters
$queryResult2 = ConnectionManager::fetchAll("SELECT * FROM table1 WHERE store_date > :actual_date",[
"actual_date" => date("Y-m-d H:i:s")
]);
// Fetch result from specified connection (without parameters)
$queryResult3 = ConnectionManager::fetchAll("SELECT * FROM table1",[],"test_connection2");Connection is a PDO wrapper (all PDO function is callable) and provides a modified fetchAssoc and fetchAll methods for better usability. Although the ConnectionManager instance provides wrapper function for Connection instance's function so we recommend to use these wrapper function instead of calling them directly.
$connection = $connectionManager->getConnection();$result = $connection->fetchAssoc("SELECT * FROM table1 WHERE id = :id",["id" => $id]);$result = $connection->fetchAll("SELECT * FROM table1",[]);You can create classes based on tables of database. To achieve this, you have to only extend your classes from SimpleRecord or HistoryableRecord and register them to the specified connection.
Assume that we have a MySQL table named as transactions and it has a primary key.
CREATE TABLE `database_name`.`transactions` (
`transaction_id` int(11) NOT NULL AUTO_INCREMENT,
`transaction_value` int(11),
`user_id` int(11),
`store_date` datetime,
PRIMARY KEY (`transaction_id`)
) ENGINE=`InnoDB` COMMENT='';Implement Transaction class:
class Transaction extends SimpleRecord {
}In next step you have to add a Table object (this is a table descriptor class) to the specified Connection instance in the following way when you initialize the connection:
$connectionManager->initializeConnection([
"name" => "test_connection", // Connection's name.
"driver" => "mysql", // Same as PDO parameter
"dbname" => "database_name", // Same as PDO parameter
"host" => "127.0.0.1", // Same as PDO parameter
"user" => "root", // Same as PDO parameter
"password" => "" // Same as PDO parameter
"attributes"=> [
attributeName => value,
...
], // Optional, PDO attributes
"tables" => [
Transaction::class => new Table([
Table::TABLE_NAME => "transactions",
Table::TABLE_ID => "transaction_id",
]),
...
]
]);And that's all! Now you can create, update and delete records from the table:
// Create a new empty record (if your table scheme allows it)
$emptyRecord = new Transaction();
// Create a new record with values
$newRecord = new Transaction([
"transaction_value" => 5000,
"user_id" => 1234,
"store_date" => date('Y-m-d H:i:s')
]);
// To create new records in table you have to call ConnectionManager's persist function
ConnectionManager::persist($emptyRecord);
ConnectionManager::persist($newRecord);
// An other option is to use persistAll function
ConnectionManager::persistAll([
$emptyRecord,
$newRecord
]);
// Now $emptyRecord and $newRecord have transaction_id attribute
echo $emptyRecord["transaction_id"]." ".$newRecord["transaction_id"]."\n";
// Load record
$transaction = new Transaction($transactionId);
echo $transation->get("store_date").": ".$transaction["transaction_value"]; // You can use object as an array
// Load multiple record from transaction table (get all transaction of an user)
$transactions = ConnectionManager::getInstanceRecords(
Transaction::class,
"SELECT * FROM transactions WHERE user_id = :user_id",
[
"user_id" => 1234
]
);
// Update record
$transaction = new Transaction($transactionId);
$transation->set("transaction_value",4900);
$transation["store_date"] = date('Y-m-d H:i:s'); //You can use object as an array
ConnectionManager::persist($transation);
// Remove record
$transaction = new Transaction($transactionId);
$transation->remove();HistoryableRecord has the same features as SimpleRecord but it also storea the previous state of a record.
Assume that we have a MySQL table named as transactions and the table has the following columns (all of them required):
- sequence_id (PRIMARY KEY)
- id (record identifier, you can use it as ID in your code)
- record_status (0|1 -> inactive|active)
- history_from (datetime)
- history_to (datetime)
CREATE TABLE `database_name`.`orders` (
`order_sequence_id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(11),
`record_status` int(1),
`history_from` datetime,
`history_to` datetime,
`order_status` int(11),
`user_id` int(11),
`store_date` datetime,
PRIMARY KEY (`order_sequence_id`)
) ENGINE=`InnoDB` COMMENT='';Implement Order class:
class Order extends HistoryableRecord {
const Pending = 0;
const Processing = 1;
const Completed = 2;
const Declined = 3;
const Cancelled = 4;
/**
* Constructor
*/
public function __construct($parameters) {
$parameters["order_status"] = Order::Pending;
parent::__construct($parameters);
}
...
}In next step you have to add a Table object (this is a table descriptor class) to the specified Connection instance in the following way when you initialize the connection:
$connectionManager->initializeConnection([
"name" => "test_connection", // Connection's name.
"driver" => "mysql", // Same as PDO parameter
"dbname" => "database_name", // Same as PDO parameter
"host" => "127.0.0.1", // Same as PDO parameter
"user" => "root", // Same as PDO parameter
"password" => "" // Same as PDO parameter
"attributes"=> [
attributeName => value,
...
], // Optional, PDO attributes
"tables" => [
Order::class => new Table([
Table::TABLE_NAME => "orders",
Table::TABLE_ID => "order_id",
Table::TABLE_SEQ_ID => "order_sequence_id"
]),
...
]
]);Now you can use HistoryableRecord as a SimpleRecord, but you can get also historical information about the instance:
$order = new Order($order_id);
// Get full history
$orderHistory = $order->getHistory();
// You can also specify a date interval
$orderHistory = $order->getHistoryBetweenDates("2017-01-05","2017-01-08");Sometimes you have to store foreign keys in your table to represent connection between different objects. Without lazy attribute load you can load the objects this way:
$transaction = new Transaction($transactionId);
$order = new Order($transaction->get("order_id")); // We suppose that a transaction table also stores a valid order_idOr if you want to provide a class method:
class Transaction extends SimpleRecord {
// ...
/** @var Order */
private $order;
// ...
/** @return Order */
public function getOrder() {
if($this->order == null) {
$this->order = new Order($this->get("order_id"));
}
return $this->order;
}
}Instead of this, you can use LazyAttribute to implement this kind of connection on fast and easily way.
First you have to extend your table description. In our example we'd like to store an 'order_id' for a transaction record and want to reach the appropriate Order instance via 'order' key:
$connectionManager->initializeConnection([
"name" => "test_connection", // Connection's name.
"driver" => "mysql", // Same as PDO parameter
"dbname" => "database_name", // Same as PDO parameter
"host" => "127.0.0.1", // Same as PDO parameter
"user" => "root", // Same as PDO parameter
"password" => "" // Same as PDO parameter
"attributes"=> [
attributeName => value,
...
], // Optional, PDO attributes
"tables" => [
Order::class => new Table([
Table::TABLE_NAME => "orders",
Table::TABLE_ID => "order_id",
Table::TABLE_SEQ_ID => "order_sequence_id"
]),
Transaction::class => new Table([
Table::TABLE_NAME => "transactions",
Table::TABLE_ID => "transaction_id",
Table::LAZY_ATTRIBUTES => [
"order" => new LazyAttribute("order_id",Order::class)
]
]),
...
]
]);(We supposed that you extended the mysql table declaration too with the new 'order_id' column.)
Now you can use a Transaction instance in the following way:
// Load Transaction instance from db
$transaction = new Transaction($transactionId);
echo $transation->get("store_date").": ".$transaction["transaction_value"]; // You can use object as an array
/** @var Order $transactionOrder */
$transactionOrder = $transaction["order"]; // Return with an Order instance
$transactionOrderHistory = $transactionOrder->getHistory();####### TODO: AccessManagement
PandaBase is licensed under the Apache 2.0 License