Home } Standard SQL } Convert SQL Stored Procedures to Lua Functions

Convert C To Lua
Lua C++ Api
Translating C To Lua Word
Translating C To Lua English
Lua2cpp

The dateparse.lua module contains a fuzzy date/time parser which parses just about any date. The dateparse.parse function is pretty smart, but it can’t predict everything, to address this we allow you to create custom date formats. How to use dateparse.lua: Add it to your shared modules in any Translator. There are ways to translate (mod note: non-obfuscated) MTA: SA.luac files back to standard.lua code, and the answer is lying on the Internet with only doing a quick search on Google and in these forums. However, you need to understand very well how does MTA load a compiled script, what features does Lua offer and how to make it work in a way.

I have written code that converts SQL stored procedures to Lua functions. This might interest people who want to know what's involved for any C-like target language. Here I will display one step at a time, emphasizing the 'Design Decisions' that I had to make, giving increasingly complex examples. Then I'll note the limitations and say how to get the source code. Alpha.

Frame

The word LUA is just a signal that this is a translation job; everything following it is expected to be standard SQL or a supported dialect.

I already had a good
recognizer so parsing the input is easy. And, so far, it's easy to find Lua syntax analogous to SQL syntax. The result is:

Design Decision #1: I'll try to preserve comments and punctuation as in the original, although it's not necessary.

Design Decision #2: SQL's BEGIN .. END can become Lua's do .. end
The alternative was Lua's while true .. break; end but that could have confused some people.

Variables

DECLARE + variable-name is easy because Lua declarations only need a name. Occasionally we have to do some extra processing though.

Result:

Design Decision #3: I convert regular identifiers to upper case because that's how SQL does things. This isn't common practice in Lua.

Design Decision #4: I change delimited (quoted) identifiers by changing all special characters to _s but keeping the original case, and then we add a number to ensure the name is unique. Detail: _33 means 'the definition was at token number 33 in the CREATE PROCEDURE statement; I use token numbers frequently to make sure I'm making something unique.

Design Decision #5: I don't preserve the data type. Lua doesn't need it often and when it does I can use Lua's type() function to see whether somethingis a number or a string .. except if the value is nil. Nil values will confuse us and users should try to avoid them. The alternative, though, was to store the data type and null status separately for every declared variable, and I think (well, 'hope') that won't be necessary in practice.

Executing

Now we get into SQL statements that must be called from Lua. For this we will use a function named sql_execute(), which is a wrapper around code that is specific to a particular DBMS. The job of sql_execute is to:
Pass parameters.
Send a statement to the DBMS server (for example with mysql_real_query if the DBMS is MySQL/MariaDB).
NB: sending should be done with Lua pcall because results must always be intercepted.
Check return values:
(If there was an error) set sqlstate to error, for example '45000', and return false.
(If there was a result set)
Copy the result set to sqlresult.
(If the result set is empty) set sqlstate to not found, for example '02000', and return nil.
(If the result set is not empty) set sqlstate to okay, for example '00000', and return true.
(If there was no result set) set sqlstate to okay, for example '00000', and return true.
The job of the function calling sql_execute is to:
Define sqlstate (a string) and define sqlresult (a table) accessible throughout the function.
Call sql_execute with whatever parameters are necessary.
Check what sql_execute returns, if necessary.

Result:

For the DEFAULT 1 clause, the function will call on the DBMS to execute 'SELECT 1;', and the result will be in the first column of the first row of sqlresult table, so I = select-result. There are no parameters in this case, which is why ,{} appears.

For the RETURN i + 3 statement, the function will call on the DBMS to execute 'SELECT ? + 3;', and since the ? is a placeholder for a parameter there will have to be a parameter, which is why ,{I} appears. Once again sql_execute() will put the result set, which happens to be one row with one column, in sqlresult. So return sqlresult[1][1] will cause the function to return 4.

Design Decision #6: all expressions will go to SQL for evaluation. Certainly for the example here it would be possible to do the addition in Lua, but when expressions contain SQL functions or operators we have to get SQL to do it. So, for simplicity, I always ask SQL to do it.

Design Decision #7: I'll assume that sql_execute can't fail for statements that are assigning, like DEFAULT and RETURN. This isn't necessarily true, but I figured: if there was something wrong with the expression, it would have failed already.

Getting Out of a block

Adding statements in a BEGIN .. END causes the usual sql_execute() calls, but also some goto instructions.

Result:

The instruction

is my substitute for SQL's implicit action
'Get out of the BEGIN .. END block if there is an error'.
Remember that sql_execute() returns an sqlstate value, and the first two characters of this value must be greater than '02' if there is an error. So the goto will go to a point just past the end of the inner Lua do .. end block, which is marked by a label ::end_7::. This isn't necessary for INSERT INTO t VALUES ('b'); because it is the last thing in the block, so it will pass out of the block anyway.

Design Decision #8: goto is the best way to get out of the block. The alternatives were: (a) use while true .. end so that break will get out of the block, as discussed earlier, (b) put 'if string.sub(sqlstate,1,2) <= '02' before every sql_execute() call in the block. Although the alternatives would work in this example, I decided they make the code unreadable when the examples get complicated.

Unconditional Flow control

I can replace SQL's

with Lua's

. I can replace SQL's ITERATE with a goto that goes to just before the end, so that the loop repeats. I can replace SQL's LEAVE with a goto that goes to just after the end, so that the loop ends.

Result:

Since the ITERATE-derived 'goto LABEL_1_1;' skips past the LEAVE-derived 'goto LABEL_1_2;' this is an infinite loop. Its only saving grace is that it is valid Lua code.

Conditional Flow Control

WHILE, IF, and REPEAT are statements that depend on a condition. I support them all the same way: by passing 'SELECT condition;' to SQL, and then (since such a statement will always return one row with a Boolean value) asking whether the first column in the first row of sqlresult is true.

Result:

We've already seen what the rest of the statements in this loop are supposed to generate, the only new thing is that 'SELECT ? < 5;' will return true (because the passed parameter I is less than 5) until I becomes 5 (because 'SELECT ? + 1;' is inside the loop).

Design Decision #9: I decided to test first if sql_execute() returns true (that is, does not return an error or not found) before checking whether the value in the result set is true. That's reasonable caution -- but I didn't decide to do a similar test for assignment. That's because a failure during condition evaluation could cause an infinite loop, so it is more serious.

Design Decision #10: I'm saying 'SELECT ? < 5;' although I dislike non-standard code -- standard code is 'VALUES (? < 5);'. This is my concession to what seems to be popular, but I am already regretting it, I probably will change this.

Cursors

Remember that sql_execute() will put the results of a SELECT into a table named sqlresult. So all I need to do is call sql_execute('SELECT 5;') for the OPEN, pick up a value from sqlresult for the FETCH statement, and rub out sqlresult for the CLOSE statement.

Result:

Here C_CURSOR is a Lua table with a copy of resultset, and C_CURSOR_OFFSET is something that FETCH can increment whenever it succeeds. FETCH will make its own decision about whether sqlstate = '02000' (which is the SQLSTATE value for NOT FOUND) by checking whether C_CURSO

Translating C To Lua