Fog Creek Software
Discussion Board




Where to store the DB schema?

I'm writing an application which, rather like CityDesk, is in VB6 with an Access MDB as the data file.  One of the things this app needs to be able to do is create new MDBs with the same structure on the fly.  I'd rather not simply copy a template MDB and be done with it (because of the possibility that the template could be deleted or corrupted), but that raises the question of where to store the information the app will need to re-create it.

I could save the schema in a text or XML file in the program directory, but then it's subject to the same deletion/corruption issues as a template.  Putting it all in code is a maintainability nightmare.  I suppose I could combine the two: store schema in an XML file, then turn that file into a string variable in code before compiling... but that's also a little bit messy.

Any suggestions?

Sam Livingston-Gray
Wednesday, August 06, 2003

Why don't you use your source control of choice?

19th floor
Wednesday, August 06, 2003

My suggestion is to store the template externally, but make sure it includes a message digest to reduce the likelihood of tampering.

You can make it more secure with some sort of digital signature, but don't bother unless there's a security implication.

Reginald Braithwaite-Lee
Wednesday, August 06, 2003

19th floor:  Could you be a little more vague, please?

Sam Livingston-Gray
Wednesday, August 06, 2003

I wrote an application to generate the XML schema for our master DB and I save it with the code in Visual Source Safe. I use this schema to generate/update the target DB schema on developer’s machines during development or our client’s machines during deployment. The schema is in synch with the code, and you can always roll back the changes if something goes wrong.

I do not encode it since I don’t believe the clients will tamper with it.

I am not sure how much this answers your question …

19th floor
Wednesday, August 06, 2003

Oh, I see.  I guess I could've clarified:  I'm trying to figure out where the schema will sit on the client's PC (this will be shrinkwrap, desktop-only software) so when they do File -> New from the menu, it can create a new data file for them.

Sam Livingston-Gray
Wednesday, August 06, 2003

Sam, that's exactly what I envisioned from your question. It's a pattern I've seen a few times, ranging from configuration files to templates.

Most users will not deliberately tamper with the files, but in my experience the very few who do (by accident or on purpose) create enough of a headache to make you wish you had tamper-resistance in place.

Reginald Braithwaite-Lee
Wednesday, August 06, 2003

Now that I think about it, I suppose I'm going to have to build a DB integrity check into the software anyway... so I could just run it on the template before I copy it.  That would at least let them know they screwed up... ;>  Thanks!

Sam Livingston-Gray
Wednesday, August 06, 2003

I think you could add the XML file or even a empty (with schema built) mdb as a custom resource file in your vb project.

Then you could extract it and do with it as you wish.

see: http://support.microsoft.com/support/kb/articles/q194/4/09.asp

DJ
Wednesday, August 06, 2003

Keep it in an encoded (note: not encrypted) text file. Just xor it with the lyrics of your favorite song or something. MD5 the encoded file and put that hash value in the application - check the file when you open it.

You could even add functionality that if the MD5 doesn't match, it offers to fetch a fresh copy from your server on the 'net (or restore from disk).

Finally, no idea how this would impact your app, but the other possibility would be to make the schema a user-editable template and put error checking in your app to handle it.

Philo

Philo
Wednesday, August 06, 2003

We have many configuration files in our shrink-wrapped product, besides the DB schema. The clients don’t tamper with them. If they do, what would stop them from tampering with the exe or dlls?

We have a dedicated folder where we keep all these files.

19th floor
Wednesday, August 06, 2003

In my experience, people who are fearless enough to screw around with installed files are also fearless enough to delete installation folders and reinstall if trouble arises.

We've done this in the past and have had no such problems. If you think it's too tempting to leave an MDB file in the program files folder, then change the extension to .dat or something.

This is not worth worrying so much about, IMO.

Big B
Wednesday, August 06, 2003

If you were writing in C++ I'd suggest sticking the template MDB file into a resource, then it'd be bundled inside your EXE and you don't have to worry about finding it or getting it lost.

In VB6, I have no idea if you have this capability or not. Will VB6 do resources?

Chris Tavares
Wednesday, August 06, 2003

Chris,

see my previous post...

DJ
Wednesday, August 06, 2003


If you're using VB, the safest place to store a template database or schema is as a resource inside the application's EXE file.  You can do this in VB4 through VB6 by creating the resource file from a script using the RC compiler in the Tools subdirectory of your VB installation.

When you need to create a database file, extract the resource into an array of bytes, then write it into a new file using VB's built in binary file access methods:
- Load the resource using VB's LoadResource statement
- Open the target (new) file name for binary access
- Call Put on the open file with the byte array returned by LoadResource.  This writes the resource data into the target file
- Close the file
- Do whatever you wanted to do with the new database file.

VB.NET supports the same concepts, but the resource and file access methods are very different.  I'll leave the .NET procedure as an excercise for the reader :-)

This approach is safer than leaving the files unprotected in the file system.  End users can't see the resources within the EXE, so they can't inadvertently tamper with them.

Craig
Wednesday, August 06, 2003

==>Putting it all in code is a maintainability nightmare.

<blink> !! <blink>

It's the exact opposite dude!

Making changes to the schema through the Access front-end is the "maintainability nightmare". You don't know who changed what, when they changed it, and why. Putting into code allows you to:

(a) Control what happens -- you can put all of your code to create tables/fields/indexes/relations etc into a module that you grant *one person* the appropriate rights to modify. No one else can modify this. That way there's a single point of control. If all of your developers are free to make schema changes in a "willy-nilly" fashion, you're screwed on maintainablility.

(b) Keep a history. With your favorite source control system, you can maintain a complete revision history of what has happened to the schema over time, and can instantaneously re-create *any* historical version of the schema.

(c) Allows you to *document* the schema. Access doesn't give you much in it's front-end to document the schema, so you've got to keep your docs somewhere else. If you maintain your schema in code, then you've got the documentation (comments and whatnot) right with the schema.

We use SQL Server primarily, but you can do the same thing in Access. Instead of T-SQL scripts that you're maintaining, you can use VBA code against the DAO model to create/maintain your schema.

Here's what we do: Create the initial schema (with the front end -- it's easier to point and click), then script out the schema, peg it as the 1.0 version, and place it under source control. All other changes to the schema are then done with a script. We end up having a series of scripts something like this:

CreateSchema.1.0.sql
AlterSchema.1.0.to.1.1.sql
AlterSchema.1.1.to.1.2.sql
AlterSchema.1.2.to1.3.sql
AlterSchema.1.3.to.2.0.sql
AlterSchema.2.0.to.3.0.sql

and have complete documentation, control, and revision history. It's actually quite maintainable. We can detect the current schema version through a series of tables we maintain, and then iterate through the scripts to bring a current schema of, say, 1.2 to 3.0 rather easily, and in an automated fashion. We also include any DML code that is needed in the Alter.Schema.sql scripts, so if any default values need populated, or tables need loaded, it's done automagically in the script. It works *very* well.

Again, you won't have .sql (T-SQL scripts) but you could very easily do this through VBA code in Access with the DAO objects (Tabledefs, Fields, Relations, Indexes, etc) and have VBA code modify the stuff. Granted, the VBA/DAO code isn't as easy to work with as a T-SQL script, but it's still pretty easy to do.

I vehemently disagree that code is the "maintainablity nightmare" you claim it to be, and tend toward the complete oposite. You're currently living the "maintainability nightmare" and if you move to using code then you'd be in "maintainability heaven".

Just my opinion.

Sgt. Sausage
Wednesday, August 06, 2003

I like the resource file idea... thanks!

Sam Livingston-Gray
Wednesday, August 06, 2003


"... using VB's LoadResource statement..."

Of course, my brain's in C++/API mode today. The appropriate statement in VB is LoadResData

Craig
Wednesday, August 06, 2003

Sausage-  Excellent points.  They're somewhat mitigated by the fact that I'm the sole developer, but still...

I'm not interested in writing that much DAO, but I *could* create a separate schema MDB that did essentially what you suggest.  Then, for completeness, I'll store it as a resource file.  And to make Philo happy, I suppose I'll XOR it somewhere along the way!

Okay, just kidding about those last two.  But using a schema DB would double as self-documentation, which I like.

Sam Livingston-Gray
Wednesday, August 06, 2003

If you're not going to put it in a resource within the EXE, then just give the template .mdb some scary looking extension and let it sit with the other supporting files for your app.

Why would a user be any more likely to delete a file with an .xyz extension than a file with an extension of .abc?

Norrick
Wednesday, August 06, 2003

I agree with Sgt. Sausage.  Are you planning to release more than one version of this app?  If so, how do you plan to upgrade existing databases when you release new versions?  Unless Jet has some sort of built-in differencing update ability that I don't know about, it seems that you'd need to go to code at this point. 

SomeBody
Wednesday, August 06, 2003

If you use the MDB file as a document for your application the way CityDesk does, why don't you turn a negative into a positive by allowing users to specify a templates directory similar to to the way VB and some other document-centric application do?

If you allow them to save a given file as a template then you have a powerful feature.

It does depend on the type of application, but for document handling apps I think it is useful since you can provide pre-made templates and let the user build upon them.

Wayne
Wednesday, August 06, 2003

It's not particularly document-centric.  It's a database for tracking client evaluations for social service agencies.  So if they create more than a couple of new files (not counting the anonymized export) over the course of a year or two, something is wrong.  (=  I'm just trying to make it as easy to use as possible, because we've had too many support issues with the current iteration as an Access 2000 app.

I'll do some sort of a hybrid solution -- store the base schema plus diffs in a separate MDB (probably renamed with workgroup security on it), then write some reusable DAO code to wrangle the format into shape.  That way I don't have to write as much DAO, but I still get the flexibility and self-documenting effects.

Sam Livingston-Gray
Thursday, August 07, 2003

*  Recent Topics

*  Fog Creek Home