One table to rule them all
-
Sounds like not a bad way of doing it, assuming that FOXPro doesn't have some other method of generating NextId numbers Behind the scenes SQL Server must do something like that in order to 'know' which number to use next for an identity column. Or does FoxPro have that ability built-in?
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
As far as I remember, Foxpro has Auto identity columns since version 7 (2006). Maybe that's a veeeeryyyy old version
-
I am currently working on a project to convert old FoxPro system to .NET. I am just baffled by the downright stupidity of the way tables are created in the fox pro database. There is a table called control which holds different record ids for other tables. Whenever there is a record got inserted in any of the tables the program first reads the control table’s one of the column, add 1 to it and save it in control table and then use that id as a record id for the record to be inserted. There is not even one table in the whole database that has a primary key. There is no primary key/foreign key relationship between control table and other tables in the database (only relationship between them is last four characters of each fields kind of suggest which table’s id it is). It makes me raged to see that control table everywhere in the program just to generate that next record id. :mad::mad::mad:
Zen and the art of software maintenance : rm -rf * Math is like love : a simple idea but it can get complicated.
virang_21 wrote:
I am just baffled by the downright stupidity of the way tables are created in the fox pro database.
Stupid does not begin to explain a FoxPro database application I was called in to consult on for a small town's utility bills. The town was so small, I drove past the city hall, not recognizing the trailer home on a lot was it. A local resident had donated a FoxPro application he had written to do their water utility billing. The program worked fine the first year, but the second year was producing impossible results and the original programmer was having a hissy fit of some sort with the mayor and refused to look at it. I had never looked at FoxPro before that day. I quickly found the resident had programmed the application like it was a spreadsheet, rather than a database. He had columns for the water readings for each month of the year. Instead of storing a date and a reading, and then fetching records based on date, he had 12 different queries that you ran, depending on the month, pulling the columns for that month.. I traced the problem (besides being the idiotic structure) to the fact that the original programmer had not considered how to handle more than one year's worth of data. I didn't have time to rewrite the application, so I applied a band-aid by clearing the month columns before they did entry on the month. The problem had been the previous year's information contaminating the data. I offered to rewrite it into something sensible, but they couldn't afford me beyond my quick fix. The best part of the trip was seeing the town safe. I had given the backups to the mayor and as I was gathering my stuff together, I saw out the corner of my eye, the mayor operating a crank. I looked to find that the safe did not have a door with pins holding it shut, but instead was a lid you literally screwed in using the crank and the teeth on the outside of the circular door. The crank had the combination lock on it. Besides the bad design of the application, I did not come away with a favorable opinion of FoxPro, but then, I was used to working in MS Access and Oracle.
Psychosis at 10 Film at 11 Those who do not remember the past, are doomed to repeat it. Those who do not remember the past, cannot build upon it.
-
I am currently working on a project to convert old FoxPro system to .NET. I am just baffled by the downright stupidity of the way tables are created in the fox pro database. There is a table called control which holds different record ids for other tables. Whenever there is a record got inserted in any of the tables the program first reads the control table’s one of the column, add 1 to it and save it in control table and then use that id as a record id for the record to be inserted. There is not even one table in the whole database that has a primary key. There is no primary key/foreign key relationship between control table and other tables in the database (only relationship between them is last four characters of each fields kind of suggest which table’s id it is). It makes me raged to see that control table everywhere in the program just to generate that next record id. :mad::mad::mad:
Zen and the art of software maintenance : rm -rf * Math is like love : a simple idea but it can get complicated.
That is just one way that programmers designed FoxPro databases. Another reply stated that SQL Server must have a similar method that is just hidden. Keep in mind that I found Visual FoxPro (VFP) to be a very productive environment to work in for a medium sized client server app. And, from what I understand, the search algorithm (modified b-Tree) used in VFP was adapted later by SQL Server. I find it very annoying when programmers evaluate a programming language without looking at the context it was designed to be used in. Or without even trying to understand how the language was designed, so the programmer can use it effectively. It definitely seems like a form of language discrimination - operating from fixed ideas of the one programming language the person knows instead of taking time to actually figure out the most effective way to use the new one.
Jack
-
I am currently working on a project to convert old FoxPro system to .NET. I am just baffled by the downright stupidity of the way tables are created in the fox pro database. There is a table called control which holds different record ids for other tables. Whenever there is a record got inserted in any of the tables the program first reads the control table’s one of the column, add 1 to it and save it in control table and then use that id as a record id for the record to be inserted. There is not even one table in the whole database that has a primary key. There is no primary key/foreign key relationship between control table and other tables in the database (only relationship between them is last four characters of each fields kind of suggest which table’s id it is). It makes me raged to see that control table everywhere in the program just to generate that next record id. :mad::mad::mad:
Zen and the art of software maintenance : rm -rf * Math is like love : a simple idea but it can get complicated.
I assume this was a workaround to some limitation of the technology, not downright stupidity of part of the developer.
CEO at: - Rafaga Systems - Para Facturas - Modern Components for the moment...
-
virang_21 wrote:
I am just baffled by the downright stupidity of the way tables are created in the fox pro database.
Stupid does not begin to explain a FoxPro database application I was called in to consult on for a small town's utility bills. The town was so small, I drove past the city hall, not recognizing the trailer home on a lot was it. A local resident had donated a FoxPro application he had written to do their water utility billing. The program worked fine the first year, but the second year was producing impossible results and the original programmer was having a hissy fit of some sort with the mayor and refused to look at it. I had never looked at FoxPro before that day. I quickly found the resident had programmed the application like it was a spreadsheet, rather than a database. He had columns for the water readings for each month of the year. Instead of storing a date and a reading, and then fetching records based on date, he had 12 different queries that you ran, depending on the month, pulling the columns for that month.. I traced the problem (besides being the idiotic structure) to the fact that the original programmer had not considered how to handle more than one year's worth of data. I didn't have time to rewrite the application, so I applied a band-aid by clearing the month columns before they did entry on the month. The problem had been the previous year's information contaminating the data. I offered to rewrite it into something sensible, but they couldn't afford me beyond my quick fix. The best part of the trip was seeing the town safe. I had given the backups to the mayor and as I was gathering my stuff together, I saw out the corner of my eye, the mayor operating a crank. I looked to find that the safe did not have a door with pins holding it shut, but instead was a lid you literally screwed in using the crank and the teeth on the outside of the circular door. The crank had the combination lock on it. Besides the bad design of the application, I did not come away with a favorable opinion of FoxPro, but then, I was used to working in MS Access and Oracle.
Psychosis at 10 Film at 11 Those who do not remember the past, are doomed to repeat it. Those who do not remember the past, cannot build upon it.
Again, you are deciding that a programming environment bad based on the design of one programmer!!
Jack
-
Again, you are deciding that a programming environment bad based on the design of one programmer!!
Jack
Jack Allread wrote:
Again, you are deciding that a programming environment bad based on the design of one programmer!!
Not at all. Irregardless of the bonehead programming, I found the FoxPro environment clunky to work in. Editing and navigation was more difficult than it needed to be. As stated, it was my first time working in FoxPro and the environment did not make it easy. In comparison, I was able to get into Access programming with much less effort. If I had been working in Access, the design would still have been boneheaded, but I think I would have been able to rewrite the application that same day.
Psychosis at 10 Film at 11 Those who do not remember the past, are doomed to repeat it. Those who do not remember the past, cannot build upon it.
-
I just always use a a natural key as well as a surrogate for the primary key. The natural is normally for doing business with, and the surrogate is normally for getting out of the poo. And doing joins.
I love to use natural keys but be careful what you use and how you use it. (First relational database I worked with, 15+ years ago, used a phone number as a natural key... worked well until someone's phone number changed, breaking all the relationships. :doh: Not my design, just my job to fix.) Creating and using artificial indexing system (assigning a unique number to a record) allows for any data to change without breaking anything. Furthermore, using a table to keep track of what the next value for an index should be makes a lot of sense when you think about the options for generating it. a) loop through the entire table and find the highest index value and add one. (This is not a problem if the table(s) are small but performance will suffer greatly with larger tables) b) keep track of what the next value should be in an easy to get to place and update it when you add a record. Even table insert functions in languages like Lua and javascript are slow because they use method a) above to do the insert. If you are using large tables you may consider method b). OK. I'm done.
somnio lunaticas
-
Jack Allread wrote:
Again, you are deciding that a programming environment bad based on the design of one programmer!!
Not at all. Irregardless of the bonehead programming, I found the FoxPro environment clunky to work in. Editing and navigation was more difficult than it needed to be. As stated, it was my first time working in FoxPro and the environment did not make it easy. In comparison, I was able to get into Access programming with much less effort. If I had been working in Access, the design would still have been boneheaded, but I think I would have been able to rewrite the application that same day.
Psychosis at 10 Film at 11 Those who do not remember the past, are doomed to repeat it. Those who do not remember the past, cannot build upon it.
I will say to start an application is VFP is a pain. When I was working in it we had a framework written by several of us that made it much easier. And if the application wasn't setup correctly to begin with it would cause real problems. One of the main difficulties I saw was the dev environment was not separated from the runtime when it came to the database connections. So you could easily open a database or able during dev and your exe would expect that database or table to be in the same path when it ran. That was a major pain in VFP.
Jack Allread
-
A Hammer is a perfect tool to drive in a Nail. If someone gets a thumb crushed while using a Hammer, you don't blame the "Hammer". It is the fault of the Bad WorksManship. Foxpro is a very Good Programming Language in the hands of a "Programmer" MS purchased "Foxpro" to learn its secrets. Many of SQL Servers internal features are taken from Foxpro. Before that SQL server was a Flop Show.
Doesn't SQL Server orginate from Watcom SQL (through Sybase SQL Anyware) ... Eg, MS bought all DB stuff. Anyone who knows where Access orginated? Or is/was it a true MS product all through ...
-
Doesn't SQL Server orginate from Watcom SQL (through Sybase SQL Anyware) ... Eg, MS bought all DB stuff. Anyone who knows where Access orginated? Or is/was it a true MS product all through ...
I'm an old Foxpro programmer and I always created keys based on a function to create a unique filename within Foxpro that uses date-time sequence to create a 10 digit word that you index on and create a function to create a new key based upon a small loop and test existence before creating it. You can also breakback the key to get the date time it was created. The key remains in the file with the data but can be used in tables below to quickly and acurately get to the record or a collection of records. I've always found that users can come up with the dardest things to screw up your keys when using user entered data as keys. MS bought Foxpro for it's optimizing engine for SQL querying called Rushmore even though they didn't want it. In fact, it became so popular it was cutting into their bottom line that they decided to kill it off before it went too far. Clipper was bought out by CA and killed by their ineptitude. Dbase, on it's last breath, by Borland who also was on its last breath. MS licensed Sybase SQL server for running on OS2 and quite literally stole its guts to build MS Server which ran on NT. I always felt as though Sybase really got ripped off in that one. Access was also created from this collaboration with Sybase and it was supposed to be a stand alone version but has always from it's beginning fell way short. But, that's not by accident because like Foxpro, they didn't want to relingish control and profit letting it get out of hand as Foxpro has done. With some 3rd party addins, Foxpro could do anything. It could do word processing, spreadsheets, email, etc. One company even used it to make a document storage system which was quite good. And, with a SQL backend, you could write thin clients and run a SQL system while at the same time utilizing Tables locally to do some of the heavy lifting. I wrote a settlement system for one of the major railroads that processed over a million records a day in SQL in 1992. It was MS server 2.0 at the time and I copied down 100 records to a local table at a time to do the processing and update to show they were processed. Could run it in tandem on as many PCs as desired although its optimum speed was on 4 pcs at a time. With just SQL or just local tables the chore would be very hard to have done. But, since you have the capabilities of both it's not hard at all. I've done barcoding, IDs System, cases tracking, time and attendance with Kronos clocks, and many more diverse things including reading barcodes and scanning. All wit
-
virang_21 wrote:
I am just baffled by the downright stupidity of the way tables are created in the fox pro database.
Stupid does not begin to explain a FoxPro database application I was called in to consult on for a small town's utility bills. The town was so small, I drove past the city hall, not recognizing the trailer home on a lot was it. A local resident had donated a FoxPro application he had written to do their water utility billing. The program worked fine the first year, but the second year was producing impossible results and the original programmer was having a hissy fit of some sort with the mayor and refused to look at it. I had never looked at FoxPro before that day. I quickly found the resident had programmed the application like it was a spreadsheet, rather than a database. He had columns for the water readings for each month of the year. Instead of storing a date and a reading, and then fetching records based on date, he had 12 different queries that you ran, depending on the month, pulling the columns for that month.. I traced the problem (besides being the idiotic structure) to the fact that the original programmer had not considered how to handle more than one year's worth of data. I didn't have time to rewrite the application, so I applied a band-aid by clearing the month columns before they did entry on the month. The problem had been the previous year's information contaminating the data. I offered to rewrite it into something sensible, but they couldn't afford me beyond my quick fix. The best part of the trip was seeing the town safe. I had given the backups to the mayor and as I was gathering my stuff together, I saw out the corner of my eye, the mayor operating a crank. I looked to find that the safe did not have a door with pins holding it shut, but instead was a lid you literally screwed in using the crank and the teeth on the outside of the circular door. The crank had the combination lock on it. Besides the bad design of the application, I did not come away with a favorable opinion of FoxPro, but then, I was used to working in MS Access and Oracle.
Psychosis at 10 Film at 11 Those who do not remember the past, are doomed to repeat it. Those who do not remember the past, cannot build upon it.
You can't blame the horse if the rider can't ride. There's many products running or almost running built by those that don't understand the basic structures of a data system. Doens't matter what the language is in this case. That one sounds so simple that you could convert it in a matter of minutes. Not sure how many user screens and reports would be required since I can't see it.
-
I'm an old Foxpro programmer and I always created keys based on a function to create a unique filename within Foxpro that uses date-time sequence to create a 10 digit word that you index on and create a function to create a new key based upon a small loop and test existence before creating it. You can also breakback the key to get the date time it was created. The key remains in the file with the data but can be used in tables below to quickly and acurately get to the record or a collection of records. I've always found that users can come up with the dardest things to screw up your keys when using user entered data as keys. MS bought Foxpro for it's optimizing engine for SQL querying called Rushmore even though they didn't want it. In fact, it became so popular it was cutting into their bottom line that they decided to kill it off before it went too far. Clipper was bought out by CA and killed by their ineptitude. Dbase, on it's last breath, by Borland who also was on its last breath. MS licensed Sybase SQL server for running on OS2 and quite literally stole its guts to build MS Server which ran on NT. I always felt as though Sybase really got ripped off in that one. Access was also created from this collaboration with Sybase and it was supposed to be a stand alone version but has always from it's beginning fell way short. But, that's not by accident because like Foxpro, they didn't want to relingish control and profit letting it get out of hand as Foxpro has done. With some 3rd party addins, Foxpro could do anything. It could do word processing, spreadsheets, email, etc. One company even used it to make a document storage system which was quite good. And, with a SQL backend, you could write thin clients and run a SQL system while at the same time utilizing Tables locally to do some of the heavy lifting. I wrote a settlement system for one of the major railroads that processed over a million records a day in SQL in 1992. It was MS server 2.0 at the time and I copied down 100 records to a local table at a time to do the processing and update to show they were processed. Could run it in tandem on as many PCs as desired although its optimum speed was on 4 pcs at a time. With just SQL or just local tables the chore would be very hard to have done. But, since you have the capabilities of both it's not hard at all. I've done barcoding, IDs System, cases tracking, time and attendance with Kronos clocks, and many more diverse things including reading barcodes and scanning. All wit
Thanks