Archive for the ‘Database’ category

MySQL using two timestamps for created/updated values

October 24th, 2007

I’ve seen this on and off for years, but never have it handy. So, here’s a quick demo:

mysql> create table sample ( timeUpdated TIMESTAMP, timeCreated TIMESTAMP, val INT );
Query OK, 0 rows affected (0.24 sec)

mysql> insert into sample (timeUpdated, timeCreated, val) values (NULL,NULL,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from sample;
+———————+———————+——+
| timeUpdated | timeCreated | val |
+———————+———————+——+
| 2007-10-24 15:25:03 | 2007-10-24 15:25:03 | 1 |
+———————+———————+——+
1 row in set (0.00 sec)

mysql> update sample set val=val+1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from sample;
+———————+———————+——+
| timeUpdated | timeCreated | val |
+———————+———————+——+
| 2007-10-24 15:25:23 | 2007-10-24 15:25:03 | 2 |
+———————+———————+——+
1 row in set (0.00 sec)

The basic idea is that by forcing NULLs in to the TIMESTAMP columns, they’ll both be triggered to use the current timestamp value. But when you don’t reference them, only the first TIMESTAMP column will be updated, and if you define it as the ‘last updated’ column, you’re good to go.

There are also ways to handle this via triggers, and loads of ways to handle this sort of logic in your app layer. I think the grails GORM layer will automatically add dateCreated and dateUpdated values behind the scenes, so if all your data is controlled by GORM, you’re set to go. If it doesn’t do it now I believe it was talked about being added soon.

WebDevRadio MySQL DBA interview

August 17th, 2007

Over at webdevradio, I’ve posted an hour long discussion I had with Keith Murphy, a local MySQL DBA.  He’s recently started at icontact.com, fast becoming one of the industry leaders in managed mailing lists and community communication tools.  I’ve known David Rasch, their CTO, for close to 2 years now, and have watched them grow to become one of the larger PHP/MySQL outfits I know of.  Keith was a great find for their organization, and his experience is going to help them manage their phenomenal growth  over the next few years.  Additionally, Keith recently started a magazine for MySQL developers and users called – what else? – MySQL Magazine.  The podcast interview discusses both of these topics in more detail, and I hope you all enjoy it as much as I had putting it together.

Generate random test data

August 16th, 2007

I put together a small utility to generate random user data several months back. I realized yesterday that I hadn’t released the source code – I thought I had. This generates plausible names, addresses, phone numbers and social security numbers. The phone number area codes don’t generally match up with the address, but the addresses are accurate in that the city/state/zip all match. The addresses are currently US-based only. The code will also generate SQL statements for you to insert directly in to your database if selected.

Have a look at the code in action over at http://www.fosterburgess.com/v2/random.php and click the ‘download source code’ link at the bottom to grab you a copy. If you have any enhancements to share, feel free to send them back and I’ll add them in.

Hard to keep up with evolving web tech!

May 30th, 2007

Whew!  The last several months have brought a flurry of web tech activity, the likes of which I haven’t seen in years.

Apollo/Silverlight/JavaFX all are aiming at reinventing in-browser development.

Yahoo Pipes, Ning, Coghead and some other platforms are giving us new ways to think about web-based development.

The YUI/Dojo/Scriptaculous/Atlas/jQuery/GWT AJAX toolkits – what can I say?  They each bring their own strengths to the front-end development arena.  It’s hard to go wrong choosing any of them, and they all illustrate the point that there’s very little reason to be doing AJAX by hand if you’re doing it at all (and I’d recommend looking at Aptana.com if you’re looking for a decent Javascript editor).

Dojo had introduced an interesting ability – the ability to use local storage.  A local storage API abstracted the read/write to local storage mechanisms, including cookies and Flash.  This Dojo functionality was pretty interesting, and may still be the best way to handle this sort of need in a cross-platform way, due to Flash’s current popularity and installed base.  It will throw up warnings after so many kilobytes of local storage for permission to use that much drive space, which may scare off a few people, but used judiciously, it’s a useful hack.

Google today announced Google Gears, a plugin which provides a local SQLite server, data cache management, and a worker pool to manage multiple worker processes, increasing browser app responsiveness (Firefox, I’m giving you the evil eye).  Some sample code from http://code.google.com/apis/gears/

  try {
    db = google.gears.factory.create('beta.database', '1.0');
  } catch (ex) {
    setError('Could not create database: ' + ex.message);
  }

  if (db) {
    db.open('database-demo');
    db.execute('create table if not exists Demo' +
               ' (Phrase varchar(255), Timestamp int)');

    // Initialize the UI at startup.
    displayRecentPhrases();
  }

I suspect this may be jumped on by many smaller ISVs as a way to provide local data storage and SQL-ish functionality in a cross-platform way.  There aren’t any real broadly supported, viable  options for client-side functionality apart from Windows, which isn’t cross-browser.  From what I’m reading, the Google Gears project is an open source project, which may mean it gets adopted and ported to other browsers besides the current IE and Firefox.  It’s released under the “New BSD” license, whatever that is, so I’m not 100% certain on that part, but it sounds open source enough for most usage.

I suspect I missing a number of other players in the emerging web technology space (Laszlo and Flex come to mind as well), but I’ve been struck by some of the cool developments over the past several months, and the Google Gears announcement today pushed me into posting this just now.

What cool web technologies have you come across recently?

PHP memcache presentation

May 3rd, 2007

Hrm… My previous post on this seems to have been eaten.  :(

David Rasch put together a great presentation on using memcache with PHP for our local PHP user group last night.  In the interest of time and expediency, we didn’t touch on everything the PHP memcache extension provides (namely distributed caching between multiple memcached servers).  We did see some startingly good improvements with the benchmarks he showed.  Yes, the example was slightly contrived, in that we could have seen much of that improvement with proper indexing, but by and large that was understood by everyone.  I’ve linked to both of the sites above because David should post his S5 presentation to at least one of those sites in the next day or so.

Generating random user data part 2

February 15th, 2007

Based on some feedback, I’ve enhanced the random user data tool.  As I wrote earlier, the tool generates data suitable for using as test data in your applications which may need basic user accounts.  The accounts can include things like first name, last name, address, city, state, zip code, phone numbers, social security numbers, and some new fields from the first round of feedback.

It now has an extra few fields for generating birthday, hiredate and email data.  And the tool will now also generate the SQL for you.  The SQL contains both the CREATE TABLE statement, as well as all the insert statements for your random test data.  I only tested it in MySQL (it worked) but the data is pretty straightforward, and I don’t anticipate any problems using it (or doing a quick edit on the CREATE TABLE syntax if it’s wrong).

If you find an issue with the tool, please let me know.

Hosted SOLR or Lucene service

January 28th, 2007

Just putting the question out to the blogosphere (love that word!) – is there any interest in a hosted Lucene or SOLR search service?  It may be something that is a non-starter, given that google and atomz have wrapped up a ‘hosted web search’ market segment already, but perhaps not.  Most people need to search through their web data, true, but google/atomz/etc search the content after its published.  Would there be much/any benefit in being able to index/search data before it’s published to the web (perhaps with extra meta data not necessarily easily publishable)?  Or perhaps searching data that is used for other, non-web-publishing activities?  Just throwing it out there – drop me a line if you’re interested in taking this further.

Sphinx full text search

January 11th, 2007

During the mysql meeting last night, Tobias mentioned the sphinx search engine project.  Apparently some ex-mysql guys split off and formed a consulting company, and are also working on this project.  I don’t know the nitty-gritty details, but I do know this seems pretty fast.  Tobias said it smokes Lucene for speed, and my initial test seemed to agree.  I just did one test that indexed 600,000 records – each record had probably 2k of text max, so it’s not huge – in 37 seconds.  This is running under vmware on a machine with other vmware servers running on it – I don’t have the specs offhand, but it’s not a top of the line machine.  Queries are all coming back in under .010 seconds, matching 2000+ results.  I will run some more tests to see how it scales and post more info here if there’s anything more notable.  Definitely looks like a project to watch.

Database idea

December 7th, 2006

Not sure if I wrote about this before – can’t find a reference here, so I’ll assume I didn’t.  :)

Something that has been a request on a lot of projects I’ve worked on is keeping a changelog of all data changes to a table.  I’ve seen this handled various ways, usually using triggers to look at every INSERT/UPDATE/DELETE statement and record the before/after (or sometimes just the ‘before’) state of each row of data modified.

After wrestling with this idea again about a month ago a light bulb went on above my head (literally, as my wife turned the light on because I was sitting in near darkness!).  Why isn’t this oft-requested/required functionality part of a database engine itself?  Yes, I’m probably not the first to think of this, but it was the first time I thought of it, so cut me some slack – I’m getting old!  If every db table had a corresponding ‘history’ table which could be queried automatically, and it was just a feature of the db, it would make many developer lives easier, and provide automatic auditing for just about any app.

The biggest issue I see here is that most auditing scenarios want the name of the person who did the changes logged as well.  In most traditional apps, the “db user” connecting and issues the queries is not the same as the “end user” using the app.  A standardized way of passing a ‘user ID’ number  to the engine (environment variable per db connection, or something similar) would be a passable way of doing this, I’d think.  Yes, it’s a slight hack, but far less work than writing and maintaining dozens of triggers on tables and keeping the triggers in sync with the table definitions.

Is this something that any databases offer ‘out of the box’?  I haven’t heard of any that do this.  MSSQL?  Oracle?  DB2?