Thread

@Mirage, Overzealous

I hadn’t considered using persistent connections, and that does make sense.  However, for me personally, I’ve stopped considering persistent connections as a good option since running into a couple of situations where I ran out of database connections by using them.  What happens is that with Apache (and probably other multi-process web servers), the pool of persistent connections is not shared between processes, so you can have a bunch of idle connections hanging around, but a new child process still has to attempt a new connection.  So, depending on how many connections your database server can accept, how long connections are kept, and the number of processes Apache is using, you can easily run into situations where you don’t have any free connections even though the actual server load is low.

Anyway, that’s just another fun consideration.

@Devon Lambert

As it says in the manual, the id column is required, and must be an automatically incremented integer.  No exceptions.

The primary key must be ID.  It cannot be renamed.  If you want a different column name or type, make a separate column that is unique.

@TheJim

Thanks for the information.  I hadn’t heard of that, so I’ll have to keep that in mind.

Most likely I’m going to do away with the reliance on CodeIgniter’s ActiveRecord some time in the future, which will revert the way the DB class works (I won’t be creating a new one for each object).  I’ve never liked some of the decisions, and I’ve already replaced the LIKE methods (why are they separate from other WHERE statements?) and added the ability to group queries (ie: parentheses).  Nearly every AR method is already replicated in the DMZ source, excluding a few like join.

But I’ve still got a lot of work in front of me.  That will end up being a 2.0 kind of change.  Probably along with all of the performance changes.

Thanks OverZealous.

I did read that, but thought there might be some way to override it within an individual model (doctrine style).

Interesting that it’s made that way as there are many DBs out there that have a reliance on a unique/primary id within a table that is not a) automatically incremented AND/OR b) not named “id”.

I initially thought about going through the library and doing a find and replace on all instances of ‘id’ and replacing with a primary_key variable but I realized there were some other dependencies around the id.

Thanks for that feedback in any event.

@Overzealous

Yeah, dropping the reliance on ActiveRecord would make a lot of sense.  Unfortunately, it would also be a bit of work, as you’ve noted.  Well, I’m sure DMZ will get there at some point.

Interesting that it’s made that way as there are many DBs out there that have a reliance on a unique/primary id within a table that is not a) automatically incremented AND/OR b) not named “id”.

Just to answer this: DMZ (and DataMapper before it) is not a generic ORM.  As you mentioned, there are full-fledged ORMs like Doctrine for those who need that level of control.  😉

DMZ is designed to be easy-to-use, and fast to set up.  (In my opinion, the more configurable an ORM is, the harder it is to get working.)  But the catch is, you must build your DB structure to match DMZ’s requirements.  In the end: it isn’t really designed to work with existing DBs, unless those DBs can be modified.

I make no apologies, though.  Doctrine and DMZ just have different purposes.  I don’t see them as “competing” in any way.  (It’s not like I make any income off of DMZ!  😊 )

Is there a way to put models in subfolders, e.g.

/models
  /app1
  user.php
  country.php
  /app2
  dolls.php
  ...

It seems that this doesn’t work since the new User() does not have any type of qualifier to specify which folder these folders come from.

Do I really have to stick with Namespace_user for categorizing models?

No.  Yes.

DMZ does not support models in subfolders.  How would that even work?  If you have a model called “User”, then the table is called “users”.  The relationships are called “users_xxx or xxx_users”.  It doesn’t even logically make sense for a simple ORM like DMZ.

If you need multiple applications, why don’t you use CodeIgniter’s methods to separate your apps (via different index files)?

Bug with left outer join when using oci8.

I was trying DMZ out and found that many of the left outer join queries are written as

select *
from “TABLE1” left outer join “TABLE2” as TABLE on ...

However, oracle, does not support this. Removing the “as” keyword resolves the issue, since oracle’s alias doesn’t work this way.

When i was trying out the original DM I didn’t have this issue. In addition i found the following line in the DB_active_rec file (though not 100% sure if this is what prevents the original DM from having this problem):

$table = preg_replace(’/ AS /i’, ’ ‘, $table); // line 1459

this should properly remove the “as” keyword. Perhaps DMZ can adopt a similar approach?

Frank

@Frank Liu

Hmm, that’s interesting.  The reason DMZ adds the aliases is because, unlike the original, DMZ supports having multiple relationships to the same model.  Therefore, to query these relationships, it is necessary to alias the related tables.  Also, DMZ supports deep relationships, with the same problem.

The only databases I have to test against are PostgreSQL (which I fully support) and MySQL (which I test against occasionally).  Both of these support the as keyword, so I’ve never noticed that it isn’t necessary.

I’m planning on creating a performance-focused release of DMZ in the next few weeks.  I’ll remove the apparently unnecessary as from the joins at that time.

Thanks for pointing it out.

@Frank Liu

Quick update: I’ve attached an updated version of DataMapper.php (the main library) without any of the AS keywords in the joins.

If you want to verify that it works for you, that would be great.

This update also enables subqueries for self-relationships (they didn’t work before), and fixes a possibly non-minor bug where related subqueries would have spaces removed before the parent table name.

I’m building an application that manages a lot of database records across several tables, and have ran into problems with DMZ running my app out of memory.  DMZ seems to load all its query results into memory at once, which hits memory pretty hard when loading several tens of thousands of records.  Is there a way to load DMZ query results one at a time, similar to what Active Record does, so I can manage my memory footprint a bit better?

I got my app working for now by freeing query results after processing and before starting a new query, something I probably should have been doing in the first place.  Here’s the technique I used:

$this->table->get();
foreach ($this->table->all as $record)
{
    # do some stuff
}
unset($this->table->all);

Thanks for the help,
Jack

@Jack Scott

Please look back (or search) this forum.  TheJim (I believe) had a solution that we discussed could be converted into an extension.  It basically involves using get_sql and then manually converting each row to an object as it is looped.  I had written some information about the limits of get_sql, so you’ll need to find that.

The latest DMZ automatically frees result sets if they contain more than 100 rows.  For efficiency, I don’t bother for smaller queries.  You can reduce this threshold, check out the config page in the manual.

I’m thinking of adding two new methods into DMZ when I work on some performance updates: get_raw, which returns the result set, and get_streamed (or something similar), which only instantiates each object as it is looped over.  These should help with large datasets.  The latter will be based on the code that I just mentioned above.

In either case, it probably isn’t very efficient to use ActiveRecord and PHP to process thousands of rows, (much less tens of thousands) no matter what other code is being used on the front end.  ActiveRecord loads all of the rows into an array, so you’ll lose a lot of memory with large numbers of rows no matter what.  You should see if you can use the database server to process these rows, so you aren’t transferring all that data back and forth.  Just my 2¢.

EDIT: Please ignore this post and forgive my stupidity 😉 Just found the solution…

Got a question…
Is there any good reason why $obj->count() clears the query?

The reason I ask is, because I’m working on pagination for a rather complex search query and I need to get the total amount of matches in the DB. Since $obj->count() clears the query I have to build it the whole query again for get() call. This seems unneccesary and ‘dirty’.

The other option would be to just skip the count and fetch the entire result set and then count them with count($obj->all), but then I’m stuck with extracting the relevant DB entries for my current page, whereas I could have much rather let the DB handle that part with limit()...

Any elegant ideas to solve this?

Please see the big yellow section at the top of the Count page in the manual.

Edit: By that, I mean, just run your query, and then get the number.  Calling count has to clear the query, because you are running a COUNT(*) against the database.  Running any query against the DB must clear the current set of instructions.