I'm looking at using ElevateDB instead of DBISAM for some projects. One of the
things I've really liked in DBISAM in the past is that I can literally just delete
the files for a table, and my code will recreate them on startup. Obviously this
isn't ideal for all purposes, but the tables for this application are used for
managing short term data.

Now, I understand that instead of using TTable.FieldDefs I'd use SQL to create the
tables, and I guess I can do an "IF NOT EXISTS("TableName" THEN CREATE TABLE" (I
hope!) but will the catalog cause trouble here? Is there a way to validate the
setup, and rebuild it if appropriate? (Sometimes it would be a disaster to lose
tables, but sometimes it isn't.)

Or am I just pushing it too hard here and should be a good boy and not delete the
tables ever?

(I do usually start my applications with a database verify to ensure they are
robust.)

/Matthew Jones/

Matthew


>I'm looking at using ElevateDB instead of DBISAM for some projects. One of the
>things I've really liked in DBISAM in the past is that I can literally just delete
>the files for a table, and my code will recreate them on startup. Obviously this
>isn't ideal for all purposes, but the tables for this application are used for
>managing short term data.

Are you sitting comfortably......then I'll begin.

The key is the catalog. If you supply a catalog with the application then ElevateDB will create the tables the first time someone tries to access them. Delete the three (or two if no BLOBs or CLOBs) files and they'll come back. If you don't supply a catalog then one will be created as you create your session and database and issue your various CREATE TABLE commands.

Switching your mindset to think in terms of the catalog and SQL is one of the most difficult things to do if you're a long time DBISAM user and never had dealings with such things.

Until Tim introduced the EMPTY TABLE command my way of a fast delete (ie rather than "while not table.eof do table.delete" or "delete from table" was to issue a DeleteFile and just zap the tables.

>Now, I understand that instead of using TTable.FieldDefs I'd use SQL to create the
>tables, and I guess I can do an "IF NOT EXISTS("TableName" THEN CREATE TABLE" (I
>hope!) but will the catalog cause trouble here? Is there a way to validate the
>setup, and rebuild it if appropriate? (Sometimes it would be a disaster to lose
>tables, but sometimes it isn't.)

Give up hope That's one of the beautiful DBISAM extensions that didn't make it through. There are ways of doing something similar. You can test the catalog to see if the table exists or wrap the sql inside a script in an exception block.

The same applies at column level. I don't know how others handle it when they constantly alter the table structure and need to test for the existence or not of a column. I don't have to, and I know Tim has built in some facilities in EDBManager, but my approach would probably be to supply a load of column definitions, compare against the catalog and build the sql on the fly.

>Or am I just pushing it too hard here and should be a good boy and not delete the
>tables ever?

Nah. Delete the little suckers - they'll be back!

>(I do usually start my applications with a database verify to ensure they are
>robust.)

I've read a number of people post they do that and I've often thought they must have small databases or patient users. I don't think its needed but I do build in repair to my apps. If they can log in they can sort it. I'm still trying to think of a good way of handling it if things are so bad they can't log in.

Roy Lambert

Roy

Thank you for that explanation. Sounds all very viable.

> >(I do usually start my applications with a database verify to
> ensure they are
> >robust.)
>
> I've read a number of people post they do that and I've often
> thought they must have small databases or patient users.

Some of the tables are indeed small. Some are completely massive, and I got a lot
of complaints about startup times... Thus the real code now creates a file on
startup called "unclean_shutdown.txt" and when the service shuts down it deletes it.
If the file is present on startup, then the full database check is done as someone
got naughty with process manager.

I like to do a verify simply because I'd rather know the thing is bad/good at
startup than an hour into the operation. The code runs 24/7 all the time (some logs
come back showing 6 months or more of operation), and the users are not
sophisticated.


/Matthew Jones/

Matthew

>Some of the tables are indeed small. Some are completely massive, and I got a lot
>of complaints about startup times... Thus the real code now creates a file on
>startup called "unclean_shutdown.txt" and when the service shuts down it deletes it.
>If the file is present on startup, then the full database check is done as someone
>got naughty with process manager.
>
>I like to do a verify simply because I'd rather know the thing is bad/good at
>startup than an hour into the operation. The code runs 24/7 all the time (some logs
>come back showing 6 months or more of operation), and the users are not
>sophisticated.

That makes a lot of sense. Most of my stuff is of the "start in the morning shut at night" type. I do have one or two things which are meant to just run. One of which is giving me grief at the moment - gets emails from my ISP and send them. It has an intermittent AV which neither MadExcept nor I can find.

Had you thought of just using ElevateDB's quick check rather than full check validation, or doing it in multiple threads?

Roy Lambert

> Had you thought of just using ElevateDB's quick check rather than
> full check validation, or doing it in multiple threads?

I've just ifdef'd it out for the moment, as I transition this code from DBISAM. Way
easier than I expected, though the SQL isn't working properly. Got to learn the
differences in my WHERE clauses. The checking is always in the initial thread that
checks everything before creating the world and running up all the workers. Only
sensible way if the tables need to be locked to fix. But in this application the
tables have to last for many years, so I'm not going to be doing anything much. Hmm,
makes me think I need a mode to restart the server to serve "maintenance" pages,
and leave the database for manual work. Will do that...

/Matthew Jones/