Joomla! 2.5 with PostgreSQL support officially released

Joomla, one of the world’s most popular open source content management systems (CMS) used for everything from websites to blogs to Intranets, today announces the immediate availability of Joomla 2.5. Along with new features such as advanced search and automatic notification of Joomla core and extension updates, the Joomla CMS for the first time includes multi-database support with the addition of Microsoft SQL Server. Previous versions of Joomla were compatible exclusively with MySQL databases.

Way to go Joomla! But why don’t you guys mention PostgreSQL database in the main release story? Do you really think that MSSQL is more common choice for the database layer for any CMS? Seriously?

UPD Apparently I was a little rushed. PostgreSQL and Oracle support must be added in the shortest time. Waiting for explanations from Joomla!

 

Why you cannot create table and PK constraint with the same name

Very interesting message appeared on the pgsql-bugs@postgresql.org list today:

When I do this

CREATE TABLE "T1"
(
"T1_ID" bigint NOT NULL,
CONSTRAINT "T1" PRIMARY KEY ("T1_ID" )
);

I get the following message:

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "T1" for table "T1"
ERROR: relation "T1" already exists
********** Error **********
ERROR: relation "T1" already exists
SQL state: 42P07

It does NOT create either the table or the constraint, and the message is confusing because there is no relation by that name.

The SQLSTATE 42P07 is described in the manual as only as “table undefined”, and it is not clear if the intent is to allow or
disallow the creation of a constraint called the same as the table in Postgresql. Oracle 11g allows this, but my feeling is that
doing this should not be allowed, just as Postgresql handles it.

I am complaining about the confusing error message which IMO is off-topic, not about how the DB handles this.

The quick answer is PRIMARY KEY constraint always has underlying system index with the same name. Thus to implement CREATE statement above PostgreSQL should create table with the name “T1″ and the index with the same name. This is impossible, because tables and indexes are stored in the same system catalog pg_class (they share the same namespace). That is where ambiguity appears. The same is true for UNIQUE constraint.

On the other hand you may freely create CHECK constraint under such conditions:

CREATE TABLE "T1"
(
"T1_ID" bigint NOT NULL,
CONSTRAINT "T1" CHECK ("T1_ID" > 0 )
);


 

Determination of a leap year in PostgreSQL

From Wikipedia:

A leap year (or intercalary or bissextile year) is a year containing one extra day (or, in the case of lunisolar calendars, a month) in order to keep the calendar year synchronized with the astronomical or seasonal year. Because seasons and astronomical events do not repeat in a whole number of days, a calendar that had the same number of days in each year would, over time, drift with respect to the event it was supposed to track. By occasionally inserting (or intercalating) an additional day or month into the year, the drift can be corrected. A year that is not a leap year is called a common year.

So to determine whether a year is a leap year or not in either the Gregorian calendar we need to check such condition:

(Year mod 4 = 0) AND ((Year mod 100 != 0) or (Year mod 400 = 0)), where mod is modulo operation

This dirty query does the trick then:

SELECT c.y, (c.y % 4 = 0) AND ((c.y % 100 <> 0) OR (c.y % 400 = 0))  FROM generate_series(1582, 2020) AS c(y)

I prefer to have function for this task. First I thought it must be written in plpgsql to be fast enough. Yeah, I’m to lazy for C functions. However after tests I saw that SQL function has the same productivity. Don’t know why. Here the sources for both of them:

CREATE OR REPLACE FUNCTION isleapyear(year integer)  RETURNS boolean AS 'SELECT ($1 % 4 = 0) AND (($1 % 100 <> 0) or ($1 % 400 = 0))'  LANGUAGE sql IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION isleapyear(year integer)  RETURNS boolean AS 'BEGIN   RETURN (Year % 4 = 0) AND ((Year % 100 0) or (Year % 400 = 0)); END'  LANGUAGE plpgsql IMMUTABLE STRICT;

P.S. Some funny traditions:

• On the British isles, it is a tradition that women may propose marriage only on leap years.
• In Denmark, the tradition is that women may propose on the bissextile leap day, February 24, and that refusal must be compensated with 12 pairs of gloves.
• In Finland, the tradition is that if a man refuses a woman’s proposal on leap day, he should buy her the fabrics for a skirt.

 

Holiday discounts on pgmdd & postgresdac

Holidays came suddenly. I know we’re little bit late with Christmas presents, but who cares anyway!

Everybody is welcome to use “Christmas” coupon code till January 14 for 20% discount on any product from MicroOLAP Technologies including the most robust Database Designer for PostgreSQL and PostgresDAC component suite.

From all of us at MicroOLAP, we wish you happy holidays and a prosperous new year!

 

Database Designer for PostgreSQL 1.8.2 released

Database Designer for PostgreSQL is an easy CASE tool which works natively under Windows OS family and Linux under Wine/WineHQ.

This is mostly bug fixing release. You should update your version of database designer as soon as possible.

You’re welcome to download the Database Designer for PostgreSQL 1.8.2 right now at:
http://microolap.com/products/database/postgresql-designer/download/

Full changelog:

[*] “ALTER TABLE SET WITH OIDS” support added to Modify Database engine
[*] Functions body is now printed as is without leading tabs
[-] “Cannot add WHERE predicate for index in Table Editor” bug fixed
[-] “Evaluation type for stored routine is always VOLATILE after Reverse Engineering” bug fixed
[-] “Find & Replace functionality is broken in SQL Executor” bug fixed
[-] “Model repair doesn’t work until table coloring option is selected” bug fixed
[-] “Modify Database ignores unchecked Grant Privileges option sometimes” bug fixed
[-] “Modify Database using incorrect syntax for ADD CONSTRAINT if tablespace specified” bug fixed
[-] “Object Tree View doesn’t refresh Roles and Tablespaces count after changes” bug fixed
[-] “Privileges are lost for schemes after editing in Schema Manager” bug fixed
[-] “Privileges are lost for schemes after model saving” bug fixed
[-] “Role name must be quoted in CREATE DATABASE statement” bug fixed
[-] “Semicolon needed after REVOKE statement in Modify Database script” bug fixed
[-] “Some functions may have the same ID” bug fixed
[-] Bugs in Privilege Manager fixed

Please don’t hesitate to ask any questions or report bugs with our Support Ticketing system available at
http://www.microolap.com/support/