Database Updates

Photo provided by Baqash

As some of you might have noticed, I took AQ down for a bit today to do a massive update of the code. You won't see any changes--at least you shouldn't see any changes--because these were all "under the hood" updates. As I mentioned before, the database has been having trouble executing some of the queries efficiently, and the problem I've been having is that the piece of code I used for creating queries wasn't really designed for nitty-gritty details like telling the database precisely the order that tables should be joined or how best to execute certain queries. In the past, the database was generally pretty good at figuring out that stuff on its own without much help from me. Now.... it needs a little extra help, but the code hadn't been designed for that sort of thing.

So this update helps fix that problem. I completely replaced the code that put together queries, and it has a lot of options I can throw at it to suggest better ways of joining tables together. I can use subqueries, which I couldn't do before. I can change the type of join and force two specific tables to link together. I can throw parenthesis into the queries where needed. I can force the database to use certain indexes or ignore others as needed. (Well, technically, the old code could do that particular trick as well, but not as slickly or reliably as the updated code.)

All-in-all, it's a much more flexible piece of code that will allow me to fine-tune super complex queries like never before.

However, one thing it doesn't actually do is fix any slow queries. For a couple of reasons, really, but mainly because running a slow query from the live site doesn't necessarily show up when I run it on my development machine or vice-versa. The live site has traffic from lots of people at once which can cause locks and issues that won't show up on my development machine. Not to mention that hundreds of people using the live site will generate queries that cause problems that I could never hope to think of trying on my test machine.

So this update is just a stepping stone to faster, better queries. Now that it's running on the live site, I can monitor for problem queries and take the actual query and figure out where it's having trouble and figure out how I can modify it to work better--and the underlying code can be modified as needed to tweak the queries.

 Anyhow.... long story short.... progress is being made, but I'm not done yet. 

This, however, was a massive update that changed the very fundamental process of creating database queries, and it undoubtedly could have broken stuff. Please be patient--I'll try to fix any problems that crop up as quickly as possible. 

Thanks for being so patient!

Inaccessible logbooks

Photo provided by Thunderbird.

Yes, I know logbooks are still inaccessible. Yes, I'm still working on it. This issue with the slow queries has been an enormous headache for me. I lose sleep over it. I've been making progress, but it's been far slower than I had ever imagined. Basically, AQ broke the database. The size and complicity has overwhelmed the database.

But I keep getting emails and seeing posts about people thinking there's stuff in their logbook that they absolutely must get to and believe that there is no other way of doing it. For the most part, almost everything in your logbook can be accessed by doing an Advanced Search. From plants, to finds to unpublished plants, adoptions, and event signups.

Most of the logbook pages can be generated by searching for yourself as the planter, finder, attempter, carver, etc. Some pages, like donations, are a combination of those. (Search for boxes that you carved but are not the planter for.)

To see your unpublished boxes, just be sure to click the box for "Show unpublished boxes" under the "Other options" section. You don't even need to add planter information--AQ will only show you your own unpublished boxes anyhow. 

Admins can actually search everyone's unpublished boxes in this manner, however, so we have to include ourselves as the planter if we want to see our own unpublished boxes. But AQ knows that non-admins aren't supposed to see other people's non-published boxes and therefore will only show you the accessible ones--which are just your own. (Sometimes people report a problem with the location or are having trouble listing a box, so this gives admins a way to access the listing and see what's going on.)

If you need to see a list of your unlisted finds and/or attempts as well as your exchanges, you can find a link to those at the bottom of your profile. There a number of links there to download some of your data: finds, plants, trackers, exchanges, etc. They're in a CSV format so you should be able to import those files into a spreadsheet program and enjoy to your heart's content.

Anyhow, until logbooks are up and working again, be sure to check the Advanced Search page. It probably has a lot of options that you didn't even realize existed if you haven't used the page much before. It really is the ultimate search tool for letterboxes. *nodding* =)

And for the few things that aren't available from the Advanced Search page, there are still other places where the data can be accessed.

Anyhow... back to working on AQ again. Sorry again for the inconvenience! I'm really not asleep at the wheel, though! =)