Progress like statements on Maria/MySQL databases with PHP

When it comes to changing data in a database, I really like Progress' language to do that. I have seen SQL that is "quite the mess" when compared to Progress' language and I have written an app in SQL and grumbled my way through it thinking "this would be so much easier with the 4GL!" I have also read code for php open source programs that definitely were a programmers first time (or at least they where not good at it)!

So why not use php/mysql (or other SQL db) to make some language like functions like Progress statements to achieve a little nicer easier to read source code to do stuff in? It won't be perfect, but it is a heck of a start (the second time too for me!) Plus, I am having fun with php so lets try it out!

Will it be just like using the 4GL? Oh no no no. There are some BIG differences in the environments. Like years ago someone got me started writing a Progress to COBOL interface and what I could do with Progress to change it like it was COBOL? I was a bit younger then and definitely didn't know stuff back then like I do now. But hey, there were many COBOL developers exiting the market and companies looking at other RDBMSes - anyhow, enough on that. I was angling on taking years and years of COBOL code and run it on Progresses DB tools - believe it or not I think there are more Progress people than COBOL people and Progresses DB is a bit more richer than a copy of a 25 year old COBOL database.

I tried another route where I made classes like the table definition and yea, it was pretty cool to put it together. I think one of my older How To articles on here covered it. But if ya added a field to the table, it would definitely change the methods in the class, and if ya changed or added a method to the PHP class, you had to convert that stuff over when the table changed! Anyhow the tools are packaged at github.com at https://github.com/scottauge/theatreops with an app written with it.

But I like to play with new ideas and said lets try to do something more SQL-ish, just wanted some language elements that would talk to the database. And my (in)famous DoSQL() function could act as the base for other stuff. Hopefully you all will find it useful and will take a look at Progress (https://www.progress.com/) to do it in the "real" world.

So in general, there is a Create(), Find(), For_Each(), Delete(), and CanFind() statements that made up the language and I wanted PHP to have them. There are no blocks in PHP (SQL does a Start, Commit, Rollback while the blocks do that in Progress), no "buffers" in PHP, automatic transitions, etc. like in Progresses language which runs on a lot of OSes (like PHP). So some changes would be needed to make it exactly like it - but I know PHP and SQL enough to live with out it. Plus I wanted to try something new. Hey, the source is out there if ya wanna make your changes too.

So lets look at DELETE in the language and my tools. One of the big differences is the idea of a buffer in the language you would do a:

find session no-lock where session.CValue = "empty".

if available session then do:

...

assign session.Cookie = get-cookie(MyCookieName).

...

delete session.

end.

With SQL the record is always in the database, you can't bring it into your environment like with the 4gl. So the tool I have does a message to delete the record:

Delete("session where CValue = 'empty'");

will translate into sql:

delete from session where CValue = 'empty';

and pass it through the engine with:

DoSQL($sql) // in general

and the engine will get rid of every thing (might be more that one!) that matches the statement.

So that is an example of the differences - Progress is more record oriented while sql is more list oriented. Blow away a record in Progress, one out of potentially many is blown away... while with sql it is more list oriented in that potentially many records matching will be blow away!

So that is an obvious difference between the SQL way and the Progress way. Mine is kinda in the middle :), hence Progress-like. Stuff you need to keep in mind!

(Don't even get me started on Create()!)

So, the test code is:

Debug("");
$Cookie="-1";
Debug("Doing BtoS(CanFind('session where Cookie=' . $Cookie))");
Debug("CanFind() is " . BtoS(CanFind('session where Cookie=' . $Cookie)));

Debug("");
Debug("Calling Create()");
if (!CanFind('session where CValue = "empty"'))
? Create ("session (CValue, Cookie) values ('empty', 1)");

Debug("");
Debug("Calling For_Each()");
For_Each("session");

Debug("");
Debug("Calling Find()");
Debug("Forward");
Find("session");
Debug("Backward");
Find("session order by Cookie desc");

Debug("");
Debug("Calling Delete()");
Delete("session where CValue = 'empty'");

Debug("");
Debug("Calling CloseDB()");
CloseDB();        

Some differences there, but livable. Results are:

Debug() Doing BtoS(CanFind('session where Cookie=' . -1))
Debug() CanFind() select * from session where Cookie=-1 limit 1
Debug() DoSQL query! select * from session where Cookie=-1 limit 1
Debug() CanFind() is false

Debug() Calling Create()
Debug() CanFind() select * from session where CValue = "empty" limit 1
Debug() DoSQL query! select * from session where CValue = "empty" limit 1

Debug() Calling For_Each()
Debug() 1 session
Debug() DoSQL query! select * from session
array(3) { ["Cookie"]=> string(1) "1" ["CValue"]=> string(5) "empty" ["Description"]=> string(0) "" } Debug()
CValue=empty
array(3) { ["Cookie"]=> string(2) "23" ["CValue"]=> string(4) "Stay" ["Description"]=> string(11) "Should Stay" } Debug()
CValue=Stay
Debug() 2 select * from session

Debug() Calling Find()
Debug() Forward
Debug() Find() select * from session limit 1
Debug() DoSQL query! select * from session limit 1
Debug() fiblock()
array(3) { ["Cookie"]=> string(1) "1" ["CValue"]=> string(5) "empty" ["Description"]=> string(0) "" } Debug()
CValue = empty
Debug() Backward
Debug() Find() select * from session order by Cookie desc limit 1
Debug() DoSQL query! select * from session order by Cookie desc limit 1
Debug() fiblock()
array(3) { ["Cookie"]=> string(2) "23" ["CValue"]=> string(4) "Stay" ["Description"]=> string(11) "Should Stay" } Debug()
CValue = Stay

Debug() Calling CloseDB()
Debug() CloseDB() Closing the db)        

With the code being:

// c:\tmp\progresslike.php

// Build_20230511221439
// - Made comments for version control
// - Made Login always available, not an on/off unless via
//?? programmer calls
// - Login info at the top for everything to know it automatically
// - CanFind() works
// - Create () works
// - Delete () works
// - For_Each() works
// - Find () works

//
// DB login info righ and ready.
//

global $Login;

$Login = array (
"servername" => "localhost",
"username" => "root",
"password" => "",
"dbname" => "game"
);

//
// Heart of db connection
//

function DoSQL ($sql, $DataFunction) {

? global $Login;
?
? // Create connection
? // if ("Conn") is set, use that, otherwise make one
? // This will stop connects to the db to one only
?
? if (isset($Login["conn"]))
?? ?? $conn = $Login["conn"];
? else {
??? $conn = new mysqli(?? ?
?? ??? ??? ??? ??? ??? ?$Login["servername"],
?? ??? ??? ??? ??? ??? ?$Login["username"],
?? ??? ??? ??? ??? ??? ?$Login["password"],
?? ??? ??? ??? ??? ??? ?$Login["dbname"]
????????????????????? );
??? $Login["conn"] = $conn;
? }
?
? if ($conn->connect_error) {
?? ?? Debug("DoSQL() connection error");
?? ?? die("Connection failed: " . $conn->connect_error);
? }

? // Through some DML (Data Manipulation Language) through the system
? Debug("DoSQL query! " . $sql);
? $result = $conn->query($sql);

? if (str_contains($sql, "select")) {
??? if ($result->num_rows > 0) {
????? while($row = $result->fetch_array(MYSQLI_ASSOC)) {
??????? $DataFunction($row);
????? } // while
??? } // if num_row > 0
? } else {
??? Debug("DoSQL() select not in $sql");
? }
?
? // Dont close up the connection, will be done with CloseDB()
? // $conn->close();

} // DoSQL()


//
// Close the db
//

function CloseDB() {
?? ?global $Login;
?? ?Debug("CloseDB() Closing the db");
?? ?$Login["conn"]->close();
}

//
// boolean to string
//

function BtoS($l) {return ($l == true) ? "true" : "false"; }

//
// Debugging
//

function Debug($Msg) {
?? ?// return;
? if ($Msg != "")
?? ?? print("Debug() " . $Msg ."<br>");
? else
??? print("<br>");
}

// --------------------------------------------------------------------------------------
// The following are meant to imitate the 4gl as best as we can with SQL statements.
// --------------------------------------------------------------------------------------

//
// CanFind()
//

function CanFind($sql) {

? global $FoundIt;

? $sql = "select * from " . $sql . " limit 1";
? Debug("CanFind() " . $sql);
? DoSQL($sql, "FoundIt");

? return $FoundIt;

}

function FoundIt($row) {

? global $FoundIt;
?
? $FoundIt=false;
? if($row) $FoundIt=true;
? return $FoundIt;
?
}

//
// Create ("tablename (fields) values (values)")
// Create("session () values ()");
//

function Create ($sql) {
?? ?
?? ?$sql = "insert into " . $sql;
?? ?Debug('Create() ' . $sql);
?? ?DoSQL($sql, "EmptyJSR");
?? ?
}

function EmptyJSR($row){
?? ?return;
}

//
// Set up a for each into a select
//

function For_Each($sql) {
?
? Debug("1 " . $sql);
? $sql? = "select * from " . $sql;
? DoSQL($sql, "ViewForEach");
? Debug("2 " . $sql);
?? ?
}

function ViewForEach($r) {
? var_dump($r);
? Debug("<br>CValue=" . $r["CValue"]);
? }

//
// Try to do a find no next or prev yet!
//

function Find($sql) {?? ?

? $sql = "select? * from " . $sql . " limit 1";
? Debug('Find() ' . $sql);
? DoSQL($sql, "fiblock");

?? ?
}

function fiblock ($row) {
? Debug("fiblock()");
?? ?var_dump($row);
? Debug("<br>CValue = " . $row["CValue"]);
?
}


function Delete ($sql) {
?? ?$sql = "delete from " . $sql;
?? ?Debug('Delete() ' . $sql);
?? ?DoSQL($sql, "EmptyJSR");

}        

With the idea being language level functions provide the SQL to be applied to the database.

I'm missing a lot, but that is the most of it!

Connect or share if this provides you interest. I'll try to get this stuff on github.com.