Маленькая база для маленькой компании

Николай Ткаченко, "Комиздат"

Сделать полезную информацию доступной для всех сотрудников не так уж и сложно. Для этого не обязательно быть программистом баз данных. Просто воспользуйтесь бесплатным офисным пакетом.

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

Такая технология существует давно и известна под названием "база данных для рабочих групп". Создать ее можно при помощи множества инструментов, которые отличаются возможностями и степенью сложности. Но в данном случае хотелось бы остановиться на проблеме организации базы данных небольшой организации, которая потребовала бы наименьших материальных и ресурсных затрат. Средства быстрой разработки программ, такие как Borland Delphy или MS Access, известны всем. А вот о том, что несложные базы данных можно строить с помощью OpenOffice, знают не все.

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

Организовать базу данных с помощью OpenOffice (www.openoffice.org) можно как локально, так и по сетевой клиент-серверной технологии. Первый метод очень прост — но при этом скорость работы может быть сильно ограничена, если этой базой будут пользоваться одновременно несколько сетевых машин. В качестве другого способа (клиент-серверная технология) можно порекомендовать связку OpenOffice и MySQL. Вообще, сервером базы данных может быть любая программа, предоставляющая интерфейс ODBC/JDBC операционной среде, в которой работает OpenOffice.

Мы же рассмотрим именно MySQL — как наиболее доступную для неискушенных пользователей серверную платформу. Тем более что и литературы по ней более чем достаточно.

Постановка задачи

Чтобы легче было разобраться в последовательности действий и их смысле, вначале оговорим и конкретизируем задачу, которую мы хотим выполнить. Первый шаг сделаем достаточно простым — но не бесполезным.

Итак, есть список сотрудников и клиентов фирмы, а также перечень ее партнеров. Требуется создать набор учетных карточек как людей, так и организаций,— причем с возможностью различных выборок. К примеру, мы должны иметь возможность, задав название фирмы, получить список ее сотрудников — или, наоборот, выбрав определенного человека, получить информацию (адрес, телефон и др.) о той организации, где он числится.

В реляционных базах данных (а сейчас распространены практически только такие БД) информация хранится в таблицах. Нам понадобится две.

В первой будут размещены учетные карточки людей. Это значит, что столбцы таблицы будут содержать такую информацию, как имя, домашний адрес, телефон, e-mail и место работы.

Вторая таблица, относящаяся к предприятию, будет иметь следующие поля: название организации, ее адрес, телефон, факс и т.д.

Как вы понимаете, связь между таблицами может строиться через общее поле, идентифицирующее предприятие. Таким образом, для каждого человека известно, в какой организации он работает. Используя эту информацию, в соответствующей таблице можно найти дополнительные сведения о человеке, например узнать его рабочий телефон.

Установка необходимых программ

Начать можно с инсталляции MySQL на сервер. Сервером можно сделать любую машину, на которой будет находиться база данных и которая будет предоставлять доступ к этой базе другим компьютерам сети. Конечно, лучше, если это будет самая "сильная" машина в офисе. Но вначале, на стадии разработки и тестирования, обычно задействуют собственный ПК. Будем надеяться, что у вас установлена Windows 2000/XP.

Итак, распаковываем пакет mysql-4.0.21-win (или другую версию этой программы) и запускаем setup. Несколько раз нажав кнопку Ok, вы получите установленный в папку c:\mysql и готовый к работе программный пакет. Запуск этого сервера можно производить как из командной строки:

 c:\mysql\bin\mysqld

 так и виде сервиса, при старте Windows. Для этого нужно дать команду:

 c:\mysql\bin\mysql -install

 Остановка mysql может быть произведена так:

 c:\mysql\bin\mysqladmin -u root shutdown

 — или с помощью графической утилиты Службы, которая находится в Панели управления.

Запускаем MySQL-сервер и создаем базу данных. Дадим ей имя myoffdb, а пользователя, управляющего этой базой, назовем mydbadm. Делается это с помощью утилиты командной строки mysql, входящей в состав пакета.

Выполняем

c:\mysql\bin\mysql -u root

— мы очутились в оболочке mysql. Создаем нужного пользователя и предоставляем ему нужные права:

GRANT ALL ON myoffdb TO 'mydbadm'@'localhost' IDENTIFIED BY 'secret';
QUIT;

Не забудьте завершать каждую команду символом ";" — в противном случае утилита запутается. Как вы уже, наверное, догадались, пользователю myoffdb приписывается еще и пароль secret.

Далее создаем саму базу данных — делаем это при помощи все той же утилиты, но уже от имени пользователя mydbadm. Из командной строки вызываем mysql:

c:\mysql\bin\mysql -u mydbadm –p

После ввода правильного пароля (secret) попадаем в утилиту и даем команду:

CREATE DATABASE myoffdb;
QUIT;

База данных создана. В принципе, здесь же можно насоздавать и нужных таблиц, но работать с командной строкой не очень удобно, так что возложим эту задачу на OpenOffice.

Пришло время установить соответствующий коннектор ODBC. Зачем он нужен? Этот интерфейс позволяет различным клиентским программам обращаться к различным SQL-серверам, используя стандартный набор команд. То есть коннектор необходим тому же OpenOffice — не важно, будет он принимать данные от MySQL- или Postgree-сервера.

Нужный нам драйвер (MyODBC-standard-3.51.8-win) можно скачать с сайта www.mysql.com. Распаковываем этот драйвер и после копирования файлов вручную добавляем его в систему. Для этого откройте Панель управления > Администрирование > Источники данных и на вкладке Пользовательский DSN нажмите Добавить.... В появившемся диалоге (см. рис. 1) нужно выбрать MySQL ODBC и нажать Готово. Затем следует уточнить параметры, относящиеся к соединению с сервером баз данных. Для оговоренного нами случая можно ввести значения, как на рисунке. Если в данное время MySQL-сервер работает, то с помощью кнопки Test Data Source можно проверить работоспособность созданного интерфейса.

Рис. 1. С помощью средств ОС Windows, подключаем драйвер ODBC

Предположим, все нормально. Тогда устанавливаем OpenOffice, запускаем его и настраиваем источник данных. Для этого нужно открыть мастер адресных книг из меню File > AutoPilot > Address Data Source.... Из списка переключателей типов источников адресов выберите самый нижний — Other external data source. Нажмите Next, а затем кнопку Settings. Откроется окно, в котором нужно будет задать необходимые сведения о базе данных (вы помните, что в это время MySQL должен быть запущен?).

Сделайте все так, как изображено на рис. 2. Не забудьте сохранить настройки (кнопки Apply и Ok). Теперь OpenOffice знает, откуда черпать данные для дальнейшей обработки.

Рис 2. Подключаем базу данных в OpenOffice с помощью мастера

В работе с базами данных посредством методов OpenOffice есть секрет. Дело в том, что при установке клиентского соединения эта программа требует у соответствующего драйвера подтверждения того, что данные открыты на изменение. Если подтверждения нет, OpenOffice открывает все таблицы только для чтения. В этом есть свои "за" и свои "против". Представим, что всю информацию в базу вносит один человек — остальные лишь вытягивают ее и находят нужные им записи. В этом случае такое поведение OpenOffice вполне оправдано и даже полезно.

Но если изменения в таблицы вносить все-таки нужно, а используемый драйвер не позволяет этого делать, придется кое-что подправить в установках: в свойствах базы данных нужно добавить поле IgnoreDriverPrivileges со значением TRUE. К сожалению, для выполнения данного действия пользовательского интерфейса не предусмотрено. Впрочем, можно задействовать специальный макрос (http://dba.openoffice.org/howto/IgnoreDriverPrivileges.html).

Если вы совершенно не в курсе, как управляться с макросами, сделайте следующее: откройте Tools > Macros > Macro... В поле Macro Name введите какое-нибудь имя (например, WriteToDB) и нажмите кнопку New — откроется окно макросов. Проще всего стереть весь текст в окне и вместо него вставить код, скопированный с веб-страницы. Дальше следует запустить макрос Main на выполнение и задать правильное имя базы данных. Вот и все. Во всяком случае, для связки OpenOffice-ODBC-MySQL это работает.

Мы уже знаем, что без таблиц, между которыми устанавливаются связи, не обойтись. Собственно, эти связи и придают системе необходимую функциональность. Что ж, давайте создадим такие таблицы.

Начнем с описания фирм. Предположим, что все данные, которые могут нам понадобиться, это название фирмы, ее адрес, телефон/факс и e-mail.

Помимо обычных полей, в таблице существует еще и специальный элемент — главный ключ. Его задача — уникально идентифицировать определенную строку таблицы. Создаваться этот ключ может как вручную, так и автоматически. Последний способ наиболее распространен и со стороны пользователя требует минимум действий.

Для создания таблиц воспользуемся встроенным в OpenOffice средством редактирования баз данных Data sources… — оно вызывается из меню Tools. Откройте вкладку Tables

Рис. 3. В этом диалоге можно отредактировать все таблицы

Теперь создаем таблицу firms — нажимаем кнопку New Table Design. Откроется редактор, в котором можно создавать поля таблицы со всеми необходимыми атрибутами: именем поля, типом, длиной и значением по умолчанию. С именем все понятно, а что касается типов, то поле может быть тестовым, числовым, датой и т.д. В нашей таблице все поля текстовые (в обозначения MysQL — VARCHAR).

Каждое поле имеет определенную длину, которая определяется максимальным количеством символов, которые можно в него вписать. Чем поле короче, тем быстрее работает  MySQL. Но, с другой стороны, если в такое поле не поместится название или адрес фирмы, то ничего хорошего тоже не получится. Как говорится, семь раз отмерь… В общем, создайте набор полей как на рис. 4 (но если у вас есть собственные соображения по поводу того, как усовершенствовать базу,— не стесняйтесь).

Рис. 4. Набор полей для таблицы с описанием фирм

При сохранении таблицы появится диалоговое окно с предложением создать Primary key (главный ключ). Соглашайтесь.

Следующая таблица, которая нам понадобится, это persons — она содержит информацию о конкретных людях. Ее поля могут быть такими: "Имя", "Фамилия", "Домашний телефон", "Место работы". Место работы должно совпадать с соответствующим (а именно — Name) полем в таблице firms. И тогда, используя выборку, мы сможем узнать дополнительные сведения о человеке, в том числе его рабочий телефон и e-mail.

Вновь воспользовавшись редактором New Table Design, задаем поля таблицы —получится нечто похожее на то, что изображено на рис. 5.

Рис 5. Поле Work — это сноска на таблицу с описанием фирм

Костяк базы данных готов, остается вопрос ее наполнения и выборки записей по различным критериям.

Заполняем таблицы

Теперь можно попробовать заполнить поля таблиц. Правильней будет сначала ввести информацию о фирмах, а потом уже заняться персональными карточками сотрудников. Это потому, что строка информации о людях имеет поле Work — сноску на таблицу firms.

Редактировать таблицы можно и в режиме Data Sources, если включить его через меню View > Data Sources или при помощи соответствующей кнопки панели инструментов. Впрочем, такая работа подходит для стадии отладки, а не для конечного использования. На рис. 6 показано, что в этом случае очень хорошо видна структура таблицы, все ее поля.

Рис. 6. В режиме Data Sources таблица представлена пользователю "как есть"

Но единственное действие, которое можно произвести для повышения удобства операторской работы, это скрыть какой-то столбец. К примеру, в данном случае столбец ID нам не нужен — он заполняется автоматически. Скрыть его можно при помощи контекстного меню (вызывается правой кнопкой мыши).

А вот переименовать колонки таблицы нельзя. Это неудобно — ведь иногда приходиться давать столбцам абстрактные имена в английском написании (вроде wrktelnum — не очень-то понятно).

Чтобы упростить редактирование базы данных, можно прямо на листе документа вставить специальную форму. Для этого следует переключиться в режим дизайна, нажав кнопку Design Mode On/Off (рис. 7).

Рис. 7. Переключатель режима дизайна

Затем следует открыть панель инструментов Form Functions и активировать инструмент Table control (рис. 8). Теперь очертите на листе фрагмент, в котором должна содержатся форма. Автоматически запустится диалог-помощник, который попросит выбрать для включения в форму нужную таблицу и ее поля.

Рис. 8. Инструмент для удобного редактирования таблиц

В созданной форме с помощью контекстного меню Columns колонке можно дать понятное имя, добавить подсказку и настроить некоторые другие опции (см. рис. 9).

Рис. 9. Теперь можно придать таблице подходящий вид

Если выключить режим дизайна, то записи таблицы будут выглядеть примерно так, как на рис. 10. А в самой форме можно найти все инструменты для добавления или удаления записей, их фильтрации и сортировки.

Создаем форму для удобного редактирования таблиц

 с персональной базой, скорее всего, вызовет ваше неудовольствие. Дело в том что поле Work в таблице persons указывает на имя фирмы (поле Name) в таблице firms. Логично было бы устроить форму редактирования таким образмо, чтобы название фирмы можно было выбирать из списка. И этого можно добиться, если заменить текстовое поле на поле со списком.

Но вначале следует создать запрос (Query), который будет возвращать список названий всех фирм, существующих в нашей базе данных. Сделать это можно с помощью элемента New Query, принадлежащего контекстному меню объекта Queries из источника данных (если последний закрыт, вызвать его можно с помощью ).

Проще всего создавать запрос в визуальном виде (Design view) — в этом случае все сводится к правильному выбору предлагаемых элементов диалога. Только в начале нужно добавить таблицы, из которых будет проводиться выборка. В нашем случае такая таблица одна — firms (рис. 11).

Рис. 11. Из этих таблиц будет происходить выборка результатов

Посмотрите на рис. 12 и сделайте точно так же. Вас может удивить присутствие двух одинаковых столбцов Name — но это вполне объяснимо: функции у них будут разные.

Рис. 12. Для работы с формой нужно два одинаковых столбца

Результат в виде SQL-синтаксиса можно увидеть, если с помощью кнопки Switch Design View On/Off (на ней изображен школьный угольник) переключиться в режим SQL. Командная строка должна выглядеть примерно так:

SELECT `Name`, `Name` FROM `myoffdb`.`firms` `firms

Как работает запрос, можно также проверить с помощью кнопки Run Query (в панели инструментов первая слева). Осталось только сохранить запрос(все формы и запросы сохраняются вместе с документом OpenOffice) под именем, скажем,  FirmsName.

Следующий этап — создание формы редактирования таблицы учетных карточек сотрудников и модернизация ее путем внедрения созданного нами запроса. Для этого элемент Table control, привязанный к таблице person, вставьте на пустую страницу. Вы уже можете редактировать (изменять, добавлять, удалять) записи, единственное неудобство это поле Work. Его содержимое — название предприятия, где работает человек. Обидно вводить эти записи еще раз. К тому же, нельзя ошибиться даже в мелочи — этого будет достаточно, чтобы не сработала автоматическая выборка, а ведь именно в автоматизации вся сила баз данных.

Впрочем, эту беду можно обойти. Осталось разобраться, как.

Вызовите правой кнопкой мыши контекстное меню столбца Work и с его помощью выполните преобразование Replace with/List box — таким образом вы замените текстовое поле на список. Самое сложное — это наполнить этот список нужным содержимым и связать его с полем Work таблицы persons. Такую настройку свойств элемента списка можно произвести, вызвав правой кнопкой мыши диалог Column...

Откроем вкладку Data. На ней находятся четыре важных для нашего случая параметра:

• DataField. Поле в таблице, к которой привязана форма, будет изменяться с помощью этого элемента списка;
• Type of list contents — способ (тип), при помощи которого будет наполняться список. Мы обязаны выбрать Query (ведь специально для этого и создавался запрос); если же избрать SQL, то SQL-запрос можно будет ввести "на ходу", без лишней возни по дизайну запросов;
• List Content. Выбрали тип наполнения — теперь нужно конкретизировать его суть. В нашем случае это можно сделать, выбрав из списка запрос FirmsName (в другом случае в этом поле пишется строка SQL-команды);
• Bound Field — очень интересный параметр. Помните, в запросе мы делали две колонки с одинаковыми значениями, Name? Роль первой проста — эта колонка отображается как содержание списка. То есть, если запрос проводить по-другому (например, SELECT `City`, `Name`), то в списке будут отображаться не названия фирм, а города, где они расположены. Отображаться, а не записываться в базу! Поле запроса, которое должно вноситься в таблицу, указывается номером (начиная с нуля) в рассматриваемом параметре.

Посмотрите еще раз на диалог. В данном поле стоит единица (ноль, кстати, указать не удается) — это получается второе по счету поле запроса, Name. Его содержимое будет записываться в поле таблицы, указанное в параметре DataField. Попросту говоря, с помощью данных параметров мы определяем, какое поле запроса писать в конкретном поле таблицы. При этом в списке всегда будет отображаться первое поле запроса. Хитро.

Снова переключите режим формы Design Mode On/Off. Надеюсь, все сделано правильно и результат достигнут: теперь фирму, в которой работает человек, можно выбирать из списка.

Запрос с параметром

Для построения разнообразных выборок из таблиц, придется освоить параметризированные запросы. Предположим, следует найти все фирмы, которые расположены в Севастополе. Для этого нужно будет построить простенький запрос (используйте New Query(Design View), если нет желания возиться с SQL-синтаксисом).

Рис. 13. Параметр запроса обозначается в поле Criterion двоеточием

Обратите внимание на рис. 13. Это подсказка по созданию запроса. В поле Criterion вписывается критерий выборки. Если критерий параметризованный (то есть такой, что будет определяться во время выполнения), то его имя должно начинаться с двоеточия. В виде SQL-команды он выглядит следующим образом:

SELECT `Name`, `City`, `Address`, `Tel`, `Fax` FROM `myoffdb`.`firms` `firms` WHERE ( ( `City` = :x ) )

Теперь выполните запрос — появится диалоговое окно с просьбой определить параметр "x" (рис. 14).

Рис. 14. Укажите город, по которому следует провести выборку

Вообще, такой вид отбора нужных данных не слишком практичен — но он демонстрирует механизм работы с параметрами и пригодится при отладке. Главное, что данный вид запроса можно встроить в форму документа, а параметр задавать более удобными для человека способами. Давайте отработаем этот прием.

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

Для управления формами документа, если их несколько, предусмотрено специальное средство — Form Navigator, он вызывается из панели функций. Вызовите контекстное меню и создайте новую форму (как это показано на рис. 15).

Рис. 15. Подчиненные формы нужны для обработки запросов с параметрами

Пока это всего лишь оболочка — в ней нет ни одного отображаемого объекта. Впрочем, ими мы займемся позже. Роль подчиненной формы лучше всего продемонстрировать наглядно (см. рис. 16).

Рис. 16. Соотношение между источником данных и документом определяется в свойствах подчиненной формы

В данном случае, мы не будем создавать отдельный запрос для выборки информации о предприятии, а впишем соответствующую SQL-команду прямо в форму:

SELECT `City`, `Address`, `Tel`, `Fax`, `Name` FROM `myoffdb`.`firms` `firms` WHERE ( ( `Name` = :firm )

Как видите, в результате работы команды будут отобраны данные (город, адрес, телефон, факс) для фирмы, название которой предается через параметр firm.

Как передать этот параметр? Для этого можно выделить соответствующее поле (Work, именно в нем находится название фирмы, где работает интересующий нас человек). При этом связь между конкретным полем и параметром запроса оформляется с помощью свойств подчиненной формы Link master fields и Link slave fields — как это изображено на рис. 16.

Механизм работы формы будет строиться по следующему принципу: при перемещении по записям основной таблицы (учетные карточки сотрудников) будет формироваться параметр firm, как значение поля Work. В результате передачи этого параметра в подчиненную форму, которая основана на параметризованном SQL-запросе, актуальные данные будут отображены в документе.

*  *  *

Информацией сегодня принято дорожить. В какой бы сфере вы ни работали, база данных — по клиентам или по сделкам — понадобится всегда.

Конечно, существуют специализированные программные продукты — CRM. Но практически все они созданы на коммерческой основе. Как ни странно, в этом есть свои минусы — чтобы поднять стоимость таких программ, разработчики "раздувают" их по максимуму. И если на данном этапе работа вашего предприятия не нуждается в полноценной, масштабной автоматизации отношений "менеджер-клиент", можно попробовать обойтись "малой кровью".

База данных, созданная собственными руками, может быть гибко адаптирована под специфику работы предприятия, оперативно модернизироваться и расширяться. А если через некоторое время вы все-таки придете к необходимости использования коммерческой CRM, прежние наработки не пропадут зря. Ведь все системы такого рода используют для хранения данных стандартное хранилище — SQL-сервер. А значит, есть техническая возможность импортировать данные в новую программу.