Πρόσβαση SQL

Το Nette Database προσφέρει δύο δρόμους: μπορείτε να γράψετε μόνοι σας ερωτήματα SQL (πρόσβαση SQL), ή να τα αφήσετε να δημιουργηθούν αυτόματα (βλ. Explorer). Η πρόσβαση SQL σας δίνει πλήρη έλεγχο των ερωτημάτων και ταυτόχρονα εξασφαλίζει την ασφαλή σύνταξή τους.

Λεπτομέρειες σχετικά με τη σύνδεση και τη διαμόρφωση της βάσης δεδομένων θα βρείτε στο κεφάλαιο Σύνδεση και διαμόρφωση.

Βασικά ερωτήματα

Για την υποβολή ερωτημάτων στη βάση δεδομένων, χρησιμοποιείται η μέθοδος query(). Αυτή επιστρέφει ένα αντικείμενο ResultSet, το οποίο αντιπροσωπεύει το αποτέλεσμα του ερωτήματος. Σε περίπτωση αποτυχίας, η μέθοδος προκαλεί εξαίρεση. Μπορούμε να διατρέξουμε το αποτέλεσμα του ερωτήματος χρησιμοποιώντας έναν βρόχο foreach, ή να χρησιμοποιήσουμε κάποια από τις βοηθητικές συναρτήσεις.

$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
	echo $row->id;
	echo $row->name;
}

Για την ασφαλή εισαγωγή τιμών σε ερωτήματα SQL, χρησιμοποιούμε παραμετροποιημένα ερωτήματα. Το Nette Database τα καθιστά εξαιρετικά απλά – αρκεί να προσθέσετε ένα κόμμα και την τιμή μετά το ερώτημα SQL:

$database->query('SELECT * FROM users WHERE name = ?', $name);

Με περισσότερες παραμέτρους, έχετε δύο επιλογές σύνταξης. Μπορείτε είτε να “διανθίσετε” το ερώτημα SQL με παραμέτρους:

$database->query('SELECT * FROM users WHERE name = ?', $name, 'AND age > ?', $age);

Ή να γράψετε πρώτα ολόκληρο το ερώτημα SQL και στη συνέχεια να επισυνάψετε όλες τις παραμέτρους:

$database->query('SELECT * FROM users WHERE name = ? AND age > ?', $name, $age);

Προστασία από SQL injection

Γιατί είναι σημαντικό να χρησιμοποιείτε παραμετροποιημένα ερωτήματα; Επειδή σας προστατεύουν από την επίθεση που ονομάζεται SQL injection, κατά την οποία ο εισβολέας θα μπορούσε να εισάγει δικές του εντολές SQL και έτσι να αποκτήσει ή να καταστρέψει δεδομένα στη βάση δεδομένων.

Ποτέ μην εισάγετε μεταβλητές απευθείας στο ερώτημα SQL! Πάντα να χρησιμοποιείτε παραμετροποιημένα ερωτήματα, τα οποία σας προστατεύουν από το SQL injection.

// ❌ ΕΠΙΚΙΝΔΥΝΟΣ ΚΩΔΙΚΑΣ - ευάλωτος σε SQL injection
$database->query("SELECT * FROM users WHERE name = '$name'");

// ✅ Ασφαλές παραμετροποιημένο ερώτημα
$database->query('SELECT * FROM users WHERE name = ?', $name);

Ενημερωθείτε για τους πιθανούς κινδύνους ασφαλείας.

Τεχνικές ερωτημάτων

Συνθήκες WHERE

Μπορείτε να γράψετε τις συνθήκες WHERE ως έναν συσχετιστικό πίνακα, όπου τα κλειδιά είναι τα ονόματα των στηλών και οι τιμές είναι τα δεδομένα για σύγκριση. Το Nette Database επιλέγει αυτόματα τον καταλληλότερο τελεστή SQL ανάλογα με τον τύπο της τιμής.

$database->query('SELECT * FROM users WHERE', [
	'name' => 'John',
	'active' => true,
]);
// WHERE `name` = 'John' AND `active` = 1

Στο κλειδί, μπορείτε επίσης να καθορίσετε ρητά τον τελεστή για σύγκριση:

$database->query('SELECT * FROM users WHERE', [
	'age >' => 25,          // χρησιμοποιεί τον τελεστή >
	'name LIKE' => '%John%', // χρησιμοποιεί τον τελεστή LIKE
	'email NOT LIKE' => '%example.com%', // χρησιμοποιεί τον τελεστή NOT LIKE
]);
// WHERE `age` > 25 AND `name` LIKE '%John%' AND `email` NOT LIKE '%example.com%'

Το Nette χειρίζεται αυτόματα ειδικές περιπτώσεις όπως τιμές null ή πίνακες.

$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // χρησιμοποιεί τον τελεστή =
	'category_id' => [1, 2, 3], // χρησιμοποιεί το IN
	'description' => null,      // χρησιμοποιεί το IS NULL
]);
// WHERE `name` = 'Laptop' AND `category_id` IN (1, 2, 3) AND `description` IS NULL

Για αρνητικές συνθήκες, χρησιμοποιήστε τον τελεστή NOT:

$database->query('SELECT * FROM products WHERE', [
	'name NOT' => 'Laptop',         // χρησιμοποιεί τον τελεστή <>
	'category_id NOT' => [1, 2, 3], // χρησιμοποιεί το NOT IN
	'description NOT' => null,      // χρησιμοποιεί το IS NOT NULL
	'id' => [],                     // παραλείπεται
]);
// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

Για τη σύνδεση συνθηκών, χρησιμοποιείται ο τελεστής AND. Αυτό μπορεί να αλλάξει χρησιμοποιώντας το placeholder ?or.

Κανόνες ORDER BY

Η ταξινόμηση ORDER BY μπορεί να γραφτεί χρησιμοποιώντας έναν πίνακα. Στα κλειδιά, αναφέρουμε τις στήλες και η τιμή θα είναι ένα boolean που καθορίζει εάν θα ταξινομηθεί αύξουσα:

$database->query('SELECT id FROM author ORDER BY', [
	'id' => true, // αύξουσα
	'name' => false, // φθίνουσα
]);
// SELECT id FROM author ORDER BY `id`, `name` DESC

Εισαγωγή δεδομένων (INSERT)

Για την εισαγωγή εγγραφών, χρησιμοποιείται η εντολή SQL INSERT.

$values = [
	'name' => 'John Doe',
	'email' => 'john@example.com',
];
$database->query('INSERT INTO users ?', $values);
$userId = $database->getInsertId();

Η μέθοδος getInsertId() επιστρέφει το ID της τελευταίας εισαχθείσας γραμμής. Σε ορισμένες βάσεις δεδομένων (π.χ. PostgreSQL), είναι απαραίτητο να καθορίσετε ως παράμετρο το όνομα της ακολουθίας (sequence) από την οποία θα δημιουργηθεί το ID χρησιμοποιώντας $database->getInsertId($sequenceId).

Ως παραμέτρους μπορούμε επίσης να περάσουμε speciální hodnoty όπως αρχεία, αντικείμενα DateTime ή τύπους enum.

Εισαγωγή πολλαπλών εγγραφών ταυτόχρονα:

$database->query('INSERT INTO users ?', [
	['name' => 'User 1', 'email' => 'user1@mail.com'],
	['name' => 'User 2', 'email' => 'user2@mail.com'],
]);

Η πολλαπλή INSERT είναι πολύ ταχύτερη, επειδή εκτελείται ένα μόνο ερώτημα βάσης δεδομένων, αντί για πολλά μεμονωμένα.

Προειδοποίηση ασφαλείας: Ποτέ μην χρησιμοποιείτε μη επικυρωμένα δεδομένα ως $values. Ενημερωθείτε για τους πιθανούς κινδύνους.

Ενημέρωση δεδομένων (UPDATE)

Για την ενημέρωση εγγραφών, χρησιμοποιείται η εντολή SQL UPDATE.

// Ενημέρωση μίας εγγραφής
$values = [
	'name' => 'John Smith',
];
$result = $database->query('UPDATE users SET ? WHERE id = ?', $values, 1);

Ο αριθμός των επηρεασμένων γραμμών επιστρέφεται από το $result->getRowCount().

Για το UPDATE, μπορούμε να χρησιμοποιήσουμε τους τελεστές += και -=:

$database->query('UPDATE users SET ? WHERE id = ?', [
	'login_count+=' => 1, // αύξηση του login_count
], 1);

Παράδειγμα εισαγωγής ή τροποποίησης εγγραφής, εάν υπάρχει ήδη. Χρησιμοποιούμε την τεχνική ON DUPLICATE KEY UPDATE:

$values = [
	'name' => $name,
	'year' => $year,
];
$database->query('INSERT INTO users ? ON DUPLICATE KEY UPDATE ?',
	$values + ['id' => $id],
	$values,
);
// INSERT INTO users (`id`, `name`, `year`) VALUES (123, 'Jim', 1978)
//   ON DUPLICATE KEY UPDATE `name` = 'Jim', `year` = 1978

Παρατηρήστε ότι το Nette Database αναγνωρίζει σε ποιο πλαίσιο της εντολής SQL εισάγουμε την παράμετρο με τον πίνακα και ανάλογα συνθέτει τον κώδικα SQL. Έτσι, από τον πρώτο πίνακα συνέθεσε (id, name, year) VALUES (123, 'Jim', 1978), ενώ τον δεύτερο τον μετέτρεψε στη μορφή name = 'Jim', year = 1978. Αυτό το εξετάζουμε λεπτομερέστερα στην ενότητα Hints για τη σύνταξη SQL.

Διαγραφή δεδομένων (DELETE)

Για τη διαγραφή εγγραφών, χρησιμοποιείται η εντολή SQL DELETE. Παράδειγμα με λήψη του αριθμού των διαγραμμένων γραμμών:

$count = $database->query('DELETE FROM users WHERE id = ?', 1)
	->getRowCount();

Hints για τη σύνταξη SQL

Ένα hint είναι ένα ειδικό placeholder στο ερώτημα SQL που λέει πώς πρέπει να μεταγραφεί η τιμή της παραμέτρου σε έκφραση SQL:

Hint	Περιγραφή	Χρησιμοποιείται αυτόματα
`?name`	χρησιμοποιείται για την εισαγωγή ονόματος πίνακα ή στήλης	–
`?values`	δημιουργεί `(key, ...) VALUES (value, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	δημιουργεί ανάθεση `key = value, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	συνδέει συνθήκες στον πίνακα με τον τελεστή `AND`	`WHERE ?`, `HAVING ?`
`?or`	συνδέει συνθήκες στον πίνακα με τον τελεστή `OR`	–
`?order`	δημιουργεί τη ρήτρα `ORDER BY`	`ORDER BY ?`, `GROUP BY ?`

Για τη δυναμική εισαγωγή ονομάτων πινάκων και στηλών στο ερώτημα, χρησιμοποιείται το placeholder ?name. Το Nette Database φροντίζει για τη σωστή επεξεργασία των αναγνωριστικών σύμφωνα με τις συμβάσεις της συγκεκριμένης βάσης δεδομένων (π.χ. κλείσιμο σε ανάποδα εισαγωγικά ` ` ` στην MySQL).

$table = 'users';
$column = 'name';
$database->query('SELECT ?name FROM ?name WHERE id = 1', $column, $table);
// SELECT `name` FROM `users` WHERE id = 1 (στην MySQL)

Προειδοποίηση: χρησιμοποιήστε το σύμβολο ?name μόνο για ονόματα πινάκων και στηλών από επικυρωμένες εισόδους, διαφορετικά εκτίθεστε σε κίνδυνο ασφαλείας.

Τα υπόλοιπα hints συνήθως δεν χρειάζεται να αναφέρονται, καθώς το Nette χρησιμοποιεί έξυπνη αυτόματη ανίχνευση κατά τη σύνθεση του ερωτήματος SQL (βλ. τρίτη στήλη του πίνακα). Αλλά μπορείτε να το χρησιμοποιήσετε, για παράδειγμα, σε μια κατάσταση όπου θέλετε να συνδέσετε συνθήκες χρησιμοποιώντας OR αντί για AND:

$database->query('SELECT * FROM users WHERE ?or', [
	'name' => 'John',
	'email' => 'john@example.com',
]);
// SELECT * FROM users WHERE `name` = 'John' OR `email` = 'john@example.com'

Ειδικές τιμές

Εκτός από τους συνήθεις σκαλωτούς τύπους (string, int, bool), μπορείτε να περάσετε και ειδικές τιμές ως παραμέτρους:

αρχεία: fopen('image.gif', 'r') εισάγει το δυαδικό περιεχόμενο του αρχείου
ημερομηνία και ώρα: τα αντικείμενα DateTime μετατρέπονται στη μορφή της βάσης δεδομένων
τύποι enum: οι παρουσίες enum μετατρέπονται στην τιμή τους
SQL literals: δημιουργημένα με Connection::literal('NOW()') εισάγονται απευθείας στο ερώτημα

$database->query('INSERT INTO articles ?', [
	'title' => 'My Article',
	'published_at' => new DateTime,
	'content' => fopen('image.png', 'r'),
	'state' => Status::Draft,
]);

Σε βάσεις δεδομένων που δεν έχουν εγγενή υποστήριξη για τον τύπο δεδομένων datetime (όπως SQLite και Oracle), το DateTime μετατρέπεται στην τιμή που καθορίζεται στη διαμόρφωση της βάσης δεδομένων με την επιλογή formatDateTime (η προεπιλεγμένη τιμή είναι U – unix timestamp).

SQL Literals

Σε ορισμένες περιπτώσεις, πρέπει να αναφέρετε απευθείας κώδικα SQL ως τιμή, ο οποίος όμως δεν πρέπει να θεωρηθεί ως συμβολοσειρά και να υποστεί escaping. Για αυτό χρησιμεύουν τα αντικείμενα της κλάσης Nette\Database\SqlLiteral. Τα δημιουργεί η μέθοδος Connection::literal().

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	'year >' => $database::literal('YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (`year` > YEAR())

Ή εναλλακτικά:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (year > YEAR())

Τα SQL literals μπορούν να περιέχουν παραμέτρους:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > ? AND year < ?', $min, $max),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (year > 1978 AND year < 2017)

Χάρη σε αυτό, μπορούμε να δημιουργήσουμε ενδιαφέροντες συνδυασμούς:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('?or', [
		'active' => true,
		'role' => $role,
	]),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (`active` = 1 OR `role` = 'admin')

Λήψη δεδομένων

Συντομεύσεις για ερωτήματα SELECT

Για την απλοποίηση της ανάκτησης δεδομένων, το Connection προσφέρει αρκετές συντομεύσεις που συνδυάζουν την κλήση query() με την ακόλουθη fetch*(). Αυτές οι μέθοδοι δέχονται τις ίδιες παραμέτρους με το query(), δηλαδή το ερώτημα SQL και προαιρετικές παραμέτρους. Μια πλήρης περιγραφή των μεθόδων fetch*() βρίσκεται παρακάτω.

`fetch($sql, ...$params): ?Row`	Εκτελεί το ερώτημα και επιστρέφει την πρώτη γραμμή ως αντικείμενο `Row`
`fetchAll($sql, ...$params): array`	Εκτελεί το ερώτημα και επιστρέφει όλες τις γραμμές ως πίνακα αντικειμένων `Row`
`fetchPairs($sql, ...$params): array`	Εκτελεί το ερώτημα και επιστρέφει έναν συσχετιστικό πίνακα, όπου η πρώτη στήλη αντιπροσωπεύει το κλειδί και η δεύτερη την τιμή
`fetchField($sql, ...$params): mixed`	Εκτελεί το ερώτημα και επιστρέφει την τιμή του πρώτου πεδίου από την πρώτη γραμμή
`fetchList($sql, ...$params): ?array`	Εκτελεί το ερώτημα και επιστρέφει την πρώτη γραμμή ως αριθμημένο πίνακα

Παράδειγμα:

// fetchField() - επιστρέφει την τιμή του πρώτου κελιού
$count = $database->query('SELECT COUNT(*) FROM articles')
	->fetchField();

`foreach` – επανάληψη μέσω γραμμών

Μετά την εκτέλεση του ερωτήματος, επιστρέφεται ένα αντικείμενο ResultSet, το οποίο επιτρέπει την περιήγηση στα αποτελέσματα με διάφορους τρόπους. Ο ευκολότερος τρόπος για να εκτελέσετε ένα ερώτημα και να λάβετε τις γραμμές είναι με επανάληψη σε έναν βρόχο foreach. Αυτός ο τρόπος είναι ο πιο αποδοτικός από πλευράς μνήμης, καθώς επιστρέφει τα δεδομένα σταδιακά και δεν τα αποθηκεύει όλα στη μνήμη ταυτόχρονα.

$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
	echo $row->id;
	echo $row->name;
	// ...
}

Το ResultSet μπορεί να επαναληφθεί μόνο μία φορά. Εάν χρειάζεται να επαναλάβετε πολλές φορές, πρέπει πρώτα να φορτώσετε τα δεδομένα σε έναν πίνακα, για παράδειγμα χρησιμοποιώντας τη μέθοδο fetchAll().

fetch(): ?Row

Επιστρέφει μια γραμμή ως αντικείμενο Row. Εάν δεν υπάρχουν άλλες γραμμές, επιστρέφει null. Μετακινεί τον εσωτερικό δείκτη στην επόμενη γραμμή.

$result = $database->query('SELECT * FROM users');
$row = $result->fetch(); // φορτώνει την πρώτη γραμμή
if ($row) {
	echo $row->name;
}

fetchAll(): array

Επιστρέφει όλες τις υπόλοιπες γραμμές από το ResultSet ως πίνακα αντικειμένων Row.

$result = $database->query('SELECT * FROM users');
$rows = $result->fetchAll(); // φορτώνει όλες τις γραμμές
foreach ($rows as $row) {
	echo $row->name;
}

fetchPairs (string|int|null $key = null, string|int|null $value = null): array

Επιστρέφει τα αποτελέσματα ως συσχετιστικό πίνακα. Το πρώτο όρισμα καθορίζει το όνομα της στήλης που θα χρησιμοποιηθεί ως κλειδί στον πίνακα, το δεύτερο όρισμα καθορίζει το όνομα της στήλης που θα χρησιμοποιηθεί ως τιμή:

$result = $database->query('SELECT id, name FROM users');
$names = $result->fetchPairs('id', 'name');
// [1 => 'John Doe', 2 => 'Jane Doe', ...]

Εάν αναφέρουμε μόνο την πρώτη παράμετρο, η τιμή θα είναι ολόκληρη η γραμμή, δηλαδή το αντικείμενο Row:

$rows = $result->fetchPairs('id');
// [1 => Row(id: 1, name: 'John'), 2 => Row(id: 2, name: 'Jane'), ...]

Σε περίπτωση διπλότυπων κλειδιών, χρησιμοποιείται η τιμή από την τελευταία γραμμή. Κατά τη χρήση null ως κλειδί, ο πίνακας θα αριθμηθεί αριθμητικά από το μηδέν (τότε δεν προκύπτουν συγκρούσεις):

$names = $result->fetchPairs(null, 'name');
// [0 => 'John Doe', 1 => 'Jane Doe', ...]

fetchPairs (Closure $callback): array

Εναλλακτικά, μπορείτε να δώσετε ως παράμετρο ένα callback, το οποίο θα επιστρέφει για κάθε γραμμή είτε την ίδια την τιμή, είτε ένα ζεύγος κλειδιού-τιμής.

$result = $database->query('SELECT * FROM users');
$items = $result->fetchPairs(fn($row) => "$row->id - $row->name");
// ['1 - John', '2 - Jane', ...]

// Το callback μπορεί επίσης να επιστρέψει έναν πίνακα με ένα ζεύγος κλειδιού & τιμής:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

Επιστρέφει την τιμή του πρώτου πεδίου από την τρέχουσα γραμμή. Εάν δεν υπάρχουν άλλες γραμμές, επιστρέφει null. Μετακινεί τον εσωτερικό δείκτη στην επόμενη γραμμή.

$result = $database->query('SELECT name FROM users');
$name = $result->fetchField(); // φορτώνει το όνομα από την πρώτη γραμμή

fetchList(): ?array

Επιστρέφει μια γραμμή ως αριθμημένο πίνακα. Εάν δεν υπάρχουν άλλες γραμμές, επιστρέφει null. Μετακινεί τον εσωτερικό δείκτη στην επόμενη γραμμή.

$result = $database->query('SELECT name, email FROM users');
$row = $result->fetchList(); // ['John', 'john@example.com']

getRowCount(): ?int

Επιστρέφει τον αριθμό των επηρεασμένων γραμμών από το τελευταίο ερώτημα UPDATE ή DELETE. Για το SELECT, είναι ο αριθμός των επιστρεφόμενων γραμμών, αλλά αυτός μπορεί να μην είναι γνωστός – σε αυτή την περίπτωση, η μέθοδος επιστρέφει null.

getColumnCount(): ?int

Επιστρέφει τον αριθμό των στηλών στο ResultSet.

Πληροφορίες για τα ερωτήματα

Για σκοπούς εντοπισμού σφαλμάτων, μπορούμε να λάβουμε πληροφορίες σχετικά με το τελευταίο εκτελεσμένο ερώτημα:

echo $database->getLastQueryString();   // εκτυπώνει το ερώτημα SQL

$result = $database->query('SELECT * FROM articles');
echo $result->getQueryString();    // εκτυπώνει το ερώτημα SQL
echo $result->getTime();           // εκτυπώνει τον χρόνο εκτέλεσης σε δευτερόλεπτα

Για την εμφάνιση του αποτελέσματος ως πίνακα HTML, μπορείτε να χρησιμοποιήσετε:

$result = $database->query('SELECT * FROM articles');
$result->dump();

Το ResultSet προσφέρει πληροφορίες σχετικά με τους τύπους των στηλών:

$result = $database->query('SELECT * FROM articles');
$types = $result->getColumnTypes();

foreach ($types as $column => $type) {
	echo "$column είναι τύπου $type->type"; // π.χ. 'id είναι τύπου int' (id is of type int)
}

Καταγραφή ερωτημάτων

Μπορούμε να υλοποιήσουμε τη δική μας καταγραφή ερωτημάτων. Το συμβάν onQuery είναι ένας πίνακας callbacks που καλούνται μετά από κάθε εκτελεσμένο ερώτημα:

$database->onQuery[] = function ($database, $result) use ($logger) {
	$logger->info('Query: ' . $result->getQueryString());
	$logger->info('Time: ' . $result->getTime());

	if ($result->getRowCount() > 1000) {
		$logger->warning('Large result set: ' . $result->getRowCount() . ' rows');
	}
};