From eba55f8361fc36179e36bf8a3cea5067b5341e37 Mon Sep 17 00:00:00 2001 From: fusion32 Date: Sat, 18 Oct 2025 18:21:31 -0300 Subject: update documentation + wrap the few SQLite scripts --- README.md | 2 +- config.cfg.dist | 4 +- postgres/README.txt | 142 +++++++++++++++++++++++++++++++++++ postgres/init.sql | 117 ----------------------------- postgres/initial-data.sql | 117 +++++++++++++++++++++++++++++ sqlite/patches/.gitignore | 2 + sqlite/patches/9999-initial-data.sql | 97 ------------------------ sqlite/z-001-migrate-v01-to-v02.sql | 30 ++++++-- sqlite/z-999-initial-data.sql | 97 ++++++++++++++++++++++++ src/querymanager.cc | 4 +- 10 files changed, 386 insertions(+), 226 deletions(-) create mode 100644 postgres/README.txt delete mode 100644 postgres/init.sql create mode 100644 postgres/initial-data.sql create mode 100644 sqlite/patches/.gitignore delete mode 100644 sqlite/patches/9999-initial-data.sql create mode 100644 sqlite/z-999-initial-data.sql diff --git a/README.md b/README.md index a1b3d09..7fdcdb0 100644 --- a/README.md +++ b/README.md @@ -16,7 +16,7 @@ make clean # remove `build` directory ``` ## Running (SQLite) -The query manager becomes the database, automatically initializing and maintaining the schema, based on the files in `sqlite/` (see `sqlite/README.txt`). The default schema file won't automatically insert any initial data (see `sqlite/init.sql`), although you could put some insertions at the end to avoid having to manually run something like `sqlite/init.sql`. There are a few configuration options, and in particular `SQLite.*` options that can be adjusted in `config.cfg` but the defaults should work for most use cases. +The query manager becomes the database, automatically initializing and maintaining the schema, based on the files in `sqlite/` (see `sqlite/README.txt`). The default schema file won't automatically insert any initial data but that can be changed by using a patch (again, see `sqlite/README.txt`). There are a few configuration options, and in particular `SQLite.*` options that can be adjusted in `config.cfg` but the defaults should work for most use cases. ## Running (PostgreSQL) The query manager becomes a relay to the actual database. And with PostgreSQL being a distributed database system, it makes no sense to have individual clients managing the schema, since there could be multiple, each with their own assumptions. For that reason there is a `SchemaInfo` table with a `VERSION` row that will be queried at startup and compared against `POSTGRESQL_SCHEMA_VERSION`, defined in `src/database_postgres.cc`, to make sure there is an agreement on the schema version. It is hardcoded because schema changes will usually result in query changes. diff --git a/config.cfg.dist b/config.cfg.dist index dcf7212..26f7a21 100644 --- a/config.cfg.dist +++ b/config.cfg.dist @@ -11,8 +11,8 @@ SQLite.MaxCachedStatements = 100 # Empty values are ignored, meaning their defaults will be used instead. # For more information see: # https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS -PostgreSQL.Host = "localhost" -PostgreSQL.Port = "5432" +PostgreSQL.Host = "" +PostgreSQL.Port = "" PostgreSQL.DBName = "tibia" PostgreSQL.User = "tibia" PostgreSQL.Password = "" diff --git a/postgres/README.txt b/postgres/README.txt new file mode 100644 index 0000000..a96995b --- /dev/null +++ b/postgres/README.txt @@ -0,0 +1,142 @@ +WARNING: This is not meant to be a complete guide on PostgreSQL, but rather a +"first steps" kind of guide. It'll only cover things on the surface level. For +a deep dive into how the database operates, how to properly configure it, and +ultimately properly administrate it, you MUST refer to the PostgreSQL manual +for your version. The most current version of the manual will describe the most +recent features, but not all features are present in all versions. + If anything, you should absolutely consult the section that regards server +administration "III. Server Administration". + + MANUAL https://www.postgresql.org/docs/current/index.html + +Installation +------------ + PostgreSQL is available in most Linux distributions as a package which is +the preferred way to get it installed. Some will automatically setup a service, +create service users, initialize the database cluster, etc... If not, you might +need to do one or more steps manually. If you're having trouble, most systems +will have specific instructions on how to set everything up. Just as an example +here are a few links for common systems: + DEBIAN https://www.postgresql.org/download/linux/debian/ + REDHAT https://www.postgresql.org/download/linux/redhat/ + SUSE https://www.postgresql.org/download/linux/suse/ + UBUNTU https://www.postgresql.org/download/linux/ubuntu/ + ARCH https://wiki.archlinux.org/title/PostgreSQL + +Configuration +------------- + By default, configuration files will be in the `data` directory which can +change locations depending on how the server was installed but is usually in +`/var/lib/postgres/data`. + All files in the `data` directory are OWNED by the *postgres* SYSTEM user, +meaning you'll only be able to modify them if you're logged in as *postgres*, +by using *sudo* privileges, or both with `sudo su postgres`. + The bulk of the configuration is inside `postgresql.conf` which has multiple +options, but of particular interest are the "CONNECTIONS AND AUTHENTICATION" +options. I won't go over specifics here but if you're planning on accepting +remote connections, you MUST properly configure SSL communication. + Access to the database is controlled with `pg_hba.conf`. This is different +from MySQL where you'd specify users as 'user'@'host' with SQL to restrict +them to certain hosts. Instead you need to specify how certain users/roles +may connect to the database in this file. Properly configuring it is probably +the most important step in securing the database, aside from configuring SSL +communication. + The last file is `pg_ident.conf` which declares mappings from system users +to database users. These mappings alone don't do anything. They must be +explicitly referenced as `map=MAPNAME` in `pg_hba.conf` for supported +authentication methods. + + Here is an example of a `pg_hba.conf` + `pg_ident.conf` local access config. +It'll allow *systemuser* to connect as *postgres* to any database using the +*peer* method which checks the system user name. It'll also allow the *tibia* +user to connect to the *tibia* database using the *scram-sha-256* password +authentication scheme. Local connections will use UNIX-domain sockets and for +that matter you'd leave `PostgreSQL.Host` empty. + +``` +# pg_hba.conf +# TYPE DATABASE USER ADDRESS METHOD +local all postgres peer map=super +local tibia tibia scram-sha-256 + +# pg_ident.conf +# MAPNAME SYSTEM-USERNAME PG-USERNAME +super systemuser postgres +``` + + MANUAL https://www.postgresql.org/docs/current/runtime-config.html + MANUAL https://www.postgresql.org/docs/current/client-authentication.html + +Database Setup +-------------- + It is highly advised to not use a SUPERUSER when connecting to the database +from the query manager, or any other service for that matter. This warrants the +creation of a secondary user that has access, but not administrative privileges. + I figured it would be simpler to have a sequence of *PSQL* commands with their +descriptions. Having a database minimaly ready for the query manager should be +a matter of following this sequence. + + Unless a database is specified, *PSQL* will connect to one with the same name +as the specified user. If the user is not explicitly specified, the system user +name will be used. Running `psql -U postgres` will connect to *postgres* as the +user *postgres*. Note that you can't connect without a database, so you'd connect +to *postgres* in order to create new databases. + +1 - Create and connect to a new database. Note that the `OWNER = postgres` clause +is redundant here but it's just to show that having the database owned by the +super user is intended. +``` +psql -U postgres -c "CREATE DATABASE tibia OWNER = postgres;" +psql -U postgres tibia +``` + +2 - Set default privileges. Newly created databases may have some default PUBLIC +privileges that we'll want to revoke to make sure the set of users that are able +to connect is tighly controlled. Then, for users that are able to connect, we +want to give default access privileges to tables. +``` +REVOKE ALL ON DATABASE tibia FROM PUBLIC; +ALTER DEFAULT PRIVILEGES IN SCHEMA public + GRANT SELECT, INSERT, UPDATE, DELETE + ON TABLES TO PUBLIC; +``` + +3 - Initialize schema. This is done by executing commands from `postgres/schema.sql`, +and optionally `postgres/initial-data.sql`. Note that since we set default privileges +before creating any tables, they should already have the approppriate privileges. +If done the other way around, we'd need to manually update table privileges. +``` +\i postgres/schema.sql +\i postgres/initial-data.sql +``` + +4 - Create secondary user. This is straighforward. Create a user with *LOGIN* +privileges and a *PASSWORD*. Then grant *CONNECT* privileges to the database. +``` +CREATE ROLE tibia WITH LOGIN PASSWORD '********'; +GRANT CONNECT ON DATABASE tibia TO tibia; +``` + + This is just one way. There are probably other, more optimal setups, but +for a small testing bench, it will do. And don't take my word on anything. +You should always check the manual for a complete description on how things +work. + + To wrap, here is a list of helpful commands available in *PSQL*. They'll +show up along with a lot of other commands when running `\?`. +``` +\q # quit +\l # list databases (will show database privileges) +\du # list users (will show user privileges) +\dO # list collations +\dt # list tables +\dv # list views +\ds # list sequences +\di # list indexes +\d NAME # describe table/view/sequence/index +\dp # list privileges +\ddp # list default privileges +``` + + MANUAL https://www.postgresql.org/docs/current/sql-commands.html + diff --git a/postgres/init.sql b/postgres/init.sql deleted file mode 100644 index fbe40c7..0000000 --- a/postgres/init.sql +++ /dev/null @@ -1,117 +0,0 @@ --- NOTE(fusion): This file contains sample initial data. It's wrapped into a --- transaction to avoid errors from leaving the database in some partial state. -BEGIN; - --- 111111/tibia -INSERT INTO Accounts (AccountID, Email, Auth) - VALUES (111111, '@tibia', '\x206699cbc2fae1683118c873d746aa376049cb5923ef0980298bb7acbba527ec9e765668f7a338dffea34acf61a20efb654c1e9c62d35148dba2aeeef8dc7788'); - --- NOTE(fusion): This would generally be a sequence of insertions but the way --- PostgreSQL handles unique auto increment columns makes so manually inserted --- values end up colliding with generated values at some point. --- On a real scenario, we'd never have manually assigned ids unless we're --- restoring some database dump, in which case it would also have saved --- sequences. -WITH - WorldIns AS ( - INSERT INTO Worlds (Name, Type, RebootTime, Host, Port, MaxPlayers, - PremiumPlayerBuffer, MaxNewbies, PremiumNewbieBuffer) - VALUES ('Zanera', 0, 5, 'localhost', 7172, 1000, 100, 300, 100) - RETURNING WorldID, Name - ), - CharacterIns AS ( - INSERT INTO Characters (WorldID, AccountID, Name, Sex) - SELECT W.WorldID, C.AccountID, C.Name, C.Sex - FROM WorldIns AS W, - LATERAL (VALUES - (111111, 'Gamemaster on ' || W.Name, 1), - (111111, 'Player on ' || W.Name, 1) - ) AS C (AccountID, Name, Sex) - RETURNING CharacterID, Name - ) -INSERT INTO CharacterRights(CharacterID, Name) - SELECT C.CharacterID, R.RightName - FROM CharacterIns AS C, - (VALUES - ('NOTATION'), - ('NAMELOCK'), - ('STATEMENT_REPORT'), - ('BANISHMENT'), - ('FINAL_WARNING'), - ('IP_BANISHMENT'), - ('KICK'), - ('HOME_TELEPORT'), - ('GAMEMASTER_BROADCAST'), - ('ANONYMOUS_BROADCAST'), - ('NO_BANISHMENT'), - ('ALLOW_MULTICLIENT'), - ('LOG_COMMUNICATION'), - ('READ_GAMEMASTER_CHANNEL'), - ('READ_TUTOR_CHANNEL'), - ('HIGHLIGHT_HELP_CHANNEL'), - ('SEND_BUGREPORTS'), - ('NAME_INSULTING'), - ('NAME_SENTENCE'), - ('NAME_NONSENSICAL_LETTERS'), - ('NAME_BADLY_FORMATTED'), - ('NAME_NO_PERSON'), - ('NAME_CELEBRITY'), - ('NAME_COUNTRY'), - ('NAME_FAKE_IDENTITY'), - ('NAME_FAKE_POSITION'), - ('STATEMENT_INSULTING'), - ('STATEMENT_SPAMMING'), - ('STATEMENT_ADVERT_OFFTOPIC'), - ('STATEMENT_ADVERT_MONEY'), - ('STATEMENT_NON_ENGLISH'), - ('STATEMENT_CHANNEL_OFFTOPIC'), - ('STATEMENT_VIOLATION_INCITING'), - ('CHEATING_BUG_ABUSE'), - ('CHEATING_GAME_WEAKNESS'), - ('CHEATING_MACRO_USE'), - ('CHEATING_MODIFIED_CLIENT'), - ('CHEATING_HACKING'), - ('CHEATING_MULTI_CLIENT'), - ('CHEATING_ACCOUNT_TRADING'), - ('CHEATING_ACCOUNT_SHARING'), - ('GAMEMASTER_THREATENING'), - ('GAMEMASTER_PRETENDING'), - ('GAMEMASTER_INFLUENCE'), - ('GAMEMASTER_FALSE_REPORTS'), - ('KILLING_EXCESSIVE_UNJUSTIFIED'), - ('DESTRUCTIVE_BEHAVIOUR'), - ('SPOILING_AUCTION'), - ('INVALID_PAYMENT'), - ('TELEPORT_TO_CHARACTER'), - ('TELEPORT_TO_MARK'), - ('TELEPORT_VERTICAL'), - ('TELEPORT_TO_COORDINATE'), - ('LEVITATE'), - ('SPECIAL_MOVEUSE'), - ('MODIFY_GOSTRENGTH'), - ('SHOW_COORDINATE'), - ('RETRIEVE'), - ('ENTER_HOUSES'), - ('OPEN_NAMEDOORS'), - ('INVULNERABLE'), - ('UNLIMITED_MANA'), - ('KEEP_INVENTORY'), - ('ALL_SPELLS'), - ('UNLIMITED_CAPACITY'), - ('ATTACK_EVERYWHERE'), - ('NO_LOGOUT_BLOCK'), - ('GAMEMASTER_OUTFIT'), - ('ILLUMINATE'), - ('CHANGE_PROFESSION'), - ('IGNORED_BY_MONSTERS'), - ('SHOW_KEYHOLE_NUMBERS'), - ('CREATE_OBJECTS'), - ('CREATE_MONEY'), - ('CREATE_MONSTERS'), - ('CHANGE_SKILLS'), - ('CLEANUP_FIELDS'), - ('NO_STATISTICS') - ) AS R (RightName) - WHERE C.Name LIKE 'Gamemaster%' COLLATE "default"; - -COMMIT; diff --git a/postgres/initial-data.sql b/postgres/initial-data.sql new file mode 100644 index 0000000..fbe40c7 --- /dev/null +++ b/postgres/initial-data.sql @@ -0,0 +1,117 @@ +-- NOTE(fusion): This file contains sample initial data. It's wrapped into a +-- transaction to avoid errors from leaving the database in some partial state. +BEGIN; + +-- 111111/tibia +INSERT INTO Accounts (AccountID, Email, Auth) + VALUES (111111, '@tibia', '\x206699cbc2fae1683118c873d746aa376049cb5923ef0980298bb7acbba527ec9e765668f7a338dffea34acf61a20efb654c1e9c62d35148dba2aeeef8dc7788'); + +-- NOTE(fusion): This would generally be a sequence of insertions but the way +-- PostgreSQL handles unique auto increment columns makes so manually inserted +-- values end up colliding with generated values at some point. +-- On a real scenario, we'd never have manually assigned ids unless we're +-- restoring some database dump, in which case it would also have saved +-- sequences. +WITH + WorldIns AS ( + INSERT INTO Worlds (Name, Type, RebootTime, Host, Port, MaxPlayers, + PremiumPlayerBuffer, MaxNewbies, PremiumNewbieBuffer) + VALUES ('Zanera', 0, 5, 'localhost', 7172, 1000, 100, 300, 100) + RETURNING WorldID, Name + ), + CharacterIns AS ( + INSERT INTO Characters (WorldID, AccountID, Name, Sex) + SELECT W.WorldID, C.AccountID, C.Name, C.Sex + FROM WorldIns AS W, + LATERAL (VALUES + (111111, 'Gamemaster on ' || W.Name, 1), + (111111, 'Player on ' || W.Name, 1) + ) AS C (AccountID, Name, Sex) + RETURNING CharacterID, Name + ) +INSERT INTO CharacterRights(CharacterID, Name) + SELECT C.CharacterID, R.RightName + FROM CharacterIns AS C, + (VALUES + ('NOTATION'), + ('NAMELOCK'), + ('STATEMENT_REPORT'), + ('BANISHMENT'), + ('FINAL_WARNING'), + ('IP_BANISHMENT'), + ('KICK'), + ('HOME_TELEPORT'), + ('GAMEMASTER_BROADCAST'), + ('ANONYMOUS_BROADCAST'), + ('NO_BANISHMENT'), + ('ALLOW_MULTICLIENT'), + ('LOG_COMMUNICATION'), + ('READ_GAMEMASTER_CHANNEL'), + ('READ_TUTOR_CHANNEL'), + ('HIGHLIGHT_HELP_CHANNEL'), + ('SEND_BUGREPORTS'), + ('NAME_INSULTING'), + ('NAME_SENTENCE'), + ('NAME_NONSENSICAL_LETTERS'), + ('NAME_BADLY_FORMATTED'), + ('NAME_NO_PERSON'), + ('NAME_CELEBRITY'), + ('NAME_COUNTRY'), + ('NAME_FAKE_IDENTITY'), + ('NAME_FAKE_POSITION'), + ('STATEMENT_INSULTING'), + ('STATEMENT_SPAMMING'), + ('STATEMENT_ADVERT_OFFTOPIC'), + ('STATEMENT_ADVERT_MONEY'), + ('STATEMENT_NON_ENGLISH'), + ('STATEMENT_CHANNEL_OFFTOPIC'), + ('STATEMENT_VIOLATION_INCITING'), + ('CHEATING_BUG_ABUSE'), + ('CHEATING_GAME_WEAKNESS'), + ('CHEATING_MACRO_USE'), + ('CHEATING_MODIFIED_CLIENT'), + ('CHEATING_HACKING'), + ('CHEATING_MULTI_CLIENT'), + ('CHEATING_ACCOUNT_TRADING'), + ('CHEATING_ACCOUNT_SHARING'), + ('GAMEMASTER_THREATENING'), + ('GAMEMASTER_PRETENDING'), + ('GAMEMASTER_INFLUENCE'), + ('GAMEMASTER_FALSE_REPORTS'), + ('KILLING_EXCESSIVE_UNJUSTIFIED'), + ('DESTRUCTIVE_BEHAVIOUR'), + ('SPOILING_AUCTION'), + ('INVALID_PAYMENT'), + ('TELEPORT_TO_CHARACTER'), + ('TELEPORT_TO_MARK'), + ('TELEPORT_VERTICAL'), + ('TELEPORT_TO_COORDINATE'), + ('LEVITATE'), + ('SPECIAL_MOVEUSE'), + ('MODIFY_GOSTRENGTH'), + ('SHOW_COORDINATE'), + ('RETRIEVE'), + ('ENTER_HOUSES'), + ('OPEN_NAMEDOORS'), + ('INVULNERABLE'), + ('UNLIMITED_MANA'), + ('KEEP_INVENTORY'), + ('ALL_SPELLS'), + ('UNLIMITED_CAPACITY'), + ('ATTACK_EVERYWHERE'), + ('NO_LOGOUT_BLOCK'), + ('GAMEMASTER_OUTFIT'), + ('ILLUMINATE'), + ('CHANGE_PROFESSION'), + ('IGNORED_BY_MONSTERS'), + ('SHOW_KEYHOLE_NUMBERS'), + ('CREATE_OBJECTS'), + ('CREATE_MONEY'), + ('CREATE_MONSTERS'), + ('CHANGE_SKILLS'), + ('CLEANUP_FIELDS'), + ('NO_STATISTICS') + ) AS R (RightName) + WHERE C.Name LIKE 'Gamemaster%' COLLATE "default"; + +COMMIT; diff --git a/sqlite/patches/.gitignore b/sqlite/patches/.gitignore new file mode 100644 index 0000000..d6b7ef3 --- /dev/null +++ b/sqlite/patches/.gitignore @@ -0,0 +1,2 @@ +* +!.gitignore diff --git a/sqlite/patches/9999-initial-data.sql b/sqlite/patches/9999-initial-data.sql deleted file mode 100644 index 53d92f2..0000000 --- a/sqlite/patches/9999-initial-data.sql +++ /dev/null @@ -1,97 +0,0 @@ --- NOTE(fusion): This file contains sample initial data and will be executed --- automatically as a patch by the query manager. See `sqlite/README.txt` for --- more details. ---============================================================================== - -INSERT INTO Worlds (WorldID, Name, Type, RebootTime, Host, Port, MaxPlayers, - PremiumPlayerBuffer, MaxNewbies, PremiumNewbieBuffer) - VALUES (1, 'Zanera', 0, 5, 'localhost', 7172, 1000, 100, 300, 100); - --- 111111/tibia -INSERT INTO Accounts (AccountID, Email, Auth) - VALUES (111111, '@tibia', X'206699cbc2fae1683118c873d746aa376049cb5923ef0980298bb7acbba527ec9e765668f7a338dffea34acf61a20efb654c1e9c62d35148dba2aeeef8dc7788'); - -INSERT INTO Characters (WorldID, CharacterID, AccountID, Name, Sex) - VALUES (1, 1, 111111, 'Gamemaster', 1), (1, 2, 111111, 'Player', 1); - -INSERT INTO CharacterRights (CharacterID, Name) - VALUES - (1, 'NOTATION'), - (1, 'NAMELOCK'), - (1, 'STATEMENT_REPORT'), - (1, 'BANISHMENT'), - (1, 'FINAL_WARNING'), - (1, 'IP_BANISHMENT'), - (1, 'KICK'), - (1, 'HOME_TELEPORT'), - (1, 'GAMEMASTER_BROADCAST'), - (1, 'ANONYMOUS_BROADCAST'), - (1, 'NO_BANISHMENT'), - (1, 'ALLOW_MULTICLIENT'), - (1, 'LOG_COMMUNICATION'), - (1, 'READ_GAMEMASTER_CHANNEL'), - (1, 'READ_TUTOR_CHANNEL'), - (1, 'HIGHLIGHT_HELP_CHANNEL'), - (1, 'SEND_BUGREPORTS'), - (1, 'NAME_INSULTING'), - (1, 'NAME_SENTENCE'), - (1, 'NAME_NONSENSICAL_LETTERS'), - (1, 'NAME_BADLY_FORMATTED'), - (1, 'NAME_NO_PERSON'), - (1, 'NAME_CELEBRITY'), - (1, 'NAME_COUNTRY'), - (1, 'NAME_FAKE_IDENTITY'), - (1, 'NAME_FAKE_POSITION'), - (1, 'STATEMENT_INSULTING'), - (1, 'STATEMENT_SPAMMING'), - (1, 'STATEMENT_ADVERT_OFFTOPIC'), - (1, 'STATEMENT_ADVERT_MONEY'), - (1, 'STATEMENT_NON_ENGLISH'), - (1, 'STATEMENT_CHANNEL_OFFTOPIC'), - (1, 'STATEMENT_VIOLATION_INCITING'), - (1, 'CHEATING_BUG_ABUSE'), - (1, 'CHEATING_GAME_WEAKNESS'), - (1, 'CHEATING_MACRO_USE'), - (1, 'CHEATING_MODIFIED_CLIENT'), - (1, 'CHEATING_HACKING'), - (1, 'CHEATING_MULTI_CLIENT'), - (1, 'CHEATING_ACCOUNT_TRADING'), - (1, 'CHEATING_ACCOUNT_SHARING'), - (1, 'GAMEMASTER_THREATENING'), - (1, 'GAMEMASTER_PRETENDING'), - (1, 'GAMEMASTER_INFLUENCE'), - (1, 'GAMEMASTER_FALSE_REPORTS'), - (1, 'KILLING_EXCESSIVE_UNJUSTIFIED'), - (1, 'DESTRUCTIVE_BEHAVIOUR'), - (1, 'SPOILING_AUCTION'), - (1, 'INVALID_PAYMENT'), - (1, 'TELEPORT_TO_CHARACTER'), - (1, 'TELEPORT_TO_MARK'), - (1, 'TELEPORT_VERTICAL'), - (1, 'TELEPORT_TO_COORDINATE'), - (1, 'LEVITATE'), - (1, 'SPECIAL_MOVEUSE'), - (1, 'MODIFY_GOSTRENGTH'), - (1, 'SHOW_COORDINATE'), - (1, 'RETRIEVE'), - (1, 'ENTER_HOUSES'), - (1, 'OPEN_NAMEDOORS'), - (1, 'INVULNERABLE'), - (1, 'UNLIMITED_MANA'), - (1, 'KEEP_INVENTORY'), - (1, 'ALL_SPELLS'), - (1, 'UNLIMITED_CAPACITY'), - (1, 'ATTACK_EVERYWHERE'), - (1, 'NO_LOGOUT_BLOCK'), - (1, 'GAMEMASTER_OUTFIT'), - (1, 'ILLUMINATE'), - (1, 'CHANGE_PROFESSION'), - (1, 'IGNORED_BY_MONSTERS'), - (1, 'SHOW_KEYHOLE_NUMBERS'), - (1, 'CREATE_OBJECTS'), - (1, 'CREATE_MONEY'), - (1, 'CREATE_MONSTERS'), - (1, 'CHANGE_SKILLS'), - (1, 'CLEANUP_FIELDS'), - (1, 'NO_STATISTICS'); - diff --git a/sqlite/z-001-migrate-v01-to-v02.sql b/sqlite/z-001-migrate-v01-to-v02.sql index b7df053..f471f53 100644 --- a/sqlite/z-001-migrate-v01-to-v02.sql +++ b/sqlite/z-001-migrate-v01-to-v02.sql @@ -1,14 +1,30 @@ -- NOTE(fusion): This file contains the migration script from v0.1 to v0.2. It --- can be placed into `patches/` to upgrade an existing database. Note that these --- changes are already present in the latest `schema.sql`, so trying to patch a --- newly created database will probably result in errors. See `sqlite/README.txt` --- for more details. +-- must be manually executed as `sqlite3 -bail -echo tibia.db < migration.sql` +-- because the original schema didn't have a `Patches` table which is necessary +-- with the new automatic patching system. Future migration scripts can be placed +-- in `patches/` for automatic execution but not this one unfortunately. +-- These changes are already present in the latest `schema.sql`, so trying to +-- apply it to a newly created database will result in errors. For more details +-- see `sqlite/README.txt`. --============================================================================== -ALTER TABLE Worlds RENAME COLUMN OnlineRecord TO OnlinePeak; -ALTER TABLE Worlds RENAME COLUMN OnlineRecordTimestamp TO OnlinePeakTimestamp; -ALTER TABLE CharacterRights RENAME COLUMN Right TO Name; +BEGIN; + +PRAGMA application_id = 0x54694442; + +PRAGMA user_version = 1; +CREATE TABLE Patches ( + FileName TEXT NOT NULL COLLATE NOCASE, + Timestamp INTEGER NOT NULL, + UNIQUE (FileName) +); + +ALTER TABLE Worlds RENAME COLUMN OnlineRecord TO OnlinePeak; +ALTER TABLE Worlds RENAME COLUMN OnlineRecordTimestamp TO OnlinePeakTimestamp; ALTER TABLE Worlds ADD COLUMN LastStartup INTEGER NOT NULL DEFAULT 0; ALTER TABLE Worlds ADD COLUMN LastShutdown INTEGER NOT NULL DEFAULT 0; +ALTER TABLE CharacterRights RENAME COLUMN Right TO Name; + +COMMIT; diff --git a/sqlite/z-999-initial-data.sql b/sqlite/z-999-initial-data.sql new file mode 100644 index 0000000..d20fee0 --- /dev/null +++ b/sqlite/z-999-initial-data.sql @@ -0,0 +1,97 @@ +-- NOTE(fusion): This file contains sample initial data and can be executed +-- automatically as a patch by the query manager if placed at `sqlite/patches`. +-- See `sqlite/README.txt` for more details. +--============================================================================== + +INSERT INTO Worlds (WorldID, Name, Type, RebootTime, Host, Port, MaxPlayers, + PremiumPlayerBuffer, MaxNewbies, PremiumNewbieBuffer) + VALUES (1, 'Zanera', 0, 5, 'localhost', 7172, 1000, 100, 300, 100); + +-- 111111/tibia +INSERT INTO Accounts (AccountID, Email, Auth) + VALUES (111111, '@tibia', X'206699cbc2fae1683118c873d746aa376049cb5923ef0980298bb7acbba527ec9e765668f7a338dffea34acf61a20efb654c1e9c62d35148dba2aeeef8dc7788'); + +INSERT INTO Characters (WorldID, CharacterID, AccountID, Name, Sex) + VALUES (1, 1, 111111, 'Gamemaster', 1), (1, 2, 111111, 'Player', 1); + +INSERT INTO CharacterRights (CharacterID, Name) + VALUES + (1, 'NOTATION'), + (1, 'NAMELOCK'), + (1, 'STATEMENT_REPORT'), + (1, 'BANISHMENT'), + (1, 'FINAL_WARNING'), + (1, 'IP_BANISHMENT'), + (1, 'KICK'), + (1, 'HOME_TELEPORT'), + (1, 'GAMEMASTER_BROADCAST'), + (1, 'ANONYMOUS_BROADCAST'), + (1, 'NO_BANISHMENT'), + (1, 'ALLOW_MULTICLIENT'), + (1, 'LOG_COMMUNICATION'), + (1, 'READ_GAMEMASTER_CHANNEL'), + (1, 'READ_TUTOR_CHANNEL'), + (1, 'HIGHLIGHT_HELP_CHANNEL'), + (1, 'SEND_BUGREPORTS'), + (1, 'NAME_INSULTING'), + (1, 'NAME_SENTENCE'), + (1, 'NAME_NONSENSICAL_LETTERS'), + (1, 'NAME_BADLY_FORMATTED'), + (1, 'NAME_NO_PERSON'), + (1, 'NAME_CELEBRITY'), + (1, 'NAME_COUNTRY'), + (1, 'NAME_FAKE_IDENTITY'), + (1, 'NAME_FAKE_POSITION'), + (1, 'STATEMENT_INSULTING'), + (1, 'STATEMENT_SPAMMING'), + (1, 'STATEMENT_ADVERT_OFFTOPIC'), + (1, 'STATEMENT_ADVERT_MONEY'), + (1, 'STATEMENT_NON_ENGLISH'), + (1, 'STATEMENT_CHANNEL_OFFTOPIC'), + (1, 'STATEMENT_VIOLATION_INCITING'), + (1, 'CHEATING_BUG_ABUSE'), + (1, 'CHEATING_GAME_WEAKNESS'), + (1, 'CHEATING_MACRO_USE'), + (1, 'CHEATING_MODIFIED_CLIENT'), + (1, 'CHEATING_HACKING'), + (1, 'CHEATING_MULTI_CLIENT'), + (1, 'CHEATING_ACCOUNT_TRADING'), + (1, 'CHEATING_ACCOUNT_SHARING'), + (1, 'GAMEMASTER_THREATENING'), + (1, 'GAMEMASTER_PRETENDING'), + (1, 'GAMEMASTER_INFLUENCE'), + (1, 'GAMEMASTER_FALSE_REPORTS'), + (1, 'KILLING_EXCESSIVE_UNJUSTIFIED'), + (1, 'DESTRUCTIVE_BEHAVIOUR'), + (1, 'SPOILING_AUCTION'), + (1, 'INVALID_PAYMENT'), + (1, 'TELEPORT_TO_CHARACTER'), + (1, 'TELEPORT_TO_MARK'), + (1, 'TELEPORT_VERTICAL'), + (1, 'TELEPORT_TO_COORDINATE'), + (1, 'LEVITATE'), + (1, 'SPECIAL_MOVEUSE'), + (1, 'MODIFY_GOSTRENGTH'), + (1, 'SHOW_COORDINATE'), + (1, 'RETRIEVE'), + (1, 'ENTER_HOUSES'), + (1, 'OPEN_NAMEDOORS'), + (1, 'INVULNERABLE'), + (1, 'UNLIMITED_MANA'), + (1, 'KEEP_INVENTORY'), + (1, 'ALL_SPELLS'), + (1, 'UNLIMITED_CAPACITY'), + (1, 'ATTACK_EVERYWHERE'), + (1, 'NO_LOGOUT_BLOCK'), + (1, 'GAMEMASTER_OUTFIT'), + (1, 'ILLUMINATE'), + (1, 'CHANGE_PROFESSION'), + (1, 'IGNORED_BY_MONSTERS'), + (1, 'SHOW_KEYHOLE_NUMBERS'), + (1, 'CREATE_OBJECTS'), + (1, 'CREATE_MONEY'), + (1, 'CREATE_MONSTERS'), + (1, 'CHANGE_SKILLS'), + (1, 'CLEANUP_FIELDS'), + (1, 'NO_STATISTICS'); + diff --git a/src/querymanager.cc b/src/querymanager.cc index 56df1f8..bac3c74 100644 --- a/src/querymanager.cc +++ b/src/querymanager.cc @@ -780,8 +780,8 @@ int main(int argc, const char **argv){ g_Config.SQLite.MaxCachedStatements = 100; // PostgreSQL Config - StringBufCopy(g_Config.PostgreSQL.Host, "localhost"); - StringBufCopy(g_Config.PostgreSQL.Port, "5432"); + StringBufCopy(g_Config.PostgreSQL.Host, ""); + StringBufCopy(g_Config.PostgreSQL.Port, ""); StringBufCopy(g_Config.PostgreSQL.DBName, "tibia"); StringBufCopy(g_Config.PostgreSQL.User, "tibia"); StringBufCopy(g_Config.PostgreSQL.Password, ""); -- cgit v1.2.3