Fun with Databases
Database day!
Ok, today's the day that I finally start playing with my database. My previous database used Access, but I'm going to start with SQL Server this time. My goal for today is:
1) Create a table to store DebugPrint snapshots for certain objects.
2) Write a test row.
3) Write a test to read the test row.
Table Schema
Yes, I'm doing a little design up front. I'll try to keep it to a minimum. I use uppercase table names (singular), and lowercase field names.
Table DEBUG_SNAPSHOT
int snapshot_id
varchar(80) alias (e.g "StageManager")
text snapshot_text (e.g. "I am a stage manager, I have these abilities...")
Some of the steps require playing with my hosting company. These steps aren't very interesting, but are a bit time consuming, so bear with me. It's a good thing you aren't reading while I'm typing, otherwise, this would be very boring. I feel like I should type hash marks every once in a while so that you know I'm still processing.
File Refactoring
Actually, while I'm waiting, I can do a little file clean-up. My holding area for successful tests has gotten rather large (over 1500 lines), and I think it can be broken up a bit. I'll start by organizing the file internally (just moving functions around). One of the nice things about this change is that it's easily interruptable. Ah ha! Looks like my database is ready. Let's see what the managing interface is like.
So far, so good. I can create a constraint to make alias unique. Nuts, I have to find the SQL for it first. Ah, I wanted an index, not a constraint. Ok, done.
Break
A short break to play half a game of Syzygy. In the meantime, I've been timed out of my database session. But, I think I have enough information to proceed.
Stored procedure
I've created my first stored procedure, and entered a test row into my new table. Now to try to connect to the database and execute the stored procedure.
testDatabaseConnection
Ok, first issue is an invalid dsn. FTP to the rescue (: Using an FP browser, I'm able to make a little more sense out of the example, and proceed to the next step (getting an error for undefined ado constants. Excellent, a single meta reference in global.asa fixes that problem. Now I have a problem calling the stored procedure. Got it - appending parameters. Test passes.
Refactoring a bit
I'm going to extract the "go to the database and get a snapshot for this alias" bit of the test, and then re-run the test. I'm starting a naming convention for functions (e.g. this is DBGetSnapshot) as an experiment. Really, I should probably create a database object that encapsulates creating the connection, but I'll wait until I need it. Excellent - test still passes.
Benefits
Interestingly, in addition to helping to ease the maintenance pain of snapshot comparison tests (and thus, remove the barrier to updating the DebugPrint function when necessary), this exercise increases my comfort level with dealing with the database. It's somehow less stressful to handle the learning curve with this example than it would be if I was implementing something that I couldn't wait for.
Snapshot Generation
Ok, now to write a function that saves snapshots to the database. I have multiple options for how to approach this - write ad-hoc SQL and test it first, and then refactor to stored procedures, or write the stored procedure first and then write the tests to get it to work. Additionally, I have the choice of
(a) trying to insert, and if it fails, try to update
(b) trying to update, and if it fails, try to insert
(c) read first, then determine whether to update (if different) or insert.
Given that insert will only happen once (per alias), and updates will be the norm, and considering that updating with the same text doesn't have any ill side effects (although it is wasteful), option (b) is tempting. However, I dislike exception-driven logic, so I'll go with (c) - even at the expense of generating two db calls occassionally. Caching is the real solution there.
DBSetSnapshot
This breaks down into two other new functions = DBUpdateSnapshot and DBInsertSnapshot.
Duplication
The first time I copy/paste the connection string, I feel a little dirty. I'm going to be proactive and short-circuit the "three times, then refactor" rule, as I know I'll need the connstring in both of these new functions. Time to put it in the Application cache. Done, all tests pass.
DBUpdateSnapshot
Written, but it's not called yet, until I update the snapshot text. Fortunately, I had enough foresight to include a typo in my initial version (; so I can fix that now. testDatabaseConnection (not quite an accurate name anymore) will now compare the snapshot text, update it (as it's different), and then validate that the update took. Excellent! It worked. However, now that the database has been updated, the next time I run this test, it will take a different path (the text is not different any more). I think that tests that execute different functionality like this are a bad idea, but I'm not going to fix it just yet.
DBInsertSnapshot
Ok, now to do the insert version. Just a copy/paste/change SQL on the UpdateSnapshot function. To test it, I'll delete the existing row from the database. Again, I'm using the same test to test different functionality. Sweet, it works.
One thing to note is that I probably don't want to run the drop/insert test automatically every time. I'm concerned about using up by identity field needlessly. Is this a valid concern? Of course, this doesn't apply to the update case.
Stored procedures
Ok, now to refactor each function to use stored procedures. DBInsertSnapshot first. Hmm... I need to find a SQL reference, guessing by trial and error is a pain. Excellent, it worked.
DBUpdateSnapshot
Last one... Done. Tests pass.
Ok, today's the day that I finally start playing with my database. My previous database used Access, but I'm going to start with SQL Server this time. My goal for today is:
1) Create a table to store DebugPrint snapshots for certain objects.
2) Write a test row.
3) Write a test to read the test row.
Table Schema
Yes, I'm doing a little design up front. I'll try to keep it to a minimum. I use uppercase table names (singular), and lowercase field names.
Table DEBUG_SNAPSHOT
int snapshot_id
varchar(80) alias (e.g "StageManager")
text snapshot_text (e.g. "I am a stage manager, I have these abilities...")
Some of the steps require playing with my hosting company. These steps aren't very interesting, but are a bit time consuming, so bear with me. It's a good thing you aren't reading while I'm typing, otherwise, this would be very boring. I feel like I should type hash marks every once in a while so that you know I'm still processing.
File Refactoring
Actually, while I'm waiting, I can do a little file clean-up. My holding area for successful tests has gotten rather large (over 1500 lines), and I think it can be broken up a bit. I'll start by organizing the file internally (just moving functions around). One of the nice things about this change is that it's easily interruptable. Ah ha! Looks like my database is ready. Let's see what the managing interface is like.
So far, so good. I can create a constraint to make alias unique. Nuts, I have to find the SQL for it first. Ah, I wanted an index, not a constraint. Ok, done.
Break
A short break to play half a game of Syzygy. In the meantime, I've been timed out of my database session. But, I think I have enough information to proceed.
Stored procedure
I've created my first stored procedure, and entered a test row into my new table. Now to try to connect to the database and execute the stored procedure.
testDatabaseConnection
Ok, first issue is an invalid dsn. FTP to the rescue (: Using an FP browser, I'm able to make a little more sense out of the example, and proceed to the next step (getting an error for undefined ado constants. Excellent, a single meta reference in global.asa fixes that problem. Now I have a problem calling the stored procedure. Got it - appending parameters. Test passes.
Refactoring a bit
I'm going to extract the "go to the database and get a snapshot for this alias" bit of the test, and then re-run the test. I'm starting a naming convention for functions (e.g. this is DBGetSnapshot) as an experiment. Really, I should probably create a database object that encapsulates creating the connection, but I'll wait until I need it. Excellent - test still passes.
Benefits
Interestingly, in addition to helping to ease the maintenance pain of snapshot comparison tests (and thus, remove the barrier to updating the DebugPrint function when necessary), this exercise increases my comfort level with dealing with the database. It's somehow less stressful to handle the learning curve with this example than it would be if I was implementing something that I couldn't wait for.
Snapshot Generation
Ok, now to write a function that saves snapshots to the database. I have multiple options for how to approach this - write ad-hoc SQL and test it first, and then refactor to stored procedures, or write the stored procedure first and then write the tests to get it to work. Additionally, I have the choice of
(a) trying to insert, and if it fails, try to update
(b) trying to update, and if it fails, try to insert
(c) read first, then determine whether to update (if different) or insert.
Given that insert will only happen once (per alias), and updates will be the norm, and considering that updating with the same text doesn't have any ill side effects (although it is wasteful), option (b) is tempting. However, I dislike exception-driven logic, so I'll go with (c) - even at the expense of generating two db calls occassionally. Caching is the real solution there.
DBSetSnapshot
This breaks down into two other new functions = DBUpdateSnapshot and DBInsertSnapshot.
Duplication
The first time I copy/paste the connection string, I feel a little dirty. I'm going to be proactive and short-circuit the "three times, then refactor" rule, as I know I'll need the connstring in both of these new functions. Time to put it in the Application cache. Done, all tests pass.
DBUpdateSnapshot
Written, but it's not called yet, until I update the snapshot text. Fortunately, I had enough foresight to include a typo in my initial version (; so I can fix that now. testDatabaseConnection (not quite an accurate name anymore) will now compare the snapshot text, update it (as it's different), and then validate that the update took. Excellent! It worked. However, now that the database has been updated, the next time I run this test, it will take a different path (the text is not different any more). I think that tests that execute different functionality like this are a bad idea, but I'm not going to fix it just yet.
DBInsertSnapshot
Ok, now to do the insert version. Just a copy/paste/change SQL on the UpdateSnapshot function. To test it, I'll delete the existing row from the database. Again, I'm using the same test to test different functionality. Sweet, it works.
One thing to note is that I probably don't want to run the drop/insert test automatically every time. I'm concerned about using up by identity field needlessly. Is this a valid concern? Of course, this doesn't apply to the update case.
Stored procedures
Ok, now to refactor each function to use stored procedures. DBInsertSnapshot first. Hmm... I need to find a SQL reference, guessing by trial and error is a pain. Excellent, it worked.
DBUpdateSnapshot
Last one... Done. Tests pass.
posted by FlyingSheep @ 10:34 AM
0 comments
