Re: [GLLUG] Moving from a Paradox database on Microsoft to Linux
On Wednesday, 29 July 2020 19:09:24 BST Bernard Peek via GLLUG wrote: > That's a problem but it's manageable. If your data comes from multiple > tables you do need to understand the relationships between them. You also > need to understand the difference between tables and queries/views and the > terminology used by both databases. Check which tables have indexes. Check > whether the system you are extracting data from enforces any validation or > business rules. That might be handled by code in data-entry forms. > > I recommend importing raw data and adding keys and indexes afterwards. You > may well find that adding those rules generates errors. If you get an error > it's almost certainly because you haven’t understood the data. > Thanks for the advice, forwarded to Rosco. -- Chris Bell Website http://chrisbell.org.uk -- GLLUG mailing list GLLUG@mailman.lug.org.uk https://mailman.lug.org.uk/mailman/listinfo/gllug
Re: [GLLUG] Moving from a Paradox database on Microsoft to Linux
That's a problem but it's manageable. If your data comes from multiple tables you do need to understand the relationships between them. You also need to understand the difference between tables and queries/views and the terminology used by both databases. Check which tables have indexes. Check whether the system you are extracting data from enforces any validation or business rules. That might be handled by code in data-entry forms. I recommend importing raw data and adding keys and indexes afterwards. You may well find that adding those rules generates errors. If you get an error it's almost certainly because you haven’t understood the data. -Original Message- From: GLLUG On Behalf Of Martin A. Brooks via GLLUG Sent: 28 July 2020 18:13 To: The mailing list for the Greater London Linux User Group Cc: Martin A. Brooks Subject: Re: [GLLUG] Moving from a Paradox database on Microsoft to Linux On 2020-07-28 17:40, John Winters via GLLUG wrote: > Importing into any Linux database from a CSV file is easy-peasy and > can be accomplished in 5-10 lines of code. That is almost certainly guaranteed not to work for any relational database as the relations will not be maintained. -- GLLUG mailing list GLLUG@mailman.lug.org.uk https://mailman.lug.org.uk/mailman/listinfo/gllug
Re: [GLLUG] Moving from a Paradox database on Microsoft to Linux
On 28/07/2020 18:13, Martin A. Brooks via GLLUG wrote: > On 2020-07-28 17:40, John Winters via GLLUG wrote: >> Importing into any Linux database from a CSV file is easy-peasy and can >> be accomplished in 5-10 lines of code. > > That is almost certainly guaranteed not to work for any relational > database as the relations will not be maintained. Not true at all. The relations are easily maintained. Either preserve the existing keys and references, or if it is necessary to generate new key fields then construct an in-memory mapping between them and convert whilst loading. I have used the technique as described above on several occasions and it works just fine. John -- Xronos Scheduler - https://xronos.uk/ All your school's schedule information in one place. Timetable, activities, homework, public events - the lot Live demo at https://schedulerdemo.xronos.uk/ -- GLLUG mailing list GLLUG@mailman.lug.org.uk https://mailman.lug.org.uk/mailman/listinfo/gllug
Re: [GLLUG] Moving from a Paradox database on Microsoft to Linux
On Tue, 28 Jul 2020 at 17:02, Chris Bell via GLLUG wrote: > > Hello, > I have been asked how best to transfer the data from a Paradox database to > something suitable on Linux. Any information welcome. Thanks. > -- While I have not used Paradox. I do have experience of transferring data from proprietary databases to more open ones. Steps I take: 1) Backup the existing database and create a VM or similar that can read it. This gives you a safe copy of the database and more importantly, the application that is used to access the database, in case months down the line you realise something is missing. 2) Note down as much about the database as possible. DB version. Driver versions etc. Depending on how old the database is, you might have a standard SQL interface to it. 3) Count the amount of rows in each table. 4) Do some test queries, and capture the output. Used later where you then do the same queries on the exported data to test compare that the data is identical. 5) Repeat (3) so that it covers all the tables. 6) Based on what you gather in (2) you have the following options: a) Use the original database's export features. (Quite often the best option) b) Use standard connectors, e.g. ODBC or JDBC to extract the data. c) Use a tool already written by someone to migrate the data. E.g. Paradox to Postgres or Paradox to mysql. Once you have the data in open source DBs such as postgres or mysql, it is then easily accessible and can be converted to other formats. 7) Points to note: DBs store fields in a large variety of character encodings. Rerun the queries you found in step 3 and 4 to ensure that you get all the same results on the exported data. DBs have field types, foreign keys, schemas. This metadata also needs exporting, and not only the records themselves. DBs also have "stored procedures" and various defined "views", different types of indexed fields. DBs behave differently. For example MS ACCESS definition of TRUE/FALSE in SQL query expressions is different from MYSQL's definition of TRUE/FALSE Exporting the DATA is one thing. Ensuring that the new application that you are using to access the DATA works the same as the old application is quite another matter and can be difficult to get right due to SQL differences between databases. Kind Regards James -- GLLUG mailing list GLLUG@mailman.lug.org.uk https://mailman.lug.org.uk/mailman/listinfo/gllug
Re: [GLLUG] Moving from a Paradox database on Microsoft to Linux
On 2020-07-28 17:40, John Winters via GLLUG wrote: Importing into any Linux database from a CSV file is easy-peasy and can be accomplished in 5-10 lines of code. That is almost certainly guaranteed not to work for any relational database as the relations will not be maintained. -- GLLUG mailing list GLLUG@mailman.lug.org.uk https://mailman.lug.org.uk/mailman/listinfo/gllug
Re: [GLLUG] Moving from a Paradox database on Microsoft to Linux
On Tuesday, 28 July 2020 17:40:36 BST John Winters via GLLUG wrote: > On 28/07/2020 17:01, Chris Bell via GLLUG wrote: > > Hello, > > I have been asked how best to transfer the data from a Paradox database to > > something suitable on Linux. Any information welcome. Thanks. > > When called upon to move large datasets between systems, I've generally > done it via CSV files, which makes it easy to check the data and > identify which half - export or import - has a problem if there are > issues. A quick web search indicates that there are a wide range of > exporters which will turn Paradox into CSV. > > The biggest issue which I've found is with character encodings. You > need to check what the existing database uses, and then check further > that the text fields do indeed contain data in that encoding. Sometimes > I've found that the source database claims one encoding, but then some > of the text fields are in a different one. The matter is further > complicated when you have to deal with third parties who don't know what > a character encoding is and behave as if you've invented them just to > annoy them, mentioning no names, cough, iSAMS. > > Importing into any Linux database from a CSV file is easy-peasy and can > be accomplished in 5-10 lines of code. > > HTH > John Thanks for the info, I am passing all the information on. -- Chris Bell Website http://chrisbell.org.uk -- GLLUG mailing list GLLUG@mailman.lug.org.uk https://mailman.lug.org.uk/mailman/listinfo/gllug
Re: [GLLUG] Moving from a Paradox database on Microsoft to Linux
On Tuesday, 28 July 2020 17:31:47 BST Martin A. Brooks via GLLUG wrote: > On 2020-07-28 17:01, Chris Bell via GLLUG wrote: > > Hello, > > I have been asked how best to transfer the data from a Paradox database > > to > > something suitable on Linux. Any information welcome. Thanks. > > A little googling suggests there are tools for automatically converting > paradox database to Postgres. You could try one of those. > > > > Mart. Hello Mart, Thanks for the reply, that is what I guessed. About all I know about databases is that there are many available, and Postgres has been recognised as one of the best for a long time. -- Chris Bell Website http://chrisbell.org.uk -- GLLUG mailing list GLLUG@mailman.lug.org.uk https://mailman.lug.org.uk/mailman/listinfo/gllug
Re: [GLLUG] Moving from a Paradox database on Microsoft to Linux
On 28/07/2020 17:01, Chris Bell via GLLUG wrote: > Hello, > I have been asked how best to transfer the data from a Paradox database to > something suitable on Linux. Any information welcome. Thanks. When called upon to move large datasets between systems, I've generally done it via CSV files, which makes it easy to check the data and identify which half - export or import - has a problem if there are issues. A quick web search indicates that there are a wide range of exporters which will turn Paradox into CSV. The biggest issue which I've found is with character encodings. You need to check what the existing database uses, and then check further that the text fields do indeed contain data in that encoding. Sometimes I've found that the source database claims one encoding, but then some of the text fields are in a different one. The matter is further complicated when you have to deal with third parties who don't know what a character encoding is and behave as if you've invented them just to annoy them, mentioning no names, cough, iSAMS. Importing into any Linux database from a CSV file is easy-peasy and can be accomplished in 5-10 lines of code. HTH John -- Xronos Scheduler - https://xronos.uk/ All your school's schedule information in one place. Timetable, activities, homework, public events - the lot Live demo at https://schedulerdemo.xronos.uk/ -- GLLUG mailing list GLLUG@mailman.lug.org.uk https://mailman.lug.org.uk/mailman/listinfo/gllug
Re: [GLLUG] Moving from a Paradox database on Microsoft to Linux
On 2020-07-28 17:01, Chris Bell via GLLUG wrote: Hello, I have been asked how best to transfer the data from a Paradox database to something suitable on Linux. Any information welcome. Thanks. A little googling suggests there are tools for automatically converting paradox database to Postgres. You could try one of those. Mart. -- GLLUG mailing list GLLUG@mailman.lug.org.uk https://mailman.lug.org.uk/mailman/listinfo/gllug
[GLLUG] Moving from a Paradox database on Microsoft to Linux
Hello, I have been asked how best to transfer the data from a Paradox database to something suitable on Linux. Any information welcome. Thanks. -- Chris Bell Website http://chrisbell.org.uk -- GLLUG mailing list GLLUG@mailman.lug.org.uk https://mailman.lug.org.uk/mailman/listinfo/gllug
