Technical topic: ASN.1 and SQL mapping

From TASTE
Revision as of 08:06, 17 November 2020 by Mperrotin (talk | contribs) (From ASN.1 to SQL)
Jump to: navigation, search

Introduction

This page describes one of the hot features of our ASN.1 compiler[1] and related the translation of ASN.1 grammars to SQL databases.

TASTE, as a set of modelling tools, heavily relies on the ASN.1 language as the way to ensure data consistency at all levels of a development lifecycle. ASN.1 is a simple and efficient language for describing data types in a precise and unambiguous manner. Data types can be simple or complex, contain constraints, and are used everywhere, any time there is a need for storing and exchanging information. In that sense, ASN.1 is comparable to languages such as XSD (an XML representation of data types). Arguably, from this point of view, ASN.1 is however much simpler, and more powerful at the same time. But the real added value of ASN.1, and the reason why we chose it in TASTE is that it also allows for multiple data physical encodings.

What is a physical encoding?

To make it simple, ASN.1 (as an ISO standard) provides pre-established ways to translate the data you manipulate at code level into a memory representation. For example, if your ASN.1 model specifies this data type:

My-Integer ::= INTEGER (0..7)   -- An integer with a range of value from 0 to 7

...then the standard will tell you that you need only 3 bits to encode this value in a buffer.


Why is this interesting? Because by being part of the standard, the production of encoding and decoding functions for a given grammar (set of data types) can be automated by tools. This is what ASN.1 compilers are about: you select the physical encoding rules you want (they range from compact binary, as shown above, to verbose XML encodings), and the tool generates optimized encoders and decoders that your code can call for your own data, so you never have to care about how to place bytes and bits in memory.


TASTE comes with its own ASN.1 compiler, that is free and open-source, and, for different purposes has the following backends:


  • Optimized C (for embedded plaform, with no heap usage, no system calls, no dependency on external libraries)
  • Spark/Ada
  • Python
  • SDL: RTDS, OpenGEODE
  • Matlab-Simulink
  • SCADE6
  • VHDL
  • HTML (Documentation)
  • and now SQL (both raw and SQLAlchemy)


In other words, to get the complete picture, TASTE ASN.1 tools can give access to ASN.1 data types to any of these languages, guaranteeing by construction full consistency, data-wise, of all the pieces of software that share these types. TASTE generates translators that makes sure that a data of any complex type, used in a C application, has exactly the same semantics and value in any of the other supported languages, and that they can be exchanged seamlessly, without having to manually implement mapping rules and checks on the individual fields (endianness, alignment, ordering are taken care by ASN.1).


Why SQL?

As stated earlier, precise and unambiguous data description is essential at all stages of a software development lifecycle - including for documentation and testing. In that spirit, data storage and retrieval is a critical aspect of a running system. You always have the choice to:

  • Take care of data storage on your own (implementing search, etc)
  • Or to delegate it to specialized tools - that is, to database engines.

The first option is always possible - and particularly useful in small applications. ASN.1 can code your data in XML or in any other form, and you can then simply dump it on disk and reload it on demand.

In real-size applications however, it is far more likely that you do not want to take the responsibility of handling this task... Think of file system issues, backups, shared access to data, searching algorithms, etc. In space applications in particular, as in many other places, big databases are created to store data received from satellites, and to save the information on the running systems. Real database engines are obviously a perfect fit for this problem, as they take care of all the problems with data storage, consistency checks, etc.

This is where our ASN.1 to SQL backend enters the game...

From ASN.1 to SQL

The ASN.1 tools in TASTE can automatically create SQL tables corresponding to ASN.1 data types, including the most complex ones.

To understand the various steps, you can execute the following commands manually (a script that automates the full process is described later on this page - check it out):

$ ls
DataTypes.asn


$ asn2dataModel.py −toSQL DataTypes.asn
$ ls  
DataTypes.asn DataTypes.sql

$ head DataTypes.sql

−− SQL statements for types used in "DataTypes.asn"
CREATE TABLE My2ndBool (
id int PRIMARY KEY,
data boolean NOT NULL
);

CREATE TABLE My2ndEnumerated (
id int PRIMARY KEY,
...

The generated SQL output, as seen in the listing above, contains semantically equivalent - and portable - SQL definitions of the tables, that can represent instances of the ASN.1 types. All tables carry a primary key, which is then followed by specific fields, depending on the kind of ASN.1 type being mapped:

  • INTEGERs are mapped to integer fields, that can potentially carry constraints - for example,this ASN.1 declaration MyInt ::= INTEGER (0 .. 20) is mapped to...
CREATE TABLE MyInt (
id int PRIMARY KEY,
data int NOT NULL, CHECK( data >=0 and data <=20)
);
  • ... and similarly for the other types.

Automated script

In order to avoid the manual execution of all these commands you can order the creation of SQL tables automatically by running the following script from your project folder:

$ taste-create-database

Complete integration with the SQLAlchemy mapper

A complete first example

In the previous section, we saw how TASTE can automatically create portable SQL definitions for the ASN.1 types used in a design. TASTE can do much more than that, if the user chooses to use the SQLAlchemy mapper - which is working together with the python mapper to allow interfacing with virtually any database: SQLite, Postgres, MySQL, Oracle, etc.

Starting from an ASN.1 grammar:

$ ls -l
total 40
-rw-r--r-- 1 ttsiod ttsiod  2593 May  2 15:41 LotsOfDataTypes.asn

...we first invoke the TASTE Python mapper:

$ mkdir -p asn2dataModel
$ asn2dataModel.py -o asn2dataModel -toPython LotsOfDataTypes.asn
$ cd asn2dataModel
$ make -f Makefile.python
...
$ cd ..

And then we invoke the SQLAlchemy mapper:

$ asn2dataModel.py -toSqlalchemy LotsOfDataTypes.asn
$ ls -lF
total 156
-rw-r--r-- 1 ttsiod ttsiod   2593 May  2 15:41 LotsOfDataTypes.asn
drwxr-xr-x 2 ttsiod ttsiod   4096 Jun 11 10:42 asn2dataModel/
-rw-r--r-- 1 ttsiod ttsiod 110479 Jun 11 10:43 lotsofdatatypes_model.py

In the listing above, we first invoke the TASTE Python mapper, which creates Python classes for our ASN.1 types (e.g. for an ASN.1 type named My-Integer, a Python class My_Integer will be created). We then invoke the SQLAlchemy mapper, which creates specially crafted Python classes (e.g. My_IntegerSQL). Instances of these classes are constructed based on the Python ASN.1 types - and can automatically store/retrieve their content into any database supported by SQLAlchemy (PostgreSQL, MySQL, SQLite, etc).

Below is a commented example of how a complex ASN.1 type is mapped and used by the automatically generated Python and SQLAlchemy mappers:

# Starting with this ASN.1 grammar:
#
# MyInt ::= INTEGER (0 .. 20)
# 
# My2ndInt ::= MyInt ( 1 .. 18)
# 
# MySeq ::= SEQUENCE {
#     anInt MyInt,
#     anotherInt My2ndInt
#
# We proceed to instantiate an instance of the MySeq type,
# and assign values inside its two fields:
#
b = MySeq()
b.anInt.Set(16)
b.anotherInt.Set(17)
#
# At this point, we have only used the TASTE Python mapper.
# But we can go further than that, and use the SQLAlchemy mapper,
# to serialize it in the database we are attached to: 
#
bb = MySeq_SQL(b)
bid = bb.save(self.session)
self.session.commit()
#
# The 'save' member returns the primary key value for the new
# table record inserted. We can search for this record using
# the powerful SQLAlchemy API. At its most basic level,
# we can lookup using the primary key:
#
z = MySeq_SQL.loadFromDB(self.session, bid)
#
# And the record returned, offers access to the contained
# table record fields:
#
assert b.anInt.Get() == z.anInt.data
assert b.anotherInt.Get() == z.anotherInt.data
#
# But that's not all - the TASTE SQLAlchemy mapper also offers
# a .asn1 property, that automatically instantiates an instance
# of a Python class that carries the data, via the normal
# TASTE Python forms:
#
assert b.anInt.Get() == z.asn1.anInt.Get()
assert b.anotherInt.Get() == z.asn1.anotherInt.Get()

As the example indicates, serializing an instance of an ASN.1 type to a database, is now a very simple matter - you just pass the Python instance to the constructor of the TypeName_SQL class, which is automatically generated for the TypeName ASN.1 type. save-ing this instance automatically performs all the necessary work to create records in the master/detail/detail/... tables (which can go arbitrarily deep, depending on the complexity of the defined type) and returns the primary key of the newly created master table record.

The developer can then utilize the full power of the SQLAlchemy API to search inside the database for records that fullfill any criteria - for example...

anInstanceWithAnIntOf10 = session.query(
    MySeq_SQL).filter(MySeq_SQL.anInt.data == 10).first()
print anInstanceWithAnIntOf10.asn1.anInt.Get()

...and in general, the SQLAlchemy ORM will automatically create all the necessary SQL statements (performing JOINs on all the appropriate tables' keys) to fetch the dataset desired.

Constraints are also respected - if we change the record above to store a value that violates the ASN.1 constraint, we get an exception from the database engine, and the database transaction is aborted.

Note that the developer doesn't need to write a single line of code to attain the aforementioned functionality: creation of semantically equivalent schema, instantiation of master/detail records, automatic loading from all necessary tables via proper JOINs, etc - it is all created automatically by the SQLAlchemy mapper. This allows the developer to concentrate only on the functionality that needs to be implemented, knowing that the rest are automatically taken care of by the TASTE mappers.

For additional details and examples of using the SQLAlchemy mapper, the reader is encouraged to stufy the DMT/tests-sqlalchemy folder of the TASTE repository - which includes complex examples, as well as testsuites that are run across many database engines.

A hands on application to message serialization

Using TASTE it is possible to write test scripts using Python ; our Python API allows for exchanging messages with the running system (on host or on target) using simple functions:

queue.sendMsg('runstep', '{ FALSE, TRUE }')   # Send message 'runstep'. Parameters are in ASN.1 format.
(msg_id, val) = queue.getNextMsg(timeout=5)
if msg_id == 'counter':
   print val.Get()   # val is in ASN.1 format


Now think of this use case: we want to store all messages that are received from the application in a database for post-processing ; later, we might want to pick up specific messages based on various criteria, and since we are using databases, we want to benefit from their powerful query systems....

Here is how our API allows to do it (we will use PostgreSQL):

  • First call the backend and create a postgresql database using the follownig command:
$ taste-create-database
  • Then here is what you can do in your Python script:
from sqlalchemy import create_engine, exc
from sqlalchemy.orm import sessionmaker

# Initialize the SQL database
engine = create_engine(
   'postgresql+psycopg2://taste:tastedb@localhost/demo', echo=False)
Base.metadata.create_all(engine)
SessionFactory = sessionmaker(bind=engine)
session = SessionFactory()

(...)
# Wait for a message from the system
(msg_id, val) = queue.getNextMsg(timeout=5)
if msg_id == 'counter':
   # Save data in the SQL database
   val_db = MyInteger_SQL(val)
   val_db.save(session)
   session.commit()
  • And later if you want to query the database:
all_values = session.query(MyInteger_SQL)
for item in all_values:
    # Get value in ASN.1 form
    print item.asn1.Get()

There is a lot of Python magic here done in the background - to query and convert data between ASN.1 and SQL. The important point is that you can work on your data from other tools (pgadmin3), make queries as complex as you may think of, with the guarantee that data is consistent with the specification you gave in the ASN.1 model.