SQL for Flat Files DML Tutorial

SQL for Flat Files DML Tutorial

Continuing on with our “Flat File SQL Engine” project today, building on yesterday’s SQL for Flat Files Primer Tutorial we can summarize the emphasis of today’s work by …

• relenting on those DML flat file data table data content restrictions, talked about yesterday saying, then …
  

  we are going to disallow ~ and ` and Comma (ie. “,”) and | from being allowed in “data values” above, period

  … well, the “period” turned out to be 18 hours … chortle, chortle … as we made it that all those characters can now be entered by the user because we control the input (during an INSERT (DML) SQL statement’s HTML (method=POST action=./flat_file_engine.php target=myiframe) form’s input type=text onblur event), defined in the PHP (later writing out a Javascript function) with …
  
$cfilter=" function filter(oi) {
var iois=oi.value;
var wasi=iois;
iois=iois.replace(/\+/g, \"~ + char(43) + ~\");
iois=iois.replace(/\'/g, \"' + char(39) + '\");
iois=iois.replace(/\~ \+ char(43) \+ \~/g,\"' + char(43) + '\");
iois=iois.replace(/\,/g, \"' + char(44) + '\");
iois=iois.replace(/\`/g, \"' + char(96) + '\");
iois=iois.replace(/\|/g, \"' + char(124) + '\");
iois=iois.replace(/\~/g, \"' + char(126) + '\");
iois=iois.replace(/\"/g, \"' + char(34) + '\");
iois=iois.replace(/\' \+ char\(126\) \+ \' \+ char\(43\) \+ \' \+ char\(126\) \+ \'/g,\"' + char(43) + '\");
if (iois != oi.value) {
oi.value=iois;
oi.title+=' ... ' + wasi;
}
return iois;
}
";

  … via the old picture in the keyhole SQL data migration trick friend for special character handling in that, in many SQL “brands” …
  
Logan's Run 1,2,3

  … can be considered to be …
  
'Logan' + char(39) + 's Run 1' + char(44) + '2' + char(44) + '3'

  … or something similar (like you can see with today’s tutorial picture) … but remember here, we are making the rules on this project … and we are attempting to fully control and vet user interaction … it’s just that we’d be mad not to model that project on existing SQL “smarts”

• DML SQL statement HTML and Javascript form allowing separate HTML input type=submit buttons for each of …
  1. SELECT
  2. INSERT
  3. UPDATE
  4. DELETE

  … allowing for WHERE clauses (that allow for AND or OR clauses that can be removed as necessary) in SELECT and UPDATE and DELETE (DML) statement scenarios … and the (method=POST action=./flat_file_engine.php target=myiframe) form approach behoves us to do the reverse of the “‘ + char(44) + ‘” (to flat file from Javascript client) in PHP at the server to resurrect a good looking report in our flat_file_engine.php (with this live run) changed pretty massively, in this way to achieve this interactive input controlled “Flat File SQL DML Engine”

You’ll find no restrictions using this web application, and, as per usual, we welcome your interaction. By design, the most you could do is DELETE all the data table records on one of the two databases, but am hoping you try more creative interaction?!

Previous relevant SQL for Flat Files Primer Tutorial is shown below.

SQL for Flat Files Primer Tutorial

Around here, we like SQL (Structured Query Language) as a tool to work for RDBMS databases (or “data sources”) of all flavours and styles and types and content, and this should come as no surprise, as a lot of thought went into its design. Now SQL commands can, generically speaking, be categorized into …

1. DDL (Data definition language) … affecting the structure of the data contained in the “data source”
2. DML (Data manipulation language) … affecting the content of the data contained in the “data source”

Notice how open-ended we are about using the term “data source”? That is because SQL is so good, that “data source” can mean lots of different things to lots of different people in lots of different places … even that little room?!

For instance, for this thread of blog postings we want you to think of “data source” equating to “flat file”, and because of how we do things on our www.rjmprogramming.com.au domain Apache/PHP/MySql CentOS (think Linux) Web Server, to be precise here we qualify this to “Linux flat file via PHP“.

We’re going to write our own PHP Flat File Engine where …

1. DDL functionality is hidden from view in the logic of the PHP
2. DML functionality is available to all users, as vetted by our PHP, whether they realize it or not (“behind the scenes, like, guv'”), as the Engine mechanism that links the user interface to the flat file data stored on our www.rjmprogramming.com.au domain web server in … you guessed it … flat file(s)

What’s the purpose of this? Can’t say for now, because we want to see what genericity happens as we go along here, but we are going to write PHP web applications to use this “Linux flat file Engine via PHP“.

What can’t a flat file data system do that a database (data system) can? We can think of, at the top of our head …

• delimitation issues regarding databases, data tables, data table columns, data table rows … a database handles all this via proprietry database “engine” software … we have to develop our own “flat file” data system thoughts here
• data type “smarts” … we’re keeping it really “everything ends up as a string” … doh … simple, for now, with our flat file data system design
• keys and indexes
• functions
• joins
• stored procedures
• triggers
• users, roles, privileges (eg. grant, deny)
• language character sets
• speed (and power) of data manipulation query findings

In our eyes, despite this, this is very much a useful exercise, and am hoping it becomes a practical reality … and you’ll be pleased to know our “prepare the breakfast in the morning before you knew there could be breakfast or know there could be a morning” project is coming along fine, thanks … and thanks for asking … for wide ranging usage applications.

Patently, “delimitation” is the biggest bugbear with our project, but if everything is “funnelled through” our code, and not allowed to escape to other outside usages, we should be able to disallow our inhouse “delimitation” characters, to have it so they are not allowed into the “data content” with a limited annoyance factor to the more adventurous users who wanted to place such characters into their data. And, of course, the other data bit of interest is non-English characters. This will be an evolving matter we think about. You’ll notice when you use database interfacing software, such as PHP and MySql‘s brilliant phpMyAdmin interface, front and centre regarding database design is a language character set of interest. Of course, this is no accident of design, and MySql, and its ilk, are sophisticated and brilliant products that have seen many many many hours of software development time put into them. Please do not think that we are replacing any of this database design and functionality brilliance with our work today, but our work may bring convenience and ease of “getting up and running data source work here on this domain, quickly and easily”.

Okay, thinking DML (Data manipulation language), we want to be able to …

• SELECT [*|Comma separated Column List] FROM [table] WHERE [Where clause] ORDER BY [Comma separated Column List];
• INSERT INTO [table] ([Comma separated Column List]) VALUES ([Comma separated Data List]);
• UPDATE [table] SET [Comma separated list of Column=Data];
• DELETE [table] WHERE [Where clause];

… where the itallicized functionalities may not be in today’s “first draft”.

For us, with our flat file data system, what defines our data content structure are our “delimitation” decisions, as per …

• one database (data source) will be defined via (our) one (big) flat file (with internally controlled naming and path decision system … ie. we control all DDL (Data definition language) internally) record delimited by ` character (to any other database (data source)) … and may throw in an Ascii character 10 (line feed) before its end delimitation, for “internal use only” clarity, and regarding the rule “database names may not contain Ascii character 10 (line feed)”
• one data table will be delimited within the database record as its first column above by | character (ie. in our flat file system design one database has one database table) … which maps to “[table]” above … and may throw in an Ascii character 10 (line feed) before its end delimitation, for “internal use only” clarity, and regarding the rule “database table names may not contain Ascii character 10 (line feed)”
• one data table [Comma separated Column List] will be delimited within the database record as its second column … and may throw in an Ascii character 10 (line feed) before its end delimitation, for “internal use only” clarity, and regarding the rule “column names may not contain Ascii character 10 (line feed)”
• one data table’s third column will be set aside for application specific data, as required … and may throw in an Ascii character 10 (line feed) before its end delimitation, for “internal use only” clarity, and regarding the rule “application specific data may not contain Ascii character 10 (line feed)”
• one data table’s row data source records will be delimited within the database record as its fourth column, delimited from each other by the ~ character, and between “data values” by the Comma character … and may throw in an Ascii character 10 (line feed) after its final “end of all rows” delimitation, for “internal use only” clarity, and regarding the rule “database names may not contain Ascii character 10 (line feed)”

… and by which you can infer …

• we are going to disallow ~ and ` and Comma (ie. “,”) and | from being allowed in “data values” above, period … sorry to all lovers of the Comma character (but we think, later, anyway, we may relent on this restriction … but, for now …)
• for “internal use only, but also for sanity” purposes, at appropriate places we can place Ascii character 10 (line feeds), as we rabbitted on about above

Today’s “Primer Tutorial” job is to take some “flat file system” data, and parse it (ie. via a “passed in” SQL statement).

1. If you don’t already have MAMP installed, consider installing MAMP and following steps below … else ignore steps below and click this link to see the equivalent www.rjmprogramming.com.au domain version of what is happening below …
2. Take (ie. copy into a buffer) today’s “first draft” flat_file_engine.php and save it to the directory of MAMP equating (most likely) to http://localhost:8888/ (which is /Applications/MAMP/htdocs on this MacBook Pro) with the name flat_file_engine.php
3. Take (ie. copy into a buffer) the “flat file system” proposed data …

   
ourdatatable1
|col1_1,col2_1,col3_1
|internal1_1,internal2_1
|r1c1_1,r1c2 1,r1c3_1~r2c1_1,r2c2_1,r2c3_1~r3c1_1,r3c2_1,r3c3_1~r4c1_1,r4c2_1,r4c3_1
`
ourdatatable2
|col1_2,col2_2,col3_2
|internal1_2,internal2_2
|r1c1_2,r1c2_2,r1c3_2~r2c1_2,r2c2_2,r2c3_2~r3c1_2,r3c2_2,r3c3 2~r4c1_2,r4c2_2,r4c3_2
`


4. Save this data to that same directory of MAMP equating (most likely) to http://localhost:8888/ (which is /Applications/MAMP/htdocs on this MacBook Pro) and call it flat_file.dat
5. Make sure MAMP is up and running … then …
6. At your favoured web browser’s address bar type (most likely) http://localhost:8888/flat_file_engine.php?sql=SELECT+*+FROM+ourdatatable2

And when you do, do you get the look of the orange part of today’s tutorial picture? We hope so. But early days with functionality here.

As you can see above, the mechanisms to do “flat file data system” work can be very easy to get up and running.

Food for thought, perhaps?

If this was interesting you may be interested in this too.

If this was interesting you may be interested in this too.