Bug 13095

Summary: Use Django ORM instead of sqlite directly
Product: [Yocto Project Subprojects] Security Response Tool Reporter: Ross Burton <ross.burton>
Component: GeneralAssignee: David Reyna <david.reyna>
Status: RESOLVED NOTABUG QA Contact:
Severity: normal    
Priority: Low CC: randy.macleod
Version: unspecified   
Target Milestone: 4.3 M4   
Hardware: x86   
OS: Multiple   
Whiteboard:
OS type for building Yocto: --- Type of Regression: ---
Verified: Documentation change: No (bug/feature does not impact docs)

Description Ross Burton 2018-12-19 14:01:02 UTC 
The NIST/MITRE/etc fetching scripts write to the sqlite data directly, using a generated Python file to expose name/column index mappings.

Instead, these tools should just import the database model and use the standard Django interfaces, so they don't rely on the underlying data model.
Comment 1 David Reyna 2018-12-19 18:41:48 UTC 
These database actions were originally written in Django.

They were explicitly moved to command line scripts away from Django, because:

1) The scripts literally run an order of magnitude faster that the Django wrappers. What takes an hour now took half a day or more before.

2) The command line scripts are much more compatible with background update scripts and CRON jobs, a crucial requirement.

- David
Comment 2 Ross Burton 2019-07-16 09:13:39 UTC 
Coming back to this, to productise srtool sqlite really isn't up to scratch.  If the update hooks were just management hooks then calling them from cron is trivial.
Comment 3 Ross Burton 2019-10-01 15:08:54 UTC 
Another good reason to use the ORM directly is that it lets us switch from sqlite to postresql.
Comment 4 David Reyna 2023-10-18 20:55:20 UTC 
Per my previous comment, the pure Django implementation is impossibly slow for the magnitude of CVE and defect records.

To accommodate the underlying database model for these backend scripts, we have added the script "bin/common/srtool_sqa.py" to provide the needed abstraction. The databases SQLite, Postgres, and MySQL are supported.

In addition, we have provided a migration script to move data from the original SQLite to for example Postgres. 

The migration script also works the other way, for example Postgres to SQLite. The reason is that is it imperative to be able to back up the database in case of errors or host crashes. Postgres does not have a model for backing up its data base other that generating SQL statements. This migration script does exactly that but puts it into an SQLite database, which given that it is one file is easy to preserve remotely, plus it is a form that can be directly accessed bu the SRTool.

David