Re: [sqlite] Please fix the EBCDIC support

[mario@tiscali]

Hello, I just found this thread and I consider it very interesting.

John's porting of SQLite to z/OS is a very nice option, and as he 
mentioned before, the modifications to get to it were minor, if any.
As far as I understand though, the result is a database which stores 
numbers as big-endian BUT strings in the local EBCDIC encoding of the 
hosting system. While this deviates from the statement that defines 
SQLite textual data as UTF-8 encoded, everything works fine (to the 
extent we tested it so far) if using a z/OS created DB on the same z/OS.


I believe this results from what is stated in 
http://www.sqlite.org/version3.html:


"SQLite is not particular about the text it receives and is more than 
happy to process text strings that are not normalized or even 
well-formed UTF-8 or UTF-16. Thus, programmers who want to store IS08859 
data can do so using the UTF-8 interfaces. As long as no attempts are 
made to use a UTF-16 collating sequence or SQL function, the byte 
sequence of the text will not be modified in any way."


Of course the uncompatible text encoding makes the z/OS DB unusable on 
other platforms and vice-versa.


I personally agree with "k" that encoding text strings in the expected 
format is user's responsibility, and even on z/OS at least 
sqlite3_prepare() and sqlite3_bind_text()  should pass their parameter 
in UTF-8 format where needed (I am sure there are other APIs who need 
the same approach). By the way this is easily doable using the iconv() 
family of services.


What I think though is that, even properly encoding API parameters in 
UTF-8 would not be enough to produce a portable SQLite DB.


To get there, in my opinion we should need to fix a set of text strings 
internally used by SQLite, that in z/OS are EBCDIC encoded, and other 
platforms expect to be UTF-8. One example is the SQLITE_FILE_HEADER, 
which John has fixed using a configure 'trick'. Another, I think, 
affects the sqlite_master table where in addition to the CREATE 
statement (which should be passed UTF-8 by the caller), there is a type 
colum, defined as text, which on z/OS is again EBCDIC encoded. Not sure 
this is the only one, but moving a very simple z/OS created DB on linux, 
and fixing the sqlite_master issue I was able to use it as-is.


I think that it would be nice to have z/OS creating a structurally 
healthy DB. This would allow portability for users willing to embrace 
the EBCDIC<->UTF-8 conversion for user data, without requiring 
additional effort for users not interested in portability.


While I am not fluent in SQLite internal structure, I am willing to help 
removing these flaws if directed / supported.


Thank you,
mario
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please fix the EBCDIC support

[John McKown]

On Thu, Oct 16, 2014 at 4:33 PM, k  wrote:

> Hi all,
> this is my first reply-to post to this mailing list (using gmane nttp
> interface) so I hope this post passes moderation ok and is correctly
> threaded and not duplicated...).
>
> Regarding the inability to use databases created on EBCDIC systems on
> 'normal' systems, my initial thoughts are that sqlite should at least
> create databases with the magic number 'SQLite format 3' correctly - ie the
> magic number should be encoded in the source as a hex literal rather than a
> string.
>

​I did this on a port of 3.8.7 that I am working on. It turns out that this
is a const char which is initialized by a C macro variable called
SQLITE_FILE_HEADER. I created a "do_config.sh" file to contain my
./configure. It looks like:

​export CFLAGS='-O3 -V -DSQLITE_MAX_MMAPSIZE=1048576 -qTARG=zOSV1R12 '
export CFLAGS="${CFLAGS} -qLANG=EXTC99 -qFLOAT=IEEE -qnolist -qnosource "
export CFLAGS="${CFLAGS} -D_POSIX_C_SOURCE=200112L -D_XOPEN_SOURCE=600"
export CFLAGS="${CFLAGS} -DSQLITE_ENABLE_COLUMN_METADATA"
export CFLAGS="${CFLAGS} -DSQLITE_ENABLE_SQLLOG"
export CFLAGS="${CFLAGS} -DHAVE_POSIX_FALLOCATE=0"
export CFLAGS="${CFLAGS}
-DSQLITE_FILE_HEADER='\"123121114151164145040146157162155141164040063000\"'"
# The above is the octal encoding for the phrase "SQLite format 3\0" in
ASCII.
# This is the first phase of being compatible with the ASCII version of the
sqlite data base file.
# Each octal value requires four \ in front of it due to multiple shell
evaluations. When presented
# to the compiler, it will look like a single \ as it really should.
./configure CC=xlc CPP="xlc -E" CXX=xlc++ CXXPP="xlc++ -E"
--prefix=$PWD/sqlite-run​


Guess what? I can use "sqlite3" on either Linux or z/OS to create a test
sqlite3 data base file. I can ftp it, in binary, to the other system. And
on that system, I can access it without an error message. !! HOWEVER !! Due
to the differences in encoding, neither side can see any tables created by
the other side. I have not had any time to check up on why this is so. I
remember some of the code does a check to validate any "name". And a "name"
encoded in EBCDIC does not validate when accessed on an ASCII system. And
vice versa. "fixing" this, first of all, would likely be a major task. And,
more importantly, likely above my current C knowledge.



>
> Regarding codepage conversions, how does this normally work? As Teg said
> (or alluded to), should it not be the application's responsibly to do the
> codepage conversion and pass to the sqlite engine the text in the
> appropriate Unicode encoding (UTF-8/UTF-16) as required. I'm not sure
> though, if there is a distinction here between using the bind functions of
> the API vs supplying literal text in the SQL. ** The documentation on the C
> API does not say a lot about how encodings are  handled for text (bound or
> in the SQL) **. I would expect in any case, if the application is locale
> aware it should respect the settings of the respective LC_* environment
> variables and perform the necessary conversions before calling the sqlite
> functions??
>
> Regarding the specific case of performing EBCDIC<->ASCII conversions on
> zOS, this is (IMHO) not the approach to take, since (outside of the unix
> subsystem of zOS) any EBCDIC codepage can be in use, and as per the above,
> a locale aware application should take care of this, rather than leaving it
> to the sqlite engine.
>
> All the above of course would (again IMHO) be moot for blobs, where
> everything would be stored as is.
>
> On the subject of support for sqlite on zOS, has anyone investigated the
> possibility on making the command line interface, or the engine itself (via
> a custom vfs) support the MVS (record orientated) filesystem?
>
> Please share your thoughts,
> Thanks in advance,
>
> k
>
>
​To address all of "k"'s requirements, I _think_ that it would require a
change in SQLite's architecture. SQLite would really need to work in,
perhaps, UTF-8 for all its internal information. I say UTF-8 because most
of the data kept in z/OS EBCDIC (IBM-1047) characters can be "round trip"
converted to/from UTF-8. And using UTF-8 would likely _not_ impact any
current ASCII users. But that is up to Dr. Hipp to decide. Definitely above
my pay grade.​


-- 
The temperature of the aqueous content of an unremittingly ogled
culinary vessel will not achieve 100 degrees on the Celsius scale.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please fix the EBCDIC support

[k]

Hi all,
this is my first reply-to post to this mailing list (using gmane nttp 
interface) so I hope this post passes moderation ok and is correctly 
threaded and not duplicated...).


Regarding the inability to use databases created on EBCDIC systems on 
'normal' systems, my initial thoughts are that sqlite should at least 
create databases with the magic number 'SQLite format 3' correctly - ie 
the magic number should be encoded in the source as a hex literal rather 
than a string.


Regarding codepage conversions, how does this normally work? As Teg said 
(or alluded to), should it not be the application's responsibly to do 
the codepage conversion and pass to the sqlite engine the text in the 
appropriate Unicode encoding (UTF-8/UTF-16) as required. I'm not sure 
though, if there is a distinction here between using the bind functions 
of the API vs supplying literal text in the SQL. ** The documentation on 
the C API does not say a lot about how encodings are  handled for text 
(bound or in the SQL) **. I would expect in any case, if the application 
is locale aware it should respect the settings of the respective LC_* 
environment variables and perform the necessary conversions before 
calling the sqlite functions??


Regarding the specific case of performing EBCDIC<->ASCII conversions on 
zOS, this is (IMHO) not the approach to take, since (outside of the unix 
subsystem of zOS) any EBCDIC codepage can be in use, and as per the 
above, a locale aware application should take care of this, rather than 
leaving it to the sqlite engine.


All the above of course would (again IMHO) be moot for blobs, where 
everything would be stored as is.


On the subject of support for sqlite on zOS, has anyone investigated the 
possibility on making the command line interface, or the engine itself 
(via a custom vfs) support the MVS (record orientated) filesystem?


Please share your thoughts,
Thanks in advance,
k

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please fix the EBCDIC support

[Teg]

Hello John,

I  feed  everything  to Sqlite in UTF-8.  If it's coming from Windows,
that  means  I have to do a UTF-16 to UTF-8 conversion.  I know Sqlite
has UTF-16 support but, I want things to be consistent across all OS's
I  work  with.  The  less  I have to think about things like this, the
better. 

C


Thursday, October 16, 2014, 10:49:52 AM, you wrote:

JM> On Thu, Oct 16, 2014 at 9:09 AM, Richard Hipp  wrote:
>> On Thu, Oct 16, 2014 at 9:53 AM, John McKown 
>> wrote:
>>
>>> On Wed, Sep 24, 2014 at 9:46 PM, Richard Hipp  wrote:
>>> > Please try the latest version of SQLite on trunk to see if that works
>>> > better.  Specifically, apply the patch at
>>> >
>>> > http://www.sqlite.org/src/vpatch?from=b2c89ef49cd1=ef30e0352b3d
>>> >
>>> > --
>>> > D. Richard Hipp
>>> > d...@sqlite.org
>>>
>>> Unfortunately, the ASCII vs. EBCDIC issued _does_ make it
>>> impossible to share a single SQLite data base file between z/OS and
>>> other ASCII-based SQLite systems.
>>
>>
>> If you store content in EBCDIC, it is retrieved in EBCDIC and if you store
>> content in ASCII it is retrieved in ASCII, regardless of which platform you
>> do the storing and retrieving on.  I wonder if this is the right approach.
>> Perhaps the zOS patch should be amended to simply transform EBCDIC->ASCII
>> on input and ASCII->EBCDIC on output.
>>

JM> Hum, I'll need to look more closely at the code to see where this
JM> would need to be implemented. As I indicated previously, making it
JM> work on z/OS was so easy that I didn't need to really look closely at
JM> the code itself. I basically compiled and it worked. And this was my
JM> first attempt to port something to the z/OS environment, so I was not
JM> very knowledgeable about it. I am somewhat more knowledgeable today
JM> due to some other porting work that was somewhat more involved. I
JM> guess that instead of saying ASCII<->EBCDIC, I need to say more
JM> exactly ISO8859-1<->IBM-1047 since there are many "ASCII' and "EBCDIC"
JM> code pages. Or does SQLite use UTF-8 internally? Hum, something else
JM> to look into. I am not really that familiar with SQLite's internals.
JM> Thanks for the thoughts. I appreciate your help. If I have more
JM> questions, I guess that I would go over to the sqlite-dev forum.

>>
>>
>>> I haven't looked closely enough at
>>> the code to see if the big-endian (z/OS) vs. little-endian (Intel)
>>> storage of integers would also be a problem.
>>>
>>>
>> We do cross-platform database portability tests between x86, sparc, and
>> PPC, to verify that big-endian vs little-endian is not a factor.

JM> Thanks for telling me that. I won't worry about it again.

>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users






-- 
 Tegmailto:t...@djii.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please fix the EBCDIC support

[John McKown]

On Thu, Oct 16, 2014 at 9:09 AM, Richard Hipp  wrote:
> On Thu, Oct 16, 2014 at 9:53 AM, John McKown 
> wrote:
>
>> On Wed, Sep 24, 2014 at 9:46 PM, Richard Hipp  wrote:
>> > Please try the latest version of SQLite on trunk to see if that works
>> > better.  Specifically, apply the patch at
>> >
>> > http://www.sqlite.org/src/vpatch?from=b2c89ef49cd1=ef30e0352b3d
>> >
>> > --
>> > D. Richard Hipp
>> > d...@sqlite.org
>>
>> Unfortunately, the ASCII vs. EBCDIC issued _does_ make it
>> impossible to share a single SQLite data base file between z/OS and
>> other ASCII-based SQLite systems.
>
>
> If you store content in EBCDIC, it is retrieved in EBCDIC and if you store
> content in ASCII it is retrieved in ASCII, regardless of which platform you
> do the storing and retrieving on.  I wonder if this is the right approach.
> Perhaps the zOS patch should be amended to simply transform EBCDIC->ASCII
> on input and ASCII->EBCDIC on output.
>

Hum, I'll need to look more closely at the code to see where this
would need to be implemented. As I indicated previously, making it
work on z/OS was so easy that I didn't need to really look closely at
the code itself. I basically compiled and it worked. And this was my
first attempt to port something to the z/OS environment, so I was not
very knowledgeable about it. I am somewhat more knowledgeable today
due to some other porting work that was somewhat more involved. I
guess that instead of saying ASCII<->EBCDIC, I need to say more
exactly ISO8859-1<->IBM-1047 since there are many "ASCII' and "EBCDIC"
code pages. Or does SQLite use UTF-8 internally? Hum, something else
to look into. I am not really that familiar with SQLite's internals.
Thanks for the thoughts. I appreciate your help. If I have more
questions, I guess that I would go over to the sqlite-dev forum.

>
>
>> I haven't looked closely enough at
>> the code to see if the big-endian (z/OS) vs. little-endian (Intel)
>> storage of integers would also be a problem.
>>
>>
> We do cross-platform database portability tests between x86, sparc, and
> PPC, to verify that big-endian vs little-endian is not a factor.

Thanks for telling me that. I won't worry about it again.

>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
The temperature of the aqueous content of an unremittingly ogled
culinary vessel will not achieve 100 degrees on the Celsius scale.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please fix the EBCDIC support

[Richard Hipp]

On Thu, Oct 16, 2014 at 9:53 AM, John McKown 
wrote:

> On Wed, Sep 24, 2014 at 9:46 PM, Richard Hipp  wrote:
> > Please try the latest version of SQLite on trunk to see if that works
> > better.  Specifically, apply the patch at
> >
> > http://www.sqlite.org/src/vpatch?from=b2c89ef49cd1=ef30e0352b3d
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
>
> Unfortunately, the ASCII vs. EBCDIC issued _does_ make it
> impossible to share a single SQLite data base file between z/OS and
> other ASCII-based SQLite systems.


If you store content in EBCDIC, it is retrieved in EBCDIC and if you store
content in ASCII it is retrieved in ASCII, regardless of which platform you
do the storing and retrieving on.  I wonder if this is the right approach.
Perhaps the zOS patch should be amended to simply transform EBCDIC->ASCII
on input and ASCII->EBCDIC on output.



> I haven't looked closely enough at
> the code to see if the big-endian (z/OS) vs. little-endian (Intel)
> storage of integers would also be a problem.
>
>
We do cross-platform database portability tests between x86, sparc, and
PPC, to verify that big-endian vs little-endian is not a factor.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please fix the EBCDIC support

[John McKown]

On Wed, Sep 24, 2014 at 9:46 PM, Richard Hipp  wrote:
> Please try the latest version of SQLite on trunk to see if that works
> better.  Specifically, apply the patch at
>
> http://www.sqlite.org/src/vpatch?from=b2c89ef49cd1=ef30e0352b3d
>
> --
> D. Richard Hipp
> d...@sqlite.org

Dr. Hipp,

I did the port of SQLite to z/OS mentioned by the OP. Sorry it took me
so long to test the above fix out. I downloaded the latest 3.8.7
amalgamation source and applied that patch to it (actually I looked at
what it did and hand edited the files myself). This did indeed fix the
compile problem. I hope to be able to do some testing and get your
excellent code available to other z/OS users as soon as possible. I
don't really have a date in mind.

I will also mention that the z/OS xlc compiler has a "problem" with
the ./configure script. On z/OS 2.1, the script detects that the
fpurge() function exists. But this function did not exist in earlier
versions of z/OS. So if, like me, you want to target a prior release
(like targeting a lower level kernel that the one you're running on),
./configure sets the HAVE_POSIX_FALLOCATE and so the code precompiles
to use fpurge(), but the compiler itself complains that the code is
invalid, due to targeting the back level z/OS versions. I have found
an easy work around. I simply put the phrase
"-DHAVE_POSIX_FALLOCATE=0" in the CFLAGS used by ./configure. This
allows a clean compile which does _not_ use the fpurge().

Again my thanks for your excellent work. I did not really have to do
_anything_, code wise, to get your program to run on z/OS in an EBCDIC
environment. Unfortunately, the ASCII vs. EBCDIC issued _does_ make it
impossible to share a single SQLite data base file between z/OS and
other ASCII-based SQLite systems. I haven't looked closely enough at
the code to see if the big-endian (z/OS) vs. little-endian (Intel)
storage of integers would also be a problem.

-- 
The temperature of the aqueous content of an unremittingly ogled
culinary vessel will not achieve 100 degrees on the Celsius scale.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please fix the EBCDIC support

[Richard Hipp]

Please try the latest version of SQLite on trunk to see if that works
better.  Specifically, apply the patch at

http://www.sqlite.org/src/vpatch?from=b2c89ef49cd1=ef30e0352b3d

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please fix the EBCDIC support

[John McKown]

Dr. Hipp,

I did the "port" (which was easy thanks to your excellent work with
EBCDIC which is already in the base) which is being distributed via
the CBTTape (http://cbttape.org) web site. I would be happy to test
any fixes which might desired, if that is in accordance with your
policy. I can understand where it might not be, due to dependence on a
possibly unreliable source (aka "me").

The source in question is in the sqlite3.c file in the section which looks like:

SQLITE_API int sqlite3_compileoption_used(const char *zOptName){
  int i, n;
  if( sqlite3StrNICmp(zOptName, "SQLITE_", 7)==0 ) zOptName += 7;
  n = sqlite3Strlen30(zOptName);

  /* Since ArraySize(azCompileOpt) is normally in single digits, a
  ** linear search is adequate.  No need for a binary search. */
  for(i=0; i wrote:
> On Wed, Sep 24, 2014 at 10:27 AM, k  wrote:
>
>> SQLite team,
>> compile of sqlite3 v3.8.6 amalgamation failed on zOS 2.1 with err CCN3045
>> undeclared identifier sqlite3CtypeMap in function
>> sqlite3_compileoption_used. I notice that this was raised previously to the
>> mailing list by John McKown. Please consider adding the necessary fixes to
>> the SQLite code.
>>
>
> Unfortunately, we do not have access to a zOS machine (real or virtual) on
> which to test the changes.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please fix the EBCDIC support

[Richard Hipp]

On Wed, Sep 24, 2014 at 10:27 AM, k  wrote:

> SQLite team,
> compile of sqlite3 v3.8.6 amalgamation failed on zOS 2.1 with err CCN3045
> undeclared identifier sqlite3CtypeMap in function
> sqlite3_compileoption_used. I notice that this was raised previously to the
> mailing list by John McKown. Please consider adding the necessary fixes to
> the SQLite code.
>

Unfortunately, we do not have access to a zOS machine (real or virtual) on
which to test the changes.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Please fix the EBCDIC support

[k]

SQLite team,
compile of sqlite3 v3.8.6 amalgamation failed on zOS 2.1 with err 
CCN3045 undeclared identifier sqlite3CtypeMap in function 
sqlite3_compileoption_used. I notice that this was raised previously to 
the mailing list by John McKown. Please consider adding the necessary 
fixes to the SQLite code.

Many thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users