mirror of
https://github.com/pennmush/pennmush
synced 2026-08-12 22:23:05 -04:00
156 lines
11 KiB
HTML
156 lines
11 KiB
HTML
<!DOCTYPE html>
|
|
<html xmlns="http://www.w3.org/1999/xhtml" lang="" xml:lang="">
|
|
<head>
|
|
<meta charset="utf-8" />
|
|
<meta name="generator" content="pandoc" />
|
|
<meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=yes" />
|
|
<meta name="author" content="" />
|
|
<title>Use SQL with PennMUSH</title>
|
|
<style type="text/css">
|
|
code{white-space: pre-wrap;}
|
|
span.smallcaps{font-variant: small-caps;}
|
|
span.underline{text-decoration: underline;}
|
|
div.column{display: inline-block; vertical-align: top; width: 50%;}
|
|
</style>
|
|
<link rel="stylesheet" href="mushdoc.css">
|
|
<!--[if lt IE 9]>
|
|
<script src="//cdnjs.cloudflare.com/ajax/libs/html5shiv/3.7.3/html5shiv-printshiv.min.js"></script>
|
|
<![endif]-->
|
|
</head>
|
|
<body>
|
|
<header>
|
|
<h1 class="title">Use SQL with PennMUSH</h1>
|
|
<p class="author"></p>
|
|
<p class="date">Revised: 04 Jan 2018</p>
|
|
</header>
|
|
<nav id="TOC">
|
|
<ul>
|
|
<li><a href="#introduction">Introduction</a></li>
|
|
<li><a href="#how-sql-can-be-used">How SQL can be used</a></li>
|
|
<li><a href="#compiling-withwithout-sql">Compiling with/without SQL</a><ul>
|
|
<li><a href="#mysql">MySQL</a></li>
|
|
<li><a href="#postgresql">PostgresQL</a></li>
|
|
<li><a href="#sqlite">Sqlite</a></li>
|
|
</ul></li>
|
|
<li><a href="#mush-configuration-overview">MUSH configuration overview</a><ul>
|
|
<li><a href="#sql-setup-tips">SQL setup tips</a></li>
|
|
</ul></li>
|
|
</ul>
|
|
</nav>
|
|
<h1 id="introduction">Introduction</h1>
|
|
<p>As of version 1.7.7p32, PennMUSH includes functions and commands that can access SQL databases. Currently, the following databases are supported:</p>
|
|
<ul>
|
|
<li>MySQL/MariaDB</li>
|
|
<li>PostgresQL (from 1.8.3p2)</li>
|
|
<li>Sqlite v3 (from 1.8.3p2)</li>
|
|
</ul>
|
|
<p>This document explains how to use (or avoid) SQL with PennMUSH, and covers the following issues:</p>
|
|
<h1 id="how-sql-can-be-used">How SQL can be used</h1>
|
|
<p>Penn doesn't use SQL for any storage internally. It just allows access to a database via softcode functions (See <code>help @sql</code> and <code>help sql functions</code> from in a game.) People have used it to hold information that can be accessed from a game and from other programs -- for example, a mail or bulletin board system with an in-game and a web interface. Another common use is to hold data that can be queried via SQL more convienently than in softcode.</p>
|
|
<p>A word of caution: The sql functions are synchronous: Nothing else can happen in the game until a query finishes. Complex queries over large tables, or queries to a SQL server on a different host computer can lag the mush for everyone!</p>
|
|
<h1 id="compiling-withwithout-sql">Compiling with/without SQL</h1>
|
|
<p>In general, configure attempts to detect all supported sql client libraries on the host, and will link with all of them, permitting you to select which platform you want at runtime. You can selectively prevent linking with client libraries that are present on your system, by passing configure a <code>--without-FOO</code> option. <code>--disable-sql</code> turns off all checks for SQL servers.</p>
|
|
<p>For example, if your host has MySQL and Sqlite and you only want to use the latter, you can invoke configure like so:</p>
|
|
<pre><code>% ./configure --without-mysql --with-sqlite3</code></pre>
|
|
<h2 id="mysql">MySQL</h2>
|
|
<p>The configure script distributed with PennMUSH automatically detects the MySQL client library via the presence of the <code>mysql_config</code> program, which will tell configure where needed headers and libraries are.</p>
|
|
<p>If you want to avoid linking these libraries on systems where they are present, pass the <code>--without-mysql</code> switch to configure.</p>
|
|
<p>If you installed MySQL from a binary package (e.g. rpm or apt), you should be sure that your system also has the development package (usually mysql-dev, mysql-devel or libmysqlclient-dev).</p>
|
|
<p>If you think you have MySQL libraries and header files but configure isn't finding them, they may be in an unusual location on your system, with <code>mysql_config</code> not in your default path. Find its location (<code>where mysql_config</code> if the program works for you, <code>find / -name mysql_config</code> otherwise), and call configure with <code>--with-mysql=/path/to/mysql_config</code></p>
|
|
<p>All this also applies to MariaDB.</p>
|
|
<h2 id="postgresql">PostgresQL</h2>
|
|
<p>The configure script distributed with PennMUSH automatically detects the PostgresQL client library via the pg_conf program, which will tell configure where needed headers and libraries are.</p>
|
|
<p>If you want to avoid linking these libraries on systems where they are present, pass the <code>--without-postgresql</code> switch to configure.</p>
|
|
<p>If you installed PostgresQL from a binary package (e.g. rpm or apt), you should be sure that your system also has the development package (usually postgresql-dev, or libpq or similar)</p>
|
|
<p>If you think you have PostgresQL libraries and header files but configure isn't finding them, they may be in an unusual location on your system, with <code>pg_config</code> not in your default path. Find its location (<code>where pg_config</code> if the program works for you, <code>find / -name pg_config</code> otherwise), and call configure with <code>--with-postgresql=/path/to/pg_config</code></p>
|
|
<h2 id="sqlite">Sqlite</h2>
|
|
<p>The configure script distributed with PennMUSH looks for sqlite3 headers and libraries in /usr, /usr/local and /opt. If it's somewhere else on your system, call configure with <code>--with-sqlite3=/path/to/sqlite3</code></p>
|
|
<p>If you want to avoid linking with sqlite on systems where it is present, pass the <code>--without-sqlite3</code> switch to configure.</p>
|
|
<p>The path is the directory that contains the hdrs/ and lib/ directories that hold the sqlite3 headers and library respectively.</p>
|
|
<h1 id="mush-configuration-overview">MUSH configuration overview</h1>
|
|
<p>mush.cnf includes these directives that configure the SQL support:</p>
|
|
<dl>
|
|
<dt><code>sql_platform</code></dt>
|
|
<dd><p>Provides the name of the SQL database server software that will be used for connections. It currently takes one of four values: "disabled" (no SQL), "mysql", "postgresql", or "sqlite3". If not specified, it defaults to disabled.</p>
|
|
</dd>
|
|
<dt><code>sql_host</code></dt>
|
|
<dd><p>The name of the host running the SQL server. It defaults to 127.0.0.1, which makes a TCP connection to the local host. For MySQL, the keyword "localhost" instead makes a domain socket (Unix) or named pipe (Windows) connection. For PostgreSQL on Unix, if a path is specified (e.g. /var/run/postgresql), PennMUSH will use the domain socket connection found in that directory. You can also specify an alternate port by setting sql_host to hostname:port (e.g. 127.0.0.1:5444).</p>
|
|
</dd>
|
|
<dt><code>sql_database</code></dt>
|
|
<dd><p>The name of the database that contains the MUSH's tables. This must be specified and there is no default.</p>
|
|
</dd>
|
|
<dt><code>sql_username</code></dt>
|
|
<dd><p>The username to connect to the SQL server with. If not specified, a null username will be used, which many SQL servers treat as "the user running this (pennmush) process".</p>
|
|
</dd>
|
|
<dt><code>sql_password</code></dt>
|
|
<dd><p>provides the password for the user. It defaults to no password.</p>
|
|
</dd>
|
|
</dl>
|
|
<p>For sqlite3, which uses a local file instead of connecting to a database server, <code>sql_database</code> gives the name of the database file, <code>sql_host</code> must be localhost, and the username and password are currently ignored.</p>
|
|
<h2 id="sql-setup-tips">SQL setup tips</h2>
|
|
<p>You will have to set up the appropriate database on the SQL server, and username permitted to perform operations in that database, and a password for that username. This is a platform-specific process.</p>
|
|
<p>If you're not sure how to configure a SQL server, but want to use SQL from softcode, using the Sqlite3 library is the easiest route, as it doesn't require a seperate server to be up and running.</p>
|
|
<h3 id="mysql-platform">MySQL platform</h3>
|
|
<p>Easiest way is:</p>
|
|
<pre><code>% mysql_setpermission --user root REQUIRED
|
|
--host <mysql host> --port <mysql port> OPTIONAL, OR:
|
|
--socket <unix domain socket> OPTIONAL
|
|
|
|
######################################################################
|
|
## Welcome to the permission setter 1.2 for MySQL.
|
|
## made by Luuk de Boer
|
|
######################################################################
|
|
What would you like to do:
|
|
1. Set password for a user.
|
|
2. Add a database + user privilege for that database.
|
|
- user can do all except all admin functions
|
|
3. Add user privilege for an existing database.
|
|
- user can do all except all admin functions
|
|
4. Add user privilege for an existing database.
|
|
- user can do all except all admin functions + no create/drop
|
|
5. Add user privilege for an existing database.
|
|
- user can do only selects (no update/delete/insert etc.)
|
|
0. exit this program
|
|
|
|
Make your choice [1,2,3,4,5,0]: 2 <==========
|
|
|
|
Which database would you like to add: mush
|
|
The new database mush will be created
|
|
|
|
What username is to be created: mush <==========
|
|
Username = mush
|
|
Would you like to set a password for [y/n]: y <==========
|
|
What password do you want to specify for : <==========
|
|
Type the password again: <==========
|
|
We now need to know from what host(s) the user will connect.
|
|
Keep in mind that % means 'from any host' ...
|
|
The host please: localhost <==========
|
|
Would you like to add another host [yes/no]: no <==========
|
|
Okay we keep it with this ...
|
|
The following host(s) will be used: localhost.
|
|
######################################################################
|
|
|
|
That was it ... here is an overview of what you gave to me:
|
|
The database name : mush
|
|
The username : mush
|
|
The host(s) : localhost
|
|
######################################################################
|
|
|
|
Are you pretty sure you would like to implement this [yes/no]: yes</code></pre>
|
|
<h3 id="postgresql-1">PostgreSQL</h3>
|
|
<pre><code>% sudo -u postgres psql
|
|
postgres=# CREATE USER mush WITH PASSWORD 'mush';
|
|
CREATE ROLE
|
|
postgres=# CREATE DATABASE mush OWNER musher;
|
|
CREATE DATABASE
|
|
postgres=# \q</code></pre>
|
|
<p>If you are using domain sockets on Unix and don't need to use password authentication, simply omit "WITH PASSWORD 'mush'".</p>
|
|
<h3 id="sqlite3">Sqlite3:</h3>
|
|
<p>As the same user account that runs the mush:</p>
|
|
<p>Edit mush.cnf so that <code>sql_database</code> is set to data/mush.db and <code>sql_platform</code> is set to sqlite3.</p>
|
|
<p>Then:</p>
|
|
<pre><code>% cd pennmush/game/data
|
|
% sqlite3 mush.db
|
|
> create table ....</code></pre>
|
|
</body>
|
|
</html>
|