The Firebird Conference

Artur Anjos

Opening Session

Firebird Foundation and Friends, welcoming festivities.

Milan Babuskov

• http://njam.sourceforge.net
• http://abrick.sourceforge.net
• http://scalar.sourceforge.net

FlameRobin - administration tool for Firebird DBMS

FlameRobin is light-weight cross-platform Firebird administration tool built entirely with open source software. This session will cover the project's goals, history, and FlameRobin's features. It will also show some specifics of the tool, and ways to use it efficiently and effectively.

The power of Firebird events

Events are one of the least known features of Firebird. This session explains basics of event functionality, and then explores various ways to use events and possible configuration problems and issues. It also shows the possibility to use Firebird in an unusual way: Firebird acting as a chat server.

Paul Beach

Firebird future development: what to expect
(followed by)
Meet the Firebird Team for Drinks

Paper by Dmitry Yemanov

A significant amount of improvement has been done in Firebird 2.0, but much more is to come. An updated draft roadmap, covering at least two next major versions, will be presented. You'll also know about our v2.0 maintanence program plans and what fixes you could expect to see backported from the development CVS branch. You'll see what's already implemented and being tested for V3.0 and how Vulcan architecture and code improvements fit this release.

Then, a foresee of our long-term goals will be outlined. We'll talk about what various kinds of users want most of all and expect to see in the future versions and how we're going to address their needs. Some feature and performance comparisons with our competitors will be presented. If you ever dreamed about temporary tables, security and statements monitoring, more powerful and ANSI-compliant SQL language, better performance, etc then you have a great chance to learn about our plans and discuss them with the development team members.

Jeanot Bijpost

From Model Driven Development to Model Driven Architectures

This session is an introduction to Model Driven Development (MDD). During the session the major principles will be explained and demonstrated. The session will conclude with a brief introduction to Model Driven Architecture (MDA). I hope this session will inspire you to consider MDD and provide you with some basic knowledge to understand MDD/MDA tools.

Session outline

Model Driven Development (MDD)

• The basic principle.
• Different models: information/database, logic/code, presentation/interface.
• Transforming models into a working application.
• Code generation v.s. interpretation.
• Mixing program generation with custom code.
• Single point of definition / Separation of layers.
• (Dis)advantages.

Model Driven Architectures (MDA)

• The basic principle.
• MDD compared to MDA.
• Claims vs. reality.

An introduction to Cathedron

This session will introduce the alpha version Cathedron. Cathedron is a design and development environment based on the principles of Model Driven Development (MDD) and targeted specifically at Firebird and InterBase.

Mattic Software has been using Cathedron as their internal development tool for the past six years. Using this tool we have developed large ERP/Workflow systems with over 60 concurrent users distributed over several locations. In November 2004 we tested Cathedron during the RAD Race 2004 competition (and won the competition). This gave us the courage to present Cathedron to the Firebird community during this conference.

Most desktop databases are shipped with a development environment. These environments allow you to easily create an application on top of your database. When users switch from a desktop database to Firebird/InterBase they are confronted with both a new (serious) database and with significantly more complex development environments such as Delphi or Java.

Cathedron might fill the gap between development environments such as Ms Access and Delphi. With little training Cathedron can be used to develop simple applications. When the workload or demands grows, desktop databases generally collapse. Since Cathedron is based on a solid database and on the solid principles of MDD, it will stand and simply scale. When demands grow even further Cathedron can be fully integrated with Delphi to profit from the best of both worlds.

For senior developers Cathedron can be a relief. You can now focus on the structure of the database (the model) and generate prototypes and fully equipped ‘standard’ interfaces in a matter of minutes. When the application evolves the advanced logic fits in nicely and the environment supports you in more complicated matters such as deployment, bug tracking, maintenance and server side processing.

The first public release for the Windows platform is planned for September 2006. We hope Cathedron will prove to be ‘the easy yet solid development environment for Firebird and InterBase’. Furthermore we hope you will share your opinions, comments and ideas with us after this session.

BOF Convenor

Birds-of-a-Feather Session 1

Slot reserved for one or more special interest sessions convened at the conference.

Birds-of-a-Feather Session 2

Slot reserved for one or more special interest sessions convened at the conference.

Birds-of-a-Feather Session 3

Slot reserved for one or more special interest sessions convened at the conference.

Arno Brinkman

Understanding the Optimizer I

This is the first of two sessions, covering the following topics:

• What is selectivity and what does it say?
• What is the execution plan?
• Explain the Firebird 2 changes in the optimizer.
• Explain how the execution plan is choosen by Firebird 2, such as how decisions are made between different available indexes.
• What are the benifits of the new index structure in Firebird 2?

Understanding the Optimizer II

This is the second of two sessions, covering the following topics:

• What is selectivity and what does it say?
• What is the execution plan?
• Explain the Firebird 2 changes in the optimizer.
• Explain how the execution plan is choosen by Firebird 2, such as how decisions are made between different available indexes.
• What are the benifits of the new index structure in Firebird 2?

Under the hood: Data access paths

Paper by Dmitry Yemanov

Access paths are ways in which data is retrieved from the database. There are a number of ways to access tables, perform joins, and so on. For example, index access paths are generally used for statements that retrieve a small subset of table rows, while full scans are more efficient when accessing a large portion of the table. The optimizer is responsible on selecting the best possible access path under every circumstance.

This topic describes the data access paths that Firebird can use to locate and retrieve any row in any table. We'll also outline related changes done in Firebird 2.0 and ODS 11.0. This material is intended to explain the internal algorithms used by the engine, without any deep review of the optimizer logic, although every examples are illustrated with plans and detailed textual representation of access graphs. You'll also see comparisons against other database engines and some notes dedicated to possible future improvements.

Lester Caine

Firebird on PHP – Integrate or Abstract

What is PHP? Probably the best kept secret on the internet. OK it is not a secret, but as with MySQL, the bulk of users don't even know that they are using it. One of the major hurdles for Firebird is the fact that everybody supports Apache/MySQL/PHP and almost every major PHP information site will not include a project unless it includes MySQL support. We will skip the reasons why MySQL is the wrong solution to the problem, and why developers prefer PostureSQL for PHP development. That is a subject for another paper. Since Firebird *IS* the best database, how do we use it with PHP.

While there are several methods of building PHP, they all build on a modular approach, and the Interbase module works fine with Firebird, but already has hooks to allow a split for a Firebird specific version. The main problem with this approach is that every database engine has it's own set of functions, and while PHP5.1 will include a first attempt at an integrated package, with it's PDO module, the differences between how these engines work makes a common interface difficult. ADOdb is one of a number of add-on's that allows a more generic design of SQL interface, and PHP applications that have been built using it can easily be ported to Firebird if that has not already been implemented.

Ready Packaged Firebird on PHP

Searching for PHP applications that are enabled for Firebird shows exactly why Mozilla's hijack of the Firebird name was such a major problem. Even today a simple search for 'Firebird PHP applications' will produce many browser related results. So what is available and what do they do.

ibWebAdmin has to be the starting point, as it provides an on-line Firebird management tool. It is a Firebird specific alternative to MySql and PostgreSQL web based tools. It provides access to databases via the normal SQL interface, but in addition wraps those command line tools that it can access in a web based front end. The demo site running ibWebAdmin into the Employee sample database also provides an archive of useful location data which can be accessed via the interface.

Bitweaver is probably not a name you are familiar with but having invested a lot of time in it, it provides a tidy framework for other numerous web applications. Having spent much time trying to find a suitable basis on to which to port my own legacy applications, progressed to a framework called TikiPro, which was it's self a port of the more popular TikiWiki content management system. Style wars are just as prevalent in PHP as they are in C++, and so finding a style you are happy with is just as important as the language one is working with. The bitweaver framework is built using ADOdb and Smarty , and does not have any of the MySQL based problems of other systems.

The whole essence of PHP is the free interchange of code between projects. Many core functions have several solutions available and so if you find that the the one you are using is not giving you the results you want, then someone else has probably already produced an alternative. The nice thing about web based applications is that you do not need to worry about the problems of deploying them onto customer machine. As long as they have a browser they can access Firebird.

Carlos Cantu

PSQL in Action

In this talk I will show 4 practical examples using Firebird's PSQL language in Stored Procedures, Triggers and Events.

The first example will use a "select stored procedure" to correct the stock quantities of products, based on the product movement. It will also detect and list the products with problems in the movement, leading to an incorrect negative stock. Good for validating and fixing data imported from old systems running Paradox or other desktop databases.

The second example will use a "select stored procedure" to build a cash-flow report, based on credits and debits from 2 diferent tables, with a running balance.

The third example will use triggers, procedures and events to simulate an auto-refresh of data in Delphi applications, in a way that people connected to the same database in different machines will always see the most updated data on the screen, without worrying to click the "refresh" button. Some techniques will be used to avoid unnecessary refreshes and so minimize network traffic.

Fourth example will show how to use recursive stored procedures to make your life easier. The example will show how you can get a full list of components and subcomponents (and their quantities) that compose a major product, for example: a computer is composed by many items: HD, CPU, motherboard, screws, etc. A recursive stored procedure of less than 35 lines can give you the list and quantity of all items needed to mount diferent configurations of a computer, with almost unlimited levels of nested subcomponents.

People watching this session must be a little bit familiar with the PSQL statements.

Pavel Cisar

Firebird Quality Assurance

Level: Beginner

The Firebird project takes Quality Assurance very seriously, and this session will give you an insight how the project's QA department has worked in the past, how it works now and what further improvements to the procedure and tools are planned.

You'll also learn about QA tools and routines used for various QA tasks, how to report issues in most useful way and how to get involved in Firebird QA.

• Introduction to QA in Open Source project
• QA methods used by Firebird project
• Automated testing: running the test harness and writing new tests
• How to participate in Field testing
• How to report bugs and other issues

Making your own Firebird PowerTools with Python

Level: Beginner

There are many powerful Firebird administration tools out there, but they all have one thing in common: They are point and click oriented. While GUI tools are great for beginners and for certain tasks, they fall short when you need to perform more complex, not so common or batch-oriented tasks. Some users know that many such tasks could be handled by the dated, yet still useful QLI tool. But QLI is neither actively maintained or well documented, and also has its weaknesses. This session will show you how the Python programming language was used to build a brand new "QLI on steroids", and how you can use it and extend it.

• Why GUI tools are not so great, and when CLI tools shine.
• Why Python was the best choice for PowerTools development.
• PowerTools architecture and implementation.
• How to use PowerTools.
• How you can extend PowerTools.

Nando Dessena

Since last April Nando has run his own software company, which develops and markets software produced through an application framework that mainly uses Firebird as the data storage component. The company is called Ethea (www.ethea.it).

Nando is a member and past committee member of the Firebird Foundation; he is also a contributor and project administrator for the open source projects FlameRobin (www.flamerobin.org) and InstantObjects (www.instantobjects.org).

Using Delphi and InstantObjects with Firebird

This session is about using the open source Object Persistence Framework (OPF) InstantObjects (IO) for Delphi/Kylix/FPC together with Firebird. I will be briefly introducing the concept of an OPF (although, due to time constraints, attendees will be expected to have done their homework about this topic already *), then I'll do an overview of the IO features, make feature and performance comparisons among the different connectivity options and show some Delphi code examples.

Target audience and prerequisites:
Delphi Win32 developers (anything from Delphi 5 upward will do, except Delphi 8 for .NET) that already know Firebird and have an understanding of OOP and an idea of what is an OPF and what it does.
Reference: http://www.ambysoft.com/essays/persistenceLayer.html.
Having already played with IO is a welcome plus but it will not be required.

Outline:

• Short introduction of myself.
• What is an OPF and why using it in Delphi in place of more traditional tools.
• InstantObjects as an OPF (with emphasis of its support for Firebird).
• Examples of using InstantObjects and Firebird: database structure and Delphi code snippets for common operations like querying, object storing, cloning, etc.
• Summary: drawbacks of the OPF approach (very limited support for transactions, locking, server-sice programming, etc.) and assessment of the typical application kinds that benefit from an OPF and of those that don't.
• Questions and Answers.

Deploying Firebird transparently on Windows

Firebird is widely used as an "embedded" database engine, not only in the sense of fbembed but often as a full server deployed together with an application (or an application server, in a hybrid C/S and three-tier scenario). This session describes a real-world success story of an application that deploys Firebird on Windows in a way that doesn't conflict with existing installations of Firebird and InterBase, with the goal of transparent installation. I'm going to briefly describe the related problems and possible solutions, and show Delphi code examples.

Target audience and prerequisites:
Windows developers (examples will be in Delphi, but all the concepts are language-independent) that need to deploy Firebird together with their applications (or application servers). Some knowledge about firebird.conf and setting up Firebird in general is welcome.

Outline:

• Short introduction of myself.
• The problem: installing an "invisible" Firebird instance together with an application (or server application), avoiding as much as possible to step onto other InterBase/Firebird installations' feet. The application's architecture needs the instance to be a full server: using fbembed.dll would be just too easy.
• The solution, a real-world case.
• Setting up an isolated Firebird environment:
  • Deciding on a TCP port, or finding a free one at installation time.
  • Making Firebird locate its home directory.
  • Installing Firebird as an application (and not as a service); starting and stopping it from the application server that "embeds it".
• Advantages of the solution: transparent installation possible, less client-side configuration necessary, ability to tune every server-wide Firebird option to suit the application's needs.
• (as time allows) Additional options (features of the real-world application, not required but made possible by the solution):
• Centralizing every aspect of the client connection (credentials, list of databases and connection strings are all provided by the application server).
• Upgrading Firebird (both server and client library) together with the application server.
• Database backup and restore, database health check (validation) managed by the application server.
• Using the application server as a Firebird Guardian.
• Questions and Answers

Vince Duggan

Using Firebird in a low bandwidth, widely distributed environment

We have 78 HealthClubs all over South Africa. They are connected in a WAN with, for the most part, 64K links. We have had to be fairly imaginative in the way we work in order to keep the clubs data access speeds reasonable.

We have two primary databases at Head Office, each between 20Gb and 30Gb in size. We replicate about 12Gb of the largest database to the clubs in order to give them local access to data.

Certain of our apps write data to the Head Office database, but most of the reading is done from the club's local version.

Our Head Office Database runs under Solaris, while the clubs are on Linux. We have written several Gpre/Api apps which we use to extract data, compare to what the clubs have, and move data around appropriately. We then re-build the database at the clubs each night.

We also retrieve a fair amount of data from each club each night to update the Head Offcie Databases using a similar process.

Biometrics: Fingerprint authentication in the real world

We store all the data in our Firebird database. Fingerprint identification is currently fairly topical, and we have potentially 400000 members who access our clubs. We have piloted fingerprint identification in one club, soon to be at a second.

It is fairly well established that fingerprints alone are not sufficient to identify a person in a high security environment (such as Banking). However, for the most part, it does not matter if we have a few errors when identifying a member as they enter the club, especially since they do have an access card to use if the fingerprint does not work.

We are probably the only large scale Health Club in the world using fingerprints for identification purposes, and may in fact be the largest organisation using fingerprints for identification in the world.

Note that there is a difference in using fingerprints for IDENTIFICATION and for VALIDATION. In the latter case, the fingerprint is used to validate that a passord/pin has been entered by the right person, while in the former, the fingerprint alone is used.

Lucas Franzen

Stored Procedures

Originally proposed as two sessions, this talk (which may be in German or English, depending on demand) will be condensed to one session.

A summary of stored procedures.
When and how to use, how to write, compile, debug, pitfalls.

The first part is a general introduction to the concept of Stored Procedures, where I'll talk about the advantages of stored procedures (work done at server, low network traffic, using as blackboxes) and talk about basic syntactic rules, a PSQL overview, and show general situations where to use stored procedures and also the situations where they are not the right choice.

After this general overview of stored procedures, I will begin digging into details, showing how to use recursive / nested stored procedures, working with proper exception handling and possibilities of debugging stored procedures.

The session will be accompanied by a lot of "real life" examples with excerpts from a Firebird Database running at many locations that shows heavy but flawless calculating on the server side.

Firebird Embedded Server

A general overview of the EmbeddedServer, it's possibilities and also its restrictions.

Language: German and/or English

By the means of a simple Delphi application all the advantages of the Embedded Server can be easily demonstrated. Also the possibilities of multiple connections from within one application (for example by the means of a shared handle via a dll) can be shown.

I plan to show the power of the Embedded Server by working with a real life application and a complex database (300+ tables) just on a USB stick.

Mad Auction

Traditional "bridal auction" whereby the Firebird Foundation collects goodies from donors and, using fair means and foul, uses them to help you to part with your money.

It's all for a good cause, though!

Ann Harrison

Firebird ODS and V2 ODS changes

ODS is the On Disk Structure - the format Firebird uses to store records, indexes, and metadata. The Firebird ODS was very stable for the first five years of the project's existence. Version 2 introduces a new index format and several metadata changes.

Ordinarily, programmers and database designers can ignore the way Firebird stores records and indexes completely. Even metadata storage is of interest mostly to interface developers. However, understanding the ODS will help you understand the performance implications of design decisions.

Detecting, correcting, and preventing database corruption

Firebird databases rarely become corrupt or require administrative intervention. Most of the messages that suggest potential corruption are raised when the system detects a problem in memory and the database on disk is uncorrupted. However, when bad things happen, databases can become corrupt physically or logically. This talk describes the differences between messages that indicate a potential problem and the two types of actual corruption: logical and physical. For each corruption type, it describes how each can occur, how to avoid problems, how to diagnose problems, and how to correct problems when possible.

Record versions: consistent read, concurrency control, and undo log

Firebird uses record versions in place of locking and logging. This talk describes how it works, trade-offs, and programming hints for improving performance.

First steps in performance tuning

This talk distills several years of experience in analyzing and identifying performance problems in Firebird. It explains the types of performance degradation that various application developers have encountered, and how to diagnose and correct several different potential problems. It also explains how this information has been incorporated into the planning for Firebird 2 and Vulcan and describes features of both that will eliminate the most common problems.

Fikret Hasovic

Firebird in n-tier setup with Delphi (and kbmMW)

This session will describe 1-tier development using Delphi and fb_embedded, a Delphi framework I developed (OpenSource), to include Firebird embedded engine into application EXE, so deployment is a matter of copying the application EXE file.

Fb_embedded 2.0 will be released soon,. A new thing is that the Firebird engine never gets extracted to disk but instead uses Memory Streams and redirect calls to Firebird engine to Memory stream.

Second part of session will cover 2-tier (traditional C/S) and 3-tier development (and n-tier, including Load-Balancing and Fail-Over) (Delphi based kbmMW server with Delphi based client).

Third part of session will review the various component libraries for accessing Firebird and explain the easiest way to convert InterBase and MSSQL systems to Firebird systems.

Open source Delphi

This session will explain the current state of the open source free pascal compiler (FPC) and assiciated Lazarus IDE. I will describe the tools, where to get them, and how to install them.

The second part of session will focus on the migration of existing Delphi (and Kylix) projects to FPC with special focus on database programs using Firebird.

Claus Heeg

Building ERP web applications based on Firebird and Cold Fusion. Migrating from 4GL applications to web based Applications

1. Background and Situation
2. What is CFML and Cold Fusion description, why Cold Fusion (not Java or PHP or ...)
3. The Project Toolset
4. How to set up connections using Jaybird
5. Stored Procedures drive all
6. Examples and code snippets
7. Performance and stability, data caching made easy
8. Pitfalls, phantom updates and optimistic locking
9. Solutions
10. Cold Fusion is expensive, are there other free software options? (free developer editon MM ColdFusion, Bluedragon, etc)

Migration and integration of other databases into Firebird using Cold Fusion

1. Oracle replication into Firebird using JDBC and Cold Fusion
2. Migration of database objects
3. Migration of a data example plus code snippets
4. Replication strategies, triggers on tables
5. Replication example code
6. Conclusion

Stefan Heymann

Using Firebird for Quality Management Software

Consic Software Engineering is using Firebird as its software for Quality Management in manufacturing industries. The talk will show how we use Firebird for various editions (local, network, evaluation) of our software, how we handle things like licencing issues and user administration and the perception of Firebird by our customers.

What Developers Should Know about Character Sets, Unicode etc.

Windows-1252 or ISO-8859-1? UTF-8? Unicode? This session will introduce you to the world of Character Sets and Unicode. Every developer must know the interrelations to make correct decisions in handling textual content.

Holger Klemt

Setting Up a bidirectional Replication and other Solutions based on EXECUTE STATEMENT Commands

Since Firebird 1.5, there is available a powerful new command: EXECUTE STATEMENT. In this Session, you can see what is possible with this Command regarding System Tables and Maintenance Operations. One more topic is a demonstration, how a bidirectional Replication can be created based on this command using Triggers and Stored Procedures.

Firebird Server Performance - How to make your application run faster

In this Session, you will see how to speed up a typical Firebird based Application. Topics are Server Configuration and Parameters, Data modelling, Index definition and much more. Beside creating Objects that might help, you will see how Firebird supports you in understanding where typical performance problems might be avoided.

Dmitri Kouzmenko

Firebird Performance Optimization for Different Applications

iBase.ru, is a consulting company, with over 3 years of practical Firebird usage in different companies. These companies have used Firebird for new tasks or moved from InterBase. This session talk will describe application specifics of banking, billing, accounting, CRM, and another types of application tasks. Since the data volume of these tasks are between 10mb and 50gb, and the number of users vary from 1 to 400, there will advice on how to manage these tasks and database sizes. Session talk will also describe different pitfalls and successful solutions in application transaction management, how to analyze problems, tendencies and needs to create perfect applications.

Agenda

• Firebird Application Market in Russia
• Database Volume Growth 2002-2005
• Practical Transaction Load
• Application Types
• Tools to Analyze and Monitor Performance

Mauricio Longo

Dynamic Databases - A Conceptual Overview

In today's usage scenarios, data-based applications and database management systems tend to be restricted in one of two aspects, flexibility or performance. As the boundaries of our digital universe expand, traditional application implementation methods are consistently having greater difficulty in keeping up with performance and usage requirements.

This session introduces the concept of dynamic database design and implementation patterns with emphasis on enhancing application flexibility and performance optimization. Both objectives are explored separately though being addressed by the common solution of dynamic database design.

Dynamic database is a name given to a database that is capable of altering its own structure in response to user manipulation of data in specific tables. This session will present formal definition of concepts and present a series of patterns for the development of dynamic database based applications. The session example scenarios are based on commonly used applications making it simple to understand the applicability of the presented concepts.

A complete working example of a generic implementation of a dynamic database in Firebird will be presented and the corresponding SQL source code made available for study and usage as a basis for implementing the concept in new and existing applications. Only database objects are used in the implemented sample guaranteeing application language and platform independence.

FireQ - Firebird Based Messaging Infrastructure (A Dynamic Database Implementation)

In modular application design it is not uncommon to need a means of communication between modules/processes. Heterogeneous, multi-vendor systems that some times expand corporate boundaries also demand easy to use, scalable and asynchronous communication solutions. Though a myriad of methods exist and are available to developers, today, one of the most simple to implement and use is messaging.

There are several different messaging implementations readily available, including some that are free. Most implementations are, however, dependant on a specific middleware, operating system or programming language. This session's objective will be to present FireQ, a 100% Firebird based, open source, implementation of a messaging mechanism for use as a communication infrastructure in modular environments.

FireQ's total Firebird implementation makes it an ideal, though simple, messaging solution for applications that use Firebird for it's database implementation. By using FireQ developers can always be sure that the application's messaging support will be available in whatever platform the application server works, through the same database connection used to access application data.

Detailed description of the dynamic database design and implementation of FireQ will be presented, as well as examples on how to use its capabilities, from Firebird stored procedures and triggers.

Special emphasis in this session will be given to the reasoning that led to FireQ's design and implementation options and how the design goals where quickly achieved with minimal work by using the Firebird Dynamic Database Reference Implementation as basic building blocks.

Kim Madsen

SOA using kbmMW (Delphi Win32/.Net), BCB, Kylix)

SOA (Service Oriented Architecture) - What is it?

Moving from client/server to n-tier using kbmMW by employing SOA.

N-tier database access with Firebird using kbmMW

How to operate Firebird (or any other) database(s) effortlessly in an n-tier setup using kbmMW's SOA principle.

Developing realtime Enterprise level business applications using the Wide Information Bus messaging system. (Delphi Win32/.Net), BCB, Kylix)

What is the "Wide Information Bus" (WIB) messaging system?

What will publish/subscribe based setups do for you?

Daniel Magin

Developing DataBase ASP.net Applications with Delphi 2006: Part 1

These two back-to-back sessions will show some of the most important techniques for developers creating Web-based applications with dataBase support. Delphi 2006 with the ASP.net framework enable you to create such applications easily.

You will hear about the basics of ASP.net Applications and how to connect to different databases over ADO.net. Learn here all about caching query results and pooling database connections.

Developing DataBase ASP.net Application with Delphi 2006: Part 2

Continuation from Part 1.

Andrew Morgan

Embedding and using sophisticated mathematics in Firebird: Fast Fourier transforms, data smoothing and high order derivatives

This paper describes how to deploy sophisticated mathematical C++classes in a UDF library and how to use them within stored procedures.The initial case study was to deploy a FFT engine,and use this as the basis for data smoothing and the calculation of high order derivatives.

The Fourier template class implements the FFT,DFT (Discrete Fourier Transform primarily to check the FFT), low-pass, high-pass, band-pass and notch filters which are used by the Smooth template class.

This technique has been extremely beneficial to the author since it has enabled the manipulation of data directly on the server without having to transport the data to an application and having to return manipulated data back via updates.

Complex processing which normally takes place in an application may now be performed directly in the database. The author has used Firebird to post messages based on ut-f-limit first and second derivatives of key thermal power generator sensors. This has eliminated the need for a remote polling program to receive large amounts of data for processing. Using this technique, the author is nearing completion of linear algebra, statistical and symbolic polynomial modules (demonstration available).

Creating and managing recursive structures

This paper describes how to create a recursive structure (self-referencing table) and how to manipulate these structures using their natural counterpart, recursive stored procedures.

Recursive structures express themselves most naturally as trees, and a directory tree is an excellent example. Recursive structures are extremely powerful when used to describe systems of arbitrary complexity. Like a directory tree, they can be made as simple or complex as the user wishes without any impact on the database metadata. Even more useful is the fact that nodes of a given tree may be reclassified within the tree without affecting the referential integrity of the associated data. When properly indexed, recursive procedures perform extremely well and are only marginally slower than a raw select of the same records.

Two types of recursions are considered, master and slave. Master recursions have no master relationship and normally have a single root node, although multiple roots are possible. Slave recursions have a root node per master relationship.

The author has created a C++Builder VCL component descended from a TCustomTree to visually represent a recursive structure in an application. The author has used recursive structures very effectively to describe systems such as regional classifications, plant classifications, instrument classifications and a database representation of a directory structure (demonstration available).

Luiz Paulo de Oliveira Santos

Studied computing since 1984, I wrote articles for Delphi and I am one of the owners of the DB FreeMagazine (www.dbfreemagazine.com.br), a specialised magazine about client/server databases.

I talked in events of the Delphi Developers Day. In the 2004 event, I talked about ODBC and at this year's event, in July, I talked about Security.

I used Interbase for the first time in 1998. In the University, for my first degree, I studied Technology of Data Processing and Mathematics for Computing, specializing in Analysis of Systems. Now, for another degree, I am studying Law for Computing.

I studied the Novell CNE curriculum. I am manager of networking in the Methodist University of Piracicaba - UNIMEP.

Implementing applications using Firebird API (Interbase 6 API) in Delphi, Lazarus and Free Pascal

Originally proposed as two sessions, this talk is about the API, its workings and the interface. I will demonstrate with some applications how we can use API in Delphi, Lazarus and Free Pascal.

• Requirements
• Structure of Interbase 6 API
• Procedures to implement using API
• Principal functions and procedures
• Managing errors
• Examples in Delphi
• Example in Free Pascal
• Example in Lazarus

Techniques for migrating from MySQL to Firebird

I will to detail how to to migrate from MySQL to Firebird. The main tips, the impact on applications and the principal SQL changes necessary.

• Requirements
• Diferences between the databases
• MySQL SQL exclusiveness
• Firebird advantages
• Principal changes
• Applications impact
• Improvements in Firebird environment
• Breaking the barrier of WEB applications

Alex Peshkov

New security features in Firebird 2.0

This session will explain the security-related problems that have been solved in Firebird 2.0:

• Thwarting Denial-of-Service (DoS) attacks,
• Non-root (non-Administrator in case of windows) installation
• Protection of the security database from various attacks
• New features in Firebird related to security

Also under consideration will be:

• Recommended configuration to access firebird remotely using insecure connections - how Firebird 2.0 differs from Firebird 1.5
• The specific issue of migrating existing installations to Firebird 2.0 - upgrading your security database.

(Security continuation)

Continuation of New security features in Firebird 2.0

Evgeney Putilin

Firebird and Java Stored Procedures

Stored procedures are one of the most powerful mechanisms for customizing database behavior. Firebird's PSQL provides rich capabilities for data manipulation, but is significantly poorer for the more sophisticated solutions, for example, integration with external applications, emails, XML processing, etc.

The following topics will be addressed in this presentation:

• Introduction to Java Stored Procedures
• A short overview of the Firebird-J distribution
• Development with Java Stored Procedures
  1. DDL extension
  2. the Java stored procedure declaration rules and code examples
  3. BLOB handling
  4. SQL callback and data manipulation within the same transaction
  5. Accessing the trigger context
  6. Using IDEs to debug and profile JavaSP
• The plugin architecture - possibilities to create stored procedures in other languages

Paul Reeves

From Basic to Advanced ISQL scripting

ISQL may be an extremely dumb script manager, but with a bit of ingenuity it can be coaxed into doing some remarkable things. This presentation will demonstrate how to get the most out of ISQL scripts.

Command-driven scripting may appear like a throwback to the stone-age, but it remains a reliable way of documenting the process of building and maintaining databases. It is especially useful when deploying to large numbers of sites.

This session will start with an overview of ISQL scripting basics, just to make sure everyone is up to speed. We will then move on to look at some advanced techniques that push Firebird's ISQL scripting to the limit. These techniques combine ISQL and the command shell to allow you to produce scripts that work dynamically, log their results and handle errors.

Migrating a PHP application to Firebird

The aim of this session is to give you the information needed to port an existing PHP application to Firebird.

PHP has matured considerably over the years and is the most widely used language for creating web-sites. Countless open source PHP applications are available and they (almost) all share one thing in common: Firebird is not directly supported.

We need to remedy this and to do so requires a basic understanding of how PHP works with databases. This session will then put this knowledge to work by implementing Firebird support to an existing application.

Practically speaking, this session will be a study in how the ADOdb query interface to PHP works.

Roman Rokytskyy

JayBird - JCA/JDBC driver for Firebird

This is the first of two linked sessions, providing basic information about JayBird: obtaining connection, creating statements, executing them, working with result sets, additional configuration, transaction isolation and parameters, using stream and segmented blobs from Java.

The main goal of this session is to show how JayBird ticks internally on basic JDBC operations and what issues people can experience during normal development.

JayBird - JCA/JDBC driver for Firebird

This second session goes deeper into the Firebird specifics and will target the the Services API support, character sets and typical issues that arise there, XA transactions, performance tracking and tuning.

Paul Ruizendaal

Fyracle, Oracle-mode Firebird

The proprietary database market is increasingly dominated by just a few companies, with Oracle being the leading supplier. Prices have stayed very high, typically well over $10K, even as database management systems are becoming commodity software.

At the same time application vendors see few growth opportunities at the enterprise end of the market and are turning their attention to mid-market accounts. Bloated, expensive database engines don't sell very well into this market.

Oracle-mode Firebird dramatically reduces the cost of porting applications from Oracle to Firebird. It accepts Oracle style SQL (joins with (+), system catalog, support for "connect by", etc.) and it supports PL/SQL stored procedures and triggers.

This talk explains what Firebird's Oracle-mode is all about and how advanced it is when compared to most other attempts.

Solution Stacks Built on Firebird and PHP - Another Flame in the LAMP?

Having a PHP-stack for Firebird is as important as having ODBC, JDBC and .Net drivers and as important as smooth integration with Delphi.

Four leading technologies dominate for building database-based solutions these days:

1. VB/Delphi based
2. Java based
3. .Net based
4. PHP/Web based ("LAMP", but that acronym misses the point)

All four seem to be growing. (1) may be getting dated, but is still getting a lot of use; (2) is seeing lots of buzz around app servers like JBoss; (3) is being pushed heavily by MS; and (4) is being pushed by big DB vendors, probably to combat threats from (2) and (3).

I will talk about solution stacks and why I think "LAMP" is a concept that has evolved into a paradigm, far beyond the specific set of "brand-name" components that gave it its acronymic name.

We will look at LAMP, Firebird-style, and explore stack solutions for building

• a mid-market Exchange/Notes replacement
• an "XML database"
• a web service

Bill Oliver

Tom Cole

Embedded Classic

This paper discusses an SMP scalability solution pursued by SAS as an alternative to Vulcan’s current fine grain threading implementation. Extensive testing led to the conclusion that the initial Vulcan solution did not meet all of the performance goals for some key SAS usage scenarios and there are still some observed instabilities that have not yet been eliminated. We experimented with an alternate strategy that combined classic servers process-per-connection model into a connection-per-thread model. The strategy has proven fruitful in our use cases. This paper describes the internal architecture of our implementation and discusses the resulting behavior relative to the initial Vulcan approach.

SAS and Firebird

SAS chose to adopt Firebird as part of our data storage solution in an effort to fulfil technical requirements emerging in our solution space. While a number of open source RDBMS options were considered, Firebird won the race because of its feature set, its embedded server personality, and the flexibility the IPL affords SAS. In addition to SAS’ business case for adopting Firebird, this presentation explains Firebird’s position within the SAS Platform architecture and reviews some of the top level use cases involved. The objective is to clarify the value of being able to leverage Firebird functionality as an embeddable RDBMS engine.

SAS/Firebird porting and testing processes

In 2006 SAS plans to ship a version of Firebird/Vulcan on twelve hardware platforms (three Windows, seven Unix/Linux, MVS/zOS, and VMS Itanium). To meet this objective, we have implemented a porting and regression testing process that encompasses each of these platforms. This presentation discusses the strategy and tools behind this porting/testing initiative and solicits the open source community for feedback/consideration on how SAS effort can be aligned with/leveraged by the Vulcan/Firebird 3.0 development effort.

SAS/Firebird performance testing strategy

One of the release readiness criteria SAS has for our use of Firebird is demonstrating long running stability by emulating 1000 users requesting work from the server for a period of seven consecutive days. To achieve this goal, we are modeling scenarios extracted from SAS solutions in an environment that exercise endurance/load characteristics of the system. This presentation describes the testing environment created, the use cases modeled, the performance measurements gathered (including average response time, throughput rate, high water marks, etc), and performance optimization strategies discovered. In sharing this information, our objective is to learn from the Firebird user community of similar performance initiatives and applicable best practices that SAS can leverage to meet its goals.

Jim Starkey

Vulcan status, features, and goals

Created by Jim Starkey to meet the need for an SMP-friendly shared server, Vulcan introduces fine-granularity multi-threading, separates the functions of Firebird into server, remote interface, engine, services, and future extensions like foreign system gateways.

Refactoring the code eliminated large amounts of conditional code and allows applications a runtime choice of architecture: classic, shared server, shared cache, or embedded.

Configuring Firebird and Vulcan for your application

Not all database applications consist of a single database running on a dedicated machine and accessed by remote clients. Some require throughput that is most economical if the application connects directly to the database handling code. Others are self-contained and should not affect any system-wide settings. The Firebird database engine can be built in different configurations - as a shared server - or as an embedded server or as a library of access routines. The API is the same, so you can offer different models for ‘personal’ and ‘corporate’ versions of your application.

(Vulcan continuation)

Vulcan status, features and goals, continued.

Thomas Steinmaurer

Audit Trails, Transaction Log, Redo with the IB LogManager product family

Firebird is well suited for mixed operation in Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP) environments due to its multi-generational architecture (MGA).

The MGA omits the necessity of a transaction log to recover a crashed database. Nevertheless, in some circumstances a change log of all committed data changes would be interesting, even for Firebird.

This session shows how to integrate a server-side auditing mechanism and available tool support, the IB LogManager product family.

Owner Migration - The Easy Way

Database objects and the database itself has an owner, namely the creator, which is a regular Firebird user. Usually, people use SYSDBA, especially Firebird newbies. Using SYSDBA as the owner of the database and included database objects might strike back when deploying the database to the customer or web hoster.

This session discusses what you should know about ownership and their consequences for deployment. It also explains the mechanism behind a freely available tool called FBOwnerMigrator for migrating to another owner without recreating the database from scratch.

Martijn Tonies

The Firebird system tables

Metadata information is stored in the Firebird system tables. What are they? How can they be used to retrieve information about tables, views, constraints etc?

What is stored in the tables and where?

How can you protect your valuable stored code when deploying your application and database?

Firebird Stored Procedures - why and how

Stored Procedures and triggers - server side code, stored in the database, is a powerful mechanism when it comes to database programming. What can you do with Firebird PSQL (Procedural SQL) and why should you use it?

This presentation will explain the possible commands in PSQL and will give you some examples and advantages of using Stored Procedures.

Serg Vostrikov

Special FIBPlus features: network traffic optimization and FIBPlus Repository

Originally proposed as two sessions, this session (and possibly a related BOF session will cover

• Network traffic optimization.
  • Caching metadata.
  • Dynamic modifying queries.
  • Using poRefreshAfterPost option in TpFIBDataSet Options .
  • Recurrent use of queries.
  • Client BLOB-filters. “Transparent” packing of BLOB-fields.
• FIBPlus Repository: Using a database as storage of queries and client options.
  • Saving and using visual field settings in a database.
  • Saving additional field options in a database.
  • Query Repository.
  • Error Messages in FIBPlus Repository.

The paper is aimed at describing and demonstrating special FIBPlus features, which enable developers to create more optimal applications for Firebird RDBMS.

In the first part of the paper I will show several ways of network traffic decreasing for the database server with low-speed network channel. In particular I will speak about decreasing of the number of system queries at the expense of automatic caching metadata at the client; decreasing of fields in modifying queries (thus reducing the volume of the data transferred); deactivating of some subsidiary queries, as well as about automatic archiving of text and graphic BLOB fields. All the features will be illustrated by the code samples available for downloading during and after the conference.

The second part of the paper will describe a unique FIBPlus mechanism, which helps to keep the following application settings in the Firebird database: field properties and settings, query collections and localized error messages for Firebird exception handling. Storage of local settings and queries in the database enables developers to make application changes by updating the database contents centrally, without recompiling the client applications. Use of the repository will be demonstrated with the code samples available for downloading during and after the conference.

Getting Started with FIBPlus

The paper is aimed at demonstrating FIBPlus features to assist in making applications for Firebird RDBMS. Firstly I will show how to connect to a database and get data from it. Non-visual data processing and use of FIBPlus together with standard visual VCL controls will be demonstrated in code examples.

This part will also include automatic generation of modifying queries, generator use and various TpFIBDataSet options.

The second part of the paper will be devoted to the master-detail scheme with the TpFIBDataSet component.

In the third part I will speak about such special FIBPlus features as local sorting, local filtering and local BLOB-filter, as they enable partial data handling at the client. Finally I will demonstrate a handy macro mechanism for generation of selective queries.

Jason Wharton

Jason Wharton lives in Mesa, Arizona, U.S.A. with his wife Sara and their six children. As well as running his software development company, CPS, he works in the IT department of the Secretary of State's office at the Arizona State Capitol, where InterBase continues to be the back end for a large farm of administrative and public information services. The IB Objects customer website at http://community.ibobjects.com and the searchable FAQ application at http://www.ibobjects.com are web applications that launch dynamic web pages from Firebird 1.5 databases.

IB Objects for Newbies

I n this session I cover various aspects of developing applications with Firebird, focusing on some aspects that Delphi and C++Builder developers generally learn the hard way when first trying to come to grips with SQL, transactions and the networked model.

Topics covered in this session include the basics of connecting your Delphi application to Firebird with minimal code, using IBO. Understanding the pitfalls is essential in the approach you take to designing applications and I also like to demonstrate how to avoid some of the common mistakes people make when first encountering the client/server style of development. I like to include some procedures for troubleshooting when databases start to misbehave, as well.

IB Objects for Experts (Advanced Techniques)

This session is geared towards intermediate to advanced level developers who have used IB Objects before and who already know their way around Firebird.

As usual in my "expert" sessions, exact coverage will depend a lot on what the session participants want to see. Topics are likely to include creating a Win32 service application using Windows sockets, email components and event alerters and making the service run using its own thread instead of the service control thread.

Possible talking points around this type of project would include techniques to dump logging information for debugging purposes, designing and responding to alerts, thread signalling and wakeup, mixing long-running jobs with short duration jobs, background replication, even to designing your own simple protocol that talks to another socket and transmits information accurately.

Dmitry Yemanov

Under the hood: Data access paths

Paper by Dmitry Yemanov

Access paths are ways in which data is retrieved from the database. There are a number of ways to access tables, perform joins, and so on. For example, index access paths are generally used for statements that retrieve a small subset of table rows, while full scans are more efficient when accessing a large portion of the table. The optimizer is responsible on selecting the best possible access path under every circumstance.

This topic describes the data access paths that Firebird can use to locate and retrieve any row in any table. We'll also outline related changes done in Firebird 2.0 and ODS 11.0. This material is intended to explain the internal algorithms used by the engine, without any deep review of the optimizer logic, although every examples are illustrated with plans and detailed textual representation of access graphs. You'll also see comparisons against other database engines and some notes dedicated to possible future improvements.

Firebird future development: what to expect
(followed by)
Meet the Firebird Team for Drinks

Paper by Dmitry Yemanov

A significant amount of improvement has been done in Firebird 2.0, but much more is to come. An updated draft roadmap, covering at least two next major versions, will be presented. You'll also know about our v2.0 maintanence program plans and what fixes you could expect to see backported from the development CVS branch. You'll see what's already implemented and being tested for V3.0 and how Vulcan architecture and code improvements fit this release.

Then, a foresee of our long-term goals will be outlined. We'll talk about what various kinds of users want most of all and expect to see in the future versions and how we're going to address their needs. Some feature and performance comparisons with our competitors will be presented. If you ever dreamed about temporary tables, security and statements monitoring, more powerful and ANSI-compliant SQL language, better performance, etc then you have a great chance to learn about our plans and discuss them with the development team members.