我需要设计存储文件所有元数据的表(即文件名,作者,标题,创建日期)和自定义元数据(用户已添加到文件中,例如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
什么查询会生成此结果？
这个问题提到了MySQL,实际上这个DBMS对这类问题有一个特殊的功能: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'
这样,您只需要一个单独的数据库查询,并且输出很容易解析.如果要将属性存储为数据库中的实际日期时间等,则需要使用动态SQL,但我会保持清晰并将值存储在varchars中.
这种查询的一般形式是
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";
如果您正在寻找比group-concat结果更可用(和可加入)的东西,请尝试以下解决方案.我已经创建了一些与你的例子非常相似的表格,以使其有意义.
这适用于:
你想要一个纯SQL解决方案(没有代码,没有循环)
您有一组可预测的属性(例如,不是动态的)
您可以在需要添加新属性类型时更新查询
您希望结果可以是JOINed,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
这是SQL中标准的"行到列"问题.
它最容易在SQL之外完成.
在您的应用程序中,执行以下操作:
定义一个简单的类来包含文件,系统属性和用户属性集合.列表是此客户属性集合的不错选择.我们称这个类为FileDescription.
在文件和文件的所有客户属性之间执行简单连接.
编写一个循环来组合查询结果中的FileDescriptions.
获取第一行,创建FileDescription并设置第一个customer属性.
虽然有更多行要获取:
取一行
如果此行的文件名与我们正在构建的FileDescription不匹配:完成构建FileDescription; 将此附加到文件描述集合的结果; 使用给定名称和第一个客户属性创建一个新的空FileDescription.
如果此行的文件名与我们正在构建的FileDescription匹配:将另一个customer属性附加到当前FileDescription
京公网安备 11010802040832号 | 京ICP备19059560号-6 