Made notice and cve queries case insensitive

[mtruj013]

Done

• Made notice and cve queries case insensitive

QA

• Check out this feature branch
• Run the site using the command ./run serve
• View the site locally in your web browser at: http://0.0.0.0:8030
• For CVE visit: http://0.0.0.0:8030/security/cves/cve-2018-19662.json
  • change the letter case for the cve id (in this case cve-2018-19662) and run again
  • see that the result is unchanged
• For USN visit: http://0.0.0.0:8030/security/notices/usn-4189-2.json
  • change the letter case for the usn id (usn-4189-2) and run again
  • see that the results are unchanged
• Note: You can also pick a random cve or usn to QA this with

Issue / Card

Fixes https://github.com/canonical-web-and-design/ubuntu-com-security-api/issues/60

[nottrobin]

The CVE tests are now broken, which is odd... https://github.com/canonical-web-and-design/ubuntu-com-security-api/runs/5021148875?check_suite_focus=true

[nottrobin]

@mtruj013 so I can walk you through how to debug the tests yourself. I should probably do this tomorrow if I get a chance.

But the error you're getting is because the .get(cve_id) way of retrieving CVEs doesn't seem to be returning anything, according to the test at least. Does it definitely work in real life? In which case the testing method is kinda broken. Or is it maybe actually broken? In which case we need to revert the code to the old .filter() method.

[mtruj013]

@nottrobin Ok, I tried everything I could locally as it was before and then with the changes and then did the same with the actual tests. Here are the results:

• get_cve works with .get() on my local machine BUT fails the actual test
• bulk_upsert_cve does not fail the test when using .get(), BUT it does fail when get_cve uses .get()
• delete_cve fails its test when using .get()
• just for the sake of completeness even though this was already addressed in earlier comments, get_notice cannot use .get() since its already filtering the query beforehand and as such, it passes everything

[nottrobin]

Ok so tomorrow let's look into the disparity between the python test and your actual testing for retrieving CVEs. That sounds like a false negative which isn't good. We shouldn't have testing infrastructure that can return a false negative like that.

[nottrobin]

It looks like the current testing uses sqlalchemy.testing.mock and magicmock to basically create a bunch of fake function responses based on specific calls that the tests make. This, if course, doesn't really test the calls themselves, it tests the mocks, which isn't all that useful.

I think a better approach might be to use e.g. testing.postgresql (https://eradman.com/posts/ephemeral-databases.html) to create an ephemeral database so we can be sure sqlalchemy is behaving as it would in the real world.

But let's look into this more deeply tomorrow.