MySQL extension

For the MySQL extension, I now have a basic set of functions implemented. Here is a list of all functions and the status. I would like to know if the functions I still plan to implement are worth the trouble or can be skipped, and if any of the ones I think should be ignored are on the must-haves list of someone.

mysql_affected_rows()            mysql-affected-rows            Basic functions
mysql_autocommit()               mysql-autocommit               ?
mysql_change_user()              mysql-change-user              ?
mysql_character_set_name()       mysql-character-set-name       Done
mysql_close()                    mysql-close                    Basic functions
mysql_commit()                   NO                             Perform query "commit;"
mysql_connect()                  NO                             Deprecated function
mysql_create_db()                NO                             Deprecated function
mysql_data_seek()                mysql-data-seek                Done
mysql_debug()                    NO                             Needs super rights.
mysql_drop_db()                  NO                             Deprecated function
mysql_dump_debug_info()          NO                             Needs super rights
mysql_eof()                      NO                             Deprecated function
mysql_errno()                    mysql-errno                    Basic functions
mysql_error()                    mysql-error                    Basic functions
mysql_escape_string()            NO                             Depricated (Use a wrapper function)
mysql_fetch_field()              mysql-fetch-field              Done
mysql_fetch_field_direct()       mysql-fetch-field-direct       Done
mysql_fetch_fields()             mysql-fetch-fields             Done
mysql_fetch_lengths()            mysql-fetch-lengths            Done
mysql_fetch_row()                mysql-fetch-row                Basic functions
mysql_field_count()              mysql-field-count              Basic functions
mysql_field_seek()               mysql-field-seek               Done
mysql_field_tell()               mysql-field-tell               Done
mysql_free_result()              mysql-free-result              Basic functions
mysql_get_character_set_info()   mysql-get-character-set-info   Done
mysql_get_client_info()          mysql-get-client-info          Basic functions
mysql_get_client_version()       mysql-get-client-version       Done
mysql_get_host_info()            mysql-get-host-info            Basic functions
mysql_get_option()               NO                             Not available in older versions pre 2012
mysql_get_proto_info()           mysql-get-proto-info           Done
mysql_get_server_info()          mysql-get-server-info          Basic functions
mysql_get_server_version()       mysql-get-server-version       Done
mysql_get_ssl_cipher()           mysql-get-ssl-cipher           ?
mysql_hex_string()               mysql-hex-string               ?
mysql_info()                     mysql-info                     Done
mysql_init()                     NO                             Not in MySQL 8.0
mysql_insert_id()                mysql-insert-id                Done
mysql_kill()                     mysql-kill                     For Threads?
mysql_library_end()              mysql-library-end              For Threads?
mysql_library_init()             mysql-library-init             For Threads?
mysql_list_dbs()                 NO    
mysql_list_fields()              NO    
mysql_list_processes()           mysql-list-processes           For Threads?
mysql_list_tables()              NO    
mysql_more_results()             mysql-more-results             Done
mysql_next_result()              mysql-next-result              Done
mysql_num_fields()               mysql-num-fields               Done
mysql_num_rows()                 mysql-num-rows                 Basic functions
mysql_options()                  mysql-options                  ?
mysql_options4()                 mysql-options4                 ?
mysql_ping()                     mysql-ping                     Done
mysql_query()                    mysql-query                    Basic functions
mysql_real_connect()             mysql-connect                  Basic functions
mysql_real_connect_dns_srv()     NO    
mysql_real_escape_string()       NO                             Use a wrapper function
mysql_real_escape_string_quote() NO                             Use a wrapper function
mysql_real_query()               NO    
mysql_refresh()                  NO                             Needs RELOAD privilege
mysql_reload()                   NO                             Needs RELOAD privilege
mysql_reset_connection()         NO                             Not available in older versions pre 2012
mysql_reset_server_public_key()  NO    
mysql_result_metadata()          NO    
mysql_rollback()                 NO                             Perform query "rollback;"
mysql_row_seek()                 mysql-row-seek                 Done
mysql_row_tell()                 mysql-row-tell                 Done
mysql_select_db()                NO    
mysql_server_end()               NO    
mysql_server_init()              NO    
mysql_session_track_get_first()  NO    
mysql_session_track_get_next()   NO    
mysql_set_character_set()        mysql-set-character-set        Done
mysql_set_local_infile_default() NO    
mysql_set_local_infile_handler() NO    
mysql_set_server_option()        NO    
mysql_shutdown()                 NO    
mysql_sqlstate()                 mysql-sqlstate                 Done
mysql_ssl_set()                  mysql-ssl-set                  ?
mysql_stat()                     mysql-stat                     Done
mysql_store_result()             mysql-store-result             Basic functions
mysql_thread_end()               mysql-thread-end               For Threads?
mysql_thread_id()                mysql-thread-id                For Threads?
mysql_thread_init()              mysql-thread-init              For Threads?
mysql_thread_safe()              mysql-thread-safe              For Threads?
mysql_use_result()               mysql-use-result               Done
mysql_warning_count()            mysql-warning-count            Done

TBD: To Be Done, planning to add this in the next version
Done: Added
?: Not sure if needed and if so why is it needed as a separate native?
NO: I do not see the need for this, especially the list functions that show items based on Regular Expressions.

If you're building a "real" interface, then it would be important for you to expose SQL escaping in some way. It's dangerous to make your API just based on "query string in, result string out"...it's too easy for data being spliced into the string to be interpreted as code. This is why systems like ODBC are parameterized in ways that have the code part indicate the slots where substitutions will be done, and make sure those slots can never be run as code.

The natives you are writing need to be parameterized somehow in a way that separates the code and the data. Something like:

table: "widgets"
quantity: 10
mysql-query "select data from ? where x = ?" [table quantity]  ; "good"

mysql-query spaced ["select data from" table "where x =" quantity]  ; "bad"

You're not using the mysql_escape_xxx or mysql_real_escape_xxx functions. So I'm not sure where you're at with this separation. Be sure to read up on this topic and why it is important.


Bigger picture: realistically speaking, it will be a long-time-if-ever before anyone uses the MySQL extension besides you. So being able to do what you want, and learn what you want, should be your main goal.

If you are serious enough to want to think about what other people might care about, that will require moving to a focus on setting up reproducible tests. Since you're comfortable with VMs now, you should probably be able to get started with a Travis project that installs MariaDB, makes a fresh database, and interacts with it. The ODBC tests show setting up MySQL databases on the command line in a script, and you'd want to write some similar lines.

You'd start by making a .travis.yml for your extension. There'll be options for installing MariaDB with something analogous to sudo apt install...but you'll have to ask Travis to wget or curl a mysql-enabled-interpreter from your website that you've built each time it runs. (The goal is to move all the existing extensions out of the main repository, so I don't want to complicate the core build any more with another extension. We'll have to cross the bridge of making the executables online when we get to it.)

As for what the test does...that's up to you. The ODBC test creates tables, inserts values, and makes sure it gets the same thing back. That's a fairly basic idea for a test which helps show general functioning. But you can make it more specific to your use case if you want.


All that said: I'm busy devoting most of my time right now to studying other languages to try and revisit longstanding design holes in Redbols. All under the lens of a full stackless coversion. That's going to be the situation for the foreseeable future, and the Wasm build remains the main target of interest.

But I'll try to help unblock you if you keep with it. Like I said, it seems like you are actually sticking with something for a while, so congratulations on that. I would imagine you're learning some things in this process. Maybe you'll find a new calling in your career because of it?

2 Likes

Thank you for your great answer again!

I added the legend for describing the meaning of the remarks.

The escape functions are marked with a question mark and I know about escaping and its importance. I was still wondering if I should make them also a native or use them inside other functions so you do not have to worry about escaping, or maybe you should take care of the escaping using Rebol functions before submitting to the MySQL functions. (Like PHP uses addslashes and stripslashes).

In a small community like ours at the moment it will be indeed the question if anybody else will use this feature. On the other hand, providing this will give some daring souls the opportunity to try this out for themselves, and even get them interested in the rest of the endeavor.

I have updated the list of implemented functions. Unfortunately mysql_get_option and mysql_reset_connection were not present in the mysql.h header file for the CentOS 7 operating system, as the headerfile that is available on that system is pre 2013 and no later version is provided. Even updating my local VM is not an option as I do not update the hosting server by that action. I had a lot of coding done before I found out. Let's say that is some free and helpful programming experience in C for me.

I will leave the mysql with this functionality if no urgent needs are posted here.

I'll have a go at a Travis - CI automated test, but I think it is best left to have testscripts that can be run on a real server. Would very much to have it included in the renc repo.

We definitively need a new build process that has a default to not include extensions unless the build opts in.

1 Like

Is your web host running a pre-2013 distribution of an OS? :-/

Well, we do like to have things tested on a wide set of platforms...and within reason, old solid ones help with dependency control.

People apparently have used Docker to run CentOS 7 on Travis. So that would be neat to see used for both the build and the tests.

I have changed the post, I think that is just a part that is not provided for by the CentOS maintainers for any (good?) reason. I believe the hosting is taking care of keeping up to date on security updates, ah and yes they are using MariaDB which may not have that problem. I wanted to stick to MySQL but I'll see if there is a different option, creating another VM soon. (NOTE_1)

Perhaps I could make an compiler directive MYSQL_PRE_2013 or alike that will not include some functions.

I had a look at docker and CentOS 7 when I had some trouble with the VM, but it was a lot of information coming towards me and I did not see how to make it work for me. Free time is even today a scarce commodity.

If I understand the article correct, Travis-CI is using Ubuntu to test stuff on, therefor they use Docker to get an instance of CentOS (an EL type OS, RHEL stands for Red Hat Enterprise Linux(?)) running and execute their testscripts on that.
Maybe a volunteer who already knows Docker, can assist here.

(NOTE_1) Created a new VM and installed MariaDB and the -devel package. The mysql.h file still was the old "Copyright (c) 2000, 2012, Oracle and/or its affiliates" version. And "mysql -V
mysql Ver 15.1 Distrib 5.5.65-MariaDB, for Linux (x86_64) using readline 5.1", I could not find a mariadb.h, they use mysql.h also(?).
So I stop investing more time into that path and decided to be satisfied with current functionality.