RE: YIKES! I must let internet users write SQL queries for our database!
That's alight Jim, I will be suing you for copyright infringement on my ideas though ;-) -Original Message- From: Jim Rising [mailto:cfflex...@jimrising.com] Sent: 24 April 2009 00:59 To: cf-talk Subject: Re: YIKES! I must let internet users write SQL queries for our database! sorry guys... I guess I'm a bit late in the conversation, and my thread only showed the initial post. :) -- Jim Rising Serial Entrepreneur Software Engineer Web Developer Knowledge work requires both autonomy and accountability. On Thu, Apr 23, 2009 at 6:57 PM, Jim Rising cfflex...@jimrising.com wrote: I wouldn't do it. The reasons are obvious. I would find out specifically what objects / methods they are wanting to allow access to, and I would build an API to give access only to those objects / methods. Open it up as a web service API and do it right. -- Jim Rising Serial Entrepreneur Software Engineer Web Developer Knowledge work requires both autonomy and accountability. On Thu, Apr 23, 2009 at 9:34 AM, Ian Skinner h...@ilsweb.com wrote: Did I get your attention? Luckily these are read-only queries, but still! I have a meeting this afternoon to discuss reworking/developing an interface to allow anonymous, anybody in the world, users to develop add hock queries on a respectably large database. This database gets about 2.5 million transactions, and growing, a year and has been around since 1973. It is public data, so we need to provide public access to it. We would like it to be self service public access so the public can stop bothering us for the data and we can spend more time on fun projects :) There is currently a form based interface, but it has serious limitations and is no longer serving its role well. So this meeting is to discuss what to do about this and how much time and effort it might take. I am soliciting you all for ideas I may be overlooking on possible solutions and|or gotcha's for something like this. So fire away please. Thank You Ian ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321924 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: YIKES! I must let internet users write SQL queries for our database!
Ian, What about creating a Flex based tool that allows users to choose the table they want to get the data from and then the columns for the table (that you wanted to be able to select by) would be available. Basically making a 'drag and drop' kinda query builder. where the users don't type anything, but are allowed to select what they want that is available. That's my $.02 Rob On Thu, Apr 23, 2009 at 10:34 AM, Ian Skinner h...@ilsweb.com wrote: Did I get your attention? Luckily these are read-only queries, but still! I have a meeting this afternoon to discuss reworking/developing an interface to allow anonymous, anybody in the world, users to develop add hock queries on a respectably large database. This database gets about 2.5 million transactions, and growing, a year and has been around since 1973. It is public data, so we need to provide public access to it. We would like it to be self service public access so the public can stop bothering us for the data and we can spend more time on fun projects :) There is currently a form based interface, but it has serious limitations and is no longer serving its role well. So this meeting is to discuss what to do about this and how much time and effort it might take. I am soliciting you all for ideas I may be overlooking on possible solutions and|or gotcha's for something like this. So fire away please. Thank You Ian ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321852 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: YIKES! I must let internet users write SQL queries for our database!
Rob Parkhill wrote: What about creating a Flex based tool... That has potential. The current tool is simple HTML forms that attempt to do what you describe, but does not flow very well as it currently exists. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321853 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: YIKES! I must let internet users write SQL queries for our database!
Hey Ian, My first thoughts on this would be to ensure that YOU keep total control over the actually SQL that is being run, if you're working with large sets of data (more than a few GB) than the performance problems which arise from poorly written SQL could likely cause you all kinds of beef ;-) and that's just assuming that people aren't hitting you with malicious code (which they will) Not only that but also having to keep an eye on the queries people are running to ensure your indexes and statistics are configured in a manner which keeps things efficient is also quite a task bleh, it'd be messy I'm sure. I'd suggest developing some form of data access API in the form of a web service or possibly with your own front end on it if that's what the use case needs which allows users access to data but means you can retain control over the actual SQL which is executed at runtime. I don't know the full details of your use case at the moment but if I was in your shoes my first thought would jump to publishing a webservice API which allows people access to the data. I think if you let users write SQL to work on your data you'll likely just end up wanted to kill yourself, genuinely! The performance and security risks involved are just so huge. Rob -Original Message- From: Ian Skinner [mailto:h...@ilsweb.com] Sent: 23 April 2009 15:35 To: cf-talk Subject: YIKES! I must let internet users write SQL queries for our database! Did I get your attention? Luckily these are read-only queries, but still! I have a meeting this afternoon to discuss reworking/developing an interface to allow anonymous, anybody in the world, users to develop add hock queries on a respectably large database. This database gets about 2.5 million transactions, and growing, a year and has been around since 1973. It is public data, so we need to provide public access to it. We would like it to be self service public access so the public can stop bothering us for the data and we can spend more time on fun projects :) There is currently a form based interface, but it has serious limitations and is no longer serving its role well. So this meeting is to discuss what to do about this and how much time and effort it might take. I am soliciting you all for ideas I may be overlooking on possible solutions and|or gotcha's for something like this. So fire away please. Thank You Ian ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321855 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: YIKES! I must let internet users write SQL queries for our database!
Ian Skinner wrote: Did I get your attention? Luckily these are read-only queries, but still! I forgot to mention an important consideration is to somehow control and mitigate query requests so that users don't tie up the database with a task taking hours and hours to process. It is already a batch processing system, where the users do not get the response immediately, but rather it is submitted to a que and then the user receives an e-mail with a ftp link to the resulting output files when their request is completed. But, more and more frequently a request is to large and|or complex and it ties up the que so that nothing can be done until such a time as a DBA can get in there and kill the process that is jamming up the system. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321856 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: YIKES! I must let internet users write SQL queries for our database!
Did I get your attention? Luckily these are read-only queries, but still! A couple of things: 1. Create a large selection of views with nice names, it will make it easier for your users to identify the tables and columns they are looking for. This will also remove or reduce the need for JOINs 2. The flex/AJAX query builder idea is a nice one, you could also provide a drop-down driven query builder and let it dynamically build the SQL statement for the user, then give the user the ability to edit the SQL. Since you're running off a replicated database the need for heavy regex checking might be reduced but you're still going to want to run the entire query through a screening to check for SQL injection. Good luck! Hatton ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321857 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: YIKES! I must let internet users write SQL queries for our database!
Robert Rawlins - Think Blue wrote: Hey Ian, My first thoughts on this would be to ensure that YOU keep total control over the actually SQL that is being run, if you're working with large sets of data (more than a few GB) than the performance problems which arise from poorly written SQL could likely cause you all kinds of beef ;-) For sure, the current HTML form based interface that does this still allows users to build queries that kill the system, which is one of the main drivers for todays meeting. To discuss what and how the system could be improved and how much effort such a project might take. The web services idea is a good one, I'll definitely put it on the list for discussion. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321858 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: YIKES! I must let internet users write SQL queries for our database!
Ian, Just out of interest, how much data are we talking about? Roughly? DB size, tables, rows etc. My web service suggestion was really based on the idea that data was returned immediately but after reading your other post I'm less sold on my own idea ;-) Can you go into a little more depth about you current use case, so, a user comes to the site, completed an HTML form about the data they want from you, you then process that data and package it into an email for them, and you're looking to automate that process a little more? Is that correct? Rob -Original Message- From: Ian Skinner [mailto:h...@ilsweb.com] Sent: 23 April 2009 16:25 To: cf-talk Subject: Re: YIKES! I must let internet users write SQL queries for our database! Robert Rawlins - Think Blue wrote: Hey Ian, My first thoughts on this would be to ensure that YOU keep total control over the actually SQL that is being run, if you're working with large sets of data (more than a few GB) than the performance problems which arise from poorly written SQL could likely cause you all kinds of beef ;-) For sure, the current HTML form based interface that does this still allows users to build queries that kill the system, which is one of the main drivers for todays meeting. To discuss what and how the system could be improved and how much effort such a project might take. The web services idea is a good one, I'll definitely put it on the list for discussion. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321859 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: YIKES! I must let internet users write SQL queries for our database!
Robert Rawlins - Think Blue wrote: Ian, Just out of interest, how much data are we talking about? Roughly? DB size, tables, rows etc. Ok, the 10 second description, otherwise we could get into a hours long dissertation. This is a very old and much cobbled together system. A main table that gets approcamately 2.5 million, and growing, new records a year. We have been collecting this data since 1973 and it currently has 46,594,827 rows. This data provides transaction information on the usage of products stored in a table of tens of thousands of pesticide products. Each of these products reference a table of thousands of chemicals of which each product is composed. When this data is exported from our internal production database to the external report database, these three related tables and some other minor tables are flattened into a singe table containing all the information about each transaction. Other then this main table there are minor related tables that provide looks up for some of the main fields in these tables, the counties in California (58), Range, Township and section numbers, years, sites|commodities (i.e. stuff grown on farms) and such. Can you go into a little more depth about you current use case, so, a user comes to the site, completed an HTML form about the data they want from you, you then process that data and package it into an email for them, and you're looking to automate that process a little more? Is that correct? Well not automate it more, but remove systematic failures and limitations of the current system. Such that it is easy for a user to build too complex or large of a query so that the process ties up the system and causes a log jam until it is cleared. And that users can not currently select data across multiple years, a common request. The main use case is for a user to come to the system and want to know how much and|or what kind of pesticides have been applied to a particular place, food commodity andIor time. Rob -Original Message- From: Ian Skinner [mailto:h...@ilsweb.com] Sent: 23 April 2009 16:25 To: cf-talk Subject: Re: YIKES! I must let internet users write SQL queries for our database! Robert Rawlins - Think Blue wrote: Hey Ian, My first thoughts on this would be to ensure that YOU keep total control over the actually SQL that is being run, if you're working with large sets of data (more than a few GB) than the performance problems which arise from poorly written SQL could likely cause you all kinds of beef ;-) For sure, the current HTML form based interface that does this still allows users to build queries that kill the system, which is one of the main drivers for todays meeting. To discuss what and how the system could be improved and how much effort such a project might take. The web services idea is a good one, I'll definitely put it on the list for discussion. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321867 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: YIKES! I must let internet users write SQL queries for our database!
Hi Ian, Ah you call that big ;-) Mine is at least an inch bigger :-p Well, that certainly makes a great deal more sense. Like you say the primary concern is to stop users from creating queries which kill the system. I think with some thought put into the construction tool this could certainly help. For starters, are the queries which the users run very varied? Or is it a very similar query each time which lets them specify parameters through the form? If it's the latter than placing constraints on the parameters to stop them querying for multiple years etc will go a fair way to achieving your goal. Secondly would be to assess the database and its capable performance and make it run the queries as efficiently as possible, I know from my own experience working with our reports database (currently about 11,000,000 rows and growing quite likely to grow to a similar size as yours within the next 12 months has shown me the benefits of good database administration. There are several key areas where performance can be gained. 1. SQL - Ensuring the queries written are optimized and achieving their goal as well as possible. 2. Indexing, having the correct indexes on your data can have a MASSIVE impact, for instance, we had statistical queries which caused similar problems you what you're seeing (slow running or crashing the server) and yet the addition of a single index means the query now regularly runs in less than a second :-D 3. Maintenance, keeping your databases maintained by rebuilding statistics and indexed, compressing the files etc, all helps benefit performance. 4. Hardware, ensuring that the box you've got the database is up to the job, where databases are concerned this generally means RAM and plenty of it, this saves the DB having to scratch the disk every time it needs data. I think that look at these kinds of measures would be a good starting point for you in making things feel a great deal tighter. I'd be keen to ensure that all that stuff is running firmly before worrying too much about the tools on the front end. As for the front end, it's all about who your users is an how regularly they want data, if it's a laymen who wants the data every now and then I would say a web service isn't a good idea as they won't benefit, however, if these are professional clients who could utilize the service to save them time coming and filling in forms then it'd be a great solution. Either way, with something ColdSpring you could create a really nice Service Layer for yourself and your nice front end and then with a couple of lines of code open that API up as well using remote proxies then you have the best of both worlds for very little effort. Does any of that make sense? I hope I'm not teaching you to suck eggs on the DB side of things, I just know from my own experience I assume that I'm reaching the limits of my system when expecting it to return records instantly and then suddenly someone shows me a neat trick that makes it happen. Rob -Original Message- From: Ian Skinner [mailto:h...@ilsweb.com] Sent: 23 April 2009 16:51 To: cf-talk Subject: Re: YIKES! I must let internet users write SQL queries for our database! Robert Rawlins - Think Blue wrote: Ian, Just out of interest, how much data are we talking about? Roughly? DB size, tables, rows etc. Ok, the 10 second description, otherwise we could get into a hours long dissertation. This is a very old and much cobbled together system. A main table that gets approcamately 2.5 million, and growing, new records a year. We have been collecting this data since 1973 and it currently has 46,594,827 rows. This data provides transaction information on the usage of products stored in a table of tens of thousands of pesticide products. Each of these products reference a table of thousands of chemicals of which each product is composed. When this data is exported from our internal production database to the external report database, these three related tables and some other minor tables are flattened into a singe table containing all the information about each transaction. Other then this main table there are minor related tables that provide looks up for some of the main fields in these tables, the counties in California (58), Range, Township and section numbers, years, sites|commodities (i.e. stuff grown on farms) and such. Can you go into a little more depth about you current use case, so, a user comes to the site, completed an HTML form about the data they want from you, you then process that data and package it into an email for them, and you're looking to automate that process a little more? Is that correct? Well not automate it more, but remove systematic failures and limitations of the current system. Such that it is easy for a user to build too complex or large of a query so that the process ties up the system and causes a log jam until it is cleared. And that users can not currently select data
Re: YIKES! I must let internet users write SQL queries for our database!
Ian Skinner wrote: Did I get your attention? Luckily these are read-only queries, but still! I forgot to mention an important consideration is to somehow control and mitigate query requests so that users don't tie up the database with a task taking hours and hours to process. It is already a batch processing system, where the users do not get the response immediately, but rather it is submitted to a que and then the user receives an e-mail with a ftp link to the resulting output files when their request is completed. But, more and more frequently a request is to large and|or complex and it ties up the que so that nothing can be done until such a time as a DBA can get in there and kill the process that is jamming up the system. If CF is handling the database access you can set a timeout on the query to prevent it from tying up the server? I honestly have never run into this specific situation, so I don't know how well that works, but the first thing I would test is if I could simply specify timeout=60 or somesuch in the cfquery and have it automatically error and exit early if the statement is taking too long. I kind of wonder too if it might not be possible to create an AIR app to house a Flex form and then use the Flex app to alert the user and give them their results when it's done... Might even give you the ability to give them more feedback about the process in the form of Your query is number 3 in queu. and then periodically update their position in queue. -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 817.385.0301 http://onTap.riaforge.org/blog ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321876 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: YIKES! I must let internet users write SQL queries for our database!
Database tuning is an important line item on my discussion list for this meeting. To expand on some of your points. First the system currently limits users to only selecting data from on year at a time as a stop-gap measure to try and limit the operating cost of the query. The trouble is that this is both too limiting and not limiting enough. First it is still quite possible for a user to build a query that will jam up the system while at the same time it limits users who want specific enough information to be reasonable, but want it for multiple years. It forces them to submit the same request over and over only changing the year. Speaking of these users, they are truly a mixed bunch. They range from the professional who supposedly knows what they want to the soccer parent who wants to know *everything* that has been used withing a 100 mile radius of their home/work/school. Unfortunately we have to cater to both as best as we can. A big driver for making this as self-help as possible was the realization that we where commonly getting these requests from both sides of a litigation case in civil court. But because of how each side might have asked for the information combined with how different developers might have understood the request and the data we could be providing radically different results to each side that is effect provided some sort of de-facto 'interpretation' of the data. A position we really do not want to be in. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321878 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: YIKES! I must let internet users write SQL queries for our database!
Yeah you're quite right here, this is certainly something to consider as a security net down the line, however my only worry would be that it doesn't solve the root of the problem which seems to be this poor database which is struggling under its own weight. I think by the sounds of things the application is having regular occurrences of slow performance which means he'd just be spitting errors at users all the time, if we can solve the core problem and then use this method to solve the odd one-off occasions when performance struggles we'll be onto a winner I think. Does that sound right? Rob -Original Message- From: s. isaac dealey [mailto:i...@turnkey.to] Sent: 23 April 2009 17:27 To: cf-talk Subject: Re: YIKES! I must let internet users write SQL queries for our database! Ian Skinner wrote: Did I get your attention? Luckily these are read-only queries, but still! I forgot to mention an important consideration is to somehow control and mitigate query requests so that users don't tie up the database with a task taking hours and hours to process. It is already a batch processing system, where the users do not get the response immediately, but rather it is submitted to a que and then the user receives an e-mail with a ftp link to the resulting output files when their request is completed. But, more and more frequently a request is to large and|or complex and it ties up the que so that nothing can be done until such a time as a DBA can get in there and kill the process that is jamming up the system. If CF is handling the database access you can set a timeout on the query to prevent it from tying up the server? I honestly have never run into this specific situation, so I don't know how well that works, but the first thing I would test is if I could simply specify timeout=60 or somesuch in the cfquery and have it automatically error and exit early if the statement is taking too long. I kind of wonder too if it might not be possible to create an AIR app to house a Flex form and then use the Flex app to alert the user and give them their results when it's done... Might even give you the ability to give them more feedback about the process in the form of Your query is number 3 in queu. and then periodically update their position in queue. -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 817.385.0301 http://onTap.riaforge.org/blog ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321879 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: YIKES! I must let internet users write SQL queries for our database!
s. isaac dealey wrote: If CF is handling the database access you can set a timeout on the query to prevent it from tying up the server? CF is not handling the query. It is simply the interface to build the query which then is submitted to the database. Stored procedures then run the queries in the que, write the results to files on a FTP server and send an e-mail back to the requester when this is completed. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321880 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: YIKES! I must let internet users write SQL queries for our database!
Hi Ian, That all makes a great deal of sense. I really feel you're onto the right track with the database tuning method, that'll be a great start, whilst this is a fairly heavy database, in this day and age it also isn't really THAT heavy and these slow running queries, crashes and batch processes really isn't something you should have to worry about, I'm sure they're all quite solvable. From a front end perspective I think that defining an API for data retrieval is really a key for you, if you can create a common interface for the data collection which allows people to specify parameters for the data, you can then build a front end app, be it HTML, FLEX, AIR or otherwise which allows users to access the data without you and the other developers having to interpret their bespoke requirements and build queries for them. This is really just building a reporting tool for them. Then, should you wish to open a web service up which would allow more experienced people/corporations to access the data then that becomes very easy because all you're doing is publishing a version of the same API you use for your application. This way you're able to meet everyone's requirements. And with this strictly documented and defined API there is none of the confusion about misinterpreting their requirements, if they get the wrong data it's their fault for specifying the incorrect parameters. Because DB tuning is all quite specific to the data you want to retrieve (and insert) I would first start assessing and designing the API, once you know what and how the data is going to be retrieved (I mean you know what the queries look like) then you can tune the database to meet that requirement. Furthermore once you're in that position you can also start thinking about query caching, views and things like that to further extend the performance of the system. Rob -Original Message- From: Ian Skinner [mailto:h...@ilsweb.com] Sent: 23 April 2009 17:35 To: cf-talk Subject: Re: YIKES! I must let internet users write SQL queries for our database! Database tuning is an important line item on my discussion list for this meeting. To expand on some of your points. First the system currently limits users to only selecting data from on year at a time as a stop-gap measure to try and limit the operating cost of the query. The trouble is that this is both too limiting and not limiting enough. First it is still quite possible for a user to build a query that will jam up the system while at the same time it limits users who want specific enough information to be reasonable, but want it for multiple years. It forces them to submit the same request over and over only changing the year. Speaking of these users, they are truly a mixed bunch. They range from the professional who supposedly knows what they want to the soccer parent who wants to know *everything* that has been used withing a 100 mile radius of their home/work/school. Unfortunately we have to cater to both as best as we can. A big driver for making this as self-help as possible was the realization that we where commonly getting these requests from both sides of a litigation case in civil court. But because of how each side might have asked for the information combined with how different developers might have understood the request and the data we could be providing radically different results to each side that is effect provided some sort of de-facto 'interpretation' of the data. A position we really do not want to be in. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321882 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: YIKES! I must let internet users write SQL queries for our database!
Yeah, the three main ideas I have developed so far are 1) database tuning, 2) API/Web service 3)Possible go to Flex after the first two. I agree that we are living with some unnecessary complexity. A good deal of this process was developed in the 90s when databases and servers were not nearly as powerful as they are now. A great deal complexity was built to try and mitigate the limits of the systems back then. I suspect we could simplify and normalize the system and then tune it properly and get a great deal of performance improvement. But the question is how big a project that is and can we get buy in for it. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321885 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: YIKES! I must let internet users write SQL queries for our database!
Yeah it sounds like your typical legacy type systems that's a little out of date. You might not even need to normalize the data, there's a lot to be said for performance if the data is denormalized and flatter, it's a balance you'll have to find. If you look back through all the requests you've had over the past couple of years you'll be able to build a pretty strong set of use cases for the interface, that'll help you understand what data you want out of the system, then a good investment will be with someone who knows SQL like the back of their hand, they'll then come in and write queries and tune the system to perform sweetly for you. Keep us posted on the progress you make, I'm sure I'll learn just as much from it as you do. Rob -Original Message- From: Ian Skinner [mailto:h...@ilsweb.com] Sent: 23 April 2009 18:12 To: cf-talk Subject: Re: YIKES! I must let internet users write SQL queries for our database! Yeah, the three main ideas I have developed so far are 1) database tuning, 2) API/Web service 3)Possible go to Flex after the first two. I agree that we are living with some unnecessary complexity. A good deal of this process was developed in the 90s when databases and servers were not nearly as powerful as they are now. A great deal complexity was built to try and mitigate the limits of the systems back then. I suspect we could simplify and normalize the system and then tune it properly and get a great deal of performance improvement. But the question is how big a project that is and can we get buy in for it. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321887 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: YIKES! I must let internet users write SQL queries for our database!
I wouldn't do it. The reasons are obvious. I would find out specifically what objects / methods they are wanting to allow access to, and I would build an API to give access only to those objects / methods. Open it up as a web service API and do it right. -- Jim Rising Serial Entrepreneur Software Engineer Web Developer Knowledge work requires both autonomy and accountability. On Thu, Apr 23, 2009 at 9:34 AM, Ian Skinner h...@ilsweb.com wrote: Did I get your attention? Luckily these are read-only queries, but still! I have a meeting this afternoon to discuss reworking/developing an interface to allow anonymous, anybody in the world, users to develop add hock queries on a respectably large database. This database gets about 2.5 million transactions, and growing, a year and has been around since 1973. It is public data, so we need to provide public access to it. We would like it to be self service public access so the public can stop bothering us for the data and we can spend more time on fun projects :) There is currently a form based interface, but it has serious limitations and is no longer serving its role well. So this meeting is to discuss what to do about this and how much time and effort it might take. I am soliciting you all for ideas I may be overlooking on possible solutions and|or gotcha's for something like this. So fire away please. Thank You Ian ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321918 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: YIKES! I must let internet users write SQL queries for our database!
sorry guys... I guess I'm a bit late in the conversation, and my thread only showed the initial post. :) -- Jim Rising Serial Entrepreneur Software Engineer Web Developer Knowledge work requires both autonomy and accountability. On Thu, Apr 23, 2009 at 6:57 PM, Jim Rising cfflex...@jimrising.com wrote: I wouldn't do it. The reasons are obvious. I would find out specifically what objects / methods they are wanting to allow access to, and I would build an API to give access only to those objects / methods. Open it up as a web service API and do it right. -- Jim Rising Serial Entrepreneur Software Engineer Web Developer Knowledge work requires both autonomy and accountability. On Thu, Apr 23, 2009 at 9:34 AM, Ian Skinner h...@ilsweb.com wrote: Did I get your attention? Luckily these are read-only queries, but still! I have a meeting this afternoon to discuss reworking/developing an interface to allow anonymous, anybody in the world, users to develop add hock queries on a respectably large database. This database gets about 2.5 million transactions, and growing, a year and has been around since 1973. It is public data, so we need to provide public access to it. We would like it to be self service public access so the public can stop bothering us for the data and we can spend more time on fun projects :) There is currently a form based interface, but it has serious limitations and is no longer serving its role well. So this meeting is to discuss what to do about this and how much time and effort it might take. I am soliciting you all for ideas I may be overlooking on possible solutions and|or gotcha's for something like this. So fire away please. Thank You Ian ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321920 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
