SET FOREIGN_KEY_CHECKS=0;
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE `td` (
`id` varchar(255) NOT NULL DEFAULT '',
`familyid` int(11) DEFAULT NULL,
`withdrawndate` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_td_familyid_id` (`familyid`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `fd` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`activefromts` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`shortdescription` text,
`useraccessfamily` varchar(512) DEFAULT NULL,
`serialized` longtext,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=163 DEFAULT CHARSET=utf8;
CREATE TABLE `fs` (
`id` int(11) NOT NULL DEFAULT '0',
`classnode` varchar(200) NOT NULL DEFAULT '',
KEY `fs_foreignkey_1` (`id`),
CONSTRAINT `fs_foreignkey_1` FOREIGN KEY (`id`) REFERENCES `fd` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `fd`;
CREATE TABLE `fd` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`activefromts` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`shortdescription` text,
`useraccessfamily` varchar(512) DEFAULT NULL,
`serialized` longtext,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=163 DEFAULT CHARSET=utf8;
insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (38,'2013-03-04 07:49:22','Max Mustermann','BA008609','/root/documentinfo/picturesubuser\n 001\n ');
insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (39,'2013-03-04 07:50:08','Max Mustermann','BA008600','/root/documentinfo/picturesubuser\n 001\n ');
insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (51,'2013-03-13 16:48:55','Max Muster','BA010300','/root/documentinfo/picturesubuser\n 001\n ');
insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (58,'2013-03-14 17:26:32','Max Power','BA013400','/root/documentinfo/picturesubuser\n 002\n ');
insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (66,'2013-03-26 15:24:11','Unterschrift Max M.','BA013400','/root/documentinfo/picturesubuser\n 001\n ');
insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (74,'2013-04-04 14:08:16','Hof Bauer Außenansicht','ZIPCON','');
insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (88,'2013-05-27 15:40:25','Klaus Kiefer','BA014300','/root/documentinfo/picturesubuser\n 001\n ');
insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (89,'2013-05-28 08:20:39','Ralph Hanwalter','BA003909','/root/documentinfo/picturesubuser\n 001\n ');
insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (90,'2013-05-29 14:54:26','Unterschrift Max Mustermann','BA012709','/root/documentinfo/picturesubuser\n 001\n ');
insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (111,'2013-06-26 09:57:22','Klaus Kiefer','BA014309','/root/documentinfo/picturesubuser\n 001\n ');
insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (140,'2013-08-14 16:28:58','bungalow78-trend','BA014300','');
insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (142,'2013-08-14 16:32:36','demohaus2','BA014300','');
insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (143,'2013-08-14 16:32:15','doppelhaus-behringen116-trend','BA014300','');
insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (144,'2013-08-14 16:31:45','landhaus142-trend','BA014300','');
insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (146,'2013-08-14 16:31:09','bungalow78-trend','BA014300','');
insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (147,'2013-08-14 16:35:51','demohaus1','BA014300','');
insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (148,'2013-08-14 16:37:38','demohaus2','BA014300','');
insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (149,'2013-08-14 16:39:05','doppelhaus-behringen116-trend','BA014300','');
insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (150,'2013-08-14 16:40:40','landhaus142-trend','BA014300','');
insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (162,'2013-08-30 09:47:01','Max Muster','BA010309','/root/documentinfo/picturesubuser\n 001\n ');
DROP TABLE IF EXISTS `fs`;
CREATE TABLE `fs` (
`id` int(11) NOT NULL DEFAULT '0',
`classnode` varchar(200) NOT NULL DEFAULT '',
KEY `fs_foreignkey_1` (`id`),
CONSTRAINT `fs_foreignkey_1` FOREIGN KEY (`id`) REFERENCES `fd` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `fs`(`id`,`classnode`) values (74,'/tree=userpicture/kategorie=aussenaufnahmen/');
insert into `fs`(`id`,`classnode`) values (74,'/tree=userpicture/kategorie=sonstiges/');
insert into `fs`(`id`,`classnode`) values (143,'/tree=userpicture/kategorie=aussenaufnahmen/');
insert into `fs`(`id`,`classnode`) values (149,'/tree=userpicture/kategorie=sonstiges/');
insert into `fs`(`id`,`classnode`) values (143,'/tree=userpicture/kategorie=sonstiges/');
insert into `fs`(`id`,`classnode`) values (149,'/tree=userpicture/kategorie=aussenaufnahmen/');
insert into `fs`(`id`,`classnode`) values (150,'/tree=userpicture/kategorie=sonstiges/');
insert into `fs`(`id`,`classnode`) values (144,'/tree=userpicture/kategorie=sonstiges/');
insert into `fs`(`id`,`classnode`) values (150,'/tree=userpicture/kategorie=aussenaufnahmen/');
insert into `fs`(`id`,`classnode`) values (144,'/tree=userpicture/kategorie=aussenaufnahmen/');
insert into `fs`(`id`,`classnode`) values (66,'/tree=userpicture/kategorie=unterschrift/');
insert into `fs`(`id`,`classnode`) values (58,'/tree=userpicture/kategorie=unterschrift/');
insert into `fs`(`id`,`classnode`) values (88,'/tree=userpicture/kategorie=unterschrift/');
insert into `fs`(`id`,`classnode`) values (162,'/tree=userpicture/kategorie=unterschrift/');
insert into `fs`(`id`,`classnode`) values (51,'/tree=userpicture/kategorie=unterschrift/');
insert into `fs`(`id`,`classnode`) values (90,'/tree=userpicture/kategorie=unterschrift/');
insert into `fs`(`id`,`classnode`) values (89,'/tree=userpicture/kategorie=unterschrift/');
insert into `fs`(`id`,`classnode`) values (38,'/tree=userpicture/kategorie=unterschrift/');
insert into `fs`(`id`,`classnode`) values (39,'/tree=userpicture/kategorie=unterschrift/');
insert into `fs`(`id`,`classnode`) values (111,'/tree=userpicture/kategorie=unterschrift/');
DROP TABLE IF EXISTS `td`;
CREATE TABLE `td` (
`id` varchar(255) NOT NULL DEFAULT '',
`familyid` int(11) DEFAULT NULL,
`withdrawndate` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_td_familyid_id` (`familyid`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/111/1372233400642.pmd',111,NULL);
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/140/1376487699812.pmd',140,'2013-08-14');
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/140/1376487727471.pmd',140,'2013-08-14');
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/142/1376487973857.pmd',142,'2013-08-14');
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/142/1376488019348.pmd',142,'2013-08-14');
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/143/1376488153694.pmd',143,'2013-08-14');
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/143/1376488175601.pmd',143,'2013-08-14');
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/144/1376488236640.pmd',144,'2013-08-14');
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/144/1376488329609.pmd',144,'2013-08-14');
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/146/1376490601869.pmd',146,NULL);
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/146/1376490664368.pmd',146,NULL);
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/147/1376490913146.pmd',147,NULL);
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/147/1376490946241.pmd',147,NULL);
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/148/1376491009680.pmd',148,NULL);
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/148/1376491054867.pmd',148,NULL);
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/149/1376491116794.pmd',149,NULL);
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/149/1376491141665.pmd',149,NULL);
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/150/1376491203736.pmd',150,NULL);
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/150/1376491236675.pmd',150,NULL);
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/162/1377848818511.pmd',162,NULL);
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/38/1362379759464.pmd',38,NULL);
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/39/1362379804507.pmd',39,NULL);
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/51/1363189729391.pmd',51,NULL);
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/58/1363277790564.pmd',58,NULL);
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/66/1364307582985.pmd',66,NULL);
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/74/1364916039095.pmd',74,NULL);
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/88/1369662014970.pmd',88,NULL);
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/89/1369722032695.pmd',89,NULL);
insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/90/1369832057370.pmd',90,NULL);
SELECT DISTINCT
x.useraccessfamily,
x.picturesubuser,
x.familyid,
x.shortdescription,
x.activefromts AS `letzte Änderung am`
FROM
(
SELECT
fd.id AS familyid,
fd.shortdescription,
fd.useraccessfamily,
IF(INSTR(fd.serialized, '/root/documentinfo/picturesubuser') > 0,
SUBSTRING_INDEX(
SUBSTRING_INDEX(
SUBSTRING_INDEX(
fd.serialized,
'/root/documentinfo/picturesubuser',
-1
),
'',
1
),
'',
-1
),
'') AS picturesubuser,
fd.activefromts
FROM
fd,
fs
WHERE
fd.id = fs.id AND
(
fs.classnode LIKE '/tree=userpicture/level1=unterschriften/%'
OR
fs.classnode LIKE '/tree=userpicture/kategorie=unterschrift/%'
) AND
IF(INSTR(fd.serialized, '/root/documentinfo/picturesubuser') > 0,
SUBSTRING_INDEX(
SUBSTRING_INDEX(
SUBSTRING_INDEX(
fd.serialized,
'/root/documentinfo/picturesubuser',
-1
),
'',
1
),
'',
-1
),
'') <> ''
) `x`
,
(
SELECT
fd.useraccessfamily,
IF(INSTR(fd.serialized, '/root/documentinfo/picturesubuser') > 0,
SUBSTRING_INDEX(
SUBSTRING_INDEX(
SUBSTRING_INDEX(
fd.serialized,
'/root/documentinfo/picturesubuser',
-1
),
'',
1
),
'',
-1
),
'') AS picturesubuser,
COUNT(*)
FROM
fd,
td
WHERE
td.familyid = fd.id AND
td.withdrawndate IS NULL AND
IF(INSTR(fd.serialized, '/root/documentinfo/picturesubuser') > 0,
SUBSTRING_INDEX(
SUBSTRING_INDEX(
SUBSTRING_INDEX(
fd.serialized,
'/root/documentinfo/picturesubuser',
-1
),
'',
1
),
'',
-1
),
'') <> ''
GROUP BY
fd.useraccessfamily,
IF(INSTR(fd.serialized, '/root/documentinfo/picturesubuser') > 0,
SUBSTRING_INDEX(
SUBSTRING_INDEX(
SUBSTRING_INDEX(
fd.serialized,
'/root/documentinfo/picturesubuser',
-1
),
'',
1
),
'',
-1
),
'')
HAVING COUNT(*) > 1
) `y`
WHERE
x.useraccessfamily = y.useraccessfamily AND
x.picturesubuser = y.picturesubuser
ORDER BY
1, 2, 3;