Databases, USMS & anywhere
This is a menu of the topics on this page (click on any):
This is a document started by Pieter Cath and helped along by Carl House. It will evolve to describe the databases used in USMS and to make general observation about databases available to USMS. This has been created because USMS is reviewing and preparing to make policy about databases and because USMS committees have ongoing work that must be done with sound database choices.
Most programs can import and export data in several different formats like the *.dbf format and several text-based formats.
We have been using comma-delimited text files with success for many years. In this format each line in the text file contains all data fields in one record in the database. The different fields are separated by commas. Trailing blanks in the fields are normally omitted so that this format can handle varying field lengths and generates smaller files than a fixed-field format. One disadvantage of this format is that no commas are allowed within a field. Comma-delimited files can be viewed and edited using a text editor, or even a word processor.
The other text-based format that is widely used is the DIF (Data Interchange Format). Again, one line contains all the data fields in one record of the database except that each field is stored with trailing blanks, making delimiters (like commas) unnecessary. The position of each field in the line is always the same. The swimmers SDIF format uses this approach and is widely used by Hy-Tek.
CSV stands for Comma-Separated-Values. It is a form of a comma-delimited text file. By default, Excel sets itself up as the associated application for .csv files when installed, and any other spreadsheet application can read them as well. It's a very commonly used format for downloading data from the web. Hy-Tek's web database for swimming performance lets you download swim results in CSV format.
The USMS National Office uses a spreadsheet (Excel) as a database program to store swimmers registration data.
SQL (Structured Query Language) is an advanced language developed for relational databases that can be used to define, manipulate, display and update data. Since it was developed in the mid-1970's, SQL has been adopted by many companies as a database language standard for both mainframe and minicomputer environments. So SQL is independent of the actual database engine that is used. SQL itself is still under development and we have SQL1, SQL2, SQL3.
SQL is not a DBMS (Database management system) like dBase, Clipper, Oracle, FoxPro, or Access. This means that if your database programs are written in SQL, they have a good chance of working with any of the major DBMS systems as long as they support SQL.
Oracle is the premier enterprise database system, but their products are expensive.
SQL Server
Microsoft's SQL Server is also a widely respected enterprise database system,
but it is also very expensive.
Pervasive.SQL combines virtually all of the best database features in a single product.
It is mature, reliable, fast, inexpensive, flexible, scalable, and portable.
It is simple to use and requires virtually no maintenance or oversight.
It runs on many different platforms and operating systems,
interfaces to any computer language, and handles web accessibility (and its heavy loads) well.
It uses Microsoft's standard ODBC and OLEDB database interfaces (plus Java/JDBC and many others)
and uses the standard SQL command language.
It has numerous advanced technical features and has a very long
(almost 20-year) history of proven performance and fully backward compatible upgrades.
For more information about Pervasive.SQL, see:
www.pervasive.com - Main web site
www.pervasive.com/psql - Product overview
www.pervasive.com/support/datasheets.asp - More detailed product information
www.pervasive.com/support/whitepapers.asp - Decision-making kinds of information
www.pervasive.com/downloads - Free 30-day trial full-product evaluation download
dBase was the first database language for personal Computers. FoxPro and Clipper are both what are called dBase dialects in that they all use the same language for procedure (program) files with each having their own little differences and enhancements.
FoxPro is now owned by Microsoft and has evolved into Visual FoxPro.
Clipper was owned until recently by Computer Associates and has a windows version called Visual Objects.
The USMS registration program (Leoware) was written in Clipper because, at the time, it was the only database program that would produce a freestanding executable (*.exe) file. The top-ten programs are written in dBase.
These database programs all store the database in dBase format (*.dbf), making them compatible as far as data interchange is concerned. However, they do not all use the same format for index files. (Index files are used for sorting).
The problem with Access, which is used by the latest Hy-Tek programs is that Microsoft does not make any attempt to keep new versions of the data files backward compatible.
Visual Basic has the capability to use database files of different formats. When Access formats are used, VBasic also suffers from the incompatibility between different versions of the Access formats. Visual Basic also supports SQL.
Access is said to not be appropriate for very large databases (over 100,000 records). It was designed as a desktop database solution, not for use on a server.
InterBase, currently owned by Borland, is sold as a commercial product. It would probably be available on the USMS server without any licensing fees. It runs on both Windows and Linux platforms.
MySQL is freely available on many Windows and Linux servers including the USMS server.
It is said to be very fast, especially for "selects".
It is probably not faster than other databases for "inserts" or "updates".
websites about MySQL:
www.mysql.com
www.mysql.com/documentation - documentation page
mysql.com/products/what_is_mysql.html - A high level overview of what MySQL is.
PostgreSQL
PostgreSQL's advantage is that it is free on Windows and Linux platforms
and more complete in terms of advanced SQL support than MySQL.
websites about PostgreSQL:
www.us.postgresql.org
postgresql documentation page
Databases and languages can communicate with each other through interfaces. Microsoft created "ODBC" (Open DataBase Connectivity) sometime around 1990 as a standard interface and it is still widely in use. Approximately 1997 Microsoft created "ADO" to serve as a hub for data connectivity and the interface on each side of it is called "OLEDB" (pronounced "o-lay-dee-bee"). ADO/OLEDB has distinct advantages in ease of programming over ODBC and is likely to increasingly displace ODBC in future years. ODBC, ADO and OLEDB are Windows facilities, so they help in the local computer. Equivalent interfaces may be available and/or needed on the server.
Conclusions
The SQL platform used for H&A should meet the following criteria.
- It should have an ADO/OLEDB interface.
- It should be capable of running on a standalone system (not a server).
- We should be able to circulate information on a compact disks which will use the SQL database at little or no incremental cost for each additional CD.
- (other?)
