Skip to content

Sqlite flat mapper

pconesa edited this page Jun 16, 2017 · 8 revisions




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.

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 firstime 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 n 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:

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:

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.

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.

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)

Clone this wiki locally
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.