I have a firebird database(this database ISN'T the destination
database) wich contains a table identical for each source table, and a view
identical for each destination table.
I need to develop an application that reads the source database(DBF) tables
and copy their data into the tables of that database, and after this, read
the views and copy their data into the destination tables.
The views I've posted on my previous message reads their data from the
source tables, not the destination tables.
The views encapsulates any difference between the source and destination
structures, so my application doesn't needs to process data in order to deal
with those differences.
Since the queries need to be stored on the database, I'm using views.


16 Apr 2007 05:16:08 -0700, Helen Borrie <helebor@...>:

>
> At 09:48 PM 16/04/2007, you wrote:
> > >
> > > Provide examples of the structures of perhaps two related source
> > > tables, along with their corresponding tables in the destination
> > > database
> >
> >
> >Ok...
> >In both source and destination databases, I have a table called
> >CIDADES('cidades' means cities in portuguese) with the following fields:
> >Cidade(Integer, primary key), CidNome(String), CidUf(String),
> >CidOutPais(Char), CidPais(String), CidKm(Integer), CidEdi(String),
> >CodPais(Integer)
> >The field CosPais is a foreign key to a table called PAIS(Country in
> >portuguese), with these fields:
> >CodPais(Integer, primary key), NomePais(String), LExterior(Char).
> >
> >In the dest database, those tables have the following ddl:
> >CREATE TABLE PAIS (
> > CODPAIS INTEGER NOT NULL,
> > NOMPAIS VARCHAR(30) NOT NULL,
> > LEXTERIOR CHAR(1) NOT NULL,
> > PRIMARY KEY (CODPAIS)
> >);
> >
> >CREATE TABLE CIDADE (
> > CIDADE INTEGER NOT NULL,
> > CIDNOME VARCHAR(30) NOT NULL,
> > CIDUF CHAR(2) NOT NULL,
> > CIDKM INTEGER,
> > CIDEDI VARCHAR(18)
> > CIDOUTPAIS CHAR(1) NOT NULL,
> > CIDPAIS VARCHAR(30) NOT NULL,
> > CODPAIS INTEGER NOT NULL,
> > PRIMARY KEY(CIDADE),
> > FOREIGN KEY(CODPAIS) REFERENCES PAIS(CODPAIS) ON UPDATE CASCADE
> >);
> >
> >(Note that in many cases, both source and dest tables are identical)
> >
> >To translate the data from the source structure to the dest structure I
> have
> >a view for each dest table. When dest table and their correspondent on
> the
> >source database are identical, that view is auto-generated by a software
> >i've wrote. So, for the tables CIDADE and PAIS, the views are:
> >CREATE VIEW VW_PAIS(
> > CODPAIS,
> > NOMPAIS,
> > LEXTERIOR
> >)
> >AS
> > SELECT
> > CODPAIS,
> > NOMPAIS,
> > LEXTERIOR
> > FROM
> > PAIS
> >;
> >
> >CREATE VIEW_VW_CIDADE(
> > CIDADE,
> > CIDNOME,
> > CIDUF,
> > CIDKM,
> > CIDEDI,
> > CIDOUTPAIS,
> > CIDPAIS,
> > CODPAIS
> >)
> >AS
> > SELECT
> > CID.CIDADE,
> > CID.CIDNOME,
> > CID.CIDUF,
> > CID.CIDKM,
> > CID.CIDEDI,
> > CID.CIDOUTPAIS,
> > CID.CIDPAIS,
> > PAIS.CODPAIS
> > FROM
> > CIDADE CID
> > LEFT JOIN VW_PAIS PAIS ON CID.CODPAIS=PAIS.CODPAIS
> >;
> >
> >(The tables referenced in the views are copies of the source tables. The
> >data returned by the views are copied into the destination tables)
>
> Sorry, but I'm still totally bemused. What is the point of the
> views? If you want to view the destination table data in the same
> field order as the source tables, why don't you just write a query?
>
> ./heLen
>
>
>

[Non-text portions of this message have been removed]