Home Grown Spreadsheet Indexeddb Primer Tutorial

Home Grown Spreadsheet Indexeddb Primer Tutorial

A few days ago we talked of the synergy between spreadsheets and database when we presented Home Grown Spreadsheet Primer Tutorial as shown below.

In sympathy with …

a “tilt” towards a more transparent spreadsheet/database relationship, to be developed further into the future

… today we start down the road of the Indexeddb local storage functionality brought in with HTML5, and introduced as an alternative methodology for a local data database solution to take over from the WebSQL methodology given up in 2010.

So what do we mean by “local storage” here? We mean a method to run a database in the client space of your web browser. In other words, if you clear your web browser’s Browsing Data you will accidentally clear this Indexeddb database data. So this isn’t for everyone, we grant you. And so far, it is not supported by all the web browser “brands” either, at this point. If you go on to research this subject more for yourself, we recommend using the Google Chrome web browser to do your testing. At this point, we want to do two lists …

1. good links on the “net” regarding HTML5 Indexeddb database work …
   • IDBDatabase.createObjectStore() – Web APIs | MDN
   • Migrating your WebSQL DB to IndexedDB – HTML5 Rocks
   • How To Use IndexedDB – Simple(st) Example | Ido Green
2. synopsis of our initial observations working with Indexeddb database methodology …
   • throw out old SQL thinking, and think transactionally, preferably utilizing JSON input data protocol
   • Indexeddb’s ObjectStore is the nearest equivalent to other RDBMS database Data Tables
   • the Indexeddb’s specification is an everchanging thing, but for the moment you will only be able to perform database structural changes (like DDL commands) by upping the database version when you open, and access the (request) onupgradeneeded event, as per …
     
request = window.indexedDB.open(dbName, dbVersion);

request.onupgradeneeded = function(event) {
todoDB.indexedDB.db = event.target.result;
var db = todoDB.indexedDB.db;
var store = db.createObjectStore(tname, { autoIncrement : true }); //, {keyPath: "timeStamp"});
setTimeout(populateTable,500);
};

request.onsuccess = function(event) {
// more here
};

request.onerror = function(event) {
// more here
};

     … accessing the Indexeddb database via Javascript calls … though in the future this concern will become obsolete

So we have a long way to go, but today’s progress sees us being able to …

• capture our spreadsheet data … revealing a “Save to Database” button … that if you click …
• (at least on Google Chrome and some other web browsers) will change to a web browser URL giving the spreadsheet worksheet a name that will become the database table name, or you can have defined this ahead of time, and then run that though the HTML5 and Javascript logic to save that data to the Indexeddb database, ready to be recalled into the cells of a (home grown) spreadsheet (system)

The HTML and Javascript and CSS code, including HTML5 and Javascript Indexeddb logic, today, you could call spreadsheet.htm (changed this way) and which has the live run link for you to try this out for yourself, data entry wise.

We hope you get some ideas here. And if this does interest, remember in the past, that MongoDB may be a database product that may interest you as well, in this line of thinking.

Previous relevant Home Grown Spreadsheet Primer Tutorial is shown below.

Home Grown Spreadsheet Primer Tutorial

Yesterday’s Circle and Point jQuery DataTable Primer Tutorial as shown below, set us to thinking about Spreadsheets, and though we don’t pretend that there are not great products out there, we thought that a new web application that involves …

• spreadsheet data entry
• pared down functionality
• a “tilt” towards a more transparent spreadsheet/database relationship, to be developed further into the future

… we thought it could be worthwhile to pare down the “bells and whistles” of proprietry spreadsheet applications so that what is most obviously disappearing, for our Spreadsheet web application, is …

• the top left “copy everything to clipboard” button … as useful as it is in the “bells and whistles” spreadsheet applications
• the left hand row number buttons … as we are forgoing cell deletions and other vertical copies and moves
• the neverending column and row worksheet feeling
• the idea of multiple worksheets in a spreadsheet
• the column data types functionality … we’ll be totally “string” … at least for now
• associated spreadsheet tools, like Charts

… and what we add/keep is …

• a means for columns to be renamed, to allow, later, for that spreadsheet/database relationship to be more transparent
• a cell of interest means by which you enter its value is retained, but looks and acts differently
• we are modest about how the spreadsheet “grows” by only spreading in the immediate vicinity of the last spreadsheet value entry

And so it is really early days with this project, so far only involving HTML and Javascript and CSS thoughts which you could call spreadsheet.html and which has the live run link for you to try this out for yourself, data entry wise.

Previous relevant Circle and Point jQuery DataTable Primer Tutorial is shown below.

Circle and Point jQuery DataTable Primer Tutorial

Perhaps you are a reader who has been interested in the “backend” of web application development, traditionally the database side of Information Technology software development. And maybe along the way you were reading when we presented MySql Stored Procedures Primer Tutorial as shown below? Well, today we take its data “thoughts” and funnel that through the brilliant, the stupendous jQuery (Javascript library) DataTable functionality.

For a lot of programmers, we tend to hone in on Data Tables, in a database, as the most latch-able onto-able thing about databases … or maybe that’s just me … anyway, the Data Table is that “thing” in a database consisting of data presented in …

• columns … to do with the “type” (or types) of data … and …
• rows … to do with the “order” of data

… like you are probably familiar with regarding spreadsheets … ie. the letters up the top are like “columns” and the “numbers” on the left hand side are like “rows” with respect to what we’re talking about above. As you can tell from this, a spreadsheet worksheet is a lot like a database Data Table.

Continuing that spreadsheet analogy, you spreadsheet enthusiasts probably have seen the concept of multiple worksheets within the one spreadsheet? Well, that is like our scenario today where we have two Data Tables called …

• Point
• Circle

… and for both tables we’re going to have these numerical “count” indexes, as is so common in RDBMS work, because computers can sort faster and join things faster with numerical data. The trade off, as for us, if you imagine it, is, that there can be Data Redundancy, or “inefficiency” in that as far as the “Point” Data Table goes, we may have the same (x,y) co-ordinate set existing multiple times, and we’ll define a unique “row” (or record) for both of these, yet, you’d have to agree, that this is, perhaps inefficient and wasteful. The upside, though, is it is not making my head hurt, because there is a one to one relationship between a “Point” (Data Table) row and a “Circle” row, making the Javascript logic we perform, and you can see with circle_point_jquery_datatable.html less involved with what we are doing today. And what is that, pray tell?

We’re going to let the brilliance of jQuery display that aforesaid mentioned data in its inimitable style … such panache! And then we are going to add “row” click event functionality to draw the point or circle of that (Data Table) row be drawn up the top using HTML SVG graphics. You can also have “column” click events, but we choose “row” clicks to be interested in, because on a row click, the whole “.innerHTML” of that row’s (HTML tr) element is accessible in the logic, and so …

• should you click on a Point row we draw (x,y) with some co-ordinate text … and …
• should you click on a Circle row we extract the equivalent Point’s (x,y) to be the circle’s SVG (CX,CY) centre point (easily done because of that one to one correspondence we talked about above), and have the radius value in the last “column” cell of the Circle row we are on, enabling everything we need to draw an SVG circle element

Now in all this, we talked about the “one to one correspondence” numerical indexes, but in practice to make this more robust, in case a superfluous “Point” row is deleted by a do-gooder user later down the track, you may have noticed that all along, the second “column” of the Circle (Data Table) (even though called “originid”) points back (ie. is the same value) as the Point table’s first (and index) column (called “pointid”) and these “join” fields (another name for “column” can be “field”) can still gather apples with other proper apples, in that scenario. But today, that is overkill, because there is no “delete” row (or “record”) functionality in our web application today. That web application you can try for yourself with this live run link.

Previous relevant MySql Stored Procedures Primer Tutorial is shown below.

MySql Stored Procedures Primer Tutorial

Today’s tutorial follows up on phpMyAdmin interface to MySql and PHP Primer Tutorial in that we again use the brilliant phpMyAdmin to oversee the results of some PHP code which uses MySql calls to create tables called POINT and CIRCLE used to store information defining a circle, and then it creates three MySql stored procedures in the database to help add circle data with the single MySql statement that goes CALL AddCircle(x, y, radius); via the use of those stored procedures. Let’s see below what Wikipedia says about Stored Procedures generally.

A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure (sometimes called a proc, sproc, StoPro, StoredProc, sp or SP) is actually stored in the database data dictionary.

Typical use for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures can consolidate and centralize logic that was originally implemented in applications. Extensive or complex processing that requires execution of several SQL statements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures by executing one stored procedure from within another.

Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement.[1]

Here is some downloadable PHP programming source code which shows the results of the MySql SQL requests made and can be renamed to ourmysqlstoredprocedure.php as required.

Here is some downloadable supervisory PHP programming source code which gathers the MySql SQL requests made and can be renamed to ourmysql_storedprocedure.php as required.

If you want to develop your own live usage (have provided a live usage link here which will not work but you can use if you are a beginner, to get used to mysql errors, which will occur after you hit either button of the link (these errors indicate bad MySql connection details which are the same reason the database dropdown is not full of options), or you can use this link to see the raw MySql SQL involved in piecing this tutorial together) of these two PHP source codes then you could fix up the hard codings for MySql host/username/password up the top of ourmysql_storeprocedure.php (where you may notice that the default host in the code is localhost:8889 which is the default host string for MySql (ie. port 8889 is used) when using a MAMP (Mac laptop) local web server (which uses localhost:8888 as its local “domain name” for http usage)) or you can keep the same code and use a URL like:

[your-domain-name-plus-a-bit-maybe]/ourmysql_storeprocedure.php?host=[your MySql host address]&username=[your MySql username]&password=[your MySql password]&database=[your optional MySql default database name within the looked up list presented]

Previous phpMyAdmin interface to MySql and PHP Primer Tutorial is relevant and shown below.

phpMyAdmin interface to MySql and PHP Primer Tutorial

You never hear much about the data when you hear about great PHP products, but we
all know it is the data that differentiates the quality of the end result.

That is probably because database products like MySql, SqlServer, Oracle SQL, Advantage and Access
are pretty good at what they do, and emphasise reliability rather than flashiness.

MySql and PHP have a great open source interface with phpMyAdmin, which is so good,
you forget that it is not the default MySql administrator’s interface product.

Let’s have a look at this WordPress database and a bit of how it looks, looking
through the prism of phpMyAdmin …

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.