libopendbx-devel — Help and discussion about OpenDBX

Re: [opendbx] slow insert with sqlite

[Alain R. <al...@fr...>]

Hi  Mariano,
I don't want to do multi threading with sqlite because I know it doesn't work.
I was curious about the squeakdbx (or opendbx architecture) because of the not so good performance and the time spent in waiting , I do not understand the squeakdbx package vs opendbx package: the doc is mentioning a squeakdbx plugin dll but I have no squeakdbx dll ? 

You are saying that in that case the external call is counted on the InputEventPollingFetcher>> wait and not in primitives (?).
I will investigate with FFI/SQlite and it should be the same (I've seen some messages about incorrect profiling reports in primitives),

I expected much better performance with sqlite , and glorp is  very good (5% of the time), I would have expected the contrary.

Thanks

Cheers
Alain
  "Mariano Martinez Peck" <mar...@gm...> a écrit dans le message de news:CAA+-=mVV...@ma......



  On Tue, Jul 5, 2011 at 10:50 PM, Alain Rastoul <al...@fr...> wrote:

    Hi,
    (sorry for sending this mail again, my pc was off for a long time and the
    message was dated from 2007, people who sort their messages would not see
    it)

    I've done a small program in Pharo 1.3 with glorp+opendbx that insert 1000
    rows in a customer table in a sqlite db.
    The 1000 insert takes 140 sec (very slow), but the Pharo profiler says that
    it spend 95%
    of the time waiting for input.
    (in InputEventPollingFetcher>> waitForInput)
    I was wondering if the queries are executed in another thread than the vm
    thread ?


  Hi Alain. No. Squeak/Pharo's thread architecture is the so called green thread, that is, only ONE OS thread is used. Internally, the language reifies Process, Scheduler, #fork: , etc etc etc. But from the OS point of view there is only one thread for the VM. So.....the regular FFI blocks the VM. What does it mean? that while the C function called by FFI is being executed, the WHOLE VM is block. Notihgn can happen at the same time. Imagine the function that retrieves the results and needs to wait for them.....TERRIBLE. So...if the backend does not support async quieries, then you are screw and dbx may be slow in Pharo. Nothing to do. 

  However, some backends support async queries, and opendbx let us configure this. This is explained in: 
  http://www.squeakdbx.org/Architecture%20and%20desing?_s=FlIhkPQOOFSlqf8C&_k=j-3_7Kw_&_n&18
  where it says "External call implementation"

  You can see the list of backends that support async queries in here: 
  http://www.squeakdbx.org/documentation/Asynchronous%20queries?_s=FlIhkPQOOFSlqf8C&_k=j-3_7Kw_&_n&17

  Notice that there is some room for improvements, but we didn't have time so far. Hernik told us some good ideas. But since we didn't need more power so far we couldn't find time to integrate his ideas. I am forwarding now the emails to the mailing list. If you can take a look and provide code, it would be awesome. Basically, it improves how and how much we wait in each side: image and opendbx.

  Finally, notice that Eliot is working in a multithreared FFI for Cog, but it is not yet available as far as I know. 

  Cheers

  Mariano


    (I thought I've seen a document about opendbx architecture but could'nt find
    it on the site).

    TIA
    Alain



    ------------------------------------------------------------------------------
    All of the data generated in your IT infrastructure is seriously valuable.
    Why? It contains a definitive record of application performance, security
    threats, fraudulent activity, and more. Splunk takes this data and makes
    sense of it. IT sense. And common sense.
    http://p.sf.net/sfu/splunk-d2d-c2
    _______________________________________________
    libopendbx-devel mailing list
    lib...@li...
    https://lists.sourceforge.net/lists/listinfo/libopendbx-devel
    http://www.linuxnetworks.de/doc/index.php/OpenDBX




  -- 
  Mariano
  http://marianopeck.wordpress.com




------------------------------------------------------------------------------


  ------------------------------------------------------------------------------
  All of the data generated in your IT infrastructure is seriously valuable.
  Why? It contains a definitive record of application performance, security 
  threats, fraudulent activity, and more. Splunk takes this data and makes 
  sense of it. IT sense. And common sense.
  http://p.sf.net/sfu/splunk-d2d-c2


------------------------------------------------------------------------------

Re: [opendbx] slow insert with sqlite

[Mariano M. P. <mar...@gm...>]

On Tue, Jul 5, 2011 at 11:11 PM, Alain Rastoul <al...@fr...> wrote:

> Hi Norbert,
> Yes they are problems with multi threading and sqlite, my question was
> about
> the opendbx implementation and/or the opendbx interface in Pharo. In Pharo
> there is only one vm thread,


yes


> but I thought that may be opendbx or the
> opendbx interface was doing the call in another thread  and the vm thread
> was waiting for completion?
>

yes, exactly. But only if the backend and database client library support
that.


> Are they documents about the opendbx architecture and or the opendbx
> interface in Pharo (couldn't find on the web site).
>

Check the links I've sent you and the emails I have forwarded to squeakdbx
mailing list.
After you have read Hernik mails I can point you to the code if you want ;)


> Thank you for your answer
>
> Regards
> Alain
>
> "Norbert Sendetzky" <no...@li...> a
> écrit dans le message de news:4E1...@li......
> > Hi Alain
> >
> >> I've done a small program in Pharo 1.3 with glorp+opendbx that insert
> >> 1000
> >> rows in a customer table in a sqlite db.
> >> The 1000 insert takes 140 sec (very slow), but the Pharo profiler says
> >> that
> >> it spend 95% of the time waiting for input.
> >
> > I can't speak for the Pharo implementation but SQLite is known for being
> > problematic when at least two threads trying to write to the same
> > database file.
> >
> >
> > Norbert
> >
> >
> ------------------------------------------------------------------------------
> > All of the data generated in your IT infrastructure is seriously
> valuable.
> > Why? It contains a definitive record of application performance, security
> > threats, fraudulent activity, and more. Splunk takes this data and makes
> > sense of it. IT sense. And common sense.
> > http://p.sf.net/sfu/splunk-d2d-c2
>
>
>
>
> ------------------------------------------------------------------------------
> All of the data generated in your IT infrastructure is seriously valuable.
> Why? It contains a definitive record of application performance, security
> threats, fraudulent activity, and more. Splunk takes this data and makes
> sense of it. IT sense. And common sense.
> http://p.sf.net/sfu/splunk-d2d-c2
> _______________________________________________
> libopendbx-devel mailing list
> lib...@li...
> https://lists.sourceforge.net/lists/listinfo/libopendbx-devel
> http://www.linuxnetworks.de/doc/index.php/OpenDBX
>



-- 
Mariano
http://marianopeck.wordpress.com

Re: [opendbx] slow insert with sqlite

[Alain R. <al...@fr...>]

Hi Norbert,
Yes they are problems with multi threading and sqlite, my question was about 
the opendbx implementation and/or the opendbx interface in Pharo. In Pharo 
there is only one vm thread, but I thought that may be opendbx or the 
opendbx interface was doing the call in another thread  and the vm thread 
was waiting for completion?
Are they documents about the opendbx architecture and or the opendbx 
interface in Pharo (couldn't find on the web site).
Thank you for your answer

Regards
Alain

"Norbert Sendetzky" <no...@li...> a 
écrit dans le message de news:4E1...@li......
> Hi Alain
>
>> I've done a small program in Pharo 1.3 with glorp+opendbx that insert 
>> 1000
>> rows in a customer table in a sqlite db.
>> The 1000 insert takes 140 sec (very slow), but the Pharo profiler says 
>> that
>> it spend 95% of the time waiting for input.
>
> I can't speak for the Pharo implementation but SQLite is known for being
> problematic when at least two threads trying to write to the same
> database file.
>
>
> Norbert
>
> ------------------------------------------------------------------------------
> All of the data generated in your IT infrastructure is seriously valuable.
> Why? It contains a definitive record of application performance, security
> threats, fraudulent activity, and more. Splunk takes this data and makes
> sense of it. IT sense. And common sense.
> http://p.sf.net/sfu/splunk-d2d-c2 

Re: [opendbx] slow insert with sqlite

[Mariano M. P. <mar...@gm...>]

On Tue, Jul 5, 2011 at 10:50 PM, Alain Rastoul <al...@fr...> wrote:

> Hi,
> (sorry for sending this mail again, my pc was off for a long time and the
> message was dated from 2007, people who sort their messages would not see
> it)
>
> I've done a small program in Pharo 1.3 with glorp+opendbx that insert 1000
> rows in a customer table in a sqlite db.
> The 1000 insert takes 140 sec (very slow), but the Pharo profiler says that
> it spend 95%
> of the time waiting for input.
> (in InputEventPollingFetcher>> waitForInput)
> I was wondering if the queries are executed in another thread than the vm
> thread ?
>

Hi Alain. No. Squeak/Pharo's thread architecture is the so called green
thread, that is, only ONE OS thread is used. Internally, the language
reifies Process, Scheduler, #fork: , etc etc etc. But from the OS point of
view there is only one thread for the VM. So.....the regular FFI blocks the
VM. What does it mean? that while the C function called by FFI is being
executed, the WHOLE VM is block. Notihgn can happen at the same time.
Imagine the function that retrieves the results and needs to wait for
them.....TERRIBLE. So...if the backend does not support async quieries, then
you are screw and dbx may be slow in Pharo. Nothing to do.

However, some backends support async queries, and opendbx let us configure
this. This is explained in:
http://www.squeakdbx.org/Architecture%20and%20desing?_s=FlIhkPQOOFSlqf8C&_k=j-3_7Kw_&_n&18
where it says "External call implementation"

You can see the list of backends that support async queries in here:
http://www.squeakdbx.org/documentation/Asynchronous%20queries?_s=FlIhkPQOOFSlqf8C&_k=j-3_7Kw_&_n&17

Notice that there is some room for improvements, but we didn't have time so
far. Hernik told us some good ideas. But since we didn't need more power so
far we couldn't find time to integrate his ideas. I am forwarding now the
emails to the mailing list. If you can take a look and provide code, it
would be awesome. Basically, it improves how and how much we wait in each
side: image and opendbx.

Finally, notice that Eliot is working in a multithreared FFI for Cog, but it
is not yet available as far as I know.

Cheers

Mariano

(I thought I've seen a document about opendbx architecture but could'nt find
> it on the site).
>
> TIA
> Alain
>
>
>
>
> ------------------------------------------------------------------------------
> All of the data generated in your IT infrastructure is seriously valuable.
> Why? It contains a definitive record of application performance, security
> threats, fraudulent activity, and more. Splunk takes this data and makes
> sense of it. IT sense. And common sense.
> http://p.sf.net/sfu/splunk-d2d-c2
> _______________________________________________
> libopendbx-devel mailing list
> lib...@li...
> https://lists.sourceforge.net/lists/listinfo/libopendbx-devel
> http://www.linuxnetworks.de/doc/index.php/OpenDBX
>



-- 
Mariano
http://marianopeck.wordpress.com

Re: [opendbx] slow insert with sqlite

[Norbert S. <no...@li...>]

Hi Alain

> I've done a small program in Pharo 1.3 with glorp+opendbx that insert 1000
> rows in a customer table in a sqlite db.
> The 1000 insert takes 140 sec (very slow), but the Pharo profiler says that
> it spend 95% of the time waiting for input.

I can't speak for the Pharo implementation but SQLite is known for being 
problematic when at least two threads trying to write to the same 
database file.


Norbert

[opendbx] slow insert with sqlite

[Alain R. <al...@fr...>]

Hi,
(sorry for sending this mail again, my pc was off for a long time and the 
message was dated from 2007, people who sort their messages would not see 
it)

I've done a small program in Pharo 1.3 with glorp+opendbx that insert 1000
rows in a customer table in a sqlite db.
The 1000 insert takes 140 sec (very slow), but the Pharo profiler says that 
it spend 95%
of the time waiting for input.
(in InputEventPollingFetcher>> waitForInput)
I was wondering if the queries are executed in another thread than the vm
thread ?
(I thought I've seen a document about opendbx architecture but could'nt find
it on the site).

TIA
Alain

[opendbx] slow insert with sqlite

[gmane.news <al...@fr...>]

Hi
I've done a small program in Pharo 1.3 with glorp+opendbx that insert 1000 
rows in a customer table in a sqlite db.
The 1000 insert takes 140 sec, but the Pharo profiler says that it spend 95% 
of the time waiting for input.
(in InputEventPollingFetcher>> waitForInput)
I was wondering if the queries are executed in another thread than the vm 
thread ?
(I thought I've seen a document about opendbx architecture but could'nt find 
it on the site)
TIA
Alain 

Re: [opendbx] Add patch of backend ( PGSQL) for support BYTES TYPE(odbx_escapt)

[Zhao T. <zha...@gm...>]

the pre patch at pgsql_odbx_get_option lose break.sorry

2011/7/5 Zhao Tongyi <zha...@gm...>:
> I need to insert the "BYTEA" type data into the PGSQL database, but
> found OPENDBX on pgsql_odbx_escapt function call the PGEscaptSring, so
> I added this patch at opendbx-1.4.5, use odbx_set_option set using
> PGEscaptString or PGEscaptBytea.
> In addition to the Opendbx project not update at long time .
>
> --
> Best regards,
>
> Tongyi ,Zhao
>



-- 
Best regards,

Tongyi ,Zhao

[opendbx] Add patch of backend ( PGSQL) for support BYTES TYPE(odbx_escapt)

[Zhao T. <zha...@gm...>]

I need to insert the "BYTEA" type data into the PGSQL database, but
found OPENDBX on pgsql_odbx_escapt function call the PGEscaptSring, so
I added this patch at opendbx-1.4.5, use odbx_set_option set using
PGEscaptString or PGEscaptBytea.
In addition to the Opendbx project not update at long time .

-- 
Best regards,

Tongyi ,Zhao

Re: [opendbx] Future plans again

[Norbert S. <no...@li...>]

Hi Thibault

> One or more connections? Can you point a backend that does this?
> (pgsql seems to keep only one alive)

Every native database library supports more than one connection. What is
not supported are several simultaneous queries resp. result sets per
connection.

> To allocate on stack and leave the mallocs to the client library, I
> would see something like: struct odbx_conn { /* First comes the
> common storage. */ int initialized; int backend_type;
>
> /* Then comes the storage that is specific to each backend. */ void
> *generic; }; I realized (sigh) that there is no other choice but to
> have this void* pointer. At initialization for pgsql it would give
> generic = (pgconn*)PQconnectdbParams(...). The malloc is left to the
> client library. In the client program it would look like: struct
> odbx_conn conn; odbx_init(&conn, ...);

OK, but you don't gain much because this is only one malloc you will
save. One more is needed for the auxiliary data and I think several are
done by the native client libraries. The reason I opted against this was
the choices you have to care about when you a) get an already
initialized structure and b) get a structure that only seem to be
initialized due to random data.

> Good point. After careful thought however I still have two points to
> suggest: _ The idea to merge all backends was more a usability
> objection: it seems excessive to have to manually install each
> package, that will weigh a few ko. A 500ko package is acceptable.
> Technically, I believe (hope?) it is still easily feasible, by
> dynamically loading the client libraries for example (with dlopen and
> such functions). _ To avoid exposing the functions pointers in
> odbx_t, you could keep them is static internal arrays. You would then
> store a backend_type variable in odbx_t, that would be used to index
> the arrays of function pointers.

Well you have to know the structure of odbx_t but it's not exposed
anywhere. Nevertheless, the more details are hidden, the better for
avoiding mistakes.

>> The prepared statement API is on the todo list but I haven't had
>> time to implement this up to now.
>
> Actually, it is not only on prepared statements. I know that sending
> the values separately can be tricky for the motto of odbx. Indeed,
> the client will either have to provide one statement for each backend
> (INSERT ... VALUES ($1,$2,$3) for pgsql), or a preprocessing will
> have to be added. I still argue in favor of sending the values only
> separately, since it is secure by design: the simplest solution is
> the most secure. I will not insist on manual escaping being insecure
> by design ^^.

Many native database libraries support prepared statements. It's
relatively simple:
- Send the SQL with markers to the server and get back a handle
- bind your values to this handle
- Send only the data to the server (one or several times)
- Get the binary values from the result set

Contrary to e.g. DBI I would favour a single function that copies the
data to a memory area provided by the client. This can be used with all
types of data and is faster and safer than allocating memory that must
be freed by the client.

> I know this would seem teasing, but can you point a backend as
> example? I would like to imagine a proper design but have still lack
> of knowledge about the variety of implementations.

mssql, odbc, oracle, sybase ... they all use several functions for
initialization before binding to the database.

> I was not clear enough on this question. I consider that: _
> unbinding/binding to a different database is expected to be uncommon,
> most of the time users will want to connect to a single database. _
> reinit a whole connection is also uncommon, because in case of error
> odbx could try to reconnect internally. The two cases where you
> reinit would be if odbx returned error and you waited a few minutes
> to reconnect, or if you want to change the host.

It's rather common. If you get a fatal error you have to rebind and
it saves much time when using the named databases if you don't have to
reinitialize the whole data structures again.

> OK. Will you use positive error codes to return values?

No, as this would interfere with the positive return values for e.g.
odbx_result().

> One more thing, maybe the result handle should not be separated from
> the odbx_t handle in function calls. This is not a technical
> discussion, but users might think that they can free the connection
> and still use the result handle (Is it actually possible??).

The initial design decision was to allow having multiple result sets per 
connection even if most databases doesn't support this. But I didn't 
want to limit the client libraries that would be able to so even if none 
of the currently implemented ones can do so :-)


Norbert

Re: [opendbx] Future plans again

[Thibault R. <tr...@kt...>]

> The reason why the memory for odbx_t is returned via odbx_t** is because
> it has to be allocated dynamically anyway (there can be one or more
> connections) and it's not returned by the function to be able to return
> an error code in case anything goes wrong. I'm not sure how you would
> like to do this on the stack.

One or more connections? Can you point a backend that does this? (pgsql seems to keep only one alive)
I completely agree for the return code.
To allocate on stack and leave the mallocs to the client library, I would see something like:
struct odbx_conn {
    /* First comes the common storage. */
    int initialized;
    int backend_type;

    /* Then comes the storage that is specific to each backend. */
    void *generic;
};
I realized (sigh) that there is no other choice but to have this void* pointer.
At initialization for pgsql it would give generic = (pgconn*)PQconnectdbParams(...). The malloc is left to the client library.
In the client program it would look like:
struct odbx_conn conn;
odbx_init(&conn, ...);

> These points are all based on your assumption that everybody only needs
> a single .so file containing all "backends". Unfortunately, this isn't
> true. The reason for separating the core library from the backends is
> that they can be installed separately. Think about the Linux
> distributions building .rpm or .deb files: There are dependencies
> between the "backends" and the native database library. If you install
> the OpenDBX library containing all backends as single library, the
> distributions must install all native database libraries as well to
> satisfy the dependencies. This is nothing the user or the packager would
> like to to. The SINGLELIB option is mainly for embedded systems where
> the requirements are exactly known and the memory/space is limited.

Good point. After careful thought however I still have two points to suggest:
_ The idea to merge all backends was more a usability objection: it seems excessive to have to manually install each package, that will weigh a few ko. A 500ko package is acceptable.
Technically, I believe (hope?) it is still easily feasible, by dynamically loading the client libraries for example (with dlopen and such functions).
_ To avoid exposing the functions pointers in odbx_t, you could keep them is static internal arrays. You would then store a backend_type variable in odbx_t, that would be used to index the arrays of function pointers.

> The prepared statement API is on the todo list but I
> haven't had time to implement this up to now.

Actually, it is not only on prepared statements. I know that sending the values separately can be tricky for the motto of odbx.
Indeed, the client will either have to provide one statement for each backend (INSERT ... VALUES ($1,$2,$3) for pgsql), or a preprocessing will have to be added.
I still argue in favor of sending the values only separately, since it is secure by design: the simplest solution is the most secure.
I will not insist on manual escaping being insecure by design ^^.

> Different native database libraries have different requirements. What
> you say is true for PostgreSQL, but not for all other DBMS libraries.
> Furthermore, separating init() from bind() allows us to specify options
> in a generic way and they are still available for rebinding.

I know this would seem teasing, but can you point a backend as example? I would like to imagine a proper design but have still lack of knowledge about the variety of implementations.
I was not clear enough on this question. I consider that:
_ unbinding/binding to a different database is expected to be uncommon, most of the time users will want to connect to a single database.
_ reinit a whole connection is also uncommon, because in case of error odbx could try to reconnect internally. The two cases where you reinit would be if odbx returned error and you waited a few minutes to reconnect, or if you want to change the host.

> If one should ever become obsolete, it has to stay until the next major
> release because otherwise we would break the ABI.

OK. Will you use positive error codes to return values?

One more thing, maybe the result handle should not be separated from the odbx_t handle in function calls. This is not a technical discussion, but users might think that they can free the connection and still use the result handle (Is it actually possible??).

Thank you for your quick answers!

Regards,
Thibault

Re: [opendbx] Future plans again

[Norbert S. <no...@li...>]

Hi Thibault

> _ The use of malloc may be prevented, by requesting a odbx_t* instead
> than odbx_t**. The allocation on the stack will thus reduce processor
> cache misses.

The reason why the memory for odbx_t is returned via odbx_t** is because 
it has to be allocated dynamically anyway (there can be one or more 
connections) and it's not returned by the function to be able to return 
an error code in case anything goes wrong. I'm not sure how you would 
like to do this on the stack.

> _ Putting the addresses of backend functions in a structure is risky
> if someone manages to overwrite it. Maybe just statically switching.
>
> _ Compiling the backends separately is a bit excessive, as they are
> not strictly backends since they use the client functions (seeing a
> backends folder I thought the pgsql plugin would talk the pgsql
> protocol ^^). To me the singlelib should be the only possibility
> (allowing the static switching from above).
>
> _ The use of void* fields is like unions, unrecommended. At the
> expense of a little more memory (storing all pgconn*, MYSQL*, ...
> handles side by side), you get static type checking.

These points are all based on your assumption that everybody only needs 
a single .so file containing all "backends". Unfortunately, this isn't 
true. The reason for separating the core library from the backends is 
that they can be installed separately. Think about the Linux 
distributions building .rpm or .deb files: There are dependencies 
between the "backends" and the native database library. If you install 
the OpenDBX library containing all backends as single library, the 
distributions must install all native database libraries as well to 
satisfy the dependencies. This is nothing the user or the packager would 
like to to. The SINGLELIB option is mainly for embedded systems where 
the requirements are exactly known and the memory/space is limited.

> _ There is no possibility to send the values separately from the
> statement (as with PQexecParams). I would argue it to be the only way
> to send values (and no explicit escaping).
>
> _ Sending values other than strings (just int, double and struct tm),
> and getting them also, like in dbi_result_get_fields from libdbi (but
> with less format bloat ^^).

Jep, you are right. The prepared statement API is on the todo list but I 
haven't had time to implement this up to now.

> _ Why having two functions (init+bind) to connect? With pgsql it
> requires to store host and port strings. With a unique function the
> options could still be specified : if before connection, they will
> try to be matched when connecting, if after connection, the backend
> will try to update them. If these two functions are here to speed
> pooling, I think this feature will be rarely used in practice.

Different native database libraries have different requirements. What 
you say is true for PostgreSQL, but not for all other DBMS libraries. 
Furthermore, separating init() from bind() allows us to specify options 
in a generic way and they are still available for rebinding.

> _ Casting out the majority of preprocessing instructions (see also
> debugging below).
>
> _ Use of NDEBUG, defined in assert.h, instead of ENABLE_DEBUGLOG,
> while logging in stderr and redirecting it to a file: #ifndef NDEBUG
> #define debug_printf(...) fprintf(stderr, __VA_ARGS__) #else #define
> debug_printf(...) ((void) 0) #endif freopen("log.txt", "w", stderr);

Good point.

> _ I like the current error codes - if negative you would have to
> manually set them, and to reorder if one becomes obsolete.

If one should ever become obsolete, it has to stay until the next major 
release because otherwise we would break the ABI.

> _ Sometimes I see a chain of ->  operators. In x86 each generates a
> LEA instruction, so I would argue to minimize their use as possible.

The OpenDBX library checks if the used pointer isn't NULL before 
dereferencing it in order to prevent segfaults ... something you see 
rather often in the MySQL library if you make a mistake ;-)

 > BTW your code was really clean and easy to read!

Thanks a lot :-)


Norbert

Re: [opendbx] Future plans again

[Thibault R. <tr...@kt...>]

> Contributions are always welcome :-)
Great! I come from the pgsql world so my observations are on the main code + pgsql backend.
> Could you give me an overview of what you think what should be changed
> or improved?
All my remarks are questions rather than affirmations:
_ The use of malloc may be prevented, by requesting a odbx_t* instead than odbx_t**. The allocation on the stack will thus reduce processor cache misses.
_ Putting the addresses of backend functions in a structure is risky if someone manages to overwrite it. Maybe just statically switching.
_ Compiling the backends separately is a bit excessive, as they are not strictly backends since they use the client functions (seeing a backends folder I thought the pgsql plugin would talk the pgsql protocol ^^). To me the singlelib should be the only possibility (allowing the static switching from above).
_ There is no possibility to send the values separately from the statement (as with PQexecParams). I would argue it to be the only way to send values (and no explicit escaping).
_ Sending values other than strings (just int, double and struct tm), and getting them also, like in dbi_result_get_fields from libdbi (but with less format bloat ^^).
_ Why having two functions (init+bind) to connect? With pgsql it requires to store host and port strings. With a unique function the options could still be specified : if before connection, they will try to be matched when connecting, if after connection, the backend will try to update them. If these two functions are here to speed pooling, I think this feature will be rarely used in practice.
_ Casting out the majority of preprocessing instructions (see also debugging below).
_ Use of NDEBUG, defined in assert.h, instead of ENABLE_DEBUGLOG, while logging in stderr and redirecting it to a file:
#ifndef NDEBUG
#define debug_printf(...) fprintf(stderr, __VA_ARGS__)
#else
#define debug_printf(...) ((void) 0)
#endif
freopen("log.txt", "w", stderr);
_ I like the current error codes - if negative you would have to manually set them, and to reorder if one becomes obsolete.
_ The use of void* fields is like unions, unrecommended. At the expense of a little more memory (storing all pgconn*, MYSQL*, ... handles side by side), you get static type checking.
_ Sometimes I see a chain of -> operators. In x86 each generates a LEA instruction, so I would argue to minimize their use as possible.

That's all, I hope no point was unclear. BTW your code was really clean and easy to read!

Best regards,
Thibault

------------------------------------------------------------------------------
All of the data generated in your IT infrastructure is seriously valuable.
Why? It contains a definitive record of application performance, security
threats, fraudulent activity, and more. Splunk takes this data and makes
sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-d2d-c2
_______________________________________________
libopendbx-devel mailing list
lib...@li...
https://lists.sourceforge.net/lists/listinfo/libopendbx-devel
http://www.linuxnetworks.de/doc/index.php/OpenDBX

Re: [opendbx] Future plans again

[Norbert S. <no...@li...>]

Hi Thibault

> Working on a CGI website, I have been looking among many C libraries to talk to databases. So far OpenDBX has been the most attractive to me : simple, fast and not reluctant to cast out any feature that would bloat the code.
> However, after diving into the source I listed a number of coding quirks that limit its performance.
> I would like to contribute, but on the design and structure of a v2 rather than new backend/prepared statements.

Contributions are always welcome :-)

Could you give me an overview of what you think what should be changed 
or improved?


Norbert

[opendbx] Future plans again

[Thibault R. <tr...@kt...>]

Hello there,

Working on a CGI website, I have been looking among many C libraries to talk to databases. So far OpenDBX has been the most attractive to me : simple, fast and not reluctant to cast out any feature that would bloat the code.
However, after diving into the source I listed a number of coding quirks that limit its performance.
I would like to contribute, but on the design and structure of a v2 rather than new backend/prepared statements.

Best regards,
Thibault

Re: [opendbx] More fun with postgresql

[Murray S. K. <ms...@cl...>]

> -----Original Message-----
> From: Norbert Sendetzky [mailto:no...@li...]
> Sent: Thursday, June 16, 2011 1:47 PM
> To: OpenDBX devel list
> Subject: Re: [opendbx] More fun with postgresql
> 
> Hi Murray
> 
> libpq seems to be a source of eternal fun ;-)

Indeed.  I don't think their handling of the last report I gave them was all that great so I'm fence-sitting about mentioning this one.

My current workaround is simply to short-circuit a query containing high-ASCII, reporting it as "not found" back to the application before it ever tries talking to libpq.  It would be nice to get this addressed though.

> The last odbx_result_finish() should not be called because no result
> handle is returned and in the worst case a duplicate free() could
> happen.

I have it protected by a check for a NULL result, so we're safe there.

> For a test you can try to call odbx_result() again after the error
> occured. Maybe a second call to PQgetResult() may help. If yes, we may
> be able to change the pgsql_odbx_result() for this special case.

That'll be a pain to get into my current code, but I can hack it in as a test and at least find out if your theory is sound.  I'll report back when I have done so.

-MSK

Re: [opendbx] Bug with Sqlite3, UNION and NULL?

[Mariano M. P. <mar...@gm...>]

Thanks Norbert for the answer. So we will see what we can do.
Best regards,

On Thu, Jun 16, 2011 at 10:58 PM, Norbert Sendetzky <
no...@li...> wrote:

> Hi Mariano
>
> > That fails with something...we have tried to isolate the problem to the
> > minimum. To reproduce do these steps:
> >
> > 'Create table test (c1 int NULL, c2 int NULL)'.
> >   'insert into test values(1, NULL)'.
> > 'insert into test values(NULL, 3)'.
> > 'select * from (select t1.c1, null t3 from test t1 UNION ALL select NULL
> t3,
> > t2.c2 from test t2)'.
> >
> > Then after, fetch all rows. Then, if you take the last row (number 4),
> which
> > has (NULL, '3'), and you try to ask the type of position 2  you get an
> > UKNOWN instead of type for the integer.
> >
> > Is this a bug? if true, where? OpenDBX? slite3 library? sqlite3 database?
>
> Not a bug, it's a feature ;-)
>
> SQLite uses an own datatype for NULL values which is not known in SQL
> standard (http://sqlite.org/c3ref/c_blob.html). The problem is that
> SQLite changes the datatype of the column in the result set from INT to
> NULL if the value is NULL in the current row. There's no known
> workaround for this.
>
>
> Norbert
>
>
> ------------------------------------------------------------------------------
> EditLive Enterprise is the world's most technically advanced content
> authoring tool. Experience the power of Track Changes, Inline Image
> Editing and ensure content is compliant with Accessibility Checking.
> http://p.sf.net/sfu/ephox-dev2dev
> _______________________________________________
> libopendbx-devel mailing list
> lib...@li...
> https://lists.sourceforge.net/lists/listinfo/libopendbx-devel
> http://www.linuxnetworks.de/doc/index.php/OpenDBX
>



-- 
Mariano
http://marianopeck.wordpress.com

Re: [opendbx] Bug with Sqlite3, UNION and NULL?

[Norbert S. <no...@li...>]

Hi Mariano

> That fails with something...we have tried to isolate the problem to the
> minimum. To reproduce do these steps:
>
> 'Create table test (c1 int NULL, c2 int NULL)'.
>   'insert into test values(1, NULL)'.
> 'insert into test values(NULL, 3)'.
> 'select * from (select t1.c1, null t3 from test t1 UNION ALL select NULL t3,
> t2.c2 from test t2)'.
>
> Then after, fetch all rows. Then, if you take the last row (number 4), which
> has (NULL, '3'), and you try to ask the type of position 2  you get an
> UKNOWN instead of type for the integer.
>
> Is this a bug? if true, where? OpenDBX? slite3 library? sqlite3 database?

Not a bug, it's a feature ;-)

SQLite uses an own datatype for NULL values which is not known in SQL 
standard (http://sqlite.org/c3ref/c_blob.html). The problem is that 
SQLite changes the datatype of the column in the result set from INT to 
NULL if the value is NULL in the current row. There's no known 
workaround for this.


Norbert

Re: [opendbx] More fun with postgresql

[Norbert S. <no...@li...>]

Hi Murray

libpq seems to be a source of eternal fun ;-)

> When odbx_result() fails in this scenario, it gives an error code
> without giving me a result handle (i.e., the returned odbx_result_t
> is NULL).  That prevents me from calling odbx_result_finish() in a
> useful way, which in turn leaves the underlying database handle in an
> unusable state.  This appears to be unrecoverable.

After having a look at backends/pgsql/pgsql_basic.c, it must be the 
result of PQresultStatus() (either PGRES_NONFATAL_ERROR or 
PGRES_BAD_RESPONSE) which is not one of the named ones (line 416). 
Afterwards, a cleanup is done but PQclear() doesn't seem to do its work.

The documentation doesn't state that there is another command than 
PQclear() to cleanup the result sets if an error occurs. Maybe it's a 
bug in libpq.

The PQ calls in your case are:
- PQsendQuery()
- PQgetResult()
- PQresultStatus()
- PQclear()
- PQsendQuery()
-> already in progress

> The sequence of calls is:
>
> odbx_escape(), returns successfully
> odbx_query(), returns successfully
> odbx_result(), fails
> odbx_error_type() returns 1 (non-fatal)
> odbx_result_finish()
>
> Then later, with the same odbx_t handle, odbx_query() still fails,
> reporting "another command is already in progress".  Is calling
> odbx_result_finish() here not enough?

The last odbx_result_finish() should not be called because no result 
handle is returned and in the worst case a duplicate free() could happen.

For a test you can try to call odbx_result() again after the error 
occured. Maybe a second call to PQgetResult() may help. If yes, we may 
be able to change the pgsql_odbx_result() for this special case.


Norbert

Re: [opendbx] More fun with postgresql

[Murray S. K. <ms...@cl...>]

Some more detail:

When odbx_result() fails in this scenario, it gives an error code without giving me a result handle (i.e., the returned odbx_result_t is NULL).  That prevents me from calling odbx_result_finish() in a useful way, which in turn leaves the underlying database handle in an unusable state.  This appears to be unrecoverable.

This is with opendbx 1.4.5 on FreeBSD.

-MSK


From: Murray S. Kucherawy [mailto:ms...@cl...]
Sent: Wednesday, June 15, 2011 1:14 PM
To: lib...@li...
Subject: [opendbx] More fun with postgresql

I'm having a problem with an error code returned by postgresql via opendbx.  I think I have my use of the API right, so this seems like it might be another interesting defect in libpq, but I want to check here to make sure.

I'm sending a query that contains some high-bit ASCII characters in it, namely 0xE1 and 0xF3.  Postgresql responds with:


ERROR:  invalid byte sequence for encoding "UTF8": 0xe17469 HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".



The sequence of calls is:



odbx_escape(), returns successfully

odbx_query(), returns successfully

odbx_result(), fails

odbx_error_type() returns 1 (non-fatal)

odbx_result_finish()



Then later, with the same odbx_t handle, odbx_query() still fails, reporting "another command is already in progress".  Is calling odbx_result_finish() here not enough?



-MSK

[opendbx] More fun with postgresql

[Murray S. K. <ms...@cl...>]

I'm having a problem with an error code returned by postgresql via opendbx.  I think I have my use of the API right, so this seems like it might be another interesting defect in libpq, but I want to check here to make sure.

I'm sending a query that contains some high-bit ASCII characters in it, namely 0xE1 and 0xF3.  Postgresql responds with:


ERROR:  invalid byte sequence for encoding "UTF8": 0xe17469 HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".



The sequence of calls is:



odbx_escape(), returns successfully

odbx_query(), returns successfully

odbx_result(), fails

odbx_error_type() returns 1 (non-fatal)

odbx_result_finish()



Then later, with the same odbx_t handle, odbx_query() still fails, reporting "another command is already in progress".  Is calling odbx_result_finish() here not enough?



-MSK

[opendbx] Bug with Sqlite3, UNION and NULL?

[Mariano M. P. <mar...@gm...>]

Hi Norbert. So long we don't have problems :)   We were doing some ORM work
on top of SqueakDBX and the ORM is trowing a query like this:

SELECT * FROM
    (SELECT t2.ID, t2.NAME, t2.SERIAL_NUMBER, NULL AS t4nullField1, NULL AS
t4nullField2, NULL AS t4nullField3, NULL AS t5nullField1, NULL AS
t5nullField2, NULL AS t5nullField3, NULL AS t5nullField4
 FROM NONPERISHABLE_ITEM t2, WAREHOUSE_ITEM_LINK t3
 WHERE t2.ID = t3.ITEM_ID AND t3.WAREHOUSE_ID = 1
UNION ALL
    SELECT NULL AS t4nullField1, NULL AS t4nullField2, NULL AS t4nullField3,
t2.ID, t2.NAME, t2.AGE, NULL AS t5nullField1, NULL AS t5nullField2, NULL AS
t5nullField3, NULL AS t5nullField4
 FROM PERISHABLE_ITEM t2, WAREHOUSE_ITEM_LINK t3
 WHERE t2.ID = t3.ITEM_ID AND t3.WAREHOUSE_ID = 1

UNION ALL
    SELECT NULL AS t4nullField1, NULL AS t4nullField2, NULL AS t4nullField3,
NULL AS t5nullField1, NULL AS t5nullField2, NULL AS t5nullField3, t2.ID,
t2.NAME, t2.SERIAL_NUMBER, t2.ASSEM_COST
 FROM UNASSEMBLED_ITEM t2, WAREHOUSE_ITEM_LINK t3
 WHERE t2.ID = t3.ITEM_ID AND t3.WAREHOUSE_ID = 1) t2


That fails with something...we have tried to isolate the problem to the
minimum. To reproduce do these steps:


'Create table test (c1 int NULL, c2 int NULL)'.
 'insert into test values(1, NULL)'.
'insert into test values(NULL, 3)'.
'select * from (select t1.c1, null t3 from test t1 UNION ALL select NULL t3,
t2.c2 from test t2)'.

Then after, fetch all rows. Then, if you take the last row (number 4), which
has (NULL, '3'), and you try to ask the type of position 2  you get an
UKNOWN instead of type for the integer.

Is this a bug? if true, where? OpenDBX? slite3 library? sqlite3 database?

This was tested on Linux with Sqlite3 and latest OpenDBX.

Thanks in advance,

-- 
Mariano
http://marianopeck.wordpress.com

Re: [opendbx] Mssql connection failed

[Norbert S. <no...@li...>]

On 05/13/2011 12:19 PM, Diogenes Moreira wrote:
> Have you analized access via ADO in the windows platforms??

No, I haven't.


Norbert

Re: [opendbx] Mssql connection failed

[Diogenes M. <dio...@gm...>]

Have you analized access via ADO in the windows platforms??

Best.
D

On Fri, May 13, 2011 at 4:08 AM, Norbert Sendetzky <no...@li...
> wrote:

> Hi all
>
> > I'm not a fan of ODBC, but with Windows and MSSQL at least it rocks
> (avoid
> > any ODBC specific syntax commands), on the other side having access to
> SQL
> > Server from Unix is really a plus and I would'nt dismiss FREETDS on the
> > contrary - I don't know about ODBC on Linux.
>
> Just some details about ODBC and FreeTDS:
>
> On Unix plattforms, there's no native ODBC driver for MS SQL Server
> available. Instead, the FreeTDS client provides connectivity to them
> and the unixODBC/iODBC projects also use the FreeTDS client as base. For
> Windows, Microsoft only provides an ODBC driver because this is the
> native client of their choice.
>
>
> Norbert
>
>
> ------------------------------------------------------------------------------
> Achieve unprecedented app performance and reliability
> What every C/C++ and Fortran developer should know.
> Learn how Intel has extended the reach of its next-generation tools
> to help boost performance applications - inlcuding clusters.
> http://p.sf.net/sfu/intel-dev2devmay
> _______________________________________________
> libopendbx-devel mailing list
> lib...@li...
> https://lists.sourceforge.net/lists/listinfo/libopendbx-devel
> http://www.linuxnetworks.de/doc/index.php/OpenDBX
>

Re: [opendbx] Mssql connection failed

[Norbert S. <no...@li...>]

Hi all

> I'm not a fan of ODBC, but with Windows and MSSQL at least it rocks (avoid
> any ODBC specific syntax commands), on the other side having access to SQL
> Server from Unix is really a plus and I would'nt dismiss FREETDS on the
> contrary - I don't know about ODBC on Linux.

Just some details about ODBC and FreeTDS:

On Unix plattforms, there's no native ODBC driver for MS SQL Server 
available. Instead, the FreeTDS client provides connectivity to them 
and the unixODBC/iODBC projects also use the FreeTDS client as base. For 
Windows, Microsoft only provides an ODBC driver because this is the 
native client of their choice.


Norbert