SQL for Flat Files Data Type Tutorial

SQL for Flat Files Data Type Tutorial

Do you recall a statement in this blog posting thread’s “primer” tutorial as per …

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

… well, today, we continue on the quest to add data type intelligence to our web application to improve on that “Primer” tutorial scenario. How, continue? Well, in a previous blog we had the functionality …

• when using web application flat_file_poll.php with its associated live run link (and changed for today’s work in this way), behind the scenes, a right hand data table column is added that is numeric, and that the child iframe hosting flat_file_engine.php ((with this live run you can try) and which accommodated in this way) now allows, for this right hand numeric column, to increment the polling count (and the leading space character is needed) within the INSERT statement VALUES fields are …
  1.  ++ … and other possibilities for later when you tailor your own polls can be …
  2.  ––
  3.  +=[number]
  4.  -=[number]
  5.  *=[number]
  6.  /=[number]
  7.  %=[number]
• these same ideas can apply to UPDATE fields in the SET section

… that helps our string data in the Flat File act a lot like a counting number. This was good enough for Poll Data but we want to use the Flat File SQL Engine interfaced with other web application projects that involve numbers and calculations, and so, today, we are adding to flat_file_engine.php ((with this live run you can try) and which changed in this way for today’s progress) …

•  [colname]+[number]
•  [colname]-[number]
•  [colname]*[number]
•  [colname]/[number]
•  [!+=_colname]
•  [!-=_colname]
•  [!*=_colname]
•  [!/=_colname]
•  [!+_colname]
•  [!-_colname]
•  [!*_colname]
•  [!/_colname]

… type syntax.

And so we have a new PHP web application that accesses the Flat Files SQL Engine called flat_file_mathematics.php and by its name you’d guess it is design fir mathematical applications, with a “first cab off the rank” being our Fibonacci Sequence and Golden Ratio option, which you might like to try with today’s live run link for this, supervises the child iframe hosting flat_file_engine.php ((with this live run you can try) and which accommodated in this way the new “make your mathematical flat file SQL engine web application” functionality.

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

SQL for Flat Files Database Tutorial

We’ve spoken about “data permanence” before, when we presented PHP Themed Supervision Sixth Genericization Tutorial

Can the Javascript client side of the web application world “save” anything? Sort of, if you want to consider HTTP Cookies, and lots and lots of websites do this, as you probably know if you use the web to shop for things. A server side language like PHP (or ASP.Net) can make this arrangement more permanent, because, as you probably know, you can clear the cache and (web) browser of cookie information whenever you like. Often that “permanence” takes the form of database record(s) but today, we’re doing it via a one file (snapshot) per IP/browser brand/date combination.

… as seen from the Javascript client side of things, the place most of we users interface to when we are doing a lot of our online goings on. In this “slice” of the “net” Cookies, or perhaps, Indexeddb, or perhaps, node.js, or perhaps MongoDB are interesting places to go to discover a more permanent data arrangement. Here, though, with our PHP driven Dynamic Polling web application supervising the Flat File SQL Engine child iframe functionality, we can fall back on web server files, as we do with that Flat File SQL Engine, and so, even though we are, for all our intents and purposes so far, just interfacing to the one web server file, it can have some of the powers of a proprietry database none the less. And the most significant feature in this respect is its ability to store data for as long as that web server flat file exists in a non corrupt state. Javascript client accessible HTTP cookies rely on the web browser cache not being cleared, which is no guarantee, for sure, regarding data permanence.

This “data permanence” came into play with our Dynamic Polling web application we left off with SQL for Flat Files Poll Tutorial yesterday, as a result of our desire that you, the user, should be able to construct your own poll, and revisit the webpage to review the findings, or add to the findings, the latter of which the general public visiting this web application can do as well, once you, the poll creator, ever click one of the poll option buttons to set the (DDL) “ball” rolling.

In summary, new functionality for Dynamic Polling today, as with WordPress 4.1.1’s SQL for Flat Files Database Tutorial, involves …

• allowing users to create their own polls, still using HTML form method=POST target=myiframe action=./flat_file_engine.php techniques with one extra data column list needed in the “internal use only” record of the Flat File SQL Engine specifications (because the user will not choose every option straight away, and for the web application to piece that back together again requires this button list)
• allowing the Flat File SQL Engine child iframe web application recognize that data is poll data and additionally show a Google Chart Pie Chart representation of the relevant SQL query result set

And so that leaves us with, today, the PHP flat_file_poll.php with its associated live run link (and changed for today’s work in this way) supervises the child iframe hosting flat_file_engine.php ((with this live run you can try) and which accommodated in this way the “make your own poll” new functionality, for your perusal … and use, hopefully … please feel free.

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

SQL for Flat Files Poll Tutorial

We venture further down the route of interfacing web applications to our Flat File SQL Engine. The first interfacing web application we started out on, to the “proof of concept” level yesterday, when we presented SQL for Flat Files DDL Tutorial, we’ve extended in its scope. Interfacing can be a rewarding programming exercise for a few reasons …

• modularization of code can be a good way to go and with interfacing you tend to compartmentalize functionality into components, which can be useful
• interfacing is a “two way street” but if you have control of the “intersection” … chortle, chortle … you can shape the design to your purposes, and ease of programming and/or use … contrast that to interfacing to third-party products, and this inhouse approach is easier on occasions, depending on third-party interfacing documentation, the quality of which can swing the argument the other way, on occasions, too
• interfacing adds flexibility, because the smaller components you develop can each easily move in different design directions, more easily, generally
• interfacing with the smaller components means third parties may be tempted by what you have to offer, if you document the interfacing well enough

… as well as, at least for us, a sense of satisfaction that design has been a major part of the project, all the way through its unit testing phase, and beyond.

So interfacing being a “two way street” did mean that we needed to tinker with the Flat File SQL Engine child iframe part of the interfacing to the Poll Web Application parent. We didn’t want our DDL “CREATE TABLE” (+ DML “INSERT”) ideas of yesterday to change, because we want any user to be able to create their own poll if they like, and we think to involve any SQL “UPDATE” statement is far too convoluted. So with a poll, users vote, and users get a vote each, generally, so we want numeric “count” data table fields (which we have control over, and arrange that such fields are the last fields in our DDL “CREATE TABLE” statements) to be able to be incremented in a Flat File SQL Engine “INSERT” statement. No worries, we make the rules, and so the rules for this, are …

• when using web application flat_file_poll.php with its associated live run link (and changed for today’s work in this way), behind the scenes, a right hand data table column is added that is numeric, and that the child iframe hosting flat_file_engine.php ((with this live run you can try) and which accommodated in this way) now allows, for this right hand numeric column, to increment the polling count (and the leading space character is needed) within the INSERT statement VALUES fields are …
  1.  ++ … and other possibilities for later when you tailor your own polls can be …
  2.  ––
  3.  +=[number]
  4.  -=[number]
  5.  *=[number]
  6.  /=[number]
  7.  %=[number]
• these same ideas can apply to UPDATE fields in the SET section

We hope you try some polling today.

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

SQL for Flat Files DDL Tutorial

Welcome to the tutorial title that changes the least but with a big change to direction with our Flat File SQL Engine thinking because today’s SQL DDL focussed work opens our Flat File SQL Engine up to ‘outside’ use. That ‘outside’ is only web applications from the www.rjmprogramming.com.au domain, and this is deliberate. Remember when we said, in an earlier blog posting on this thread whose last offering was SQL for Flat Files DML Tutorial?

DDL functionality is hidden from view in the logic of the PHP

… well, yes, that is still the go, and today (as with WordPress 4.1.1’s SQL for Flat Files DDL Tutorial) we open up a mechanism in the code of flat_file_engine.php (with this live run you can try) and which in a small time way, in this way to allow for this interfacing to other www.rjmprogramming.com.au domain web applications that fit in with the “protocols” we introduce for our Flat File SQL Engine today. The rules of interfacing are …

• the web application “calling” the Flat File SQL Engine should reside on the www.rjmprogramming.com.au domain
• the “calling” web application should use an HTML form method=POST methodology, and must fit in with precise field name conditions
• the “calling” web application should use a DDL SQL statement of the form, exemplified by CREATE TABLE [newApplicationTableName] (colname1of4,colname2of4,colname3of4,colname4of4) to effectively CREATE DATABASE [newApplicationTableName] within the Flat File system … and then from then on …
• the “calling” web application should use a DML SQL statement of the form, exemplified by INSERT INTO [newApplicationTableName] (colname1of4,colname2of4,colname3of4,colname4of4) VALUES (‘colvalue1of4′,’colvalue2of4′,’colvalue3of4′,’colvalue4of4’) to populate that Flat File System database

… and then, after all this is achieved, everybody using the Flat File system will be able to read (ie. SELECT) that new database’s information, but only the web application that created that database will be able to write or modify or delete (ie. INSERT, UPDATE, DELETE) that database’s (=data table’s) data records (rows).

So, with this quite simple design, we are substituting the “user” of a conventional proprietry database system with a home grown web application in our (very much pared down) Flat File SQL Engine design.

Which brings us to a real web application that takes advantage of this Flat File SQL Engine. The first cab off the rank will be a dynamic Polling Web Application, the first draft, and just at the ‘proof of concept stage’, for which you can see some PHP code is flat_file_poll.php and/or try its associated live run link.

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

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 (also shown at WordPress 4.1.1’s SQL for Flat Files DML Tutorial) 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.

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

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

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

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