User:Emijrp/Creating your own database for your tool

From Wikitech
Jump to navigation Jump to search

This tutorial explains the steps for creating your own database for your tool. You also can create a database with your personal account (just skip the tool creation steps).

Creating the database

  • Login into tools-dev.wmflabs.org with your personal account:
    • mosh -a yourusername@tools-dev.wmflabs.org
  • Become your tool:
    • become yourtoolname
  • Read the user parameter from your replica.my.cnf. Use it as prefix for your database name:
    • sname1234__YOURDATABASENAME
  • Open a new terminal to upload the schema and write:
    • scp createdatabase.sql yourusername@tools-dev.wmflabs.org:/data/project/yourtoolname
  • Finally, import the database schema:
    • mysql --defaults-file="${HOME}"/replica.my.cnf -h tools-db < createdatabase.sql

Using the database

From shell

  • sql local
  • use s51138__heritage_p;
  • select * from table1 where 1;

From PHP

$dbmycnf = parse_ini_file("../replica.my.cnf");
$dbuser = $dbmycnf['user'];
$dbpass = $dbmycnf['password'];
unset($dbmycnf);
$dbhost = "tools-db";
$dbname = "s51138__heritage_p";

$db = new PDO('mysql:host='.$dbhost.';dbname='.$dbname.';charset=utf8', $dbuser, $dbpass);

See also the old Toolserver help.

Backup your database

Schema

Example of createdatabase.sql file:

CREATE DATABASE IF NOT EXISTS `sname1234__YOURDATABASENAME`;
USE `sname1234__YOURDATABASENAME`;

DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
`param1` int(10) unsigned NOT NULL DEFAULT '0',
`param2` varbinary(255) NOT NULL DEFAULT '',
PRIMARY KEY (`param1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*
INSERT IGNORE INTO `table1` SET `param1` = 1, `param2` = 'Hola';
INSERT IGNORE INTO `table1` SET `param1` = 2, `param2` = 'Adios';
INSERT IGNORE INTO `table1` SET `param1` = 3, `param2` = 'Más cosas';
*/

See also