LXF82:OOo Basic

Материал из Linuxformat.

Работа с базой данных

ЧАСТЬ 3 Очередной шанс поумнеть при помощи Марка Бэйна — на этот раз его макросы и советы по работе с базами данных помогут вам запускать запросы, создавать отчеты в OpenOffice.org и вести учет ваших книг и

До чего ж я люблю OpenOffice.org — особенно когда использую его вместе с OOo Basic. Не только потому, что он высвобождает меня из клещей ProprietarySoft, Inc — больше потому, что он действительно хорош. В предыдущих выпусках мы увидели, как легко можно манипулировать текстовыми документами и таблицами с помощью OOo Basic. На сей раз -посмотрим, как извлекать информацию из базы данных.

Главное, надо быть как можно ленивее. Представьте, например, что вы хотите подготовить счет для ужасно популярного Linux-журнала, в который вы пописываете. Зачем терять время, перепечатывая то, что у вас уже хранится? Это руководство даст вам инструменты, пресекающие лишнюю трату времени, а заодно, естественно, позволит насладиться исследованием Unix.

Ингредиент № 1: сервер базы данных

Начинать — так с начала. Раз уж это руководство по макросам для извлечения информации из базы данных, вам понадобится база данных. Однако я не намерен рассказывать о ее установке: это выходит за рамки руководства. Конечно, если бы вы объявили, что базы данных у вас нет и вы даже не знаете, с чего начать, то я ответил бы: «Без паники, это легко». Затем я предположил бы, что вам нужен сервер баз данных - так ведь можно использовать любой старый компьютер, подсоединить его к сети и затем установить Debian (если у вас нет второго компьютера, запустите сервер на своей машине). Вы сами можете создать минимальный загрузочный диск с http://www.debian.org, вставить в привод, перезагрузиться и следовать инструкциям. Об установке дополнительного программного обеспечения (рабочего стола, файл-сервера, web-сервера и т.д.) беспокоиться нечего: достаточно необходимого минимума.

Тут я велел бы вам превратить ваш компьютер в сервер баз данных с помощью команды apt-get install mysql-server, а затем отредактировать файл /etc/mysql/my.cnf, закомментировав строку bind-address = 127.0.0.1 (чтоб она выглядела как #bind-address = 127.0.0.1). Это позволит подключаться к серверу с любого компьютера вашей сети.

Вам, небось, захотелось бы создать и базу данных, и пользователя для доступа к ней. Тогда бы я посоветовал сделать следующее:

mysql -uroot mysql
SET password FOR 'root'@'localhost' = password('put your own password here');
CREATE DATABASE accounts;
GRANT ALL privileges ON accounts.* TO 'your user'@'%'
IDENTIFIED BY 'your user password';
exit;

Наконец, я предложил бы задать вашему новому серверу статический IP-адрес, отредактировав файл /etc/network/interfaces так, чтобы конец файла был похож на следующее:

#iface eth0 inet dhcp
iface eth0 inet static
address 192.168.1.3
netmask 255.255.255.0
gateway 192.168.1.1

В этом пункте я бы отметил, что вам пора перегрузиться и зайти на компьютер, где у вас стоит OpenOffice.org.

Но так как наше руководство исключительно про OOo Basic, а не про создание баз данных, всего этого я делать не буду.

Доступ к базе данных

OpenOffice.org пока не запускайте. Чтобы облегчить себе жизнь (для того и придуманы макросы), воспользуемся UnixODBC, это API для доступа к источникам данных, который избавит нас от трудностей создания соединений к серверу и базам данных — протоколы, посылка сигналов и все такое прочее. Самое сложное, что предстоит сделать — это установить UnixODBC и его библиотеки MySQL на машину, где вы будете использовать OOo. На Debian это делается всего-навсего через

apt-get install unixodbc
apt-get install libmyodbc

Очевидно, если у вас другой дистрибутив, то придётся проверить для него процесс установки — взгляните на UnixODBC на странице http://www.unixODBC.org. Когда вы установите UnixODBC, понадобятся еще две вещи. Первое — отредактировать /etc/hosts так, чтобы он включал ссылку на сервер вашей базы данных, то есть 192.168.1.3 acamas. Второе — отредактировать /etc/odbc.ini, чтобы он включал примерно следующее:

(accounts)
Description = MySQL db test
Driver = MySQL
Server = acamas
Database = accounts
Port = 3306

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

Разборки с базой данных

Откройте OpenOffice.org. Тип документа безразличен; пусть, например, это будет документ Writer. В меню Tools [Сервис, иногда нужный вам пункт оказывается в меню View (Вид), — прим.ред.] среди подменю имеется одно под названием Data Sources (Источники данных). Кликните на нем, и вы увидите форму Data Source Administration (Управление источникам данных).

С ней все просто: нажмите New Data Source (Новый источник данных) и установите тип базы MySQL на вкладке General (Общие). Затем перейдите на вкладку MySQL, добавьте имя базы данных в Data Source URL (Адрес источника данных) и введите имя пользователя (не забудьте создать пустую базу данных и пользователя, прежде чем получать к ней доступ из OOo). Далее нажмите на закладку Tables (Таблицы). Там ничего не будет (потому что никаких таблиц вы еще не создали). Угадайте, что мы теперь сделаем? Правильно, рванём в пивбар, с вас причитается. Нет? Ладно, оставим это на потом: время создавать данные.

Если вы любитель командной строки (должен сознаться, что я из них), вернитесь на сервер с базой данных, зайдите в базу и создайте таблицы. Не забывайте, что вы можете подключиться прямо с текущей машины.

bainm@hector:~/ooobasic3$ mysql -hacamas -ubainm -pmypassword accounts
mysql > CREATE DATABASE accounts;
CREATE TABLE accounts.customer (id int AUTO_INCREMENT,
surname varchar(50), firstname varchar(50),
address1 varchar(50), address2 varchar(50), city varchar(50), county
varchar(50), country varchar(50), postcode varchar(50),PRIMARY KEY (id));
CREATE TABLE accounts.invoice (id int AUTO_INCREMENT,customer_id int,
sent_date date,paid_date date,PRIMARY KEY (id));
CREATE TABLE accounts.item (id int AUTO_INCREMENT,customer_id int,
invoice_id int,title varchar(50),details varchar(255),value double,
PRIMARY KEY (id));
INSERT INTO accounts.customer
(surname,firstname,address1, address2,city,county,country,postcode)
VALUES
('Smith','John','The Big House','1 The Street','Thistown','Thisshire','UK','TH 1 1HT');
INSERT INTO accounts.customer
(surname,firstname,address1, address2,city,county,country,postcode)
VALUES
('Jones','Mary','Building A','Industrial Est.','Hereton','Herehire','UK','HE1 1EH');
INSERT INTO accounts.item (customer_id,title,value) VALUES (1,'A fine piece of work',500);
INSERT INTO accounts.item (customer_id,title,value) VALUES (1,'A great job',500);
INSERT INTO accounts.item (customer_id,title,value) VALUES (2,'Day 1',1500);
INSERT INTO accounts.item (customer_id,title,value) VALUES (2,'Day 2',1600);

Кого это в восторг не приводит, пусть возьмёт форму Data Source Administration (Управление источникам данных), перейдёт на вкладку Tables (Таблицы) и нажмёт на кнопку New Table Design (Создать новую таблицу). Можете воспользоваться формой Table Design (Создание таблицы), с её помощью таблицы создавать легко.

Работа с новыми таблицами

Мы извели довольно много времени на разборки с базой данных — без её правильной работы не обойтись, а все остальное само встанет на свои места. Теперь можно заняться нашим первым макросом для работы с базой данных. Если вы следовали руководству прошлого выпуска, то привыкли к функции OOo CreateUnoService (вы ведь практиковались, не так ли?). Мы снова собираемся использовать ее здесь, на сей раз для доступа к RowSet. Это имя OOo для набора записей, оно позволяет выполнять запросы к базе данных и получать от нее информацию.

RowSet = createUnoService("com.sun.star.sdb.RowSet")

Осталось только сказать RowSet о базе данных, к которой вы хотите подключиться (то есть к той, что вы установили в Data Source Administration (Управление источникам данных)): сообщите имя пользователя, пароль и запрос, который хотите выполнить. RowSet получит результат запроса и представит его вам.

Поэтому вы, видимо, захотите сделать следующее:

sub main
sql1
end sub
Sub sql1
Dim RowSet
RowSet = createUnoService("com.sun.star.sdb.RowSet")
RowSet.DataSourceName = "Accounts"
RowSet.User="bainm"
RowSet.Password = "password"
RowSet.Command = "SELECT count(*) c FROM item"
RowSet.execute()
RowSet.next()
MsgBox "There are " + rowSet.getString(1) + " items"
End Sub

Отлично, теперь рассмотрим следующий пример:

Dim RowSet
Sub Main
connectToDatabase ("Accounts", "bainm", "kawasaki")
sql1
End Sub
Sub connectToDatabase(database as string, username as string, password
as string)
RowSet = createUnoService("com.sun.star.sdb.RowSet")
RowSet.DataSourceName = database
RowSet.User = username
RowSet.Password = password
End Sub
Sub updateRowSet(sql as string)
RowSet.Command = sql
RowSet.execute()
End Sub
Sub sql1
updateRowSet("SELECT count(*) c FROM item")
RowSet.next()
MsgBox "There are " + rowSet.getString(1) + " items"
End Sub

Теперь понятно, как легко расширить функциональность макроса. Взгляните:

Sub sql2
updateRowSet("SELECT id, surname, firstname FROM
customer")
while RowSet.Next()
MsgBox "Customer No. " + rowSet.getString(1) +
" " + rowSet.getString(2) + _
" " + rowSet.getString(3)
wend
End Sub

Написание отчетов

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

Замечательно то, что мы можем делать впечатляющие вещи добавкой всего нескольких строк кода. Мы уже разбирали процедуру loadNewFile (мы познакомились с ней в LXF80 и модифицировали в LXF81) для создания нового документа Writer, и у нас есть процедура add_paragraph для записи в документ (не пугайтесь, весь нужный код содержится на прилагаемом диске). Надо только добавить процедуры для создания отчетов по информации в базе данных. Вот простой способ создания документа, содержащего список всех покупателей в базе данных Accounts:

Dim RowSet
Sub Main
connectToDatabase ("Accounts", "bainm", "kawasaki")
loadNewFile
createCustomerReport
End Sub
Sub createCustomerReport
updateRowSet("SELECT id, surname, firstname FROM
customer")
while RowSet.Next()
add_paragraph("Customer No. " + _
rowSet.getString(1) + " " + rowSet.getString(2) +
" " + rowSet.getString(3))
wend
End Sub

Вот и вся любовь. Процесс прост: посылаете запрос в базу данных, а затем отображаете результат в документ. Конец истории? Вообще-то не совсем. В LXF80 мы обнаружили: никто не любит менять функцию Main под создание каждого нового отчета — ну разве что мазохисты. И снова, ключевым моментом является создание диалогового окна для управления требуемыми работами.

Вам уже не понадобится вручную набирать содержимое элементов в виде списка. Нет, на этот раз вы загрузите их прямо из базы данных. Представим, что вы добавили элемент list box и назвали его lstCustomers в диалоговом окне dlgAccounts. Чем его загружать? Вы меня опередили: можно послать запрос в базу данных на получение списка покупателей:

updateRowSet("SELECT surname, firstname FROM customer")

Теперь в цикле переберите набор записей и загрузите их в элемент

list box:
lstCustomers.AddItem(rowSet.getString(2) + " " + rowSet.
getString(1), i)

Просмотрите процедуру loadlstCustomers, расположенную на нашем диске, чтобы разобраться, как она работает.

Новый элемент list box пригодится как фильтр для создания произвольных отчетов. Допустим, вам захотелось увидеть все предметы, купленные определенным покупателем — ну так используйте свойство selectedItem из list box и получите выбранный текст, а затем примените его для создания SQL-запроса:

sql = " select title,value from customer, item " + _
" where cutomer.id = item.customer_id " + _
" and concat(customer.firstname,concat(' ',customer.surname))
= '" + _
lstCustomers.selectedItem +"'"

Ешё лучше встроить SQL в функцию. Зачем? Таким образом вы сможете использовать запрос в любой процедуре без необходимости переписывания кода. Теперь добавьте в окно кнопку, ассоциируйте с ней процедуру и начинайте пользоваться. Для начала сделайте процедуру, выводящую на экран окно с вашим построенным SQL-запросом. Теперь используйте SQL для получения нового набора записей и запишите итог в документ Writer. cmdItemReport с нашего диска покажет вам это в действии.

Я уверен: вы поймете, как всё это просто (запомните хорошенько: это просто), и автоматизация извлечения информации из базы данных в документ OOo Writer тоже проста. Вас, наверное, не удивит, что данные можно передавать и в таблицу Calc. Взаимодействие с базой данных происходит таким же образом. Единственное отличие — вы должны писать в отдельные ячейки, а не в абзацы, а это даёт даже больше гибкости в отображении вашей информации.

А теперь я вас покидаю — придумайте сами, что теперь делать: всё необходимое мы обсудили в LXF80, LXF81 и в этом выпуске. И если вы всё ещё в тупике, то загляните в раздел «Журнал» на диске — готовые программы уже ждут вас не дождутся.

Медиа-библиотека

На закуску рассмотрим простое приложение — оно поможет вам хранить и просматривать список всех ваших CD- и DVD-дисков, пластинок или книг.

Начните с создания таблиц в вашей базе данных. Вам придется задаться вопросом: работать ли с отдельной базой данных для каждого проекта или поместить все таблицы в одну базу? Я бы порекомендовал первое — так проще управлять информацией. Однако, выбрав этот метод, не забудьте добавить запись о новой базе в /etc/odbc.ini и добавьте ее как новый источник данных в OpenOffice.org. Понадобится также подсказать макросу, чтобы он использовал новую базу данных - поменяв connectToDatabase («Accounts», «bainm», «kawasaki») на connectToDatabase («library», «bainm», «kawasaki»).

Далее: не пытайтесь вбить всё в одну таблицу — получите только проблемы. Какие именно? Что ж, давайте рассмотрим простой пример - поле, содержащее имя. Вы-то знаете, что Б Гейтс, Уильям Гейтс и Властелин Зла означают одно и то же лицо, но ваш компьютер не знает, и это затруднит процесс создания запроса. Взгляните на таблицу:

Взамен можно использовать две таблицы — одна с описанием предметов, другая с авторами:

Таким образом, вместо запоминания всевозможных написаний имени автора вы обойдётесь его идентификационным номером. Аналогично, вам не надо хранить слова cd, lp, book в таблице, содержащей заголовок. Вместо этого можно использовать что-то вроде:

Теперь с помощью SQL-запроса вы можете получить из базы данных полезную информацию:

SELECT item.title, author.name, media.type
FROM item, author, media
WHERE item.author_id = author.id
AND item.media_id = media.id;

Используйте этот SQL-запрос в процедуре заполнения таблицы результатом запроса — изучите showFullLibrary на диске, чтобы понять, как это работает (там же вы найдете SQL-запрос для создания базы данных и пример файла /etc/odbc.ini). Внимательно посмотрев на этот макрос, вы обнаружите, что в нем не содержится жестко заданного числа столбцов, когда осуществляется запись в документ; вместо этого для создания цикла используется свойство RowSet.Columns.Count. И что? А вот что: неважно, если вы измените число записей, получаемых в запросе — макрос автоматически вставит правильное число столбцов в таблицу.

Фильтрация данных

Так и слышу ваш крик: «Да не хочу я видеть все, что содержится в базе данных! Мне надо смотреть только CD-диски, или только книги, или только работы одного художника». Что ж, легко — если вы создадите новую форму, то можете добавить на нее несколько элементов list box и заполнить их из таблиц author и media (так же, как мы сделали в примере с покупателями). Элементы list box можно использовать как фильтры для построения запроса. На диске, showFilteredLibrary показывает, как использовать опциональный ввод для построения такого фильтра и последующего отображения результата в таблицу.

Чтобы добавить в базу новые предметы, авторов или типы носителей, вам пригодится выражение insert, например:

INSERT INTO library.author (name) VALUES ('Hawkwind');
INSERT INTO library.item (title,author_id,media_id) VALUES ('The Ambient Anarchists',4,1);

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

Домашнее задание

Задание на месяц (и никаких экивоков типа «если у вас будет время, то…» - это нужно не мне, а вам): проанализируйте свои каждодневные задачи и выберите те, которые можно автоматизировать рассмотренным нами способом. Не ради увеличения производительности и тому подобной ерунды, а исключительно из лени. Рекомендую также взглянуть на запросы update — почему бы не записывать данные в базу так же, как и читать их из нее?