Эксперименты

Учёт логинов в MySQL базе данных

2011.08.06

Иногда связок логин/пароль становится так много, что даже записанные на отдельной бумажке (в отдельном текстовом файле) они занимают больше экрана места. Вчера меня это достало и я воспользовался тем, что на моей машине работает MySQL сервер. Сегодня мы с вами делаем простую систему для учёта учётных записей в MySQL базе данных.

Создание базы данных и таблицы логинов

    CREATE DATABASE my DEFAULT CHARACTER SET UTF8;
    USE my;
    CREATE TABLE logins (
      id INT UNIQUE AUTO_INCREMENT PRIMARY KEY,
      hostname VARCHAR(255) CHARACTER SET UTF8,
      type VARCHAR(255),
      address VARCHAR(255),
      login VARCHAR(255) CHARACTER SET UTF8,
      password VARCHAR(255) CHARACTER SET UTF8,
      alias VARCHAR(255) CHARACTER SET UTF8
    );

Теперь у нас есть база данных my (подошла бы любая, но я решил создать отдельную для подобных игр с SQL). В этой базе есть таблица logins, поля которой имеют следующее назначение (id я пропускаю, такое поле должно быть в любой таблице).

hostname:
Адрес сервера, к которому относится логин. Лично я для простоты пишу без http://, и указываю https:// перед адресом только если подключение явно осуществляется по HTTPS
type:
Тип подключения. В моей ментальной модели учётных записей существуют следующие типы подключений:
- http:
  Подключение к веб-сайту через браузер, обычно такие пароли — от админских панелей веб-сайтов
- ftp:
  FTP подключение.
- panel:
  Это подключения к админской панели учётной записи на хостинге.
- mysql:
  Пароли от MySQL серверов напрямую
- phpmyadmin:
  Подключение к phpMyAdmin. Бывает, что напрямую подключение к MySQL серверу запрещено, а взаимодействие с БД осуществляется через интерфейс phpMyAdmin. В таком случае получается, что логины/пароли — от базы данных, а адрес относится к Сети, а не к Интернету. :)
address:
Адрес на сервере [hostname], который относится к учётной записи. Я пишу сюда путь до админской панели в случае [type] == "http", и название каталога на FTP сервере в случае [type] == "ftp". Для [type] == "mysql" и [type] == "phpmyadmin" сюда можно писать название базы данных. В общем, полезное поле.\
alias:
Вряд ли вы веб-сайты, к которым у вас есть учётные записи, называете по [hostname]. Это поле содержит запоминающееся название этого веб-сайта. Оно очень важно, потому что поиск, который мы сделаем в сделующем разделе, будет искать по альясам.

Поиск по базе

В целом, на этом можно было бы поставить точку, потому что теперь с помощью обычных SQL запросов можно делать со своим списком логинов всё, что заблагорассудится. Добавление в таблицу новой учётной записи делается так:

    INSERT INTO logins (alias, hostname, type, login, password, address)
      VALUES ('Рога и Копыта', 'kopyt.xx', 'http', 'batras', 'TOPSECRET', '/bx/admpanel');

Извлечение из таблицы сведений делается, например, так:

    SELECT type, hostname, login, password, address FROM logins
      WHERE alias like '%Копыт%';

Однако, это, конечно, ужасно. Я всегда ищу по альясам, а типов не так уж и много, переживу, если СУБД мне выдаст не одну строчку, а пять. Поэтому воспользуемся PL-SQL и создадим процедуру, которая уменьшит требуемое количество набираемых символов.

    DELIMITER //
    CREATE PROCEDURE find_login(IN alias_str VARCHAR(255))
    BEGIN
      SELECT type, hostname, login, password, address FROM logins
        WHERE alias LIKE CONCAT('%', alias_str, '%');
    END//

Использовали подмену разделителя команд, чтобы можно было вставить в тело процедуры точку с запятой. Теперь можно искать учётные записи по части альяса:

    CALL find_login('Копыт');

Здесь я DELIMITER вернул обратно, конечно же. :) Всё, теперь жить можно. Аналогично можно сделать процедуру для укорочения INSERT INTO, но я думаю, что это уже жлобство.