asfernandes
This blog has a new home: https://asfernandes.github.io

Introducing the boolean datatype

Posted In: . By Adriano

The support for a BOOLEAN datatype was the third most voted feature in the Firebird tracker. The deal with it was about make it right and complete, having booleans expressions allowed where values are expected, and vice versa. Oracle did it wrong and they BOOLEAN is crap.

Yesterday I finished and committed this support for Firebird 3. You can use them like any other type. You can index it, aggregate by it and sort by it. If you have an index on a boolean field, you can also do indexed searches by its negated form (NOT field).
Three new literals are introduced: FALSE, TRUE and UNKNOWN (same as NULL).
Booleans are not implicitly convertible to any other datatype. But it's convertible to/from strings with CAST.
It's allowed to test booleans without compare with TRUE or FALSE. For example, "field1 OR field2" and "NOT field1" are valid expressions. It's also allowed to compare with others operators, including the new IS operator: "field1 IS FALSE".
Currently it has a terrible and bad hack. The new syntax would introduce a lot of parser conflicts related with trigger's INSERTING, UPDATING and DELETING expressions. These boolean expressions was non-reserved words and allowed to be used as column and variable names. The correct solution shall be to reserve these words and do not allow them to be used as names without double quotes.
As usual, this causes conflict opinions. I don't want to fight forever on this, so I make them work as triggers keywords in boolean expressions and as values in non-booleans expressions. This is much very compatible with previous versions, but is very confusing. For example, in "SELECT inserting, NOT inserting FROM test WHERE INSERTING and INSERTING IS TRUE" the INSERTING's are recognized as value, keyword, keyword and value.
I hope this clears people's minds and we have a better solution in the final version.

 
Up?
3

Problema com Java 1.6.0_22 e NF-e de SP

Posted In: , . By Adriano

Tivemos um problema, em ambiente interno de testes, com os webservices da Nota Fiscal Eletrônica do estado de São Paulo. O sistema estava sendo migrado de sistema operacional HP-UX (com Java distribuído pela HP) para um novo servidor com Red Hat Enterprise Linux (com Java da Sun Oracle).

Ao acessar os webservices a aplicação congelava ou dava erros de "read timed out". O primeiro passo foi verificar se o servidor estava acessando a internet normalmente. Através do elinks era possível acessar sites via http e https normalmente. O webservice retornava corretamente o erro de falta de certificado cliente.
Depois tentei fazer conexões pela aplicação adicionando "?WSDL" às URLs. Nem o ambiente de homologação nem o de produção da NF-e funcionavam. Outros endereços http funcionavam normalmente pela aplicação. Pra debugar o problema, adicionamos a opção -Djavax.net.debug=all. A requisição era enviada mas não vinha a resposta.
Resolvi fazer o teste com os webservices de contingência do SCAN e funcionou. Analisando os logs da conexão, resolvi procurar sobre o termo no_renegotiation que estava aparecendo quando acontecia o erro.
Esta página esclareceu o problema, que foi causado devido a uma alteração no Java para prevenir uma vulnerabilidade do protocolo TLS. Adicionamos as opções -Dsun.security.ssl.allowUnsafeRenegotiation=true e sun.security.ssl.allowLegacyHelloMessages=true até que o ambiente de SP seja atualizado.

 
Up?
0

Firebird 2.5 Introduces New Audit Features and Improved Scalability

Posted In: . By Adriano

October 4, 2010

The Firebird Project today announces Firebird 2.5, the fifth and newest major release of its open source relational database management system. Enhancements in this new release are pitched at better serving the needs of businesses of any scale, from embedded device to enterprise resource planning solutions.

Philippe Makowski, President of the non-profit Firebird Foundation observes that the Firebird 2.5 release is a very important step in Firebird's 10th anniversary year, not just for the Firebird Project but for the whole open-source world, too.

"Today businesses all around the world are looking for mature, cost-effective solutions and Firebird 2.5 offers them a solution that is truly powerful and truly free", he noted.

The mix of high performance, small footprint, supreme scalability, silent and simple installation and royalty-free deployment make Firebird a highly attractive choice for all types of software developers and vendors. Firebird deployments are well established around the globe, serving data to hundreds of thousands of business systems with hundreds of users and databases exceeding 300GB.

Known Firebird installations number more than 2,000 each day, according to download statistics.

"Many of our customers have Firebird databases with sizes from 200GB to 400GB and they keep growing", said Stewart Spink, CTO of Watermark Software. "The performance improvements in Firebird 2.5 will ensure that their future demands will be satisfied completely."

Alexander Shaposhnikov, CIO of medical distributor Profitmed predicts that Firebird 2.5 will support the growth of Profitmed's business. "Currently we have a 65Gb database and 250 active users working 24x7. We intend to double both the size of the database and the number of users in two years and we have chosen Firebird 2.5 for this business-critical task."

Firebird binaries are distributed for a variety of system and hardware platforms: Windows, Linux, MacOS, Solaris, HP-UX and more. Firebird runs on on x86, x64, PowerPC, Sparc and other hardware platforms, providing an easy migration mechanism between these diverse platforms.

The V.2.5 release binaries ship for 32-bit and 64-bit Windows and Linux, with MacOSX x86 to follow. Other POSIX platform builds are likely to appear before the year is out, in response to demand.

New features of Firebird 2.5

  • New SuperClassic Architecture Firebird 2.5 introduces a new architecture, tagged "SuperClassic", to gain better leverage from multi-core and multi-CPU hardware environments and improve resource usage for systems with large numbers of users and huge databases.
  • Audit System audit tracing and user trace sessions via the Services API enable nearly real-time monitoring and analysis of everything going on in a database
  • Cross-database queries Firebird 2.5 opens the way for Firebird database instances to query one another and to exchange information
  • Enhanced user management User management becomes accessible and flexible through SQL requests submitted from user databases
  • Other features include autonomous transactions within PSQL modules (stored procedures, triggers, SQL blocks), support for regular expressions as arguments in SQL using the SIMILAR TO predicate, asynchronous cancellation of connections, enhancements of monitoring capabilities and much more.
Paul Beach, President and CEO of IBPhoenix, an organisation that offers professional services and contributes to Firebird development, considers the new security features and the impressive scaling capability of Firebird 2.5 will be welcomed by many existing Firebird users.

"In particular, it will help those in the finance and health industries to meet the growing requirements of their businesses", he notes, "especially in pushing the limits of performance."

David Wilder, CTO of Bas-X, welcomed the Firebird 2.5 release for its improved useability and flexibility. "It will enable our customers to turn over more transactions and run more concurrent users on the same hardware," he said.

Pricing and Availability

Firebird 2.5 is an open source database system that is available free of charge for any kind of usage, be it commercial, educational, non-profit or simply for private use. Both binary packages and the complete source code can be downloaded at no cost through the Firebird Project website for immediate installation. No registration or activation is required.

About the Firebird Foundation

The Firebird Foundation is a non-profit organization with the goal to support the development and growth of the Firebird relational database system. The Foundation was incorporated in 2002 and is currently supported by more than 300 active members, several of whom are also cash sponsors. It also accepts and manages donations of cash and resources from persons and organisations that are not regular members. The Foundation redistributes these funds as grants to some of the key workers in the Firebird Project. The Firebird Project has no other source of income.

Media Contact: Alexey Kovyazin * +7 910 402 94 34 * admin AT mindthebird DOT com

 
Up?
0

Firebird 2.5 launch

Posted In: . By Adriano

Firebird 2.5 release date is set - it will be October 4th, 2010.

Please join Philippe Makowski, President of Firebird Foundation, and Dmitry Yemanov, lead Firebird developer, at the Launch Webinar devoted to the 5th major release of Firebird.

It will take place at October 4th, 2010, at 13:00 GMT (10:00 pelo horário de Brasília).

If you have any questions regarding Firebird 2.5, please feel free to contact Mind the Bird campaign at launch@mindthebird.com.

 
Up?
2

Firebird 10th anniversary. How I came to it.

Posted In: . By Adriano

July 31th, 2010. Today Firebird completes its 10th anniversary. The Firebird website has a set of history documents about how that started. It’s something you must read if you’re interested in Firebird.

I’m sometimes asked about how I became one of its team members. So I’m going to tell something about this.

First things first. My first contact with InterBase was with the one present in the Delphi 3 CD. Searches says it was InterBase 4.2. I must say I didn’t like it too much. But my main problem at the time was with SQL. Not with its syntax, but with the idea of manipulating a database with text commands, and not via an API in a programming language. Fortunately, I was a child and grew up since then. :-)

The second important thing I remember about InterBase was about it becoming open source. This was something I liked, but just forgot soon later.

Then sometime later I came to the Info download site and it had Firebird with a text saying something like “Download it, while it’s free”. I downloaded it (the 1.0 version) and saw on its website that there was no plan to make it non-free. I started using and liked it. What most attracted me was it excellent transaction control mechanism.

I then started using the 1.5 RC versions and found a security bug on it. I tried to report on the SourceForge bug tracker without success. So I subscribed myself to firebird-devels mailing list and reported it there. That happened around in December 2003, and I never leaved the list since then. :-)

Lurking at firebird-devel was a challenge. I didn’t understand much English nor the technical talks, but I stayed there reading every message. I started to read the source code and understand the subsystems. I then started following firebird-checkins mailing list, where I could better understand how each feature/fix got implemented.

Around July 2004, I joined the Firebird PT_BR project to adapt for Firebird 1.5 the Brazilian collation done by Paulo Henrique Albanez for Firebird 1.0. I proposed different approach to definitively integrate it in Firebird and it was also rejected. So I got the work to do the “right” changes to the Firebird International Language (INTL) subsystem. In September 2004 I officially joined the project.

What’s more nice about being there is that I can work with very competent team, delivering a good software to millions of people. And just doing something I really like, designing features, programming in C++ and working with compiler (SQL / PSQL) implementation...

Long live Firebird!

 
Up?
2

Window functions (part3) - new functions

Posted In: . By Adriano

In the first and the second part of window function series, I talked about the OVER clause, with/without partitions and with/without ordering. Till then, no new function had been introduced, so it has about the already existing aggregate functions.

Now, new window-only functions has been introduced. I'll separate them in two groups: ranking and navigational. Both set of functions can be used with/without partition/ordering, but them does not make much sense without ordering.

Ranking functions (DENSE_RANK, RANK and ROW_NUMBER): with these functions, one can create different type of incremental counters. Think about SUM(1) OVER (ORDER BY SALARY), these functions does this type of thing, but all of them in different ways. Lets see an example query, also comparing with the SUM behavior.

select
    id,
    salary,
    dense_rank() over (order by salary),
    rank() over (order by salary),
    row_number() over (order by salary),
    sum(1) over (order by salary)
  from employee
  order by salary;

And the result set:

idsalarydense_rankrankrow_numbersum
38.001111
49.002222
110.003334
510.003344
212.004555

As you see, the functions differs when repeated values are found in the order key (the salary of 10.00). With DENSE_RANK, no gaps are created and all repeated values receive the same counter. With RANK, the initial counter is used for all repeated values, but it make gaps so the next non-repeating value (the salary of 12.00) does not consider the previously repeated values as in different positions. With ROW_NUMBER, each line receives an incremental value. And SUM(1) is very like RANK, but the value is computed after all repeated values are summed, so the gap is before the repeated values.

Navigational functions (LAG and LEAD): with these functions, an expression could get the value of a previously (LAG) or a posterior (LEAD) row of the query. Follow the demonstrating query.

select
    id,
    salary,
    lag(salary) over (order by salary),
    lead(salary) over (order by salary)
  from employee
  order by salary;

And the result set:

idsalarylaglead
38.00<null>9.00
49.008.0010.00
110.009.0010.00
510.0010.0012.00
212.0010.00<null>

The functions have a second argument, that is implicitly 1, which means the number of previously/posterior rows will be queried. If this row does not exist, <null> is returned.

And of course, you can use these functions as expressions and compute, for example, the difference of an employee salary from the one greater and/or lesser.

 
Up?
2

Firebird History in 9 minutes video

Posted In: . By Adriano

This is a code_swarm of the firebird2 tree.

 
Up?
2

Window Functions (part2) - cumulative aggregates

Posted In: . By Adriano

Continuing with the window functions support for Firebird 3, I have now added another OVER sub-clause: ORDER BY. The ORDER BY sub-clause can be used with or without partitions, and it makes aggregate functions return the partial aggregations as the records are being processed.


Considering our employee table has this data:

idsalary
110.00
212.00
38.00
49.00
510.00

And we run this query:

select
    id, salary, sum(salary) over (order by salary) cum_salary
  from employee
  order by salary;

The result set produced will be:


So cum_salary returns the partial/accumulated aggregation (of the SUM function). You may found strange the 37.00 repeated for the ids 1 and 5, but that is how it should work. The ORDER BY keys are grouped together and the aggregation is computed once (but summing the two 10.00).

You can use multiple windows with different orders, and ORDER BY parts like DESC, NULLS LAST, etc. Here is a weird example:

select
    id, salary,
    sum(salary) over (order by salary) cum_salary,
    sum(salary) over (order by salary desc) cum_salary_desc
  from employee
  order by salary;

The result set produced will be:

idsalarycum_salarycum_salary_desc
38.008.0049.00
49.0017.0041.00
110.0037.0032.00
510.0037.0032.00
212.0049.00
12.00

With a partition ORDER BY works the same way, but at each partition boundary the aggregation is reset.

All aggregation functions are usable with ORDER BY, except the LIST function. As LIST returns blob, it would be slow to return multiple blobs (one for each order group), trick to implement, and I don't see an usage scenario for it.

 
Up?
9

Window Functions

Posted In: . By Adriano

By the SQL specification, window functions are a kind of aggregation, but which does not “filter” the result set of a query. The aggregated data is mixed with the query result set. That sort of functions are used with the OVER clause. Users of Oracle also knows window functions as analytical functions.

We have promissed very basic support for window functions in Firebird 3, which was the OVER () clause using the current aggregate functions. With the OVER () clause, one can mix with the query result set the aggregated data over the entire result set. Let me explain with an example.

We have a table EMPLOYEE with columns ID, NAME and SALARY, and want to show each employee with his respective salary and the percentage of his salary over the payroll. With a “normal” query, we would do:

select
    id,
    name,
    salary,
    salary / (select sum(salary) from employee) percentage
  from employee;

We need to repeat ourselves and wait so much to see the results. We can also make it hopefully faster using a cross join, but still the whole employee table will need to be read more than one time. Change the table by a complex view or add various “windows” and we'll have a performance problem for sure.

The same query could be specified in much more elegant and faster way using a window function. Here is it how:

select
    id,
    name,
    salary,
    salary / sum(salary) over () percentage
  from employee;

Here, sum(salary) over () is computed with the sum of all SALARY from the query (the employee table). This is what the OVER () clause does.

But the OVER clause is not just that, and now I've added another of its subclauses to Firebird. It's the PARTITION subclause.

A partition is a way to make the OVER aggregation based on a GROUP. Its syntax is:
    <window function>([<expr>]) OVER (PARTITION BY <expr> [, <expr> ...])

So now since the aggregation is done over a group, it could produce more than one row. So the result set generated by a partition is joined with the main query using the same expression list of the partition.

For a example, lets add to our employee table a ROLE column. Now we want the same information, but instead of see the percentage of the employee salary over all employees, we want to see that value based on the employees occuping the same role. Here is how that query could be written:

select
    id,
    name,
    role,
    salary,
    salary / sum(salary) over (partition by role)
  from employee;

With the current implementation, the query (without the window functions) is executed one time and cached. Extra aggregation is done on this cached data. Partitions are joined by the new hash join algorithmn, and the main window (OVER without a partition) is joined through a cross join with the main query.

There is much more about window functions, but this subset seems to cover some major use cases of them.

 
Up?
4
Older Posts | Newer Posts