db-ddlutils-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Eric Auer <eric.a...@mpi.nl>
Subject Re: ddlutils troubles and workarounds with svn, mysql, postgresql
Date Mon, 04 Feb 2008 15:17:18 GMT
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hi Thomas,

sorry for the late reply and thanks for your answers :-).

> If you really want to work on the trunk version...
> you can simply build the JAR via
> 
> ant jar
> 
> There is no need to use 'dist' target if you don't
> want the full documentation build.

I think I did not use an explicit target, just the
default one. Maybe you could put a note on the "how
to get the SVN version" page which tells that most
people only need the "jar" ant target :-).


>> Next attempt was to download the 1.0 binary. It
>> turned out that the binary already contains three
>> things: The docs, the main jar, and most of the
>> supplemental jars. Very nice...

>   <fileset dir="lib">
>     <include name="**/*.jar"/>

I used lib/ and *.jar instead of lib and **/*.jar,
but assumed that both should work...?

>     <include name="**/*.zip"/>

Why include zips in the classpath?


> As for what downloadable artifact contains what,
> I filed a JIRA ticket
> ... issues.apache.org/jira/browse/DDLUTILS-191
> to make that more obvious on the web page.

Thanks :-)


>> Somehow mysql uses 0000-00-00 00:00:00 and null as
>> synonyms for null for timestamps. However, you
>> cannot insert such invalid (month/day are 0) date
>> timestamps into postgresql databases...

> This should already be handled by DdlUtils.
> Could you file an issue in JIRA and perhaps attach
> sample files to reproduce it (e.g. MySql SQL to create
> and fill the DB, the Ant snippet that you used to read
> the DB with DdlUtils) ?

Hmm it might be hard to create a small test case...
You could contact me off-list about that.


>> Second problem is that bugzilla uses indexes on
>> some varchar columns. Postgresql uses btree indexes
>> in a way where each value must be at most 8/3 kB.

> Mhmm, this looks like a DB-specific optimization
> (type of index)

Dunno... I mean btree is default and nice but it has
this limitation. You cannot automatically detect if
a column will contain btree incompatible data at some
point in the future, but you could mention the issue
in the documentation about postgresql and you could
suggest some sort of workaround: People could edit
the XML files or you could introduce a command line
option to use another type of index either for some
selected indexes or for all indexes. Or as in my case,
people can edit the XML files to leave areas without
index if the index is not performance critical :-).


>> mysql-connector-java-5.0.8-bin.jar
>> postgresql-8.2-507.jdbc4.jar

Can using jdbc4 connectors cause problems? Could
future ddlutils gain anything from using jdbc4?


>> postgresql database to XML files. It turned out
>> that ddl-utils failed to read the database schema
>> from the live database because of a missing schema
>> function. I was unable to get ddl-utils to proceed
...
>> ERROR: function information_schema._pg_keypositions()
>> does not exist is mentioned on
>> http://forum.hibernate.org/viewtopic.php?t=957909&view=next
>> as is some workaround: It might make a difference which
>> JDBC connector you use, but it always helps to:
>>
>> CREATE FUNCTION information_schema._pg_keypositions() RETURNS SETOF int4 AS
>> $BODY$select g.s
>>         from generate_series(1,current_setting('max_index_keys')::int,1)
>>         as g(s)$BODY$
>>   LANGUAGE 'sql' IMMUTABLE;
>> ALTER FUNCTION information_schema._pg_keypositions() OWNER TO postgres;
>> Note that you have to do this for the database in
>> question, not for the global information schema.

> DdlUtils is in  the same position here as Hibernate: this is a problem
> between the JDBC driver and the database itself. As far as DdlUtils is
> concerned, you can use a slightly older PostgreSql driver (8.0 or - if
> it works - 8.1).

Could you explain the underlying problem a bit and
why it helps to use a JDBC driver which is older
(or in some other way another version) than the DB
itself? What sort of operation is affected / what
type of tools beyond Hibernate and DDL Utils will
probably suffer from the problem? Thanks...


>> but I cannot use any TABLE NAME PATTERN. Would be
>> great if ddl-utils could implement that filter!
> 
> This is already in the list of upcoming features:
> https://issues.apache.org/jira/browse/DDLUTILS-87

Cool, so it is planned for 1.1? Whats the roadmap?


> You can also use XSLT using Ant's style task.
> E.g. check out this article for more info:
> http://www.ibm.com/developerworks/xml/library/x-antxsl/

Wow thats impressive... but beyond my skills ;-).


>> reason, pga_diagrams is only readable for the
>> superuser on one postgresql installation...

> Mhmm, you shouldn't have to be superuser to read
> a DB from postgres with DdlUtils. Especially since
> there should be no reason to dump pga_diagrams as it
> is (unless I'm mistaken) a system table.

I think it is, yes. Actually some users seem to have
a habit of just throwing away pga_* ... But as you
say, it is a system table so why dump it at all?
I assumed DDLUTILs or the JDBC driver were trying
to read it to get some DB metadata information...


>> PS: troubles in the precompiled docs I downloaded
>> later: The PDF uses very dark blue compared to the
>> HTML and the UML diagrams are not shown in the PDF.

Feel free to file an issue for that one, I have
no login for the JIRA afair...


>> And there is a content error where the docs say
>> that some DB cannot do tinyint in -255..255 range
>> but only in (signed byte) range. Some other place
>> in the docs says that tinyint can be signed or
>> unsigned byte...

Well if it can really be EITHER, then you would be
sort of right when you request at least 9 bits for
the target column when you store tinyints in DBs?
You say JDBC uses unsigned, DBs can use unsigned
or signed or even "something similar" :-).


>> ... By the way, VARCHAR can be
>> up to 10*1024*1024 Unicode chars, not 254 ASCII,
>> at least in 8.x versions of PostgreSQL :-).

I do not know if and how this can be configured, I
just wanted to mention that I believe that several
DBs no longer have their limit as low as 254 bytes.
The 10M Unicode chars might be an arbitrary default
in my copy of PostgreSQL.

Eric

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org

iD8DBQFHpyx+99dkROyhRRsRAlfuAJ40M6c6NIspjEUVRvyUwGsUrftVtACgxVLC
IrIkXKXhRmTw3+70XvykvUw=
=xtka
-----END PGP SIGNATURE-----

Mime
View raw message