SQLMAKE is a program which creates a text file of SQL statements that will create a database with approximately the same structure as the starting DP4 database. It can cater for problems caused by SQL reserved words being used as field or table names on the DP4 database, and also for name changes necessary because the target SQL database has a limit on the number of characters permitted in identifiers.
To run SQLMAKE you specify the database name in the usual way, and any of the numerous command tail options as listed in the help for the program. Two text files are used by the program to help generate the SQL, which you can edit to change the program behaviour:
The file reserved.sql contains a list of reserved words on SQL followed by an alternative name to give a field,table or role that uses that name. This file is not guaranteed complete by any means, and the alternatives may not be to your liking, so do edit the file.
The file dtypes.sql contains a list of the DP4 datatypes, with SQL equivalents. The SQL types used by default are somewhat lowest common denominator - for example no attempt is made to use types such as BYTE, BIT(1), or VARCHAR, all of which may be more approriate for your needs. SQLMAKE will always specify a length for character fields, but never for other fields. Therefore for types such as dates and times you may specify any other properties you wish for the field. You may need/want to change the types for dates and times.
The program produces two output files:
You can use the
-limit n
command tail to specify a limit on identifier length. The default is 64,
or 30 if the -oracle option is specified, whereas in previous versions it was
18. SQLMAKE reduces identifier length where necessary by eliminating
underscores, then vowels, then repeated consonants, then 'S' and 'R', and
finally constructing a name based on the internal number of the table/field. By
default the same shortened name will always be used for each table, role, or
field, but apart from that the minimum amount of reduction will be done.
Two options radically affect the amount of name translation that may be required (reducing name translation may be beneficial if you want to be easily port SQL between a DP4 and SQL database)
A new option -index_anonynmous is added. This causes create index statements to use names based on the internal numbering of the DP4 database, not the names. This can dramatically reduce the number of entries in the name translation tables.
A new option -avoid_translation is added. When this option is selected, instead of always using the shrunken version of a database name (especially for fields and roles), the name is only shrunk where necessary. This can cause the fields to have different names on the SQL database where they would have the same name on the DP4 database, but it is harmless and reduces the number of entries in the name translation tables.
SQLMAKE generates CREATE SEQUENCE statements for auto-increment tables when the -oracle option is specified. The name for the sequence can be controlled using the -sequence_format command tail - normally you would specify either "%s_SEQ" or "SEQ_%s".
SQLMAKE does not currently try to specify a precision for numbers, or impose any constraints on the fields (other than that primary keys should not be null). No doubt it would be possible to generate more sophisticated SQL. If further enhancements to this program would be useful to you please let us know what your requirements are.
If you specify the -oracle option the default role separator is changed to _, which is the default for AUXORACL. In this case it is possible that two distinct fields in a table may be given the same name. If you use a $ role separator this cannot happen.
There is a known limitation in the way CREATE INDEX statements are generated: SQLMAKE ensures all indexes are unique by adding in components from the primary key of the table after any secondary key components. In some cases this can result in two indexes being created for the same set of fields. Oracle treats this as an error. The error is harmless, but you can avoid it by switch off one or other of the indexes using SETLINK. If this error arises it is an indication that you can improve your DP4 database by switching off the index with fewer key fields and updating your programs to use the other index. After reorganisation your database will be smaller and updates to affected tables will be faster.
Please make sure the RESERVED.SQL and DTYPES.SQL file are present in the working directory for SQLMAKE.