RqRDDFire - A Free FlagShip RDD for Firebird RDBMS v.0.9.0, September 2007, Brazil Ricardo Delamar Roque (rroque6428@terra.com.br) License ======= This library is free software; you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation; either version 2.1 of the License, or (at your option) any later version. This library is distributed AS IS in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details. You should have received a copy of the GNU Lesser General Public License along with this library; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA http://www.gnu.org/licenses/lgpl-2.1.html Donate to FirebirdSQL.org ------------------------- Firebird is FREE and COOL! I have been using it for many years, but without contributions no open source project can survive. "Choosing Firebird and saving or making money by your choice? Show your appreciation and encouragement by contributing money in proportion to these benefits." http://firebirdsql.org/#consider-your-contribution Description ----------- RDD (Replaceable Database Driver) is a way of accessing the data formats of other database systems and keep intact *most* of your code. It makes an application data format independent. Creating a RDD for SQL is a complex task, since most users do not want to loose the speed access to the data they are used to and at the same time want to modify very little code. RqRDDFire makes a decent job to satisfy both requisites. It is a work of one person alone. Brief History ------------- I intended initially to give commercial support to this RDD, but due to the lack of interest from FS users at that time (2007), I gave up and move to other projects. This source code was adapted, translated and this brief documentation was quickly done to supply a sudden interest (2013). I am not a native English speaker, so you may find several errors and typos, please understand that the main goal here is to give a functional tool. All parts were tested and since the source code is supplied many of you can indeed correct some small errors and expand the code. I pray that you share any improvements however. Requirements ------------ RqRDDFire was initially coded under FlagShip 4.48 and Firebird 1.0, but the present release was compiled using Ubuntu 11.04, FlagShip Release 7.01.19a_26 (kindly supplied by Multisoft) and Firebird 2.5.2.26540. Roadmap (LINUX ONLY) ------- Install FlagShip 7.01 Install FirebirdSS 2.5.2 !!Take a note of the SYSDBA password during the process. Also, I use the SuperServer version, but I don't expect any trouble using the Classic one. *Please*, read carefully the following information, do not skip any of the steps or inner steps, they are all most important. Make a cup of coffee, take a long breath and I promise it won't take long. STEP 1 - COMPILING THE LIB ====== // You need to compile 'librqrddfire.a' and 'rqfbconn.fh'. To do that you // will need the most basic development environment, which many Linux boxes // can install very easily, and in many cases it is installed by default, // like in Ubuntu. !! // You'll need to edit 'Makefile' to change the SYSDBA password and the // Firebird folder. Use an editor of your choice. Later on, if you need // to change the SYSDBA password that is ok, *no* need to redo this step) // $ tar xvfz RqRDDFire_Free.tgz $ cd RqRDDFire_Free $ vi Makefile + FIREBIRD_HOMEDIR=/opt/firebird !!Confirm this information + FIREBIRD_SYSDBA_PWD=masterkey !!Confirm this information $ make clean $ make // After some warnings you should now have the static lib. STEP 2 - COMPILING THE UDFs ====== // In order to use functions during index creation, I rewrote some // FS functions in pure C. Those functions became UDFs (User Defined // Functions) inside Firebird. $ cd udf $ make clean $ make # cp rqrddudfs /opt/firebird/UDF !!Need ROOT privileges STEP 3 - COMPILING THE REFERENCE PROGRAM ====== // A lot will be learned by looking inside 'checkrqrdd.prg', which is a // reference program. 'checkrqrdd.prg' will import an existing DBF, // then perform several operations and recreate it at the end. // A Firebird database 'sample.fdb' is supplied and have all you need to // start, including the register of the UDFs. !! // To recreate a DBF in Firebird we made use of External Tables. A special // permission is necessary in 'firebird.conf' // # vi /opt/firebird/firebird.conf !!Need ROOT privileges + #ExternalFileAccess = None (Copy this line) + ExternalFileAccess = Restrict /tmp (... and make this change) # /etc/init.d/firebird restart !!Need Restart // // The above step is very important. If it is not done then the importing // process will fail. In this case DROP the table 'EXT_MYDBF' manually // and retry (use ISQL). In 'checkrqrdd.prg' we delete 'mydbf.dbf' // to proof that we are working with the SQL version of that table, if // for any reason the execution was aborted you can recreate 'mydbf.dbf': $ cp copy_mydbf.dbf mydbf.dbf !! // There is a very useful multilevel Log System. To enable it: # > /var/log/rqrddfire.log !! Need ROOT privileges # chmod 666 /var/log/rqrddfire.log // // From now on to observe what the RDD is doing, just: $ tail -f /var/log/rqrddfire.log !! // change the SYSDBA password inside the source code. Of course // you can use a different user. $ cd demo $ vi checkrqrdd.prg + #define USER "SYSDBA" !!Confirm this information + #define USER_PWD "masterkey" !!Confirm this information That is it! Most of your learning will take place by analysing 'checkrqrdd.prg' carefully. Hope you enjoy. --------------- Additional Info --------------- *** Supported Functions for Index Creation (See also checkrqrdd.prg) DTOS( expD ) UPPER( expC ) SUBSTR( expC1, expN2, expN3 ) PADR( exp1, expN2, expC3 ) PADL( exp1, expN2, expC3 ) STR( expN1, expN2, expN3 ) STRZERO (expN1, expN2, expN3 ) *** To Recreate 'sample.fdb' using ISQL $ /opt/firebird/bin/isql SQL> create database "sample.fdb" user "SYSDBA" password "..."; SQL> edit "rdd-installation-dir/udf/rqrddudfs.sql"; :wq SQL> commit; SQL> show functions; *** Main Features * Implementation of the following methods/properties of DataServer Interface: ALIAS, APPEND, BOF, CLEARINDEX, CLOSE, COMMIT, CREATEDB, CREATEORDER, DBSTRUCT, DELETE, DELETED, DRIVER, EOF, FCOUNT, FIELDGET, FIELDINFO, FIELDNAME, FIELDPOS, FIELDPUT, FLOCK, FOUND, GOBOTTOM, GOTO, GOTOP, INDEXCOUNT, INDEXEXT, INDEXKEY, INDEXORD, LASTREC, NAME, RDDNAME, RECCOUNT, RLOCK, RLOCKLIST, RECNO, REFRESH, SEEK, SETORDER, SHARED, SKIP, UNLOCK, USED, ZAP. * Multiples connections. * Result sets creation from SQL clauses. * Embedded converter: DBF files to Firebird tables. * Embedded converter: Firebird tables to DBF files. * Several behaviour optimization levels. * Direct connection to Firebird Daemon, with no intermediates. * No limit of users. * No limit of connections. * Powerful schema to modify Firebird tables structure. * Automatic Drop operation for temporary tables. * Disconnection at any time with automatic Close operation of associate tables. * Detailed activity log with 10 levels. * Database structure schema creation for easy system updates. * File/Record locking. * Direct SQL clauses execution. * Real Firebird index creation using the actual set of supported functions: DTOS, UPPER, SUBSTR, PADL, PADR, STR and STRZERO. * Supported DBF Field types: Character, Date, Numeric and Logical. * Extremely fast record counting for use with DBEdit and Browse functions. * Utilization with multiples RDD’s which allows gradual migration. * Tested for CGI application use. * Closely based on Multisoft RDD specifications. *** FAQ Q: Is it possible adapting RqRDDFire to use with MySQL, PostGre, etc? A: 'rqfbconn.e' is very specific to Firebird whereas 'rqrddfire.prg' and 'rqfbrs.prg' are mostly independent, so if you can adapt the first one most of the job will be done. Also, many ideas can be used to write other RDDs. I have no plans to port or translate. Q: Is Firebird ready to production? A: Today I use FB to my production enviroment and it is totally trustable. The project is active and promising. Of course if your system is critical you should consider to pay for a ready support from the FB team directly. Q: Can I access SQL direcly using RqRDDFire? A: Yes. Check 'Test009a' inside 'checkrqrdd.prg'. Also, if you take time to analyse the source code you may find many undocumented and useful functions. Q: I want to use RqRDDFire in my system, can you give me support? A: Normal support is not available. I have not write code using FS recently, so I can not promptly help you. However, I could participate in some projects but there will be a cost of course. Let me know in advance. Q: Will RqRDDFire work for future versions of FlagShip and Firebird? A: Most probably yes! I took the source code of 2007 and compiled it with almost no modifications. Minor changes should be expected though.