Обсуждение: Type mappings
Hi, folks I'm trying to build a PostgreSQL interface to Guile/scheme (with much help from others), the PostgreSQL part of squile (see http://www.forcix.cx/squile.html). Obe of the things squile tries is to transform the database types into native language types. For PostgreSQL this would imply looking things up in the pg_types table and figuring out what to do. I've had a peek into two of the interfaces which try to do a type mapping: jdbc and odbc. My impression is: - odbc tries to do a static mapping, ignoring whatever is in the pg_types Right? - jdbc looks the type name (via the oid, as given by PQftype()) and decides based on that. The result is cached (which tome looks a bit problematic, since the contents of the table could change at run time, but I may be too pedantic here ;) It would be interesting to write up a type mapper which could be of some more general utility (no idea whether this would be feasible at all). I'd be willing to give it a try. I'm sure this has been discussed at length elsewhere -- but I didn't manage to find anything searching this list's archives. I would appreciate any pointers in this directions. Thanks a lot -- tomas
tomas@aura.de wrote: > Obe of the things squile tries is to transform the database > types into native language types. > > For PostgreSQL this would imply looking things up in the > pg_types table and figuring out what to do. ... > - jdbc looks the type name (via the oid, as given by PQftype()) > and decides based on that. The result is cached (which to me > looks a bit problematic, since the contents of the table could > change at run time, but I may be too pedantic here ;) Types don't change too often. Not often enough to hurt the UI's anyway, which is what JDBC is for. On the other hand,speed gain from caching type information is significant. > It would be interesting to write up a type mapper which could be > of some more general utility (no idea whether this would be > feasible at all). I'd be willing to give it a try. It is a useful thing. E.g. my project needs conversion between SDDF, SQL, (restricted form of) Prolog, and Jess (don'task why--it's a long story). I would definitely appreciate a generic type mapper. It is feasible too. My chickenscratch has worked so far. :) > I would appreciate any pointers in this directions. JDBC source has the queries you need. PostgreSQL manual decribes the types. It's just a matter of sitting down and writinga pretty wrapper/converter. =alex
> > tomas@aura.de wrote: [...] > Types don't change too often. Not often enough to hurt the > UI's anyway, which is what JDBC is for. On the other hand, > speed gain from caching type information is significant. > Ummm... I wasn't implying not to cache the types -- I think caching is a must. But we could (for example) invalidate the cache whenever anything happens to the pg_types table (that would imply setting up a notifier (did I get the PostggreSQL parlance right?) > > It would be interesting to write up a type mapper which could be > > of some more general utility (no idea whether this would be > > feasible at all). I'd be willing to give it a try. > > It is a useful thing. E.g. my project needs conversion between > SDDF, SQL, (restricted form of) Prolog, and Jess (don't ask > why--it's a long story). I would definitely appreciate a > generic type mapper. It is feasible too. My chicken scratch > has worked so far. :) > The question is... what do we use as the target data types? I could just go for the Guile data types -- but then we have Just Another Type Mapper, Not Useful To Anyone (TM). Maybe with a bit more of thought (say you can register conversion functions with it, depending on the PostgreSQL type name (but it might make sense to base our decisions on other attributes of the table, like the input/output functions)) we could get something more useful. > JDBC source has the queries you need. PostgreSQL manual > decribes the types. It's just a matter of sitting down > and writing a pretty wrapper/converter. > Thanks, I'm already having a look at it. It's very readable. regards -- tomas
tomas@aura.de wrote: > Ummm... I wasn't implying not to cache the types -- I think > caching is a must. But we could (for example) invalidate the > cache whenever anything happens to the pg_types table (that > would imply setting up a notifier (did I get the PostggreSQL > parlance right?) Yes, that's a nice solution. I don't need it right now, so I don't really care. ;) > > > It would be interesting to write up a type mapper which could be > > > of some more general utility (no idea whether this would be > > > feasible at all). I'd be willing to give it a try. > > > > It is a useful thing. E.g. my project needs conversion between > > SDDF, SQL, (restricted form of) Prolog, and Jess (don't ask > > why--it's a long story). I would definitely appreciate a > > generic type mapper. It is feasible too. My chicken scratch > > has worked so far. :) > > > The question is... what do we use as the target data types? I > could just go for the Guile data types -- but then we have > Just Another Type Mapper, Not Useful To Anyone (TM). Maybe > with a bit more of thought (say you can register conversion > functions with it, depending on the PostgreSQL type name (but > it might make sense to base our decisions on other attributes > of the table, like the input/output functions)) we could > get something more useful. You might get some ideas from SDDF: http://www-pablo.cs.uiuc.edu/Project/SDDF/SDDFOverview.htm I think, type name aloneis sufficient, but users should be able to specify their own conversion functions. For example, a table: create tablefoo (bar1 text, bar2 int4); insert into foo values ('one', 1); insert into foo values ('two', 2); might be translatedinto Prolog as: % foo(+string, +integer) foo('one', 1). foo('two', 2). However, this is not the only way (andoften not the best way) to do the conversion. What's best should be decided by the user. All *I* am looking for isa neat wrapper around all those selects that fills in some parse-tree-like structures. I am not particularly picky abouttypes, but I would like the thing to run in the database backend, as opposed to another process (huge performance advantagein my case). I would prefer it to be in Tcl, or, failing that, in C. Basically, I need to select a subset of datafrom a bunch of tables and convert it for use by other tools, e.g. for a data mining or a production system; and converttheir output back to SQL. It is just one example of how a generic type mapper might be used. Your goals might betotally different. =alex