I'm going to attempt a PostgreSQL work for a customer. They would like to create a huge professional database with lots of complex joins, so after consideration I've selected to choose PostgreSQL over MySQL.
An essential consideration is how you can effectively interface towards the database with scripts. Presently, the customer uses in regards to a million scripts to import and reshape data for their needs, but uses no database (unless of course you think about CSV files to become a database). Using the arrival of the database structure with queries and sights, the requirement for scripts is going to be less, but posting will still have to be done frequently, and conveying/confirming too. For me personally the perfect finish result will be a number of standardized scripts, ideally having a web interface, to ensure that the customer are capable of doing regular tasks rapidly and error-free having a click from the button.
My real question is which scripting approach is going to be most suitable. Most likely any scripting language having a Postgres or perhaps an ODBC wordpress plugin would suffice, however i am searching to create a wise option for the long-term. Does anybody have knowledge about this? Does Postgres present an internal scripting language, and it is simple to use to construct a GUI for your? What are the standardized tools readily available for posting/conveying, and therefore are they easy to customize enough to permit standardization of tasks to click-level? What about PHP or perl?
Thanks ahead of time. Any tips, assets, puzzled looks or pitiful gestures is going to be truly appreciated -)
I personally use Python/Jython for connecting to PostgreSQL and do something more important.
- there's pl/python so that you can use Python from PostgreSQL
- you may create "stand alone" programs using Python DB API, there's doc about database programming, and PostgreSQL in particulars
- you should use Jython if you want JVM atmosphere, especially JDBC driver
Good examples of usage:
- transforming PDF, MS Word and OopenOffice documents held in BLOBs to text to index it
- posting data from various sources, not just resumes Python is actually strong at transforming text data
- testing motorists although some our applications are native and employ ODBC or JDBC motorists and Jython can function with both motorists (for ODBC there's JDBC-ODBC bridge)
On my small other posts on So that you can see I personally use Python/Jython along with other databases as Oracle and Informix. For instance I produced tools that dumps some info from database schema in order to easily compare databases in make sure production atmosphere.
As you are speaking about scripts that specifically just manipulate the database, I'd start most abundant in native tools.
- SQL and PL/pgSQL saved functions for adjusting and processing data
COPY TOfor posting from and conveying to flat files
- An ETL tool for just about any re-shaping that can not be handled using the above
Now, you need to provide some easy web interface for interfacing with one of these scripts. Here the very best language is most likely the main one you and your team already knows. All major languages have Postgres motorists. The word what you select will have little impact should you keep the data manipulation tasks in the database layer.
One factor to think about is when lengthy the normal script will require to complete. If it's greater than a couple of minutes, i quickly suggest decoupling it on the internet interface. For the reason that situation, the net interface should permit the user to queue the script to begin to ensure that the server can run it in addition to the web request cycle.