Stored Procedures

[Guy Riddle]

"Your Database is Your Prison" also extols stored procedures:

Start with stored procedures. “Ask yourself: ‘How bad would it be if a hacker rooted my webserver?’ If the answer is ‘real bad’, then move your authentication logic to a stored procedure inside the database. It’s more secure, maintains better layering, and higher performance for the end user.”

So I tried a FUNCTION I used in a database:

$ mysql -h 127.0.0.1 bedrock
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: bedrock 6f25a8097802c2d2d7a4b45e7f6a312bcccfb952

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [bedrock]> DELIMITER |
MySQL [bedrock]>  CREATE FUNCTION GeoDistKM( lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT ) RETURNS float
    ->   BEGIN
    ->   DECLARE pi, q1, q2, q3 FLOAT;
    ->   DECLARE rads FLOAT DEFAULT 0;
    ->   SET pi = PI();
    ->   SET lat1 = lat1 * pi / 180;
    ->   SET lon1 = lon1 * pi / 180;
    ->   SET lat2 = lat2 * pi / 180;
    ->   SET lon2 = lon2 * pi / 180;
    ->   SET q1 = COS(lon1-lon2);
    ->   SET q2 = COS(lat1-lat2);
    ->   SET q3 = COS(lat1+lat2);
    ->   SET rads = ACOS( 0.5*((1.0+q1)*q2 - (1.0-q1)*q3) );
    ->   RETURN 6378.388 * rads;
    ->  END;
    -> |
ERROR 502 (HY000): near "FUNCTION": syntax error
MySQL [bedrock]> DELIMITER ;
MySQL [bedrock]>

No?  How do you get them into your database where you get all that "secure high performance"?

[David Barrett]

Sorry for the confusion here.  In Bedrock, stored procedures are written in C++, and deployed along with the server as plugins.  Please see https://github.com/Expensify/Bedrock/tree/master/plugins for the plugins available "out of the box".  Thanks for asking!

--
You received this message because you are subscribed to the Google Groups "Bedrock" group.
To unsubscribe from this group and stop receiving emails from it, send an email to bedrock+unsubscribe@googlegroups.com.
To post to this group, send email to bed...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/bedrock/52e30a0a-2446-4273-9c9f-b3caa0b78338%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Guy Riddle]

I did figure it out, and added one (see my second pull request).  The files in the "plugins" directory are not built as separate .so plugins.  The one I added, called udf.so is built from udfplug.cpp and udf.c, and satisfies my need to add a MySQL-like stored procedure.

There is mimimal BedrockPlugin class mechanism, just enough for me to get sqlite3_create_function called with the other required bits.