SQLite Logger Module implementation discussion

[Konan__]

I used the jpowel's SQlite library as "low level interface to the SQLite db file".

What I am trying to do is to create a DQMH module that Logs the test result like the Teststand default database schema (with UUT_Result table, Step table, Limits table, etcc....) 

When you have a Relational database, how do you model it?

What I did up to now is to model it as an object that has:

- the file path of the DB file

- a UUT_Result object  ----> this obj has an array of object "UUT_Step" ----> each step has the info about the step (type of step, measure, unit, ecc..)

When I have the total result at the END of the test sequence (composed of N steps), I also can compute the total time elapsed, so ONLY NOW I can "save" to DB.

At the end I have all the info about the sequence (start time, end time, result OK/FAIL, sub-table with step details, etc....)

Then I could "send the message to SQLlite DQMH" to log this object.

The object is then "converted" into a SQL statement string and processed by the SQLlite Jpowel VIs 

do you think it's OK??

have you done something similar before?

I choose SQLlite because:

1) it's the simplest DB, it's local, no server

2) it's better than Text Log, when later you will use the DB to extract info about your tests, the DB obviously scales better for space/speed/etc.

[joerg.hampel]

---

@Konan__ wrote:

When you have a Relational database, how do you model it?

---

This link might be interesting to you if you're not yet familiar with normalization ("the process of structuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity"): https://en.wikipedia.org/wiki/Database_normalization

---

@Konan__ wrote:

When I have the total result at the END of the test sequence (composed of N steps), I also can compute the total time elapsed, so ONLY NOW I can "save" to DB.

---

You could also INSERT data when starting your testrun, and then UPDATE the data after finishing the tests.

[Taggart]

This is not really a DQMH question, more of a database question.

I am kind of curious why fell like you have to wait until the test is over to write everything at once in one giant SQL statement.  Seems like you could log it as you go. Teststand is certainly capable of that.

[Konan__]

---

@Taggart  ha scritto:

This is not really a DQMH question, more of a database question.

I am kind of curious why fell like you have to wait until the test is over to write everything at once in one giant SQL statement.  Seems like you could log it as you go. Teststand is certainly capable of that.

---

I recreated the Teststand db structure, and used the SQLite.

I didnt think about using UPDATE command actually... that's why I wrote "need to wait the end", because there is a field on th db called "UUT_duration", it has the duration (seconds) of the test.

Of course that value is available at the end of a test... (I use an action engine to track time). 

Tables are related, so I have create an object model, with a "toSQL" method that serialize the object into a SQL statement that insert into all tables (UUT_test ---> one to many UUT_Steps --->one to many UUT_step_result, etc...)

This thing can be "wrapped" into a DQMH module, or an actor.

With Actor, you could also create a sort of derivation (like Logger --> Logger to SQLite / SQLserver / Postgre /etc...)

[FabiolaDelaCueva]

In DQMH, you could create the database LVOOP hierarchy and wrap it in a DQMH module. 

Developers calling the code wouldn't need to know anything about the class hierarchy. The DQMH module can take care of factory pattern and other differences between what inputs different methods may need when direct dynamic dispatch is not possible