Skynet creating a Stored procedure script

Recently the main process changed the operation of the database, from the previous spelling SQL script to the MySQL side to create a good stored procedure, directly call the stored procedure.

First, test the above procedure on a table:

Database-Side Stored procedures: (Test table)

CREATE TABLE ' Randomval ' (
' id ' int (ten) unsigned not NULL,
' Val ' int (ten) unsigned not NULL,
' Step ' int (ten) unsigned not NULL,
PRIMARY KEY (' id ')
) Engine=innodb DEFAULT Charset=utf8;

To create a stored procedure:

DELIMITER $$

CREATE definer= ' root ' @ '% ' PROCEDURE ' qy_insert_randomval ' (in in_id int, in In_val int, in In_step int)
BEGIN
Insert INTO randomval (' id ', ' Val ', ' Step ') VALUES (in_id, In_val, In_step)
On duplicate key update ' id ' = in_id, ' val ' = in_val, ' step ' = In_step;
END $$

DELIMITER;

Skynet connection: Changing the Testmysql.lua under test

Skynet.start (function ()
Local function on_connect (db)
Db:query ("Set CharSet UTF8");
End
Local Db=mysql.connect ({
Host= "127.0.0.1" or "LAN IP",
port=3306,
Database= "XXXX",
User= "XXXX",
Password= "XXXX",
Max_packet_size = 1024 * 1024,
On_connect = On_connect
})

If not DB then
Print ("Failed to connect")
End
--Stitching SQL statements that call stored procedures
Print ("Testmysql success to connect to MySQL server")

　 --table T format ignored, and project-related, customizable
Local T = {__fields = {id = $, val = +, step = #, __tname = "Randomval", __head_ord = {"id", "Val", "Step"}}
Local tmp_sql = {}
Local Sql_first_part = String.Format ("Call" ... " Qy_insert_ ". T.__tname. "(" )
Table.insert (Tmp_sql, Sql_first_part)

ASSERT (nil ~= T.__head_ord)
Local counter = 0
For K, V in Ipairs (T.__head_ord) do
Print (k, v)
ASSERT (Nil ~= t.__fields[v])
If counter > 0 Then
Table.insert (Tmp_sql, ",")
Else
Counter = counter + 1
End

If Type (t.__fields[v]) = = "string" Then
Table.insert (Tmp_sql, String.Format ("'%s '", T.__fields[v]))
Else
Table.insert (Tmp_sql, String.Format ("%s", T.__fields[v]))
End
End

Table.insert (Tmp_sql, ")")
Print ("SQL is:", Table.concat (Tmp_sql))
Db:query (Table.concat (Tmp_sql))

--db:disconnect ()
--skynet.exit ()
End

At this point, the stored procedure is created, run the Testmysql script:./skynet./examples/config.mysql

The stored procedure is successfully called and a record is inserted.

Because the main process requires each table to have a "insert unsuccessful update stored procedures", although the use of navicat, but the current understanding of it can only be created one by one, very troublesome;

Either execute a SQL script that creates all the procedures, and execute the creation with Navicat. He chose the latter.

To extract the commonalities first, you need the following:

(1) Unified format named "Qy_insert_" + "table name"

(2) Gets the table name that needs to create the stored procedure, each field name of the table, the field type (Table_type), and the value type of the field (Data_type)

Here is the implementation:

Local addr = Io.open ("./user_sqlscript.sql", "W")
ASSERT (Addr)
Local sql = "SELECT table_name from INFORMATION_SCHEMA. TABLES where Table_schema = "..." ".. "Database name": All tables in the database are selected here. Add the following table that is required for filtering: "and table_name not like". "‘" .. " g_% ". "‘";

Print (SQL)
Local table_list = db:query (SQL)
--[[

DELIMITER $$

CREATE definer= ' root ' @ '% ' PROCEDURE ' qy_insert_randomval ' (in in_id int, in In_val int, in In_step int)
BEGIN
Insert INTO randomval (' id ', ' Val ', ' Step ') VALUES (in_id, In_val, In_step)
On duplicate key update ' id ' = in_id, ' val ' = in_val, ' step ' = In_step;
END $$

DELIMITER;

　　Take this stored procedure as an example

--]]
For K, V in Ipairs (table_list) do
Local script = {}
Local TMPSQL1 = {}--store ' qy_insert_randomval ' (in in_id int, in In_val int, in In_step int) "In in_id int, in In_val int , in In_step int "
Local TMPSQL2 = {}--store insert INTO randomval (' id ', ' Val ', ' Step ') in "' Id ', ' Val ', ' Step '"
Local TMPSQL3 = {}--store "in_id, In_val, In_step" in Values (in_id, In_val, In_step)
Local TMPSQL4 = {}--store on duplicate key update ' id ' = in_id, ' val ' = in_val, ' step ' = In_step; In the "' id ' = in_id, ' val ' = in_val, ' Step ' = In_step"

Local sql = String.Format ("Select column_name, Data_type, Column_type from Information_schema. ' COLUMNS ' where table_name = ".. '" ".. '%s ': ' "; ', V.table_name)

--column_name is the column name, Column_type is the type of the column (Varchar,int (6) unsigned), and so on, Data_type is the value type (int, varchar) of the column, etc.
Print (SQL)
Local col_val = db:query (SQL)
ASSERT (Col_val)
For K, V in Ipairs (col_val) do
For SK, SV in pairs (v) do
If SV = = "int" Then
Print ("string is")
End
End
End

--format each percudure part
Local idx = 0
For SK, SV in Ipairs (col_val) do
If idx > 0 Then
Table.insert (TMPSQL1, ",")
Table.insert (TMPSQL2, ",")
Table.insert (Tmpsql3, ",")
Table.insert (Tmpsql4, ",")
Else
IDX = 1
End

If Sv. Data_type = = "varchar" Then
Table.insert (TMPSQL1, String.Format ("in%s%s", "In_": Sv.) COLUMN_NAME, SV. Column_type))
Else
Table.insert (TMPSQL1, String.Format ("in%s%s", "In_": Sv.) COLUMN_NAME, SV. Data_type))
End

Table.insert (TMPSQL2, String.Format ("'%s '", SV. COLUMN_NAME))
Table.insert (Tmpsql3, String.Format ("in_%s", sv. COLUMN_NAME))
Table.insert (Tmpsql4, String.Format ("'%s ' = in_%s", sv. COLUMN_NAME, SV. COLUMN_NAME))
End

Table.insert (TMPSQL1, ") \ n")
Table.insert (TMPSQL2, ")")
Table.insert (Tmpsql3, ") \ n")
Table.insert (Tmpsql4, "; \ n")

--chain all Tmpsqlx up
Table.insert (script, "DELIMITER $$\n")
Table.insert (script, String.Format ("CREATE definer= ' root ' @ ' percent ' PROCEDURE '%s ' (", "Qy_insert_"): V.table_name))
Table.insert (script, Table.concat (TMPSQL1))
Table.insert (script, "BEGIN \ n")
Table.insert (script, String.Format ("insert into%s (%s values (%s", V.table_name, Table.concat (TMPSQL2), Table.concat ( TMPSQL3)))
Table.insert (script, String.Format ("On duplicate key update%s", Table.concat (TMPSQL4)))
Table.insert (script, "end$$ \ n")
Table.insert (script, "DELIMITER;")
Local percudure = table.concat (script)
Print (percudure)

Addr:write (Percudure)
Addr:write ("\ n")
End
Addr:close ()--open file Don't forget to close

The above code implements a query from the database of all the tables and their column names, the types of columns, and then assembled into each table required by the stored procedures.

The tool used earlier is navicate when you create a stored procedure with a tool: function->procedure-> input parameter-> directly in the middle of the begin,end write process.

And when created with this method, the resulting stored procedure Code is not the following content

DELIMITER

．．．

．．．

END $$

DELIMITER;

Therefore, at first unaware of this problem, I directly generated the SQL file with navicate generation stored procedure is problematic, always say that there is a problem with the stored procedure, but the comparison with Navicat directly generated code, is identical. therefore very depressed.

Search online to find the content to add the above, and then build the success.

The reasons described in this article are quite detailed:

Http://blog.sina.com.cn/s/blog_4c197d420101d3oa.html

------------------------------Split Line------------------------------

Skynet the golden mean stored procedure can be used when querying the database and accepting the return value:

Db:query ("Call stored procedure name (@a,@b, @c)")

Local R = Db:query ("Select @a, @b, @c")

Note: The outgoing variable is @a,@b,@c, there is no space between the tangent @a

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

Because the database base is not very good, have not found a good way to implement the call stored procedure return data set operation, I hope you enlighten!

Added later.

Skynet creating a Stored procedure script