Query Queries

The recordkeeping project is getting a bit out of hand.  It’s fun to work on, but if there had ever been an original scope of project (doubtful), it’s been left in the dust long ago.

100% my fault- I’ll be working on the approval tracking option and realize; “This really needs email notification to be truly useful.”  Completely true- no one wants to log into the app each time they need to check if there’s been a response- they should be able to get email updates.

But it shouldn’t be required, so now it has to be an option.  And who should get the email updates?  Everyone involved?  Just those with posting auth? A small addition snowballs into a couple weeks of work.

One of the tricky parts of finishing the approval option was the SQL involved.  The original feature had no tracking feature- there was basically just one large textarea field where posters could all add text/edit any existing text.  So the design of that db table was very simple.  But in adding both individualized entries, and tracking individual approval recordings, we added quite a bit of complexity on the db front.

Not something I have a ton of experience with, so there were some false starts.  Making sure each poster’s approval setting was unique depending on the recordkeeping category entry required a few joins, but we got there eventually.

All that work done, and we decided that the main entry screen should really be more of a timeline- tracking normal comments, file uploads, and approval entries.  It’s tough when a mid-project redesign has to happen, but sometimes it really is for the best- this was a good idea and fixing it halfway through made a lot more sense than finishing and shipping the project, only to promise another update later (which might never happen- there are other areas to improve!).

So there were some good lessons learned regarding database design.  And some frustrating lessons learned regarding syncing the UI and the backend.  Makes me appreciate the new JS frameworks that do a lot of that work for you- and do it much better than I am!

Where were we?

That’s right- the search feature.

After a bit of a delay, we’re back to work.  The current issue: adding the placement of the search button.  We wanted 3 options- in the header, in the footer, and in the left menu.  This was complicated by the fact that there is a layout option that doesn’t include the left menu (the horizontal menu).  In the end, we decided that if the user has the horizontal menu enabled, and they try to select the left menu placement for the search button, it will simply default back to the header.  Should eliminate any confusion (probably not).

The database tables are updated, the php conditionals are in place- the location of the search button is now customizable by the user.  But it still doesn’t really work very well.  Currently, the function just uses a simple sql ‘Like’ clause with the search keyword(s) inserted in dynamically.  But it returns the whole text area where the keyword is found.  We’re trying to find a way to limit it to a certain number of words- should be a simple matter of getting x number of characters before and after the keyword, and then finding the nearest space to cut it off at a word.  Actually, as I typed that, I realized there is a better way.  First, check to see if the keyword appears in the first sentence- if so, return that sentence.  If not, grab back to the previous punctuation and forward to the next punctuation (or end of the area) and display that.

Time to try it out!

On another note- I’ve started experimenting with Django.  It seems really cool- the model to database table relationship seems quite intuitive, and the templating system has been fairly easy to use so far.  The difficult area (for me) has been the url mapping.  It looks like once it’s set up, it is a great feature, but figuring out the regex based system is not going well for me.


I find that I get sidetracked a bit too easily.

I should be working on an upgrade of the recordkeeping feature.  As stated before, we’re looking to add a lot more functionality, as well as a nicer user experience, and it’s all likely going to be pretty difficult with the existing code base.

But I thought we might start with a simple facelift on the search feature.  The current interface is clunky and confusing- opening results in an ugly new window on top of the site, fullscreen.  Clicking any result in the listing will reload the page behind the search screen- so in effect, it looks like it’s doing nothing at all.

So we started sprucing it up a bit- open in a small modal, ajax requests for the results instead of leaving the page, term highlighting, and more.  But the results were off.  Searches were returning pages that didn’t have the term- why?  Turns out the search function was also searching any embedded html code.  Search for ‘document’, and you get a listing of every page with a jquery function embedded.  Search for ‘style’ and get every page with custom css inlined (bad practice, I know, but having a wysiwyg toolbar is a necessity).

How did the original developer get around this?  Simple- they didn’t.  The search results just returned a list of pages- no preview text.  Users would see the list and click on any link to a page, but there was no guarantee that their keyword would actually be on that page (it could just be a term in the source code somewhere).

So that’s been the bulk of the work so far- sanitizing the search results from the db to remove any code.  That way, a user only sees relevant results.  I naively searched for a way to only get non-code text from the db using some SQL magic, but (as anyone with a tiny bit of knowledge should know), that doesn’t seem to be possible.  So we’re reduced to getting all the results, then using some PHP regex checks to remove stuff that looks like code (anything between <script> or <style> tags, for starters).  The final loop over the results in the PHP file checks if the row includes the search keyword after the checks for code- if not, it reduces the count by 1 in order to keep the ‘total results’ number correct.

Is this the best or most efficient way to do it?  Probably not, but it works for now.  And we still have a lot of work to do before getting back on the recordkeeping track!

Keeper of Records

The photo album feature will be shipped this weekend, so it’s time to stop talking about that (because if we stop talking about it, maybe it won’t crash and burn when pushed to production).

Next up: the recordkeeping feature.  It’s fairly useful- we use it for our own internal tracking- but it’s also fairly ugly.  And fairly non-user-friendly.  It needs a facelift: new interface, easier to navigate, no more tables.  It also needs to work out a bit: strengthen up the process by adding drag/drop functionality for adding documents or images to a record.  Finally, it needs to learn some new skills: we’ve had multiple requests to allow a custom request form feed directly into a recordkeeping entry, and I think this is where the bulk of the work will end up going.

Creating a form that dumps entered info into a database isn’t too hard (though security concerns should always be taken seriously).  But when the form can be customized by the customer, it seems to become a lot more complex.  For example: when creating the table to store the form data- how many attributes should it have?  One form might have 3 text fields, 2 checkbox groups, and a drop down select list.  Another might have 7 text fields.  How should we design that table to store the info?  Does it make sense to create a new table for each form, and just make the columns ‘on the fly’ as the first form is created?  The advantage to that is that each subsequent form (of that same category) would already have the table, and could simply be entered.  Or maybe we restrict forms to be used with a database backend to a limited number of fields- let’s say 5 for example.  But even then, those five fields could be any one of 8 options for field type, so making sure the schema matches the data coming from the form could be an issue.

We don’t have any answers yet, but sometimes it helps just to write down the questions.

More user shenanagins

Did I spell shenenagins right?  Probably not.

More work done on reorganizing and upgrading the user account creation screen for the CMS I’m working on.  ‘Above the fold’ is now the welcome message, help link, and basic user info (name, username, password, contact info, notification option).  Previously, the email info entry had been at the bottom- caused a good deal of confusion.

Then there are save options.  Setting up a new user requires an initial save after entering the basics, so this seemed like a good idea.

After that are the other options.  Home/directory/email list config.  Admin authorization options, etc.  These are much less vital (though home assignment is an important step).  Need to do a little sql magic to get the current info from the db for some items (directory privacy settings, current email lists subscribed to).  Then we will work out the rest of the client side js checks on the form itself.