Fog Creek Software
Discussion Board

Knowledge Base
Terry's Tips
Darren's Tips

Databases: who's done what?

How have others manipulated the database structure of a .cty file, and for what purpose?

Sunday, February 15, 2004

By manipulated do you mean access to the raw database on a read-only, or read-write, basis or adding new fields or tables into the database itself?

The database is stored in MS Access format, so any version of MS Access 2000 or later will allow you to open up the tables. There is some guidance for this on the fogcreek site. If you don't have MS Access 2 the application "copy boy" will allow you to make changes to the raw database. (You can get to the copy boy site thru fogcreeks). Making changes directly requires some forethought though. Article stories, for example, are held in a UTF-8 encoded BLOB field, of which the first 405 bytes or so are extraneous but needed data, so you wont be able to just edit away at them. (Of course, why would you want to anyways since thats what CD is for).

You can also add extra tables or fields into the database, but you will have to use another application to create and edit them.

I've used Delphi 7 and it's ADO library to access and edit the fields that normally belong on the Extra's tab. There is an inherant danger in using an application to access the raw data. The most obvious is that the developers may change the field formats from version to version of city desk and also if you make changes you have to be careful to correctly update the "last modified date" or your new page may not get picked up by the publish wizard.

If you want to perform some action prior to publishing your finished website then you may want to look at making a post-processor. In the publish settings box you can make a program of your own run just after the file is saved locally or before the final FTP of the site. This program only needs to be able to parse HTML files and has the advantage that it can be "scheduled" to run before each and every publish.

Hope this helps,

Ashley Dennison
Monday, February 16, 2004

Perhaps "extend" is a better word to use rather than "manipulate". I'm curious whether anyone out there has added tables/functionality to the CD file, either to tie into other apps or do things that CD doesn't do.

Some thoughts:

Adding permissioning by creating separate tables or .cty files where the tables are linked back to the main .cty file -- in short, providing a subset of the main .cty file based on access rights.

Creating web forms that write to the .cty file. Good for straigtforward intranet updates or to provide greater developer control and user simplicity for general site updates. Perhaps even as a way to provide flow control.

Tuesday, February 17, 2004

One one project, I have created a simple Access invoice-creation tool that automatically invoices third parties for the content created in CityDesk.

As mentioned elsewhere in this forum, you should always ensure that the end-user of such tools cannot screw with the CityDesk tables in a way that breaks CityDesk editing.

David Walker
Wednesday, February 18, 2004

Creating "web forms" to update content in the database is unlikely to work as the main article text is held in a UTF-8 edit control format with 400 odd bytes at the start of binary information.

If you were thinking of allowing people in your organisation the ability to update their content you should purchase the client application in packs of 5.

I should imagine that the publication of any method or tool which allowed the direct creation of pages and a way of subsequently publishing them without requiring CityDesk to be installed in some shape or form, would in fact be a breech of copyright.

Ashley Dennison
Monday, February 23, 2004

I doubt it. In fact, I suspect FogCreek would welcome such if it made CityDesk data files into some sort of standard.

Wednesday, February 25, 2004

I have a MS Access function that reads an Access table and populates a CityDesk variable with the associated HTML code (variables are Access memo fields that can be up to 64k).  Then all you have to do is put the reference to the variable in the body of the article.  The function's parameters are CityDesk filename and Access table name.  The HTML is built with the Access field names in the first-row using <th></th> tags.  The function automatically links to the CityDesk tblVariables table - the CityDesk file and Access database must be in the same directory. 

Terry Powell
Friday, February 27, 2004

*  Recent Topics

*  Fog Creek Home