Стандартизация структуры базы данных Fb2Librarian

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

Оригинальный текст был размещен на Google Wave, но в связи с неясным будущем проекта, был перенесен сюда.

Первоначальный вариант вместе с комментариями пока доступен по адресу https://wave.google.com/wave/waveref/googlewave.com/w+Gdjs6CXzC

Начнем с таблицы AUTOR

Таблица AUTOR


CREATE TABLE AUTOR 
(
    AUTORID       INTEGER NOT NULL,
    FIRSTNAME     VARCHAR(40) NOT NULL,
    MIDNAME       VARCHAR(40) NOT NULL,
    LASTNAME      VARCHAR(40) NOT NULL,
    NICKNAME      VARCHAR(40) NOT NULL,
    EMAIL         VARCHAR(125),
    HOMEPAGE      VARCHAR(125),
    PHOTO         BLOB SUB_TYPE 1 SEGMENT SIZE 1024,
    INFO          BLOB SUB_TYPE 1 SEGMENT SIZE 1024,
    BIBLIOGRAPHI  BLOB SUB_TYPE 1 SEGMENT SIZE 1024
)

Как я уже говорил - поля FIRSTNAME, MIDNAME, LASTNAME и NICKNAME, в стандарте FictionBook не являются обязательными и могут отсутствовать. В базе приходится заполнять их пустыми строками, к тому же, длина этих полей в стандарте не ограниченна, но на практике встречаются строки с длинной более 40 символов. В связи с этим предлагаю увеличить длину до 254.

Поля EMAIL и HOMEPAGE рассчитаны на одно значение, в стандарте это не так, но из за совместимости с версией 1.5.1 пока оставим все как есть.

Изменить подтип у поля PHOTO на BINARY:

PHOTO         BLOB SUB_TYPE 0 SEGMENT SIZE 1024

Стандарт с версии 2.2 предусматривает поле id (идентификатор автора, присваивается библиотекой), возможно стоит добавить новое поле LIBRARYID VARCHAR(40) (или AUTOR_GUID)

Индексы AUTOR_IDX0, AUTOR_IDX1 и AUTOR_IDX2 в текущем виде никем не используются, хотя тут я могу ошибаться, предлагаю заменить их на:

CREATE INDEX AUTOR_IDX0 ON AUTOR COMPUTED BY (UPPERCASE(FIRSTNAME));
CREATE INDEX AUTOR_IDX1 ON AUTOR COMPUTED BY (UPPERCASE(LASTNAME));
CREATE INDEX AUTOR_IDX2 ON AUTOR COMPUTED BY (UPPERCASE(NICKNAME));

Таблица AUTOR_SYNONIMS

CREATE TABLE AUTOR_SYNONIMS 
(
    ID         INTEGER NOT NULL,
    AUTORID    INTEGER NOT NULL,
    LASTNAME   VARCHAR(40) CHARACTER SET NONE NOT NULL,
    FIRSTNAME  VARCHAR(40) CHARACTER SET NONE NOT NULL,
    MIDNAME    VARCHAR(40) CHARACTER SET NONE NOT NULL,
    NICKNAME   VARCHAR(40) CHARACTER SET NONE NOT NULL,
    INLIST     T_BOOLEAN_INT NOT NULL /* T_BOOLEAN_INT = SMALLINT DEFAULT 0 NOT NULL CHECK (VALUE IN (0,1)) */
)

Всё тоже что и выше, увеличить длину до 254 Индексы AUTOR_SYNONIMS_IDX1 и AUTOR_SYNONIMS_IDX2 заменить на:

CREATE INDEX AUTOR_SYNONIMS_IDX0 ON AUTOR_SYNONIMS COMPUTED BY (UPPERCASE(firstname));
CREATE INDEX AUTOR_SYNONIMS_IDX1 ON AUTOR_SYNONIMS COMPUTED BY (UPPERCASE(lastname));

Таблица BOOK

CREATE TABLE BOOK 
(
    BOOKID          INTEGER NOT NULL,
    GENRELIST       VARCHAR(1024) CHARACTER SET NONE,
    ANNOTATION      VARCHAR(4096) CHARACTER SET NONE,
    AUTORLIST       VARCHAR(254) CHARACTER SET NONE,
    KEYWORDS        VARCHAR(254) CHARACTER SET NONE,
    BOOKNAME        VARCHAR(252) CHARACTER SET NONE,
    DATEVALUE       DATE,
    DATEVISIBLE     VARCHAR(25) CHARACTER SET NONE,
    LANG            VARCHAR(10) CHARACTER SET NONE,
    COVERPAGE       BLOB SUB_TYPE 1 SEGMENT SIZE 1024 CHARACTER SET NONE,
    SRCLANG         VARCHAR(10) CHARACTER SET NONE,
    "SEQUENCE"      VARCHAR(125) CHARACTER SET NONE,
    SEQNUMBER       VARCHAR(4) CHARACTER SET NONE,
    SEQUENCEID      INTEGER,
    DI_PROGUSED     VARCHAR(254) CHARACTER SET NONE NOT NULL,
    DI_DATEVALUE    DATE,
    DI_DATEVISIBLE  VARCHAR(14) CHARACTER SET NONE NOT NULL,
    DI_SRCURL       VARCHAR(254) CHARACTER SET NONE NOT NULL,
    DI_SRCOCR       VARCHAR(254) CHARACTER SET NONE NOT NULL,
    OLDID           VARCHAR(254) CHARACTER SET NONE,
    DI_VERSION      VARCHAR(10) CHARACTER SET NONE NOT NULL,
    DI_HISTORY      BLOB SUB_TYPE 1 SEGMENT SIZE 1024 CHARACTER SET NONE,
    PI_BOOKNAME     VARCHAR(254) CHARACTER SET NONE NOT NULL,
    PI_PUBLISHER    VARCHAR(254) CHARACTER SET NONE NOT NULL,
    PI_CITY         VARCHAR(50) CHARACTER SET NONE NOT NULL,
    PI_YEAR         VARCHAR(10) CHARACTER SET NONE NOT NULL,
    PI_ISBN         VARCHAR(125) CHARACTER SET NONE NOT NULL,
    CUSTOMINFO      BLOB SUB_TYPE 1 SEGMENT SIZE 1024 CHARACTER SET NONE,
    TEXT            BLOB SUB_TYPE 1 SEGMENT SIZE 1024 CHARACTER SET NONE,
    DATEIN          TIMESTAMP,
    DATEUPDATED     TIMESTAMP,
    MYID            VARCHAR(1024) CHARACTER SET NONE,
    EXT             VARCHAR(5) CHARACTER SET NONE,
    FILESIZE        FLOAT,
    FILENAME        VARCHAR(4096) CHARACTER SET NONE,
    FILEPATH        VARCHAR(1024) CHARACTER SET NONE,
    FILEDATE        TIMESTAMP,
    USERID          INTEGER,
    USERNAME        VARCHAR(40) CHARACTER SET NONE
)

Поля DI_PROGUSED, DI_SRCURL, DI_SRCOCR, PI_BOOKNAME, PI_PUBLISHER, PI_CITY, PI_YEAR и PI_ISBN не являются обязательными, т. е. и тут убираем NOT NULL.

Изменить подтип у полей COVERPAGE и TEXT на BINARY:

COVERPAGE       BLOB SUB_TYPE 0 SEGMENT SIZE 1024,
TEXT            BLOB SUB_TYPE 0 SEGMENT SIZE 1024

Индексы:

CREATE INDEX BOOK_IDX1 ON BOOK (BOOKNAME);
CREATE INDEX BOOK_IDX3 ON BOOK ("SEQUENCE");
CREATE INDEX BOOK_IDX4 ON BOOK (SEQNUMBER);

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

С другой стороны, существует индекс по полю DATEIN и отсутствует для поля DATEUPDATED, надо бы добавить:

CREATE INDEX BOOK_IDX7 ON BOOK (DATEUPDATED);

Это позволит в одном запросе выбрать все изменённые/добавленные книги за определённый период.

Таблица SEQUENCES

CREATE TABLE SEQUENCES 
(
    SEQUENCEID  INTEGER NOT NULL,
    "SEQUENCE"  VARCHAR(125) CHARACTER SET NONE NOT NULL
)

Стандарт FictionBook допускает наличие вложенных серий, этого же можно добиться введя в таблицу SEQUENCES дополнительное поле PARENTID:

PARENTID    INTEGER

Таблица BOOK_SEQUENCE

CREATE TABLE BOOK_SEQUENCE 
(
    BOOKID      INTEGER NOT NULL,
    SEQUENCEID  INTEGER NOT NULL
)

По моему мнению, в ней не хватает поля SEQNUMBER

Таблицы DOCAUTHOR и TRANSLATE

CREATE TABLE DOCAUTHOR 
(
    DOCAUTORID  INTEGER NOT NULL,
    FIRSTNAME   VARCHAR(40) NOT NULL,
    MIDNAME     VARCHAR(40) NOT NULL,
    LASTNAME    VARCHAR(40) NOT NULL,
    NICKNAME    VARCHAR(40) NOT NULL,
    EMAIL       VARCHAR(125),
    HOMEPAGE    VARCHAR(125),
    PHOTO       BLOB SUB_TYPE 1 SEGMENT SIZE 1024,
    INFO        BLOB SUB_TYPE 1 SEGMENT SIZE 1024
)

CREATE TABLE "TRANSLATE" 
(
    TRANSLATEID  INTEGER NOT NULL,
    FIRSTNAME    VARCHAR(40) NOT NULL,
    MIDNAME      VARCHAR(40) NOT NULL,
    LASTNAME     VARCHAR(40) NOT NULL,
    NICKNAME     VARCHAR(40) NOT NULL,
    EMAIL        VARCHAR(125),
    HOMEPAGE     VARCHAR(125)
)

та же беда, что и у таблицы AUTOR, расширяем поля до 245.

Индексы DOCAUTHOR_IDX1, DOCAUTHOR_IDX2, DOCAUTHOR_IDX3 и индексы TRANSLATE_IDX1, TRANSLATE_IDX2 и TRANSLATE_IDX3, заменить на эквивалентные с COMPUTED BY

В таблице DOCAUTHOR изменить подтип у поля PHOTO на BINARY:

PHOTO         BLOB SUB_TYPE 0 SEGMENT SIZE 1024

Таблица GRADE

CREATE TABLE GRADE 
(
    BOOKID  INTEGER NOT NULL,
    POINT   INTEGER NOT NULL
)

мне в ней очень не хватает поля USERID :)

Таблица VERINFO

CREATE TABLE VERINFO 
(
    "VERSION"          VARCHAR(15),
    MODE               SMALLINT,
    MOUNT              VARCHAR(4096),
    FILENAMINGPATTERN  VARCHAR(252),
    DB_ID              VARCHAR(40) CHARACTER SET ASCII NOT NULL
)

предлагаю узаконить поле DB_ID (введенное в обиход уважаемым Tanone).

Далее, в целях сохранения совместимости различных программ работающих с базой данных Библиотекаря, предлагаю все дополнительные поля выносить в отдельные таблицы. При использовании FOREIGN KEY на базовые таблицы, обязательно указание ON DELETE [CASCADE | SET NULL | NONE]

Для разрешения конфликтов в наименовании объектов (таблиц, индексов, процедур и т.д.), предлагаю присвоить каждой заинтересованной стороне уникальный префикс и использовать его в качестве обязательной части имени, например так:

FBN$TABLE_NAME

Длину префикса предлагаю ограничить 3 символами. Список всех префиксов можно будет хранить на форуме, у меня на сайте или в другом доступном месте. Для ведения версионности дополнений, предлагаю ввести таблицу SCHEMAINFO.

Таблица SCHEMAINFO

CREATE TABLE SCHEMAINFO
(
    PREFIX          VARCHAR(3) NOT NULL PRIMARY KEY,
    "VERSION"       VARCHAR(15)
)

Список жанров

Добавить в стандартный список жанров:

INSERT INTO GENRE (GRGENREID, GENREID, RUSNAME, ENGNAME) VALUES ('other', 'other', 'Прочее', 'Other');

INSERT INTO GRGENRE (GRGENREID, NAME, ENGNAME) VALUES ('other', 'Прочее', 'Other');

История изменений

Версия 3

  • Добавлен индекс по полю DATEUPDATED в таблице BOOK
  • В таблицу SEQUENCES добавленно поле PARENTID
  • Полям FIRSTNAME, MIDNAME, LASTNAME и NICKNAME возвращено первоначальное ограничение NOT NULL
  • UNICODE_FSS используется как кодировка по умолчанию
  • Все вызовы функции UPPERCASE заменены на вызовы встроенной функции UPPER
  • Схеме присвоен номер 1.6.3.0

Версия 2

  • Изменение подтипа для полей BOOK.TEXT, BOOK.COVERPAGE, AUTOR.PHOTO и DOCAUTHOR.PHOTO

Версия 1

  • Первоначальная версия
blog comments powered by Disqus