[Standalone] IcMySQL - Efficient MySQL abstraction

R-Scriptss · July 29, 2023, 9:46pm

Nice!

GenesisComunity · August 26, 2023, 12:17pm

nice JOb!

OrigenStore · August 26, 2023, 7:50pm

wooow!

KXDEX · December 23, 2023, 5:12pm

NEW MAJOR UPDATE (v3.0.0)

We have redone the whole system to solve major problems and we have taken the opportunity to improve and add support for MySQL, Mongo, Redis and ORM (object relational mapping), apart from all the new features listed in the main post.

The new version can be found uploaded in the repository.
Documentation: Here

OrigenStore · December 23, 2023, 5:14pm

Sound cool! I’ll test it

ErZurkyNene · December 23, 2023, 5:33pm

Love the work guys, keep it up!

Refurb · December 23, 2023, 9:42pm

I swapped from oxmysql to this and now my server runs 5 degrees cooler

Jaareet · December 23, 2023, 10:33pm

Great job, guys! I’m really pleased with what you’re achieving. Thanks for the resources. Let’s keep adding to the forum!

DanielGP · December 23, 2023, 10:38pm

Thank you very much

KXDEX · December 24, 2023, 3:06pm

thx

zRxnx · December 24, 2023, 3:18pm

@Linden will you review v3.0.0 also?

Baifern_Unlimited · December 24, 2023, 4:28pm

Thank you for taking the time to create this for me and others to use

KXDEX · December 24, 2023, 5:35pm

I am delighted to hear that you have found the system so useful. Our intention in making it accessible to everyone was precisely that: to offer help and contribute positively to the community.
Your support and kind words mean a lot to us! Please, if you have any problems or suggestions, feel free to share them here.

Linden · December 25, 2023, 6:32am

No clue what you’re doing to achieve such results.

local curtime = os.nanotime
local results = {}

---@param iterations number
---@param title string | number
---@param func function
local function runBenchmark(iterations, title, func)
    local start = curtime()

    for i = 1, iterations do
        func()
    end

    results[#results + 1] = { (curtime() - start) / iterations, title }
end

---@param iterations number
local function showResults(iterations)
    print(('Average results from %d iterations (ms)'):format(iterations))
    table.sort(results, function(a, b) return a[1] < b[1] end)

    for i = 1, #results do
        local result = results[i]
        print(('#%d - %.4f\t(%s)'):format(i, result[1] / 1e6, result[2]))
    end

    table.wipe(results)
end

---@param n number
---@param tbl table<string, function>
local function benchmark(n, tbl)
    for k, v in pairs(tbl) do
        runBenchmark(n, k, v)
    end

    showResults(n)
end


CreateThread(function()
    local initTable = {
        'DROP TABLE IF EXISTS `test_table`',
        [[CREATE TABLE `test_table` (
        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        `username` VARCHAR(50) NOT NULL DEFAULT '0',
        `identifier` VARCHAR(50) NOT NULL DEFAULT '0',
        PRIMARY KEY (`id`)
        )]],
        'TRUNCATE `test_table`',
        'ALTER TABLE `test_table` AUTO_INCREMENT = 1',
    }

    MySQL.transaction.await(initTable)

    local insertUsers = {}

    for i = 1, 5000 do
        insertUsers[i] = { 'INSERT INTO `test_table` (username, identifier) VALUES (?, ?)',
            { 'Testuser_' .. i, 'abcdef' .. i } }
    end

    MySQL.transaction.await(insertUsers)

    local Query = exports.icmysql.AwaitQuery

    benchmark(1000, {
        ['ox'] = function()
            MySQL.query.await('SELECT username FROM test_table WHERE id = ?', { 3 })
        end,
        ['ice'] = function()
            Query(nil, 1, 'SELECT username FROM test_table WHERE id = ?', { 3 })
        end,
    })
end)

[  resources] Started resource test
[script:test] Average results from 1000 iterations (ms)
[script:test] #1 - 0.4476      (ice)
[script:test] #2 - 0.4579      (ox)
     ensure test
[  resources] Stopping resource test
[    c-scripting-core] Creating script environments for test
[  resources] Started resource test
[script:test] Average results from 1000 iterations (ms)
[script:test] #1 - 0.3466      (ox)
[script:test] #2 - 0.4444      (ice)
     ensure test
[  resources] Stopping resource test
[    c-scripting-core] Creating script environments for test
[  resources] Started resource test
[script:test] Average results from 1000 iterations (ms)
[script:test] #1 - 0.3301      (ox)
[script:test] #2 - 0.3763      (ice)
     ensure test
[  resources] Stopping resource test
[    c-scripting-core] Creating script environments for test
[  resources] Started resource test
[script:test] Average results from 1000 iterations (ms)
[script:test] #1 - 0.3547      (ice)
[script:test] #2 - 0.3758      (ox)

Balbo404 · December 25, 2023, 6:44am

I will redo the tests and post the results here.

Linden · December 25, 2023, 6:47am

You’re not even going to achieve anything more than micro-optimisations when both scripts

use the same mysql driver
use promises
use connection pools

The resources don’t run any complex / heavy code by themselves; you instead need to be looking at usability and features… and also compatibility or things like sparse arrays, nil parameters, supported syntax.

Balbo404 · December 25, 2023, 6:58am

I really had done something wrong and the results are basically the same

Average results from 1000 iterations (ms)
#1 - 0.2392      (ox)
#2 - 0.2755      (ice)

Average results from 1000 iterations (ms)
#1 - 0.2537      (ice)
#2 - 0.2989      (ox)

Average results from 1000 iterations (ms)
#1 - 0.2494      (ice)
#2 - 0.3204      (ox)

Average results from 1000 iterations (ms)
#1 - 0.2341      (ox)
#2 - 0.2530      (ice)

DanielGP · December 25, 2023, 5:45pm

I have done an update that improves the performance a lot and exceeds the performance of ox, I have done the benchmark that Linden has provided and here I will show the results that I have obtained after the performance improvement:

With 1.000 Iterations:

With 10.000 Iterations

Note: If someone wants to test the benchmark he/she should remove the provide oxmysql from the icmysql/fxmanifest.lua and set the Config.ReplaceExports option in the icmysql/config.js file to false. This is to avoid problems when starting/restarting the script.

And I’ve added the nil params support for the query values

Zerio · December 25, 2023, 9:04pm

great progress on this

just wanted to say though,

github.com

IceClusters/icmysql/blob/main/.yarn.installed

This document is essential to avoid the automatic construction of the troublesome yarn resource.

this file does nothing (most of the time)

as yarn doesnt just check if the file exists, it checks when it was last modified and compares it to the package json’s last modification date

github.com

citizenfx/cfx-server-data/blob/master/resources/[system]/[builders]/yarn/yarn_builder.js#L25


      
          			const resourcePath = GetResourcePath(resourceName);
          			
          			const packageJson = path.resolve(resourcePath, 'package.json');
          			const yarnLock = path.resolve(resourcePath, '.yarn.installed');
          			
          			const packageStat = fs.statSync(packageJson);
          			
          			try {
          				const yarnStat = fs.statSync(yarnLock);
          				
          				if (packageStat.mtimeMs > yarnStat.mtimeMs) {
          					return true;
          				}
          			} catch (e) {
          				// no yarn.installed, but package.json - install time!
          				return true;
          			}
          		} catch (e) {
          			
          		}

the real way to fix this would be to just not include the package.json & lock files in the released files.
hope this helps (i had to deal with similar issues in the past lol)

Beauflexx · December 25, 2023, 9:08pm

Sorry for my noob question, but is this like a oxmysql replacement?
Seeing some cool features here, just slightly curious upon everything and the rest.
It handling mysql/oxmysql calls? like backwards combatable