good chemistry is complicated, and a little bit messy -LW |
|
PerlMonks |
comment on |
( [id://3333]=superdoc: print w/replies, xml ) | Need Help?? |
As pme asked, what SQL DB are you using? Do you have a choice? If you have a choice, I would recommend SQLite. This is my "go to" DB and it doesn't require any installation or admin of a DB Server - the "care and feeding" of a DB server can get involved. With SQLite, there are no "accounts" or "passwords", if you have permissions to access to the DB file, you can use it. One of your hassles just goes away.
I am working on an SQL application right now. While I am experimenting, my version of first.pl deletes the existing DB if it exists and starts completely anew. An alternative is the SQL: $dbh->do("DROP TABLE IF EXISTS main"); which I use in some other processing phases. This code is to show how easy it is to "get started" and create a brand new DB.
Of course if you need to know, help can be provided about the CREATE TABLE statements and how to do the inserts. My code is not relevant to your application. My DB is a "small DB", only about I think a temp table would only persist while your DB connection that created it is open. I see no need to mess with that. Just make a regular table which will persist across any disconnect/reconnect. If you want to "clean up after yourself", then just DROP the table. So, How can I declare the database connection parameters just once? With SQLite, you just need the DB name. For my app, I just put the name "MASTER.sqlite" as a constant in all of the programs (and yes the actual name is bit more descriptive). I could make a config file with that data in it or even account/password info if I was using a different DB. I don't see why you would need to split the code up into 4 programs? I mean things like "third.pl will join the two temp tables from first and second and load it into third". That is one SQL statement! CREATE TABLE AS.... A separate program is a lot of overhead of one SQL statement. But the normal way to do this would be to have a shell batch file that runs each of your programs and then exits if X.pl returns a non-zero error code. Just like any other program, Perl will return 0 if program exits successfully, error code if an abend. You can force error exit with exit(476); or whatever. Update:
I mentioned that your "create 3rd table from 2 other tables" is a single SQL statement.
This is so fast that I haven't bothered to measure it. Update2: I can see situations where processing should be broken up into multiple phases. But I don't see the need that for the OP's question. In my current app, I am trying to figure out given your complete USPS address: street address (not a PO box), zip code and state, what state do you live in? It turns out that is a very complicated question to answer! Much to my surprise! With the DB's I have, I will be able to come close to a predicted state of residence, but there will be uncertainty in that prediction. Right now I have different "phases" of DB creation while I am developing so that I can see intermediate analysis of results while I am fine tuning the heuristics (rules of thumb). I don't think the OP is trying to answer any kind of question like "what state do you live in?". In reply to Re: designing a program - your wisdom needed
by Marshall
|
|