|
Welcome,
Guest
|
Suggestions
TOPIC: Moving ThinkingRock from XML to Database
Moving ThinkingRock from XML to Database 15 years 7 months ago #1345
|
I hate cross-posting as much as the next guy, but the public forum is positively *dead*. Here's the link to the original thread:
www.trgtd.com.au/forum/viewtopic.php?f=15&t=3348 Here's what I said there: So you have one of your worst lurkers in your midst. :P I finally broke down and bought a membership, and am downloading 3.0 now. Installed the iPhone version yesterday, and was sad to take note of the fact that I had to click File->iPhone Sync in order to do sync over the air (I've set up NAT to be able to access it from the road). Then I realized why - it's the XML back to rear it's ugly head. It's the same reason you can't reliably have two copies of TR accessing the xml file simultaneously - you either risk corruption or the two falling out of sync. So my first coding language is perl, followed by javascript, then java. So I'm not *completely* out of my mind, and I do have a reasonably solid grasp of OO coding... The source code is out there and available - I'm not in a position to crack it open this very second, but here's the thought I was going to give a whirl, and I'm wondering if anyone else would be interested in helping: 1. Abstract away any and all data source calls. If my understanding is correct, the XML file gets loaded into memory at launch, the data stays in memory until a save is issued. What I would like to do is instead of having those calls pull from a stack in memory, have them instead call a method, say for stupidity's sake .get_topics() (oops, sorry, java uses camelCase, so .getTopics() ), and for the moment, move the memory call to that method, same would go for .setTopic(), or really any other call to memory. 2. Make sure we didn't break anything. TR should still function 100% as it did before. 3. Create a database to work against. This is tricky, because SQLite seems like a no-brainer here. It's only tricky because for the last 4 years, I have not coded against any one database. Since Perl is my first choice of language, I always use SQL::Abstract. I don't know if there's a similar class available for Java, but if there is, I would prefer to do that, so that we might target SQLite, but allowing other databases later becomes far simpler. In any case, lay out the database versus the current XML layout. Make sure that we have relationships in order - ie, if you delete a context, it doesn't orphan a bunch of stuff that was using that context before. At least in MySQL, you can have the database handle the cleanup and your code doesn't even have to know about it. I don't know if that's true for SQLite or other databases. Some research is needed - but I imagine TR already has those routines for cleaning up the native XML. 4. Make sure import-to and export-from xml works. In Perl, I use XML-Twig for this sort of thing, but I'm sure there are fine classes in java to do the heavy lifting. I expect this to be simpler than it looks. 5. Slowly, one method at a time, go back through the new abstracted away methods we've created, re-write it to make the calls against the database instead of against memory. Test, test, test.... 6. Once we know all is well, allow TR to optionally export to legacy XML at specified intervals, or on command. 7. Relish the now-freed memory since you're not storing everything in memory anymore. :) Now I may have just wasted a ton of time typing all of this up. I'm not sure. Maybe steps have already been made in this direction, but everything I've read on these forums would make me think that this is an obvious direction to go. I'm not above putting my coding where my mouth is and do the whole "if you want it so bad, type it yourself", but since the actually storage of data is rather critical (just a tad...), input from others before I go wasting my time would be a "good thing". ;) I love the concept of being able to leave TR to listen for iPhone sync requests while away from the computer rather than having to do File->Sync to iPhone, and you don't have to worry about corruption nearly so much. It also allows you to store your db on a network share/nfs/afp, and run TR from any location that can ready it and have current info. Sure, it will slow down performance, but it should work nicely. Anyway, I'd love to hear your thoughts. Please don't bash me too hard if I'm re-treading something already in the works. I did use the search button, honest! As far as a Java equivalent of SQL::Abstract, I found this: www.is.informatik.uni-duisburg.de/projec...package-summary.html Just at a glance that should serve nicely. Again, I'm interested in other's opinions... EDIT: Nevermind the above, Java comes bundled with the solution already: java.sun.com/javase/6/docs/technotes/guides/jdbc/ EDIT MY EDIT: No, nevermind. Again. JDBC ensures connectivity, but raw SQL is still in used, not abstracted. The simplest measure would be to make sure all queries are ANSI SQL compliant to guarantee that it would work with whatever DBMS is in use, but even working with MySQL I wind up with proprietary calls. :( Just to give you an idea of what SQL::Abstract looks like: #!/usr/bin/env perl
use strict; # always!
use DBI; # Perl's native database interface library
use SQL::Abstract;
# Establish our database handle for use with DBI. This just defines our dbms.
my $database_handle = DBI->connect('DBI:SQLite:dbname=/path/to/ThinkingRock.db');
# my $database_handle = DBI->connect('DBI:mysql:ThinkingRock:my.ip.address.here','username','password');
# Create the abstractor object.
my $sql = SQL::Abstract->new;
# Run my query through the abstractor's select() method and put them into
# Perl's DBI interface objects.
# The following could be just as easily written as:
# SELECT column1,column2,column3 FROM myTable WHERE column1 != 'doNotMatchMe' AND column2 = 'MatchMe';
my ($statement,@bind) = $sql->select(
'myTable',
[
'column1',
'column2',
'column3',
],
{
'column1' => {
'!=', 'doNotMatchMe'
},
'column2' => {
'MatchMe'
}
}
);
# Now that the abstractor object has what we want, we can use the prepare method from our
# DBMS' $database_handle to prepare it appropriately.
my $statement_handle = $database_handle->prepare($statement);
# Now execute the SQL
$statement_handle->execute(@bind);Seems like a roundabout way of doing things, and I won't argue about how fugly OO Perl is. :P DBI has been around in Perl long enough that escaping it is just about impossible. Basically, I'd like to be able to do CRUD stuff abstracted away to make sure it works no matter what DBMS gets chosen. There's ActiveRecord-JDBC, but that's a port of ActiveRecord for Ruby on Rails, literally you wind up with Ruby code embedded in your Java, and somehow that solution strikes me as asking for trouble. :( Bookmarking for reading later: slingshot.dev.java.net/one%20minute/SlingshotSQL.html slingshot.dev.java.net/one%20minute/DynaModel.html I'll delete this post after I have a chance to look it over. At first blush, this looks like the solution to what I wanted. |
|
Last Edit: 15 years 7 months ago by numbski.
The administrator has disabled public write access.
|
Re:Moving ThinkingRock from XML to Database 15 years 7 months ago #1346
|
I just hit my first bump in the road. :(
I checked out the sources from subversion, and they're the 2.x code - not the 3.x code. Is there any way to get a more current set? Good news is that unless you changed going into version 3, you're using xerxes for xml handling, and that's something I've at least encountered before, so I can be looking into that. I'm trying to make sense of the DataSource code to get a feel for at least how you're handling it now, so I can map out how to do the change. I don't know if this would be more appropriate as a plugin or as core code (I would think the latter, except that you have no reason to trust me...), but I cannot find any documentation or examples on how to properly write a plugin, as I've had an inclination to do a test plugin for Google Voice. Oh, and looking at tr.email, adding imap should be pretty trivial too... |
|
Last Edit: 15 years 7 months ago by numbski.
The administrator has disabled public write access.
|
Re:Moving ThinkingRock from XML to Database 15 years 7 months ago #1352
|
Hi
Some points: - The TR - iPhone sync will be improved so that you will not have to run it manually. - The iPhone application uses a data base with data that is not the same as the TR data - it is not a simple matter of transferring TR data (as is) to the iPhone application. - The next version of the TR iPhone application is being developed so the data is likely to change. - I plan to change TR to use a database in the near future (but need to do goals first). - TR does not use xerces for the main data file it is using XStream. |
|
Last Edit: 15 years 7 months ago by Jeremy.
The administrator has disabled public write access.
|
Re:Moving ThinkingRock from XML to Database 15 years 7 months ago #1358
|
Yeah I was aware on all points above. :)
I still cannot locate the sources for 3.x. Even if I didn't touch the datasource code at all, there's still the matter of imap support and multiple email account support, both of which I could look into. I just really had my motivation up to dig into getting database backends going because that would be the main stumbling block for multiple client access. I know the iPhone isn't a *true* client, but the situation where you want to machines accessing the same xml file is a real problem. Put it into a sqlite or mysql database, problem solved. (Actually, I cannot recall if you wind up file-locking sqlite or not...) You fix the multiple-client access, then that opens a path down the road if you wanted true multi-user functionality. Then there's the matter of me wanting to do up a Google Voice plugin. Where can I find the 3.x codebase? P.S If you're looking for wishlist stuff for iPhone, capturing voice memos would be a good call. ;) |
|
The administrator has disabled public write access.
|
Moderators: claire
Time to create page: 0.531 seconds