<?php
/*
dobu {
file:id(`example-17380`) {
ascoos {
logo {`
__ _ ___ ___ ___ ___ ___ ___ ___
/ _` |/ / / __/ _ \ / _ \ / / / _ \ / /
| (_| |\ \| (_| (_) | (_) |\ \ | (_) |\ \
\__,_|/__/ \___\___/ \___/ /__/ \___/ /__/
`},
name {`ASCOOS OS`},
version {`1.0.0`},
},
example {
class {`TJSQLDBHandler`},
methods {`createDatabase(), createUser(), selectDatabase(), setSQLQuery(), bind(), execute(), getResults(), getLastError(), close()`},
source {`example3.php`},
category:langs {
en {`Databases`},
el {`?????? ?????????`}
},
subcategory:langs {
en {`JSQLDB`},
el {`JSQLDB`}
},
summary:langs {
en {`Example demonstrating database creation, user assignment, table creation, batch inserts with prepared statements, and data retrieval using the JSQLDB engine.`},
el {`?????????? ??? ??????? ?? ?????????? ?????, ???????????? ??????, ?????????? ??????, ??????? ????????? ?? prepared statements ??? ???????? ????????? ???? ??? JSQLDB.`}
},
desc:langs {
en {`This example showcases a complete workflow using the TJSQLDBHandler:
- initializing the database handler,
- creating a database and user,
- creating a table using SQL DDL,
- inserting multiple records using batch prepared statements,
- executing a SELECT query with ordering and limits,
- retrieving results,
- and properly closing all database resources.
It demonstrates real?world usage of JSQLDB inside Ascoos OS, including error handling and logging integration.`},
el {`???? ?? ?????????? ??????????? ??? ?????? workflow ?? ?? TJSQLDBHandler:
- ???????????? ??? database handler,
- ?????????? ????? ??? ??????,
- ?????????? ?????? ???? SQL DDL,
- ???????? ????????? ???????? ?? batch prepared statements,
- ???????? SELECT ?? ?????????? ??? ????,
- ???????? ?????????????,
- ??? ????? ???????? ???? ??? ????? ??? ?????.
??????????? ?????????? ????? ??? JSQLDB ???? ??? Ascoos OS, ??????????????????? ??? error handling ??? ??? logging.`}
},
keywords {`jsqldb, database, json-sql, sql-engine, ascoos-os,prepared-statements, batch-insert, select-query,table-creation, utf8, grapheme, example`},
tags {`example, jsqldb, database, insert, select, handler,prepared, batch, migration, demo`},
author {`Drogidis Christos`},
license {`AGL (ASCOOS General License)`},
since {`1.0.0`},
sincePHP {`8.4.0`}
}
}
}
*/
declare(strict_types=1);
use ASCOOS\OS\Kernel\DB\JSQLDB\TJSQLDBHandler;
use ASCOOS\OS\Kernel\Core\TError;
global $conf, $my;
$properties = [
'tables_prefix' => 'ascoos_', // final prefix e.g. ascoos_articles
'default_compression' => true // compression in TEXT
];
// Initialization of the database object
$jsqldb = new TJSQLDBHandler($conf, $properties);
// 1. Create or select a database (usually you do it once or during the install)
try {
// Database creation
$jsqldb->createDatabase('test_db');
// User creation and mapping to a database
$jsqldb->createUser('user', 'pass', 'test_db');
// Select current database
$jsqldb->selectDatabase('test_db');
} catch (Exception $e) {
$jsqldb->logger->log("Database init error: " . $e->getMessage(), $jsqldb::DEBUG_LEVEL_ERROR);
$jsqldb->close();
new TError("Database initialization problem.", E_ASCOOS_DB_JSQLDB_NOT_INIT);
}
// 2. Creating a table (usually in the migration/install script)
$sql = "CREATE TABLE IF NOT EXISTS `#__articles` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`article_id` INT UNSIGNED NOT NULL DEFAULT 0,
`cat_id` INT UNSIGNED NOT NULL DEFAULT 0,
`user_id` INT UNSIGNED NOT NULL DEFAULT 0,
`lang_id` INT UNSIGNED NOT NULL DEFAULT 0,
`title` VARCHAR(200) NOT NULL,
`content` TEXT NULL COMPRESSED,
`created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(),
`updated` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP()
);";
$jsqldb->setSQLQuery($sql);
if (!$jsqldb->execute()) {
$jsqldb->close();
new TError("Failed to create table: " . $jsqldb->logger->get_last_log(), E_ASCOOS_DB_JSQLDB_CREATE_TABLE);
}
// 3. Insert record (prepared statement)
$insertQuery = "INSERT INTO #__articles (article_id, cat_id, user_id, lang_id, title, content)
VALUES (?, ?, ?, ?, ?, ?)
";
$data = [
[1, 1, 1, 1, 'Title 1', 'Test Content 1'],
[1, 1, 1, 2, 'Title 2', 'Test Content 2'],
[2, 2, 1, 1, 'Title 3', 'Test Content 3'],
[3, 3, 1, 1, 'Title 4', 'Test Content 4'],
[3, 3, 1, 2, 'Title 5', 'Test Content 5']
];
$types = 'iiiiss'; // int, int, int, int, string, string
$jsqldb->bind($types, $data, $insertQuery); // notice: array inside an array for batch
if (!$jsqldb->execute()) {
$jsqldb->close();
new TError("Insert failed: " . $jsqldb->getLastError(), E_ASCOOS_DB_JSQLDB_INSERT_DATA);
}
// 4. Search - simple
$selectQuery = "SELECT article_id AS aid, title, content AS doc
FROM #__articles
WHERE user_id = ? AND lang_id = ?
ORDER BY created DESC
LIMIT 10
";
$jsqldb->bind('ii', [$my->id, 1], $selectQuery);
if (!$jsqldb->execute()) {
$jsqldb->close();
new TError("Insert failed: " . $jsqldb->getLastError(), E_ASCOOS_DB_JSQLDB_SELECT_QUERY);
}
$data = $jsqldb->getResults();
// 5. Closing all open database resources
$jsqldb->close();
print_r($data);
?>
|
Download
