ADS

[rdonnay]

This should take significantly less time than a year.

I trust your instincts.
I'll have a talk with Bobby.

[Tom]

These are the most important tasks:

1. Get the upsizing done. We don't use the automatic upsizing. We create upsize files by ourselfes and control the work of the dbfupsize.dll. You need server data in your app, you may need to create the target database with SQL, which is not a big issue. Good to know: The PGDBE takes care of your character set. Even if you still work with OEM - no problem!

2. If you want to remove all physical folders, get an idea about what to do with data outside DBFs, like XPF-files, documents and so on. Maybe it's a good idea to move this stuff into tables aswell: Collect all XPF data and put it in one table.

3. Establish a server connection like for ADS. Set the connection as the default for every thread your app creates.

4. Look at service functions. DbPack() is still rather slow. Do you really need it? Same with OrdCreate().

5. Watch for filters. Complex filter expressions don't work. You can't use your functions in filters, you even can't use all Xbase++-functions, so maybe it's a good idea to get rid of all filters. Since you still can use whatever your filter expression was (no matter how complex) with DbEval() or Eval(), maybe moving from DbSetFilter() to DbEval() is an idea. Maybe there are other ideas (we created a function called "BuildSqlFilter()" which works for small tables - it collects the records with DbEval() and creates a filter expression afterwards). Maybe using Passthrough SQL here is an idea. Use DataObjects. Create more index files. Whatever. Get rid of complex filters.

6. Replace dealing with physical files like the usage of File() and all copy functions. Use Table() and others instead.

This is mainly it. Things get a bit more complicated when working with multi-tenancy.

We managed to serve all three data models in the app, a file based database, ADS and PGDBE, so we only need to maintain one code base and move our customers to SQL step by step. This would be completely impossible with every other solution.


Add: You need to think about backup concepts, and maybe delivering the software gets a little more complicated. You need to learn a little about pgAdmin and find an idea about how to deal with PostGreS-updates (there is a new server installation with every major update). Besides, knowledge about SQL is an excellent idea.

[rdonnay]

Get the upsizing done.

This is what stopped me the last time I started on this project. I had a phone conversation with Steffen and told him that we needed much more control over the upsizing process so we could upsize from an ADS server connection. Are you saying that this is possible now?

data outside DBFs, like XPF-files, documents and so on.

We collect a lot of documents. We must have on file many images, signatures, etc. I see no reason to move this to a data table because we have complete control over the installation and the server. Documents are being created constantly.

Establish a server connection like for ADS.

That is already done in the current code. No reason to do it differently with PGDBE.

Watch for filters.

We don't use filters. We use scopes and SQL WHERE clauses. We already use a lot of SQL via ADS.

Use Table()

We already do that. I insisted on using a dictionary connection rather than a free connection many years ago. We have also needed that for encrypted data.

move our customers to SQL step by step.

I have been doing this for several years now with ADS.

It is now up to Bobby to convince the new owners of the business that this is a wise investment of our time.

[Tom]

Are you saying that this is possible now?

No, it isn't. There is an upsize file (XML) and the DbfUpsize() function, which does the job. But we created our own UI for that, using the responses of DbfUpsize(). The function still awaits physical tables.

[rdonnay]

The function still awaits physical tables.

That seems to be a deal killer for us.
Our data tables are huge.
We would need to be able to upsize without having to export all the data from ADS to FOX.
I don't think that FOX can even handle our tables now.

[SlavkoDam]

I don't think that application code migration from ISAM to true SQL would be so long process (1 year ), if you have the right tool. In PowerSql library you have the same SQL function for each DBF function, there is one to one counterpart with very similar name. You don't have to manually create SQL connection and dataset objects and work with their properties and methods, like in SqlExpress (monkey work ). With PowerSql library everything is straightforward.

So, with PowerSql the true SQL migration would be very fast and easy, there is no need to change your basic application logic at all. Besides, you can use any SQL DBMS that you or your client want, not only PG. You can use the multi-schema model too, if you need that. Overall, true SQL is much faster than Alaska PGDBE, since there is no additional internal ISAM/PG maintenance to do.

Don't hesitate to modernize and advance your applications with true SQL. Its not the future, its the present and you have to catch it. Use PowerSql to do that in a fast and easy way.

[Tom]

Don't hesitate to modernize and advance your applications with true SQL. Its not the future, its the present and you have to catch it.

SQL is just language. It's a way to get data back from a database. SQL ist not a technique (not a technical solution). You can have bad or good servers, or good or bad connections, and it doesn't matter how you get your data here. We all don't know what happens tomorrow, and we all are still a little suprised about what happend in the AI-world during the last few years.

Having an SQL server is what you need today, this is correct. But nobody sees how the data access in your app exactly works, how you get your record sets or update data. And nobody is interested, as long as it's a good and cheap and reliable SQL server. Maybe your way is "true SQL". I personally don't care about that. I want to have good results in short time. PGDBE in my opinion is the best way for that.

[SlavkoDam]

I want to have good results in short time.

I don't think that 1 year is a short and acceptable time. If you have it, good. And I do care about the optimization of my database and application and want the best results from them. I will always work with direct and best technique and not some simulation. It is not the same and will never be. As a programmer, I want to keep forward and develop myself with modern techniques and solutions, and not to get trapped with old one. I think that we all should.

Anyway, as you said once: All roads lead to Rome.

[Tom]

I don't think that 1 year is a short and acceptable time

That was Roger's question. The time we invested to migrate to PGDBE was about four weeks (net).

[slobodan1949]

Hello everyone,

I am nearing the end of the translation of the accounting ADS application (database) to PostgreSQL (PGDBE) application (database). I enjoy the job just as much as I did when I started working with Clipper 87 40 years ago. It's always good to do new interesting things. Old things get boring over time and start tiring a person. I thought it would be good to share some of my experiences from that job, but it so happened that Tom joined this post and said everything I wanted to say. All I have to do is confirm it. Read carefully all his arguments in this post. These findings are not philosophical positions without confirmation in practice. Those arguments are the result of experience from a large operational job on the transition of serious business software from ADS server and DBF ISAM database to PostgreSQL server and SQL tables.

I could only add the following:

- I had to make my own UPSIZE tool by writing my own code. Instead of DBFUPSIZE.EXE I use DBFUPSIZE.DLL and some functions from that library and my own functions that do the same job as DBFUPSIZE.EXE
- I don't create SQL tables like the full UPSIZE that is obtained from Alaska Xbase++ DBFUPSIZE.EXE but I throw out indexes and FTS
- I gave up UPSIZE simulation of NTX and CDX indexes, I gave up problematic ISAM commands: SET FILTER TO, PACK, DELETE-PACK, and similar. I use passthroughSQL instead.
- PassthroughSQL is a powerful thing. I'm more than happy with that revelation. All ISAM commands and functions can be very easily switched to passtroughSQL and work much faster and more reliably, and the best thing is that they have much more capabilities than the same ISAM commands.
- I am getting ready to replace all basic ISAM commands and functions, even those that work well for now, with passtroughSQL.

Conclusion:

- All the business logic in my application that is directly related to communication with the database can be very quickly and easily transferred via PGDBE.DLL to the postgreSQL database.
- other business logic (calculations, calculations, statistics, reports, PDF, XML, JSON, other...) should not be touched. It works fine without any corrections.
- I work at eXpress++ and I have to give a big credit to Roger again. DCBROWSE, DCSTATIC, DCGET, DCSAY, DC..., DC... EVERYTHING WORKS ALSO WITH PostgreSQL PGDBE without problems. I hope that it will work like that when I am 100 years old and Roger is 105!