TL;DR:
Make a single-table web app thus:
Create a directory for it: cd /var/www/yoursite.com/; mkdir is; cd is; mkdir APP
Download/unpack is.2.1.tgz: curl https://tomveatch.com/is/is.2.1.tgz; tar zxvf is.2.1.tgz
You may want some supporting bits like TV's {lib,local,login,logout,here,my,scrudTop,etc.}.php files. Please ask!
Copy/customize your IS config file: cp templates/is.php APP/APP.php; emacs APP/APP.php
Change a target in Makefile: sed -i 's/PrevAppName/APP/' Makefile
Follow your nose in setting up your MySQL database, table, permissions...: ./APP/is$APP.db
Run Make: % make
Test: point your browser to yoursite.com/is/APP
Then you can fix up the UI as you like now that the basic
SQL|PHP|JSON|JS|HTML path is paved from DB to browser.
SUMMARY:
Here is an easy, simple system for building SCRUD apps on a LAMP
stack.
Briefly: In this system you put an information structure into a kind
of deep source file, then run 'make APP=YourAppName' to generate all the
surface source code layers of SQL, PHP, JS and HTTP/JSON/etc. to
implement a SCRUD server API and through it a set of SCRUD SQL
operations all from the perspective of a web client.
You can use the generated, customized code bits to set up, maintain,
and interact with your database instead of writing your own PHP SQL
server and a client-side Javascript API to interact with each other to
carry out the SCRUD operations using your information structure. A
single-page app in index.php generates forms or links for the
different operations. The forms call to the client-side layers in
isAPP.js and isapi.js to handle form activation. isapi.js passes the
operation back to the server in isAPP.php. isAPP.php checks user
privileges and performs the DB operations layer, returning results
back up the chain for, for example, insertion into the client's
display views. You are encouraged to customize the views and
operations, and send back added capabilities to share with others.
INTRODUCTION:
SCRUD means: Search, Create, Read, Update, Delete. These are the
operations needed for a records based database. They enable making a
record of transactions, events, or history, as well as picking out
ones you want to look at, editing them, or deleting them.
So it's pretty generally useful.
It is built on common infrastructure: a MySQL database available to
you on your server; a web server with PHP; and client systems able to
handle HTML and Javascript. You'll need 'make' and probably you'll be
on some unixish system. I use emacs but you can use any editor. In
short, it's pretty universal. I hope to use IS for an iOS/Android
landlord phone-app, for a public dog names database; one could use it
in an IoT dashboard to build and use a transaction history, I can't
even think of all the things I could use it for. I bet you have
several, and the cost in developer time is what's held you back. True
in my case. Below are Steps to Follow to use this, as well as a
breakdown of the files in this system, some placeholders for future
work, and further discussion about the design and history.
Feel free to contact the author, Thomas C Veatch at his email,
first initial, middle initial, last name at g mail dot come.
STEPS TO FOLLOW:
So here's what you do.
* Think about your application, give it a nice short punchy name
like Dog or App. Mine are called Test, Tx, PM, Water, Dog, N+V, etc.
* Make a list of the elements that will need to be in your standard
record structure for each event or transaction.
* Manually make a directory on your web server with your application
name with the command line: % mkdir $(APP).
* Manually copy templates/is.php to $(APP)/$(APP).php
is.php provides a sample information structure which you
can use to derive your own.
* Change $(APP)/$(APP).php to suit your app's information structure.
* Your derived version of is.php will need some technical details,
for each element of the standard record you have in mind for your
application. These include the kind of SQL table column data it
will be represented by, what kind of HTML input type it will be
shown as in the user interface, a size in case it is a string of
some length, a default value, etc. You should be able to figure
it out by the examples, or email me with questions.
* Then in ./Makefile change APP=Test replacing Test with your app's
name, then run it (% make) to create all the js and php files
inside the $(APP)/ directory.
* Then when you run 'make', it automatically uses the PHP's convenient file
rewriting capability to use the details in $(APP).php to fill out
and customize the templates so that they work with your
information structure in the various places they must: Javascript,
SQL, an index page to start from and the final PHP IS CRUD (that's
a pun and joke, not an insult) server program that will be run on
the server machine during a client call.
* It will also auto-generate the SQL code that will create the
database, table, and columns that are needed to support your
particular information structure. Run % $(APP)/is$(APP).db without
arguments to see what you can do with it. Follow the selected
instructions to set up your database with your
information structure in a table in it. Create user, then DB,
then table; also, later on you may need to backup and/or restore
your DB, and if your app evolves you may need to modify the table
too; all these are explained so a person can do these things even
though they may not know how to run MySQL through a CPanel UI or
carry out all these steps in technical detail, as long as they
aren't afraid to learn and willing to follow instructions. I
consider myself the target audience; if it works for me it
likely will work for you too.
* There are some simpler test runs implemented in the makefile under
make targets tc, tr, tu, td (Test * CRUD) on the same machine,
using CURL. I use them for a certain level of debugging.
* I put error messages into $(APP)/isphp.err, but Apache needs it to
pre-exist and be writable by apache so:
% cd $(APP); touch isphp.err; chmod 777 isphp.err;
If you don't need debugging messages (others of which also appear
in /var/log/php/php_error.log) then delete isphp.err. It seems like a
bit of a security risk if it is allowed to grow without bound,
so 'rm isphp.err' when things stabilize. To watch the latest
errors as they happen, use 'tail -f isphp.err'
* I also debugged using the Chrome Developer Tools accessed in
Chrome under the three vertical dots in the menu bar -> More Tools
-> Developer Tools. Very useful. In Safari, I use the Develop
menu with Empty Caches and Show JavaScript Console to help debug.
DEPENDENCIES:
* This Package: https://tomveatch.com/ops/is/is.2.1.tgz
* Server side: Linux OS, Apache web server, MySQL database and PHP
* Client side: A modern browser with JavaScript.
* Libraries:
https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css
I thought this would prettify stuff but was mostly annoying so
largely removed by now.
* Includes:
/var/www/share/lib.php for login/logout/permissions functions.
/var/www/share/tvuserdb.php for web-user emails/passwords/permissions access
/var/www/share/here.php copy to APP dir and customize with path details
/var/www/share/login.php a login page. Put it there but also symlink it into your APP directory so it can be pointed at inside your site.
/var/www/share/logout.php a logout page. "" "" ""
/var/www/share/my.php My generic page used widely at tomveatch.com
/var/www/share/mytop.php Top part of the above
/var/www/share/mybot.php Bottom part of the above
/var/www/share/scrudTop.php mytop customized for SCRUD apps
/var/www/share/humbeep.css some CSS shared across some of my sites
Ask me for these, please.
FILES:
In the is/ directory are:
README This file.
index.html a link to README
templates/ where the templates reside. php sources for the APP layers.
Makefile make APP=MyApp generates the APP layers
make distrib creates is.2.1.tgz
make clean removes error logs
DevDoc.txt Notes from building an app with IS.
LICENSE GPL 3
Now lets look at is/templates/*:
is.php a sample Information Structure, which governs the rest.
% cp is.php ../APP/APP.php, then edit it.
isdb.php generates MySQL instructions and code for setup,
maintenance, and all the SCRUD ops.
isphp.php generates PHP server-side request-handlers for the IS SCRUD ops.
isapijs.php generates a JS library with client-side functions that
connect to the server to do IS SCRUD ops.
isjs.php generates a JS library with UI modification functions that
insert IS functionality onto a set of HTML divs.
isindex.php generates a sample index.php file to offer SCRUD on the
given information structure.
FUTURE STEPS:
* find a way for the initializer of $is[Columns] inside $APP/$APP.php can
also be used to initialize $cs in isindex.php -> index.php
Perhaps put it into a separate file, that can be included in both places?
Or rename $cs as $is having included $APP/$APP.php in the place it is needed.
x connect UI functions to server API calls.
x Create/Delete the DB and Table as admin
* Delete/re-Create the DB and Table as admin
x Run some Create + Updates to put stuff in the DB.
* Run a backup, delete the whole DB, and restore cycle.
* Do a derived app for Dog Names.
* Another app: Corona
* Another app: comments on ontheground.php
* Another app: IOT monitoring dashboard from any device like a thermostat.
* Another app: a social website.
* Another app: PlayAlong
* Another app: MyWord
* Think up more apps.
DATA FLOW:
Here is how the data flows.
The OPs are listed as SCRUD: Search Create Read Update Delete.
Each is initiated by user action in the client/brower, submitting an
HTML FORM.
The following data flow handle the form submission, sending a call
and returning the results.
It encapsulates the HTML|JS|PHP|mySQL I/O from browser to server to
DB and back.
How?:
The form has a parameter onSubmit with \"return fn();\" (JS code) in it.
The js code is defined in JS code included into html via
or
The JS fn() is is_\$APP_\$OP() e.g., is_Tx_Search() in isTx.js,
which loads data from the form via the DOM, packages it up into
a URL and JSON &c., and calls isapi.js:isSearch() running in
the client/browser to do the AJAX call onto the server.
by setting XHR up and initiating it to go off into the ether.
But when the XHR gets a return result, an event invisible to
us, it does what it invisibly does, but it does send us stuff
that is visible through the onload function, called
asynchronously upon receipt of the result.
Server-Side: isXXX.php recieves the AJAX call request, unpacks
it, builds an SQL query, queries mysql with it, receives the
result, re-packages the result as appropriate (as JSON?), and
returns it via php echo \$res;, after some setting of headers.
Somehow this comes into the inputs of AJAX caller
(is_Tx_Search():myonload())
The myonload() function is defined in isTx.js::is_Tx_Search()
and sent to isapi.js::isSearch() and pushed into the XHR.
Asynchronously myonload() is called, gets its data, and puts
that into the HTML document.
DESIGN DISCUSSION:
Let there be a certain information structure IS and its octopus arms through
the parts of a web app and its lifecycle through time.
For web apps, it has to exist as octopus, i.e., be able to:
exist in parallel in multiple computers and contexts: server DB & PHP; client JS & UI.
circulate from one context to the other
So we need a standard way to map representations from IS to each context,
and software methods and execution to move them from source to recipient contexts
Also it needs a healthy life cycle: a new structure element type
needs to be made to exist in all the contexts: add an
arm, modify an arm, remove an arm.
DB backup is another part of the lifecycle.
Let an IS be a PHP associative array structure defining what is needed
to be known in the different places. (e.g., [b,c=>d...])
Key/value pairs in $is store the names of DB, Table, the columns in
the Table, user names, the URL and path to the app server programs, etc.
In IS how does client connect with server? For each CRUD operation
the single-page app in index.php attaches an onsubmit function to a
FORM. isindex.php provides a DataArea and ControlArea to attach to.
isdb.php generates an admin tutor for interacting with the database.
isphp.php generates the server-side CRUD API handler.
Then let a Makefile apply PHP itself to compile through, reading the
IS structure and mapping these template files written to be processed
with PHP so as to generate (=>) the following files (replace "APP"
with your app name):
templates/is.php => APP/APP.php: (cp this manually so make does
not overwrite it.) The idea is to color in all the templates
below with the information structure in (your derived version
of) is.php, as follows:
templates/isindex.php => APP/index.php: a client page with the
beginnings of the CRUD UI. This just creates a place to hang
stuff, and the client-side Javascript does almost all of the
HTML work.
template/isdb.php => APP/isAPP.db: provides instructions on how to
create and maintain a changing database, both to CREATE anew and
to MODIFY table for changes. Also shows what SQL looks like, to
do CRUD ops on the IS records on the server, as an informative
example, so you know how to do them.
template/isphp.php => APP/isAPP.php: PHP server-side API
implementation, provides an (authenticated?) server API to those
SQL CRUD operations on the server DB returning CRUD results to
client. It is middleware, between the client and the DB.
templates/isjs.php => APP/isAPP.js: A JS library/function file that
implements CRUD-related UI displays. Enough UI is built to let
user initiate a New, Search/Read, EDIT, and Delete and to use
the JS API to make them happen through server requests and
return data handlers.
templates/isapijs.php => APP/isapi.js: A JS library/function file
that implements a CRUD API to be called within the client and
that makes the related server calls to the PHP API.
RELATED WORK:
Incidentally there is a thing out there called PHP-CRUD-API, but I am
not that smart and found it to be intimidatingly, and
incomprehensibly complex and enormous, and I thought it would take a lot
more time figuring it out than writing my own, so here you go. I
did steal the idea from PHP-CRUD-API of using HTTP request methods
(POST, GET, PUT, DELETE) as both synonyms for CRUD operations and as
the means of communicating through HTTP which of the operations is
being requested. So it is not all POSTs with a JSON structure
holding the operation in it. Some of it is REST-ish like a GET with
a URL suffixed with ?id=NNN to READ that number-identified record.
Other operations do send JSON back and forth.
Concisely, then, you might think of IS as a PHP implementation of a
REST-ish API where the requested URL identifies the IS API itself
(URL/PATH/APP/isAPP.php) and the request method specifies which of
the CRUD ops is being requested, plus JSON where needed to transfer
the CRUD data, if not simply appended to the URL. Then client side
JavaScript to build a minimum UI.
VERSION HISTORY:
That is the idea here. January 12, 2020 work started. January 23
there was some php self-rewriting templates for some of these
parts, but nothing compiled, ran, or worked. Just some ideas in
perhaps the right direction. February 2 it is now a tiny step past
there, but I have used it to do all the CRUD operations through the
UI and seen that they pop through into the database. June 2023
I needed this to do better for a Transactions recording app, so
improved the useability and added the security model, posting is.2.1.tgz.
I can begin to
keep a straight face, declaring it to be an implementation of its
specs. Since it actually kind of is, it is time
to include other people. It will move faster with more eyes on it.
Therefore following the principle of release early, release often,
I hereby open the Kimono and offer IS to you for use under my
favorite license, the GPL. Please try it, use it, find its
limitations and help fix them, and please send any changes you make
so that I can incorporate them into an improved version and release
them to the world.
Thank you!
Tom
Thomas C Veatch PhD
tcveatch@gmail.com
1-206-858-2633
------------------------ Design and Bug Notes ------------------------
Open Bugs & Goals:
Think a bit, Tom. We are basically dealing with a set of pages in
front of a set of SCRUD operations, with various transitions among
them.
Transition from Create to Update (Save[Inactive]) displaying the created record.
Transition from Read to Update, displaying the read record.
Record display occurs
after Read
during Update process after read and during Save[active] and Save[inactive]
during Create
before Delete
More generally:
CREATE successors: edit+UPDATE, DELETE, CLEAR+edit+CREATE, Pick some + VIEW MULTIPLE, pick one + READ
READ successors: edit+UPDATE, DELETE, CLEAR+edit+CREATE, Pick some + VIEW MULTIPLE, pick one + READ
UPDATE successors: edit+UPDATE, DELETE, CLEAR+edit+CREATE, Pick some + VIEW MULTIPLE, pick one + READ
VIEW MULTIPLE: successors: pick 1+READ |some + VIEW MULTIPLE
DELETE successors: stay in VIEW MULTIPLE with one less, READ next id, fresh CREATE, Pick some + VIEW MULTIPLE, pick one + READ
Each of these is a UI tab or page, and the whole thing amounts to a
database editor. But individual applications might be single-entry
editors, or note-takers-only, or flexible search pages, or scientific
data extractors (|R), or data editing/re-coding UIs, or a combination
of these, or related subsets one for the public and others for
authorized user or for the developer, a scientist etc.
The Dariush idea is simply to provide a service with a defined web
API, then let the UI be separately programmed. So IS provides a
super-set of that, with example calls. The API is actually hidden in
the client/server interaction. To form a clean client-side JS API,
separate it from the UI: require the data to come as function
arguments not as inputs stored mysteriously somewhere in some
arbitrary form document element to be extracted within the API itself.
Then the developer can call the API functions on the client, but still
the information flow in the API would be clean.
A determined user might indeed pull code out for various purposes from
this merely-exemplifying UI, to create their own API, but it would be
nice of us to do that for them.
x Refactor API from UI.
x Debug refactored API+UI: x Create, x Read, x Delete, x Update
x Widgetry should be independent of row data copying.
x Provide a hook in the widgetry to hang the
x Let onload call a copy function with a widget selector parameter. (gotPut()
x then for each column put the received data into the selected widget by colomn
I say build myself some apps for Leads and Expenses, etc., and see how
it goes. Leads is basically CREATE plus later UPDATE as status
changes, multiple rows one for each lead ID and one for each status
change, plus VIEW MULTIPLE by date or etc to allow choosing 1 by name
for UPDATE.
Expenses is basically: (1) CREATE with a photo upload plus (2) an
UPDATE (re-code) as to entered-by, vendor/payee, amount, date,
from-account, maybe a boolean checkoff for did it get transferred to
QB or not. Then (3) VIEW MULTIPLE within a date range to then add one
(CREATE) or pick one (READ).
Some random apps:
Dog: a database of dog names contributed by the public over the years.
Water: to record water meter readings
Corona: to record deaths so far.
PM: to record PM complaints, work assignments, reports of work complete.
Tx: to record receipts or Transactions
Leads Update UI: Title, Name, address, created on date, last mod date
buttons for Convert to Tenant, and for Save.
Tenant Update UI: Name, Email, Tel, Due $, Due Date,
REceived|Deposited $_ by _ [Submit]
[Login]
EvaluateProps --> [E]
ManageProps --> [M]
Tenants @ Address
Name Tel Email Since Lease-Exp $late $days
[Go]-> TU
|