Best web/database tools?
|
| Author | Content |
|---|---|
| NoDough Apr 27, 2007 9:01 AM EDT |
Hello All, I'm about to embark on a project which... 1) Pulls data from one of our vendors across the Internet 2) Imports that data into a PostgreSQL table (which links to other tables.) 3) Makes data queries available through a browser. I am requesting your opinions concerning item #3. There will be a simple web interface which will build a query for the table. The resulting data set will be displayed in the browser in a format suitable for printing. The only locked in piece I have is PostgreSQL. The tools and the server are open for discussion. What, in your opinion are the best tools for building this webdatabase interface? Thanks in advance, NoDough |
| Sander_Marechal Apr 27, 2007 9:24 AM EDT |
Regarding (1), no hatter how you do it (database connection, replication, XML-RPC, SOAP, whatever), just make sure it's secured tightly. As for (3) it depends. Where do these queries come from? Who's it targeted at? If the users make the queries and they are a failrly technical lot then you could use a standard PostrgreSQL frontend. It it's always the same queries, you could consider a standard reporting or BI package. Don't need it interactive? Have a look at ReportLab for Python. There are sooo many possibilities. The best advice I can probably give is to stay away from programs that generate PHP/APS/Java frontends for a database. They're often buggy and not "good enough" for a UI. Which will tempt you into changing the generated code (and straight into the 9th level of hell). Never *ever* edit generated code. |
| NoDough Apr 27, 2007 9:40 AM EDT |
Quoting:Regarding (1), no hatter how you do it (database connection, replication, XML-RPC, SOAP, whatever), just make sure it's secured tightly. NP. Quoting:As for (3) it depends. Where do these queries come from? Who's it targeted at? It's an intranet application. Because we make heavy use of VPNs for remote access, it will probably never face the public Internet. Quoting:If the users make the queries and they are a failrly technical lot then you could use a standard PostrgreSQL frontend. No, they're very much NOT a technical lot. The query will be built for them. The form will allow them to specify simple values, like transaction date ranges. Quoting:It it's always the same queries, you could consider a standard reporting or BI package. Don't need it interactive? Have a look at ReportLab for Python. There are sooo many possibilities. Yes, there are. Hence my request for your experiences. Quoting:The best advice I can probably give is to stay away from programs that generate PHP/APS/Java frontends for a database. They're often buggy and not "good enough" for a UI. Which will tempt you into changing the generated code (and straight into the 9th level of hell). Never *ever* edit generated code. No thanks, I'll just stay here at level 8. 8^o |
| DarrenR114 Apr 27, 2007 10:10 AM EDT |
That whole thing can be as easy as a single text entry box on a Perl-powered form (or even JSP with tomcat) to a set of dropdown boxes to limit how the queries are built. You'll have to decide based on who the endusers are and how SQL-savy they are. For instance, with a single text entry box with any kind of normal PHP, PERL, or JDBC connection, it would be possible to not only execute DML but DDL as well. And I have a feeling it would be a real PITA to find that your users are creating and dropping tables at their leisure ... Also, don't forget SSL considerations - do you want query results going out in the open under HTTP or is it worth considering HTTPS? |
| NoDough Apr 27, 2007 10:22 AM EDT |
These users are on the low end of the technical savvy scale. For this data, SSL is not worth the effort. However, I suspect that a successful project here will lead to several more where security is indeed an issue. My first thought was an Apache/PERL setup, as I have a limited amount of experience with each independently, but not using them together. |
| jdixon Apr 27, 2007 10:33 AM EDT |
> It it's always the same queries, you could consider a standard reporting or BI package. A year or so ago, one of the folks I support was being threatened with losing his access to Business Objects. Looking around in desperation for something he could use instead, I found Agata: http://www.agata.org.br/ He never did find time to look at it in detail, as they decided to let him keep his access to Business Objects. It looks interesting, but I have no personal use for it, so I've never tested it out. It's available for both Linux and Windows. It's probably overkill for this type of project. |
| Abe Apr 27, 2007 10:38 AM EDT |
Quoting:I am requesting your opinions concerning item #3 I suggest using AJAX with PHP. Most , scallable, pleasant and user friendly, easy to code and maintain, many many examples on the Internet.. Create forms for caned queries and for advanced ad hock queries. Take the form data and create the query for the user. Don't expect users to know any thing about SQL when there is no need for them to and since it is so easy to take form data and covert it to SQL query. Create html/css for screen output and tab/comma separated columns for field data to import into spreadsheet. |
| number6x Apr 27, 2007 10:40 AM EDT |
Ruby on rails is a pleasure to work with.
http://www.rubyonrails.org/ Ajax and web 2.0 stuff is all pretty easy. I have enjoyed the chances I've gotten to work in ruby and in rails. Here is some rails stuff in action: http://www.basecamphq.com/ ------------------------------------------------------------------------------------- I have not worked with Django, but have heard good things about it http://www.djangoproject.com/ Django is python based. Here is an example of a Django project: http://www.chicagocrime.org/ I have not used Django, but python is sweet. -------------------------------------------------------------------------------------- Seaside is also getting rave reviews http://www.seaside.st/ I've never written in Smalltalk, so I have not tried this. None of these, along with more traditional frame works like Perl Mason, Zope, or J2EE is point and click. You will have to do real programming. |
| NoDough Apr 27, 2007 11:30 AM EDT |
jdixon: You're correct, Agata looks like overkill. number6x and Abe: Ajax/PHP, Ruby/Rails, DJango/Python, and Seaside/Smalltalk are all possibilities and I am investingating. I prefer a solution with minimal client-side requirements. For example, native IE6 does not support XmlHttpRequest, so I would avoid technologies using that object. Any thoughts about which technology supports the lowest common denominator in browsers? |
| number6x Apr 27, 2007 11:57 AM EDT |
XHR is supported as an active X object in IE's 6 and before. It is supported natively in IE7.
From the web app point of view, there should be no difference. Rails has objects for all of the ajax-ey things you want to use, so implementation is pretty easy. You do not have to use any ajax with Ruby on Rails. I'm pretty sure that holds true for almost any framework, so lack of XmlHttpRequest is not a problem. Just don't use it or anything based on Ajax. I've never had problems with IE6 and XHR. XHR is a Microsoft creation after all. The only problems I've run into with IE is the 'quirks mode' problems with CSS rendering. http://www.quirksmode.org/css/quirksmode.html A good experienced CSS person will save you headaches for getting the 'lowest common denominator'. Its the stylesheets that will setup up how things look in the client's browser, so that's where the magic has to happen. How are you going to host the app? If you already have a shared host contract you may be able to use a system supported by your hosting company. Their support may make the decision for you. Why spend more money than you have to? PHP is common thanks to demand for LAMP. Ruby is suprisingly common too. If you are hosing it on your own servers, what OS will you use and what web server? I don't believe in swimming upstream. Rails forum support for MySQL is the most common, but a few of the Rails heavy hitters are Postgress users so you will still find plenty of help. I've used both as well as SQLlite for rails. |
| NoDough Apr 27, 2007 12:14 PM EDT |
Quoting:How are you going to host the app?This is an Intranet app. It will not face the public Internet. It will, however, be accessed from branch offices and mobile users through VPNs, so I need to be bandwidth sensitive. Quoting:If you are hosing it on your own servers, what OS will you use and what web server?The PostgreSQL server already exists and is running Windows Server 2003. I have my choice of using that same 2003 server and IIS for hosting the web interface (uh, I don't think so) or setting up a Linux box and the web server of my choice (yup, that's better.) (edited for format) |
| number6x Apr 27, 2007 12:54 PM EDT |
Ajax can save bandwidth, or kill it. If you use it to just update a line of a small div on your page, you can cut down on bandwidth. If you try to get fancy and add text completion and auto spell checking to text fields, you will have to set up XHRequests that check the fields every few seconds. These can kill bandwidth. ---------------------------------------------------- Since its is on your own servers, look for a web server that your support people already know how to use. PHP on Windows has pretty good support, but Apache is preferred to IIS. IIS and Ruby on Rails is getting better, but I wouldn't choose it. Check how some of the other frameworks play with IIS. Apache is a good choice, even in an MS shop. Most people have experience with it. Apache on Windows 2003 may be a compromise if the support people kill your Linux choice. If you do get a Linux box, call it 'Pella' or 'Anderson', or name it after some other Window manufacturer. The windows boxes may be fooled and not get scared. :) ---------------------------------------------------- Are the postgreSQL tables already built? If they are, Ruby on Rails will have a tougher time. A lot of Rail's ease of use is based on the unique id column in each table, called id. The framework expects an attribute called 'id' in each table that is the unique primary index. If the tables already exist, you will have to tell your models what the name of the unique primary index for each table is. Rails has support for using legacy tables, but you will have more configuration to do. The auto generated scaffolding code may have to be tweaked a little from time to time. This would detract from the simplicity and ease of set up. If you get to set up the tables, Rails Migrations will set the id column for you. The class/table naming conventions in Rails are pretty straight forward too. I wish I could tell you more about the other frameworks, but I have not used them. I have J2EE experience, but that would be overkill for what you want. The same is probably true for .NET. |
| NoDough Apr 27, 2007 1:05 PM EDT |
Quoting:If you do get a Linux box, call it 'Pella' or 'Anderson', or name it after some other Window manufacturer. The windows boxes may be fooled and not get scared.I was thinking of calling it 'Brick', or even better, 'Ernest T. Bass'. :D Quoting:Are the postgreSQL tables already built?All but one. I'm creating a table for the new vendor info and linking it to the existing tables. Honestly, after some evaluation of the options, I'm leaning toward Django/Python. However, there's still much research to do. |
| number6x Apr 27, 2007 1:27 PM EDT |
Django should be a good choice. The python groups are pretty active, so you should find lots of good help. Ernest T. Bass, that's good! Is your firewall named Barney? Good Luck! |
| Sander_Marechal Apr 27, 2007 2:19 PM EDT |
Quoting:I suggest using AJAX with PHP. Oh please, enough with the AJAX hype already. If you have a new shiny hammer, everything looks like a nail. It's a one-trick pony that can be useful in some places on some rather complicated web applications. It's a hack around a platform limitation. And to boot, 90% of the "AJAX" you see out there isn't AJAX at all, but just AJ. They comveiniently forget the AX (And XML) part. There's no reason a plain database front end would need any AJAX at all. Especially if it has predefined queries and just a few variables to fill. |
| dinotrac Apr 27, 2007 5:07 PM EDT |
sander - I have to agree. AJAX can do some cool stuff for those times when you need to do cool stuff. I can think of one company that tortures javascript to pre-load a lot of info that would benefit (some) from AJAX, but... for most straight-ahead business apps, it's primary virtue is that it keeps your curious geeks happy. |
| Abe Apr 27, 2007 5:11 PM EDT |
Quoting:Oh please, enough with the AJAX hype already Sanders, Ajax is not all hype. I ought to know, because I recently developed a web application using AJAX after work hours in my spare time at home. It uses XAMPP, which consists of Apache, MySQL, PHP and Perl. If it was not for AJAX, I wouldn't have been able to do what is needed without it. The company I work at hired a consulting company to develop a VB.Net (Not Web based) application to store engineering drawings with a work flow capability. To make this part short, the cost was about $250,000. and wasn't really that successful. It wasn't because, although it works, the users didn't find it of any help because it is too convoluted. Any ways, this is what prompt me to do something about and show others at work want can be done with Web Services and all Open Source without needing any of MS crap. I also made a presentation which convinced them of utilizing AJAX for many apps that are already developed using standard desktop tools (VB.Net) and covert Web apps to use AJAX interface. Using a browser, users can do everything the need to do and if not, it is so scalable it is just unimaginable. It has couple views only that do all what I will be describing. I chose XAMPP, which takes 5 minutes to installs and keeps everything contained in one directory tree. When done, you will have an Apache server with all modules you can thing of that are available with Apache, you will have MySQL database completely installed with MyPhPAdministrator, and an ftp server. The application allows a user to: Enter a drawing information (tables & fields) via forms, upload a file, store it as a blob in database. The forms are done via AJAX interface where only changed data are refreshed. This is a tremendous feature of AJAX which makes a web based application to behave exactly like a desktop application but a lot faster because it is a true client-server interface. It allows dwg assignment, dwg is checked out to multiple users at the same time and downloaded to user desktop local disk for revision. This is also using AJAX request to a library of js & PHP functions. It allows dwg check in to the database after revision. During this process, it makes sure to keep track of the work flow where revisions by different users are not lost. It is a little lengthy to describe the whole thing but again, AJAX simplified this complex process. It does standard caned search and displays the results in really neat format. It also has advanced search that takes the user input in fields and creates sophisticated search query that consists of equal, gt, lt, max, min, like, etc what ever you can think of without the user having to think of any SQL at all. If it wasn't for AJAX, this interactivity wouldn't have been possible. Again, when the search finds a number of record that is greater than a threshold, it creates pages and you can move forward and backward displaying the pages without refreshing the whole page. It only refreshes the region where the entries are displayed. Again, without AJAX, this wouldn't have been possible. Users can create may groups each of many dwg numbers and saved in a table in the database for any user to use. There is a menu to manage these groups, create them, delete them or part of them, recall any of them via a drop down list, print the list or create a spreadsheet, etc... Again, the ease and user friendly and the dynamic way it is done wouldn't have been possible without AJAX. I can go on and on listing the features and capabilities for a long time but I think this is enough for now. The bottom line is, YES there is a lot of hype about AJAX, but what I found out after developing this application is most of it is true. There is a lot of mis-use of the AJAX capabilities like number6x said, but there is many capabilities that do a lot for the developer. It makes it very easy to develop an application and make it look like and have the same features and interactivity like a desktop application. When you hear some one saying it might some day replace all standard application, believe it because I just did that and it was much easier, simpler and faster to develop than a desktop application using the great tools of MS Visual Studio. The Main reason I chose MySQL & PHP is because there is a lot of code on the Internet that is available to every to learn from, or use without any restrictions. I had limited time, and Google was my friend to find a lot of code examples to learn from or use. May be some day you will hear about it or see it because I will be creating a screen capture of actual demo of this application and publish it. I think at that time you will be truly convinced of AJAX as a great Web tool which could replace desktop based applications. Sorry guys, this is going to be available for a price to recuperate my quality time I lost while developing it. |
| Sander_Marechal Apr 27, 2007 6:07 PM EDT |
Sorry Abe, but I still see things in your description that would work just as well without AJAX. There's nothing wrong with refershing a page, you know. Especially not of a fast intranet. Nor is there anything wrong with sending extra data to the client at pageload and simply swap stuff out on the client side. It surprises me how many people worry about the 1.5Kb overhead of a header, menu and footer in these days of multi-megabit DSL and gigabit LANs. AJAX does have it's uses. I've used it a whole two times. One time was on a page that had many tabs in it, each tab showing a different search result set, and each resultset could have multiple pages. Swapping tabs was done on the client (just hiding divs). The AJAX bit came in when a user wanted to page through the results in one tab. It would refresh that one tab and leave the rest alone. In this case it made sense because refreshing the entire page would have caused all (expensive) search queries to have been executed again, instead of just the query for the one tab. The second time was on a combination of two select dropdowns. The second one would have it's content swapped out when the first one changed. There was almost a megabyte of data in option combinations for the second dropdown so here AJAX made sense. I fail to see why an upload form would need AJAX. You can simply submit the entire form and redisplay it again, filling in the same data that the user had before they hit "Upload". Even if you're talking about a very big form, it's probably < 1Kb in data. That's peanuts. |
| dinotrac Apr 28, 2007 3:07 AM EDT |
>I fail to see why an upload form would need AJAX. You can simply submit the entire form and redisplay it again, filling in the same data that the user had before they hit "Upload". I've seen a case at a former client where an upload form could benefit from AJAX. I'm thinking of an order form (a pretty complex one, as it involved real estate services) for a variety of products that a) would dynamically display fields depending on product chosen, b) would dynamically display products (and optional fields) depending on customer code -- with multiple customer codes per user, and c) might display different choices in dropdown boxes (ie, local branch locations) depending on the state chosen. Buncho buncho stuff to load up at start time, and access speed couldn't be guaranteed as users might be at their office or out on the road. |
| Abe Apr 28, 2007 9:44 AM EDT |
Quoting:but I still see things in your description that would work just as well without AJAX. I agree but, there are many things that can't be done in a browser without AJAX. Besides the benefits AJAX has in terms of lowering the traffic by 70% and improving the performance of web application, it has major benefits in terms of user interactivity with web application. As a matter of fact, and according to my experience, users in general love it because it makes a web application a lot more elegant and pleasant experience. Although refreshing is not such a big annoyance, but the less of it the better for the user. Too much refreshing is confusing; AJAX eliminates a lot of that. When you have data that depends on other data that the user already entered or selected, users don't have to constantly keep track of what is changing. Dynamically updating instead of refreshing the whole page is a major improvement of web applications and will become a must have requirement by users. We hardly scratch the surface of AJAX. With many of the tools and libraries that are being developed, like DOJO and RICO, etc... it is becoming a development framework. Thanks to Google for making it well know and famous. If MS didn't thing it is going to lessen the importance of its desktop programming tools, it wouldn't have rush to release its Atlas tool so quickly. On the Internet, many of the outfits are rushing to convert and implement AJAX in their their applications and sites. Visit some of the Internet sites and see how it is different that standard DHTML. Yes, you can a do many of the same thinks using the standard HTML & Javascript, but AJAX makes many of those things much faster easier to develop and maintain. |
You cannot post until you login.