Skip to content

To Do List

Jump to bottom
Roberto Artigas edited this page Jan 27, 2024 · 30 revisions

Do yourself a favor, create a to-do list for your ISAM to SQL conversion. Even with the list, you are going to miss something. If you miss something, add it to your list. At the end of your process, when you have something in production, please send me your list with any improvements. It will be appreciated by all that come to this page. I will upgrade the to-do list and give you credit. And that new list can be printed at the start of a new conversion by anyone that needs it, to do their conversion.

You have to start somewhere. (Why?, How?, What?)

  • Reasons for conversion

    • Goals, Purpose, Motivation
    • What is the plus (benefits)
    • What is the minus (costs)
    • Short term VS Long term
    • When you sell your business?

  • Who? wants What?

    • Important VS Urgent
    • Architecture VS Requirements
    • Builder VS Stakeholder
    • Infinite VS Finite

  • Conversion Considerations

    • Customers, Clients
    • Existing System
    • Which SQL backend
    • Operations Environment
    • Additional Complexity
    • System and User Security
    • Backup and Retention
    • Deployment Ease
    • Periodic System Upgrade
    • Process Automation
    • Training (IT, Users)
    • Auditing and Balancing
    • Disaster Recovery

  • Versioning the DATABASE backend

    • Customer updates: What version do they have?
    • Database Version: What version is the latest?
    • Scripts to convert database formats
    • Scripts to convert database data
    • Keeping track of the customer
      • Hardware
      • Operating System
      • Software
    • Managing the customer versioning

  • Create to-do List

  • Conversion activity notebook

  • Make a dictionary SQL ready

    • Naming conventions
      • Do you have any?
      • Do you document?
      • Disciplined process?
      • table prefixes
      • table aliases
      • keys: pkey, fkey, ukey, key
      • relations: fromtable-totable
    • Check dictionary for errors
      • Tables RASQL(NO) for no generation.
      • Tables with no keys
      • Tables with no primary keys
      • Multiple fields on the primary key.
      • GLOBAL: Duplicate keys
      • ...
    • Correct and cleanup dictionaries
      • External name attributes
      • Initial values
      • Tab names (Data, Internal, ...)
      • MEMO, BLOB might cause problems (structure 47's)
      • CSTRING to STRING (utf-8, utf-16)
      • ...
    • Adjust for reserved words
      • SQL backends and Clarion

  • DCT2SQL: Useful options

    • Add "_" add the end of reserved word
    • Add table prefix to identifiers (LOOKING INTO IT FOR INDEXES)
    • Add "quotes" around the identifiers
    • Make identifiers all lower case
    • Do not change LONG to DATE/TIME

  • Validate and audit dictionary

    • DCT2SQL: GUID
    • DCT2SQL: FILE
    • DCT2SQL: PREFIX
    • DCT2SQL: DLO
    • DCT2SQL: RESERVED
    • DCT2SQL: DUPKEYS
    • DCT2SQL: XREF FIELDS
    • DCT2SQL: XREF KEYS

  • What did I forget?

  • What don't I know?

  • What can go wrong?

  • Create TestODBC program

    • Use the WIZARD to generate

  • Refactor ISAM dictionary

    • Get changes working

  • Non-Distributed Data Base

    • Global area (G:driver, G:owner variables)
    • G:owner = 'Driver=PostgreSQL Unicode;server=127.0.0.1;database=databasename;uid=???;password=???;port=5432;'
    • Table: General: Driver: ODBC
    • Driver options: /BUSYHANDLING=2 /NESTING=TRUE /VERIFYVIASELECT=TRUE /AUTOINC='SELECT currval(''define.tablename_seq'')'
    • Owner Name: !G:Owner
    • Full Path Name: define.table name
    • File: Options: STRING: EMULATEAUTONUMKEY (TRUE)
    • Field: Options: Predefined: SoftVelocity.ABC and Clarion.IsIdentity IsIdentity (TRUE)
    • Side by side testing items.

  • Distributed Data Base

    • Time stamp fields (ts, sts, dts)
    • Encryption version identifier
    • Global Unique Identifier (guid, uuid)
    • Data Sources (internal, external)
    • Data Synchronization
    • Auditing and Balancing

  • Create SQL DDL scripts

    • Connection strings
    • Create script for backend
      • Create table
      • Create primary index
      • Create sort order index
      • Create trigger
    • Test against backend
    • Referential integrity last (by itself) thing
      • Create for all tables that exist.
    • Test against backend (again)

  • Generate some data

    • Initial Values

  • Convert data from ISAM to SQL

    • Write CSV output program for each table ( DUMPLOAD )
    • Write SQL load query for each table
    • Write query to read the last ID and update sequence
    • Write your own conversion program

  • Testing conversion (TestODBC)

    • browse, form (CRUD)
      • Insert record (1). Save. Make changes. Save.
      • Insert record (2). Save.
      • Delete record (2). Gone!
      • Insert record (3). Save.
      • Browse should show ID 1 and 3.
    • reports
    • processes
    • RECALL: hot fields in browse and processes
    • Resetting table and sequences while testing (PostgreSQL)
      • TRUNCATE define.tablename CASCADE;
      • DROP SEQUENCE IF EXISTS define.tablename_seq CASCADE;
      • CREATE SEQUENCE define.tablename_seq INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1;
      • ALTER TABLE define.tablename ALTER COLUMN sysid SET DEFAULT NEXTVAL('define.tablename_seq');
      • SELECT last_value AS tablename_seq FROM define.tablename_seq;

  • The DEMO test (when you get it running)

    • Developers: Process maintainability and code quality. Would they maintain this?
    • CEO, CFO, CTO: Meeting business goals. Would they buy this?
    • Audience: The elevator speech (Punch a higher floor!)
    • Reason: Let's Go Crazy: What's it all for?

  • Day-to-Day Production

    • DO Automation or Self-Service (Never send a human to do a machine's job)
    • Repeatability and Traceability
    • Disaster scenarios and outages
    • Describe your current state: Business and Software
    • Why?

  • Periodic maintenance

    • DO Automation or Self-Service (Never send a human to do a machine's job)
    • Repeatability and Traceability
    • Disaster scenarios and outages
    • Describe your current state: Business and Software
    • How?

  • Development Changes

    • DO Automation or Self-Service (Never send a human to do a machine's job)
    • Repeatability and Traceability
    • Disaster scenarios and outages
    • Describe your current state: Business and Software
    • What?

  • Planned Obsolescence

    • Obsolescence is how you got here.
    • I brought you into the world, I can take you out.
    • It's my DATA, you are just here for the ride.
    • Get your DATA out of the gutter.
    • Get your DATA out to play with others.
    • You might want to have a plan to do it again!

  • What were the Conversion Results

    • IF it hurts, THEN do it again.
    • FORGET the pain, RECALL the knowledge.
    • DCT2SQL: It's Alive!

Code Wiki Main Repositories

Progress increases the complexity of the new problems. Advancement solves those complex new difficulties. This creates additional new progress. And then, the cycle repeats.
Roberto Artigas y Soler

¿Quién es esta niña?
Señorita más fina.
Tu corazón es suyo.
Who's That Girl - Madonna

Copyright (c) 2020, Roberto Artigas, All rights reserved world wide. - roberto.artigas.dev@gmail.com

Clone this wiki locally