{"action":"created","issue":{"url":"https://api.github.com/repos/qgis/QGIS-Enhancement-Proposals/issues/27","labels_url":"https://api.github.com/repos/qgis/QGIS-Enhancement-Proposals/issues/27/labels{/name}","comments_url":"https://api.github.com/repos/qgis/QGIS-Enhancement-Proposals/issues/27/comments","events_url":"https://api.github.com/repos/qgis/QGIS-Enhancement-Proposals/issues/27/events","html_url":"https://github.com/qgis/QGIS-Enhancement-Proposals/issues/27","id":113787719,"number":27,"title":"QEP # : Auxiliary storage","user":{"login":"NathanW2","id":381660,"avatar_url":"https://avatars.githubusercontent.com/u/381660?v=3","gravatar_id":"","url":"https://api.github.com/users/NathanW2","html_url":"https://github.com/NathanW2","followers_url":"https://api.github.com/users/NathanW2/followers","following_url":"https://api.github.com/users/NathanW2/following{/other_user}","gists_url":"https://api.github.com/users/NathanW2/gists{/gist_id}","starred_url":"https://api.github.com/users/NathanW2/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/NathanW2/subscriptions","organizations_url":"https://api.github.com/users/NathanW2/orgs","repos_url":"https://api.github.com/users/NathanW2/repos","events_url":"https://api.github.com/users/NathanW2/events{/privacy}","received_events_url":"https://api.github.com/users/NathanW2/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/qgis/QGIS-Enhancement-Proposals/labels/Draft","name":"Draft","color":"207de5"}],"state":"open","locked":false,"assignee":{"login":"mhugo","id":1618556,"avatar_url":"https://avatars.githubusercontent.com/u/1618556?v=3","gravatar_id":"","url":"https://api.github.com/users/mhugo","html_url":"https://github.com/mhugo","followers_url":"https://api.github.com/users/mhugo/followers","following_url":"https://api.github.com/users/mhugo/following{/other_user}","gists_url":"https://api.github.com/users/mhugo/gists{/gist_id}","starred_url":"https://api.github.com/users/mhugo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mhugo/subscriptions","organizations_url":"https://api.github.com/users/mhugo/orgs","repos_url":"https://api.github.com/users/mhugo/repos","events_url":"https://api.github.com/users/mhugo/events{/privacy}","received_events_url":"https://api.github.com/users/mhugo/received_events","type":"User","site_admin":false},"milestone":null,"comments":6,"created_at":"2015-10-28T10:30:44Z","updated_at":"2015-11-02T10:17:16Z","closed_at":null,"body":"========================================================================\r\nQGIS Enhancement ??: Auxiliary storage\r\n========================================================================\r\n\r\n**Date** October 2015\r\n**Author** Hugo Mercier\r\n**Contact** hugo dot mercier at oslandia dot com\r\n**Last Edited** \r\n**Status**  \r\n**Version**\r\n\r\nSummary\r\n-------\r\n\r\nThis proposal is about an evolution of QGIS aiming at storing auxiliary data\r\nin a layer. Such auxiliary data are data used mostly for the needs of QGIS (symbology) and have no real\r\ninterest in being stored with the native raw geospatial data.\r\n\r\nThe need arises from the restrictions existing in the manual placement of labels.\r\nManual placement of labels are possible in QGIS by setting some labeling properties (X and Y position,\r\nand rotation angle optionaly) as being \"data-defined\", meaning that values come from a column (or an expression).\r\nBut setting this up on an existing layer requires either to add new columns to the source layer, while it is\r\nnot always possible or desirable. Creating another table and using joins to it is possible but the synchronisation\r\nbetween the two tables is not possible, leading to a solution not satisfactory.\r\n\r\nWe propose here a more integrated solution where the creation of auxiliary columns or layers\r\nare brought more transparently to the user by storing them in a central repository carried by the\r\nproject file.\r\n\r\nRequirements\r\n------------\r\n\r\nThe proposed solution have the following requirements :\r\n\r\n- It should allow to transparently add columns of any type to a layer, without adding them to the source data source.\r\nBeside usual types (string, real, integer), we may also think of geometry types. We only consider types that can be converted to atomic types (string for WKT or WKB)\r\nThe user should be able to edit values in these columns when the layer is in editing mode.\r\n\r\n- These values should not be stored with the source data, and should be carried by the project, i.e.\r\nwhen the project file is moved or copied, the auxiliary data should still be there.\r\n\r\n- Adding a new row to the layer also adds a new row for each auxiliary field (with a null or default value)\r\n\r\n- Removing a row from the layer also removes the corresponding row of each auxiliary field\r\n\r\n- New GUI elements should ease the creation of auxiliary fields\r\n  - on each \"data-defined\" property\r\n  - possibly on groups of \"data-defined\": for manual label placement or label connectors\r\n\r\n- It should be possible to save auxiliary data to a new layer\r\n\r\nProposed solution\r\n-----------------\r\n\r\nWe propose to add a new type of field, close to the existing \"virtual field\" (where a field can be defined as an expression).\r\nHere a new type of field, \"auxiliary field\", will point to a central data storage.\r\n\r\nThe central data storage should allow to store any kind of auxiliary column definition and data for any layer. And it should\r\nbe linked to, or be part of, a project file. The access to auxiliary data should be efficient.\r\n\r\nCore changes\r\n------------\r\n\r\nProject file\r\n------------\r\n\r\nThe current project file format (XML) is too limited to carry an efficient storage of such auxiliary data. It should somehow be evolved\r\nto include a binary indexable format. SQLite is chosen as a first candidate for this data format. But it might evolve if performance issues\r\nare found.\r\n\r\nGiven the current state of the core code where lots of classes assume the project file is an XML file (everything that is saved to the project file\r\nhas a writeXML method), it seems hard to completely refactor the project file format to a format based on SQLite.\r\n\r\nWe propose to use a Zip file as the root of the new project file format. A Zip file exposes a filesystem-like structure.\r\nThe original XML-based project file will then be embedded in the Zip file. And additional files could de added, and in particular\r\nan SQLite file.\r\nIt only adds a step after actually saving the XML tree: including it in a Zip file. Symetrically during the loading, the first step would be to extract the XML file and pass it to the\r\nexisting readXML methods.\r\n\r\nAuxiliary fields\r\n-------------------\r\n\r\nAn auxiliary field is defined for a given layer. It is a new type of field: a new enum value for QgsFields::FieldOrigin (OriginAuxiliary). The code\r\nof QgsVectorLayer, QgsVectorLayerFeatureIterator and QgsVectorLayerEditBuffer will be changed to reflect this new type, so that they will appear in the list of fields of a layer (alongside native fields and virtual fields).\r\n\r\nSince an auxiliary field is conceptually a (left) join on an external database table, auxiliary fields may only be added to vector layers that have a **primary key**.\r\n\r\nAuxiliary fields are defined as a table in the central SQLite database.\r\n\r\nFor instance, for a vector layer L with columns (ID, A, B, AF1, AF2) with native fields ID (primary key), A, B and auxiliary fields AF1 and AF2, there is\r\nan auxiliary table T with columns (ID, AF1, AF2). The auxiliary table is named after the name of the origin layer.\r\n\r\nA new row is added to the auxiliary table when a new row has been added to the origin vector layer and a data has been set (not null).\r\n\r\nA row is removed from the auxiliary table when the corresponding row has been removed from the origin vector layer.\r\n\r\nAn auxiliary table is added when the first auxiliary field of a layer is created.\r\n\r\nAn auxiliary table is removed and data are lost when the origin layer is closed. The closing of a layer should explicitly warn the user about the loss of auxiliary data.\r\nAn auxiliary table is also removed when every auxiliary fields of a layer are removed.\r\n\r\nGUI Changes\r\n-----------\r\n\r\nAuxiliary fields will be represented in the list of fields by a distinctive icon. And new icons in the layer properties, as well as in the attribute table widgets will allow to create\r\nor remove auxiliary fields.\r\n\r\nData-defined properties are the most probable place where auxiliary fields will be used.\r\nIn the menu that can be found for each data-defined property, a new entry could be added to allow the creation, in a click, of an auxiliary field for that property\r\nand link it as the source of the data-defined property.\r\n\r\nSimilar GUI shortcuts could be added in order to automate the creation of a group of data-defined properties.\r\nA button (found both in the labeling properties dialog of the layer and in the manual labeling toolbox) will be added for the enabling of manual placement of labels.\r\n\r\nIt will be possible to save auxiliary data of a layer to a plain vector layer, by selection of a set of auxiliary fields in the attribute table or layer's properties dialog.\r\n\r\n\r\nPerformance Implications\r\n------------------------\r\n\r\nAccess to auxiliary data will take place during the retrieval of a feature, through a QgsVectorLayerFeatureIterator, if such fields have been selected.\r\nThe basic implementation will retrieve a row of auxiliary data for each feature retrieved (SELECT WHERE id = ). Since the central SQLite database\r\nwill be configured to use an index for each auxiliary table, speed should not be a major concern.\r\n\r\nFaster access could be investigated when the QgsFeatureRequest is not filtered or if it uses a FilterFid or FilterFids.\r\n\r\nTest Coverage\r\n-------------\r\n\r\nCore changes will be covered by unit tests: modifications to QgsFields, QgsVectorLayer and QgsVectorLayerFeatureIterator\r\n\r\n\r\nBackwards Compatibility\r\n-----------------------\r\n\r\nA new format for project files is introduced here. Project files from previous versions will still be available for opening. When saving back these project files, they will be\r\nsaved in the new file format.\r\n\r\nOther excluded approaches\r\n-------------------------\r\n\r\nOther approaches have been investigating and excluded. They may be reconsidered if circumventing their drawbacks is possible.\r\n\r\nWe may think of using table joins to handle such auxiliary data and ease the creation of such joins for the end user.\r\nHowever, the current implementation of joins are too restricted : no editing of a joined value is possible and there is no\r\nsynchronization between the main table and the joined table.\r\n\r\nVirtual layers come also in mind for the implementation of such feature (not yet integrated). This will require the implementation of virtual layers\r\nto have a write support (through triggers) to offer editing possibility for auxiliary data.\r\nAnother concern about virtual layers may be about performances since in that model, values from the original data source would be\r\nconverted into QgsFeatures, then into a representation suitable for an SQLite virtual table and then to QgsFeatures. Such transformation\r\nis not needed and an optimisation would be desirable.\r\n\r\nVoting History\r\n--------------\r\n"},"comment":{"url":"https://api.github.com/repos/qgis/QGIS-Enhancement-Proposals/issues/comments/152977887","html_url":"https://github.com/qgis/QGIS-Enhancement-Proposals/issues/27#issuecomment-152977887","issue_url":"https://api.github.com/repos/qgis/QGIS-Enhancement-Proposals/issues/27","id":152977887,"user":{"login":"mhugo","id":1618556,"avatar_url":"https://avatars.githubusercontent.com/u/1618556?v=3","gravatar_id":"","url":"https://api.github.com/users/mhugo","html_url":"https://github.com/mhugo","followers_url":"https://api.github.com/users/mhugo/followers","following_url":"https://api.github.com/users/mhugo/following{/other_user}","gists_url":"https://api.github.com/users/mhugo/gists{/gist_id}","starred_url":"https://api.github.com/users/mhugo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mhugo/subscriptions","organizations_url":"https://api.github.com/users/mhugo/orgs","repos_url":"https://api.github.com/users/mhugo/repos","events_url":"https://api.github.com/users/mhugo/events{/privacy}","received_events_url":"https://api.github.com/users/mhugo/received_events","type":"User","site_admin":false},"created_at":"2015-11-02T10:17:16Z","updated_at":"2015-11-02T10:17:16Z","body":"Ahh. Got it :)\r\nYes QGis layer definition file could be extend to support auxiliary data. That's a good point. And it would be a way to share auxiliary data between projects."}}