Hello ,
First let me say , Nice progress!
The problem is that the Snapsot query you have created contains ID references taken from the DFM (vol_id ,aggr_id ,array_id) and reference to already created tables in the storage schema .
(We definitely need to change the UI so such mistakes will not be done .)
In order to solve you problem you should
1) Remove the FK to foreign keys to array table (you will need to recreate the table )
2) Add to the Select query the volume Name and the Array IP (or host name) -do JOIN with the relevant DFM tables.
3) Do the JOIN in the Filter level between snapshot and Volumes .
Another simpler option is to wait for 1.1 where WFA contains embedded support for snapshot caching as part of the certified content
Another note :
If you choose to go on and implement the query on 1.0.2 ,note that the query you created caches deleted snapshots
you can use the below Create and Select queries as reference
CREATE TABLE storage.snapshot (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`timestamp` DATETIME DEFAULT NULL,
`volume_id` INT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
SELECT
snapshot.snapshotId AS id,
snapshot.snapshotName AS name,
snapshot.accessTime AS timestamp,
snapshot.volId AS volume_id
FROM
dfm.snapshots snapshot
JOIN
dfm.volumes volume ON volume.volId = snapshot.volId AND volume.volState IS NOT NULL
JOIN
dfm.aggrs aggregate ON volume.aggrId = aggregate.aggrId
JOIN
dfm.objects obj ON volume.volId = obj.objid AND obj.objDeleted IS NULL AND ((obj.objFlags & 0x20000000000000) = 0)
