Extensibility in a database system

• US 10,642,589 B2
• Filed: 03/13/2017
• Issued: 05/05/2020
• Est. Priority Date: 03/13/2017
• Status: Active Grant

• Current Assignee: Singlestore, Inc.
• Original Assignee: MemSQL, Inc.
• Inventors: Andrews, Michael Adam, Paroski, Andrew John, Hanson, Eric Norman
• Primary Examiner(s): Nguyen, Duy Khuong T
• Application Number:
  US15/457,688
• Publication Number:
  US 20180260196A1
• Time in Patent Office: 1,149 Days
• CPC Class Codes: G06F 16/242 Query formulation
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 8/447 Target code generation

Abstract

A method includes receiving a user-defined extension configured to operate on data from a content database and comprising code expressed in a first programming language, the user-defined extension comprising at least one unresolved object and a function calling the at least one unresolved object. On a first pass through the user-defined extension code, each respective unresolved object is bound to a respective placeholder object such that the function calls each respective placeholder object and each respective placeholder object is mapped to the respective unresolved object, and delaying evaluation of the user-defined extension code until a second pass through the user-defined extension code. On the second pass, machine code corresponding to a compiled version of the user-defined extension is obtained by sequentially obtaining machine code for each respective unresolved object as each respective placeholder is called in the function. A related system and non-transitory computer-readable medium are also provided.

Claims

1. A method comprising:

obtaining a user query in a structured query language, the user query comprising at least one query parameter for selecting data from a content database;

parsing the user query to identify a skeletal query form of the user query in a first programming language and the at least one query parameter, wherein the skeletal query comprising at least one unresolved object and a function calling the at least one unresolved object;

generating byte code defining a plurality of functions corresponding to the user query,

interpreting the byte code using the at least one query parameter, thereby returning a result of the user query satisfying the at least one query parameter,

generating machine code corresponding to the user query based on the byte code,

executing the machine code corresponding to the user query using the at least one query parameter if the user query is called again, thereby returning a result of the user query satisfying the at least one query parameter;

wherein the generating and executing machine code corresponding to the user query comprising;

on a first pass through the skeletal query;

binding each respective unresolved object to a respective placeholder object such that the function calls each respective placeholder object and each respective placeholder object is mapped to the respective unresolved object,

delaying evaluation of the skeletal query until a second pass through the skeletal query;

on a second pass through the skeletal query, obtaining machine code corresponding to a compiled version of the skeletal query by;

sequentially obtaining machine code for each respective unresolved object as each respective placeholder is called in the skeletal query; and

executing the machine code corresponding to the compiled version of the skeletal query, thereby performing at least one operation on the data of the content database.

2. The method of claim 1, wherein:

the at least one unresolved object comprises a plurality of table variables;

a first table variable of the plurality of table variables is defined in terms of at least one of;

a second table variable of the plurality of table variables, or

a scalar variable;

the function references the first table variable, thereby calling a nested invocation of the first table variable and the second table variable or the scalar variable; and

sequentially obtaining machine code for each respective unresolved object comprises unnesting the nested invocation of the first table variable and the second table variable or the scalar variable such that the function refers only to defined tables or literal values.

3. The method of claim 2, wherein the scalar variable is bound to the function at the time the function is assigned to the first table variable such that any subsequent modification to the scalar variable is not utilized when sequentially obtaining machine code for each respective unresolved object.

4. The method of claim 1, wherein obtaining the machine code for each respective unresolved object further comprises:

for a first subset of respective functions defined by the unresolved objects having corresponding machine code stored in a code database;

retrieving the corresponding machine code for the respective function from the code database; and

aggregating the corresponding machine code for each of the first subset of respective functions defined by the unresolved objects, thereby forming the machine code for each respective unresolved object.

5. The method of claim 1, wherein obtaining the machine code for each respective unresolved object further comprises:

for a second subset of respective functions defined by the unresolved objects that do not have corresponding machine code stored in a code database;

generating code in a second programming language corresponding to a compiled version of the respective function,

generating byte code corresponding to a compiled version of the code in the second programming language, and

obtaining the corresponding machine code for the respective function based on the byte code; and

aggregating the corresponding machine code for each of the second subset of respective functions defined by the unresolved objects, thereby forming the machine code for each respective unresolved object.

6. The method of claim 5, wherein obtaining the machine code corresponding to the compiled version of the respective function based on the byte code comprises:

for a second subset of respective functions defined by the byte code having corresponding machine code stored in the code database, retrieving the corresponding machine code for the respective functions; and

aggregating the corresponding machine code for each respective function defined by the byte code.

7. The method of claim 5, further comprising storing the corresponding machine code for the respective function based on the byte code in the code database.

8. The method of claim 1, further comprising storing the machine code corresponding to the compiled version of the skeletal query in a code database.

9. The method of claim 1, wherein the code database is stored in at least one of a local memory, a local storage, and a server cache.

10. The method of claim 1, wherein the skeletal query comprises one of:

a stored procedure, a user-defined function, a table-valued function and a user-defined aggregate function and the first programming language is a structured query language.

11. The method of claim 1, further comprising defining the skeletal query in a structured query language.

12. A non-transitory computer readable medium comprising instructions which, when executed by a processing system, cause the processing system to:

obtain a user query in a structured query language, the user query comprising at least one query parameter for selecting data from a content database;

parse the user query to identify a skeletal query form of the user query in a first programming language and the at least one query parameter, wherein the skeletal query comprising at least one unresolved object and a function calling the at least one unresolved object;

generate byte code defining a plurality of functions corresponding to the user query,

interpret the byte code using the at least one query parameter, thereby returning a result of the user query satisfying the at least one query parameter,

generate machine code corresponding to the user query based on the byte code,

execute the machine code corresponding to the user query using the at least one query parameter if the user query is called again, thereby returning a result of the user query satisfying the at least one query parameter;

wherein generate and execute machine code corresponding to the user query comprises;

on a first pass through the skeletal query;

bind each respective unresolved object to a respective placeholder object such that the function calls each respective placeholder object and each respective placeholder object is mapped to the respective unresolved object,

delay evaluation of the skeletal query until a second pass through the skeletal query;

on a second pass through the skeletal query, obtain machine code corresponding to a compiled version of the skeletal query by;

sequentially obtaining machine code for each respective unresolved object as each respective placeholder is called in the skeletal query; and

execute the machine code corresponding to the compiled version of the skeletal query, thereby performing at least one operation on the data of the content database.

13. The medium of claim 12, wherein:

the at least one unresolved object comprises a plurality of table variables;

a first table variable of the plurality of table variables is defined in terms of at least one of;

a second table variable of the plurality of table variables, or

a scalar variable;

the function references the first table variable, thereby calling a nested invocation of the first table variable and the second table variable or the scalar variable; and

sequentially obtaining machine code for each respective unresolved object comprises unnesting the nested invocation of the first table variable and the second table variable or the scalar variable such that the function refers only to defined tables or literal values.

14. The medium of claim 13, wherein the scalar variable is bound to the function at the time the function is assigned to the first table variable such that any subsequent modification to the scalar variable is not utilized when sequentially obtaining machine code for each respective unresolved object.

15. The medium of claim 12, wherein obtaining the machine code for each respective unresolved object further comprises:

for a first subset of respective functions defined by the unresolved objects having corresponding machine code stored in a code database;

retrieving the corresponding machine code for the respective function from the code database; and

aggregating the corresponding machine code for each of the first subset of respective functions defined by the unresolved objects, thereby forming the machine code for each respective unresolved object.

16. The medium of claim 15, wherein obtaining the machine code corresponding to the compiled version of the respective function based on the byte code comprises:

for a second subset of respective functions defined by the byte code having corresponding machine code stored in the code database, retrieving the corresponding machine code for the respective functions; and

aggregating the corresponding machine code for each respective function defined by the byte code.

17. The medium of claim 15, further comprising storing the corresponding machine code for the respective function based on the byte code in the code database.

18. The medium of claim 12, wherein obtaining the machine code for each respective unresolved object further comprises:

for a second subset of respective functions defined by the unresolved objects that do not have corresponding machine code stored in a code database;

generating code in a second programming language corresponding to a compiled version of the respective function,

generating byte code corresponding to a compiled version of the code in the second programming language, and

obtaining the corresponding machine code for the respective function based on the byte code; and

aggregating the corresponding machine code for each of the second subset of respective functions defined by the unresolved objects, thereby forming the machine code for each respective unresolved object.

19. The medium of claim 12, further comprising storing the machine code corresponding to the compiled version of the skeletal query in a code database.

20. The medium of claim 12, wherein the code database is stored in at least one of a local memory, a local storage, and a server cache.

21. The medium of claim 12, wherein the skeletal query comprises one of:

a stored procedure, a user-defined function, a table-valued function and a user-defined aggregate function and the first programming language is a structured query language.

22. A system, comprising:

a processing system configured to;

obtain a user query in a structured query language, the user query comprising at least one query parameter for selecting data from a content database;

parse the user query to identify a skeletal query form of the user query in a first programming language and the at least one query parameter, wherein the skeletal query comprising at least one unresolved object and a function calling the at least one unresolved object;

generate byte code defining a plurality of functions corresponding to the user query,

interpret the byte code using the at least one query parameter, thereby returning a result of the user query satisfying the at least one query parameter,

generate machine code corresponding to the user query based on the byte code,

execute the machine code corresponding to the user query using the at least one query parameter if the user query is called again, thereby returning a result of the user query satisfying the at least one query parameter;

wherein the generate and execute machine code corresponding to the user query comprising;

on a first pass through the skeletal query;

bind each respective unresolved object to a respective placeholder object such that the function calls each respective placeholder object and each respective placeholder object is mapped to the respective unresolved object,

delay evaluation of the skeletal query until a second pass through the skeletal query;

on a second pass through the skeletal query;

obtain machine code corresponding to a compiled version of the skeletal query by;

sequentially obtaining machine code for each respective unresolved object as each respective placeholder is called in the skeletal query; and

execute the machine code corresponding to the compiled version of the skeletal query, thereby performing at least one operation on the data of the content database.

23. The system of claim 22, wherein:

the at least one unresolved object comprises a plurality of table variables;

a first table variable of the plurality of table variables is defined in terms of at least one of;

a second table variable of the plurality of table variables, or

a scalar variable;

the function references the first table variable, thereby calling a nested invocation of the first table variable and the second table variable or the scalar variable; and

sequentially obtaining machine code for each respective unresolved object comprises unnesting the nested invocation of the first table variable and the second table variable or the scalar variable such that the function refers only to defined tables or literal values.

24. The system of claim 23, wherein the scalar variable is bound to the function at the time the function is assigned to the first table variable such that any subsequent modification to the scalar variable is not utilized when sequentially obtaining machine code for each respective unresolved object.

25. The system of claim 22, wherein obtaining the machine code for each respective unresolved object further comprises:

for a first subset of respective functions defined by the unresolved objects having corresponding machine code stored in a code database;

retrieving the corresponding machine code for the respective function from the code database; and

aggregating the corresponding machine code for each of the first subset of respective functions defined by the unresolved objects, thereby forming the machine code for each respective unresolved object.

26. The system of claim 22, wherein obtaining the machine code for each respective unresolved object further comprises:

for a second subset of respective functions defined by the unresolved objects that do not have corresponding machine code stored in a code database;

generating code in a second programming language corresponding to a compiled version of the respective function,

generating byte code corresponding to a compiled version of the code in the second programming language, and

obtaining the corresponding machine code for the respective function based on the byte code; and

aggregating the corresponding machine code for each of the second subset of respective functions defined by the unresolved objects, thereby forming the machine code for each respective unresolved object.

27. The system of claim 26, wherein obtaining the machine code corresponding to the compiled version of the respective function based on the byte code comprises:

for a second subset of respective functions defined by the byte code having corresponding machine code stored in the code database, retrieving the corresponding machine code for the respective functions; and

aggregating the corresponding machine code for each respective function defined by the byte code.

28. The system of claim 26, further comprising storing the corresponding machine code for the respective function based on the byte code in the code database.

29. The system of claim 22, further comprising storing the machine code corresponding to the compiled version of the skeletal query in a code database.

30. The system of claim 22, wherein the code database is stored in at least one of a local memory, a local storage, and a server cache.

31. The system of claim 22, wherein the skeletal query comprises one of:

a stored procedure, a user-defined function, a table-valued function and a user-defined aggregate function and the first programming language comprises a structured query language.