News

Pomm 2: One year in production
published 9 months ago

The good, the bad, the ugly Two years ago, after few weeks of prototyping based on the 4 previous years of Pomm 1, the first stone of Pomm 2 was laid . Several months later a first beta version was released and some developers, really happy with what they were doing with Pomm, started to use it in production with some of their small projects. Most of these projects are still running right now. One year ago, a production ready version was released...


A short focus on lateral join
published last year

In the last article, focus was made on the full text search capabilities of Postgresql. Because real life businesses are complex, full text queries become complex with lot joins inside CTEs (covered by another previous article ), lateral joins may then be an interesting tool to compute data. Let’s consider the following example: we want to fetch the last measure from meteorology stations: SELECT station.station_id, station...


A short focus on text search
published last year

brute force approach Postgresql proposes several ways for searching text. Of course there is the good ol’ LIKE operator (note that Postgres also offers a ILIKE operator for case insensitive search) Postgresql newcomers may not know it is possible to use regular expressions to search for text patterns using the ~ and ~* (case insensitive) operators: select p.slug, p.title, p.author_id, p.content from blog.post p where...


Pomm 2.0.1 is out, 2.1.0 is on track.
published last year

We are happy to announce Pomm 2.0.1 is now available. This is a maintenance release. There are very few commits between this tag and the 2.0.0. This is a very good news for us. As we have more and more feedbacks from Pomm users, with between 700 and 800 downloads per month, it means Pomm is very stable. Coming with that news, the Pomm 2.0 branch has been forked from master, which means 2.1 developments are on the way. What’s on the menu : PHP...


Pomm 2.0.0 is out
published last year

More than one year after bootstrapping the first lines of code, the complete rewrite of Pomm is now production ready for everyone. So, what’s in Pomm 2 ? Pomm is a PHP database layer dedicated to Postgresql. It provides a different approach from classical DBAL and ORM: make the end developers able to use the database to keep the complexity out of their applications. Pomm offers: a high level API to ease database access and security unrestricted...


Possible regression with result iterator’s methods
published last year

As the release candidate process does slowly converge to stable, there is a minor tweak that may introduce regression on existing applications. Foundation’s issue #60 exposes: empty collections still have their method isLast and isFirst methods to return false. Neither true nor false have sense in this case as there are no records to be returned. Pomm’s code has been modified to have these methods to return null in this specific...


A short focus on Pomm’s Foundation
published last year

As its name indicates, Foundation is Pomm’s main package. It makes developers able to easily manage and use database connections. There are incidentally some comparisons with Doctrine’s DBAL which Foundation may looks like at first glance but Foundation is at opposite corners of what DBAL is even though it can be used the same way. To understand why, let’s focus on one of Foundation’s client: the query manager. (reach TL;DR ). Simple is beautiful...


Pomm 2.0.0 RC 3 is out
published last year

More than one month after the latest Release Candidate, here is the last version before Pomm 2 gets stable. This release comes with an important number of bug fixes and code arrangements. We want to thank all the people who made the effort of submitting patches or reporting bugs. It did lead us to find tricky bugs and performance improvements. Even though Pomm2 is not in a stable state yet, it already empowers several web sites in production...


A short focus on recursive queries
published 2 years ago

In the last article we saw how to perform basic statistical operations in the database. Let’s go on a previous article about CTE and more precisely about recursive CTE. In case you want to try it your side, here is the GIST with the data and the structures.. Be sure you download the file and insert it through a \i psql command to avoid paste & copy annoyances with the COPY commands where data are separated using tabs. What do we...


A short focus on width_bucket function
published 2 years ago

In the last short focus article we saw how to see how many sales were performed during each day of a given period of time. What if now we want to see the distribution of sales upon their prices? What do we want? To see how many sales have a total price between 0-999, 1,000-1,999 etc. up to 10,000? This is where a Postgres’s mathematical function enters the scene: width_bucket . To be short, this function returns the position of each row in...


Tag Pomm 2.0.0-rc.2
published 2 years ago

We are pleased to inform you that Pomm 2.0.0-rc.2 has been released. This is a polishing release before the coming stable version. Add convenient methods Where::createWhereNotIn() method. Improve default session handling. Refactor Pomm’s service code. Refactor Array converter. Fix bugs in deferrable constraints in ModelLayer. Add Model::deleteWhere() method. Show default session in pomm:inspect:config CLI command. Thank...


A short focus on joins
published 2 years ago

After the introduction about the CTE, it is important to understand how sets can be joined together in a single set. All the material needed to play with the following exercise can be found on this gist . There are two tables: one containing sellers an another one containing sales. If you download the gist, be sure to download the RAW file and then import it in a freshly created database using \i in your psql client. Let’s say we want something...


A short focus on Common Table Expressions
published 2 years ago

Common Table Expressions aka CTEs are one of Postgresql's most mysterious feature for SQL newcomers. CTE are introduced by the standard ISO SQL99 and are supported by Postgres since version 8.4. To be short, they replace in a more convenient way nested SELECT statements. This example is about employees of a company divided in departments. If you want to get your hands dirty with this one, here is the SQL file you should use to create a ready to...


A short focus on window functions
published 2 years ago

In this hopefully short article, we will try to understand what window functions are and why since 2003, there are the application programmer's best friend. We will first get through well known aggregate functions to spot the differences between theses functions families. Let's say we have the data about some European factories with production information. See what we can do with Postgres Setup If you are willing to get your hands dirty with...


Speaking at PgDay Paris and PHP Tour Luxembourg 2015
published 2 years ago

We will be happy to meet you either at the Paris PgDay the 21st of April or the PHP Tour in Luxembourg the 13th of May. If you plan to attend the PHP Tour, I will be here from the Tuesday, so it is possible to prepare a coding session at the end of the day with a drink. Because a day full of PHP is a way more cool with a touch of Postgres on top ! See you there ! ...


Pomm2-RC1 is out !
published 2 years ago

It is a tremendous news for us. After 6 months of hard work, we are thrilled to announce an almost complete rewrite of Pomm is ready to be used for production purposes. This is an important step for us because this first release candidate means we freeze the features in Pomm2, so what is in the menu ? What's new since beta2 ? Embedded entities and composite types converters did appear. Because with Postgresql, creating a table means creating...


Changes in Pomm2's projection system
published 2 years ago

The better: the change Programmers books call that «the difference between theory and real life». The projection as proposed by Pomm 2 until now was powerful but yet simplistic. By example if one wants to extend a projection with the information of the age of students in the table is odd or even: $ projection -> setField ( ´ is_even ´, ´ cast(extract(year from age(%birthdate)) as int) % 2 = 0 ´, ´ bool ´ ) ; $ sql ...


Pomm 2.0.0 beta 2 is available
published 2 years ago

We are happy to announce that Pomm 2 beta 2 is available. This new step in Pomm 2 developments is based on numerous feedbacks we had regarding the last release. First, the flexible entity system has been entirely refactored so one can choose whether or not to use the FlexiblEntity provided with Pomm or to create his own. As we had queries to interface better with JmsSerializer, we wanted a nice way to make the model manager use different kind...


2015 is starting with good news
published 2 years ago

The last two weeks have been busy for us to make sure we would be able to announce good news at the very start of 2015. The first one being the talented Nicolas Joseph being part of Pomm team. The second awesome news is that we do now have a bundle for the Symfony framework. We did a lot of rework around Pomm profiler already present in Silex, so the profiler is available de facto with the bundle. The last tremendous news is the profiler is...


Release: Pomm 2.0 beta 1
published 2 years ago

We are thrilled to begin the stable release process of Pomm2. This version is almost a complete rewrite of Pomm but still shares the exact same philosophy: Data structure is defined in the place it is the more constrained: the database. Entities are flexible containers and know nothing about databases. There will be no query builders (but there is a condition builder). SQL is first class citizen. The idea behind Pomm took place in...