Thread: Opening two databases at the same time?
Is there a way, or is it possible to open two databases at the same time to retrieve information from one database and store it in the other?
On Tue, 13 Apr 1999, De Leersnijder Frederic wrote:
> Is there a way, or is it possible to open two databases at the same time
> to retrieve information from one database and store it in the other?
I did it one of my programs - I open a database that stores WWW log,
process some data, open another DB, put some data in, and close both DBs.
I am using Python as the programming language, and PyGres
(Postgres=>Python driver) uses libpq, so any libpq-based program will do.
Oleg.
----
Oleg Broytmann http://members.xoom.com/phd2/ phd2@earthling.net
Programmers don't die, they just GOSUB without RETURN.
On Tue, 13 Apr 1999, De Leersnijder Frederic wrote:
> Is there a way, or is it possible to open two databases at the same time
> to retrieve information from one database and store it in the other?
Yes, but you'd have to do it through an external language using libpq,
and then make multiple connections and copy the data between the two
databases.
Brett W. McCoy
http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
"To err is human, to forgive, beyond the scope of the Operating System"
On Tue, 13 Apr 1999, Oleg Broytmann wrote:
> On Tue, 13 Apr 1999, De Leersnijder Frederic wrote:
> > Is there a way, or is it possible to open two databases at the same time
> > to retrieve information from one database and store it in the other?
>
> I did it one of my programs - I open a database that stores WWW log,
> process some data, open another DB, put some data in, and close both DBs.
> I am using Python as the programming language, and PyGres
> (Postgres=>Python driver) uses libpq, so any libpq-based program will do.
>
> Oleg.
Oleg - I have done it in perl (using DBI:DBD) without much effort.
If you don't localize variables (using my or local) it is particularly
painless.
Frederic - if you want I'd be pleased to send some syntax...tot straks!
Tom
----
North Richmond Community Mental Health Center
Thomas Good Information Systems Coordinator
E-Mail: tomg@ { admin | q8 } .nrnet.org
Phone: 718-354-5528
Fax: 718-354-5056
Empowered by PostgreSQL 6.3.2
Is it possible in postgres to retrieve objects with differing fields, like in an object database? Like if I have CREATE TABLE aaa ( a text ); CREATE TABLE bbb ( b text ) inherits (aaa); CREATE TABLE ccc ( c text ) inherits (aaa); SELECT * from aaa*; Is it possible to get the fields a and c returned on objects of type bbb and ccc? This is what an object database would do automatically. I ask this question both in terms of the C interface as well as the psql terminal program.
Chris Bitmead wrote: >Is it possible in postgres to retrieve objects with differing fields, >like in an object database? Like if I have > >CREATE TABLE aaa ( a text ); > >CREATE TABLE bbb ( b text ) inherits (aaa); > >CREATE TABLE ccc ( c text ) inherits (aaa); > >SELECT * from aaa*; > >Is it possible to get the fields a and c returned on objects of type bbb >and ccc? This is what an object database would do automatically. No; your query on aaa* will return only field a, although it will include all rows from aaa, bbb and ccc. This is in line with standard OO treatment of inherited classes. Class aaa only has a single feature, a; it knows nothing about additional features of descendant classes. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 ======================================== "But seek ye first the kingdom of God, and his righteousness; and all these things shall be added unto you." Matthew 6:33
Oliver Elphick wrote: > This is in line with standard OO treatment of inherited classes. > Class aaa only has a single feature, a; it knows nothing about additional > features of descendant classes. But if you are say, using postgres to construct some C++ classes for types bbb and ccc, you would want to be able to get access to fields b and c so that you can construct your classes appropriately. This is how real object databases work. -- Chris Bitmead http://www.bigfoot.com/~chris.bitmead mailto:chris.bitmead@bigfoot.com
Chris Bitmead wrote: >Oliver Elphick wrote: > >> This is in line with standard OO treatment of inherited classes. >> Class aaa only has a single feature, a; it knows nothing about additional >> features of descendant classes. > >But if you are say, using postgres to construct some C++ classes for >types bbb and ccc, you would want to be able to get access to fields b >and c so that you can construct your classes appropriately. This is how >real object databases work. [bbb and ccc both inherit from aaa.] If you need features of bbb and ccc you must use those classes, not their ancestor. Class bbb knows about a and b and class ccc knows about a and c, but aaa doesn't know about b and c because they are not defined in aaa. `Vertebrate' is a descendant class of `animal'. `Vertebrate' has a feature `bones', but `animal' doesn't, because the majority of animals don't have bones at all. This is how inheritance works in the Eiffel language, at least. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 ======================================== "But seek ye first the kingdom of God, and his righteousness; and all these things shall be added unto you." Matthew 6:33
Oliver Elphick wrote: > If you need features of bbb and ccc you must use those classes, not their > ancestor. > > Class bbb knows about a and b and class ccc knows about a and c, but > aaa doesn't know about b and c because they are not defined in aaa. > > `Vertebrate' is a descendant class of `animal'. `Vertebrate' has a feature > `bones', but `animal' doesn't, because the majority of animals don't have > bones at all. > > This is how inheritance works in the Eiffel language, at least. I guess the point is if you had an Eiffel collection of animals, two Invertebrates and two vertibrates, and did a save to disk. When you loaded the collection back in from disk you wouldn't expect to get back 4 animals, whose status as vertibrates or invertibrates is no longer known. In a real object database, you could say "Get all the animals", and they would come back appropriately - some as vertibrates, some as invertibrates. Since they come back properly we can call methods on different types of animals and they will behave differently as appropriate. -- Chris Bitmead http://www.bigfoot.com/~chris.bitmead mailto:chris.bitmead@bigfoot.com
Chris Bitmead wrote: >I guess the point is if you had an Eiffel collection of animals, two >Invertebrates and two vertibrates, and did a save to disk. When you >loaded the collection back in from disk you wouldn't expect to get back >4 animals, whose status as vertibrates or invertibrates is no longer >known. > >In a real object database, you could say "Get all the animals", and they >would come back appropriately - some as vertibrates, some as >invertibrates. Since they come back properly we can call methods on >different types of animals and they will behave differently as >appropriate. That is so; however, if you use class `animal' only, you can see only the features that are appropriate to animals as a whole. To see features of more specialised classes, you have to use those classes. To take a real-life example in PostgreSQL: Table = person +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | id | char() not null | 10 | | name | text not null | var | | address | int4 | 4 | | salutation | text default 'Dear Sir' | var | | envelope | text | var | | email | text | var | | www | text | var | +----------------------------------+----------------------------------+-------+ Table = organisation (inherits from person) +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ [inherited fields] | contact | char() | 10 | | structure | char() | 1 | +----------------------------------+----------------------------------+-------+ Table = customer (inherits from organisation) +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ [inherited fields] | acs_code | char() | 8 | | acs_addr | int4 | 4 | | class | char() default '' | 1 | | type | char() | 2 | | area | char() | 2 | | country | char() default 'GB' | 2 | | vat_class | char() | 1 | | vat_number | char() | 12 | | discount | float8 | 8 | | commission | bool default 'f' | 1 | | status | char() default '' | 1 | | deliver_to | int4 | 4 | | factor_code | text | var | +----------------------------------+----------------------------------+-------+ Table = supplier (inherits from organisation) +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ [inherited fields] | terms | char() | 2 | | acs_code | char() | 5 | +----------------------------------+----------------------------------+-------+ In this case, customer and supplier both have a field `acs_code', but the lengths are different and the meaning is different (customer account code and supplier account code). I could define a field `status' for supplier which was a CHAR (like the one in customer) but meant something entirely different: customer status - null = normal, s = suspended, p = prepay only; supplier status - p = preferred, x = no longer used. I might also have another inheritance tree of individual inheriting from person, where status is - s = single, m = married, d = divorced. It would be wrong to mix these columns into one, because their meanings are different. If you were to `select status from person*' which column should be shown? You are right to say that all data should be retrievable, but wrong in wanting to attach descendant features to an ancestor class. In this example, if I want a list of contacts, I must `select contact from organisation*', because person does not have a contact field. I lose nothing thereby, because there is no information to be got from any other sub-trees about contact. If any other descendant of person does define contact, it does not have the same meaning as does contact in organisation and it should not, therefore, be reported anyway. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 ======================================== "But as many as received him, to them gave he power to become the sons of God, even to them that believe on his name." John 1:12
Oliver Elphick wrote:
> It would be wrong to mix these columns into one, because their meanings are
> different. If you were to `select status from person*' which column should
> be shown?
ERROR: Ambiguous column - Organization::status, Employee::status.
> You are right to say that all data should be retrievable, but wrong in
> wanting to attach descendant features to an ancestor class.
If the database were the whole world - maybe. But people use languages
to program databases. With a sort-of object database like PGSQL, an
object language would seem an obvious choice.
But there is no simple way to write a language integration interface so
that you could... say SELECT * from person* and instantiate appropriate
C++ classes complete with ancestor attributes for ancestor classes.
Maybe a new syntax would be appropriate, select ** from person*, the
"**" meaning to select all the attributes from the subclasses as well as
the specified classes.
This would then be used by a funky C++ language binding to instantiate
different C++ objects for different types of rows that come back.
> In this example, if I want a list of contacts, I must `select contact from
> organisation*', because person does not have a contact field. I lose
> nothing thereby, because there is no information to be got from any other
> sub-trees about contact.
You lose something, which is the ability to write a C++ language binding
that looks something like
Collection<Person> c = query("SELECT ** from person*);
and have any person decendants to have all their correct attributes
instantiated.
I'd love to see a project to give postgres an ODMG (Object Database
Management Group) standard interface for various languages. The guts of
postgres clearly has all the fundamentals, but a few details like this
would have to be added.
--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com