Assistance with using SQLite in PAMGuard
(1.1) By AmberCrittenden (ambercrittenden) on 2021-10-29 06:15:09 edited from 1.0 [link]
Hi, I'm a PhD student and have been attempting to use SQLite as a database in PAMGuard. I have loaded an SQLite database module into PAMGuard, but when the analysis is finished, opeing the database in SQLite Studio shows no data (all fields are empty) even when I can see the detector modules are making detections. I have also attempted opening the SQLite database in PAMGuard Viewer after PAMGuard has finished running, but all of these fields are also empty. Do any PAMGuard users have suggestions as to why I am not able to view the output in my SQLite database? Any and all insight would be greatly appreciated!
(2) By SeverKetor on 2021-10-07 02:28:37 in reply to 1.0 [link]
This sounds more like a PAMGuard issue than an SQLite one. If I had to throw a dart blindfolded, I'd guess it's not committing transactions. Maybe there's a Save button/option/whatever that you need to hit? You'll have better luck if you ask for PAMGuard support elsewhere.
(3) By Larry Brasfield (larrybr) on 2021-10-07 02:36:34 in reply to 1.0
Without being a PAMGuard user, I can still suggest some clarifications that will help others (who might be PAMGuard users) to sort out what is going wrong for you. My comments follow quoted fragments: > I have loaded an SQLite database module into PAMGuard, Is an "SQLite database module" the same as an "SQLite database"? Or are you simply saying that you have enabled PAMGuard to access SQLite databases generally? > when the analysis is finished, opeing the database in SQLite Studio shows no data Here, it will be crucial to detail what "the database" means. We can assume that you intend to refer to a database that you intended to access from within PAMGuard, but you need to specify what, specifically, you wrote/did/clicked etc. to designate an actual database file for the SQLite library to open and operate upon. > shows no data (all fields are empty) Are the expected tables present? Do you mean that no rows were inserted when they should have been? Please use conventional terminology, where rows in a table correspond to tuples of the column values. (The term "field" often means a member of such a tuple. Is that your usage here?) > even when I can see the detector modules are making detections. If you were to briefly explain the importance or consequence of this in terms of resulting modifications upon the actual database opened by PAMGuard, folks here who are not PAMGuard experts may be able to help. > I have also attempted opening the SQLite database in PAMGuard Viewer after PAMGuard has finished running, but all of these fields are also empty. To me, (a PAMGuard non-user), this means one of two things: (1) the effects of your PAMGuard run on the database it actually opens are not what you are expecting; or (2) the database being modified by your PAMGuard run is not the same one you are peering into with other tools (including "PAMGuard Viewer".) I do not see enough information in your post to distinguish these cases. > suggestions as to why I am not able to view the output in my SQLite database? (Not to be flippant here, just analytical:) Either "the output" is an empty set, or it is landing somewhere other than "\[your\] SQLite database". Have you run an experiment where you use PAMGuard the same way, except with a different DBMS where "the database" unambiguously refers to something you can view with other tools? Or put differently: Why do you believe "the output" is not the empty set?
(4) By AmberCrittenden (ambercrittenden) on 2021-10-29 06:49:48 in reply to 3 [link]
Hi Barry, thank you for taking the time to respond my post. Yes, to my understanding an “SQLite database module” is the same as an “SQLite database”, a ‘module’ is the terminology used in the PAMGuard user manual to describe an element added to the PAMGuard interface. Yes, by “database” I am referring to a database intended to be accessed within PAMGuard. I added the database by following the instructions described in the PAMGuard help manual which directed that I designate SQLite as the chosen database system in the database selection menu. I then created an SQLite database file (.sqlite3). I need the database to store count data generated by PAMGuard when it analyses .wav audio files. Yes, the expected tables are present, but they do not display the count data expected. I apologise for my lack of convention terminology; I am still attempting to learn the appropriate terminology as I familiarise myself with these programs for the first time. Yes, by ‘field’ I am referring to tuples in the table rows. The detector module in PAMGuard is counting the number of whistles present in an audio file, and I am attempting to have the sum of that count data displayed in the SQLite database file. When the whistle is present in the audio file, I can visually see PAMGuard is detecting it with the real-time spectrogram, but that count data is not shown anywhere in the SQLite database file. I believe your first assumption is correct, the effect of the PAMGuard run on the database is not as expected; the tables are generated but the count data is absent. I’m confident “the output” is not an empty set as I can visually see that PAMGuard is detecting the whistles in the audio file correctly, and therefore I believe count data should be displayed in the SQLite database file. Another piece of information that may help is that when I open the SQLite database file in SQLite Studio is receive the following error messages: Could not restore window 'Whistle_and_Moan_Detector (TowST)', because database TowST could not be resolved. Could not restore window 'Whistle_and_Moan_Detector_Localised_Contours (TowST)', because database TowST could not be resolved. Could not restore window 'PamguardModules (TowST)', because database TowST could not be resolved. Could not restore window 'Sound_Acquisition (TowST)', because database TowST could not be resolved. Does this shed any light on what my issue could be? That is, that the count data from the PAMGuard processing is absent from the SQLite database file despite visual confirmation that there should be count data present? Thank you again for your time.
(5.1) By Simon Slavin (slavin) on 2021-10-29 07:47:53 edited from 5.0 in reply to 4 [link]
Please don't use SQLite Studio for debugging. We don't know how it works and we don't know what its error messages mean. Instead, please use the SQLite Command Line Shell: <https://sqlite.org/cli.html> Which you can download as part of the 'Precompiled Binaries' package from <https://sqlite.org/download.html> This program was written by the same team which wrote SQLite itself. If it does anything wrong, they know whether the problem is with that program or with SQLite itself. Make sure your PAMGuard program – and everything else – doesn't have the SQLite database open. Then use the command line program to open the database and examine it using commands like <code>.schema</code> and <code>.table</code> and by just typing SQL commands. Does that program report that the database file contains what you expected ?
(6) By Larry Brasfield (larrybr) on 2021-10-29 12:57:27 in reply to 4 [link]
> \[lots of PAMGuard-specific moans\] because database TowST could not be resolved. ... > Does this shed any light on what my issue could be? It sheds the same light as your previous post which appeared to show that "the database" of which you spoke was not well defined. That is why I advised: you need to specify what, specifically, you wrote/did/clicked etc. to designate an actual database file for the SQLite library to open and operate upon. A problem we often see in this forum is this: A user specifies a filename for the database file but does not know where in their filesystem(s) that file actually resides. Then they get confused because their program's effect(s) upon "the database<sup>1</sup>" cannot be observed when they use another tool to examine "the database<sup>2</sup>", which happens because database<sup>1</sup> is a different byte sequence than database<sup>2</sup>. Did you specify an <u>[absolute path](https://www.bing.com/search?q=absolute+paths)</u> as "the database"? If not, your problem is highly likely to be the same above-described one we see here often. Until I see an answer to this question, I will assume you are suffering that same problem. The solution is to specify an absolute path to PAMGuard for its database, then specify the same absolute path to whatever other tool you use to observe PAMGuard's output data.