Now you'll know why I hate making assumptions and would rather be
told straight out about such things! I thought from what I'd read, that the
Metadata was a series of attributes used by the Schema! But once I read
your explanation below and went back and took a look at my already created
tables, and extracted the metadata, I finally understood where my thinking
had gone wrong! Thanx for straightening me out on this!
My reasons for wanting to have the application itself create a whole
new database on order, such as when a new user logs on to the system and
runs the app for the first time, is that in Delphi this meant everything was
hard coded in my exe and there was no worry about corrupted or missing
files. But since I can now no longer do things that way, I still think that
including the metadata and running it upon a create new database command
will be the best overall method to use. Let me ask you though. What
exactly is necessary to run the metadata and create the database. And is
there a command-line order I can "put" together on the fly so as to instruct
that the new database for the currently logged on user is named and created
within the directory wanted? Then of course, anytime in the future that
same user logs on, HIS or HER own database will be opened up by the
application when run. Down below you say that one of the alternatives for
running the script is via the command-line, but what is needed to run it? I
would rather not have to include a copy of a management tool with the app,
and in many cases these will be self-installed and my only contact will be
via phone or e-mail. So if a third-party exe is required to run the script
in the first place, and it's not something that either is or can be included
in my exe, it would appear that the other method, where a full copy of the
database is included as a compressed file along with the setup and then
accessed and copied over to the named directory when ever a new database is
required.
You have to understand the kind of people I'm dealing with back
here. I'll soon be writing an upgrade to a training application I created
pro-bono for the County Dev Assoc., which provides trained illiterate and
mentally challenged temp employees. They now want me to expand on the
training application which did and will continue to include a separate
database file with multiple tables in them for each trainee to use as they
learn how to input and retrieve data. Most of these people cannot even
write their own names, but put them in front of a keyboard and show them
what, when, and how to do something, and they take to it like a duck takes
to water! It's really something! But the most important thing about it is
not that they CAN handle the work, but that doing so gives them a feeling of
belonging and value that they would never otherwise have! Giving a little
of that to some of these people is more than worth the time I spend putting
such an application together!
from Robert Meek dba Tangentals Design CCopyright 2006
Proud to be a moderator of "The Delphi Lists" at elists.org
"Life is the product of haphazard collisions that occur between known
elements within a envelope of chemical energy, which as a catalyst provides
an environment not only needed for its creation, but for it's sustenance,
growth, replication, and evolution as well. "
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of Cosmin Prund
Sent: Saturday, February 03, 2007 5:47 AM
To: Moderated discussion list about database programming with Borland's
Delphi
Subject: Re: To Helen or any Firebird pros
In the SQL world everything is done from a "script". All data is
retrieved using "queries", and there is an instruction for everything,
from creating a database to creating a table, an trigger, or dropping
(deleting) a table/database/trigger. If you'd have done everything "by
the book" (IB users guide), you would have known this because all
database and all tables are created using "isql" (command line SQL
interface to the database). Since you haven't used "isql" (and note: I
don't recommend it, there are simpler ways to do things those days) you
missed that part. The language used to manipulate database structures
(tables and all) is called "Data Definition Language" - DDL for short.
The structure of the database is called (or I'm calling it?) the
"metadata", so DDL is SQL language for manipulating the "metadata".
Fire up your IbExpert, double-click on a table. Did you notice the DDL
tab before? IbExpert is kind enough to show us the DDL for re-creating
the given table, so we can work with IbExpert's GUI to easily create and
modify tables, but if required, one can copy-paste the DDL somewhere and
use it "as is".
Next: Look in the "Tools" menu, there's a "Extract Metadata" command.
Click on it, then check the "Extract All" option and hit the green
"play" button to start the extract process. IbExpert will extract an
script that may be used to re-create the whole database. This includes
everything in the metadata (database itself, tables, triggers, stored
procs - you can see the list in IbExpert, I don't need to copy it here).
The extracted script may be run on a customer's computer to re-create
the database, using an number of alternative methods: using iSql
(command line), using IbExpert, using IbConsole. The component usites
I've used with Delphi so far all included a way to directly run such an
script. I never had the need to do this with .Net so I don't know how
it's done, but chances are there's a way to do it. Just look for
something that talks about scripts (RunScript, ExecuteScript, etc).
Please note the DDL extract option is not specific to IbExpert, you'll
find a similar command in any application designed to work with any SQL
database. As a matter of fact, there's a *very* good chance the command
line SQL interpreter for any real-life SQL server is called "isql", just
like in Firebird's / Interbase's case. For simple databases, the DDL
script extracted using IbExpert might work on other databases "as-is"
(because this data-manipulation language is standardized). If it doesn't
work, chances are it can be easily changed so it will work.
Back to your question: You've got a number of options for allowing the
end-user to create a new database.
(a) Keep a copy of the DDL-script to re-create the database with your
application. You'll need to ask the user for the database name (not
folder, Firebird databases are stored in a single file), change a few
lines in the script (the line that says the name of the database, the
line that gives the user name/password), run the script and that's it,
you've got a brand new database. This is options is probably the most
portable, as it allows you to work with different versions of
Interbase/Firebird and it allows easy changing of the Database itself
(maybe you want to move to MsSQL - I don't).
(b) Keep a "backed-up" copy of your database with the application. When
the user wants to create a new database, you simply restore the database
at the requested location and that's it, you're done. This is a bit more
flexible then (a) as it allows you to include both "metadata" and "data"
in the script. Well... method (a) also allows you to put data in the
script but the script will instantly become huge (as the data will be
included as "INSERT" statements).
(c) Keep a compressed copy of the full database with the application.
When the user wants a new database, you uncompress a single file. This
is the least flexible solution as it locks you into a single Firebird
version, but hey, it's soooo easy! And if most of your users will be
using the "Embeded" version of Firebird, they're locked into a single
version of Firebird anyway. This is the method I'm using!
As you get more used to this, you might start looking into other things,
like on-the-fly table creation. Just look at the DDL for a single table
(as you can see it in IbExpert) and remember you can copy-paste that
script into Delphi and run it: (FbCommand.ExecuteNonQuery). You can also
create different tables for different needs, simply by building up the
script and then running it.
--
Cosmin Prund
Robert Meek wrote:
> I had been using Nexus for the last three years, doing all direct
> table access, no SQL, for commercial or pro-bono work, and had started
> trying to learn SQL with it after picking up a few recommended books on
the
> subject. Unfortunately there were simply too many bugs and other
> difficulties with the system that I was not able to work out. And when
> you're trying to learn something new knowing whether a problem you are
> encountering is caused by your own errors or that of the system itself is
> important to know!
> I've already created a few test tables using IBExperts and have
> written my code to setup, create/open, close a firebird database in a
> net-based application, but so far I haven't used any SQL to manipulate it.
> Let me ask a question here that may sound a little dumb. But as I
> never had to worry about such things when using direct table access
methods
> this is quite new to me and I haven't found a definitive answer anywhere.
> Am I correct in assuming that if I want my application to allow a
> user to create a new database directory and then a particular set of
tables
> I can create a schema of my design database and use it as the model for
the
> new creation of such tables in the working application? Using table
> components I would simply store the definitions and then create each table
> if it didn't already exist. But with Firebird there are no table
components
> to speak of, so if I create my tables during design, then save out a
schema,
> is that basically the same thing or is there more to it?
> It's odd but unlike when learning Delphi and not having any
> programming experience before hand, basic questions like these were quite
> easy to find out about, but in database documentation and tutorials they
all
> seem to skip over this kind of stuff as if you're already supposed to know
> it! You end up wasting a lot of time on simple concepts that shouldn't be
a
> problem but are for the lack of one simple sentence telling you exactly
what
> a schema is and what it is used for!
>
_______________________________________________
Delphi-DB mailing list
Delphi-DB@elists.org
http://www.elists.org/mailman/listinfo/delphi-db
_______________________________________________
Delphi-DB mailing list
Delphi-DB@elists.org
http://www.elists.org/mailman/listinfo/delphi-db
