Threaded Mode | Linear Mode

n1ghtwish · (02-02-2018)

This is a PHP script that iterates through all your NPC script files, finds all the chardefs and exports pertinent data into a "chardefs" MySQL table. Essentially, it does the same thing as the "f_add_rebuild" found in the default SQLite .add menu written by Xun and Ben, except rather than running the script inside Sphere, this just reads straight from the script files. I have modified Ben and XuN's .add script to read from the MySQL table that this script outputs to, therefore eliminating the need to run "f_add_rebuild" every time you add or remove a char script. I will add a quick write up on how to do that in the second post.

UPDATES:
2/01/2018:
- Expression searches are now case INsensitive ( CHARDEF = chardef )

One benefit to this script is that it will also export stuff under the @Create trigger, properties that are only set when the character is actually spawned or created... so if you want to have some sort of monster compendium or bestiary on your website, you'd probably want to display the damage, strength, dexterity and intelligence ranges and whatnot. It will search your icons script file to match the ICON property with the actual hexidecimal ID # for that icon item in the MUL files. It will also truncate comments and eliminate white spaces from all variables before inserting them into the table.

Example of the data put to work:
http://www.gargoylepoop.com/bestiary

For this to work, you will either need to have a web server with PHP 5x or 7x and copy your NPC script files to that -OR- you can do it how I am and install PHP on your Sphere machine (Windows for me) and run the script using a batch file. You then simply double click the batch file and watch the magic happen. You'll also need to make sure MySQLi extension is enabled (it seems Windows' PHP does not have it enabled by default). Of course, you also need a MySQL server somewhere. You can't use mine Big Grin

How I have mine setup:
C:\Sphere\php\EXPORT.bat
C:\Sphere\php\chardef_export.php

Batch file (adjust paths accordingly)

Code:

@echo off

C:\php\php.exe C:\Sphere\php\chardef_export.php

echo Press any key to close...

pause >nul

MySQL table structure:

PHP Code:

CREATE TABLE `chardefs` (
 `idx` int(11) NOT NULL AUTO_INCREMENT,
 `id` varchar(30) DEFAULT NULL,
 `baseid` text,
 `defname` text,
 `icon` varchar(10) DEFAULT NULL,
 `bstat_str` varchar(30) DEFAULT NULL,
 `bstat_dex` varchar(30) DEFAULT NULL,
 `bstat_int` varchar(30) DEFAULT NULL,
 `bstat_maxhits` varchar(30) DEFAULT NULL,
 `damage` varchar(13) DEFAULT NULL,
 `armor` varchar(20) DEFAULT NULL,
 `barding_diff` varchar(6) DEFAULT NULL,
 `fame` varchar(20) DEFAULT NULL,
 `karma` varchar(20) DEFAULT NULL,
 `color` varchar(20) NOT NULL DEFAULT '0',
 `resources` text,
 `name` text,
 `category` text,
 `subsection` text,
 `description` text,
 PRIMARY KEY (`idx`),
 UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf 

A screenshot of the script output:

Your MySQL user will need all DATA privileges, along with DROP and ALTER.

In the PHP script, alter your settings as needed. You'll need to point the script to your NPC's folder (Sphere/scripts/npcs) and your pet icons file (Sphere/scripts/items/sphere_item_char_icons.scp). If you have NPCs spread out everywhere, you can move them all together or you can alter the script to point to each directory. Keep in mind, this script looks for the CHARDEF header in ALL .scp files in the directory, so if you have other files in the directory, it will still go through them but ignore everything unless it's a CHARDEF script.

The script will run several thousand MySQL queries. This should not be an issue if you have any half decent hardware. For me, with an 6th gen i5 and 8gb of RAM, the script takes 15-17 seconds.

With all that said, here ya go! Have fun!

The PHP script (chardef_export.php)

PHP Code:

<?php
/*
Last Edit: 2/01/2018
Written by n1ghtwish
*/

// ------ SETTINGS -------
// MySQL connection info
$INFO['sql_host']    =    "YOUR HOST HERE";
$INFO['sql_user']    =    "MYSQL USER HERE";
$INFO['sql_pass']    =    "MYSQL PASSWORD HERE";
$INFO['sql_db']        =    "DATABASE NAME";

// MySQL table to send data to
$chardefTable         = "chardefs";

// Path to NPC script directory
// Might have to move some files around -OR- modify the script
$scriptPath         = "C:/Sphere/scripts/npcs/";

// sphere_item_char_icons.scp (or whatever yours is called)
$sphereIconsFile     = "C:/Sphere/scripts/items/sphere_item_char_icons.scp";

// icon to use for chardefs without one
$noIcon             = "02100"; // wisp
// -------------------------------

$start = time();
echo "Connecting to MySQL database... \n";
// establish connection
$conn = new MySQLi($INFO['sql_host'], $INFO['sql_user'], $INFO['sql_pass'], $INFO['sql_db']) or die("Connection failed: " . $conn->connect_error);

echo "Using table '$chardefTable'... \n";
echo "Truncating table '$chardefTable'... \n\n";
$conn->query("TRUNCATE TABLE $chardefTable") or die($conn->error);

$total = 0;
$dir = new DirectoryIterator($scriptPath);
foreach ($dir as $file){
    if ($file->getExtension() == "scp") {
        echo "Scanning $file ... \n";
        $fullPath = $scriptPath . $file;
        $total += chardefExport($fullPath, $file, $conn);
    }
}

// find and set missing icons
$result = $conn->query("SELECT baseid FROM $chardefTable WHERE icon IS NULL") or die($conn->error);
$rows = $result->num_rows;
echo "\nFinding icons for $rows non-base chardefs...\n";

while ($i = $result->fetch_assoc()){
    $baseid = $i['baseid'];
    $icon = getMissingIcon($baseid, $conn);
    $conn->query("UPDATE $chardefTable SET icon = '$icon' WHERE baseid = '$baseid'") or die($conn->error);
}

// finally, add wisp icon to final rows without icon set
$result = $conn->query("UPDATE $chardefTable SET icon = '$noIcon' WHERE icon = '' OR icon IS NULL") or die($conn->error);
$affected = $conn->affected_rows;
echo "$affected chardefs left without icon property, setting to '$noIcon'. \n";

$end = time();
$elapsed = $end - $start;

// Yay, done!
echo "\nYay, done!  $total TOTAL chardefs exported in $elapsed seconds!\n";

function getMissingIcon($baseid, $conn){
    global $chardefTable;
    $result = $conn->query("SELECT icon FROM $chardefTable WHERE defname = '$baseid'") or die($conn->error);
    $i = $result->fetch_assoc();
    return $i['icon'];    
}

function findBaseHexIcon($icon_def){
    global $sphereIconsFile;
    $iconFile = new SplFileObject($sphereIconsFile);
    $check = "DEFNAME=" . $icon_def;
    foreach ($iconFile as $line){
        $line = trim($line);
        if (preg_match("/^\\[(ITEMDEF.*?)\\]/i", $line)){
            $itemdef = trim(str_replace(array('ITEMDEF','itemdef','[',']'), '', $line));
            continue;
        } elseif ($line == $check){
            return $itemdef;
        }
    }
}

function stripComments($string){
    if (preg_match("/(\/\/)/", $string)){
        $s = explode("//", $string);
        return $s[0];
    }
    return $string;
}

function chardefExport($file, $fileName, $conn){
    global $chardefTable;
    $scp = new SplFileObject($file);
    $scp->setFlags(SplFileObject::SKIP_EMPTY);
    $baseStat_prefix = "bstat_";

    $count = 0;
    foreach ($scp as $line){
        $line = trim($line);
        
        // LOCATE CHARDEF HEADER
        if (preg_match("/^\\[(CHARDEF.*?)\\]/i", $line)){
            $chardef = trim(str_replace(array('CHARDEF','chardef','[',']'), '', $line));
            $color = 0;
            continue;
        }
        
        if (!isset($chardef))
            continue;
        
        // DETERMINE IF BASE CHARACTER, SET DEFNAME AND/OR ID
        if (preg_match("/(^DEFNAME=.*?)|(^ID=.*?)/i", $line)){
            if (ctype_xdigit($chardef)) {
                $base = true;
                $id = $chardef;
                if (preg_match("/(^DEFNAME=.*?)/i", $line)){
                    $d = explode("=", $line);
                    $defname = stripComments(trim($d[1]));
                }
                $conn->query("INSERT INTO $chardefTable (id, defname) VALUES('$id','$defname')") or die($conn->error);
            } else {
                if (preg_match("/(^ID=.*?)/i", $line)){
                    $d = explode("=", $line);
                    $base_id = stripComments(trim($d[1]));
                }
                $base = false;
                $id = $chardef;
                $defname = $chardef;
                $conn->query("INSERT INTO $chardefTable (id, baseid, defname) VALUES('$id','$base_id','$defname')") or die($conn->error);
            }
            $count ++;
            continue;
        }
        
        // GET PET ICONS FOR BASE CHARS OR OTHERS WITH ICON PROPERTY SET
        if (preg_match("/(^ICON=.*?)/i", $line)){
            $d = explode("=", $line);
            $icon = findBaseHexIcon(trim($d[1]));
            $conn->query("UPDATE $chardefTable SET icon = '$icon' WHERE id = '$chardef'") or die($conn->error);
            continue;
        }
                
        // FIND DATA
        // Data searches grouped in similar data formats
        
        // FAME / KARMA
        if (preg_match("/(^FAME=.*?)|(^KARMA=.*?)/i", $line)){
            $d = explode("=", $line);
            $s = trim(strtolower($d[0]));
            $i = trim(str_replace(array('{','}'), '', $d[1]));
            $data = stripComments(preg_replace('/\s+/', ' to ', $i));
            $conn->query("UPDATE $chardefTable SET $s = '$data' WHERE id = '$chardef'") or die($conn->error);
            continue;
        }
        
        // FIND BASE STATS (STR, DEX, INT, MAXHITS)
        // COLUMN STRUCTURE: bstat_str, bstat_int, etc...
        if (preg_match("/(^STR=.*?)|(^DEX=.*?)|(^INT=.*?)|(^MAXHITS=.*?)/i", $line)){
            $d = explode("=", $line);
            $s = trim(strtolower($d[0]));
            $i = trim(str_replace(array('{','}'), '', $d[1]));
            $data = stripComments(preg_replace('/\s+/', ' - ', $i));
            $baseStat = $baseStat_prefix.$s;    
            $conn->query("UPDATE $chardefTable SET $baseStat = '$data' WHERE id = '$chardef'") or die($conn->error);
            continue;
        }
        
        // CATEGORY / SUBSECTION / DESCRIPTION / NAME / ARMOR / RESOURCES
        if (preg_match("/(^CATEGORY=.*?)|(^SUBSECTION=.*?)|(^DESCRIPTION=.*?)|(^NAME=.*?)|(^ARMOR=.*?)|(^​RESOURCES=.*?)/i", $line)){
            $d = explode("=", $line);
            $s = trim(strtolower($d[0]));
            $data = stripComments(trim($d[1]));
            
            $stmt = $conn->prepare("UPDATE $chardefTable SET $s = ? WHERE id = '$chardef'");
            $stmt->bind_param("s", $data);
            $stmt->execute() or die($stmt->error);
            continue;
        }
        
        // DAMAGE PROPERTY
        if (preg_match("/(^DAM=.*?)/i", $line)){
            $d = explode("=", $line);
            $data = trim($d[1]);
            $dam = str_replace(",", " - ", $data);
            $dmg = stripComments($dam);
            $conn->query("UPDATE $chardefTable SET damage = '$dmg' WHERE id = '$chardef'") or die($conn->error);
            continue;
        }

        // BARDING DIFF
        if (preg_match("/(^tag\.barding\.diff=.*?)/i", strtolower($line))){
            $d = explode("=", $line);
            $bdiff = rtrim(stripComments($d[1]));
            $conn->query("UPDATE $chardefTable SET barding_diff = '$bdiff' WHERE id = '$chardef'") or die($conn->error);
            continue;
        }
        
        // COLOR
        if (preg_match("/(^COLOR=.*?)/i", $line)){
            $d = explode("=", $line);
            $data = $d[1];
            if (ctype_xdigit($data))
                $color = stripComments($data);
            $conn->query("UPDATE $chardefTable SET color = '$color' WHERE id = '$chardef'") or die($conn->error);
        }        
    }
    
    if ($count <= 0){
        echo "No chardefs found in $fileName \n";
    } else {
        echo "$count chardefs exported from $fileName \n";
    }
    return $count;
} 

n1ghtwish · (02-02-2018)

Converting XuN and Ben's SQLite .add system to use MySQL table:

NOTICE: Don’t do this yet! I was too quick to post the script that I didn’t think to explain that we are only dealing with CHARDEFS here, so you should leave the .add script alone until we can get ITEMDEFS exported as well. I do have a PHP script for that in the works, it’s s just a little different because we have to handle dupelists.

If you know what you’re doing, you can use the PHP script for CHARDEFS and just get rid of the CHARDEF loop in the .add script. You’d still convert fully to MySQL but you’ll need to modify the ITEMDEF loop to insert to MySQL rather than SQLite, and then of course, everything should SELECT from MySQL, where the CHARDEFS table was populated by the PHP script and the ITEMDEF table by the .add Sphere script. Hope that makes sense.

Working with file "sphere_add_sqlite.scp":
1. Make sure "def.Item_DB" and "def.Char_DB" are set accordingly
2. Find "[function f_add_search]"
3. Replace all instances of "ldb" under that with "db"

That's it!

If you use my PHP script above, you won't really need all the build and setup functions.

You now have a PHP function to build your database with more data than you can get from within Sphere AND you have XuN and Ben's awesome .add function to read from it!

alacanet · 10-27-2018 12:47 PM

How much are the improvements?

I hope you will improve soon!

Threaded Mode \| Linear Mode Chardef Export (PHP+MySQL)
Author	Message