scipion logo

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.

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:

persistSetOfParticles(mySetOfLegoParticles)

def persistSetOfParticles(setOfParticles):

   pass

Persisting process

Mapper creation

First thing to use the flat mapper is to instantiate it.

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:

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:

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:

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.

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.

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)