Те сомнения, которые не разрешает теория, разрешит тебе практика.
Людвиг Фейербах
Редактор структуры базы данных является одним из важнейших инструментов разработки информационной системы, которая должна хранить и обрабатывать данные. В этом разделе подробно разбираются возможности встроенного в My Visual Database (MVDB) редактора структуры БД. Первый опыт работы с редактором можно получить при создании проекта “Библиотека v.1.0”, теперь настало время подробно изучить редактор и все его возможности.
Рассматриваемый редактор простой и удобный, он позволяет описывать сущности модели данных в представлении, не привязанном к особенностям реализации в конкретной СУБД. Именно поэтому возможно быстрое переключение между SQLite и MySQL. Визуально сущности представлены таблицами, в которых отображаются название полей и типы хранимых в них данных. Таблицы заполняют рабочую область редактора, имеют фиксированный размер. Связи между сущностями отображаются в отдельном представлении – схеме данных.
Правила и соглашения
Для правильного функционирования программы, а также для удобства сопровождения и документирования, при создании имён для таблиц и полей рекомендуется придерживаться следующих правил и соглашений.
- не использовать для названий ключевые слова или названия функций SQL;
- в названиях использовать только строчные латинские буквы, цифры и символ подчёркивания;
- если название состоит из нескольких слов, разделять их символом подчёркивания;
- названия должны быть осмысленными и отражать назначение таблицы или поля;
- названия должны быть как можно короче;
- используйте для названий английские слова
Такой стиль оформления позволит вам повысить производительность системы, упростить написание фильтров, вычисляемых полей и SQL-запросов.
Ключевые слова SQLite – https://www.sqlite.org/lang_keywords.html
Ключевые слова MySQL – https://dev.mysql.com/doc/refman/5.7/en/keywords.html
Выбор СУБД
Программы, создаваемые с помощью MVDB, могут работать с двумя СУБД – SQLite или MySQL. По умолчанию новые проекты создаются для SQLite, но вы в любой момент можете изменить тип используемой системы управления базами данных. Для этого выберите вкладку “Таблицы базы данных” (1) и нажмите кнопку выбора базы (2), в заголовке которой отображается текущая СУБД. В открывшемся окне вы можете выбрать СУБД SQLite (3) или MySQL (4).
Файл базы данных SQLite sqlite.db создаётся рядом с файлом программы. Если вы удалите этот файл, то он будет создан при первом запуске приложения. Удаление базы обычно производят в двух случаях: чтобы очистить базу от всех данных и чтобы разрешить настройку свойства поля “Обязательное”.
Работа с СУБД MySQL будет рассматривается в рамках создания LOW-CODE программ, так как потребует больших усилий и специальных знаний для развертывания информационной системы. Теоретически NON-CODE программы тоже могут работать с СУБД MySQL, но на практике все же требуется написание минимального кода для обеспечения комфортной и безопасной работы. Также потребуются знания языка запросов SQL, так как использование MySQL подразумевает более сложную логику работы и требования к производительности, обеспечить которую только инструментами MVDB не получится.
Создание таблиц
Перейдите на вкладку “Таблицы базы данных”, нажмите кнопку ”Новая таблица” (1). В появившемся окне наберите название таблицы (2) и нажмите кнопку “ОК” (2) для сохранения результата.
Рекомендую создавать таблицы по степени важности и частоте возможной модификации: в первую очередь таблицы для хранения журналов, документов, затем сложных справочников, и в последнюю очередь – простых справочников. Это не совсем удобно при создании ссылочных полей, зато экономит время при отладке и доработки проекта.
Чекер “Таблица является словарём” в некоторых случаях помогает внутренним алгоритмам MVDB строить запросы при конструировании выборок табличных данных при глубине вложенности зависимостей таблиц больше двух, но я рекомендую не полагаться на этот чекер, а использовать в этих случаях вычисляемые поля.
При создании таблицы автоматически создаётся поле id для хранения первичного ключа. Это поле не отображается в конструкторе базы данных, но доступно для отображения в табличном представлении с помощью компонента Table Grid.
Изменять значение первичного ключа не рекомендуется, поэтому данное поле не появляется в списках полей – источников данных у компонентов редактирования.
Создание полей для хранения данных
Для создания поля нажмите кнопку “Новое поле” (1) и выберите тип (2)
В открывшемся окне необходимо ввести название поля (1) с учетом правил и соглашений (см. п 4.1.). При необходимости вы можете изменить тип поля, выбрав его из списка (2).
В секции “Дополнительно” находятся поле для ввода значения по умолчанию (1) и чекер запрета пустых значений (2). Значение по умолчанию будет добавлено в новую запись, если на форме редактирования нет компонента для ввода значения для данного поля, а поле имеет признак “Обязательное”.
Таблица 1. Типы полей
| Тип поля | Примечание |
|---|---|
| Текст | Для хранения текста произвольной длины. Максимальная длина ограничена константой SQLITE_MAX_LENGTH = 109 |
| Целое число | Для хранения целых чисел в диапазоне [-9223372036854775808 .. 9223372036854775807] При этом необходимо учитывать, что диапазон целых чисел, с которым работает MVDB, -2147483648..2147483647. Числа, выходящие за его пределы, при отображении средствами MVDB будут вызывать ошибку “is not a valid integer value“. |
| Вещественное число | Для хранения чисел с плавающей запятой. |
| Деньги | Для хранения чисел с плавающей запятой с дополнительными настраиваемыми правилами отображения и ввода, настроенными по умолчанию для отображения двух знаков после запятой.  Можно добавить текст до или после числа, указать количество знаков после запятой и наличие разделителя (верхней запятой) между тысячами. |
| Да/Нет | Для хранения флагов (логических данных). Технически используются целочисленные значения 0 и 1. |
| Дата/Время | Данные о дате и времени, которые хранятся в базе в виде строки. |
| Дата | В базу записывается только дата, время обнуляется. В табличном представлении отображается только дата. |
| Время | В базу записывается только время (дата записывается как значение 1899-12-30); в табличном представлении отображается только время. |
| Изображение | Предназначен для хранения изображения, как в самой базе, так и ссылки на файл. |
| Файл | Предназначен для хранения любых файлов, как в самой базе, так и ссылки на файл. |
| Счётчик | Хранит целочисленное значение, которое автоматически увеличивается при добавлении новой записи. Тип этого поля не меняется после создания. Можно задать начальное значение. Имя поля фиксированное: record_count. У таблицы может быть только одно поле с типом “Счётчик”. |
При создании полей с типом Изображение и Файл автоматически создаётся служебное поле <имя поля>_filename для хранения имени загруженного файла (если файл или картинка загружаются в базу данных) или ссылки на файл (если в базе хранится только ссылка на файл). Это поле не отображается в редакторе, но доступно через SQL-запросы.
Создание связи
Рассмотрим добавление связи на примере. Пусть имеются две таблицы: master (2) и slave (1), причём несколько записей в таблице slave ссылаются на одну из записей в таблице master. Чтобы добавить связь “один-ко-многим” нажмите кнопку “Новое поле”, в выпадающем списке выберите пункт “Связь” (3)
Выберите таблицу, на которую нужно сделать ссылку. В нашем случае это таблица master (1). Если при удалении записи в таблице master необходимо удалять все зависимые записи в таблице slave, установите чекер “Удалять записи…” (2). Если ссылка на главную таблицу является обязательной (а в большинстве случаев так оно и есть), установите чекер “Обязательное” (3). Нажмите кнопку “ОК” (4) для сохранения результата.
Название поля формируется автоматически: id_<имя главной таблицы>. Созданное поле является внешним ключом.
Можно добавить несколько ссылок на одну и ту же таблицу, но только одна из них будет использоваться в программе конструктором построения выборки данных. Для выборки данных по дополнительным полям необходимо дополнительно создать вычисляемое поле.
При создании связей есть один нюанс, который проявляется, если вы добавляете связь в уже существующую базу. А существовать она начинает (автоматически создается) при первом запуске вашего приложения.
Так вот, при попытке добавить связь в уже существующую базу данных и укажете, что данное поле является обязательным, то система потребует указать значение поля по умолчанию.
Если вы только начали создание структуры БД, то проще удалить файл sqlite.db, но если вы проводите модификацию работающей программы, то удалять данные не стоит.
Казалось бы, можно указать какое-нибудь фиктивное значение, например ноль, и вопрос закрыт. Но в этом случае поле не добавится, а отобразится ошибка:
Однако, если эту операцию проделать в два этапа, то все закончится успешно:
Сначала добавляем поле со снятым флагом “Обязательное”.
Затем редактируем поле и ставим значение по умолчанию и флаг.
Результат: вы добавили обязательное ссылочное поле без удаления базы данных.
Вычисляемые поля
Вычисляемое поле позволяет отображать данные в нужном формате, отображать результаты вычисления и преобразований, которые выполняются на стороне СУБД, создавать выборки данных по условию. Для создания вычисляемых полей требуется знание SQL, данная тема подробно рассматривается при создании LOW-CODE приложений.
По сути вычисляемое поле – это фрагмент запроса к БД, написанный на языке SQL. Фрагмент имеет имя, благодаря чему он может использоваться при настройке компонентов (таблицы, выпадающего списка) наравне с полями реальных таблиц. Но за эту простоту приходится платить снижением эффективности работы, так как скорость выборки данных может существенно снижаться при наличии у таблицы вычисляемых полей. Также может увеличиться время открытия формы редактирования в следствии того, что алгоритмы MVDB при инициализации формы редактирования производя выборку данных по всем полям, включая вычисляемые, вне зависимости от того, есть ли на форме соответствующие им компоненты редактирования или нет.
Редактирование структуры БД
Вы можете в любой момент изменить структуру используемой проектом базы данных.
Перед изменением структуры необходимо закрыть все приложения, которые используют редактируемую базу.
Для изменения названия таблицы нажмите кнопку с изображением карандаша (1), в появившемся окне введите новое название таблиц (2) и сохраните изменения (3).
Если на редактируемую таблицу имеются ссылки, они все будут автоматически изменены.
Автокорректировка ссылочных полей работает только, если вы уже сохранили созданный проект.
При изменении типа поля содержимое поля уничтожается, так как для этого производятся две операции: удаление поля и добавление нового. Если при редактировании устанавливается флаг “Обязательное”, а таблица с данными уже создана, то программа потребует ввести значение в поле “Значение по умолчанию”, для того, чтобы, в случае необходимости, заполнить пустые поля указанным значением.
Для удаления таблицы нажмите кнопку удаления таблицы (1) и подтвердите удаление (2).
Если база уже содержит данные, а на удаляемую таблицу есть ссылки от других таблиц, то необходимо сначала удалить ссылки на удаляемую таблицу и только потом – саму таблицу.
Изменение структуры базы данных не приведёт к автоматическому изменению настройки свойств визуальных компонент, использующих изменяемые структуры. Эти изменения необходимо выполнить самостоятельно.
Вы можете изменить последовательность расположения полей в таблице, используя перетягивание строк мышкой. Для этого выберите поле для перемещения (1) и, удерживая нажатой левую кнопку мыши, укажите новую позицию (2). Отпустите кнопку мыши.
Изменить взаимное расположение таблиц также возможно. Для этого можно отредактировать текстовый файл “tables.ini” который содержит сведения о структуре базы данных:
Схема данных
Для получения визуализации схемы данных нажмите на кнопку “Показать схему базы данных” (1). В открывшемся окне отображается схема (2), элементы которых можно перемещать по форме для удобства восприятия.
Таблица 2. Условные обозначения
| Обозначение | Описание |
|---|---|
 | Первичный ключ (Primary key) |
 | Внешний ключ (Foreign key) с каскадным удалением зависимых записей |
 | Внешний ключ (Foreign key) с контролем наличия зависимых записей |
 | Поле с данными. Допускается значение Null. |
 | Поле с данными, обязательное для заполнения. Значение Null не допускается |
 | Вычисляемое поле |
Настройка подключения
По умолчанию программа настроена на подключение к базе данных, которая находится рядом с исполняемым файлом, в файле с названием sqlite.db. Настройки подключения можно изменить с помощью пункта меню “Инструменты” – “Настройка” на главной форме запущенного приложения.
На вкладке “Файл базы данных” можно выбрать два варианта размещения файла базы данных: стандартный (1) или произвольный (2). Во втором случае можно указать как локальные файлы компьютера, так и файлы, расположенные в сети.
По умолчанию настройки хранятся в файле settings.ini, который располагается рядом с программой (1). Альтернативный вариант хранения настроек (2) используется в случае, если программа размещается в папке Program Files. Это сделано для того, чтобы защита Windows не препятствовала программе редактировать файл настройки.
А теперь пора приступить к созданию приложения, в котором мы сможем использовать несколько связанных между собой таблиц. Либо углубимся в понимание языка SQL и его применения при работе с СУБД SQLite.
Класс