Introduction Following instructions are for both Linux/Windows versions. Firebird Random Data Generator is a engine to generate random test data for Firebird RDBMS database. It can generate all data-types used by Firebird RDBMS, except array data-type. User can use the generated test data to simulate the database production situation, find hiding problems in the database design, confirm the Primary Key and Foreign Key relations, and so on. It is normally the fist step to do before put the database into real production field, -- the dressed rehearsal before an opening night. Apart from generating random test data Firebird Data Generator can also create/save the SQL script which was used to insert all of the random data records, thus it also creates and keeps a SQL text file of all random data inserted into the database. Firebird Data Generator will also calculate the time used to generate all of the random records. There are 3 versions of Firebird Random Data Generator 1. Firebird Random Data Generator Standard. connect to only one database generate Max. 30 sets of different random data for each round of inserts ( the random data set will change itself in each round of inserting, so even 1 set of random data can insert into numberless Primary Key column/field) populate multiple tables simultaneously, no limitation to the number of tables/records to be populated in each round of inserts no limitation on how many rounds of inserts can be done keeping the Primary Key and Foreign Key relations across tables within database. Standard Version: " Cast seeds into fields, Let grow Fruits." 2. Firebird Random Data Generator Professional. connect to Max. 4 database simultaneously generate Max. 60 different random data for each round of inserts ( the random data set will change itself in each round of inserting, so even 1 set of random data can insert into numberless Primary Key column/field) populate multiple tables simultaneously in all connected databases (Max.4 databases), no limitation to the number of tables/records to be populated in each round of inserts no limitation on how many rounds of inserts can be done keeping the Primary Key and Foreign Key relations across tables within all connected databases. ( tables in different database can keep the same Primary Key and Foreign Key relations. Professional Version: "We are now casting seeds on multiple lands, " Can we also harvest them at the same time?" 3. Firebird Random Data Generator Enterprise. connect to Max. 4 database simultaneously generate Max. 99 different random data for each round of inserts ( the random data set will change itself in each round of inserting, so even 1 set of random data can insert into numberless Primary Key column/field) populate multiple tables simultaneously in all connected databases (Max.4), no limitation to the number of tables/records to be populated in each round of inserts no limitation on how many rounds of inserts can be done keeping the Primary Key and Foreign Key relations within database, and across all connected databases. insert user defined files as random test data to blob field, across all connected databases. embed fixed string into blob data-type so that user may test 'searching' function in database. Enterprise Version: "Great lands provide great powers, "Which demand greater management." All of the 3 versions of Firebird Random Data Generator support: UTF-8 character set (default) Data-type of Char (max.32k) Varchar (max.32k) Smallint (-32767 ~ 32768) Integer (-2147483647 ~ 2147483647) Float Double Precision Deciaml/Numeric (Max. precision 18) Timestamp Time Date Blob Auto-Incremental (integer) UPDATE OR INSERT SQL for Firebird 2.1+ preview of the random test data use standard SQL script to generate random data create standard SQL script for generating/insert random data to records only save/load the SQL script embed user defined constants in the SQL script ( will not change throughout whole operation) user defined starting number for the auto-incremental filed (min. 0), and can continue from the end number for next round of inserting. save/load the user defined random test data variable definition file. execute any standard SQL DDL script. enable/disable triggers. a data grid to review the data of database. Operation Manual AboutBox of Standard Version Create DB will create firebird database for testing in /tmp directory ( C:\tmp directory) user: sysdba, password: masterkey, character-set UTF-8 Each database contains 3 tables, each of the first 2 tables contains 11 columns of different data-types (tbl_a, tbl_b) the last table (tbl_c) contains one more integer column to test the auto-incremental data. Standard Version will create only one database, Professional and Enterprise version will create 3 databases. (make sure all database are read/write/execute possible) you can also use your own database directly. It will create the C:\tmp directory by itself. Continue to the main operation screen. There is only one database to be operated in Standard version. Main Operation Screen the upper left part of this panel is composed of 11 different data types that can be generated, each with a checkbox in front of it. * Char/Varchar (max.32k) * Smallint (-32767 ~ 32768) * Integer (-2147483647 ~ 2147483647) * Float * Double Precision * Deciaml/Numeric (Max. precision 18) * Timestamp * Time * Date * Blob * Auto-Incremental (integer) below this panel is the Sample Random Data Grid with columns: No, selected, DataName, DataType, Datalength DataSample 30 rows for Standard version, generate Max. 30 different random data for each round of inserts 60 rows for Professional version, generate Max. 60 different random data for each round of inserts 99 rows for Enterprise version, generate Max. 99 different random data for each round of inserts Tip: one random data can be used to insert into numberless records, and it changes itself after each round of inserting. At the bottom of the left part are 5 buttons: Load, Save, Delete, Clear All, and Generate Sample Data Now let's try to generate some random data Have the Char/Varchar data type checkbox checked, set the length to 10 (or any integer between 1 and 32000, as required by the column) move cursor to Sample Random Data Grid and click at 'selected' of row 1 (No. 01). there will be a 'yes' shown in that cell. now click 'Generate Sample Data' button: DataName DataType DataLength DataSample :@Val01 Char 10 'cfhYwUmGs' <=(can be anything else) click 'Generate Sample Data' button again, and the DataSample will be changed. (generated a new random data) In the Enterprise version there are some more options for the user. none pre(fix) suf(fix) both and a text entry box. The default option is none, and nothing in the text entry box. If you select 'pre' and type in a string, such as '_PRE_'in the text entry box, then this string will be attached in front of the randomly generated string, such as "_PRE_sdfdfgk", and inserted into the column. If you select 'suf' and type in a string, such as '_SUF_'in the text entry box, then this string will be attached at the back of the randomly generated string, such as "sdfdfgk_SUF_", and inserted into the column. If you select 'both' and type in a string, such as '_BOTH_'in the text entry box, then this string will be attached both in front of and at the end of the randomly generated string, such as "_BOTH_sdfdfgk_BOTH_", and inserted into the column. tip: the column length must be sufficient enough to contain both the extra string and the randomly generated string. For example, the length of the CHAR column is 20, the length of the extra string is 5, then set the length of the random string less than 15, let's say 12. then the total length of the string to be inserted into the column is 5+12=17, shorter than the max. length of the CHAR column. have the SmallInt data type checkbox checked, set the range as required by the column. (optional) move cursor to Sample Random Data Grid and click at 'selected' of row 2 (No. 02). there will be a 'yes' shown in that cell. now click 'Generate Sample Data' button: DataName DataType DataLength DataSample :@Val02 SmallInt -3476 <=(can be anything else) click 'Generate Sample Data' button again, and the DataSample will be changed. (generated a new random data) In the Enterprise version there are some more options for the user in the BLOB column. embed into blob checkbox and the text entry box. The default option is un-checked and nothing in the text entry box. If the checkbox checked and there is text/string in the text entry box, this text/string will be embedded in the middle of the blob randomly generated text. For example if the blob text length is set to be 40, then this text/string will start at the 20th character of the blob text. If you do not re-set/re-generate the starting number of the auto-increase number, it will start from the number it stopped at in the previous round of inserting. repeat above steps to have all the rows, or as many rows as you need, filled Above picture shows the generating of a blob random data. tips: even just one row of random data can be used to insert into numberless tables/columns for numberless rounds of insert or all rows with the same data type for just one round of insert into numberless tables/columns Having set/generated all the random data, you may want to save the settings so that it can be re-loaded and used again next time. Click 'Save' and give a filename such as '/tmp/data-list.xml', and Save. click 'Clear All' button to wash away all settings and data generated in the Sample Random Data Grid. click 'Load' button to load the just saved '/tmp/data-list.mxl' file into Sample Random Data Grid. You can see all the data settings are there, but 'selected' and 'DataSample' cells are blank. this is because THOSE DATA HAVE NOT BEEN GENERATED. repeat the above steps to have the checkbox checked, length/range set, 'selected' clicked according to each setting, and then click 'Generate Sample Data' button, generate data accordingly row by row. We now will have the same random data settings as defined before. When click 'Generate Sample Data' button, data will be generated into the present selected/high-lighted row as it is defined by the data-type whose checkbox is currently checked. Make sure you are filling the correct data into the targeted row. 'Delete' button will delete everything in the high-lighted row. Included is a ready-made data-list.xml file with 30 rows of random data which you can load, and re-generate row by row. ( a video ) The Database Now that we have generated the random data sets, we have to deal with database. First connect database one by one. (only one database possible for the Standard version, Max 4 databases possible for Professional and Enterprise Versions) Fill in the hostname, database name, user name, user password, and click Connect button If a database is correctly connected user will see the table names in the Table/Column selector combobox, and when clickthe table name, all of its column names will appear in the checkbox- listbox below. Have the checkbox checked to select the columns/fields you want to insert the random data, or use 'Select All' button to have all columns/fields selected. Then click "Generate Random Data SQL' button. User will see a message box warning not to forget to change to REAL value, and the following SQL text in the memo box below: (or your column/field names...) INSERT INTO TBL_B( FLD_CHAR, FLD_VAR, FLD_SML, FLD_INT, FLD_FLOAT, FLD_DBLPRE, FLD_DEC, FLD_TMSTAMP, FLD_TIME, FLD_DATE, FLD_BLOB ) VALUES ( :@Valnn ,:@Valnn ,:@Valnn ,:@Valnn ,:@Valnn ,:@Valnn ,:@Valnn ,:@Valnn ,:@Valnn ,:@Valnn ,:@Valnn ); Now change the :@Valnn to the corresponding random data generated previously. The data-type and length must fit to that of the corresponding column/field. For example: INSERT INTO TBL_A( FLD_CHAR, FLD_VAR, FLD_SML, FLD_INT, FLD_FLOAT, FLD_DBLPRE, FLD_DEC, FLD_TMSTAMP, FLD_TIME, FLD_DATE, FLD_BLOB ) VALUES ( :@Val01 ,:@Val02 ,:@Val03 ,:@Val04 ,:@Val05, :@Val06 ,:@Val07, :@Val08 ,:@Val09 ,:@Val10, :@Val11 ); There is prepared a sample SQL script named data-list.sql which a user can load into the SQL Script memo by clicking button of "Load From File". It is based on the sample database and its tables/columns. Now a user can, not necessarily, to copy the changed SQL text to SQL Script memo by clicking "Copy To SQL Script" button. In the SQL Script page/panel there is a "Pre-Checking" button. Click this button and then all the :@Val01....:@Val11 parameters will be changed to REAL data that will be inserted into the columns. See if this is what you are expecting: INSERT INTO TBL_A( FLD_CHAR, FLD_VAR, FLD_SML, FLD_INT, FLD_FLOAT, FLD_DBLPRE, FLD_DEC, FLD_TMSTAMP, FLD_TIME, FLD_DATE, FLD_BLOB ) VALUES ( '_AA_ZLpQakSZTE' ,'tooVXRIWJP_AA_' ,23973 ,379515543 ,0.00086915766587 ,-4.32976787529835E-292 , 714887458276.6711 ,'1998-11-5 22:59:28' ,'08:46:58' ,'1988-7-4' ,'maf&#ALVqC_MM_omoNXYJXdX' ); Of course, yours will have completely different values, and each round of inserting will bring up a completely new set of random data. Make sure that before clicking the "Pre-Checking" button each parameter has a generated corresponding REAL value. Another picture to show the SQL script before the pre-chcking result. Repeat this step for each table of all connected database. The same random data parameter can be used for different columns/fields in different tables/databases. That is how the integrity of Primary key/Foreign key and be kept through out all connected database easily. For the Enterprise Version there is also a function of "Blob<=>File Generator", which enables user to insert a file (any kind of file) into the blob column/field. User can also use this function to insert files into the production databases, particularly good for batch inserting. On the left-side is the Blob<=>file function for Enterprise Version only. On the right-side is the result of the upper picture after Pre-checking. Now let's generate some blob<=>file parameters. Click "<>" button of the first row, that will bring up a open file box, then select the file to be inserted. The full path file name will be filled in the File Name cell of the first row. The Blob Name is :BLOB_A. Press "Generate" button of the first row, the checkbox of the first row will be checked automatically. Now the first Blob<=>File parameter is generated, ready for use. Repeat the above steps to generate all Blob<=>File parameters, if needed. All together there can be 10 parameters. For example we want to insert parameter :BLOB_A into the FLD_BLOB of TBL_A of database sample1.fdb. In the database panel on the right-side, select database 1, TBL_A, FLD_BLOB. ( can select only ONE blob column/field each time) Click "Generate Blob<=>File SQL" button. There will be a pop-up message reminding you how to do with the SQL. "Do not forget to chnage :BLOB_? to a REAL value, such as :BLOB_A ... :BLOB_J' add a WHERE clause if necessary, such as: UPDATE TBL_A SET FLD_BLOB = :BLOB_A WHERE FLD_BLOB in (select fld_blob from tbl_a ROWS 1 to 10); Thus only first 10 rows will be processed, otherwise ALL records will be processed ! This SQL will be executed ONLY in the final loop. Blob params MUST BE in CAPITALS, EACH SQL MUST END WITH ;" In the Database 1 SQL Script memo under the blob<=>file parameter grid a SQL text will appear as : UPDATE TBL_A SET FLD_BLOB= :BLOB_? ; Change manually to: UPDATE TBL_A SET FLD_BLOB= :BLOB_A ; Repeat above steps for all blob<=>file parameters. Make sure the blob parameter SQL is in the correct database SQL script memo. Or you can do as following: 1. generate variable parameters as usually. :@Val01 as Char datatype :@val02 as VarChar datatype :@Val03 as Blob datatype 2. generate BLOB<=>FILE parameter :BLOB_A <=> full-path-filenameA :BLOB_B <=> full-path-filenameB 3. connect to database and issue the following SQL commands: In the normal SQL command memo: INSERT INTO TBL_B( FLD_CHAR, FLD_VAR, FLD_BLOB ) VALUES ( :@Val01 ,:@Val02 ,:@Val03 ); INSERT INTO TBL_B( FLD_CHAR, FLD_VAR, FLD_BLOB ) VALUES ( :@Val01 , 'BLOB_FILE_A' ,:@Val03 ); INSERT INTO TBL_B( FLD_CHAR, FLD_VAR, FLD_BLOB ) VALUES ( :@Val01 , 'BLOB_FILE_B' ,:@Val03 ); In the BLOB<=>File parameter memo: UPDATE TBL_B SET FLD_BLOB= :BLOB_A WHERE FLD_VAR = 'BLOB_FILE_A' ; UPDATE TBL_B SET FLD_BLOB= :BLOB_B WHERE FLD_VAR = 'BLOB_FILE_B' ; 4. set the loop number, and GO! Thus only records where its FLD_VAR value is 'BLOB_FILE_A' will be updated with its blob fields to contain fileA. (records done by the second inserting in the normal SQL command memo) and only records where its FLD_VAR value is 'BLOB_FILE_B' will be updated with its blob fields to contain fileB. (records done by the third inserting in the normal SQL command memo) Tips: type through the SQL command-line, do not use ENTER/TAB to change line since the SQL parser may not recognize the feed-line symbol, and it may thus report a SQL error. The 'constant value' can be used for any data-type accordingly. It will not change throughout the whole inserting. handy and useful. If a blob column/field in tabel_a of database_1 is written onto the SQL script memo of database 4, it will fail in operation. Tips: An advanced user can put any standard DDL SQL command there. It will be executed only once in the final loop of each round of the inserting. If "De-Active All Triggers Before Inserting" checkbox is checked, all triggers will be de-activaed and then re-actived after inserting. If "Generate SQL only" checkbox is checked, only a SQL Script text file is generated, no data is inserted, but only a text SQL Script generated and saved If "Update or Insert" is checked, the SQL command will be UPDATE OR INSERT .... ( only valid for Firebird 2.1 plus, and there must be Primary column/field in the concerned table) If "Save Data Generating SQL Script to.." is checked, all data will be generated, inserted, and also the SQL command used for the inserting is saved as a standard SQL script to a text file. That SQL Script text file can be used later anywhere. Total Number of Loops for Generator, how many ROUNDS (not rows) for each generating of random data. ( not how many records totally) For example there is connected to one database with 3 tables. If here is set to 10, that's to say each table will get 10 rounds of inserting to make 10 new rows, totally 30 new rows made. If 3 databases connected like that, there will be totally 90 new rows inserted. User Defined Value Replacement Prefix: recommended NOT to change, unless for advanced user who wants to use ':@Val' as embedded string. If the ProgressBar is checked, user can see the operation in motion, but it will slow down the whole operation. "GO" button. Press it and see what happens by yourself. User's ISQL Panel is mainly for user to check the result of the random data generating/inserting. It only supports basic SQL command, not the extended ones. and it is not supposed to be used as an general purpose database administration tool. If you want one, it is here gfb and lazafire For example: have Database 1 and the Select SQL Command checkbox checked, then type 'select * from tbl_a' in the SQL memo below. click "Execute SQL" button, and the selected table data is shown in the data grid. (Demo versions can only be connected/operated on the sample databases at the fixed location) All demo versions (linux/windows standard/professional/enterprise) can be downloaded at the download page at this site. FreeDownload If you have any questions you are welcome to contact me at: daomannlee@gmail.com GUIFA Home FLAP |