User:Emijrp/Creating your own database for your tool

From Wikitech

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

Retrieved from "https://wikitech.wikimedia.org/w/index.php?title=User:Emijrp/Creating_your_own_database_for_your_tool&oldid=1875236"