[Helma-user] DB Transactions and Insert order

[Maksim Lin for technical support mailling lists]

> I've noticed two things:
> 
> 1) Helma does not appear to use the database's transactions. 
> I found a thread about this from 2001 with Hannes offering to 
> implement it and one person voting it up (as a default-off 
> datasource-selectable feature)--can I add my upvote? Except 
> that I think here in 2008 most people not using MySQL know 
> what transactions are and pretty much expect they'll be used, 
> so maybe it should be default on in a future version?
> Even MySQL supports transactions if you use InnoDB tables. 
> I've searched the source code as best I can, and it doesn't 
> look like this has actually been added yet. Please let me 
> know if I missed something. The two advantages of 
> implementing this would be (1) that if you get a DB error in 
> the middle of a commit, the DB is not left in an inconsistent 
> state, and (2) often much better performance in the case of 
> bulk object creation.

Hmm, I wonder if this is just a matter of turning off "auto-commit" on
the JDBC connection?
I would then imagine the commit on the JDBC conn would go around line
418 of Transactor.java since helmas "own" transaction commit happens
there for the benefit of the XML DB. The tricky thing looks to be
figuring out how to get your hands on teh undedrlying JDBC conn object
in that commit() method of the Transactor class...

Though its been a while since I've done any "raw" JDBC coding so maybe
I'm making it sound easier then it iwould be to implement.

 
> 2) I designed my DB with some foreign key constraints not 
> expecting Helma to do anything particular about them but 
> expecting that there would not be any conflict. However, as 
> I'm bulk importing hierarchical data, I'm getting foreign key 
> constraint violations. I always create the parents before 
> their children, so as best as I can tell during commit() 
> Helma doesn't necessarily insert nodes in the order they were created.
> Perusing objectmodel/db/Transactor.java, I see that "dirty" 
> nodes are kept track of with a HashMap, which of course 
> doesn't guarantee any particular order when you convert it to 
> an array. Am I reading it right?
> Could this be fixed? If I'm reading right, it seems that just 
> using a LinkedHashMap instead of a HashMap would do the trick.
> 

This on the other hand I think is correct. And I think you are right,
keeping a time ordered list of dirty Nodes pending to be commited to the
DB would be better.

Maks.