1. Field of the Invention
The present invention relates generally to web applications that provide an Application Program Interface (“API”) for external, untrusted developers and more particularly to systems and methods for implementation of a structured query language interface in a distributed database environment.
2. Description of Related Art
Conventionally, Structured Query Language (“SQL”) is designed for querying data contained in a single relational database. Though widely understood and adopted, SQL is not designed for querying data contained in a distributed database, nor is it designed for providing access to this data to external developers. Instead, alternative Application Program Interfaces are used by external developers to access this data.
Alternative Application Program Interfaces suffer from several drawbacks. First, most alternative Application Program Interfaces are incapable of performing focused queries. Thus, bandwidth and computing resources are wasted in providing query responses that may contain large amounts of irrelevant data. Second, third-party developers employing alternative Application Program Interfaces typically need to reconfigure their respective programs for each new query routine. Accordingly, when a distributed database is expanded with one or more additional categories of data, third-party developers generally need to spend time reconfiguring their respective programs. Third, business logic rules imposed by an administrator and/or a user of a distributed database typically must be applied to data to be queried via an alternative Applied Program Interface before third-party developers may access the data. Thus, there is a need for systems and methods for implementation of a structured query language interface in a distributed database environment.
Systems and methods for implementation of a structured query language interface in a distributed database environment are provided. Exemplary systems include a distributed database configured with items of data, a volatile cache memory configured with a subset of the items of data, a scripting language configured to extract data from the volatile cache memory, and a structured query language interface configured to receive a query over a network, to send the query to the scripting language, and to receive extracted data from the scripting language in response to the query. Further systems include the scripting language configured to apply business logic rules to the extracted data before the extracted data is sent to the structured query language interface. The structured query language interface may also be further configured to send some or all of the extracted data in a format to accommodate a database maintained by a third-party developer.
Exemplary methods include configuring a distributed database with items of data, configuring a volatile cache memory with a subset of the items of data, configuring a scripting language to extract data from the volatile cache memory, and configuring the structured query language interface in communication with the scripting language to receive a query over a network, to send the query to the scripting language, and to receive extracted data from the scripting language in response to the query. Further methods include configuring the scripting language to apply business logic rules to extracted data before the extracted data is sent to the structured query language interface.
According to various exemplary systems and methods, an SQL interface is used by one or more third-party developers to query an expandable distributed database. Query responses may be subject to business logic rules imposed by an administrator and/or user of the distributed database. As additional categories of information are added to the distributed database, third-party developers may source the information found in the additional categories, without having to reconfigure their respective systems. Because query constraints may include such commands as the WHERE clause and only list certain fields in the SELECT clause, query responses match a specific condition, rather than providing all of the information available about a large set of items.
According to various exemplary systems, the distributed database 135 may represent a distributed database for a web-based social network. The distributed database 135 may be comprised of multiple databases and the number of databases may increase or decrease over time.
The volatile cache memory 130, in one exemplary system and method, may pool most or all of the data found in the distributed database 135. In general, the volatile cache memory 130 facilitates rapid query response times, when compared to the time required for querying all or most all of the databases forming the distributed database 135. Further, the data found in the volatile cache memory 130 may represent the data set from which a particular query response may be sourced. The data found in the distributed database 135 may represent the data sourced in a query response if the volatile cache memory 130 appears to lack certain pieces of data that may be the subject of a particular query and found in the distributed database 135.
In one further exemplary system and method, the optional business logic rules 125 may represent limitations selected by the users or members of a web-based social network. For example, a member of the social network may limit the other members of the social network that may access the member's personal information, such as the member's address and telephone number. The business logic rules 125 may also represent limitations selected by an administrator of the social network.
The scripting language 120, according to various exemplary systems, may be any programming environment typically used to create dynamic web pages, including, however not limited to, PHP Hypertext Preprocessor. With syntax from C, Java and Perl, PHP code may be embedded within HTML pages for server side execution. PHP may be used to extract data from a database in order to present it on a web page. PHP may extract data out of the volatile cache memory 130. Optionally, PHP may apply the business logic rules 125 against extracted data. It is this data (with or without application of the business logic rules 125) that is generally provided to the third-party developer 115 in the form of an SQL query response.
In one exemplary system, the SQL interface 105 may utilize SQL-style syntax for queries. For example, the third-party developer 115, such as an online book retailer, may wish to learn the identities of the members of a web-based social network who have an interest in baseball. Accordingly, the third-party developer 115 may utilize the SQL interface 105 to perform such a query. Optionally, a response provided to the third-party developer 115 utilizing the SQL interface 105 may be limited by the business logic rules 125, such as privacy limitations. For example, the third-party developer 115 may receive the name, however, not the email address of a particular social network member who is interested in baseball.
According to further exemplary systems and methods, query responses provided to a third-party developer, such as the third-party developer 115, are automatically formatted to accommodate the database of the third-party developer. For example, numerous third-party developers may each have a database, with each database having various subsets of the data found in an expandable distributed database, such as the distributed database 135. An expandable distributed database may be represented by a social network that adds additional fields or categories of data associated with its members. For instance, a social network may add a field to its distributed database for the brand of car owned by each of its members. Accordingly, some third-party developers may wish to modify their respective databases to include this new information, subject to any business logic rules (e.g. business logic rules 125) imposed by the social network and/or its members. The responses provided to the third-party developers are automatically formatted to accommodate the respective databases of the third-party developers.
Based on one exemplary system and method, queries may be indexable. In order to make a query indexable, the WHERE may contain an “=” or “IN” clause for one of the columns marked with a “*”. A third-party developer may not, for example, just specify WHERE 1 as the entire WHERE clause, since in many cases this may result in far too large of a result set (the photos table as described herein, for example, may contain over 1 billion entries). In general, queries should be limited to working on a specific, enumerable set of items, by referencing one of the indexable columns (i.e. the columns marked with a “*” in reference table 200) in the WHERE clause. More specifically, if the WHERE clause were to be written in Disjunctive Normal Form, each conjunction should contain at least one “=” or “IN” expression that uses an indexable column.
Queries, according to various exemplary methods, may be in the form SELECT [fields] FROM [table] WHERE [conditions]. The FROM clause in the third-party developer SQL interface may contain only a single table. Additionally, in the SELECT or WHERE clauses, the IN keyword may be used to perform subqueries, but the subqueries may not reference variables in the outer query's scope.
For example, using the exemplary reference table 200 shown in
In response, the third-party developer may be provided with the following information:
As another example, using the exemplary reference table 200, a third-party developer may wish to obtain the photo image sources and captions for photos in which two given users or members of the social network are both tagged, and only for those photos in the set that have captions. Accordingly, the third-party developer may utilize the third-party developer SQL interface to query:
In response, the third-party developer may be provided with the following information:
At step 310, a distributed database is configured with items of data. According to one exemplary method, the distributed database is associated with a web-based social network.
At step 320, a volatile cache memory is configured with a subset of the items of data in the distributed database.
At step 330, a scripting language is configured to extract data from the volatile cache memory.
At step 340, a structured query language interface is configured to receive a query over a network, to send the query to the scripting language, and to receive extracted data from the scripting language in response to the query.
At step 350, the structured query language interface is configured to send some or all of the extracted data to a third-party developer in a format to accommodate a database maintained by the third-party developer.
At step 410, a query is received over a network from an application or web site maintained by a third-party developer.
At step 420, data responsive to the query is extracted by a structured query language.
At step 430, business logic rules are applied to the extracted data.
At step 440, the extracted data is formatted to accommodate a database maintained by the third-party developer.
At step 450, the formatted extracted data is sent over the network to the third-party developer.
While various systems and methods have been described above, it should be understood that they have been presented by way of example only, and not limitation.
The present application claims the benefit and priority of U.S. Provisional Patent Application Ser. No. 60/902,777 filed on Feb. 21, 2007, titled “Systems and Methods for Implementation of a Structured Query Language Interface in a Distributed Database Environment,” which is hereby incorporated by reference.
Number | Name | Date | Kind |
---|---|---|---|
5937413 | Hyun | Aug 1999 | A |
6029141 | Bezos | Feb 2000 | A |
6856995 | Ibitayo et al. | Feb 2005 | B1 |
7013292 | Hsu | Mar 2006 | B1 |
7249123 | Elder | Jul 2007 | B2 |
7269590 | Hull | Sep 2007 | B2 |
7353247 | Hough et al. | Apr 2008 | B2 |
7558841 | Taboada et al. | Jul 2009 | B2 |
7616124 | Paessel et al. | Nov 2009 | B2 |
7680882 | Tiu et al. | Mar 2010 | B2 |
7853881 | Aly Assal et al. | Dec 2010 | B1 |
7945653 | Zuckerberg et al. | May 2011 | B2 |
8015019 | Smith et al. | Sep 2011 | B1 |
8380716 | Mirus | Feb 2013 | B2 |
8496531 | Youm | Jul 2013 | B2 |
8572117 | Rappaport | Oct 2013 | B2 |
8583480 | Byrne | Nov 2013 | B2 |
8615432 | Howard et al. | Dec 2013 | B2 |
8706550 | Chalawsky et al. | Apr 2014 | B1 |
8732101 | Wilson et al. | May 2014 | B1 |
20010037721 | Hasegawa | Nov 2001 | A1 |
20020049603 | Mehra et al. | Apr 2002 | A1 |
20020059201 | Work | May 2002 | A1 |
20020124053 | Adams et al. | Sep 2002 | A1 |
20030037064 | Choban et al. | Feb 2003 | A1 |
20030145093 | Oren | Jul 2003 | A1 |
20030222918 | Coulthard | Dec 2003 | A1 |
20030225632 | Tong | Dec 2003 | A1 |
20040024846 | Randall | Feb 2004 | A1 |
20040088177 | Travis | May 2004 | A1 |
20040148275 | Achlioptas | Jul 2004 | A1 |
20050021750 | Abrams | Jan 2005 | A1 |
20050114759 | Williams | May 2005 | A1 |
20050154639 | Zetmeir | Jul 2005 | A1 |
20050159970 | Buyukkokten | Jul 2005 | A1 |
20050171799 | Hull | Aug 2005 | A1 |
20050171955 | Hull | Aug 2005 | A1 |
20050177385 | Hull | Aug 2005 | A1 |
20050197846 | Pezaris | Sep 2005 | A1 |
20050198020 | Garland | Sep 2005 | A1 |
20050198031 | Pezaris | Sep 2005 | A1 |
20050198305 | Pezaris | Sep 2005 | A1 |
20050203807 | Bezos | Sep 2005 | A1 |
20050216300 | Appelman et al. | Sep 2005 | A1 |
20050216550 | Paseman | Sep 2005 | A1 |
20050235062 | Lunt | Oct 2005 | A1 |
20050256756 | Lam | Nov 2005 | A1 |
20060004590 | Khoo | Jan 2006 | A1 |
20060041543 | Achlioptas | Feb 2006 | A1 |
20060042483 | Work | Mar 2006 | A1 |
20060047605 | Ahmad | Mar 2006 | A1 |
20060048059 | Etkin | Mar 2006 | A1 |
20060052091 | Onyon | Mar 2006 | A1 |
20060080613 | Savant | Apr 2006 | A1 |
20060085419 | Rosen | Apr 2006 | A1 |
20060136419 | Brydon | Jun 2006 | A1 |
20060143183 | Goldberg | Jun 2006 | A1 |
20060161599 | Rosen | Jul 2006 | A1 |
20060173985 | Moore | Aug 2006 | A1 |
20060184617 | Nicholas | Aug 2006 | A1 |
20060190281 | Kott | Aug 2006 | A1 |
20060194186 | Nanda | Aug 2006 | A1 |
20060218225 | Hee Voon | Sep 2006 | A1 |
20060229063 | Koch | Oct 2006 | A1 |
20060230061 | Sample | Oct 2006 | A1 |
20060247940 | Zhu | Nov 2006 | A1 |
20060248573 | Pannu | Nov 2006 | A1 |
20060256008 | Rosenberg | Nov 2006 | A1 |
20060265227 | Sadamura | Nov 2006 | A1 |
20060293976 | Nam | Dec 2006 | A1 |
20070174389 | Armstrong | Jul 2007 | A1 |
20070186007 | Field et al. | Aug 2007 | A1 |
20070203996 | Davitz et al. | Aug 2007 | A1 |
20070208916 | Tomita | Sep 2007 | A1 |
20070282987 | Fischer | Dec 2007 | A1 |
20080005076 | Payne | Jan 2008 | A1 |
20080010343 | Escaffi | Jan 2008 | A1 |
20080033776 | Marchese | Feb 2008 | A1 |
20080040428 | Wei | Feb 2008 | A1 |
20080046976 | Zuckerberg | Feb 2008 | A1 |
20080052371 | Partovi et al. | Feb 2008 | A1 |
20080070697 | Robinson | Mar 2008 | A1 |
20080086458 | Robinson et al. | Apr 2008 | A1 |
20080091723 | Zuckerberg et al. | Apr 2008 | A1 |
20080098313 | Pollack | Apr 2008 | A1 |
20080104679 | Craig | May 2008 | A1 |
20080120411 | Eberle | May 2008 | A1 |
20080214148 | Ramer et al. | Sep 2008 | A1 |
20080270038 | Partovi et al. | Oct 2008 | A1 |
20080274687 | Roberts et al. | Nov 2008 | A1 |
20080313714 | Fetterman et al. | Dec 2008 | A1 |
20090030919 | Brezina et al. | Jan 2009 | A1 |
20090030940 | Brezina et al. | Jan 2009 | A1 |
20090055435 | Kiviluoto et al. | Feb 2009 | A1 |
20090070334 | Callahan et al. | Mar 2009 | A1 |
20090164408 | Grigorik et al. | Jun 2009 | A1 |
20090177484 | Davis et al. | Jul 2009 | A1 |
20090187537 | Yachin et al. | Jul 2009 | A1 |
20090204581 | Lim | Aug 2009 | A1 |
20090282342 | Fabris et al. | Nov 2009 | A1 |
20100023434 | Bond | Jan 2010 | A1 |
20100174709 | Hansen et al. | Jul 2010 | A1 |
20100257028 | Hillerbrand | Oct 2010 | A1 |
20100274815 | Vanasco | Oct 2010 | A1 |
20100280879 | O'Sullivan et al. | Nov 2010 | A1 |
20100280913 | O'Sullivan et al. | Nov 2010 | A1 |
20100306185 | Smith et al. | Dec 2010 | A1 |
20100306834 | Grandison et al. | Dec 2010 | A1 |
20110038470 | Kent | Feb 2011 | A1 |
20110130168 | Vendrow et al. | Jun 2011 | A1 |
20110191417 | Rathod | Aug 2011 | A1 |
20110191717 | Cort et al. | Aug 2011 | A1 |
20120072416 | Gowel | Mar 2012 | A1 |
20120174155 | Mowrey et al. | Jul 2012 | A1 |
20130124542 | Lee et al. | May 2013 | A1 |
20130144899 | Lee et al. | Jun 2013 | A1 |
Entry |
---|
Hemedinger, Slaughter, “Social Networking and SAS: Running PROCs on your Facebook Friends”, pp. 1-8, 2011. |
Flores, Fernando et al. “Computer systems and the design of organizational interaction.” In ACM Transactions on Information Systems (TOIS), vol. 6, Issue 2, Apr. 1988. |
PCT International Search Report and Written Opinion, PCT US2008/02354, Aug. 1, 2008. |
Phanouriou, C., “UIML: A Device-Independent User Interface Markup Language,” PhD dissertation submitted to Computer Science and Applications Dept., Virginia Polytechnic Institute and State University. Published on Sep. 26, 2000, [Online] [Retrieved on Jun. 15, 2008]. Retrieved from the Internet: <URL: http://xml.coverpages.org/PhanouriouETD.pdf>. |
Chipin, “How Chipln Works: An Overview,” Dec. 15, 2006. [Online] [Retrieved Jun. 20, 2008] Retrieved from the Internet <URL:http://web.archive.org/web/20061215090739/www.chipin.com/overview.>. |
Parzek, E. “Social Networking to Chipln to a Good Cause,” Business Design Studio, Jun. 29, 2006. [Online] [Retrieved Jun. 20, 2008] Retrieved from the Internet <URL:http://www.businessdesignstudio.com/resources/blogger/2006/06/social-networking-to-chipin-to-good.html.>. |
Number | Date | Country | |
---|---|---|---|
20090049014 A1 | Feb 2009 | US |
Number | Date | Country | |
---|---|---|---|
60902777 | Feb 2007 | US |