On 11 May 2006, at 9:20 PM, Chris Johnson wrote:

I'm in favor of adding these functions -- but if and only if we also add a database privilege escalation feature for the site admin to use. Many hosts with *better* security provide multiple database users with tiered privileges specifically so that web applications can access the database without full create/drop rights. Widespread use of the above functions will break Drupal on those hosts. (yes, that means my sites :-)

I agree. This would also be useful for the install system. Although a case could be made that that is a separate patch. And i did mention the privilege escalation in my post =) -- Adrian Rossouw Drupal developer and Bryght Guy http://drupal.org | http://bryght.com

On Thu, 11 May 2006 20:31:32 +0200, Adrian Rossouw <adrian@bryght.com> wrote:

...

http://drupal.org/node/63049

Now that we have .install files for modules, I believe it is time we extended our database abstraction layer.

I recommend we create the following extra functions :

1. db_create_table($tablename, $columns); // columns is an associative array with the 'name' => 'type'

Why 'name' => 'type'? I'd think that 'name' => 'foo', 'type' => 'foo', 'default' => 0, and so on would be a beter way to capture all the extra stuff necessary. Chx makes a good point, though, that data column types differing between implementations is one of the ugly bits. It'd be odd to set up our own 'drupal-esque' data types and map them to db specific types in the function. --Jeff

That's what the abstraction layer is there for.

we specify integer, longtext, shortext blah .. and the abstraction layer changes that into whatever the equivalent is for that database type.

ie: varchar() and text in mysql / postgres.

Choose _SENSIBLE_ types, _FOR US_ and then change it into whatever works for each database.

This would give us consistency in how tables are defined.

As I think about it, I believe you're right. --Jeff

Adrian Rossouw wrote:

Perhaps unit tests will help us weed out the dodgy non-standard sql that sometimes gets used, and then we can add more supported db types to our stack? (without breaking our backs supporting them)

I suppose that's one benefit to coding for mysql (the lowest common denominator)

Except, mysql really isn't the lowest common denominator. They have a spate of non-standard SQL extensions that are widely and popularly used -- mostly due to history. It'd be nice to get folks to use more standard, portable SQL so that we could support more db types reliably. What's the method to get there? Unit tests? Dev documentation? An SQL audit before CVS commit? ..chrisxj

Adrian Rossouw wrote:

we specify integer, longtext, shortext blah .. and the abstraction layer changes that into whatever the equivalent is for that database type.

ie: varchar() and text in mysql / postgres.

Choose _SENSIBLE_ types, _FOR US_ and then change it into whatever works for each database.

Exactly! (makes note to self: engage brain before typing next time) ..chrisxj

On 11 May 2006, at 9:36 PM, Karoly Negyesi wrote:

Ah yes. And between 'character variable' of postgres and 'varchar' of mysql, which one ?

? what do you mean ? -- Adrian Rossouw Drupal developer and Bryght Guy http://drupal.org | http://bryght.com

On May 11, 2006, at 3:36 PM, Karoly Negyesi wrote:

On Thu, 11 May 2006 20:31:32 +0200, Adrian Rossouw <adrian@bryght.com> wrote:

...

1. db_create_table($tablename, $columns); // columns is an associative array with the 'name' => 'type'

Ah yes. And between 'character variable' of postgres and 'varchar' of mysql, which one ?

The last time I checked (about two or three days ago?), PostgreSQL accepted column definitions using 'varchar' just fine... According to http://www.postgresql.org/docs/7.2/static/datatype.html varchar is an alias for character varying. Now, there might be problems with blob fields... Ricky

On 11 May 2006, at 11:04 PM, Adrian Rossouw wrote:

...

The last time I checked (about two or three days ago?), PostgreSQL accepted column definitions using 'varchar' just fine...

Except it uses exactly the same storage mechanism as text, except it has an extra constraint check.

For our uses, text is always the correct type.

And text is also not our issue. -- Adrian Rossouw Drupal developer and Bryght Guy http://drupal.org | http://bryght.com

On Thursday 11 May 2006 18:19, Chris Johnson wrote:

Recent version of postgres accept 'varchar' as a synonym for 'character varying' but does mysql accept the latter?  If not, use 'varchar'.

MySQL accepts CHARACTER VARYING just fine. I'm pretty sure that's the official syntax from the SQL standard as well. Scott -- ------------------------------------------------------------------------------- Syscrusher (Scott Courtney) Drupal page: http://drupal.org/user/9184 syscrusher at 4th dot com Home page: http://4th.com/

The're useful when the structure of a table evolves over time -- for example, version 1.45 of module foo adds an index for better performance. YOu can change the original definition, but you'll need these additional functions to write the update_x() function. --Jeff -----Original Message----- From: John Pulles [mailto:john.pulles@gmail.com] Sent: Thursday, May 11, 2006 3:57 PM To: development@drupal.org Subject: Re: [development] Extend database abstraction layer,to include table creation. These functions are a useful addition, but I am not sure if the developer that defines tables for his module should need to use them. An alternative for the install system could be that the structure of the tables is defined in a xml file that can be validated before it is processed by the install system.

ot if it requires module-specific logic to convert data to a new format, or migrate old records to a new schema. Seriously, we just got the _install() and _update_x() hooks in place and they're working really well. The last thing we need is a completely different mechanism for updating being shoved into place, driven by a mess of 'intelligent' code that assumes it knows best. ;) --Jeff -----Original Message----- From: John Pulles [mailto:john.pulles@gmail.com] Sent: Thursday, May 11, 2006 4:17 PM To: development@drupal.org Subject: Re: [development] Extend database abstraction layer,to include table creation. 2006/5/11, Jeff Eaton <jeff@viapositiva.net>: The're useful when the structure of a table evolves over time -- for example, version 1.45 of module foo adds an index for better performance. YOu can change the original definition, but you'll need these additional functions to write the update_x() function. Probably a 'diff' can be made to determine the difference between the current tables (if they are present) and the updated definition. The install/update system would then process the differences.

On Friday 12 May 2006 04:42, Bèr Kessels wrote:

...

db_fetch_all() This "whiling" over the db_fetch_object is driving me nuts :)

Money mouth and that stuff: from helpers.module (still needs testing here before I commit this)

function db_fetch_all_as_objects($query) {

function db_fetch_all_as_arrays($query) {

Related to this: http://drupal.org/node/53488 I'm planning on adding those db utility functions. As per the question I asked in the thread, should I be looking to add those to helper.module rather than core somewhere? I'm just wary of people not even knowing they exist. (Please reply in the issue, too, so that it's easier to track. Thanks.) -- Larry Garfield AIM: LOLG42 larry@garfieldtech.com ICQ: 6817012 "If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it." -- Thomas Jefferson

On 12 May 2006, at 5:03 PM, Earl Miles wrote:

Uggh. Adding extra loops because you don't like 'while' is nothing more than a performance hit that is unnecessary.

Not to mention a memory drain. The only place I could see something like this being useful is everywhere we do : $result = db_query('something'); while ($data = db_fetch_array($result) { $return[$data['some_key']] = $data; } AND NOTHING ELSE. And i don't think 4 (really 3 lines) of code is that much of a schlepp. -- Adrian Rossouw Drupal developer and Bryght Guy http://drupal.org | http://bryght.com

On 12 May 2006, at 7:01 PM, Angela Byron wrote:

Same difference, except you end up looping twice. :P

except for lookups. where you end up doing : print ($data[$key); ie: not loops. -- Adrian Rossouw Drupal developer and Bryght Guy http://drupal.org | http://bryght.com

Moshe Weitzman wrote:

...

The only place I could see something like this being useful is everywhere we do :

$result = db_query('something'); while ($data = db_fetch_array($result) { $return[$data['some_key']] = $data; }

AND NOTHING ELSE.

Of course. That's why Ber gave function name called fetch_all(). You use it when you need it. I can't understand why someone would start making noise on the list about this. You use it if you need it. That's all.

Well, except that people who aren't quite familiar with what's going on will think that it's easier and use it in the wrong place.

moshe weitzman wrote:

Pople might misuse it is not a justification for omitting code from core. Building a collection from a db recordset an extremely common pattern and including this convenience function reduces code and makes it more readable. Do you think that improper instances of this will get into core? No chance. It won't get into any of the well reviewed contrib modules either. So what if people misuse a function in rarely used contrib module? Thats likely the least of its worries.

Agreed. But going back to my point, how do we know what field to set the array key on? Key on the first field selected? Or scrap the idea of hashes and just chuck them into a numeric array, knowing that in some (most?) cases you'll need to loop through it again to make the hash what you want? It would be nice for someone who is in support of a db_fetch_all() function to maybe grep through core/contrib looking for examples of this and come up with a sane default behaviour. -Angie

Op vrijdag 12 mei 2006 22:24, schreef Larry Garfield:

One line to get a key/value set that's already perfectly prepared to drop into a select box.

Woa, I was just building a "select_for_select" function. This sounds soo much like it. (http://api.rubyonrails.com/classes/ActionView/Helpers/FormOptionsHelper.html...) And it makes me think: with all the discussion going on: is the contrib a good place to maintain a helpers.module? To maintain a growing set of library functions for Drupal? Would a distribution, completely separate fron drupal core be a better place? Really, there seems to be a good reason against every single function and parameter of a function within Drupal... and they are very good reasons. For the people outing the reasons... Bèr

On Friday 12 May 2006 15:44, Angela Byron wrote:

Agreed. But going back to my point, how do we know what field to set the array key on? Key on the first field selected?

The standard behavior in other libraries I've seen, such as PEAR, is to have the first selected field become the key. Scott -- ------------------------------------------------------------------------------- Syscrusher (Scott Courtney) Drupal page: http://drupal.org/user/9184 syscrusher at 4th dot com Home page: http://4th.com/

Regarding performance, given that creating tables is such an infrequent thing, and even when done, it only affect a few tables, the performance cost of the new abstraction layer is negligible. I am sort of conflicted on this new abstraction layer thing. I agree that will make things much easier for everyone, and hence has merit. However, unless we go totally SQL-less (i.e. no one can write direct SQL in Drupal modules, think ORM, e.g. Active Record), this will only take part of the issue (database portability) away, and not all of it. People can still write non-portable SQL, although the table creation part was taken care of. So, this may give a false sense of total abstraction, while we do not have it yet. For an alternate view on abstraction (that I do not fully support mind you, I think abstraction has merits), read Jeremy Zawodny's Database Abstraction Layers Must Die. http://jeremy.zawodny.com/blog/archives/002194.html His main points are: 1. With abstraction layers, performance cannot be gained. 2. Changing databases is never easy for a large site.

On 5/14/06, Larry Garfield <larry@garfieldtech.com> wrote:

On Saturday 13 May 2006 12:06, Khalid B wrote:

...

http://jeremy.zawodny.com/blog/archives/002194.html

His main points are:

1. With abstraction layers, performance cannot be gained.

Depends on your performance measurement. Abstraction layers are not about improving system performance; they're about improving developer performance.

We could make Drupal a hundred times faster if we recoded it in hand-tweaked x86 assembler. It would take us a few years to get something even get something marginally useful, but it would be faster because we'd have eliminated a dozen abstraction layers! (PHP, C, SQL, etc.) I'd rather burn a few cycles than a few days or weeks of my time. Hardware costs less than I do.

...

2. Changing databases is never easy for a large site.

Depends on your codebase, and if you planned ahead.

Let me play devil's advocate here ... The problem with an abstraction layer for the database that we are proposing is that : 1. It is not complete, since engine specific SQL statements can be embedded, and hence portability will be lost in this case. There is a false sense of portability that is there. 2. It is a lowest common denominator thing, and hence is dumbed down. 3. When you want to do things in the backend because you do not want to retreive the data in the middle tier (PHP) and incurr a performance penalty, you often have engine specific syntax (think of UNIX_TIME functions for example, date intervals, ...etc.) End Devil's Advocate mode. I am not against the database creation stuff that Adrian proposed. What I see is that in the future we may move to total abstraction. If we realize that we can no longer optimize for a specific engine without losing portability, OR that we allow it at the risk of losing portability, then we should be fine. The problem is where we are under the illusion that we have something (portability and performance) but we are not.

...

If we realize that we can no longer optimize for a specific engine without losing portability,

What's wrong with doing :

db_create_table('blah' // etc.

if ($globals['db_type'] == 'type') { // optimise the table for that db type. }

It's still a lot less code than duplicating every single line of every single schema for every single db type.

When we look at our usage patterns up to now, that ends up being the exception and not the rule.

Nothing wrong with that. And that enforces my point that there is no practical absolute abstraction. If a PostgreSQL guy writes this and does not bother with writing a MySQL part, then that piece of code is now PostgreSQL specific and no longer abstracted. As I said, nothing wrong with that AS LONG AS WE REALIZE that by allowing this, we wander off into engine specific code again ...

On Friday 12 May 2006 15:27, moshe weitzman wrote:

Pople might misuse it is not a justification for omitting code from core. Building a collection from a db recordset an extremely common pattern and including this convenience function reduces code and makes it more readable.

+1 Every time we can turn this: do_a_query() while (not_done_with_rows) { put_another_row_into_array } return rows_array into this: return fetch_all_from_query() we have made some Drupal module smaller, faster to compile, easier to read, and more maintainable. It's not a matter of whether programmers are *able* to use a while() loop -- it's a matter of whether they should have to code the same loop pattern again and again and again. I would go even further, to suggest three other very common usage patterns that should get dedicated functions: 1. Execute a query that will return exactly one row, and return that row into an object. If the query returns multiple rows, return only the first one. Example use case: SELECT * FROM {node} WHERE nid=1234; Suggested function signature: $object = db_query_row($sql [, $params]) 2. Execute a query that will return exactly one column of one row, and return that data value as a simple variable of mixed type. Example use case: SELECT COUNT(*) FROM {node} WHERE type='story'; Suggested function signature: $mixed = db_query_value($sql [, $params]) 3. Execute a query that will return exactly one column of an arbitrary number of rows, and return those values as an integer-subscripted array. Example use case: SELECT DISTINCT type FROM {node} ORDER BY type; Suggested functoin signature: $array = db_query_column($sql [, $params]) Aside from the reduction in code size of all the modules that use the new functions, there is an additional advantage. Right now, there is no way to tell the database layer that one is finished with a query, to free up the result set resources. Since these functions -- as well as the db_fetch_all() function previously discussed by others -- all have *internal* knowledge of when the underlying SQL query is finished, they can transparently release the internal result set resource before they return. The PEAR code, which is GPL, could be easily adapted to provide these functions. I'm willing to volunteer to do it, though it sounds as if there are others who are eager to work on this project. Scott -- ------------------------------------------------------------------------------- Syscrusher (Scott Courtney) Drupal page: http://drupal.org/user/9184 syscrusher at 4th dot com Home page: http://4th.com/

Op vrijdag 12 mei 2006 19:18, schreef Moshe Weitzman:

...

$result = db_query('something'); while ($data = db_fetch_array($result) {     $return[$data['some_key']] = $data; }

If you read the code, you will find that I do nothing more then this. I do not call this "noise". IMO this is all about "shortcuts", If Drupal "forces" us to write: $result = db_query('something'); while ($data = db_fetch_array($result) { $return[$data['some_key']] = $data; } twenty times. Then IMO Drupal Does a very Bad Job. That is all. I presented a solution to an itch that itches me. Nothing more nothing less. Feel free to call it "a memory drain". But also feel free to call it "usefull". And above all:These endless and *extremely* annoying discussions are the one and only reason this is not a patch for core but a helper function in the herlpers module. And yes, please contribute to that. the only thing id like to see is sone "patch history" to make sure we dont end up with a complete crappy helper module. Bèr

On Fri, May 12, 2006 1:28 pm, Chris Johnson said:

Earl Miles wrote:

...

Bèr Kessels wrote:

...

db_fetch_all() This "whiling" over the db_fetch_object is driving me nuts :)

Uggh. Adding extra loops because you don't like 'while' is nothing more than a performance hit that is unnecessary.

Yeah, I'm pretty -1 on the idea of adding these to the database abstraction. If one is coding PHP for Drupal, one should be a competent enough programmer to deal with a while loop over a database fetch.

..chrisxj

It's not a matter of competence, but of convenience. In my non-Drupal work (which currently is far more time than my Drupal work, sadly), I need to get a series of query result rows as objects first, then pass them around, surprisingly often. Same for getting just one column of a result set as an array. Sure, that's 4-5 lines of simple code, but it's the same 5 lines 100 times. If you're doing that several times, that's a clear sign that you should be abstracting it. It makes the code easier to read later, too. --Larry Garfield

On Friday 12 May 2006 17:01, Bèr Kessels wrote:

Op vrijdag 12 mei 2006 21:12, schreef Larry Garfield:

...

If you're doing that several times, that's a clear sign that you should be abstracting it.  It makes the code easier to read later, too.

This is called DNRY Do not repeat yourself.

As soon as you have written a single line of code twice, you have done something wrong! Maybe not now, but certainly along the road.

Bèr

I refer to it as the Asimov Rule: The most unlikely number in the universe is 2. (If you write the same thing a second time, odds are you'll be writing it a 3rd, 4th, and 97th time, so generalize NOW before you go further.) -- Larry Garfield AIM: LOLG42 larry@garfieldtech.com ICQ: 6817012 "If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it." -- Thomas Jefferson

James Walker wrote:

On 12-May-06, at 4:58 AM, Bèr Kessels wrote:

...

db_fetch_all()

+1 .. but use with care, of course... lest you much all your RAM ;)

Totally. Just as well that we don't store files in the database tables! ;) I have this type of wrapper function in a couple of modules that connect to an external db's, although I don't use it 100% of the time it is a nice shortcut to have available.

Adrian Rossouw schrieb:

On 11 May 2006, at 8:31 PM, Adrian Rossouw wrote:

...

http://drupal.org/node/63049

Now that we have .install files for modules, I believe it is time we extended our database abstraction layer.

I recommend we create the following extra functions :

Now the next question is, is anybody interested in working on this. This will help making the installer get in a lot quicker, by reducing the amount of cross db development we need to do.

I just started working on a implentation in database.mysql.inc. Think I'll be able to publish a patch on the day after tomorrow ;) best regards, Franz Heinzmann --- frando@xcite-online.de xcite-online.de

See http://drupal.org/node/63049 for a implentation of the proposed functions. There is some documentation available in the code and I wrote some usage examples in my comment in the issue tracker. best regards, frando --- Franz Heinzmann frando@xcite-online.de Adrian Rossouw schrieb:

On 12 May 2006, at 2:55 PM, Frando wrote:

...

I just started working on a implentation in database.mysql.inc. Think I'll be able to publish a patch on the day after tomorrow ;)

Awesome. Thanks man =).

-- Adrian Rossouw Drupal developer and Bryght Guy http://drupal.org | http://bryght.com

Adrian Rossouw wrote:

On 11 May 2006, at 8:31 PM, Adrian Rossouw wrote:

...

http://drupal.org/node/63049

Now that we have .install files for modules, I believe it is time we extended our database abstraction layer.

I recommend we create the following extra functions :

Now the next question is, is anybody interested in working on this. This will help making the installer get in a lot quicker, by reducing the amount of cross db development we need to do.

I'm interested in working on the database abstraction layer. ..chrisxj

There certainly is a cost for this extra abstraction layer. But the beneits are substantial, and IMO, outweight the costs you've identified. Expanding on my response in this issue (http://drupal.org/node/63049) - automatically gives us pgsql compatibility for most modules. Only those that use non standard sql are broken. - paves a clear path toward fixing the slight security problem we introduced with 4.7. Since the install and update systems regularly create and drop tables, an admin has to give the DB user those permissions. For 4.8 we want the admin to have the option of defining a one $db_url for usual queries and another for database definition queries. Database definition queries will go through the aforementioned functions and at that point we can challenege the user for uid=1 credentials or something else. - specific to the installer, adrian mentions that our three schema files unify to one. also, consider how much code gets thrown out when you take every update in every contrib module and remove the pgsql branch. We will be defining updates only once. So, it might be true that installer should proceed without this. But The benefits are substantial IMO and is a project worth doing IMO. It isn't that hard either. -moshe On 5/12/06 12:00 PM, "Dries Buytaert" <dries.buytaert@gmail.com> wrote:

On 11 May 2006, at 20:31, Adrian Rossouw wrote:

...

Now that we have .install files for modules, I believe it is time we extended our database abstraction layer.

I recommend we create the following extra functions :

1. db_create_table($tablename, $columns); // columns is an associative array with the 'name' => 'type' 3. db_drop_table($tablename); 3. db_create_index($tablename, $columns); // columns is an array of column names, or just a single column. 4. db_drop_index($tablename, $columns); 5. db_add_column($tablename, $column, $type); // PGSQL already has a similar function, but it isn't in both systems. 6. db_drop_column($tablename, $column);

The installer that is coming in, will be converting the database.*sql files into a system.install file, and with these functions, we can collapse ALL THREE SCHEMA FILES, into a single, much simpler file.

Can't we move forward without these functions? It looks like we are introducing more and more dependencies to that makes it harder to move the install system forward.

While an additional database layer (SQL is already an abstraction layer) makes it easier to port Drupal to other database systems, it makes it harder to understand what is going on. It is a lot less accessible to people that are new to Drupal, but that do have a SQL background. So personally, I'm not all that convinced that these functions are a good idea ...

I, for one, find it much easier to read an SQL statement than some (new) Drupal-specific table definition. I don't want to learn or use a such definition language. Such functions make the database scheme less transparent.

-- Dries Buytaert :: http://www.buytaert.net/

Op vrijdag 12 mei 2006 18:00, schreef Dries Buytaert:

I, for one, find it much easier to read an SQL statement than some   (new) Drupal-specific table definition.  I don't want to learn or use   a such definition language.  Such functions make the database scheme   less transparent.

The agility! wonderfull ;) Serious: What Drupal needs above all, is not some "Higher Language To Talk To Lower Languages", but a way to make stuff easier. IMveryHO forms api surpassed its goal in this: What is easier: calling form_weight() or building an object-from-drupal-specific-arrays? Point is clear. If we go for some DB abstraction layer, we should really, IMnsHO look at Active Record (PHP stuff at http://phplens.com/phpeverywhere/?q=node/view/228), wich does not take SQL away from developers but makes it easier to use. A Big Difference, IMO. Whether we go for AR itself or some AR library, the central point should IMO be: "make it easier for developers to build sites". Wich is far from "make a system in such a way that developers are pushed to build technical better system". Drupal, IMO is walking slowly towards the latter the way its going now. A pity, because it does not make things better. Only "technical better". Bèr

I'm going to do some Bèr's praise here. I'm usually really strict about the DNRY personally. Though I use 3 instances as a clue that I'm going to need to split the code into a function. Continuing that train of thought, how many time have you had to do some sort of INSERT/UPDATE switch in you drupal code? I think Bèr is right on in suggesting we consider something like AR. It's a little more OO than drupal has been in the past do to overhead concerns but I'm not sure that applies here. Big +1 from me. James On 5/12/06, Bèr Kessels <ber@webschuur.com> wrote:

Op vrijdag 12 mei 2006 18:00, schreef Dries Buytaert:

...

I, for one, find it much easier to read an SQL statement than some (new) Drupal-specific table definition. I don't want to learn or use a such definition language. Such functions make the database scheme less transparent.

The agility! wonderfull ;)

Serious: What Drupal needs above all, is not some "Higher Language To Talk To Lower Languages", but a way to make stuff easier.

IMveryHO forms api surpassed its goal in this: What is easier: calling form_weight() or building an object-from-drupal-specific-arrays? Point is clear.

If we go for some DB abstraction layer, we should really, IMnsHO look at Active Record (PHP stuff at http://phplens.com/phpeverywhere/?q=node/view/228), wich does not take SQL away from developers but makes it easier to use. A Big Difference, IMO.

Whether we go for AR itself or some AR library, the central point should IMO be: "make it easier for developers to build sites".

Wich is far from "make a system in such a way that developers are pushed to build technical better system". Drupal, IMO is walking slowly towards the latter the way its going now. A pity, because it does not make things better. Only "technical better".

Bèr

On 13 May 2006, at 00:17, Bèr Kessels wrote:

Serious: What Drupal needs above all, is not some "Higher Language To Talk To Lower Languages", but a way to make stuff easier.

IMveryHO forms api surpassed its goal in this: What is easier: calling form_weight() or building an object-from-drupal-specific-arrays? Point is clear.

I agree. While Drupal becomes more powerful, it also becomes more difficult to develop for. Drupal 4.7's new forms API is a prime example. It is obviously great in terms of security and flexibility, but at the same time, it is also _much_ harder to use than the old form API. What does that mean? Well, that some people can make really fancy forms but that the majority of the people will find it more difficult to make basic forms. This is somewhat problematic as we need more people that are good Drupal developers. There is such a strong demand for good Drupal developers, yet which each new version, we add more barriers as a side effect of making Drupal more powerful. As a result, we'll get many more users, but relatively fewer developers. And that's a big problem. For most of us eliminating the various SQL schemas makes perfect sense. After all, most of us are expert Drupal developers, and as such, an incremental improvement is easy enough to deal with. However, for new Drupal developers, who are not intimate with Drupal's code, this just increases the barrier. Why did you end up with Drupal to begin with? Because it had an extremely powerful API for every single problem? Or because it was clean code, easy to get into and make work? The web is built by millions of individuals, many of which are amateurs. They continuously update, tweak and rebuild their websites. We want Drupal to remain accessible for them. Hence, the challenge is to make Drupal more powerful _AND_ easier to develop for. This requires that we question certain development directions and look at them through the eyes of amateurs. http://buytaert.net/complexity-is-a-disease http://buytaert.net/why-php-and-not-java -- Dries Buytaert :: http://www.buytaert.net/

Dries wrote:

...

We want Drupal to remain accessible for them.

"Gerhard Killesreiter" wrote:

Do we?

My impression is that you do not wish it be accessible.

I certainly want them to be able to use Drupal but I'd appreciate if they'd keep their hands out of the code if they aren't programmers. This only makes for nasty support cases.

There currently is no way to use Drupal without modifying code. Period. The ease of use score for Drupal would have to be considered very low, compared to other CMS tools. It's range of functionality is far greater, of course, but not for the average, download-and-install user, or even the generic site user, who doesn't even manage the Drupal installation. The chief barrier to fully marrying Drupal is the ongoing code-writing that must be done: even themes are written in PHP, and once you add a module, you must immediately begin PHP training. How do you propose that any Drupal user (not developer, just a user) "keep their hands out of the code"? -- Gary

Dries wrote:

...

...

We want Drupal to remain accessible for them.

Gerhard wrote:

...

Do we?

I wrote:

...

My impression is that you do not wish it be accessible.

Gerhard wrote:

Hadn't you promised to unsubscribe from this list?

You continue to make my point. After receiving a half dozen off-list apologies for your behavior (a real shame that others felt the need to distance your behavior from Drupal's reputation), I believe that I'll just filter your posts to the trash. (And I would encourage you to exercise the same freedom, beginning at your leisure.) This way, I can receive valuable reading from civilized people and avoid the "angry penguins". When one is engaged in a conflict with another person, and the other person simply refuses respect and reason, there is no safe option but to disengage from that person. Perhaps there are valid explanations for your diminished social capacity -- either cultural, psychological or genetic -- but they are of no interest to me. You do not threaten me, Gerhard, and I simply refuse to be bullied by you. Feel free to use your time and valuable human energy on more fruitful tasks. I know that I will. Sadly, and with mind always open, -- Gary

I want to jump back a bit and make a quick point. Coming in just recently and doing a *lot* of converting old fapi to new, the new api is a lot easier to understand and work with for me. For one thing I don't have to worry about the the args for each func or even one func. The keys are descriptive and tell you what your adding and are common among most form elements. All it required was a basic understanding of html form which should be a requirement anyways My point is fresh eyes see things different. Also, I have several installs with no coding what so ever. Every time I'd come to merlin with "I think I'm going to code a module to do X", he'd tell me to look at module Y he or someone else had already written. That aside and back on topic. I know drupal is a scratch your own itch sort of system but its growing fast. I think a lot of people here have been hinting at this but pretty soon the firebird guys are going to be wanting to post compatibility patches to core. And then(pull out those buzzword bingo cards and get ready) someone's boss is going to ask if they can run an "enterprise" ready install of drupal on oracle. Suddently our update functions are 200lines long instead of 50 and our .install files are 10k. I'm probably exagerating a little but those seem like some serious maintainability issues. Also, I have a good grasp of SQL, both mySQL and plSQL(not really pgSQ: yet), but

Comment.find_by_id(12) Comment.title = "Foo Bar" Comment.save!

is actually easier to read for me. Maybe that's just a design practice modules should use more but I think there's room to take some of the SQL leg work of the drupal dev. James

Bèr Kessels wrote:

Op zaterdag 13 mei 2006 18:56, schreef Earl Miles:

...

This is patently untrue. Only my very first Drupal implementation has modifications to core code, and I later learned how to do those things I wanted without core code.

I just did a very quick CVS diff over my 21 sites multisite installation: Core is omdified, but only because of security reasons (I dont want PHP eval stuff). But 17 of the 21 sites contain one or more modified modules. 6 of the 21 contain modules that I wrote specially for them 15 of 21 contain newly built or modified themes.

You are making these changes because you can. You skills help you to run better sites. But even if you had not the skills you yould still use Drupal. Sure, your sites would lack a feature here and there, but that's not the point. The point is that Drupal "out of the box" works for a lot of people and you don't need to know PHP to set up a site which already has a lot of features. Cheers, Gerhard

Op maandag 15 mei 2006 14:34, schreef Gary (Lists):

And my point is not to be taken as "just a complaint", for it is not.  It's a real barrier, most especially, to keeping things up to date and implementing new features as they become recommended.

Op zondag 14 mei 2006 14:45, schreef Gerhard Killesreiter:

You are making these changes because you can. You skills help you to run better sites. But even if you had not the skills you yould still use Drupal. Sure, your sites would lack a feature here and there, but that's not the point. The point is that Drupal "out of the box" works for a lot of people and you don't need to know PHP to set up a site which already has a lot of features.

I am not complaining. I am saying two things: 1) Drupal is very powerfull (mostly becuase of its architecture) for modificated websites. There is absolutely no way that I could hack Joomja, or Nuke in such an easy way (I used both a lot, so its not just a matter of knowing one app better). Which is was my reason for moving to Drupal. 2) Becuase Drupal has this power, and this great "feature", we have a real gem at hand! We should not forget that a lot of people might use Drupal for this very reason too. Focussing only on end-user improvements, is IMO bad. That is my main point. Dries points out in several blog posts and mailinglists mails that the low "hop on level" is a good thing in Drupal. I wanted to add to his points the fact that it is easy to hack Drupal. Hacking your stuff is not bad. Because IMO getting a good site up n Running you simply cannot do without it. From the Lets keep Drupal Hack Friendly dept. Bèr

Bèr Kessels wrote:

Hacking your stuff is not bad. Because IMO getting a good site up n Running you simply cannot do without it.

I still think this is an unfair characterization. You can do amazing things with Drupal without hacking the core, you just have to know exactly how to do it sometimes. As I said, I have several installations without core hacking involved. There are some special needs, right now, that might still involve a little core hacking here and there but a lot of these hacks that people do are just as likely to be unnecessary. And characterizing Drupal as requiring core hacking because you have some special needs is unfair to the entire community who CAN make Drupal useful without hacking the core.

On Mon, 2006-05-15 at 08:34 -0400, Gary (Lists) wrote:

"Gary (Lists)" wrote:

...

There currently is no way to use Drupal without modifying code. Period.

schreef Earl Miles:

...

...

This is patently untrue. Only my very first Drupal implementation has modifications to core code, and I later learned how to do those things I wanted without core code.

"Bèr Kessels" wrote:

...

I just did a very quick CVS diff over my 21 sites multisite installation: Core is omdified, but only because of security reasons (I dont want PHP eval stuff). But 17 of the 21 sites contain one or more modified modules. 6 of the 21 contain modules that I wrote specially for them 15 of 21 contain newly built or modified themes.

Conclusion: I am NOT able to deploy a Drupal site without touching PHP or hacking up the system.

I believe that you make my point very well. These numbers are useful.

And my point is not to be taken as "just a complaint", for it is not. It's a real barrier, most especially, to keeping things up to date and implementing new features as they become recommended.

Umm I've put together some awesome asset management tools for magazines that respect editorial and production workflows using drupal, without coding more than a few patches for broken modules. that aren't yet released..

Dries Buytaert wrote:

...

...

This is somewhat problematic as we need more people that are good Drupal developers. There is such a

We recently got some very nice additions to our developer crowd.

At any point in time, we had a nice addition of developers. That doesn't mean there is enough talent available.

What qualifies as "enough"? Drupal is a growing project and sure we could need more Drupal coders. But it isn't that we wouldn't get new code. You already added a couple new features to HEAD and 4.7 is only two weeks old. There are a lot of patches flying around and soon I will have to get extra patches for 4.7 because the code changed so much.

...

...

strong demand for good Drupal developers, yet which each new version, we add more barriers as a side effect of making Drupal more powerful.

I think this is only a matter of perception. You (as I was) aren't yet fully comfy with the new forms API and thus think that everybody will find it more difficult. For people who are new to it, they will just read the (excellent!) docs and should be able to make the usual forms within a few minutes.

It's not just a matter of perception. At least a dozen people told me they got flabbergasted by the new forms API.

Oh, I was too. Because I was used to the old ways. It took some time - and the process isn't finished - to get used to the new ways. I am quite sure that 2/3 of your dozen people were people who were used to the old string based functions and had not really investigated the new forms. Of course, I am not yet 100% confortable with it, but I was always a slow learner. ;)

...

...

The web is built by millions of individuals, many of which are amateurs. They continuously update, tweak and rebuild their websites. We want Drupal to remain accessible for them.

Do we? I certainly want them to be able to use Drupal but I'd appreciate if they'd keep their hands out of the code if they aren't programmers. This only makes for nasty support cases.

Funny. You just mentioned you didn't know any PHP or MySQL when you started using Drupal ...

Right. I first had tried to build a web site builder in bash and AWK and found it didn't work too well. Then I looked for CMSes in FORTRAN. Didn't find any and resorted to learn another programming language. But I wouldn't say that I didn't have any programming experience. Cheers, Gerhard

On 13 May 2006, at 13:15, Derek Wright wrote:

in general, i totally agree with the thrust of what dries is saying about trying to keep drupal easier to develop for. however, i also think adrian is right in this specific case that such a table creation and modification abstraction layer would be a good thing.

That is the thing. I agree with Adrian as well (I can see the advantages), it's just that we need to be careful and look at this from a beginner's point of view. We should avoid turning this thing into a system that quacks like an enterprise-level CMS. In such systems, you can define database schema's using XML, but at the end of the day, they aren't used as much as Drupal. Guess why? Part of the answer is that no one can install or customize such system without hiring a consultant. That's not where we want to go.

i'm not exactly an amateur, but i didn't know *any* SQL syntax as of 5 months ago when i first tried setting up a drupal site for my band. for me, the kind of abstraction layer adrian proposed (and others expanded on) would have probably helped me get up to speed more quickly. maybe i'm in the tiny minority of potential drupal developers who aren't already SQL wizards, but who know how to write code (i didn't know php either, but the syntax is enough like perl and C and the docs are good enough that i could figure it out). however, from my perspective as still something of a drupal novice, i don't think this would have slowed me down or deterred me in any way.

How can you understand the table definition proposed by Adrian (see below), if you don't know any SQL? I don't think this gets you up to speed any more quickly. Quite the contrary, you'd first have to learn SQL and then you have to figure out how this Drupal specific definition format maps onto it. Make no mistake, a Drupal-specific definition language is no excuse for not having to learn MySQL. It does add an additional step to the learning curve! db_create_table('client_system', array( 'cid' => array('type' => 'int', 'NOT NULL' => TRUE, 'PRIMARY KEY' => TRUE), 'name' => array('type' => 'short text', 'NOT NULL', 'default' => '', , 'PRIMARY KEY' => TRUE), 'type' => array('type' => 'short text', 'NOT NULL', 'default' => ''), )); Hundreds of books have been written about PHP and MySQL. As a newbie, you can buy these books and understand what Drupal's MySQL schemas mean. If we add an extra abstraction layer, these books are going to be less useful. Hence, we added a barrier. It does, however, help you write portable code, but that isn't something most people are concerned about. Newbies want to write code that works, and that they can understand. Being able to understand something is very rewarding/motivating. Portability is not their main concern. IMO. I'm not saying that the database definition function is a bad thing or that we should drop it on the floor. We have to carefully evaluate whether the advantages (portability) outweight the drawbacks (less developers). All I can say, is that, as a programmer, I can write SQL faster than I can write db_create_table() definitions. Plus, I value the transparency of the "raw" SQL statements; at least I know _exactly_ what is going on under the hood. Here is something to think about (I'm still trying to figure out the best way to formulate this): The net effect of creating abstracting layers for everything is that you're building a new programming language on top of the existing languages (eg. PHP and MySQL). At some point, you'll loose some of the underlying languages' advantages (eg. available documentation, talent pool, transparency). You see this happen in the Java world. Because of all the layers (EJBs, Struts, JSTLs, JSPs, Beans, CMP, BMP, JAXM), J2EE started to live a life on its own. Being a "regular" Java developer isn't enough to get certain jobs. -- Dries Buytaert :: http://www.buytaert.net/

On 12 May 2006, at 17:50, Gerhard Killesreiter wrote:

...

Hundreds of books have been written about PHP and MySQL. As a newbie, you can buy these books and understand what Drupal's MySQL schemas mean.

While that is true, most people don't buy these books. I think that Adrian's proposed scheme isn't harder to learn from scratch than the usual SQL queries. The only drawback is that when you learn plain SQL you can use it elsewhere too while you can't do so with Adrian's abstraction layer.

No. If no one would buy these books, there wouldn't be as many available.

...

I'm not saying that the database definition function is a bad thing or that we should drop it on the floor. We have to carefully evaluate whether the advantages (portability) outweight the drawbacks (less developers).

Any developer worth the name should be easily able to understand this scheme if he has prior knowledge of SQL. If he hasn't then it doesn't matter (to us) if he learns plain SQL or our db abstraction layer.

No. If you believe that is true, then the following holds as well: "Any developer worth the name should be easily able to understand PostgreSQL if he has prior knowledge of (My)SQL". -- Dries Buytaert :: http://www.buytaert.net/

http://cvs.sourceforge.net/viewcvs.py/easymod/sql_parser/root/includes/sql/ Look who committed that... sound familiar, heh?

Dries Buytaert wrote:

How can you understand the table definition proposed by Adrian (see below), if you don't know any SQL? I don't think this gets you up to speed any more quickly. Quite the contrary, you'd first have to learn SQL and then you have to figure out how this Drupal specific definition format maps onto it. Make no mistake, a Drupal-specific definition language is no excuse for not having to learn MySQL. It does add an additional step to the learning curve!

This is a very good point, and something I don't think a lot of us have considered while proposing this. But I have a question... would the introduction of db_create_table() mean removing the ability to just continue copy/pasting PHPMyAdmin dumps into db_query()? Or are we simply proposing some additional API functions for developers who wish to write portable SQL code in an easy way? It seems to me we could have the best of both worlds by not changing the current behaviour of db_query(), and instead just introducing some additional API calls for more "advanced" developers who understand MySQL but maybe don't know PostgreSQL and would like to write portable code could do so without needing to install PostgreSQL and read the whole manual there (which is another learning curve :)). -Angie

On 13 May 2006, at 5:17 PM, Angela Byron wrote:

But I have a question... would the introduction of db_create_table() mean removing the ability to just continue copy/pasting PHPMyAdmin dumps into db_query()? Or are we simply proposing some additional API functions for developers who wish to write portable SQL code in an easy way? They can't do that now with the install system anyway.

If you put table creation code "FOR A MODULE" into phpmyadmin, it won't update the schema versions, and your updates, when they happen. Will break. Sure, you're free to just not use the install system for that module at all, but at that point you should not be distributing your module, and it shouldn't be in drupal.org cvs for consumption by other people. Keep in mind, putting sql into phpmyadmin also doesn't to table prefixing, and all the other things a distributed module should do. If you distribute your code on drupal.org, you should be using the install system and if you are using the install system, you should be using the abstraction layer.

It seems to me we could have the best of both worlds by not changing the current behaviour of db_query(), and instead just introducing some additional API calls for more "advanced" developers who understand MySQL but maybe don't know PostgreSQL and would like to write portable code could do so without needing to install PostgreSQL and read the whole manual there (which is another learning curve :)). Nobody suggested changing db_query at all. These are some extra functions, exclusively meant for the .install files.

We already have some of these functions, but only for postgres, and these functions have cleaned up the postgres updates significantly. What it comes down to, is these functions are essentially 'theme()' functions, but for sql statements. Nothing more, nothing less. -- Adrian Rossouw Drupal developer and Bryght Guy http://drupal.org | http://bryght.com

Bèr Kessels schrieb:

Op zaterdag 13 mei 2006 14:50, schreef Dries Buytaert:

...

Hundreds of books have been written about PHP and MySQL. As a newbie, you can buy these books and understand what Drupal's MySQL schemas mean. If we add an extra abstraction layer, these books are going to be less useful. Hence, we added a barrier.

It does, however, help you write portable code, but that isn't something most people are concerned about. Newbies want to write code that works, and that they can understand. Being able to understand something is very rewarding/motivating. Portability is not their main concern. IMO.

There is a way inbetween this too. The Middle Road. And that is how Active Record works. Let us forget about OO vs non OO for a second and look at this:

Comment.find_by_id(12) Comment.title = "Foo Bar" Comment.save!

But you can also do: Comment.find(:first, :conditions => "id = 12") ..same as above ..

And even: Comment.find_by_sql("select * from comments where id = 12") ..same as above..

Now. The point is that in your Day to Day work, you need not bother about SQL. But that is not the same as "not being allowed to use SQL".

David Heinemeier Hansson: "Some object-relational mappers seek to eradicate the use of SQL entirely..." "..Active record does not. It was built on the notion that SQL is neither dirty nor bad, just verbose in the trivial cases." "... Therefore, you shouldn't feel guilgy when you use find_by_sql() to handle either performance bottlenecks or hard queries. Start out using the object-oriented interface for productivity and pleasure, and then dip beneath the surface for a close-to-the-metal experience when you need to".

Which IMO is how we should treat Drupals "abstraction layer" too.

Not try to pull out the SQL etc for sake of abstraction. Not making stuff more OO for the sake of portability. But only to make working on Drupal more fun, faster, and more productive.

We should really try to avoid becoming a platform that you "fight against to get stuff done your way". It should be a fun and productive thing to do, working on a Drupal site. Working with Drupal should leave you all warm and fuzzy. With a "wow! this is very smart and usefull" erlebnis every hour.

Instead of a "why the *** cant I just use foo bar here. Wy the *** do i need to read sqlApiLayers introductions and manuals when all I need is to store a value."

I am not saying this is the case now. But I fear that if we continue rolling down the road of more complexity and more abstraction, without remaining pragmatic, we are going to loose precious developers.

Bèr

+1. I think, ActiveRecords in drupal would just be perfect. For my last project, I used a php framework called CakePHP [1]. It's quite similar to RoR, but written in php. It implents a lot of ActiveRecords stuff, and I never created a web app faster. Especially for simple, every-day-work, for example by coding quite a simple data managment module, ActiveRecords saves not only a lot of code, but also a lot of time. I can't see any reasons against including ActiveRecords in drupal, espacially because no one will be forced to use them, but a lot of people will. best regards, frando [1] www.cakephp.org

Op zondag 14 mei 2006 08:36, schreef Larry Garfield:

True, they don't cover everything, but could we possibly be leveraging that better?

That is my whole point. Esp. since Drupal is not (and should not be, IMO) OO enough to become real Active Record. And indeed, main aim of my helpers.module is to provide a lot of these functions in a quick-to-go environment. I hope, however, that that module can be some of a stepping stone for core. We can test functions and concepts there. We can also see what stuff is used more often and what not. Popular helper functions should get a patch then for Core. (if someone finds the need to do so!) Bèr

On 13 May 2006, at 14:27, Jeremy Epstein wrote:

...

Hence, the challenge is to make Drupal more powerful _AND_ easier to develop for. This requires that we question certain development directions and look at them through the eyes of amateurs.

IMO, learning the DDL specifics of two different database systems is more of a barrier for new developers, than learning a simple abstraction layer ever could be. I can't speak for all developers, but personally, I've always been much more familiar with MySQL, and I've been scared and uneducated about the details of pgSQL for quite some time. I've been developing with Drupal for over a year, but only recently have I taken the time to dig a little deeper (just a little) into pgSQL - just enough to make my modules compatible with it.

The fact you didn't dig deeper into PostgreSQL until now, illustrates my point. People new to Drupal want to write code that works. They are just scratching their own itch. They want to tweak or extend Drupal, and they want it to be super easy to do. Getting the job done, and done quickly while still feeling good about it, is what sucks them into Drupal. Portability is not a major concern until they are fully sucked into Drupal development. -- Dries Buytaert :: http://www.buytaert.net/

2006. május 13, szombat 16.04 dátummal Jeremy Epstein ezt írta:

Fact (as you said): new Drupal developers just want to write code that works, and they don't care about portability. So the only way that

The newbies only want a good documentation and examples in one place. Yesterday, I try to look for taxonomy_save_term's documentation. There's no a word about this function. Ok, experienced users are able to look inside the code to find out how it works, but I don't think that it is the best way. Newbies love to write perfect code, but if we don't learn them how they can do it and we force them, they'll be upset.

I don't see this as being any different from the learning barrier that already exists to support table prefixing. New developers must learn the {curly bracket} syntax before they start writing any SQL in Drupal

Should they read and memorize the whole Drupal Handbook to write a code? I don't think so. Did you know, the {} is only (i don't know, because when I type "curly" into the search box, I get issues, forum topic, etc but not the handbook pages) mentioned in the "How to write database independent code" . It's not about how to write db independent code. When I run through the table of contents of the handbooks, I don't see any section dedicated to how to use the db functions properly. I think, if you force the users to use special functions, you have to give them sophisticated documentation, where you tell them how and why they should use, with detailed use cases. You can't write too much. Good practice: "Multipage forms with the Forms API" (http://drupal.org/node/54753) It's a detailed tutorial with a huge code and description. Bests, -- Aries

Dries Buytaert wrote:

For most of us eliminating the various SQL schemas makes perfect sense. After all, most of us are expert Drupal developers, and as such, an incremental improvement is easy enough to deal with. However, for new Drupal developers, who are not intimate with Drupal's code, this just increases the barrier.

I sit on the fence regarding abstraction layers. But a quick comment about new Drupal developers. The best thing for a newbie who has SQL experience (eg. me) is to have a plain english data dictionary where each table and field is described. Sure I can glean information from database.mysql, but its usefulness is limited. sime

On 12 May 2006, at 6:00 PM, Dries Buytaert wrote:

Can't we move forward without these functions? It looks like we are introducing more and more dependencies to that makes it harder to move the install system forward. I never said it was a dependency. I said it would make it simpler. We can still do it without this, it's just much more work, the same with every single other schema we are supporting in duplicate or even triplicate.

While an additional database layer (SQL is already an abstraction layer) makes it easier to port Drupal to other database systems, it makes it harder to understand what is going on. It is a lot less accessible to people that are new to Drupal, but that do have a SQL background. So personally, I'm not all that convinced that these functions are a good idea ... This is not about supporting more db types, this is about reducing code, and reducing duplication of it.

I don't think it's sane that every single change to the schema needs to happen in 4 places. I don't like the fact that a contrib module accepting a patch to support postgres, could hold up development of their module. I don't like the fact that postgres and mysql schemas might get out of synch in contrib modules (WITHIN THE SAME _install() FUNCTION). Something like that is incredibly annoying to fix, and increases the amount of code developers need to write, some of which they might not be capable of writing (ie: the postgres schema updates).

I, for one, find it much easier to read an SQL statement than some (new) Drupal-specific table definition. I don't want to learn or use a such definition language. Such functions make the database scheme less transparent. mysqldump -usomething database > database.pgsql or pgsql -Usomething database > database.pgsql

These proposed functions are essentially theme_ functions with a db_query on the result at the end. The 'type' is essentially the index for a map of the types each database uses to represent what we need. The amount of code we stand to remove : (apart from the ~1800 lines of sql we can remove with the extra schemas) And an example of only one of the update_ functions we could have improved. These functions seem to be an average of 20 lines of code, per function, per update that does stuff like this, over ALL OF CORE AND CONTRIB. --- The original (55 lines long) --- function system_update_166() { $ret = array(); $ret[] = update_sql("DROP TABLE {directory}"); switch ($GLOBALS['db_type']) { case 'mysqli': case 'mysql': $ret[] = update_sql("CREATE TABLE {client} ( cid int(10) unsigned NOT NULL auto_increment, link varchar(255) NOT NULL default '', name varchar(128) NOT NULL default '', mail varchar(128) NOT NULL default '', slogan longtext NOT NULL, mission longtext NOT NULL, users int(10) NOT NULL default '0', nodes int(10) NOT NULL default '0', version varchar(35) NOT NULL default'', created int(11) NOT NULL default '0', changed int(11) NOT NULL default '0', PRIMARY KEY (cid) )"); $ret[] = update_sql("CREATE TABLE {client_system} ( cid int(10) NOT NULL default '0', name varchar(255) NOT NULL default '', type varchar(255) NOT NULL default '', PRIMARY KEY (cid,name) )"); break; case 'pgsql': $ret[] = update_sql("CREATE TABLE {client} ( cid SERIAL, link varchar(255) NOT NULL default '', name varchar(128) NOT NULL default '', mail varchar(128) NOT NULL default '', slogan text NOT NULL default '', mission text NOT NULL default '', users integer NOT NULL default '0', nodes integer NOT NULL default '0', version varchar(35) NOT NULL default'', created integer NOT NULL default '0', changed integer NOT NULL default '0', PRIMARY KEY (cid) )"); $ret[] = update_sql("CREATE TABLE {client_system} ( cid integer NOT NULL, name varchar(255) NOT NULL default '', type varchar(255) NOT NULL default '', PRIMARY KEY (cid,name) )"); break; } return $ret; } --- the new version (28 lines) --- function system_update_166() { $ret = array(); db_drop_table("directory"); db_create_table(client, array( 'cid' => array('type' => sequence), 'link' => array('type' => 'short text', 'NOT NULL', 'default' => ''), 'name' => array('type' = 'tiny text', 'NOT NULL', 'default' => ''), 'mail' => array('type' = 'tiny text', 'NOT NULL', 'default' => ''), 'slogan' => array('type' = 'tiny text', 'NOT NULL', 'default' => ''), 'mission' => array('type' = 'tiny text', 'NOT NULL', 'default' => ''), 'users' => array('type' = 'int', 'NOT NULL', 'default' => 0), 'node' => array('type' = 'int', 'NOT NULL', 'default' => 0), 'version' => array('type' = 'tiny text', 'NOT NULL', 'default' => ''), 'created' => array('type' = 'timestamp' , 'NOT NULL' => TRUE, 'default' => 0), 'changed' => array('type' = 'timestamp' , 'NOT NULL' => TRUE, 'default' => 0), )); db_create_table('client_system', array( 'cid' => array('type' => 'int', 'NOT NULL' => TRUE, 'PRIMARY KEY' => TRUE), 'name' => array('type' => 'short text', 'NOT NULL', 'default' => '', , 'PRIMARY KEY' => TRUE), 'type' => array('type' => 'short text', 'NOT NULL', 'default' => ''), )); break; return $ret; } -- Adrian Rossouw Drupal developer and Bryght Guy http://drupal.org | http://bryght.com