MySQL, PHP и… Microsoft Office

Виталий Сизов

Введение

Проектирование базы данных

Подготовка источника информации

Пакеты SQL-запросов

Создание комплекса инструментальных средств

Заключение

Введение

Думаю, что читателям знакома типичная картина внедрения информационных технологий на новом предприятии. Сначала приобретается единственный персональный компьютер — просто для того, чтобы не отстать от жизни. И на этом компьютере пытаются реализовать несложную систему делопроизводства и бухучета. Когда управленческого персонала становится больше, приобретают новые компьютеры. Затем появляется желание связать их в единую сеть и освоить серьезные программные комплексы, допускающие многопользовательские режимы работы, например, «1С:Предприятие». Все это работает под управлением Windows, и кажется, что так будет продолжаться всегда. Оборудование и программное обеспечение время от времени модифицируют по мере выпуска новых Pentium’ов и очередных версий операционной системы и пакетов популярных прикладных программ. Постепенно предприятие развивается, и тогда возникает потребность в создании корпоративной базы данных. Естественно, что решение ищут прежде всего на знакомой платформе Windows, благо Microsoft предлагает массу продуктов для организации приличной базы данных. Это и стандартный Access, и увядающая СУБД FoxPro и, наконец, мощный и дорогой SQL Server.

Привычный путь эволюционного развития под сенью программного обеспечения от Microsoft начинает вызывать сомнения только тогда, когда компания открывает для себя Internet. Пока дело ограничивается созданием домашних страничек, программы от Microsoft еще способны удовлетворять потребности пользователя. Но рано или поздно появляется интерес к иной платформе — UNIX. Оказывается, что большинство серверов в Сети работают вовсе не под управлением Windows, а для того, чтобы в полной мере использовать возможности редакторов Web-страниц от Microsoft, требуется Internet Information Server — специальный сервер, также производства Microsoft. Поставщиков услуг Web-хостинга, базирующихся на таком программном обеспечении, не так уж и много. Кроме того, для организации серьезной базы данных следует иметь доступ и к Microsoft SQL Server. Конечно, все это можно приобрести и включить в корпоративную сеть собственный Internet-сервер — были бы деньги на оборудование и дополнительная вакансия для системного администратора.

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

Однако проблема выбора между UNIX и Windows не ограничивается только экономическими соображениями. Исключительную важность здесь имеет концепция открытого кода, завоевавшая мир UNIX-программистов. В Internet созданы специальные индексы ресурсов — исходных текстов программ, которые решают самые разнообразные задачи, возникающие при разработке приложений для Internet и Intranet. При этом качество многих из них намного превосходит продукцию доморощенных программистов. Неудивительно, что разработчики, открывшие для себя UNIX и освоившие во многом благодаря открытым кодам тонкости С++, Perl, PHP, свысока смотрят на своих собратьев, которые возятся с системами программирования под Windows — типа Visual Basic.

Как водится, и здесь не обошлось без крайностей. Появление открытых операционных систем Linux, серверов Apache, систем программирования Perl, PHP, СУБД MySQL и Postgres, приводило по образному выражению ряда публикаций к «сметанию, c винчестеров» операционных систем Windows и серверов Microsoft.

Не лучше обстоит дело и с появлением Windows-версий этих систем программирования и СУБД. Во-первых, как правило, версии для Windows не являются самыми свежими вариантами программного продукта, а во-вторых, в среде Windows остается сомнительной ценность результатов, полученных при «моделировании» поведения программ, предназначенных для работы в Internet на серверах UNIX.

В действительности все это можно обставить гораздо проще. Следует признать, что лучшими программными продуктами для создания рабочих мест на персональных компьютерах и в локальной сети являются продукты Microsoft — такие как Microsoft Office. Здесь имеются великолепные средства разработки с интеллектуальной поддержкой и множеством надежных компонентов и элементов управления, организованных специально для объектно-ориентированного программирования (ООП).

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

Скорее всего, таким «подходящим» сервером окажется Apache, работающий под управлением UNIX или Linux. При этом нужно выяснить, какие средства для создания серверных приложений будут доступны пользователю, если он заключит контракт на использование данного ресурса. Будет просто отлично, если вместо классического каталога CGI-BIN с поддержкой Perl владелец сервера предлагает PHP3/PHP4 и возможность создания базы данных. Язык PHP поддерживает множество баз данных: Adabas D, InterBase, Solid, dBase, mSQL, Sybase, Empress, MySQL, Velocis, FilePro, Oracle, UNIX dbm, Informix, PostgreSQL. Но не следует забывать, что при описываемом подходе придется создавать новую базу данных. Ведь речь идет не об интеграции, а о создании специализированного ресурса, ориентированного для работы в Internet. Поэтому нужно остановиться на современных базах данных, специально созданных для такой работы. Это прежде всего MySQL — относительно небольшая и быстрая реляционная СУБД, соответствующая спецификации ANSI 92 SQL. Кроме того, вполне вероятно, что MySQL окажется единственной возможностью, предоставленной поставщиком услуг хостинга.

Полный вариант статьи вы можете найти на нашем CD-ROM.

Получив подтверждение о том, что современные средства для создания полноценных Web-приложений доступны, можно приступать к реализации самых смелых замыслов. Но как? Сервер с базой данных далеко и никаких «стартовых» приложений и инструментальных средств разработчика не предвидится. К тому же форматы файлов неизвестны, а доступ к ресурсам операционной системы ограничен. Остается принять правила игры и работать с сервером точно так, как будет с ним работать в будущем пользователь разрабатываемого приложения, а именно — с помощью обозревателя Web-страниц, привычного Internet Explorer или Netscape Navigator.

Особенностью современных баз данных является то, что все они понимают язык запросов SQL. Запрос SQL представляет собой обычную текстовую строку, которую легко передать на сервер с помощью метода GET даже из окна для ввода URL, которое имеется в любом обозревателе. Для этого нужно лишь указать адрес серверной программы — скрипта, готового принять и обработать запрос клиента.

Таким образом, для успешной работы с базой данных на удаленном сервере необходим скрипт, принимающий в качестве параметра текстовую строку SQL-запроса и передающий ее для исполнения СУБД. Как можно догадаться, такой скрипт пишется на языке PHP.

Чтобы сделать работу более приятной, дополнительно можно создать и клиентскую HTML-страницу с интерактивной формой ввода <FORM>, включающей текстовое поле <INPUT> для запроса SQL и единственной кнопкой Submit.

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

В начало В начало

Проектирование базы данных

Решение о создании базы данных на сервере Internet редко возникает на пустом месте. Чаще всего разработчик располагает готовой информацией, которую целесообразно разместить в базе данных. Исключение составляют такие приложения, как форумы и гостевые книги, где вся информация является новой. В остальных случаях значительная часть проектируемой базы данных уже имеется в виде файлов или документов Microsoft Office. Поскольку MySQL является реляционной базой данной, то вся информация в ней хранится в виде обычных двухмерных таблиц с фиксированной структурой полей. Аналогом таких таблиц в Windows являются отдельные таблицы Microsoft Access, списки Excel, DBF-файлы FoxPro. Почему-то UNIX-программисты считают, что лучшей формой для обмена данными между двумя платформами являются CSV-файлы Microsoft Excel, и даже предусмотрели специальные утилиты для преобразования такого формата Windows в записи базы данных UNIX и наоборот. Логика здесь простая. Excel будто бы создан для того, чтобы готовить данные в табличной форме. Начинать ввод информации можно сразу, без какого-либо проектирования и описания структуры таблицы. А сохранение таблицы в формате CSV исключает необходимость в знаниях специальных форматов, например заголовков файлов DBF.

Конечно, можно поставить и решить такие фундаментальные задачи, как полное преобразование единого файла базы данных Access или совокупности DBF-файлов из одного каталога базы данных ISAM в базу данных MySQL. Но потребность в подобных задачах невелика, поскольку, как уже говорилось, базы данных в Internet обычно представляют собой новые образования. Это и понятно: если база данных уже существует в Windows, то ее полная передислокация неизбежно приведет к потере многих готовых компонентов, таких как формы и отчеты. Не проще ли установить Microsoft Internet Information Server?

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

Совсем иное дело — ввод данных в таблицах Excel. Здесь можно использовать весь арсенал средств копирования и автозаполнения: Использовать формулы и интеллектуальную поддержку. Вставлять целые блоки записей из других документов, произвольно выбирать последовательность перехода от ячейки к ячейке. Другими словами, изобразить в Excel прототип новой базы данных — минутное дело, которое можно поручить даже самому неквалифицированному персоналу.

К сожалению, одних только таблиц с исходными данными для создания базы данных здесь недостаточно. Необходимо задать структуру таблиц, определить имена и форматы полей, определить ключевые поля или индексы. Подобные описания нужно сделать для каждой таблицы. Описать структуру полей таблицы проще всего сразу в виде запроса SQL:

CREATE TABLE employees (  
  id tinyint(4) DEFAULT '0' NOT NULL AUTO_INCREMENT,  
  first varchar(20),  
  last varchar(20),  
  address varchar(255),  
  position varchar(50),  
  PRIMARY KEY (id),  
  UNIQUE id (id)  
);  

В приведенном примере по данному SQL-запросу будет создана таблица employees (служащие) с пятью полями, задающими идентификатор (id), имя, фамилию, адрес и должность. При этом идентификатор является первичным уникальным ключом таблицы. Значение такому ключу может присваиваться автоматически при добавлении новой записи.

Структура SQL-запроса для создания таблицы проста. Кроме ключевого слова CREATE TABLE, указывается имя таблицы, за которым в скобках через запятую перечисляются идентификаторы полей и их форматы. Для индексируемой таблицы, кроме того, указывается выражение для основного и дополнительных ключей.

Непосредственно добавление новых записей в таблицу выполняется с помощью следующего SQL-запроса:

INSERT INTO employees VALUES (  
  1,  
  'Bob',  
  'Smith',  
  '128 Here St, Cityname',  
  'Marketing Manager'  
);  

Не составляет труда автоматизировать формирование подобных запросов, используя в качестве источника информации ячейки рабочих листов Excel. Если представить структуру полей таблицы в виде списка, показанного на рис. 1, то процедура формирования запроса CREATE TABLE может выглядеть, как показано в листинге 1.

Создание явного формализованного дескриптора для каждой таблицы целесообразно по многим причинам. Список полей, показанный на рис. 1, может служить не только для формирования запроса CREATE TABLE. Он понадобится и для запросов на добавление, удаление и изменение записей. Но самое главное, без него не обойтись при формировании сложных запросов SELECT, позволяющих комбинировать данные из нескольких таблиц, сортировать и делать все то, ради чего создан язык запросов, да и сама реляционная модель базы данных. Кроме того, части подобного списка легко копировать при проектировании дескриптора новой таблицы, например при наличии одноименных полей, используемых в качестве полей связи.

В начало В начало

Подготовка источника информации

В общем случае реляционная база данных состоит из нескольких таблиц, которые проектируются в процессе, известном как нормализация. Если все необходимые таблицы снабдить дескрипторами полей и все это поместить в рабочую книгу Excel, то такая книга может служить полным описанием базы данных. Совсем как MDB-файл Microsoft Access. Конечно, описание или модель не есть собственно база данных, но этого и не требуется. Индексы построит MySQL, который и обеспечит все необходимые действия по «разрезанию» и «склеиванию» таблиц по запросам пользователя.

Если же подойти к делу профессионально, то необходимо формализовать макет базы данных, построенный на листах книги Excel в виде классов и коллекций объектов. Например, класс Table (дескриптор таблицы) может выглядеть, как показано в листинге 2.

Как видно из вышеприведенного примера, одним из свойств объекта Table является коллекция объектов Fields, свойствами которых будут идентификаторы и форматы полей. Сама же база данных и есть коллекция объектов Table.

Имея такую конструкцию, не составит труда сгенерировать любые SQL-запросы. Это все равно, что обращаться к свойствам объектов реальной базы данных с предопределенной разработчиками объектной моделью.

Чтобы создать реальную коллекцию объектов, достаточно просмотреть все листы рабочей книги и извлечь существенную информацию (см. листинг 3).

В этом примере предполагается, что дескрипторы таблиц находятся на листах книги с именами, оканчивающимися на «_prop» (от слова property). Создание подчиненной коллекции Fields выполняется с помощью процедуры FieldsCollectionSet, практически не отличающейся от приведенной выше процедуры генерации SQL-запроса CREATE TABLE.

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

В начало В начало

Пакеты SQL-запросов

Выше было показано, как можно построить модель будущей базы данных. Были созданы и предпосылки для автоматической генерации SQL-запросов. Принимая во внимание, что для создания каждой таблицы и для добавления каждой новой записи требуется отдельное SQL-предложение, можно констатировать, что, еще не начиная работать с сервером, мы уже имеем солидный потенциал SQL-запросов. В системе UNIX для подобных ситуаций используется основная программа СУБД MySQL, которая допускает исполнение целого пакета запросов, сохраненных в так называемых dump-файлах. Если бы у нас имелся доступ к командной строке UNIX, то подобный процесс выглядел бы так:

mysql -u root mydb < mydb.dump

В данном случае mydb.dump — текстовый файл, содержащий последовательность SQL-предложений, начинающихся с новой строки.

Поскольку обычно владелец сервера не предоставляет доступа к командной строке UNIX, основные средства работы с MySQL остаются недосягаемыми для удаленного клиента. Впрочем, не очень-то и хотелось. Зачем осваивать Telnet, если имеется любимый обозреватель и PHP. Поставим и решим ключевую задачу проекта — создадим на сервере не простой обработчик SQL-запросов, а обработчик пакетов. Это уже серьезная задача, и следует подходить к ней, вооружившись всей мощью ООП.

Итак, создадим класс для обработки пакетов (см. листинг 4).

Если уж создается настоящий класс, то кроме решения основной задачи (выполнения пакетов SQL–запросов) неплохо было бы наделить его несколькими полезными возможностями. Во-первых, предусмотрено два варианта получения запросов: первый — обычная строковая переменная $sql_query, второй — имя текстового файла, содержащего пакет запросов $sql_file. Следовательно, можно реализовать обработку dump-файлов — так же, как это делается с помощью командной строки UNIX.

Ассоциативный массив $list_names — это еще одна дополнительная возможность. С его помощью можно заменить имена полей в результирующей таблице на нечто более приятное для пользователя, например, вывести вместо невыразительного «id» наименование типа «табельный номер».

Понять, как работает данный класс, можно на примере наиболее часто адресуемого с помощью URL серверного скрипта, который, благодаря наличию класса, становится совсем небольшим (листинг 5).

Особую прелесть приведенному PHP-скрипту придает то, что он не только выполняет произвольный пакет SQL-запросов, но и выводит результирующие таблицы, оформленные, как говорится, вполне дружелюбно. Имена полей базы данных заменяются на содержательные заголовки колонок; четные и нечетные строки оформляются разными стилями; числовые данные выравниваются вправо, а символьные — влево.

В том случае, если пакет SQL-запросов не предусматривает вывода таблиц, во избежание появления пустого экрана выводится сообщение об окончании работы.

В начало В начало

Создание комплекса инструментальных средств

В предыдущих разделах было показано, как создавать все необходимые средства для удаленной работы с базой данных MySQL, не прибегая к установке собственного сервера Apache. Дело за малым: необходимо связать все эти средства воедино. Для этого на клиентской стороне осталось только реализовать генератор SQL-запросов и «подцепить» компоненты, способные передавать пакеты запросов на сервер по протоколам Internet.

Если опять идет речь о клиентской стороне, то можно быть уверенным, что все необходимое найдется у Microsoft. Действительно, кто лучше Microsoft сумеет предоставить изощренный SQL Builder? На рис. 2 представлена форма, целиком заимствованная из примеров разработки приложений для баз данных, входящих в комплект поставки Visual Basic.

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

В качестве канала связи с Internet можно использовать не один, а целых два инструмента от Microsoft. Прежде всего это Internet Explorer.

Самый простой путь — воспользоваться предусмотренной возможностью обработки сервером текстовых файлов, содержащих пакеты SQL-запросов. Для этого достаточно сохранить сформированные запросы в промежуточном файле на локальном компьютере, а в клиентской HTML-странице добавить в форму еще одно поле <INPUT> с атрибутом TYPE=FILE. Об остальном позаботится сам обозреватель.

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

В результате получается замечательная система, функционирующая как единое целое и не содержащая никаких лишних перемещений данных и промежуточных файлов. На рис. 3 показано, как выглядит окно Internet Explorer, открытое из Excel с данными, загруженными непосредственно из конструктора SQL запросов.

Вторым инструментом для передачи пакетов SQL-запросов является непосредственно Microsoft Excel. Точнее, имеющийся в приложении механизм Web-запросов, который реализует протоколы GET и POST вообще без участия обозревателя и HTML-документов.

Для создания Web-запроса Excel необходимо подготовить простейший файл формата IQY, который выглядит следующим образом:

WEB
1
http://www.yourname.com/sqlbatch.php3
user=[“user”,”User”]& _
password=[“password”,”Password”]& _
database=[“database”,”Database”]& _
sql_query=[“sql_query”,”SQL Query”]

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

При использовании описанного способа самым замечательным является то, что результат выполнения серверного скрипта будет помещен непосредственно в указанное место листа Excel. Таким образом, круг замыкается. Исходные данные для базы данных MySQL готовятся в Excel. С помощью VBA Excel формируется пакет SQL-запросов, который сохраняется в ячейках и пересылается для исполнения посредством Excel Web Query. Результат обработки пакета СУБД MySQL, (а это, как правило, производные таблицы), помещается тут же в рабочей книге. При этом никакие драйверы ODBC не используются, клиентские наборы данных (recordsets) не создаются и никаких программных кодов не требуется.

В начало В начало

Заключение

По прочтении настоящей статьи легко убедиться, каких замечательных результатов можно добиться, не противопоставляя платформы UNIX и Windows, а наоборот, используя их совместно, выбирая из каждой платформы простые и удобные решения. Судите сами. Задача администрирования базы данных MySQL может быть решена и обычно решается только на одной платформе. Разрабатываются мощные комплексы серверных приложений на языке PHP или C++ / UNIX, чтобы предоставить удаленному клиенту возможность формировать произвольные SQL-запросы с помощью только обозревателя Web-страниц. Представьте себе, насколько это сложно, если с помощью обычных элементов управления, используемых в формах Web-страниц, попытаться реализовать нечто подобное SQL Builder, показанному на рис. 2. Да и сама работа по формированию SQL запроса в интерактивном режиме в Internet вряд ли доставит пользователю удовольствие.

Можно работать с удаленной базой данных MySQL только под управлением Windows. Для этого разработан драйвер ODBC, который поставляется вместе с пакетом MySQL. Но здесь также нужно писать и отлаживать довольно сложный программный код, который не идет ни в какое сравнение с простыми и элегантными кодами PHP/MySQL. Кроме того, доступ к базе данных из Windows с помощью ODBC не позволяет оценить реальный вид результата выполнения запросов при предстоящей работе пользователя с обозревателем, а ведь именно для этой цели создаются базы данных на серверах Internet.

К сожалению, продемонстрированный симбиоз технологий требует от разработчика более широкого объема знаний, чем обычно, поскольку в одном проекте собраны и Visual Basic, и VBA, и Web-программирование в UNIX, и базы данных, причем все это с классами — как на клиентской, так и на серверной стороне. А что может быть интереснее для настоящего программиста?!

 

КомпьютерПресс 6'2001

1999 1 2 3 4 5 6 7 8 9 10 11 12
2000 1 2 3 4 5 6 7 8 9 10 11 12
2001 1 2 3 4 5 6 7 8 9 10 11 12
2002 1 2 3 4 5 6 7 8 9 10 11 12
2003 1 2 3 4 5 6 7 8 9 10 11 12
2004 1 2 3 4 5 6 7 8 9 10 11 12
2005 1 2 3 4 5 6 7 8 9 10 11 12
2006 1 2 3 4 5 6 7 8 9 10 11 12
2007 1 2 3 4 5 6 7 8 9 10 11 12
2008 1 2 3 4 5 6 7 8 9 10 11 12
2009 1 2 3 4 5 6 7 8 9 10 11 12
2010 1 2 3 4 5 6 7 8 9 10 11 12
2011 1 2 3 4 5 6 7 8 9 10 11 12
2012 1 2 3 4 5 6 7 8 9 10 11 12
2013 1 2 3 4 5 6 7 8 9 10 11 12
Популярные статьи
КомпьютерПресс использует