CSWAM MYSQL Interface

 

A Web-site’s access to Data-base facilities/operations is usually implemented by Server-side Application Program Interface (API) functions/methods. CSWAM specifies the design of a Server-side library (amac_lib), and provides an implemented PHP language version (amac_lib.php), to perform general purpose Server-side operations (See the CSWAM Programming Document), including access to MYSQL Data-base Server(s).

1.     Server Access File (saf.txt).

 

 Web-site program access to all types of Server logic(s) (including MYSQL) are defined via named entries within the text Server Access File (saf.txt). An entry within the server access   file defines the access parameters for each Server-side connection (I.E., Data-base, Application Server, etc), interfaced via the CSWAM Server-side library.

1.1.       SAF Table Format

 

 An SAF entry is a semi-colin character delimited collection of server connection and access data. The entry data is a comma delimited list of tag=value argument data(Arg1-Argn) as follows:

Format: Entry-name: Arg1, Arg2, Arg3,…..;

 

Entry-name

The name given to an SAF table entry or Server type

 (user/admin) access restrictions

Arg(1-n)

The comma delimited list of tag=value argument data (Arg1-Argn) groups associated with the entry name. These arguments are the URL address of an interfaced Server and the specified requirements/conditions to access and use server facilities. Groups of argument data can be can be separated via a colin (:) character within a table entry.

 

1.2.   SAF Table Example

The following example depicts the use of SAF table entries to specify access conditions for a MYSQL type Data-base Server interface.

allow.types:

admin;

 

mysql.user:

 

nocmds=db:

nosqlops=drop ,table ,database ,update ,set ,alter ,grant ;

 

 sqluser:

server=localhost,

 type=user,

 db=world,

 srvuser=,srvpw=,

 dbuser=,dbpw=;

 

sqladm:

server=localhost,

 type=admin,

 db=world,

 srvuser=root,

 srvpw=passwd,

 dbuser=,

 dbpw=;

 

 rsqluser:

server=website.com,

 type=user,

 db=world,

 srvuser=user-name,

 srvpw=use-rpasswd,

 dbuser=user-name,

dbpw=user-passwd;

 

rsqladm:

server=website.com,

 type=admin,

 db=world,

 srvuser=website-root,

 srvpw=owner-passwd,

 dbuser= website-owner,

 dbpw= owner-passwd;

 

 

1.3.  MYSQL Interface Initial Access Entry

In this example, connection data needed to access an MYSQL Server is specified by the following entry in the SAF table:

sqluser: server=localhost,  srvuser=,  srvpw=,  dbuser=,  dbpw=, type=user;

   sqluser

The entry name in the SAF table given to the MYSQL Server connection data.

 

 

   server

The URL address of the MYSQL server

 

 

 srvuser

The User (name) to access the Server (default is “”)

 

    srvpw

The Password of the User to access the Server (default is “”)

 

 

  dbuser

The User (name) to access a  MYSQL data base (default is “”)

 

    dbpw

The Password of the User to access a  MYSQL data base (default is “”)

 

     type

The type of limited access to  _MYSQL commands (nocmds=… ) and,

 MYSQL Server Operations( nosqlops=… ,). See mysql.user:

 SAF table entry for settings.

 

 

2.     CSWAM Server Interface Design

 

A CSWAM Web-site Server interface should be an object oriented class design, based on methods/functions and global storage/attributes facilitating easy access to the provided handles-to (functions & storage) and results-from an external Server interface.

.

2.1.  MYSQL Server Interface

The general design for an interface to any MYSQL Server is described and in this case  implemented in the CSWAM Server-side, Library (amac_lib.php, the PHP Language Version) . The design consists of:

class _MYSQL

{

_MYSQL Storage

Initialized _MYSQL instance/object from the requested server entry data/information in the Web-site SAF table.

 

connect method

Establish _MYSQL instance/object to requested Server.

 

iface method

Execute MYSQL SQL commands and Server  interface statement/ operation(s).

 

ermesg method

Method for MYSQL interface error reporting.

 

sql method

Execute files and/or argument string arrays containing MYSQL SQL commands  and Server SQL statement(s);

 

  }

 

2.1.1.     _MYSQL Storage

 

$srvname

The name provided to connect to a MYSQL Server via access information within the Server Access File/table. The name is provided as and argument to initialize the _MYSQL Object storage Constructor/Function.

 

$dels

Output record and field control delimiting characters; specified as a the second _MYSQL Object Constructor argument.

 

$fdel

“;” ;   // Field delimiter character for each entry in the SAF table.

 

$adel

“,” ;   // Argument delimiter character for SAF table entries. 

 

$saf

“saf.txt”;   // The name of the Web-site Server Access File.

 

$bf

_STR::get_file_data($this->saf, $server.’:’, $this->fdel,”” ) ; // MYSQL Server access information from the SAF table entry .

 

$server

_STR::get_data($bf, ‘server=’, $this->adel, “ localhost” ) ;  // MYSQL Server URL address, default is localhost.

 

$type

_STR::get_data($bf,'type=',$this->adel,"user" ) ; // The type of MYSQL access(user/admin) not permitted via this entry.

 

$srvuser

_STR::get_data($bf,’srvuser=’,$this->adel,”” ) ; // MYSQL Server login-name, default is “” .

 

 

 

 

$srvpw

_STR::get_data($bf,’srvpw=’,$this->adel,”” ) ; // MYSQL Server password, default is “” .

 

$dbuser

_STR::get_data($bf,’dbuser=’,$this->adel,”” ) ; // MYSQL Server data-base login-name, default is “” .

 

$dbpw

_STR::get_data($bf,’dbpw=’,$this->adel,”” ) ;// MYSQL Server data-base password, default is “” .

 

$srvid

mysql_connect($this->server, $this->srvuser, $this->srvpw) ; // MYSQL Server connect object instance.

 

$lbf

_STR::get_file_data($this->access,'mysql-'.$this->type .'.',$this->fdel,"" ) ; // MYSQL User type information from the SAF table entry .

 

nocmds

_STR::get_data($lbf,'nocmds=',':',"" ) ; // MYSQL commands not permited to $type user.

 

nosqlops

_STR::get_data($lbf,'nosqlops=',':',"" ) ; ; // MYSQL SQL Statement operations not permited to $type user.

 

 

2.1.2.     _MYSQL Connect Method/Function

 

The static connect method provides the means to instantiate (objectify) the _MYSQL class in order to issue subsequent iface method commands and SQL statements to a MYSQL Server. The connect method returns a  _MYSQL object, associated with the requested Server name.  If the requested Server name is not active (not objectified already), the connect method will create a new _MYSQL instance to connect to the specified MYSQL Server address provided as argument data:

$obj = new  _MYSQL ($saf_entry, $dels);

_MYSQL

CSWAM PHP MYSQL interface class definition

$saf_entry

The entry name in the SAF table that specifies access conditions to the interfaced MYSQL Server. (URL address, type access, permission names and passwords).

 

$dels

The record and field/item delimiting characters used to define the MYSQL Resultant message information (records and fields).

 

 

  1. _MYSQL Connect Method Server Address Reference (SAR)

A connection to a MYSQL server is constructed via the information provided in the Server Address Reference (SAR) as follows:

 

Format:

Saf-nm:Ref-id

Saf-nm

The MYSQL Server entry name in the Server Access File (SAF)

 

:Ref-id

Optional local referencing suffix used to distinguish multiple references to the same _MYSQL Object (instantiation).

 

 

  1. _MYSQL Connect Method Arguments

 

The connect method provides the means of establishing or reusing an objectified connection to a MYSQL Server. This method will return a _MYSQL instance (pointer) to the requested MYSQL Server or null if the SAR address produces an error (Addressed Server doesn’t exist).

 

FORMAT:

$Mysql-obj = connect( $sar, $dels)

 

$sar

Server Address Reference (SAR)

 

$dels

The record and field/item delimiting characters used to define the MYSQL Resultant message information (records and fields).

 

 

 

 

2.1.3.     _MYSQL Interface Method/Function

 

The Interface method (iface) of the _MYSQL class provides a simplified means for Web-site utilization of MYSQL Server Data-base features. The iface method provides the means of:

  • Connecting to a MYSQL server,
  • Executing simplified SQL commands,
  • Issuing SQL statement strings for Server execution.

 

The format(s) of the iface method is as follows:

$obj  =_MYSQL::iface(connect, $erpt, $server, $dels, “”)

 

and

 

$obj.iface ($cmd, $erpt, $arg1, $arg2, $arg3)

 

The $erpt argument to the Interface method is used to control reporting error conditions to the client side. If the iface method detects an error condition it examines the contents of the provided $erpt argument to determine the reporting logic:

  • If the $erpt is a null string a Null return value is returned to the calling logic.
  • If the $erpt string exists (not Null), an error messages will be generated and reported to the Client-side.

 

The formats of the iface method commands are as follows:

 

$cmd

The format of _MYSQL iface commands to connect, set, and retrieve information with a Mysql Server  requires 5 arguments as follows:

 

connect

$obj  =_MYSQL::iface(’connect’,$erpt, $sar, $dels, “”)

 

querry

 $obj.iface(’querry’ ,$erpt, $SQL-stmt, $bpos , $nlines) ;

$SQL-stmt= SQL text string statement(See standard SQL statements)  to be sent to Server, and then execute $obj.fetch-data  to cause generation of the resultant response message

 

$bpos =  beginning report line number for message response(default=1)

 

$nlines= number of lines to report in message response(default = 1000000)

 

db     

$obj.iface(’db’ ,$erpt, $db-name, ‘’ , ‘’);

$db-name = switch Server operations to the  named data-base ($db-name)

 

num_fields    

$obj.iface(’num_fields’,$erpt, $tbl_name’, ‘’ , ‘’);

$tbl-name = Report the number of fields in the specified table ($tbl-name)

 

num_rows

$obj.iface(’num_rows’,$erpt, $tbl_name’, ‘’ , ‘’);

$tbl-name = Report the number of rows in the specified table(tbl-name)

 

field_name

$obj.iface’(field_name’,$erpt, $tbl_name’, $col_num , ‘’);

$tbl-name = The specified table($tbl-name)

$col_num= Report the name of the field in pos ($col_num) within the specified table($tbl-name)

 

list_tbls

$obj.iface(’ list_tbls’,$erpt, $db-name, ‘’ , ‘’);

$db-name = Report the names of tables in the specified Data-base ($db-name)

 

list_dbs

$obj.iface(’ list_dbs’, ‘’, ‘’ , ‘’);

Report the names of Data-bases on the Mysql Server.

 

fetch-data

$obj.iface(’ fetch-data’, $bpos ,$nlines) ; //Generate message response

$bpos =  beginning report line number for message response(default=1)

 

$nlines= number of lines to report in message response(default = 1000000)

 

2.1.4.     _MYSQL SQL Method/Function

 

The SQL method of the _MYSQL class executes multiple iface method operations/commands from a statement array or named disc file. SQL function/method  parses input data and issues the $obj. iface commands to:

·       Connect to a MYSQL server, and

·       Issue iface commands to send and receive SQL data, and

·       Send SQL text statement string commands to a MYSQL Server, and

·       Format Server response message reports.

 

 

FORMAT:

_MYSQL::sql($indata, $dels,$erpt)

 

$indata

 

Input data buffer or named disc file for execution of semicolon delimited SQL iface method commands and standard SQL Server statements.

 

$dels

The record and field/item delimiting characters used to define the MYSQL Resultant message information (records and fields).

 

$erpt

The $erpt argument is passed to the _MYSQL iface method for each SQL command to control reporting error conditions to the client side of the Web-site. If  the iface method detects an error condition it examines the contents of the provided $erpt argument to determine the reporting logic:

  • If the $erpt is a null string a Null return value is returned to the SQL method logic.
  • If the $erpt string exists (not Null), an error messages will be generated and reported to the Client-side, aborting further SQL method command processing.

 

 

 

  1. SQL Method  Input File Parsing Rules

 

The SQL method will accept a disc file as an input data source. In the file mode, the file data is parsed into a _MYSQL statement array via the following preprocessing steps:

    • All /* Comment convention data is removed (/* ….. */).
    • All – Comment convention data is removed (-- …. CR) .
    • All remaining data is split, via semicolon delimiters, into an array of _MYSQL statements.

 

 

  1. _MYSQL Input Statement Array Pre-Processing

 

The SQL method pre-processes a _MYSQL statement array as follows:

  • Statement argument delimiting characters (,) are ignored inside parenthesis.
  • Statement white-space and return prefix characters are ignored.

 

  1. SQL Method Statement Processing

 

The SQL method examines each statement array member and processes its command ($cmd) as follows:

 

 

$cmd

The Format of the SQL Command

server

Connect to the requested MYSQL Server

 

’server’, sar;

 

sar = Server Address Reference (SAR)

 

sql

Connect to MYSQL  Server/DB, send SQL stmt, and get results

 

’sql’ , sar, db-name,  SQL-stmt, bpos, nlines;

 

SQL-stmt= SQL text string command (See standard SQL statements)  to be sent to Server, and then execute $obj.fetch-data  to cause generation of the resultant response message

 

bpos =  beginning report line number for message response(default=1)

 

nlines = number of lines to report in message response(default = 1000000)

 

querry

Issue SQL command/statement to the Server

 

’querry’, SQL-stmt, bpos , nlines ;

 

db     

Switch Server operations to the  named data-base

 

’db’, db-name ;

 

db-name =  The name of a data-base within the connected MYSQL Server (SAR).

 

 

num_fields    

Report the number of fields in the specified table

 

’num_fields’, tbl-name;

 tbl-name  = The name of a table within the current Data-base.

 

num_rows

Report the number of rows in the specified table

 

’num_rows’, tbl-name;

 

field_name

Report the name of a column within a Data base table

 

’field_name’, tbl-name, col_num;

 

col_num= the numbered pos of a column with a table

 

list_tbls

Report the names of tables within a specified Data-base

 

’ list_tbls’, db-name;

 

If  db_name is null, current Data-base tables are reported.

 

list_dbs

Report the names of Data-bases on the Mysql Server

 

’ list_dbs’;

 

fetch-data

Report message data from last  Mysql querry response information

 

’ fetch-data’, bpos , nlines;

 

file

Execute the commands within the specified file

 

 

None of  the above commands

Send querry statement to Mysql Server

 

SQL-stmt, bpos, npos;

 

 

 

 

2.1.5.     _MYSQL Error Reporting Method/Function

 

CSWAM provides a unique facility to report Server side errors to the Client side (Browser) software. The ermesg method/function employs this CSWAM feature to report generated error messages for all the internally detected _MYSQL error conditions:

 

Mysql

Error type

Generated Message reported to Client side/Browser

Connect

AJAX:Ident= sqlout:URL=cswam_server.php: (-99) ***Server DB SQL command: connect Error: ***

 

Data base

AJAX:Ident= sqlout:URL=cswam_server.php: (-99)***Server DB SQL command: db Error: ***

 

SQL-cmd

AJAX:Ident= sqlout:URL=cswam_server.php: (-99)***Server DB SQL command: querry Error: ***

 

 

 

3.     CSWAM Server Program MYSQL Server Access Design

 

3.1.  Cswam Web-site Mysql Access Implementation

 

Mysql Server Access for the Cswam sample Web-site is designed to utilize the two main dynamic response presentation areas (object id’s_data and _response) and defining two functional window areas within them: object ids sqlin and sqlout.

 

3.2.  Cswam Accept SQL Input command & Result Windows

 

In these windows of the cswam sample web-site, _MYSQL::sql commands and arguments are accepted within the Input area (sqlin) and the executed the Server response message is displayed in the output/result area (sqlout):

// Load SQL Data base command/response Window Areas                                  

function sqlin()

            {

            $data = _STR::make("

            <SPAN><DIV>

Enter SQL Statements below and double-click to send:

</DIV>

            <TEXTAREA class='execreq'  id='sqlin' value='' rows=1 ondblclick=

\" samesite(';,sqlout;_STR(\$(\'sqlin\',\'value\'))','*_response,_answer' );\"

>

            </TEXTAREA></SPAN>

            ");

 

            $rsp = _STR::make("

<SPAN><DIV>SQL Statement Execution Results:</DIV>

            <DIV id='sqlout'>

            <TEXTAREA class='execshow' value='' rows=1></TEXTAREA></DIV></SPAN>

");

            _SND::rsp('data1',$data);

            _SND::rsp('',$rsp);

            }

                                               

 

 

3.3.  Cswam Populate MYSQL Result Window With Server Response

 

// Report SQL Data base command response in sqlout area

function sqlout()

            {

            global $_RQST;

 

            $ans = _MYSQL::sql($_RQST->args,"\n,") ;

            $rsp = _STR::make("

            <SPAN>

            <DIV>SQL Statement Execution Results:</DIV>

            <DIV id='sqlout'>

            <TEXTAREA class='execshow' value='' rows=1>~1</TEXTAREA>

            </DIV>

            </SPAN>

            ",$ans);

            _SND::rsp('',$rsp ) ;

            }