Ceci est une ancienne révision du document !
CREATE VIEW v_zotero_items AS SELECT t1.itemID, t2.typeName, substr(t6.VALUE, instr(t6.VALUE, ' ') + 1) AS datation, group_concat(t4.VALUE, ' # ') AS title -- group_concat(t4.fieldName) as property, -- group_concat(t5.value), -- group_concat('itemData' || t5.valueID) AS id FROM items t1 JOIN itemTypes t2 ON t2.itemTypeID = t1.itemTypeID LEFT JOIN itemData t3 ON t3.itemID = t1.itemID AND t3.fieldID IN (110, 115,12) LEFT JOIN itemDataValues t4 ON t4.valueID = t3.valueID LEFT JOIN itemData t5 ON t5.itemID = t1.itemID AND t5.fieldID IN (14) LEFT JOIN itemDataValues t6 ON t6.valueID = t5.valueID WHERE t2.itemTypeID NOT IN (14, 1) GROUP BY t1.itemID, t2.typeName;
Apparemment marche seulement avec les clients à accès direct et non avec ceux qui utilisent le connecteur JDBC.
ATTACH '/Users/fberetta/Zotero/zotero_copie.sqlite' AS zotero; PRAGMA database_list; SELECT * FROM zotero.v_zotero_items LIMIT 10;