At 12:32 AM 3/02/2007, you wrote: > > 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?
Yes. But it's not like you're used to, with data stores that contain tables and other objects as physical files. Delphi applications are called clients. Clients communicate with the big, abstract puzzling thing called "a database" using SQL, which is passed across the client interface (A.K.A. API) in predefined C structures which someone has already kindly hidden from you by objectivising them and giving you useful handy things like Properties and Methods. SQL is divided into distinct, non-overlapping sets. Static SQL is something that is restricted to programming (usually in C) where chunks of SQL are embedded directly in the program source code and are pre-compiled using a program called Gpre. In Delphi, we never go there. We use a different set called Dynamic SQL (DSQL). DSQL comes in two flavours: DML and DDL. DML is the set of statements that operate on the data in database objects (SELECT, INSERT, UPDATE, DELETE, EXECUTE) while DDL is the set that operates on objects (CREATE, ALTER, RECREATE, plus a few more). There is no way whatsoever to create file-based objects with Firebird or InterBase (or other big-end DBMSs). It is all done with DDL statements. IBExpert and other such tools are nothing but an ordinary Delphi application with an "idiot" interface for dynamic SQL. "Queries" and "Table views" in these tools are SELECT statements under the hood. Creating databases, under the hood, constructs a DDL statement CREATE DATABASE statement, which, at the server, creates a physical file, opens it, attaches the client to it and creates a myriad of on-disk objects for its own use. Then it waits for more requests. So, next, you plug in some values to your tool's interface, to create a table. Under the hood the application constructs a CREATE TABLE (.....) statement and sends of the request. And so on, for constraints, indexes, generators..... all the stuff you have already worked out you need from your analysis and design. To do that from an application, you need a statement object. What that might be depends on what components you're using. Even a VCL TQuery will do; although the BDE is not really designed to perform DDL efficiently. Other components, like IBO and FIBPlus have more efficient statement classes available for DDL and other statements that don't use datasets. The crucial property is the one called SQL. That's the one where your DDL statement goes, e.g. your Create Table statement. The Execute method creates the object in your transaction space; a call to the Commit procedure of the transaction makes it permanent in the database. >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? Firebird, etc., don't have any physical objects that are created when you create tables. They are just definitions that live in a group of system tables. Later, when you add data to those tables, the db engine requests (or re-allocates) some blocks of disk known as pages. Streams of data are just laid into these pages sequentially, using the rules and regulations defined for that table. The whole story of the implementation of the language sets revolves around that mechanism. > 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! Certainly the Delphi documentation about data access has been restricted to the BDE, which is the Paradox engine. The BDE is great for Paradox. Fantastic, actually. Not bad for Access, Foxpro and other flat-file data stores. But the BDE doesn't do anything natively with Firebird/IB. It's actually a labyrinth of Paradox tables created on disk from which it feeds data back and forth. I don't believe it was ever meant to be a serious way to do database programming with relational databases. Delphi is like that: you get some bare bones and you build on what you need. It's the same story with DBX - bare bones, roll your own. It's hard to argue that Borland should have documented how to do the serious stuff, when it doesn't actually provide the components itself. So far IB (and subsequently Firebird) have been well supported with third-party Delphi components. (There are also these days plenty of equivalent components out there for the likes of Oracle, DB2, and so on but Fb/IB are still by far the best served.) You can link to trial versions of many of them from the IBPhoenix website. More info about the sites can be found in the Firebird release notes and QS Guides. From about Delphi 7 onward, you could have the IBX components, either on your Miscellaneous disk or by downloading from CodeCentral. The components themselves are not that great, but they do work with Firebird, up to a point. What's more, Borland *has* documented them, quite thoroughly, actually. The entire Developer's Guide (DevGuide.pdf) from the IB 7 docset is dedicated to IBX and is fairly complete. > 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! Forget it. "Schema" is just RDBMS-speak for "database" or "database definition". CREATE SCHEMA is a synonym for CREATE DATABASE in many DDL implementations, including Firebird and IB. A lot of the European-sourced tools use SCHEMA in preference to DATABASE because it's the same word in many languages. You can "extract the schema" in most of the tools, which generates a text "script" of DDL statements, in the right order, that you can run through ISQL or a script component in your Delphi component set, to recreate a database and its objects. The Firebird Book, that rubbishy doorstopper that you are not going to waste your money on, has a whole Chapter (14) about scripts. Helen _______________________________________________ Delphi-DB mailing list Delphi-DB@elists.org http://www.elists.org/mailman/listinfo/delphi-db
