Re: [xmlblaster] MySQL

[Michele Laghi <laghi at swissinfo.org>]

OK I have committed the code.
Note that I also added "not null" on the nodeId for the entries table 
since this is also a key (it is anyway never null). I thought a key is 
always implicitly not null.

For the sake of genericity I named the two new mappings:
"blobVarName" (defaults to "blob") and "keyAttr" (defaults to ""). So 
you can add to the mappings for MySql the following:

",blobVarName=blobData,keyAttr= not null"

Note that the space at the begin of " not null" is important.

I did not hardcode "blobData" since databases currently in production 
would have compatibility problems when upgrading.

If there still is stuff to change you can commit the changes yourself. 
When looking for a solution please always choose generic solutions 
instead of specific ones. In case of indecisions there is always the 
mailing list.

And now enjoy the match ;)

Michele



Peter Bennett wrote:
> Greetings Michele
>
> Can you make and test the following changes to
> based on the notes that follow also...
> not null needs to be mapped...
>
> JdbcManagerCommonTable.java
>
> req = "CREATE TABLE " + this.nodesTableName.toUpperCase() + " (nodeId " 
> + this.stringTxt + " not null, PRIMARY KEY (nodeId))";
>
> req = "CREATE TABLE " + this.queuesTableName.toUpperCase() +
>           "(queueName " + this.stringTxt +
>                   " not null, nodeId " + this.stringTxt +
>                   " not null, numOfBytes " + this.longintTxt +
>                   ", numOfEntries " + this.longintTxt +
>                   ", PRIMARY KEY (queueName, nodeId)" +
>                   ", FOREIGN KEY (nodeId) REFERENCES " 
> +                      this.nodesTableName + " ON DELETE CASCADE)";
>
> req = "CREATE TABLE " + this.entriesTableName.toUpperCase() + " (dataId 
> " + this.longintTxt +
>                   " not null, nodeId " + this.stringTxt +
>                   ", queueName " + this.stringTxt +
>                   " not null, prio " + this.intTxt +
>                   ", flag " + this.stringTxt +
>                   ", durable " + this.booleanTxt +
>                   ", byteSize " + this.longintTxt +
>                   ", ablob " + this.blobTxt +
>                   ", PRIMARY KEY (dataId, queueName)" +
>                   ", FOREIGN KEY (queueName, nodeId) REFERENCES " + 
> this.queuesTableName + " ON DELETE CASCADE)";
>
> Tables created in MySQL are not allowed to have fields that are
> defined as keys with null values therefor they must have the not null
> qualifier... varchar(128) is the correct mapping for string as you
> suggested. Thanks.
>
> I don't think you will be creating records with null values in your key
> fields...
>
> Maybe it needs a mapping... Don't map or Map to "" for db's that don't
> care.... That way config files don't need changing just the mapping
> added for MySQL. :)
>
> The blob field name has to be changed for MySQL...
> Note I have had similar problems with other key words... Namely "user".
>
> You have an error in your SQL syntax near 'blob blob, PRIMARY KEY 
> (dataId, queueName), FOREIGN KEY (queueName, nodeId) REFE' at line 1"
>
> If I change the field name to ablob it works fine...
> Obviously code using that field name will need changing as well.
> Line #712 or there abouts req = .... I could not find anything else
> quickly...
> You know it better than me...
>
> It all needs testing under postgres and the other dbs... I will test
> here with postgres and MySQL but I am running out of time today... Gotta
> have dinner then go out and watch the All Black rugby team thrash the
> Frogs (French) on Sky. We love to hate them... Rainbow Warrior lives on.
>
> Back into it all day tomorrow...
>
> Regards


--
Michele Laghi
mailto:laghi at swissinfo.org
tel. +46 8 7492952 / mob. +46 70 4103964
http://www.geocities.com/laghi2000
http://www.xmlBlaster.org