Zend_DB anyone?

sneakyimp

I'm working on a new project which uses Zend Framework. We're considering using Zend_DB (a natural choice for Zend Framework projects) and I'm reading the following text in the Zend_DB_Table documentation:

MySQL, Microsoft SQL Server, and SQLite are examples of RDBMS brands that support auto-incrementing primary keys.

PostgreSQL has a SERIAL notation that implicitly defines a sequence based on the table and column name, and uses the sequence to generate key values for new rows. IBM DB2 has an IDENTITY notation that works similarly. If you use either of these notations, treat your Zend_Db_Table class as having an auto-incrementing column with respect to declaring the $_sequence member as TRUE

What does that mean exactly? Should I just set $_sequence to TRUE reqardless of whether I'm using MySQL or PostgreSQL or Oracle and forget about it? The wording is quite strange.

Also, I'm wondering if anyone has used it and has any anecdotes about it's pros and cons.

jeepin81

It means nothing.... It's just saying the difference in AI. DB2 vs MySQL.....etc... same concept - different naming.

Nothing more than that.

SystemINetwork might have a more in-depth explanation but I'm pretty certain ur just reading about different naming conventions.

db2 is what i use and its good.

Weedpacket

It means that if the table in question has a primary key column that is automatically populated by a incrementing value then set $_sequence to true.

Different DBMSs use different methods to define such a column. That paragraph just notes that Zend_DB also recognises the (more object-oriented) mechanism used by PostgreSQL and DB2 for the purpose, even though they don't have "autoincrementing columns". (PostgreSQL, for example, has a fake "serial" column type).

For more detail, compare Using a Table with an Auto-Incrementing Key, Using a Table with a Sequence, and Using a Table with a Natural Key.

sneakyimp

Thanks for your response, Weedpacket.

I am familiar with the concept of a sequence as I encountered it in Postgres some time ago. Those links you referenced are precisely the page I was reading when I decided to write this question.

It sounds like if I am using the SERIAL notation in Postgres or the IDENTITY notation in IBM DB2 when I define my tables, then I should just write my code exactly as I would were I using MySQL as my DBMS: namely, when I define my Table classes, I set $_sequence=TRUE.

The lingering question is how I must change my coding style if I am to be truly database-agnostic. What if I'm using Oracle with sequences or something. Seems to me this depends both on my table structures (which will be different in a different DBMS) and on my code. Maybe two options:
option 1: natural keys
Write extra code to manually determine and specify the correct primary key for this database table. This likely to require a lot of SELECT MAX(id) FROM table_x queries in the code.
option2: sequences
This might work a little better because MySQL will ignore sequence arguments to the Zend_Db::lastInsertId method. On the other hand it might not.

Weedpacket

I think your lingering question is precisely the one that the mechanism is designed to address (judging from the framework source code, Oracle sequences are supported).

If you're going to be truly database-agnostic, then how would you deal with, say, an HBase or Bigtable db? If you don't have the option of doing the coding needed to extend the framework in the required direction, then at some point you're going to have to draw the line. Like I did once when I discovered that the user database I was supposed to integrate with consisted of a stack of exercise books and post-it notes; I kid thee not.

sneakyimp

Weedpacket;10981801 wrote:
I think your lingering question is precisely the one that the mechanism is designed to address (judging from the framework source code, Oracle sequences are supported).

It's good to hear from you again Weedpacket. I often wonder what it must be like to live with the clarity of perception that you have.

As for the mechanism, I think you are correct that it is intended to facilitate db-agnosticism and yet I'm reminded of a link from laserlight about leaky abstractions. I had sort of an epiphany on reading your post that I simply don't have enough time available on this project to concern myself with this sort of thing.

Weedpacket;10981801 wrote:
If you're going to be truly database-agnostic, then how would you deal with, say, an HBase or Bigtable db? If you don't have the option of doing the coding needed to extend the framework in the required direction, then at some point you're going to have to draw the line. Like I did once when I discovered that the user database I was supposed to integrate with consisted of a stack of exercise books and post-it notes; I kid thee not.

Bigtable. sigh. Is it possible to build a site without JOINs?

As for your drawing of the line, surely it was due to budget constraints? Or perhaps you just avoiding the crazy.

Were it only the books, you might be able to skip the INSERT/UPDATE/DELETE code, but SELECT would still be quite a ***** (although possibly still feasible).

Come to think of it, you might even be able to work the post-it notes too.

Weedpacket

I guess I find myself wanting to define a domain-specific language for the task, and then writing an interpreter cum code generator to turn programs written in it into something runnable. Then I remember that such a language already exists, called SQL....