skript-db

# skript-db _Awesome direct database access for Skript_ > This is a fork of btk5h's skript-db, rewritten to function with Minecraft versions 1.21 up to 26.x (Skript 2.14 and newer), including bug fixes, performance improvements, transactions and batch statements What makes skript-db different from other SQL addons? - Automatic SQL injection protection - Support for connections to multiple databases - An amazingly intuitive list variable system for reading query outputs - Transactions and batch statements for many rows in one round trip #### Syntax ``` [the] data(base|[ ]source) [(of|at)] %string% [with [a] [max[imum]] [connection] life[ ]time of %timespan%] [[(using|with)] [a] driver %-string%] ``` #### Examples ``` set {sql} to the database "mysql://localhost:3306/mydatabase?user=admin&password=12345&useSSL=false" set {sql} to the database "mariadb://localhost:3306/mydatabase?user=admin&password=12345" set {sql} to the database "postgresql://localhost:5432/mydatabase?user=admin&password=12345&ssl=false" set {sql} to the database "sqlite:plugins/database.db" # Extra parameters: set {sql} to the database "postgresql://localhost:5432/mydatabase?user=admin&password=12345&ssl=false" with a maximum connection lifetime of 30 minutes set {sql} to the database "postgresql://localhost:5432/mydatabase?user=admin&password=12345&ssl=false" with a maximum connection lifetime of 30 minutes using driver "org.postgresql.Driver" ``` --- ### Effect `Execute Statement` Executes a statement on a database and optionally stores the result in a variable. Expressions embedded in the query are bound as parameters, so they can never change the meaning of the statement. A list expression such as `%{_ids::*}%` expands to one parameter per value, which makes `IN (...)` easy. If a single variable, such as `{test}`, is passed, the variable will be set to the number of affected rows, or the number of rows of a query. With `generated keys` it receives the first key created by an insert. If a list variable, such as `{test::*}`, is passed, the query result will be mapped to the list variable in the form `{test::::}`. `{test::}` holds the column name, so `loop {test::*}` iterates over the columns. The list is emptied before the result is stored. `with arguments` binds the given values to the question marks of the statement. `with batch arguments` runs the statement once per sub list of a list variable, all in one round trip and one transaction. If `quickly` is specified, the SQL query will be done without jumping back to the main thread, which saves waiting for the next tick, however that makes code after it to also be on a separate thread, you can jump back to the main thread by adding `wait a tick`. #### Syntax ``` [quickly] execute %string% (in|on) %datasource% [with [batch] arg[ument][s] %-objects%] [and store [the] [generated keys] [(output|result)[s]] (to|in) [the] [var[iable]] %-objects%] ``` #### Examples ``` execute "select * from players" in {sql} and store the result in {output::*} ``` ``` execute "select * from players where name = %{_name}% and score > %{_min}%" in {sql} and store the result in {output::*} ``` ``` execute "select name from players where id in (%{_ids::*}%)" in {sql} and store the result in {output::*} ``` ``` execute "insert into scores (name, score) values (?, ?)" in {sql} with arguments "Steve", 100 and store the generated keys in {_id} ``` ``` set {_rows::1::*} to "Steve", 100 set {_rows::2::*} to "Alex", 250 execute "insert into scores (name, score) values (?, ?)" in {sql} with batch arguments {_rows::*} and store the result in {_count} ``` --- ### Section `Transaction` Runs every statement inside the section on one connection as a single transaction and commits at the end of the section. If a statement fails, everything is rolled back, the rest of the section is skipped and `last sql error` holds the reason. Do not leave the section with `stop`, `exit` or `return`. #### Syntax ``` [(sql|database)] transaction (on|in) %datasource%: ``` #### Examples ``` transaction on {sql}: execute "update accounts set balance = balance - %{_amount}% where id = %{_from}%" in {sql} execute "update accounts set balance = balance + %{_amount}% where id = %{_to}%" in {sql} if last sql error is set: send "Transfer failed: %last sql error%" ``` --- ### Expression `Last Data Source Error` => `text` Stores the error from the last executed statement or transaction of the current trigger, if there was one. #### Syntax ``` [the] [last] (sql|db|data(base|[ ]source)) error ``` --- ### Expression `Unsafe Expression` => `text` Opts out of automatic SQL injection protection for a specific expression in a statement. Needed for table and column names, which cannot be parameters. Never use it with player input. #### Syntax ``` unsafe %text% ``` #### Examples ``` execute "select %unsafe {columns variable}% from %unsafe {table variable}%" in {sql} ``` ``` execute unsafe {fully dynamic query} in {sql} ```