Patch to trim trailing spaces from char columns in PostgreSQL

[Andrus Moor]

I created patch to allow remove trailing spaces from retrieved character
strings in PostgreSQL.

Please review and commit.

Andrus

[gmo...@gmail.com]

I commited your edit.
Question - what are you trying to trim? A fixed-length char column?

> trimspaces.patch
> 1KDownload

[Andrus Moor]

> I commited your edit.

Thank you very much.

> Question - what are you trying to trim? A fixed-length char column?

Yes.
Historically I have only CHARACTER type columns in database, not VARCHAR
(this seems a bad database design for .NET application).
Database is in use in large number of places. Changing CHAR->VARCHAR is huge
work, requires to write conversion scripts and change existing not .NET
application code.

Also I'm not sure that varchar type variable lenght primary key is OK.
Even in DbLinq northwind sample database varchar primary key CustomerID is
padded with underscores:

ATT__
BT___

any idea variable lenght varchar key is not used but all primary key field
are padded to fixed size ?

Trailing spaces look ugly in Winforms controls.
I tried several months to override formatting and parsing events to hide
trailing spaces from user.
However this becomes very complicated in comboboxes and virtual grids, when
custom and winforms data binding is involved.

I hope that all string data is retrieved in this single place in DataReader
ReadString() function and this does not cause any bad effects.
Is it so ?

Andrus.

[Andrus Moor]

> I commited your edit.

This is broken now. Postgres data reader is not called anymore after last
refactoring.
I created patch to fix this.

Andrus.

Index: DbLinq/Util/RowEnumeratorCompiler.cs

===================================================================

--- DbLinq/Util/RowEnumeratorCompiler.cs (revision 200)

+++ DbLinq/Util/RowEnumeratorCompiler.cs (working copy)

@@ -732,7 +732,10 @@

object o = dataRecord.GetValue(index);

if (o == null || o is DBNull)

return null;

- return o.ToString();

+ if (!Settings.TrimEnd)

+ return o.ToString();

+ return o.ToString().TrimEnd();

}

private static bool GetAsBool(IDataReader2 dataRecord, int index)

[picrap]

Sorry for the error.
However, we should consider this kind of patch directly in the driver,
not in the common part.

Also, since I didn't notice any unit testing regression this may imply
tha

[picrap]

Sorry for that... I'll need to be more focused on such kind of
details.

However, I will make two remarks:
1. This kind of patch has nothing to do within the DbLinq core. It may
eventually be in the specific driver.
2. I didn't notice any regression, and I don't understand why (I
honestly didn't check the tests): I PostgreSql has a problem when
treating variable length strings a fixed length one, shouldn't have I
seen this regression somewhere?

On 14 fév, 21:12, "Andrus Moor" <kobrule...@hot.ee> wrote:

[gmo...@gmail.com]

This trimming of fixed-length columns is a new feature, going beyond
what's in MS Linq-to-Sql.
We need a test that works on fixed-length char column.
Tomorrow, I am going to put this into the 'Andrus' database, where I
am collecting tests that cannot go against Northwind.
Andrus DB exists so far only for Mysql and Pgsql, needs porting to
Oracle, SqlServer and SqlLite.

[Andrus Moor]

>We need a test that works on fixed-length char column.
Tomorrow, I am going to put this into the 'Andrus' database, where I
am collecting tests that cannot go against Northwind.

Unfortunately Northwind does not have CHAR columns, only VARCHAR
However it is possible to create TrimEnd unit test against VARCHAR column
also:

1. set column to value having trailing spaces
2. read column back and verify that there is no trailing spaces.

or use CREATE TABLE or ALTER COLUMN to create CHAR column.

> Andrus DB exists so far only for Mysql and Pgsql, needs porting to
Oracle, SqlServer and SqlLite.

I think all or most of this database issues can be created against northwind
database also so this can be removed.

Is it resonable to use Datacontext ExecuteCommand() to modidy database if
this is required for tests ?

Btw. Is CREATE TEMP TABLE portable across database systems ? Does all have
TEMP clause and is it safe to use this in tests ?
TEMP tables are dropped automatically in end of connection in Postgres

I have C# 2008 Express only. How to run unit tests in C# express without
modifying code ?

Andrus.

[Andrus Moor]

> 1. This kind of patch has nothing to do within the DbLinq core. It may
eventually be in the specific driver.

I described the reasons for this patch I other message some weeks ago in
this newsgroup.

Basically if someone like I has trailing spaces in columns in database this
patch allows to trim trailing spaces without changing
database structure from char to varchar.
Mostly this happens with CHAR type columns.
.NET WinForms UI does not work well with CHAR type of columns, trailing
spaces cause frustrating UI experience.
Fixing this in code is huge work, requires overriding Formatting and Parse
events in all controls and more. I wast a number of days trying this and
then desided to fix this in DbLinq level. After this fix all those issues
suddenly disappear.
I find that most reasonable solution in my case is to add this facility to
DbLinq.

This issue may occur in all dbms system, it is not PostgreSQL specific.
There is no place (and no reason) to add this in new DbLinq architecture to
PostgreSQL only, new core calls Reader GetString() directly without wrapper
code.

Yes this may be a little specific but if possibly I would be very happy to
have this function in DbLinq. Maybe in future if I move to .NET I will
create
script to convert all my CHAR columns to VARCHAR columns. I'm currently
testing .NET only and existing applicaton works very well and even expects
CHAR columns.

> 2. I didn't notice any regression, and I don't understand why (I
honestly didn't check the tests): I PostgreSql has a problem when
treating variable length strings a fixed length one, shouldn't have I
seen this regression somewhere?

Previous version trims trailing spaces if TrimEnd is set to true.
Rev. 200 does not trim trailing spaces in this case.
Maybe to move old reader code from source.

Andrus.

[picrap]

OK, I think I got your point, and here are a few thoughts/suggestions:
1. This is probably still not a DbLinq problem, but a most general DB
problem.
2. So, I still think DbLinq's must not fix this (or we will get tons
of specific cases like this one).
3. I suggest we open the data mapping (and any suitable part) to
"extensions" (I also suggest that we may allow extensions :)). So for
example, ending spaces trimming may be an extension, called each time
there is a string mapping.

Doing this, we keep the core as a core and we allow lot of extensions.

I'm going think about this and make suggestions here, if you agree
with the points above.

[Andrus Moor]

>1. This is probably still not a DbLinq problem, but a most general DB
problem.
>2. So, I still think DbLinq's must not fix this (or we will get tons
of specific cases like this one).
>3. I suggest we open the data mapping (and any suitable part) to
"extensions" (I also suggest that we may allow extensions :)). So for
example, ending spaces trimming may be an extension, called each time
there is a string mapping.

I don't see currently any other specific extension nor tons of specific
cases coming.
For me this tiny change is sufficient.

Please apply patch or allow implement TrimEnd in DbLinq GetString in any way
you prefer.

> make suggestions here

I really need working TrimEnd implementation for DbLinq usage.

Andrus.

[Andrus Moor]

>3. I suggest we open the data mapping (and any suitable part) to
"extensions" (I also suggest that we may allow extensions :)). So for
example, ending spaces trimming may be an extension, called each time
there is a string mapping.

>Doing this, we keep the core as a core and we allow lot of extensions.

>I'm going think about this and make suggestions here, if you agree
with the points above.

Can we implement something like Formatting / Parse pattern in Winforms
DataGridView:

DbLinq raises GetString() event on each string string property retrieval,
passing readed value, property and entity as event parameters and allowing
to change value property in event handler.

Or use single event after whole row is loaded from database.

Andrus.