Как развернуть схему сущностей-атрибутов MySQL
Мне нужно создать таблицы, в которых хранятся все метаданные файлов (то есть имя файла, автор, название, дата создания) и пользовательские метаданные (которые были добавлены в файлы пользователями, например CustUseBy, CustSendBy). Количество настраиваемых полей метаданных не может быть задано заранее. Действительно, единственный способ определить, что и сколько пользовательских тегов было добавлено в файлы, - это изучить то, что существует в таблицах.
Чтобы сохранить это, я создал базовую таблицу (имеющую все общие метаданные файлов), таблицу Attributes (содержащую дополнительные, необязательные атрибуты, которые могут быть установлены в файлах) и таблицу FileAttributes (которая назначает значение для атрибута для файла).
CREAT TABLE FileBase (
id VARCHAR(32) PRIMARY KEY,
name VARCHAR(255) UNIQUE NOT NULL,
title VARCHAR(255),
author VARCHAR(255),
created DATETIME NOT NULL,
) Engine=InnoDB;
CREATE TABLE Attributes (
id VARCHAR(32) PRIMARY KEY,
name VARCHAR(255) NOT NULL,
type VARCHAR(255) NOT NULL
) Engine=InnoDB;
CREATE TABLE FileAttributes (
sNo INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
fileId VARCHAR(32) NOT NULL,
attributeId VARCHAR(32) NOT NULL,
attributeValue VARCHAR(255) NOT NULL,
FOREIGN KEY fileId REFERENCES FileBase (id),
FOREIGN KEY attributeId REFERENCES Attributes (id)
) Engine=InnoDB;
Пример данных:
INSERT INTO FileBase
(id, title, author, name, created)
VALUES
('F001', 'Dox', 'vinay', 'story.dox', '2009/01/02 15:04:05'),
('F002', 'Excel', 'Ajay', 'data.xls', '2009/02/03 01:02:03');
INSERT INTO Attributes
(id, name, type)
VALUES
('A001', 'CustomeAttt1', 'Varchar(40)'),
('A002', 'CustomUseDate', 'Datetime');
INSERT INTO FileAttributes
(fileId, attributeId, attributeValue)
VALUES
('F001', 'A001', 'Akash'),
('F001', 'A002', '2009/03/02');
Теперь проблема заключается в том, что я хочу показать данные следующим образом:
FileId, Title, Author, CustomAttri1, CustomAttr2, ...
F001 Dox vinay Akash 2009/03/02 ...
F002 Excel Ajay
Какой запрос будет генерировать этот результат?
Ответы
Ответ 1
В вопросе упоминается MySQL, и на самом деле эта СУБД имеет специальную функцию для такого рода проблем: GROUP_CONCAT(expr). Взгляните в справочное руководство по MySQL по группам по функциям. Функция была добавлена в MySQL версии 4.1. Вы будете использовать GROUP BY FileID в запросе.
Я не уверен, как вы хотите, чтобы результат выглядел. Если вы хотите, чтобы каждый атрибут, указанный для каждого элемента (даже если он не задан), будет сложнее. Однако это мое предложение о том, как это сделать:
SELECT bt.FileID, Title, Author,
GROUP_CONCAT(
CONCAT_WS(':', at.AttributeName, at.AttributeType, avt.AttributeValue)
ORDER BY at.AttributeName SEPARATOR ', ')
FROM BaseTable bt JOIN AttributeValueTable avt ON avt.FileID=bt.FileID
JOIN AttributeTable at ON avt.AttributeId=at.AttributeId
GROUP BY bt.FileID;
Это дает вам все атрибуты в том же порядке, что может быть полезно. Выход будет выглядеть следующим образом:
'F001', 'Dox', 'vinay', 'CustomAttr1:varchar(40):Akash, CustomUseDate:Datetime:2009/03/02'
Таким образом, вам нужен только один запрос БД, и вывод легко анализируется. Если вы хотите сохранить атрибуты в качестве реального Datetime и т.д. В БД, вам нужно будет использовать динамический SQL, но я останусь свободным от этого и сохранил значения в varchars.
Ответ 2
Если вы ищете что-то более пригодное для использования (и совместимое), чем результат группы-concat, попробуйте это решение ниже. Я создал несколько таблиц, очень похожих на ваш пример, чтобы это имело смысл.
Это работает, когда:
- Вам нужен чистый SQL-решение (без кода, без петель)
- У вас есть предсказуемый набор атрибутов (например, не динамический)
- Вы обновляете запрос, когда необходимо добавлять новые типы атрибутов.
- Вы бы предпочли результат, который может быть подключен к UNIONed или вложен в качестве подзаголовка
Таблица A (Файлы)
FileID, Title, Author, CreatedOn
Таблица B (Атрибуты)
AttrID, AttrName, AttrType [not sure how you use type...]
Таблица C (Files_Attributes)
FileID, AttrID, AttrValue
Традиционный запрос вытащил бы много избыточных строк:
SELECT * FROM
Files F
LEFT JOIN Files_Attributes FA USING (FileID)
LEFT JOIN Attributes A USING (AttributeID);
AttrID FileID Title Author CreatedOn AttrValue AttrName AttrType
50 1 TestFile Joe 2011-01-01 true ReadOnly bool
60 1 TestFile Joe 2011-01-01 xls FileFormat text
70 1 TestFile Joe 2011-01-01 false Private bool
80 1 TestFile Joe 2011-01-01 2011-10-03 LastModified date
60 2 LongNovel Mary 2011-02-01 json FileFormat text
80 2 LongNovel Mary 2011-02-01 2011-10-04 LastModified date
70 2 LongNovel Mary 2011-02-01 true Private bool
50 2 LongNovel Mary 2011-02-01 true ReadOnly bool
50 3 ShortStory Susan 2011-03-01 false ReadOnly bool
60 3 ShortStory Susan 2011-03-01 ascii FileFormat text
70 3 ShortStory Susan 2011-03-01 false Private bool
80 3 ShortStory Susan 2011-03-01 2011-10-01 LastModified date
50 4 ProfitLoss Bill 2011-04-01 false ReadOnly bool
70 4 ProfitLoss Bill 2011-04-01 true Private bool
80 4 ProfitLoss Bill 2011-04-01 2011-10-02 LastModified date
60 4 ProfitLoss Bill 2011-04-01 text FileFormat text
50 5 MonthlyBudget George 2011-05-01 false ReadOnly bool
60 5 MonthlyBudget George 2011-05-01 binary FileFormat text
70 5 MonthlyBudget George 2011-05-01 false Private bool
80 5 MonthlyBudget George 2011-05-01 2011-10-20 LastModified date
Этот коалесцирующий запрос (подход с использованием MAX) может объединить строки:
SELECT
F.*,
MAX( IF(A.AttrName = 'ReadOnly', FA.AttrValue, NULL) ) as 'ReadOnly',
MAX( IF(A.AttrName = 'FileFormat', FA.AttrValue, NULL) ) as 'FileFormat',
MAX( IF(A.AttrName = 'Private', FA.AttrValue, NULL) ) as 'Private',
MAX( IF(A.AttrName = 'LastModified', FA.AttrValue, NULL) ) as 'LastModified'
FROM
Files F
LEFT JOIN Files_Attributes FA USING (FileID)
LEFT JOIN Attributes A USING (AttributeID)
GROUP BY
F.FileID;
FileID Title Author CreatedOn ReadOnly FileFormat Private LastModified
1 TestFile Joe 2011-01-01 true xls false 2011-10-03
2 LongNovel Mary 2011-02-01 true json true 2011-10-04
3 ShortStory Susan 2011-03-01 false ascii false 2011-10-01
4 ProfitLoss Bill 2011-04-01 false text true 2011-10-02
5 MonthlyBudget George 2011-05-01 false binary false 2011-10-20
Ответ 3
Общая форма такого запроса будет
SELECT file.*,
attr1.value AS 'Attribute 1 Name',
attr2.value AS 'Attribute 2 Name',
...
FROM
file
LEFT JOIN attr AS attr1
ON(file.FileId=attr1.FileId and attr1.AttributeId=1)
LEFT JOIN attr AS attr2
ON(file.FileId=attr2.FileId and attr2.AttributeId=2)
...
Итак, вам нужно динамически строить свой запрос из необходимых вам атрибутов. В php-ish псевдокоде
$cols="file";
$joins="";
$rows=$db->GetAll("select * from Attributes");
foreach($rows as $idx=>$row)
{
$alias="attr{$idx}";
$cols.=", {$alias}.value as '".mysql_escape_string($row['AttributeName'])."'";
$joins.="LEFT JOIN attr as {$alias} on ".
"(file.FileId={$alias}.FileId and ".
"{$alias}.AttributeId={$row['AttributeId']}) ";
}
$pivotsql="select $cols from file $joins";
Ответ 4
Это стандартная проблема "строк в столбцах" в SQL.
Это проще всего сделать за пределами SQL.
В вашем приложении выполните следующие действия:
-
Определите простой класс, содержащий файл, системные атрибуты и набор пользовательских атрибутов. Список является хорошим выбором для этой коллекции атрибутов клиента. Позвольте назвать этот класс FileDescription.
-
Выполнение простого соединения между файлом и всеми атрибутами клиента для файла.
-
Напишите цикл для сборки FileDescriptions из результата запроса.
-
Извлеките первую строку, создайте FileDescription и установите первый атрибут клиента.
-
Пока есть больше строк для извлечения:
- Получить строку
- Если это имя файла строки не совпадает с созданным нами FileDescription: завершите создание FileDescription; добавьте это к результату Collection of File Descriptions; создайте новую, пустую FileDescription с заданным именем и атрибутом первого клиента.
- Если это имя файла строки соответствует созданной нами FileDescription: добавьте другой атрибут клиента в текущую FileDescription
Ответ 5
Я экспериментировал с разными ответами, и ответ Мэйтай был самым удобным для меня. Мой текущий проект, хотя он использует Doctrine с MySQL, имеет довольно много свободных таблиц.
Ниже приводится результат моего опыта решения Methai:
создать таблицу сущностей
DROP TABLE IF EXISTS entity;
CREATE TABLE entity (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255),
author VARCHAR(255),
createdOn DATETIME NOT NULL
) Engine = InnoDB;
создать таблицу атрибутов
DROP TABLE IF EXISTS attribute;
CREATE TABLE attribute (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
type VARCHAR(255) NOT NULL
) Engine = InnoDB;
создать таблицу атрибутов атрибутов
DROP TABLE IF EXISTS attributevalue;
CREATE TABLE attributevalue (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
value VARCHAR(255) NOT NULL,
attribute_id INT UNSIGNED NOT NULL,
FOREIGN KEY(attribute_id) REFERENCES attribute(id)
) Engine = InnoDB;
создать таблицу соединений entity_attributevalue
DROP TABLE IF EXISTS entity_attributevalue;
CREATE TABLE entity_attributevalue (
entity_id INT UNSIGNED NOT NULL,
attributevalue_id INT UNSIGNED NOT NULL,
FOREIGN KEY(entity_id) REFERENCES entity(id),
FOREIGN KEY(attributevalue_id) REFERENCES attributevalue(id)
) Engine = InnoDB;
заполнять таблицу сущностей
INSERT INTO entity
(title, author, createdOn)
VALUES
('TestFile', 'Joe', '2011-01-01'),
('LongNovel', 'Mary', '2011-02-01'),
('ShortStory', 'Susan', '2011-03-01'),
('ProfitLoss', 'Bill', '2011-04-01'),
('MonthlyBudget', 'George', '2011-05-01'),
('Paper', 'Jane', '2012-04-01'),
('Essay', 'John', '2012-03-01'),
('Article', 'Dan', '2012-12-01');
таблица атрибутов заполнения
INSERT INTO attribute
(name, type)
VALUES
('ReadOnly', 'bool'),
('FileFormat', 'text'),
('Private', 'bool'),
('LastModified', 'date');
заполнить таблицу атрибутов атрибутов
INSERT INTO attributevalue
(value, attribute_id)
VALUES
('true', '1'),
('xls', '2'),
('false', '3'),
('2011-10-03', '4'),
('true', '1'),
('json', '2'),
('true', '3'),
('2011-10-04', '4'),
('false', '1'),
('ascii', '2'),
('false', '3'),
('2011-10-01', '4'),
('false', '1'),
('text', '2'),
('true', '3'),
('2011-10-02', '4'),
('false', '1'),
('binary', '2'),
('false', '3'),
('2011-10-20', '4'),
('doc', '2'),
('false', '3'),
('2011-10-20', '4'),
('rtf', '2'),
('2011-10-20', '4');
заполнить таблицу атрибутов entity_attributevalue
INSERT INTO entity_attributevalue
(entity_id, attributevalue_id)
VALUES
('1', '1'),
('1', '2'),
('1', '3'),
('1', '4'),
('2', '5'),
('2', '6'),
('2', '7'),
('2', '8'),
('3', '9'),
('3', '10'),
('3', '11'),
('3', '12'),
('4', '13'),
('4', '14'),
('4', '15'),
('4', '16'),
('5', '17'),
('5', '18'),
('5', '19'),
('5', '20'),
('6', '21'),
('6', '22'),
('6', '23'),
('7', '24'),
('7', '25');
Отображение всех записей
SELECT *
FROM `entity` e
LEFT JOIN `entity_attributevalue` ea ON ea.entity_id = e.id
LEFT JOIN `attributevalue` av ON ea.attributevalue_id = av.id
LEFT JOIN `attribute` a ON av.attribute_id = a.id;
id title author createdOn entity_id attributevalue_id id value attribute_id id name type
1 TestFile Joe 2011-01-01 00:00:00 1 1 1 true 1 1 ReadOnly bool
1 TestFile Joe 2011-01-01 00:00:00 1 2 2 xls 2 2 FileFormat text
1 TestFile Joe 2011-01-01 00:00:00 1 3 3 false 3 3 Private bool
1 TestFile Joe 2011-01-01 00:00:00 1 4 4 2011-10-03 4 4 LastModified date
2 LongNovel Mary 2011-02-01 00:00:00 2 5 5 true 1 1 ReadOnly bool
2 LongNovel Mary 2011-02-01 00:00:00 2 6 6 json 2 2 FileFormat text
2 LongNovel Mary 2011-02-01 00:00:00 2 7 7 true 3 3 Private bool
2 LongNovel Mary 2011-02-01 00:00:00 2 8 8 2011-10-04 4 4 LastModified date
3 ShortStory Susan 2011-03-01 00:00:00 3 9 9 false 1 1 ReadOnly bool
3 ShortStory Susan 2011-03-01 00:00:00 3 10 10 ascii 2 2 FileFormat text
3 ShortStory Susan 2011-03-01 00:00:00 3 11 11 false 3 3 Private bool
3 ShortStory Susan 2011-03-01 00:00:00 3 12 12 2011-10-01 4 4 LastModified date
4 ProfitLoss Bill 2011-04-01 00:00:00 4 13 13 false 1 1 ReadOnly bool
4 ProfitLoss Bill 2011-04-01 00:00:00 4 14 14 text 2 2 FileFormat text
4 ProfitLoss Bill 2011-04-01 00:00:00 4 15 15 true 3 3 Private bool
4 ProfitLoss Bill 2011-04-01 00:00:00 4 16 16 2011-10-02 4 4 LastModified date
5 MonthlyBudget George 2011-05-01 00:00:00 5 17 17 false 1 1 ReadOnly bool
5 MonthlyBudget George 2011-05-01 00:00:00 5 18 18 binary 2 2 FileFormat text
5 MonthlyBudget George 2011-05-01 00:00:00 5 19 19 false 3 3 Private bool
5 MonthlyBudget George 2011-05-01 00:00:00 5 20 20 2011-10-20 4 4 LastModified date
6 Paper Jane 2012-04-01 00:00:00 6 21 21 binary 2 2 FileFormat text
6 Paper Jane 2012-04-01 00:00:00 6 22 22 false 3 3 Private bool
6 Paper Jane 2012-04-01 00:00:00 6 23 23 2011-10-20 4 4 LastModified date
7 Essay John 2012-03-01 00:00:00 7 24 24 binary 2 2 FileFormat text
7 Essay John 2012-03-01 00:00:00 7 25 25 2011-10-20 4 4 LastModified date
8 Article Dan 2012-12-01 00:00:00 NULL NULL NULL NULL NULL NULL NULL NULL
сводная таблица
SELECT e.*,
MAX( IF(a.name = 'ReadOnly', av.value, NULL) ) as 'ReadOnly',
MAX( IF(a.name = 'FileFormat', av.value, NULL) ) as 'FileFormat',
MAX( IF(a.name = 'Private', av.value, NULL) ) as 'Private',
MAX( IF(a.name = 'LastModified', av.value, NULL) ) as 'LastModified'
FROM `entity` e
LEFT JOIN `entity_attributevalue` ea ON ea.entity_id = e.id
LEFT JOIN `attributevalue` av ON ea.attributevalue_id = av.id
LEFT JOIN `attribute` a ON av.attribute_id = a.id
GROUP BY e.id;
id title author createdOn ReadOnly FileFormat Private LastModified
1 TestFile Joe 2011-01-01 00:00:00 true xls false 2011-10-03
2 LongNovel Mary 2011-02-01 00:00:00 true json true 2011-10-04
3 ShortStory Susan 2011-03-01 00:00:00 false ascii false 2011-10-01
4 ProfitLoss Bill 2011-04-01 00:00:00 false text true 2011-10-02
5 MonthlyBudget George 2011-05-01 00:00:00 false binary false 2011-10-20
6 Paper Jane 2012-04-01 00:00:00 NULL binary false 2011-10-20
7 Essay John 2012-03-01 00:00:00 NULL binary NULL 2011-10-20
8 Article Dan 2012-12-01 00:00:00 NULL NULL NULL NULL
Ответ 6
Однако существуют решения использовать строки в качестве столбцов, а также переносить данные.
Это связано с трюками запросов для этого в чистом SQL, или вам придется полагаться на определенные функции, доступные только в определенной базе данных, используя таблицы Pivot (или Cross tables).
В качестве примера вы можете увидеть, как это сделать здесь в Oracle (11g).
Версия для программирования будет проще обслуживать и делать, и, кроме того, будет работать с любой базой данных.
Ответ 7
Частичный ответ, так как я не знаю MySQL (ну). В MSSQL я бы посмотрел таблицы Pivot или создавал временную таблицу в хранимой процедуре. Это может быть трудное время...
