.. figure:: /docs/images/scipion_logo.gif :width: 250 :alt: scipion logo .. _sqlite-flat-mapper: SqliteFlatMapper ================ It is a class which main responsibility is to persist/retrieve "sets" or any homogeneous collection of objects in an optimal manner. Objects to store ---------------- In general any big list/collection with homogeneous content is a good candidate to be used with the flat mapper. SetOfParticles, SetOfClasses, SetOfVolumes, and any class that inherits form Set ( at pyworkflow/object.py) is a good candidate to be persisted with the flat mapper. Any other big, homogeneous list, even if it doesn't extends Set, will work for the flat mapper. Example ------- Let's start with an example and follow the complete process. Let's take SetOfParticles which is basically a list of **Particle** objects. .. figure:: /docs/images/guis/particles_table.png :align: center :alt: particles_table Each of the particles in the set has the same 14 attributes (creation attribute is never shown in the viewer): * id * enabled * _index * _filename * label * comment * creation * _samplingRate * _acquisition. * _magnification For the sake of simplicity we will take into account only those attributes mentioned above, ignoring the rest. Therefore, let's assume we have in our code something like this: .. code-block:: python persistSetOfParticles(mySetOfLegoParticles) def persistSetOfParticles(setOfParticles): pass Persisting process ------------------ Mapper creation ~~~~~~~~~~~~~~~ First thing to use the flat mapper is to instantiate it. .. code-block:: python persistSetOfParticles(mySetOfLegoParticles) def persistSetOfParticles(setOfParticles): flatMapper = SqliteFlatMapper('legoparticles.sqlite', globals()) The constructor of the SqliteFlatMapper needs a dbName parameter ('legoparticles.sqlite' here) that will be the file name for the sqlite database. During the creation, a connection is established and the database is created with an empty schema. Storing the set ~~~~~~~~~~~~~~~ Now that we have the mapper and and empty database. We can proceed to store our lego data: .. code-block:: python persistSetOfParticles(mySetOfLegoParticles) def persistSetOfParticles(setOfParticles): flatMapper = SqliteFlatMapper('legoparticles.sqlite', globals()) # Loop through the lego particles for particle in setOfParticles.iterItems(): # Insert a single lego particle. flatMapper.insert(particle) Schema generated by the flat mapper ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The first insert call, will trigger the database schema creation. With the first insert, the mapper sees for the first time what to store, and is then when it can create all the tables and metadata needed. Objects table ^^^^^^^^^^^^^ The main table that will hold all the values of the set is the **Objects** table. Every objects table is composed by 2 type of columns: * Fixed columns (5): This part is common among all databases created by this mapper. This columns are used to store common attributes regardless which object is being stored. **id, enabled, label, comment and creation**. * Variable columns: These columns are specific for each object, but theirs names do not matches with the object attribute name. Instead, are named from c1 to cn, being the number of attributes of the object to persist. Going back to our case, since our Lego Particle objects have 14 attributes (5 of them are the fixed ones), the Objects table should have 14 fields: 5 fixed + 9 variables (named from c1 to c9). Let's have a look at the table: .. figure:: /docs/images/mappers/sqlflatmapper_1row.png :align: center :alt: sqlflatmapper_1row If you pay attention, you'll find there is one more variable field than expected (we see 10 "c" columns). The reason for this is because our particle object contains another object (\_acquisition) and this generates an extra column. After inserting the first particle you will see something like this: .. figure:: /docs/images/mappers/sqlflatmapper_objects_fields.png :align: center :alt: sqlflatmapper_objects_fields As you can see, the values for the first particle object are stored there, the "fixed" ones and the variable ones too in thee "c" columns. We can guess that on "c2" the mapper has stored the \_filename ("Runs/000002\_ProtImportParticles/extra/micrograph.xmp") but it's not that obvious for other values. The mapper maps the columns "cx" to the object properties and stores it in the *Classes* table. Classes table ^^^^^^^^^^^^^ This table serves as a map between the object attributes and the "c" columns present in the Objects table. .. figure:: /docs/images/mappers/flatmapper_classes.png :align: center :alt: sqlflatmapper_objects_fields The "label\_property" column corresponds to each of the attributes of our "Particle" objects, and the "column\_name" column with the "c" column used in the Objects table to store the value of that property. NOTE: The first row (C00) is not present in the Objects table. This corresponds to the class of the object to instantiate (Particle) NOTE1: Also, C06 (in our case), corresponds to another object (Acquisition) that is contained in our Particle object. This field is present in the Objects table, but its value will always be empty. Properties table ^^^^^^^^^^^^^^^^ Our SetOfParticle, itself, has some attributes that also need to be persisted. :: self._samplingRate = Float() self._hasCtf = Boolean(kwargs.get('ctf', False)) self._alignment = String(ALIGN_NONE) self._isPhaseFlipped = Boolean(False) self._isAmplitudeCorrected = Boolean(False) self._acquisition = Acquisition() self._firstDim = ImageDim() # Dimensions of the first image For a single SetOfParticles (regardless the number of particles in the set) we have a few values to persist. For this purpose, the "Properties" table is created, to store those attributes. .. figure:: /docs/images/mappers/flatMapper_properties.png :align: center :alt: flatMapper_properties.png With this our SetOfParticles should have been persisted in an sqlLite file. Retrieving process ------------------ Retrieving all ~~~~~~~~~~~~~~ Imagine now that you need to retrieve the SetOfParticles. For that we again need an instance of the mapper and ask it to retrieve the set. :: myParticles = retrieveSetOfParticles() def retrieveSetOfParticles(): flatMapper = SqliteFlatMapper('legoparticles.sqlite', globals()) return flatMapper.selectAll() Retrieving one ~~~~~~~~~~~~~~ If you only want to retrieve one object you can do so using myParticle = flatMapper.selectById(1234)