How to implement middleware?

[anttilinno]

I'm sorry, but I am window shopping for ORM like library, and if it is not mentinoned, I usually ask, how to implement middleware. My batteries included orm like product would be performant and include magic for updating records with inserted_at, updated_at and deleted_at. Also, all queries would know to ignore deleted_at IS NOT NULL records, when not specified (flag withDeleted = true etc.) How easy is it to implement in pogi?

[holdfenytolvaj]

It is not included in pogi and here is why:

• we only want to do what is necessary and as few magic as possible :).
• this is sth you want to do at table creation time to make the guarantee that no cheating is allowed, even on manual updates. Since pogi, does not create the table for you, by default it is not pogi's responsibility.

Nevertheless we also have sth similar for few tables. We use postgre's triggers for this functionality: ` CREATE OR REPLACE FUNCTION update_created_updated_column() RETURNS TRIGGER AS $$ BEGIN NEW.created = now(); NEW.updated = now(); RETURN NEW; END; $$ language 'plpgsql';

CREATE OR REPLACE FUNCTION update_updated_column() RETURNS TRIGGER AS $$ BEGIN NEW.updated = now(); RETURN NEW; END; $$ language 'plpgsql';

CREATE TRIGGER "users_oninsert" BEFORE INSERT ON "users" FOR EACH ROW EXECUTE PROCEDURE update_created_updated_column(); CREATE TRIGGER "users_onupdate" BEFORE INSERT OR UPDATE ON "users" FOR EACH ROW EXECUTE PROCEDURE update_updated_column(); ` Similar could be done for "delete" to move the deleted row to a "users_deleted" table, so no extra condition would be needed for queries. (just to note, now() will be the transaction time)

[anttilinno]

Can this boilerplate code be automated with migrate tools etc? Also, I'm not very sure, how trigger cascading is handled in postgres and how performance effective it is, when updating large data sets.

To my lazy eye, it adds up as an awful lot of boilerplate lying around, not to mention one has to be very consistent, as future changes can be very painful.

But thank you anyway for your response, very kind of you 😄

[holdfenytolvaj]

There is no magic in the world, actually this is the only way to implement these timestamp columns, if it is handled in the code then it is very shaky (e.g. for special queries or manual updates) and might be slower (e.g. psql above only request the current time from the machine once per transaction even if millions of rows are updated). MySQL's DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP column definitions just creating these kind of triggers in the background (so basically doing the same). This thing only needed to be added once at table creation wherever you needed. Might looks scary but at least you know what is in the background (and actually i would say this is more future safe then "hidden"/"automatic" solutions).

But yes, there is no recepie that taste best for everybody, I understand. I'm happy if it was any helpful :-)

[anttilinno]

Seems there is a trade off between ease of use and reasonable trust level. Eloquent comes to my mind. Should be large enough user base, that problems with consistence would creep through.

Just my last 2 cents 😄

[holdfenytolvaj]

Unfortunately large user base is not guarantee for anything :/ it's a real trade-off :) see e.g.: http://laravel.io/forum/05-20-2015-created-at-and-updated-at-values-not-set-when-seeding-from-csv

and https://github.com/laravel/framework/blob/4.2/src/Illuminate/Database/Eloquent/Model.php you can see the implementation looks slower (it creates a time object for every record, that probably request the current time + beside that, doing many dynamic lookups): (Multi update is probably better, since it even has a warning on it - https://laravel.com/docs/4.2/eloquent#insert-update-delete)

protected function updateTimestamps() { $time = $this->freshTimestamp(); if ( ! $this->isDirty(static::UPDATED_AT)) { $this->setUpdatedAt($time); } if ( ! $this->exists && ! $this->isDirty(static::CREATED_AT)) { $this->setCreatedAt($time); } } public function freshTimestamp() { return new Carbon; }