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
Re: [GLLUG] Openssl and certificates
Hello, On Tue, Jul 28, 2020 at 09:47:51AM +0100, Chris Bell via GLLUG wrote: > Openssl makes it easier to create my own CA and issue certificates for local > boxes with specified uses such as WWW and EMAIL, but I am not clear on the > best > approaches for multiple domains and boxes. I have dedicated individual boxes > to use as web server, email gateway, and email server, and multiple boxes for > each job to enable online backup and offline upgrades. Should individual > certificates be created for individual boxes or should the same certificate > be > shared between all boxes allocated for each individual job? I don't think TLS concerns itself with what particular piece of hardware is involved, it's about what is terminating the TLS conversation for a given name. If the conversation for foo.example.com could end up at any one of several hosts then all hosts need the same TLS key material. If you're terminating the conversation on a single load balancer with 20 hosts behind it but you're not talking TLS between the load balancer and the hosts, then only the load balancer needs the key material. If you have an active/passive pair of load balancers to provide redundancy then both need the key material. And so on. I create them with Let's Encrypt and have config management renew them and push them out to where they need to be, so it doesn't really matter how many there are. If you had a web site on https://example.com/ I don't think you would be wanting to call your mail server also example.com, so the question of whether to share the key material doesn't arise. But let's say for argument's sake that your mail server calls itself mail.example.com and you also have webmail on https://mail.example.com/. Should those two things share the same key material? With config management it is almost as easy to have them have unique key material as it is to have them share. For long-lived keys there is an argument to have them be separate so as to have fewer copies that could be mislaid, but in the Let's Encrypt age the certs are renewed every three months so that is less of a concern. Also whether to use a single wildcard cert for everything under example.com. With frequent renewal I think you could argue either way. I'd be more concerned about automation and only then think about whether to use one or many or wildcard certs for the same name. If the names are not valid outside your local network (e.g. you expect users to connect to private DNS names like https://admin.mycorp/) then you can't use Let's Encrypt and have to do your own CA, which does make things a lot more of a faff. I tend to argue for things being in the public DNS for this reason, as at least then you can do ACME DNS-01. Cheers, Andy -- https://bitfolk.com/ -- No-nonsense VPS hosting -- 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
Re: [GLLUG] Openssl and certificates
On Tue, 28 Jul 2020 at 09:48, Chris Bell via GLLUG wrote: > > Hello, > Openssl makes it easier to create my own CA and issue certificates for local > boxes with specified uses such as WWW and EMAIL, but I am not clear on the > best > approaches for multiple domains and boxes. I have dedicated individual boxes > to use as web server, email gateway, and email server, and multiple boxes for > each job to enable online backup and offline upgrades. Should individual > certificates be created for individual boxes or should the same certificate be > shared between all boxes allocated for each individual job? > Thanks for any information. > -- > Chris Bell > Website http://chrisbell.org.uk > Certificates for use with TLS have a number of possible options: 1) Host specific. So contain only one domain name. e.g. www.website.com 2) Multiple hosts sharing the certificate. Using something called "subject alternative names", you can have one cert, multiple domains. e.g. www.website.com, www.second.com, www.third.com all in a single cert. e.g. www.website.com, email-gw.website.com, email-srv.web all in a single cert. 3) Wildcard e.g. *.website.com So, the decision to have multiple certificates, or one wildcard certificate is really up to you. Either options are workable. If you use your own CA with openssl, it will work for you locally, but external users will not have the correct trusted root certificate so they will see warnings before connecting. Kind Regards James -- GLLUG mailing list GLLUG@mailman.lug.org.uk https://mailman.lug.org.uk/mailman/listinfo/gllug
[GLLUG] Openssl and certificates
Hello, Openssl makes it easier to create my own CA and issue certificates for local boxes with specified uses such as WWW and EMAIL, but I am not clear on the best approaches for multiple domains and boxes. I have dedicated individual boxes to use as web server, email gateway, and email server, and multiple boxes for each job to enable online backup and offline upgrades. Should individual certificates be created for individual boxes or should the same certificate be shared between all boxes allocated for each individual job? Thanks for any information. -- Chris Bell Website http://chrisbell.org.uk -- GLLUG mailing list GLLUG@mailman.lug.org.uk https://mailman.lug.org.uk/mailman/listinfo/gllug