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.

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.

Store 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 to 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 is generates an extra column.