Post Reply 
 
Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Chardef Export (PHP+MySQL)
Author Message
n1ghtwish
Apprentice
*

Posts: 40
Likes Given: 0
Likes Received: 26 in 8 posts
Joined: Jan 2016
Reputation: 0



Post: #1
Chardef Export (PHP+MySQL)
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` (
 `
idxint(11NOT NULL AUTO_INCREMENT,
 `
idvarchar(30) DEFAULT NULL,
 `
baseidtext,
 `
defnametext,
 `
iconvarchar(10) DEFAULT NULL,
 `
bstat_strvarchar(30) DEFAULT NULL,
 `
bstat_dexvarchar(30) DEFAULT NULL,
 `
bstat_intvarchar(30) DEFAULT NULL,
 `
bstat_maxhitsvarchar(30) DEFAULT NULL,
 `
damagevarchar(13) DEFAULT NULL,
 `
armorvarchar(20) DEFAULT NULL,
 `
barding_diffvarchar(6) DEFAULT NULL,
 `
famevarchar(20) DEFAULT NULL,
 `
karmavarchar(20) DEFAULT NULL,
 `
colorvarchar(20NOT NULL DEFAULT '0',
 `
resourcestext,
 `
nametext,
 `
categorytext,
 `
subsectiontext,
 `
descriptiontext,
 
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;

(This post was last modified: 02-02-2018 12:02 PM by n1ghtwish.)
01-31-2018 04:30 AM
Find all posts by this user Like Post Quote this message in a reply
[+] 3 users Like n1ghtwish's post
n1ghtwish
Apprentice
*

Posts: 40
Likes Given: 0
Likes Received: 26 in 8 posts
Joined: Jan 2016
Reputation: 0



Post: #2
RE: Chardef Export (PHP+MySQL)
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!
(This post was last modified: 02-02-2018 05:31 AM by n1ghtwish.)
01-31-2018 06:31 AM
Find all posts by this user Like Post Quote this message in a reply
[+] 3 users Like n1ghtwish's post
alacanet
Apprentice
*

Posts: 2
Likes Given: 0
Likes Received: 0 in 0 posts
Joined: Aug 2013
Reputation: 0



Post: #3
RE: Chardef Export (PHP+MySQL)
How much are the improvements?

I hope you will improve soon!
(This post was last modified: 10-27-2018 12:48 PM by alacanet.)
10-27-2018 12:47 PM
Visit this user's website Find all posts by this user Like Post Quote this message in a reply
Post Reply 


Forum Jump:


User(s) browsing this thread: 1 Guest(s)

This forum uses Lukasz Tkacz MyBB addons.