Title: Best BDE Replacement With SQL
Question: Test of Database Management Systems. BDE replacement tools with SQL.
Answer:
Best BDE Replacement With SQL Introduction At one time, BDE was the database management system of choice, but since Borland stopped supporting it, many companies need to find a good replacement. As Chief Developer of DistcomSoft, the task of sorting out which DBMS wed go with fell to me. This article is a summary of the results of my testing the leading systems. DistcomSofts databases contain more than 5 gigabytes of data and because we mostly use SQL queries in our projects, SQL support is crucial in any DBMS upgrade. Rather than rely on a systems documentation, I decided to test each product with actual data and queries to make certain it was compatible with our existing software and to determine which supported the richest implementation of SQL for future development. Hopefully, this data will be of some help to others who need to replace their BDE software. The Database Management Systems Tested Absolute Database version 4.30 www.componentace.com Advantage Database version 7.0 www.advantagedatabase.com Apollo version 6.1 www.vistasoftware.com DBISAM version 4.05 www.elevatesoft.com KeyDB version 1.43.02 www.keydb.com NexusDB version 1.0 www.nexusdb.com TurboDB version 4.19 www.turbodb.com How the tests were performed We tested the following groups of queries: · Simple SELECT that included WHERE, GROUP BY, ORDER BY, DISTINCT, TOP, INTO, various kinds of JOINs, UNION, EXCEPT, and INTERSECT. · Expressions in SELECT including comparison, logical, arithmetical, string operations, date/time, aggregate functions, data types for auto-conversion, CAST, CASE and IFNULL functions, and expressions in GROUP BY. · Nested correlated and uncorrelated queries, such as IN, EXIST, ANY, ALL, as well as scalar subqueries. · Data manipulation using INSERT, UPDATE, DELETE, CREATE TABLE, ALTER, and DROP. · Index support with CREATE INDEX and DROP, involving case sensitivity and direction. · Transaction support, specifically START TRANSACTION, COMMIT, and ROLLBACK. · Other capabilities such as EMPTY TABLE, spaces in table/field names, etc. Only the standard syntax of queries was checked with each engine. If a query with stanadard syntax was not processed successfully by a database engine, I tried to adapt the query to make it work with this DBMS. I tested only statement support, not the speed of execution. The entire list of test queries was saved in a file, which was read by the test program that checked each query on a sample database loaded into each DBMS. The sample database was in dBase IV format and included the two tables you see below. The tables were filled with plausible data of type integer, string, float and date. The queries are similar to those typically executed on these types of datasets. The tables were either imported from their dBase IV format using utilities shipped with each DBMS or were created in the specific format used by the engine. Test table coders: ID FIRST_NAME LAST_NAME EXPERIENCE SALARY JOINED 1 John Connor 2.00 30000 06/05/2003 2 Dave Rogerson 5.00 32000 09/15/2001 3 Mark Barrel 4.50 34000 05/25/2002 4 Nick Carlson 1.25 36000 11/30/2003 5 John Smith 10.00 38000 02/15/1998 6 Luke Skywalker 0.50 40000 02/01/2004 7 Bred Canvus 3.30 42000 04/09/2003 8 Arthur Clark 4.00 44000 05/25/2002 9 Jimmy Toron 1.00 46000 04/06/2004 10 Ford Smith 2.00 48000 07/18/2003 Test table projects: ID CAPTION LEADER_ID CODERS COST DEADLINE 1 Engine core 5 Dave Rogerson, Mark Barrel 200.00 10/15/2003 2 Core patch #1 5 Dave Rogerson 50.00 11/15/2003 3 Audio plugin 2 John Connor 100.00 12/10/2003 4 Core patch #2 5 Mark Barrel 25.00 12/05/2003 5 Video plugin 10 Nick Carlson 120.00 12/20/2003 6 Core patch #3 5 12.25 01/13/2004 7 Skins support 6 Luke Skywalker 20.00 02/10/2004 8 OS integration 8 Bred Canvus 50.50 02/10/2004 9 Core patch #4 2 Jimmy Toron, John Connor 10.00 02/12/2004 10 *nix implementation 3 Ford Smith 200.00 11/11/2004 The evaluation scheme is simple: if a DBMS can perform a query it gains a point; otherwise it gains nothing. If a query is fatal to the application (access violation, runtime error, infinite loop, etc), the DBMS loses 5 points. In some cases the success of a query depended on the success of a previous query, such as dropping a field after renaming it, so this must be kept in mind when analyzing the results table. The table itself contains a column for each DBMS tested and a column showing the query used in that test. OK means no exception was raised by the query. Failed means the statement/capability is not supported. FATAL signifies the dismal fact that the query killed program execution. Queries Absolute Advantage Apollo DBISAM KeyDB NexusDB TurboDB SELECT * FROM coders OK OK OK OK OK OK OK SELECT * FROM coders ORDER BY FIRST_NAME OK OK OK OK OK OK OK SELECT * FROM coders ORDER BY 2 ASC, 4 DESC OK OK OK OK OK OK OK SELECT First_Name, Last_Name FROM coders ORDER BY Salary OK OK Failed Failed Failed Failed Failed SELECT first_name AS Name FROM coders OK OK OK OK OK OK OK SELECT DISTINCT first_name AS name FROM coders OK OK OK OK OK OK Failed SELECT TOP 5 * FROM coders OK OK Failed OK Failed OK Failed SELECT TOP 5,6 * FROM coders OK Failed Failed Failed Failed Failed Failed SELECT * INTO newcoders FROM coders OK OK Failed OK Failed OK Failed SELECT * FROM projects WHERE cost50 OK OK OK OK OK OK OK SELECT Leader_ID, Sum(Cost) FROM projects GROUP BY Leader_ID OK OK OK OK OK OK OK SELECT Leader_ID, Sum(Cost) FROM projects GROUP BY Leader_ID HAVING SUM(cost)=100 OK OK OK OK OK OK Failed SELECT * FROM projects WHERE Cost=100 UNION SELECT * FROM projects WHERE Leader_ID=2 OK OK OK OK OK OK Failed SELECT * FROM projects WHERE Cost=100 UNION ALL SELECT * FROM projects WHERE Leader_ID=2 OK OK Failed OK OK OK Failed SELECT * FROM projects WHERE Cost=100 UNION CORRESPONDING BY (Leader_ID, Coders, Caption, Cost) SELECT * FROM projects WHERE Leader_ID=2 OK Failed Failed Failed Failed Failed Failed SELECT * FROM projects WHERE Cost=100 EXCEPT SELECT * FROM projects WHERE Leader_ID=2 OK Failed Failed Failed Failed Failed Failed SELECT * FROM projects WHERE Cost=100 EXCEPT CORRESPONDING BY (Leader_ID, Coders, Caption, Cost) SELECT * FROM projects WHERE Leader_ID=2 OK Failed Failed Failed Failed Failed Failed SELECT * FROM projects WHERE Cost=100 INTERSECT SELECT * FROM projects WHERE Leader_ID=2 OK Failed Failed Failed Failed Failed Failed SELECT * FROM projects WHERE Cost=100 INTERSECT ALL SELECT * FROM projects WHERE Leader_ID=2 OK Failed Failed Failed Failed Failed Failed SELECT * FROM projects WHERE Cost=100 INTERSECT CORRESPONDING BY (Caption, Cost) SELECT * FROM projects WHERE Leader_ID=2 OK Failed Failed Failed Failed Failed Failed SELECT First_name+Last_Name FROM coders WHERE Experience*Salary100000 OK OK OK OK OK OK OK SELECT c.First_name+c.Last_Name, p.Caption FROM coders c, projects p WHERE c.ID=p.Leader_ID OK OK OK OK OK OK OK SELECT Caption, Cost FROM projects WHERE Cost50 OK OK OK OK OK OK OK SELECT Caption, Cost FROM projects WHERE Cost150 OK OK OK OK OK OK OK SELECT Caption, Cost FROM projects WHERE NOT(Cost150) OK OK OK OK OK OK OK SELECT Caption, Cost FROM projects WHERE Caption LIKE '%plugin' OK OK OK OK OK OK OK SELECT Caption, Cost FROM projects WHERE Caption LIKE 'Core patch #_' OK OK OK OK OK OK OK SELECT Caption, Cost FROM projects WHERE Cost BETWEEN 50 AND 150 OK OK OK OK OK OK OK SELECT Caption, Cost FROM projects WHERE Coders IS NULL OK OK OK OK OK OK OK SELECT Leader_ID, Caption, Cost FROM projects WHERE Leader_ID IN (5,2) OK OK OK OK OK OK OK SELECT * FROM coders, projects OK OK Failed OK OK OK OK SELECT First_Name, Caption FROM coders c, projects p WHERE (c.ID=p.Leader_ID) OK OK OK OK OK OK OK SELECT c.ID, c.First_Name, c.Experience, p.Caption, p.Cost FROM coders c INNER JOIN projects p ON (c.ID=p.Leader_ID) OK OK OK OK OK OK OK SELECT c.ID, c.First_Name, c.Experience, p.Caption, p.Cost FROM coders c INNER JOIN projects p USING (ID) OK Failed Failed Failed OK OK Failed SELECT * FROM coders c NATURAL INNER JOIN projects OK Failed Failed Failed OK OK Failed SELECT * FROM coders c LEFT JOIN projects p ON (c.ID=p.Leader_ID) OK OK Failed OK OK OK Failed SELECT * FROM coders c RIGHT JOIN projects p ON (c.ID=p.Leader_ID) OK Failed Failed OK OK OK Failed SELECT * FROM coders c FULL JOIN projects p ON (c.ID=p.Leader_ID) OK Failed Failed Failed OK OK Failed SELECT (First_Name || Last_Name) AS FullName FROM coders OK Failed OK OK OK OK OK SELECT UPPER(First_Name) FROM coders OK OK OK OK OK OK OK SELECT LOWER(First_Name) FROM coders OK OK OK OK OK OK OK SELECT LTRIM(First_Name) FROM coders OK OK OK OK Failed Failed OK SELECT RTRIM(First_Name) FROM coders OK OK OK OK Failed Failed OK SELECT TRIM(BOTH 'M' FROM First_Name) FROM coders OK Failed OK OK OK OK OK SELECT First_Name, LENGTH(First_Name) FROM coders OK OK OK OK Failed OK OK SELECT First_Name, SUBSTRING(First_Name FROM 1 FOR 3) FROM coders OK OK OK OK OK OK OK SELECT First_Name, POSITION('a' IN First_Name) FROM coders OK OK OK OK Failed OK OK SELECT UPPER(SUBSTRING(First_Name FROM 2 FOR 3)) FROM coders OK OK OK OK OK OK OK SELECT * FROM projects WHERE NOW OK Failed OK Failed Failed Failed OK SELECT * FROM projects WHERE CURRENT_TIMESTAMP OK Failed Failed OK OK OK Failed SELECT * FROM projects WHERE CURRENT_DATE OK OK Failed OK OK OK Failed SELECT (First_Name || ' has ' || Experience || ' years of experience.') AS phrase FROM coders OK Failed OK Failed Failed Failed FATAL SELECT First_Name+' joined at '+Joined FROM coders OK Failed OK Failed Failed Failed FATAL SELECT COUNT(*) FROM projects OK OK OK OK OK OK Failed SELECT COUNT(Coders) FROM projects OK OK OK OK OK OK OK SELECT MIN(Cost) FROM projects OK OK OK OK OK OK OK SELECT MAX(Cost) FROM projects OK OK OK OK OK OK OK SELECT AVG(Cost) FROM projects OK OK OK OK OK OK OK SELECT SUM(Experience*Salary) FROM coders WHERE ID OK OK OK OK OK OK OK SELECT First_Name, CAST(Experience AS CHAR(10)) FROM coders OK OK FATAL OK OK OK Failed SELECT First_Name, CAST(Joined AS CHAR(10)) FROM coders OK OK FATAL OK OK OK Failed SELECT Caption, IFNULL(Coders, 'None') FROM projects Failed OK Failed OK Failed Failed Failed SELECT First_Name, CASE WHEN Salary40000 THEN 'Expensive' WHEN Salary OK OK OK Failed Failed OK Failed SELECT Caption, CASE Cost WHEN 200 THEN 'Most expensive!' WHEN 10 THEN 'Most cheap!' ELSE 'Normal price' END AS Category FROM projects OK OK Failed Failed Failed Failed Failed SELECT COUNT(*) FROM (SELECT DISTINCT First_Name FROM coders) OK Failed OK Failed FATAL OK Failed SELECT First_Name FROM coders c WHERE c.ID IN (SELECT p.Leader_ID FROM projects p WHERE p.Cost50) OK OK OK OK OK OK Failed SELECT First_Name, Last_Name FROM coders c WHERE EXISTS (SELECT * FROM projects p WHERE p.Leader_ID=c.ID) OK OK Failed Failed OK OK Failed SELECT * FROM coders WHERE Joined OK OK OK Failed OK OK Failed SELECT * FROM coders WHERE Joined OK OK OK Failed OK OK Failed SELECT * FROM coders WHERE Joined IN (SELECT Deadline FROM projects) OK OK OK OK OK OK Failed SELECT * FROM coders WHERE Joined OK OK OK Failed OK OK Failed SELECT * FROM coders WHERE Joined OK OK OK Failed OK OK Failed SELECT First_Name, (SELECT COUNT(*) FROM projects p WHERE c.ID=p.Leader_ID) AS Total FROM coders c OK Failed Failed Failed Failed OK Failed INSERT INTO coders (ID, First_Name, Last_Name, Experience, Salary) VALUES (11, 'Mike', 'Row', 3, 250000) OK OK OK OK OK OK OK INSERT INTO coders VALUES (21, 'Robert', 'Linster', 1, 30000, '10.03.2004') OK OK OK OK OK OK OK INSERT INTO coders VALUES (16,'Roger', 'Pascal') OK Failed OK Failed OK OK Failed INSERT INTO coders SELECT * FROM coders OK OK Failed FATAL OK OK OK UPDATE coders SET Salary=Salary+4000 WHERE Salary OK OK Failed OK Failed OK OK UPDATE projects SET Coders=(SELECT DISTINCT First_Name FROM coders c where c.ID=Leader_ID) OK OK Failed Failed OK Failed OK DELETE FROM coders WHERE ID5 OK OK OK OK OK OK OK CREATE INDEX MyIndex ON coders (ID, First_Name) OK OK OK OK OK OK OK CREATE UNIQUE INDEX MyIndex2 ON projects (Caption) OK OK OK OK OK OK OK CREATE INDEX MyIndex3 ON coders (Experience ASC, Last_Name DESC CASE) OK Failed Failed Failed Failed Failed Failed DROP INDEX coders.MyIndex OK OK Failed OK OK OK OK CREATE TABLE temp (ID INTEGER, XText CHAR(40), Created DATE) OK OK OK OK OK OK OK ALTER TABLE temp ADD More_Text CHAR(20) OK OK OK OK OK OK OK ALTER TABLE temp MODIFY More_Text CHAR(60); OK OK Failed OK OK Failed OK ALTER TABLE temp RENAME More_Text TO NewText OK OK Failed OK OK Failed OK ALTER TABLE temp DROP NewText OK OK OK OK OK Failed OK DROP TABLE temp OK OK OK OK OK OK OK DROP TABLE IF EXISTS projects Failed Failed Failed OK Failed Failed Failed CREATE TABLE "temp tab" (ID INTEGER, XText CHAR(40), Created DATE) OK OK OK OK OK OK Failed ALTER TABLE "temp tab" ADD "More Text" CHAR(20) OK OK OK OK OK OK Failed INSERT INTO "temp tab" (ID, XText, "More Text") VALUES (33, 'One', 'Two') OK OK Failed OK OK OK Failed SELECT * FROM "temp tab" OK OK Failed OK OK OK Failed ALTER TABLE "temp tab" DROP "More text" OK OK OK OK OK OK Failed DROP TABLE "temp tab" OK OK OK OK OK OK Failed START TRANSACTION OK OK Failed OK Failed Failed Failed INSERT INTO coders (ID, First_Name) VALUES (14,'Martin') OK OK OK OK OK OK OK ROLLBACK WORK OK OK Failed OK Failed Failed Failed START TRANSACTION OK OK Failed OK Failed Failed Failed INSERT INTO coders (ID, First_Name) VALUES (14,'Marty') OK OK OK OK OK OK OK COMMIT WORK OK OK Failed OK Failed Failed Failed EMPTY TABLE coders Failed Failed Failed OK Failed Failed Failed Total 104 queries 101 81 54 72 69 78 41 The tests were run on a common desktop: AMD Duron 1400MHz, 256MB RAM, 20GB HDD running Windows 2000 Professional SP3, Delphi 7. Summary of Results for Each DBMS Absolute Database This DBMS proved the most powerful and stable of all the systems tested. It was missing a few capabilities such as EMPTY TABLE, DROP TABLE IF EXISTS and IFNULL, but it far surpassed its closest competitor. I was especially pleased with its transactions support, auto-conversion of data fields and how it manipulated datasets with the EXCEPT and INTERSECT statements. All subqueries were accomplished correctly. Surprisingly, for many tasks, Absolute Database was the only engine that was capable. I also found it could insert a BLOB field within the SQL query MimeToBin() function, as well as work with tables entirely within RAM, which is very handy (and fast). AbsoluteDB comes with two useful utilitiesDBManager and DBImportExportthat greatly simplify work and migration from old database formats. Absolute has a single file database format and can access tables from different databases using the same query. Advantage Database This DBMS displayed good stability and SQL support though it has its own command eccentricities. For example, double and single quotes ( and ) sometime mean different things, which may cause trouble until youre fully acquainted with this software. It cannot access different databases with the same query. Advantage DB does not understand EXCEPT, INTERSECT, CORRESPONDING BY and has problems with JOINs and data type auto-conversions. It has problems with nested SELECTs (examples: SELECT First_Name, (SELECT COUNT(*) FROM projects p WHERE c.ID=p.Leader_ID) AS Total FROM coders c; SELECT COUNT(*) FROM (SELECT DISTINCT First_Name FROM coders)). When performing an INSERT omitting column names you cant insert only some of the first fields; it must be the entire row of data. No queries appeared to be fatal for Advantage Database and it stands in Second Place on the pedestal to the right of AbsoluteDB. Apollo This is the strangest engine I tested. Apollo uses the DBF format to store tables so I thought it would be easy to import the original databases. However, opening the tables in both utilities shipped with the installation resulted in empty columns, all of type DATE. Moreover, when I switched to table structure I saw that the fields simply did not exist! At the same time when I set it all up and performed a simple SELECT * I mysteriously got the correct dataset with all dates visible. Needless to say, I was not impressed. To avoid possible format conflicts I decided to create tables and fill them using the engine itself. Here lay another ambush. If you create the table coders and fill it with data, then create and fill projects, Apollo gives you an sql_execSQL error. On the other hand, if you create and fill projects first, then coders, everything goes fine. Obviously, Apollo has a very unstable engine. Being fed with SELECT First_Name, CAST(Experience AS CHAR(20)) FROM coders, SELECT First_Name, CAST(Joined AS CHAR(20)) FROM coders it hangs up with an access violation. Some queries fail from time to time with NIL stream, sql_closeSQL, sql_getRecord. Sometimes the same query describes its failure with two different explanations. DROP TABLE and DROP INDEX are not supported. Simple math operations like UPDATE ... SET do not work. It doesnt support special directions for fields of an index. For a complete list of what Apollo cannot do refer to the table above. It appears to be that the Apollo developers are taking their first steps in including SQL support into their product. Apollo supports some extended features, yet cannot complete the more basic and typical requests. Although TurboDB scored less, I definitely placed Apollo last on my list of DBMSs to select from. DBISAM This engine comes with transaction support and a flexible command syntax; for instance it understands function calls with different parameter notations: SUBSTRING(astring FROM 3 TO 5) and SUBSTRING(astring, 3, 5) for example. Surprisingly however, it does not support non-alias table prefixes, such as coders.First_Name. It also has problems with nested queries and neither supports the CASE statement nor field type auto-conversions. What really drew blood in its score was the simple query INSERT INTO coders SELECT * FROM coders resulted in an infinite loop with the database growing to eventually fill the entire disk drive and bring down the server. But, even this liability was somewhat compensated by a few DBISAM-specific SQL extensions (like IMPORT, EXPORT, REPAIR TABLE) that may occasionally come in handy. KeyDB KeyDBs syntax remained mostly at the ANSI 92 standard (not bad in my opinion), but its developers included inadequate help on any extensions they did provide. Specifically, theres no information available on which functions can be used, their syntax and their limitations. Further, for some reason, the developers decided to add their own, personal feature. When assigning UdbQuery.SQL property (UdbQuery.Active is False!), some processing occurs and the expression SELECT COUNT(*) FROM (SELECT DISTINCT First_Name FROM coders) results in the message SexprNoRParen, [TokenName]. Such behavior is suspicious. Also, the not unusual command SET Salary=Salary+4000 did not work. The functions NULLIF and CAST are not supported. It cannot access tables from different databases in one request. However, KeyDB executed all JOINs correctly and successfully performed many of the SELECT FROM SELECT queries, which gives me a bit of hope that with hard work the developers could produce a decent engine some day. NexusDB Working with NexusDB I got the distinct feeling that its developers just love complexities. To perform a single query on the local table it took five (!) components on the form. This DBMS is recommended for those people who love to customize and tweak. Transactions cannot be accomplished through SQL, it does not support EXCEPT/INTERSECT, field auto conversion, IFNULL nor searched CASE and chokes on UPDATE projects SET Coders=(SELECT DISTINCT First_Name FROM coders c where c.ID=Leader_ID). It cannot access tables from different databases and does not support non-alias table prefixes (coders.First_Name). But, on other groups of queries NexusDB deservedly received a high score and gets Bronze. TurboDB It is hard to say anything good about TurboDB. My first disappointment was its buggy export/import utility. Second was in discovering it does not understand a capitalization change: field First_Name is not First_name, but is recognized as FIRST_NAME. For example, the functions Ltrim, Rtrim must be written exactly that way, not ltrim or LTRIM. Queries such as SELECT (First_Name || ' has ' || Experience || ' years of experience.') AS phrase FROM coders and SELECT First_Name+' joined at '+Joined FROM coders result in a 216 error with its inevitable consequences. The unsupported SQL statements list is much too long. Refer to the table above for details. Summary Nothings perfect in this world, Im told, and so it is with these top DBMSs. I guess I was naively hoping to find an ultimate database system, but every one I tested was missing something. Even so, the DBMS I chose to migrate to was clear: Absolute Database. I give it top awards as: most reliable, easiest to use and widest range of SQL commands available. Besides having a first-rate SQL engine, it provides high-quality utilities and useful documentation. If you need to build a client-server complex, try Advantage Database or NexusDB to setup connection options to possibly achieve a better speed. But if a file-server is what youre looking for, I havent found any better than Absolute DB. In multi-user mode it preserves all its advantages. Use the link below to get the sample test tables, SQL queries list used on each DBMS and the source code of the test program. sqltest.zip Send any questions or remarks to roman.korzh@hotpop.com I hope this overview helps you save some time and money in your work. Roman Korzh, Chief Developer of DistcomSoft