Giter Site home page Giter Site logo

Comments (10)

ndwarshuis avatar ndwarshuis commented on July 20, 2024 1

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?

from org-sql.

ndwarshuis avatar ndwarshuis commented on July 20, 2024

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.

from org-sql.

vonHabsi avatar vonHabsi commented on July 20, 2024

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.

from org-sql.

vonHabsi avatar vonHabsi commented on July 20, 2024

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.

from org-sql.

ndwarshuis avatar ndwarshuis commented on July 20, 2024

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?

from org-sql.

vonHabsi avatar vonHabsi commented on July 20, 2024

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.

from org-sql.

vonHabsi avatar vonHabsi commented on July 20, 2024

This is exactly what I had in mind. Thanks

from org-sql.

ndwarshuis avatar ndwarshuis commented on July 20, 2024

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.

from org-sql.

vonHabsi avatar vonHabsi commented on July 20, 2024

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.

from org-sql.

ndwarshuis avatar ndwarshuis commented on July 20, 2024

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.

from org-sql.

Related Issues (18)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.