fieldsCombined, itemTypeFields, itemTypeFieldsCombined, baseFieldMappings, baseFieldMappingsCombined

[Emiliano Heyns]

I'm trying to get a handle on how fields are stored in the database. itemTypeFields seems pretty clear, but what do the relations in fieldsCombined, itemTypeFieldsCombined, baseFieldMappings and baseFieldMappingsCombined mean?

[Emiliano Heyns]

On Tuesday, August 8, 2017 at 5:29:03 PM UTC+2, Emiliano Heyns wrote:

I'm trying to get a handle on how fields are stored in the database. itemTypeFields seems pretty clear, but what do the relations in fieldsCombined, itemTypeFieldsCombined, baseFieldMappings and baseFieldMappingsCombined mean?

After a bit of experimentation, it looks like this dumps out all known fields for the zotero items -- would love to know if I have it right:

SELECT * FROM (

  SELECT it.typeName, f.fieldName, a.fieldName AS fieldAlias

  FROM baseFieldMappingsCombined bfmc

  JOIN fields f ON f.fieldID = bfmc.baseFieldID

  JOIN fields a ON a.fieldID = bfmc.fieldID

  JOIN itemTypes it ON it.itemTypeID = bfmc.itemTypeID

  UNION

  SELECT it.typeName, f.fieldName, NULL

  FROM itemTypes it

  JOIN itemTypeFields itf ON it.itemTypeID = itf.itemTypeID

  JOIN fields f ON f.fieldID = itf.fieldID

) ORDER BY typeName, fieldName

;