Thread: Visual FoxPro (VFP) 9 Migration Strategy to Postgresql on Linux
We are in the early stages of planning a migration of all our VFP 9 databases to Postgresql. Our VFP dbc contains roughly300 tables and 8GB total data. Screen replacements are likely going to be HTML5/CSS3 based and report replacementswill likely be one of the open source BI reporting portals. While we know VFP very well, and we use a LOT oflinux elsewhere in the company - we know very little about Postgres. We do see a lot of tutorials out there for new Postgresdatabases, but not much in the way of migration from VFP. We're curious to know if anyone else has gone through asimilar migration that may have some advice on how to get started. NOTICE: This email and any files transmitted with it are confidential and intended solely for the use of the individual(s) or entity to whom they are addressed. If you have received this email in error please notify the sender and delete all copies on your system. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of Twin Manufacturing Company, Inc.
Re: Visual FoxPro (VFP) 9 Migration Strategy to Postgresql on Linux
From
Leif Biberg Kristensen
Date:
Fredag 13. april 2012 17.41.41 skrev Kevin Salisbury : > We are in the early stages of planning a migration of all our VFP 9 > databases to Postgresql. Our VFP dbc contains roughly 300 tables and 8GB > total data. Screen replacements are likely going to be HTML5/CSS3 based > and report replacements will likely be one of the open source BI reporting > portals. While we know VFP very well, and we use a LOT of linux elsewhere > in the company - we know very little about Postgres. We do see a lot of > tutorials out there for new Postgres databases, but not much in the way of > migration from VFP. We're curious to know if anyone else has gone through > a similar migration that may have some advice on how to get started. The most important step is to get a thorough grip on PostgreSQL, and understand what it can do for you. With a clear grasp of concepts such as transactions and foreign key constraints, you'll probably find it far easier to implement your business logic with a PostgreSQL database than dirty ol' FoxPro. On the other hand, you're much more on your own wrt the interface. On a far smaller scale than your project, I migrated my genealogy database from a commercial FoxPro program to my own homegrown PostgreSQL/PHP application. You can read about it here: http://solumslekt.org/forays/ > NOTICE: > This email and any files transmitted with it are confidential and > intended solely for the use of the individual(s) or entity to whom > they are addressed. If you have received this email in error please > notify the sender and delete all copies on your system. Please > note that any views or opinions presented in this email are solely > those of the author and do not necessarily represent those of Twin > Manufacturing Company, Inc. That is a rather silly thing to tell an email list. regards, Leif http://code.google.com/p/yggdrasil-genealogy/
Le 14/04/2012 11:25, Leif Biberg Kristensen a écrit : > dirty ol' > FoxPro Foxpro is not dirty AND not dead ! But I don't think that this list is the right place to discuss about that !! -- Jean MAURICE Grenoble - France - Europe www.j-maurice.fr www.atoutfox.org www.aedtf.org
Hi Kevin, here in France some people have done an 'upgrade' from VFP to Postgres. As it seems to work well some others are taking the same way. I am one of these. I, too, is an expert in VFP and SQL; I then thought that going to postgres should have been a simple way but it is not. One of the problem I encountered is : with VFP we are free to do what we want modifying table structures, modify data with BROWSE (!), .... it is a lot more complicated with Postgres. The other one is that I used a large amount of parameterized views, I haven't found the manner to convert them to postgres functions but I am at the beginning of the road. Two things I'have done will, I think, be efficient : I have no rule in my VFP database (no constraint, no referential integrity), all is done in 'access class' : for each table I have an access class to read, update, reindex, pack, verify data in this table. All 'safety work' is done in these classes. I have two databases in my main app (it's a school management system): one is local and contains parameters; I mean datas not being often changed (holidays dates, prices, taxes, ...) and it will stay within VFP (no problem with safety, size) on each client (on each local PC). So the 'main' database on the file server is a lot simpler and I'll only upgrade the dbc. I mean : I try to minimize the upgrade work. English is not my language. I hope you understand what I wrote !! If you speak french, you can have help here : www.atoutfox.org I have also had a lot of help on the Profox mailing list that you can find on www.leafe.com Best regards -- Jean MAURICE Grenoble - France - Europe www.j-maurice.fr www.atoutfox.org www.aedtf.org PS you build turbines ? (The Leaders in Turbine Engine Module and Component Repair!). Here where I live (Grenoble in France), we have a great turbines builder (Alstom) !
Hello Leif, Thank you for your message - we will use your website as a reference. Yes, it was always our plan to learn PostgreSQL asfast as possible - but it never hurts to ask others how they accomplished a similar feat. It's also obvious this is notgoing to be a quick migration, so we'll get started in phases. PS. I agree about the old legal message that was automatically appended to all our mail. Silly indeed! Regards, Kevin >>> From: Leif Biberg Kristensen <leif@solumslekt.org> To: <pgsql-novice@postgresql.org> Date: 4/14/2012 5:26 AM Subject: Re: [NOVICE] Visual FoxPro (VFP) 9 Migration Strategy to Postgresql on Linux Fredag 13. april 2012 17.41.41 skrev Kevin Salisbury : The most important step is to get a thorough grip on PostgreSQL, and understand what it can do for you. With a clear grasp of concepts such as transactions and foreign key constraints, you'll probably find it far easier to implement your business logic with a PostgreSQL database than dirty ol' FoxPro. On the other hand, you're much more on your own wrt the interface. On a far smaller scale than your project, I migrated my genealogy database from a commercial FoxPro program to my own homegrown PostgreSQL/PHP application. You can read about it here: http://solumslekt.org/forays/ That is a rather silly thing to tell an email list. regards, Leif http://code.google.com/p/yggdrasil-genealogy/ -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice
On Fri, Apr 13, 2012 at 10:41 AM, Kevin Salisbury <KSalisbury@twinman.com> wrote: > > We are in the early stages of planning a migration of all our VFP 9 databases to Postgresql. Our VFP dbc contains roughly300 tables and 8GB total data. Screen replacements are likely going to be HTML5/CSS3 based and report replacementswill likely be one of the open source BI reporting portals. While we know VFP very well, and we use a LOT oflinux elsewhere in the company - we know very little about Postgres. We do see a lot of tutorials out there for new Postgresdatabases, but not much in the way of migration from VFP. We're curious to know if anyone else has gone through asimilar migration that may have some advice on how to get started. I started out in foxpro and ended up in postgres. IMNSO, you're making the correct choice. In my case, I didn't go 'full web' right off the bat but went to a delphi-postgres thick client approach which remains, even today, the most efficient method of developing applications ever invented. There's a lot of ways to do web applications and how you do it will probably be a bigger mental switch than the backend database. Postgres can fortunately duplicate most of the things foxpro can do (in my case, it was functional indexes) and once you get used to the SQL paradigm things will feel easy, natural, and elegant. Being foxpro guys you'll probably be writing a lot of stored procedures (plpgsql, while quirky, is great for that) and keeping a lot of logic in the backend which is a good approach. On the web side, I would tend to avoid all technologies that employ scaffolding, or auto-generation, of either the html or the sql. Learning jquery is a fantastic choice for your client side javascript and it will open the door to many fantastic free/commercial rlibraries like jqueryui, jstree, highcharts, google maps, and the up'n'coming google charts. Take a very strong look at the new postgres json support (http://people.planetpostgresql.org/andrew/index.php?/archives/255-JSON-for-PG-9.2-...-and-now-for-9.1!.html) and how it can efficiently tie into json receiving javascript libraries. On the BI side, you can either do an ad hoc java script based solution or employ a reporting framework. If you want to go full framework then your best options are probably in the java side of things. The best of class reporting solutions are probably jasper for classic reports or Pentaho Mondrian for full BI. I prefer full javascript, but it's totally understandable if you want to use a framework especially if you want to make a heavy investment into java. There's also the expensive Smart framework which can either be used directly or via java through the GWT. Speaking of the GWT, while I think it's great for a heavy java team, I think you'll get more mileage out of javascript/html5/css if you're starting from scratch. Anyways, good luck. merlin
Merlin, Thank you for the detailed response! Yes, we have started along the lines you describe; HTML 5/CSS based CMS system - We believe we will need a strong CMS to manage all the HTML/CSS that will be in this project.There appear to be dozens of capable open source PHP CMS systems - concrete 5 is the one we like the best so far.http://www.concrete5.org/ . It's biggest downfall is that it is tied to mysql. However, when you say, "On the web side,I would tend to avoid all technologies that employ scaffolding, or auto-generation, of either the html or the sql."are you saying you would rather avoid using a PHP CMS like concrete 5? Reporting/Business Intelligence - Yes, we like Pentaho as well. We're looking at the entire Pentaho BI http://community.pentaho.com/and perhaps combined with R http://www.prashantraju.com/2010/01/pentaho-and-the-r-project/ .We have not looked closely at Jaspersoft Community offerings http://jasperforge.org/ but if is on the schedule to do so. Like CMS, we like the idea of a open BI framework to save some development time and for project support purposes. pg/vfp Data Synchronization - We plan to schedule a regular data synchronization using a cron job with something like PgDBF.http://pgdbf.sourceforge.net Eventually we could turn this off once we have all reports/screens migrated into manualHTML/CMS. Thanks, Kevin >>> From: Merlin Moncure <mmoncure@gmail.com> To: Kevin Salisbury <KSalisbury@twinman.com> CC: Postgresql Novice <pgsql-novice@postgresql.org> Date: 4/16/2012 10:09 AM Subject: Re: [NOVICE] Visual FoxPro (VFP) 9 Migration Strategy to Postgresql on Linux On Fri, Apr 13, 2012 at 10:41 AM, Kevin Salisbury <KSalisbury@twinman.com> wrote: > > We are in the early stages of planning a migration of all our VFP 9 databases to Postgresql. Our VFP dbc contains roughly300 tables and 8GB total data. Screen replacements are likely going to be HTML5/CSS3 based and report replacementswill likely be one of the open source BI reporting portals. While we know VFP very well, and we use a LOT oflinux elsewhere in the company - we know very little about Postgres. We do see a lot of tutorials out there for new Postgresdatabases, but not much in the way of migration from VFP. We're curious to know if anyone else has gone through asimilar migration that may have some advice on how to get started. I started out in foxpro and ended up in postgres. IMNSO, you're making the correct choice. In my case, I didn't go 'full web' right off the bat but went to a delphi-postgres thick client approach which remains, even today, the most efficient method of developing applications ever invented. There's a lot of ways to do web applications and how you do it will probably be a bigger mental switch than the backend database. Postgres can fortunately duplicate most of the things foxpro can do (in my case, it was functional indexes) and once you get used to the SQL paradigm things will feel easy, natural, and elegant. Being foxpro guys you'll probably be writing a lot of stored procedures (plpgsql, while quirky, is great for that) and keeping a lot of logic in the backend which is a good approach. On the web side, I would tend to avoid all technologies that employ scaffolding, or auto-generation, of either the html or the sql. Learning jquery is a fantastic choice for your client side javascript and it will open the door to many fantastic free/commercial rlibraries like jqueryui, jstree, highcharts, google maps, and the up'n'coming google charts. Take a very strong look at the new postgres json support (http://people.planetpostgresql.org/andrew/index.php?/archives/255-JSON-for-PG-9.2-...-and-now-for-9.1!.html) and how it can efficiently tie into json receiving javascript libraries. On the BI side, you can either do an ad hoc java script based solution or employ a reporting framework. If you want to go full framework then your best options are probably in the java side of things. The best of class reporting solutions are probably jasper for classic reports or Pentaho Mondrian for full BI. I prefer full javascript, but it's totally understandable if you want to use a framework especially if you want to make a heavy investment into java. There's also the expensive Smart framework which can either be used directly or via java through the GWT. Speaking of the GWT, while I think it's great for a heavy java team, I think you'll get more mileage out of javascript/html5/css if you're starting from scratch. Anyways, good luck. merlin -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice
On Mon, Apr 16, 2012 at 12:33 PM, Kevin Salisbury <KSalisbury@twinman.com> wrote: > > Merlin, > > Thank you for the detailed response! Yes, we have started along the lines you describe; > > HTML 5/CSS based CMS system - We believe we will need a strong CMS to manage all the HTML/CSS that will be in this project.There appear to be dozens of capable open source PHP CMS systems - concrete 5 is the one we like the best so far.http://www.concrete5.org/ . It's biggest downfall is that it is tied to mysql. However, when you say, "On the web side,I would tend to avoid all technologies that employ scaffolding, or auto-generation, of either the html or the sql."are you saying you would rather avoid using a PHP CMS like concrete 5? yeah, basically. I prefer to make the mindspace investments on the client side (that is, the browser). There are so many good javascript libraries out there that you can utilize and html frameworks tend to be (and understand there are HUGE exceptions to this generalization) a way to avoid taking the full html5/css plunge. They're just too high level for me. Of course this depends a lot on the type of site you're trying to make -- if it's more of a classic publishing site then a CMS makes a lot of sense. But if you're making a highly interactive application type site then you should be looking here: http://code.google.com/apis/ajax/playground/?type=visualization here: http://jqueryui.com/ and here: http://jquery.com/ . > Reporting/Business Intelligence - Yes, we like Pentaho as well. We're looking at the entire Pentaho BI http://community.pentaho.com/and perhaps combined with R http://www.prashantraju.com/2010/01/pentaho-and-the-r-project/ .We have not looked closely at Jaspersoft Community offerings http://jasperforge.org/ but if is on the schedule to do so. Like CMS, we like the idea of a open BI framework to save some development time and for project support purposes. Yeah Pentaho is good stuff but I tend to have very mixed feelings about Java generally in terms of an application development stack. It's a systems programming language at heart and poorly suited to general application development. That hasn't stopped gazillions of developers from going that route and it's very easy to jump into the java world with both feet, but be very skeptical about the effort/progress ratio: sooner or later after working through the dizzying maze of xml configs you'll notice that despite tremendous efforts nothing is really getting done. Especially I advise avoiding the ORM frameworks like hibernate that 'help' you deal with databases. > pg/vfp Data Synchronization - We plan to schedule a regular data synchronization using a cron job with something like PgDBF.http://pgdbf.sourceforge.net Eventually we could turn this off once we have all reports/screens migrated into manualHTML/CMS. yeah -- there are a number of relatively easy ways to transfer data. If you have a copy of sql server laying around you can always try and leverage DTS. I'm not familiar with pgDBF so maybe you can get good results there. barring that. I'd probably be looking at a textual conversion, dumping the results from foxpro in some reasonable approximation of SQL and loading them through postgres. Typically though for database to database transfer I always and up doing the tried and true method, manually transferring the schema with some judicious editing and text format (csv) for data transfer. merlin