Generating model files

With Pomm, the database is the reference. Model files are generated by introspecting your database schemas. Generated model classes will have their namespace set upon database schema name. The following script generate the model files in the /tmp/YourDb/YourSchema directory.

Retrieving data

This is just a simple example of how Pomm makes things simple. Imagine you have such structure in the database:

Note the array of varchars for the authors field. Let's fetch a single result and see how it looks like in PHP:

Here is the result:

The types are converted into their PHP equivalents. The authors field is an array of strings, timestamps are turned into a php DateTime instance. This will also work with almost all basic geometric types, interval, hstore, ltree and pg objects.

findWhere

Assumed you have a power_supply_transformer table in your database in the online schema, a PowerSupplyTransformer model class will be generated with the namespace "YourDb\Online". You can directly retrieve PowerSupplyTransformer instances from the database:

Query builder

Now imagine you have a car table in the garage schema and you want to query for any sport cars with a given set of colors. You can extend the Car class using Pomm's Where class to dynamically build the where clause of your query:

The controller is as simple as:

You might have noticed this is not optimal since SQL proposed the IN clause. We can simplify the algorithm while making it faster:

Extending objects

With Pomm it is possible to extend objects by adding new fields to them using the SELECT clause. These fields will be accessed like any other 'existing' field but will not be saved by UPDATE. Here is an example that add to all blog Post instances the distance in days since the post has been created:

Of course there is another method getGroupByFields and even a getRemoteSelectFields to use with filters (see below).

Converters and Types

As Pomm scans the database to create model files, it knows the type for each column of your schema. For convenience, Pomm automatically converts some Postgresql's types to their PHP equivalent. A boolean in Pg will be a Bool in PHP, a timestamp in Pg will be a Datetime instance in PHP etc ... Some types like "circle" or "lseg" do not have a native equivalent in PHP, Pomm proposes Circle and Segment PHP classes with their according Converter class. Of course, it is possible to create you own converters and types. Here is an example:

An electrical transformer has two or more wirings (one primary and one or more secondaries) each can withstand a maximum voltage and current. A transformer_winding custom type is defined in the database, allowing the power_supply_transformer table to declare an array of them in the secondary section:

Here are the data type and the Converter classes used:

The converter must be registered to the Database instance. It is possible to declare your own database that extends Database where your application's needed converters would be automatically registered at instanciation. Each PowerSupplyTransformer instance now provides you with an array of WindingPowerType when acessing the "secs" attribute:

Object queries

In Postgresql, defining a table is also defining a type. This means you can query for table records fetching them as composite records not to say objects. This is very useful because we can aggregate them in arrays ! Let's play with a ss_object table that contains some objects of our solar system being almost all of them being a satellite of another solar system object:

You can find a dump here with data and structure.

How can we display the list of all objects orbiting the sun with their own satellites ? The dummy answer is to iterate on objects is_satellite_of = 'soleil' and then to issue another query on objects that are satellites on these objects. This is very awkward as it multiply the number of SQL queries issued to the server and set a lot of processing in the PHP layer. Let's use objects instead:

We use here the array_agg aggregate to set all the satellites in an array of ss_objects. It is just a matter of minutes to settle that query in a SsObjectMap class:

In the code above, the fields methods are used to let our query as generic as possible. To use this model class, let's write a simple CLI application:

As soon as you see the results, you will want the satellites to be sorted by distance and, why not, display their position. window functions are a perfect hit for this. Let's use a subquery using Postgresql nice WITH syntax:

This query looks more complex but it isn't really more complex. A subquery named ss_satellites returns all the ss_object records with an extra field: ranking. Ranking is set by calling a window function that returns the row number partitioned by which object they gravitate around and numbered by ascending distance.

The SELECT query is almost untouched. If you implement it in the SsObjectMap class, you may want to display the ranking information. Of course, it is returned by the query in the ss_object composite form. The problem with the composite form is we loose the column names ! Composite attributes not being defined in the object structure are then stored under a _extra attribute: