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::<column name>::<row number>}. {test::<column name>} 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%:
    <statements>

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}