Doesn't work on my system - solved with help of sqlite3 ".log" and ".trace" commands

[vonHabsi]

Doesn't work on my system - solved with help of sqlite3 ".log" and ".trace" commands

[ndwarshuis]

See the develop branch for a new function org-sql-dump-update-transactions which will dump the exact SQL commands to be run during org-sql-user-update into a buffer which can then be saved into a file. It still wraps the commands in their own transactions but it's easy enough to delete the begin transaction; and commit; at the end if you are running the commands manually.

For now I've only done the org-sql-user-update operation because this is where things are likely to go wrong. Is this what you had in mind?

[ndwarshuis]

I just pushed a commit which adds a debug option. To activate it, run (setq org-sql-debug t) in your conf or in the minibuffer. Note that this will only print errors at the bulk transaction level, and it won't print anything if the transaction hangs.

Please try running your commands again with this option enabled and post the output here if you cannot figure out what the issue is based on it.

[vonHabsi]

After updating to the new version and setting org-sql-debug this is what the Message buffer displays.

(org-sql-user-reset)

Resetting Org SQL database
Debug output for org-sql reset
CREATE TABLE transaction: Run successfully [10 times]
Org SQL reset completed
"Org SQL reset completed"

(org-sql-user-update)

Updating Org SQL database
Debug output for org-sql update
DELETE transactions: Not run
UPDATE transactions: Not run
INSERT transactions: Error: UNIQUE constraint failed: tags.file_path, tags.headline_offset, tags.tag, tags.inherited
Org SQL update complete
"Org SQL update complete"

(org-sql-user-clear-all)

Really clear all? (y or n) y
Clearing Org SQL database
Debug output for org-sql clear-all
DELETE transaction: Error: cannot start a transaction within a transaction
Org SQL clear completed
"Org SQL clear completed"

It seems that after the unique constraint error happens to the tags table the transaction is not cancelled and rolled back.

[vonHabsi]

I got it succeed eventually.

One of the headlines had a repeated rag and it caused a unique constraint error. Adding org-sql-debug gave an idea of what was happening, but I had to add a .log and a trace in the sqlite console before I was able to find the exact statement.

I think you should the include the option to turn off transactions and make it easier for users to see the raw sql generated.

I know Doesn't work on my system doesn't sound professional but someone without knowledge of SQL logging and tracing would have found it hard to diagnose.

[ndwarshuis]

Good to hear you fixed it. When you say "turn off transactions" are you suggesting a dry-run-like feature where it just dumps all the SQL statements that would be run without actually running them?

Also, do you have a need for repeated tags since that was the issue in the first place?

[vonHabsi]

Good to hear you fixed it. When you say "turn off transactions" are you suggesting a dry-run-like feature where it just dumps all the SQL statements that would be run without actually running them?

Yes something like that, though I would prefer the statements to be committed one by one, rather than being wrapped in a transaction. Doing it in emacs would probably cause emacs to hang, so dumping them into a file for the user to load them manually would be better.

Also, do you have a need for repeated tags since that was the issue in the first place?

I have no need for repeated tags, but it was not possible to anticipate that it could cause an error when inserted into a database.

[vonHabsi]

This is exactly what I had in mind. Thanks

[ndwarshuis]

Good :) I'll work on the other dump functions shortly. Can you also please open a separate issue with the repeated tag failure you described above (including minimal example)? I'm not sure just looking at the schemas why that would happen.

[vonHabsi]

I don't think the repeated tag failure was a bug on your part. It is one of those things you can't predict.

The unique key on tags is filepath:headline_offset:tag:inherited. Now imagine a line with the tags :a:b:b:c:. The value inserted for the first b will be filepath,headline_offset,b,inherited.
As there is another b, the software will attempt to insert another filepath,headline_offset,b,inherited. As the same values have been entered for the first b this second b will result in a unique key violation.

The error is not really a fault of your code, just that the tagging system in orgmode is not conceived to enforce uniqueness. The only thing you can do if you consider it worthwhile is to test if a tag appears more than once in the same headline and warn the user, but I think the onus is on the user who wants to use org-sql to check their file contents.

The combination of debugging, dumping transactions, and the use of logging and tracing should be enough. If you add some documentation on how to use these options, users will be able to find out why org-sql update are not working.

[ndwarshuis]

Ah got it, it won't be that hard to ensure uniqueness in the database with -uniq and then warn the user if duplicated tags are found.