DEV Community: Prasad Saya

Indexes in MySQL – Notes

Prasad Saya — Mon, 27 Nov 2023 11:48:56 +0000

Indexes are used to find rows in a database fast. Without indexes the database has to start from first row and find the required rows until the last row, sequentially (a.k.a. full table scan). Full table scans are slow on tables with large number of rows.

MySQL uses the indexes to point to the rows as specified in the query criteria and retrieve them quickly. In MySQL you can create different types of indexes based upon your query, search and performance requirement.

Here is some info about types of indexes, associated commands and index usage by queries.

Index Types

Column index - An index on a single column.
Composite index - An index on multiple columns. A.k.a. multi-column index, concatenated index.
Unique index - An index on column(s) that have no duplicate values.
Primary key index - A special index that stores row data for InnoDB tables. If a primary key is not defined, the first Unique index is used for this. Otherwise, a hidden index is created on a synthetic column of row id values. A.k.a. Clustered index.
Secondary index - A non-primary key index.
Covering index - An index that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, thus saving disk I/O.
Partial index - An index that represents only part of a column value, typically the first N characters of a long VARCHAR value.
Spatial index - An index created on spatial data type (e.g., POINT and GEOMETRY) columns. Spatial data is also known as geographic data or geospatial data. You can also create non-spatial indexes on spatial data types.
Fulltext index - An index that holds the search index in the MySQL full-text search mechanism. This is for text-based data types (CHAR, VARCHAR, or TEXT).
Hash index - A type of index intended for queries that use equality operators, rather than range operators such as greater-than or BETWEEN. It is available for MEMORY tables.
Adaptive hash index - An optimization for InnoDB tables that can speed up lookups using = and IN operators, by constructing a hash index in memory.
Descending index - An index where index storage is optimized to process ORDER BY column DESC clauses.
Invisible index - An index that is not used by the optimizer. Invisible index makes it possible to test the effect of removing an index on query performance without dropping it.
Virtual index - An index on virtual or stored generated columns or a combination of regular and generated columns.
Multi-Valued index - An index defined on a JSON column that stores an array of values.

Creating Indexes

You can create an index, modify, delete or query the database to see what indexes are there.

Here are some commands and their usage:

Create using CREATE TABLE INDEX, ALTER TABLE ADD INDEX, CREATE INDEX
Modify using ALTER TABLE ALTER INDEX, ALTER TABLE RENAME INDEX
Delete using ALTER TABLE DROP INDEX, DROP INDEX
List indexes using SHOW INDEXES

Example Usage:

-- Create a table and an index on two columns c1 and c2
CREATE TABLE t1 (
  c1 VARCHAR(10),
  c2 VARCHAR(15),
  c3 VARCHAR(30),
  c4 DATETIME,
  INDEX ix1 (c1, c2)
);

-- Add a unique index
ALTER TABLE t1 ADD UNIQUE INDEX uix1 (c4);

-- List the indexes
SHOW INDEXES FROM t1\G

-- Create a partial index
CREATE INDEX part_of_c3 ON t1 (c3(10));

-- Add a JSON data column to the table
-- Create an index on its name field (NOTE: your collation may be different value)
ALTER TABLE t1 ADD COLUMN jdata JSON;
DESCRIBE t1;
CREATE INDEX jdata_name ON t1 ((CAST(jdata->>"$.name" AS CHAR(20)) COLLATE cp850_general_ci));

-- Delete an index (following two statements has same result)
ALTER TABLE t1 DROP INDEX uix1;
DROP INDEX uix1 ON t1;

SHOW INDEXES FROM t1;

Verifying Index Usage

You can check whether your queries really use the indexes created in the tables. For this, use the EXPLAIN statement. EXPLAIN provides information from the optimizer about how MySQL would execute a query. With the help of EXPLAIN, you can see where you should add indexes to tables so that the statement executes faster by using indexes to find rows.

EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. Here is an example using SELECT on this table test2:

CREATE TABLE test2 (
  c1 int NOT NULL,
  c2 varchar(10),
  PRIMARY KEY (c1)
);

Sample rows in table:

mysql> SELECT * FROM test2;
+-----+---------+
| c1  | c2      |
+-----+---------+
|   1 | NULL    |
|   5 | five    |
|   4 | four    |
|   3 | three   |
|   2 | two     |
...
+-----+---------+

List the table indexes and check the usage in a SELECT query:

mysql> SHOW INDEX IN test2\G
*************************** 1. row ***************************
        Table: test2
   Non_unique: 0
     Key_name: PRIMARY
  Column_name: c1
...


mysql> EXPLAIN SELECT * FROM test2 WHERE c1 >= 3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test2
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
...

mysql> EXPLAIN ANALYZE SELECT * test2 WHERE c1 >= 3\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (test2.c1 >= 3)  (cost=1.46 rows=6) (actual time=0.016..0.0251 rows=6 loops=1)
    -> Index range scan on test2 using PRIMARY over (3 <= c1)  (cost=1.46 rows=6) (actual time=0.0143..0.0224 rows=6 loops=1)

EXPLAIN ANALYZE (introduced in MySQL 8.0.18) runs a statement and produces EXPLAIN output along with timing and additional information about how the optimizer's expectations matched the actual execution.

Useful Notes

Index prefix - The initial or leading columns of a composite index. A query that references the first few columns of a composite index can use the index, even if the query does not reference all the columns in the index.
Index hints - Index hints give the optimizer information about how to choose indexes during query processing (for overriding the indexes recommended by the optimizer). Index hints apply to SELECT, UPDATE and DELETE statements.
Index extensions - InnoDB automatically extends each secondary index by appending the primary key columns to it. If a table has a column c1 as primary key and column c2 has an index, MySQL (optimizer) treats the index on c2 as c2+c1 columns.
Full table scan - A query that requires reading the entire contents of a table, rather than just selected portions using an index.
Optimizer - The MySQL component that determines the best indexes and join order to use for a query, based on characteristics and data distribution of the relevant tables. A.k.a. Query Optimizer.
Index data structures - Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Some exceptions are (a) indexes on spatial data types use R-trees, (b) MEMORY tables use hash indexes by default, and (c) InnoDB uses inverted lists for FULLTEXT indexes.
MySQL storage engines - Storage engines are MySQL components that handle the SQL operations for different table types. InnoDB is the default and most general-purpose storage engine. There are also MyISAM, MEMORY, CSV, NDB, and others. Each of these have their specific purpose. Some indexes are specific to some storage engines.

You can install MySQL Server on your computer by following the instructions from this GitHub Gist Basic steps to install MySQL 8 Server and create database data and try the above code snippets.

For further reference see MySQL 8.0 Reference Manual.

Revisiting Photography after a long (, long...) time

Prasad Saya — Wed, 22 Nov 2023 14:30:15 +0000

Couple of months back I had ideas about shooting some photos for fun and hobby. Hence, I started looking for information about the current state of photography.

I occasionally use my Android phone camera, but that is still a phone for me. A camera means you have reason to take time off or go out and do something more purposeful - photography. For me photography was (still is) technique and involving light, colors, things, nature, people, aperture, ISO, shutter speed, and exposure. Being out on your own sometimes at odd times, odd places and a satisfaction (somewhat akin to that of being on a golf course driving range on your own practicing) irrespective of the outcome.

I learnt photography long time back when I was in college, around year 1980. I was studying my Mechanical Engineering degree and I had enrolled into the college photography club. Naturally, I came to know experts in the field and I was interested.

I learnt by reading photography books, trying, taking help, and doing together with a friend. I had a camera (a rangefinder camera with built in light meter) and some borrowed equipment (a tripod, and a TLR camera). Armed with these and some trying I was able to get the best photographer prize at the annual college competition in 1982. My photos were mostly black & white, shot in the dark, and not the usual sceneries, sunsets, flowers, etc. I had enjoyed the action and learnt the basics well. It is not easy compared to today’s equipment and their capabilities.

After that I did some photography once in a while. Back in year 2001 got my own Nikon SLR camera with a 24-120mm zoom lens. That was still using photographic film, not the digital photography. The interest did not last long.

The title picture is a selfie in a mirror. It was one of first photos I shot with my recent and new camera.

Camera

Couple of months back, I started noting the digital cameras and their capabilities. After few weeks, I knew that photography technique and basics are mostly the same, but it’s the equipment, technologies, convenience, etc., are different.

I did get a book to revisit my knowledge about photography. The book is "Understanding Exposure" fourth edition, by Bryan Peterson. The book is about photography and technique (irrespective of equipment). It suited me well and I liked it, as it had the right knowledge I needed to relearn.

I also avoided any books and knowledge involving equipment about digital photography. I felt that a distraction and excitement. Though I browsed a few online videos for relevant information.

Once I was convinced that this is something I need to try, I looked for a camera with changeable lenses - a SLR or nowadays a DSLR. It needed to be a beginner level, and also interesting to me.

I found this camera - Nikon ZFC. It is a mirrorless DSLR (mirrorless DSLRs are light weight, smaller and newer tech). It can capture 21 Mega pixel digital photos. I opted for a normal 50mm f1.8 lens to go with it. It can also shoot decent videos. I got this couple of weeks back. And, a tripod earlier this week.

Initially, the new camera was a bit overwhelming. It had a user manual of 600+ pages. An array of displays, menus, submenus, settings, technology and more terminology. My camera also had manual controls for ISO, shutter speed and exposure compensation like in older film cameras giving it a unique and retro look (which I like).

I have to learn this new technology of digital photography. I know there is a good learning curve. I am learning by myself.

Initial try

Back in January 2023, I had written a post on this site Books I had read in the past decade or so.... I needed a picture with books to go with the article’s title. It was a borrowed one. I wanted to replace that photo with a real one and with the real books I had listed in the article. So, I started to get a decent picture using the new camera. I tried various shots and finally got the photo and updated on the post.

It was not easy with my new equipment. I used my study lamps for lighting. Tried various positions, settings on the camera and lighting. After a few photos I settled for one. This was little exciting too. I did not know which was the best picture of the few. The urge to post it online immediately was there.

So, on November 13th I edited the article to have the new picture. It was satisfying. The experience was good and encouraging.

The photo from initial shooting:

This one is from a tree as shot from my bedroom window (I wanted to make the background out of focus from the target):

It is not about equipment

Few weeks back I was in my kitchen putting together my breakfast. Fresh fruit is part of my diet. I get Papayas often and they are available plenty. As I cut this one, I noted a familiar shape. This was unexpected. This from the nature.

Here is a shot with my Android phone.

What next?

The weather had been cloudy and it’s been raining often lately. This is making it difficult to get out and try the camera outdoors. So is the city traffic. I am waiting to find time and opportunities to get out and experience the photography.

Access MySQL Document Store from NodeJS Application

Prasad Saya — Tue, 19 Sep 2023 08:59:29 +0000

In my previous post Using MySQL as a NoSQL Document Store - Introduction I had shown accessing MySQL Document Store using the MySQL Shell client using X DevAPI. Please refer the post for an introduction to the JSON data document store, usage of the APIs and the MySQL Shell.

In this post, I use the MySQL Connector/Node.js (NodeJS Driver) and the X DevAPI APIs in a NodeJS and ExpressJS JavaScript server-side program.

I had used MySQL Server v8.0.34 and NodeJS v18.17.1 for programming on a Windows 11 computer. The NodeJS application has the following dependencies (from the package.json):

"dependencies": {
    "@mysql/xdevapi": "8.0.33",
    "express": "^4.18.2"
 }

The npm registry for the MySQL NodeJS Driver: @mysql/xdevapi

The Data

I used the following structure for the JSON document data to store in the database. The database (schema) is called X_db and the collection is users. I am assuming these already exist. The following is the structure of each document in the collection. Note that the structure can be different for each document in a collection.

Please refer my previous post (linked at the top) for details about collection, document, the _id field, creating the collection and the schema.

{
    "_id": "1",
    "name": "Pete",
    "age": 13,
    "city": "Dallas",
    "favs": [
        "Cheese cake",
        "Skiing"
    ]
}

In this app we will make sure the name field is mandatory and is also unique within the users collection. This is by defining an index on the name field with unique and required constraints.

Start the MySQL Shell and connect to the database server. From the MySQL Shell in the default JavaScript mode:

// Create a unique and required index on the name field
mysql-js> db.users.createIndex("name", { fields: [ { field: "$.name", type: "text(20)", required: true } ], unique: true })
// List the indexes on the collection
mysql-js> session.sql("SHOW INDEX FROM x_db.users")

In case you need to drop an index, use the following syntax:

mysql-js> db.collection_name.dropIndex("index_name")

The App

The NodeJS app has two programs - index.js and database.js. The database.js has the database connection code using the MySQL NodeJS Driver APIs. The index.js is the ExpressJS web server and has the web API routes to perform CRUD (Create, Read, Update and Delete) operations on the MySQL Document Store.

The app is a barebones program with basic error handling (the data validation and detailed logging is not included for the sake of brevity). The app will show proper responses and logging in case of database connection errors, non existing schema or the collection, data errors for unique and required field constraint violations.

I have tried the web APIs using Postman software.

The APIs are:

Post request with a JSON document as body to create a user: http://localhost:3000/users/
Get request to list all users sorted by name field: http://localhost:3000/users/
Get request to read data for a specified name: http://localhost:3000/users/:name
Put request to modify the document by its _id field with the body as a JSON: http://localhost:3000/users/:id
Delete request to delete a document by its _id: http://localhost:3000/users/:id

The Code

database.js:

const mysqlx = require('@mysql/xdevapi')
const config = { host: 'localhost', port: 33060, user: 'user_name', password: 'password' }
const objects = { schema: 'x_db', collection: 'users' }

const getDbSession = async () => mysqlx.getSession(config)

const getCollection = async () => {
    const session = await getDbSession()
    const schema = session.getSchema(objects.schema)
    if (! await schema.existsInDatabase()) {
        throw new Error(`The schema ${objects.schema} doesn't exist`)
    }
    return schema.getCollection(objects.collection)
}

module.exports = { getDbSession, getCollection }

index.js:

const express = require('express')
const app = express()
const PORT = 3000
const db = require('./database.js')
app.use(express.json())

app.get('/dbinfo', async (req, res, next) => {
    try {
        const session = await db.getDbSession()
        res.send(session.inspect())
    }
    catch(err) {
        next(err)
    }
})

app.post('/users', async (req, res, next) => {
    try {
        const collection = await db.getCollection()
        const r = await collection.add(req.body).execute()
        res.send(`Inserted a document with id ${r.getGeneratedIds()}`)
    }
    catch(err) {
        next(err)
    }
})

app.get('/users/:name', async (req, res, next) => {
    try {
        const collection = await db.getCollection()
        const crsr = await collection.find('name = :param')
                                        .fields(['_id', 'name', 'city'])
                                        .bind('param', req.params.name)
                                        .execute()
        res.send(crsr.fetchAll())
    }
    catch(err) {
        next(err)
    }
})

app.get('/users', async (req, res, next) => {
    try {
        const collection = await db.getCollection()
        const crsr = await collection.find().sort("name asc").execute()
        res.send(crsr.fetchAll())
    }
    catch(err) {
        next(err)
    }
})

app.put('/users/:id', async (req, res, next) => {
    const key = Object.keys(req.body)[0]
    try {
        const collection = await db.getCollection()
        const r = await collection.modify('_id = :param')
                                    .set(key, req.body[key])
                                    .bind('param', req.params.id)
                                    .execute()
        res.send(`Updated ${r.getAffectedItemsCount()} document`)
    }
    catch(err) {
        next(err)
    }
})

app.delete('/users/:id', async (req, res, next) => {
    try {
        const collection = await db.getCollection()
        const r = await collection.remove('_id = :param')
                                    .bind('param', req.params.id)
                                    .execute()
        res.send(`Deleted ${r.getAffectedItemsCount()} document`)
    }
    catch(err) {
        next(err)
    }
})

app.use((err, req, res, next) => {
    console.log("<<<ERROR>>>")
    console.log(err)
    res.send(err.toString())
})

app.listen(PORT, () => {
    console.log(`Express app listening on port ${PORT}`)
})

References

Using MySQL as a NoSQL Document Store - Introduction

Prasad Saya — Sat, 16 Sep 2023 08:15:16 +0000

MySQL is mostly known to be SQL database server. To store JSON data it has table columns of JSON data type. In addition, MySQL can be used as a document store and perform "NoSQL" operations. This post is about getting an introduction to this feature.

Specialized clients can perform SQL as well as CRUD operations on the document database. These clients are MySQL Shell and the MySQL Connectors. MySQL Shell is an interactive interface using JavaScript, Python or SQL modes. Connectors are used for developing applications using programming languages like Java, NodeJS, Python, C++, etc.

The main components for using MySQL as document store are X Plugin, X Protocol and X DevAPI.

X Plugin enables MySQL Server to communicate with these clients. These clients communicate with a MySQL Server using X Protocol. X DevAPI is used to develop client applications.

In this post we will use MySQL Shell to perform some operations on document store.

To try the code samples you will require an installation of MySQL Server and MySQL Shell on your computer. I had used MySQL Server and Shell with the version 8.0.34 on a Windows 11 computer.

MySQL Server

In case you need to install MySQL newly, there are instructions from the GitHub Gist: Basic steps to install MySQL 8 Server and create database data

Verify X Plugin is installed on the server using the following command from the mysql client:

mysql> SHOW plugins;
+-------------------+----------+
| Name              | Status   | ...
+-------------------+----------+
...
| mysqlx            | ACTIVE   | ...
...

MySQL Shell

The next step is to install MySQL Shell software.

Requirement

Make sure you have the Visual C++ Redistributable for Visual Studio 2015. You can verify if already installed from Windows Control Panel -> Programs and Features.

Download and install

Visit the MySQL website and navigate to MySQL Community Downloads -> MySQL Shell

Starting and connecting to MySQL Server

Make sure your MySQL Server is started and running. We are using the Windows command prompt.

From the MySQL Shell install directory:

command prompt>> bin\mysqlsh
MySQL Shell 8.0.34
...
mysql-js>

By default the shell starts in JS mode.

Note the MySQL Shell is not connected to the server. The connection to MySQL Server instance is handled by a session object.

To see information about the current global session:

mysql-js> shell.status()
MySQL Shell version 8.0.34
Not Connected.

Connect to the server

mysql-js> \connect mysqlx://user_name@localhost:33060
mysql-js> shell.status()
MySQL Shell version 8.0.34
Connection Id: 28
Server version: 8.0.34 MySQL Community Server - GPL
Current user: user@localhost
Protocol version: X protocol
...

NOTE: You can also start mysqlsh with a session from command prompt:
command prompt>> mysqlsh mysqlx://user_name@localhost:33060

Exit the MySQL Shell.

mysql-js> \quit

MySQL Shell Global Objects

MySQL Shell includes a number of built-in global objects that exist in both JavaScript and Python modes. Some useful ones are the session, db and shell. Their usage is shown in this post's code samples.

db is available when the global session was established using an X Protocol connection with a default database specified, and represents that schema.
session is available when a global session is established, and represents the global session.
shell provides access to various MySQL Shell functions.

Document Store

The main concepts are the JSON Document, Collection and CRUD (Create, Read, Update and Delete) operations.

A JSON document is a data structure composed of key-value pairs. This is the fundamental structure for using MySQL as document store. The document key-value values can be simple data types, such as integers or strings, but can also contain other documents, arrays, and lists of documents.

A JSON document is represented internally using the MySQL binary JSON object, through the MySQL JSON datatype.

Note that a document does not need to have a predefined structure. A collection can contain multiple documents with different structures.

A collection is a container that is used to store JSON documents in a MySQL database. You can perform CRUD operations against a collection of documents.

Each collection has a unique name and exists within a schema. Schema is equivalent to a database.

Collection - create, list and delete

Start MySQL Shell and connect to the server.

mysql-js> db                    /* this returns empty, as no existing schema is specified with the connection */
mysql-js> \use <existing_db>    /* specify a default schema, and it must be an existing schema */

mysql-js> \sql                  /* change the mode to sql */
                                /* check what databases exist, and can create one if needed */
                                /* in SQL mode you can use mysql SQL commands */
SQL> SHOW DATABASES;        -- list databases
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
...

SQL> CREATE DATABASE x_db;  -- create a new database
SQL> \js                    -- return to JavaScript mode

mysql-js> \use x_db;                    // make the newly created database as default database
mysql-js> db
Default schema `x_db` accessible through db.

mysql-js> db.getCollections()           // list collections in the default schema
[]
mysql-js> db.createCollection("users")  // create a new collection in the database
<Collection:users>
mysql-js> db.getCollections()
[
    <Collection:users>
]
mysql-js> db.dropCollection("users")    // delete the collection

CRUD Operations on the Collection

CREATE

mysql-js> db.users.add({ name: "Jerry", age: 10 })  // insert a new document into the collection

mysql-js> db.users.find()                           // list all documents in the collection
{
    "_id": "000065017f9d0000000000000001",
    "age": 10,
    "name": "Jerry"
}

Note the _id field. The _id field is a mandatory field for each document in the collection. This acts as a primary key. It is (and must be) a unique value within the collection. Its value is set by the server (when not provided) or can be provided. It is immutable (cannot be changed or deleted).

mysql-js> db.users.add({ name: "Jill", age: 12 }, { name: "Jim", age: 9 })  // insert two documents
mysql-js> db.users.add({ name: "Jack", age: 8, city: "New York" })          // insert document with different structure
mysql-js> db.users.add({ _id: "myid_99", name: "Jamie", age: 11 })          // insert a document with a provided _id

mysql-js> db.users.count()
5

READ

db.users.find("name = 'Jack'")              // search for specific document(s) using a filter
db.users.find("name LIKE 'Ji%' AND age < 10")

The following operators can be used to specify search conditions: OR (||), AND (&&), XOR, IS, NOT, BETWEEN, IN, LIKE, !=, <>, >, >=, <, <=, &, |, <<, >>, +, -, *, /, ~, and %.

The syntax for expressions that form a search condition is the same as that of traditional MySQL. Note that you must enclose all expressions in quotes.

db.users.find().fields([ "_id", "name" ])   // project specific field(s) in output

db.users.find().fields([ "_id" ]).limit(1)  // read only one document
db.users.find().sort("age desc")            // read documents sorted by age descending

UPDATE

db.users.modify("name = 'Jack'").set("city", "Chicago")             // change Jack's city as Chicago
db.users.modify("name = 'Jack'").unset("city")                      // remove the city field for Jack
db.users.modify("true").set("info", { state: "NY", favs: [] })      // update all documents with a new object field

db.users.modify("name = 'Jack'").arrayAppend("$.info.favs", "Red")  // add a value to the array field for Jack
db.users.modify("name = 'Jim'").set("info.city", "LA")              // update an object field

DELETE

db.users.remove("name = 'Jill'");   // remove documents with a condition
db.users.remove("true").limit(1);   // remove one document
db.users.remove("true");            // remove all documents in collection

Other Notable Features

You can create indexes on the document field(s).
You can define document validation i.e., verify documents against a JSON schema. This enables that all documents in the collection have a certain predefined structure.
There are operations on the collections which work with one document only. These are getOne, replaceOne, addOrReplaceOne and removeOne. All these methods take the string _id value as a parameter. For example, db.users.getOne("myid_99")

Working with Result Sets

All CRUD operations discussed above return a result set object with various attributes.

The add, modify and remove operations return a Result class object. This has details about number of rows affected by the operation, auto generated document IDs, warnings, etc., depending on the operation.

The find operation returns a DocResult class object. This is the The fetched data set.

Using the MySQL Shell in JS mode:

// Add a new document, and verify the result object

var r = db.users.add({ name: "John" }).execute()
r.getAffectedItemsCount()   // 1
r.getGeneratedIds()         // [ "000064f987110000000000000004" ]

Note the execute method. This is to be specified when using the JavaScript code in MySQL Shell. In interactive mode this is automatic.

// Get all documents and print one document at a time:

var r = db.users.find().execute()
var doc
while (doc = r.fetchOne()) {
    print(doc)
}

// Get all documents as a list object:

var r = db.users.find().execute()
var list = r.fetchAll()
print(list)
print(list.length)
for (index in list) { 
    print(list[index].name) 
}

// And, use the list as JS array:
[...list].map(e => e.name).forEach(e => print(e, " "))

Working with Relational Tables

Perform CRUD operations on MySQL relational tables in the MySQL Shell.

First, the table(s) to work with need to be created in MySQL Shell's SQL mode or in mysql client. For example, we work with cities table in the current schema.

SQL> CREATE TABLE cities (city VARCHAR(20) NOT NULL PRIMARY KEY, state VARCHAR(2) NOT NULL);

MySQL Shell JS mode:

db.getTables()  // list all tables in the current database

db.cities.insert([ "city", "state" ]).values("New York", "NY")
db.cities.insert().values("Los Angeles", "CA")

db.cities.select()
db.cities.select(["city"]).where("state = 'NY'").orderBy("city asc")

db.cities.update().set("city", "Buffalo").where("city = 'New York'")

db.cities.delete().where("city = 'Buffalo'")

The above CRUD operation functions have the same results as when used with SQL.

Like the result set for the collections's find, the table's select also returns a Result object. You can use the fetchOne and fetchAll methods on it.

In addition, you can also run SQL operations using the session object. For example, session.sql("SELECT * FROM cities"). This returns a SqlResult class object.

Conclusion

The X DevAPI allows working with JSON data and relational table data within the same application program using the "NoSQL" syntax. This has advantages from the flexibility of the JSON document and the strict rules used with relational data.

You can also use the db.getCollectionAsTable("collection_name") to use the collection as table and use the table CRUD syntax on it. For example, db.getCollectionAsTable("users").select().

References

Learning C programming again...

Prasad Saya — Sat, 12 Aug 2023 10:10:43 +0000

I am revisiting C programming after nearly 30 years.

I first studied C programming language around 1991-1992. I was working as a programmer at a small software consulting company in Bangalore, India. I was mostly working with COBOL programming. I had taken a few days off from work and enrolled into a training program in Unix and C. The training was conducted by Digital Equipment Corporation (DEC) in India (DEC's Unix flavor was called as Ultrix).

Though I learnt the C, I never got to use it; UNIX in various flavors, yes. Few weeks back I had purchased a copy of the book, C Programming Language (second edition) by Brian W. Kernighan and Dennis M. Ritchie. I am studying the book, trying the code samples and also solving few exercises.

I completed the first chapter titled, CHAPTER 1: A Tutorial Introduction. It is an introductory chapter, with basic programming topics, examples and exercises. The topics in general are: printing hello world, declaring variables and symbolic constants, using arithmetic expressions, using for and while statements, character input and output, arrays and character arrays, functions and arguments, external variables and scope.

I had tried most of the exercises and here are eight of them from various sections in the chapter. The exercise solutions are based upon what I came to know about C from the first chapter of the book.

I have posted the code from couple of exercise solutions below. The remaining are in a GitHub Gist C Programming Exercise Solutions.

I had used the MinGW C compiler on a Windows computer to compile the programs.

Here are the sections and the exercises:

1.1 Getting Started
Exercise 1-1 Run the "Hello, world" program on your system. Experiment with leaving out parts of the program to see what error messages you get.

#include <stdio.h>

/* The Hello world program */
int main() 
{ 
    printf("Hello World!\n");
}

1.2 Variables and Arithmetic Expressions
Exercise 1-3 Modify the temperature conversion program to print a heading above the table. Solution

1.3 The For Statement
Exercise 1-5 Modify the temperature conversion program to print the table in reverse order, fom 300 degrees to 0.

#include <stdio.h>

/* Fahrenheit to celsius table - prints in reverse,
   for the fahrenheit 300 to 0 in steps of 20 */
int main()
{ 
    int fahr;

    printf("Fahrenheit\tCelsius\n");
    for (fahr = 300; fahr >= 0; fahr = fahr - 20)
        printf(" %3d\t\t%6.1f\n", fahr, (5.0 / 9.0) * (fahr - 32));
}

1.5 Character Input and Output
Exercise 1-8 Write a program to count blanks, tabs and newlines. Solution

1.6 Arrays
Exercise 1-13 Write a program to print a histogram of the lenghts of the words in its input. It is easy to draw a histogram with the bars horizontal; a vertical orientation is challenging. Solution

1.7 Functions
Exercise 1-15 Rewrite the temperature conversion program of Section 1.2 to use a function for conversion. Solution

1.9 Character Arrays
Exercise 1-19 Write a function reverse(s) that reverses the character string s. Use it to write a program that reverses its input a line at a time.

#include <stdio.h>

#define MAX_LINE_LEN    1000 /* Assume each line is limited to this length */

int get_line(char line[], int maxline);
void reverse(char s[]);

/* Program that reverses its input a line at a time.
   Uses a function reverse(s) that reverses the character string s. */
int main()
{
    char line[MAX_LINE_LEN];

    while((get_line(line, MAX_LINE_LEN)) > 0) {
        reverse(line);
        printf("%s\n", line);
    }

    return 0;
}

/* get_line: read a line into s, return length */
int get_line(char s[], int lim)
{
    int c, i;

    for (i = 0; i < lim-1 && (c = getchar()) != EOF && c != '\n'; ++i)
        s[i] = c;
    if (c == '\n') {
        s[i] = c;
        ++i;
    }
    s[i] = '\0';

    return i;
}

/* length: returns the length of string s */
int length(char s[]) 
{
    int i;

    i = 0;
    while (s[i] != '\0')
        ++i;
    return i;
}

/* copy: copy from into to; assume to is big enough */
void copy(char to[], char from[])
{
    int i;

    i = 0;
    while ((to[i] = from[i]) != '\0')
        ++i;
}

/* reverse: reverses the input string */
void reverse(char s1[])
{
    int i, j, len;

    len = length(s1); /* get length of the string */

    /* make copy of the original string */
    char s2[len];
    copy(s2, s1);

    /* make a reverse of the original string */
    j = 0;
    for (i = len-1; i >= 0; --i, ++j)
        s1[j] = s2[i];
}

1.10 External Variables and Scope
Exercise 1-23 Write a program to remove all comments from a C program. Don't forget to handle quoted strings and character constants properly. C comments do not nest. Solution

My first personal website

Prasad Saya — Thu, 06 Jul 2023 14:01:49 +0000

Few weeks back I started working on a website for myself with an introduction and some work related details. I concluded with registering the domain and hosting the site today.

The website has the usual home, posts, projects and about pages. These are HTML/CSS pages with some JavaScript coding.

I also added a Photos page with couple of photos I had taken. This page is only navigable from the About page.

Deciding what posts or content to include in the site is not an easy task. I think the website content is a constant work in progress.

You can view the website at: prasadsaya.com

Notes about a NodeJS program I wrote at work - 2nd March, 2023

Prasad Saya — Thu, 09 Mar 2023 11:52:24 +0000

The app was about an ExpressJS web server accessing MySQL database. The client is written using the EJS - a templating language that lets generate HTML using JavaScript. I will briefly touch upon some aspects of the code for the server as well as the client.

MySQL Database Queries

The database CRUD (Create, Read, Update and Delete) queries were written as stored procedures (or procedures). The procedures were called in the NodeJS program using the mysql library calls. An advantage of using the procedures is that the stored procedure is defined and stored in the database along with table, index, and other database object definitions. A call to the procedure executes it on the database server and returns the result. This also allows your application code to be clean and simple (avoiding the SQL query syntax details).

A sample procedure code looks like the following. This code updates a table row based upon a provided items table id and quantity values and returns the updated row count as a result.

DELIMITER $$
DROP PROCEDURE IF EXISTS update_item;
CREATE PROCEDURE update_item(IN id INT, IN quantity_in INT)
BEGIN
    UPDATE items
    SET quantity = quantity + quantity_in 
    WHERE  item_id = id;

    SELECT ROW_COUNT() AS updated_count;
END$$
DELIMITER ;

The NodeJS code calling the procedure:

const sql = "CALL update_item(?, ?)";
connection.query(sql, [ id, quantity ], function (error, result) {
    // work with the query result or the error object ...
})

Sometimes, I also had to use the async-await syntax for the database query code:

// this in an async function ...
try {
    const query = util.promisify(connection.query).bind(connection);
    const result = await query(sql, [ id, quantity ]);
    // ...
}
catch(error) { 
// ...

Note that the util in the above code is the NodeJS util module.

The App's Client

This is a sample EJS client code used in the app:

<%- include("header", { title: title }); %>

<div>Browse items and perform edits on them here.
  <form action="item_edit" method="post">
    <input type="hidden" name="_method" value="add" >
    <button type="submit"><i class="fa fa-plus"></i> Add Item</button>
  </form>
</div>

<table>
  <thead>
    <tr>
      <th>Id</th>
      <th>Name</th>
      <th>Quantity</th>
      <th>Action</th>
    </tr>
  </thead>
  <tbody>
    <% items.forEach(function(row) { %>
      <tr>
        <td><%= row.item_id %></td>
        <td><%= row.name %></td>
        <td><%= row.quantity %></td>
        <td>
          <form action="item" method="post">
            <input type="hidden" name="id" value=<%= row.item_id %> >
            <input type="hidden" name="_method" value="delete" >
            <button type="submit"><i class="fa fa-trash"></i></button>
          </form>
          <form action="item_edit" method="post">
            <input type="hidden" name="_method" value="edit" >
            <input type="hidden" name="id" value=<%= row.item_id %> >
            <button type="submit"><i class="fa fa-pencil"></i></button>
          </form>
        </td>
      </tr>
    <% }); %>
  </tbody>
</table>

<%- include("footer"); %>

The screenshot of the client as seen in the browser window (only the relevant rendered HTML is shown):

Comments

A typical web app has aspects of database, a web server and a browser client. The web server provides the services to access the database and send data to the client. All these together are typically referred as a full-stack application.

Writing such an app is the full-stack development. Full stack development requires skills in programming specific languages (in this case NodeJS, JavaScript), databases and the client side scripting. In addition, the knowledge of how the components of the stack connect, interact and provide specific functionality.

Notes about a NodeJS program I wrote at work - 22nd Feb, 2023.

Prasad Saya — Thu, 23 Feb 2023 01:01:32 +0000

The NodeJS program is about preparing data and sending a POST request to an API external to this app. The app receives a response from the request for further processing. An aspect of this app is making the POST request at a regular interval, for example every 6 hours. Finally, I needed to log the API calls to a file.

In this post I discuss, in brief, the various libraries used to accomplish the task.

How to make a job run every n hours?

I used a cron task scheduler library, the node-cron. The scheduler code looked like this:

const cron = require("node-cron");
const CRON = "0 0 */6 * * *";
cron.schedule(CRON, async () => {
    console.log("Making the API request.");
    // ...
});

How to make a POST request using form data?

For this functionality, I used the form-data library. This allowed sending form data with an uploaded file and also set custom headers. For example, creating the form data:

const FormData = require("form-data");
const formData = new FormData();
formData.append("mydata", "some data");
formData.append("myfile", fs.createReadStream("abc.xyz"));
const options = { method: "POST", body: formData, headers: { myhdr: "hdr value" } };

Send the request and receive a response using node-fetch using the earlier created form data:

const fetch = require("node-fetch");
const resp = await fetch("http://www.example.com/someapi", options);
const json_data = await resp.json();
// Do something with the response data here ...

An aspect to note is that the functionality (or similar) of the fetch npm package is the implementation in NodeJS 17.x fetch.

What logger?

The logging needed to be both in the console as well as to a file. The logged message included a timestamp too. I used the winston logging library. The logger code looked like this:

const winston = require("winston");
const logger = winston.createLogger({
    level: "info",
    format: winston.format.combine(winston.format.timestamp(), winston.format.json()),
    transports: [
        new winston.transports.File({ filename: "log_file_name.log" }),
        new winston.transports.Console(),  
    ],
});

logger.info({ message: "Logger started" });

Conclusion:

I had used various libraries to accomplish a task. I note that the logging is the most common task and an important aspect of developing apps.

Revisiting my first PHP app after a month and its improvements...

Prasad Saya — Mon, 13 Feb 2023 09:17:09 +0000

Last month (7th January 2023) I had posted an article on this site, Notes about my first PHP app. It was a quiz app. It was part of my learning PHP.

The present post is about writing similar app. The app's functionality is same. But, this time I had coded using some newer PHP features. Notable among them are the usage of data from a database. Another feature is the usage of a layout template with content from different actions. Finally, the app has a better look.

The Data

The quiz data is now stored in a database. The MySQL Server has a quiz database with two tables - data and options.

The data is created using SQL scripts. The app connects to the database and reads data from the tables using mysqli APIs. You will notice that the data is normalized.

The previous app's data was JSON documents stored in a file. Each JSON document represented a quiz item and had composite data types like arrays.

The App

The app has following components:

DatabaseConnection: This class extends mysqli class to create a database connection object.
DatabaseTable: An instance of this class is created for each table using the connection object. This has methods like, findAll, findById, findByColumn, etc., to access the database data.
Controller: This class takes all the DatabaseTable objects as input. An instance starts or uses an existing session. This has methods for each of the app's actions (start, play, and end). Each method returns the view template and the data to be rendered in it.
index: This is the entry point to the app. This is also the entry point to each of the app's functions. This creates the database connection, the table and controller objects for each action. Based upon the action, specific controller method is executed. The output is rendered into the app's main layout template.
home_html, play_html, end_html are the views for the start, the quiz play and the quiz end pages respectively. The layout_html is the main template.
styles.css is for better looks (or style) and onload_js.js is JavaScript to set a button's state. play_js.js has a JavaScript function that evaluates if a question is answered correctly and then makes an Ajax call to score.php to update the score (which is tracked in a session variable).

App's Screenshots:

Here are two screenshots of the app running in a browser - the landing and the quiz play pages.

Conclusion

This time in addition to object orientated classes, arrays (and its various functions like shuffle, extract, in_array) and get/post/session variables, notable PHP features used are:

spl_autoload_register for registering given function as __autoload() implementation.
Output Buffering Control functions ob_start and ob_get_clean.
MySQL Improved Extension with its object orientated APIs to connect to the database and read from the tables.

The app has a better structure and classes in reusable formats. These can be used in other applications with minor modifications. The app is structured to have more tables and more functionality. This is work in progress. The app has better looks due to improved CSS.

The GitHub repository An Example PHP App has the source code. It also has details on MySQL Server 8 installation, mysqli extension configuration, and the SQL scripts for creating the database with sample data. The app is developed using PHP 8.2 and the database is MySQL 5.5.

Books I had read in the past decade or so...

Prasad Saya — Thu, 19 Jan 2023 20:03:47 +0000

The picture above shows the actual books I have. I made this photograph today (13th Nov, 2023). I replaced the borrowed picture.

I recently noted couple of posts on this website about books (both technical and non-technical). I had some thoughts and I am writing this about what books I had read in the past 10 years or so. I had written this in one sitting and without any plan on how to organize the list and what to write.

I have been reading books of English language (mostly fiction) actively since I was in college. There is no specific pattern - I had read books as and I felt like. There were stretches of months and years I had not read any books.

In the past decade I had been reading little bit and here are some of those books. These include fictional, non-fictional and software related.

When I had an urge or time to read I found the books from various sources. Popular books lists, physical book stores, online book stores, libraries, references from other books or talks, book reviews, and also for specific purpose (this last category applies mostly to my profession).

These books include eBooks and paperbacks.

Some books are freely sourced (these are mostly from online resources like libraries), others purchased and few borrowed.

Some of these are re-reads.

Some of these are sitting on my bookshelf and some on my computer's hard drive or in the cloud.

So, what are these books? I will touch on some of them and the experience from what I can recollect now. The list is in alphabetical order by author under each category.

Free eBooks

These are a list of eBooks from free online sources. Most of these books are from the Project Gutenberg (it is an online library of free eBooks). The books are mostly fictional and popular classics. I have mentioned one book per author, but often it is more than one I had read from the same author.

Alexandre Dumas (The Count of Monte Cristo)
Anton Chekov (Short Stories)
Arthur Canon Doyle (stories about the Sherlock Holmes) - this is a re read for 3rd or 4th time
Charles Dickens (Great Expectations)
D. H. Lawrence (Sons and Lovers) - I had read the other book, the famous and once banned book - The Lady Chatterley's Lover.
Darwin, Charles (The Voyage of the Beagle)
E.M. Foster (A Room with a View) - I had watched the movie too.
Franz Kafka (Metamorphosis)
Fyodor Dostoyevsky (Brothers Karamazov)
G.K. Chesterton (The Man Who Knew Too Much)
Gabriel Garcia Marquez (One Hundred Years of Solitude)
George Eliot (Middlemarch)
H. G. Wells (War of the Worlds)
Harriet Beecher Stowe (Uncle Tom's Cabin)
Jack London (Call of the Wild) - I had read the White Fang long time back.
James Joyce (The Dubliners) - I had read the Ulysses too (by the same author).
Kate Chopin (The Awakening, and Selected Short Stories)
L. M. Montgomery (Anne of Green Gables)
Leo Tolstoy (War and Peace)
Margaret Mitchell (Gone with the wind) - This one I had found in an online book library from Australia.
Mark Twain (Adventures of Tom Sawyer) - this was a re read for the 3rd time.
Miguel de Cervantes (Don Quixote)
Nevil Shute (Trustee from Toolroom) - This I found from a reference in another book.
Oscar Wilde (The Importance of Being Earnest)
P. G. Wodehouse (My Man Jeeves)
Robert Louis Stevenson (Treasure Island)
Rudyard Kipling (The Man Who Would Be King)
Shaw, Bernard (Pygmalion)
William Shakesphere (Much Ado about Nothing) - Yes, I had watched the movie, first.

Fictional eBooks

Ayn Rand (The Fountainhead)
J. D. Salinger (Catcher on the Rye)
Larry McMurty (Lonesome Dove) - A long story about cowboys.
Pearl S. Buck (The Good Earth) - A story of rural Chinese people and their lives during 20th century

Fictional Paperbacks

Aleksandr Solzhenitsyn (The First Circle) - A Russian language book translated to English. I came to know about this book from its reference in another book.
Alex Haley (Roots).
Banana Yoshimoto (Kitchen) - An English translation of a Japanese book.
Eleanor H. Porter (Polyanna) - Had picked up at a bookstore casually. It is about an orphaned child.
Fuminori Nakamura (The Thief) - An English translation of a Japanese book. Had picked up at a book store casually. This book is a hard cover.
J. R. R. Tolkien (The Hobbit) - I haven't read the LOTR, yet.
James Redfield (The Celestine Prophecy) - I came to know about this book as I watched the move made from this book at a retreat.
Jandy Nelson (I'll Give You the Sun) - This is one of the most recent reads. This is a story about teenagers.
O. Henry (100 Selected Stories) - Had picked up in an online store casually. I had liked its colorful cover - An Illustration of Manhattan. The book is all short stories. It has a beautiful story called as "The Gift of the Magi".
Richard Bach (Jonathan Livingston Seagull, a story) - Small book under 90 pages. Had picked up in an online store casually. Had read it more than once.
Ruzbeh N. Bharucha (The Fakir) - Came to know about the book from a library.
Saul Bellow (Herzog) - Came to know about this book from a book listing.
Toni Morrison (The Bluest Eye) - Had picked up at a bookstore casually.
W. Somerset Maugham (Of Human Bondage) - Had picked up at a bookstore casually.

Non Fiction Books

These are related to psychology, self help, biography, philosophy and religion. All are paperbacks, except the Quiet and Walden which I got from the iTunes store. Walden was a free copy.

Anne Fadiman (Ex Libris) - It is about books, reading books and the experience.
Carl Sagan (Cosmos)
Carlo Rovelli (Seven Brief Lessons on Physics)
Dacher Keltner (Born to be Good) - This book is about human feelings.
David Herbert Donald (Lincoln) - A bigraphical book on Abraham Lincoln.
Ekhart Tolle (The Power of Now) - Teachings from a contemporary spiritual teacher.
Eknath Eswaran (The Dhammapada) - This is about Lord Buddha's teachings.
Harini Nagendra and Seema Mundoli (Cities and Canopies - Trees in Indian Cities)
His Holiness Dalai Lama of Tibet (Freedom in Exile) - An autobiographical book.
J. Krishnamurti (Happy is the Man who is Nothing - Letters to a Young Friend) - A tiny book of less than 70 pages.
James Allen (A Man Thinketh)
James Doty (Into the Magic Shop) - A biographical book, I came to know about from an interview the author had given in a wellness magazine. One of my recent reads.
M. K. Gandhi (My Experiments with Truth) - An autobiographical book.
Malcolm Gladwell (What the Dog Saw) - Came to know about the book from a library and had found the title rather amusing.
Max Muller (Ramakrishna, his life and sayings)
Michael Singer (The Untethered Soul - A journey beyond yourself) - This is one of those books I haven't completed reading yet, though I got it couple of years back.
Nelson Mandela (Long Walk to Freedom) - An autobiographical book.
Paul Pearsall (The Heart's Code) - A beautiful book about human heart (the physical heart too).
Susan Cain (Quiet) - This book is to get to know more about quiet people.
Swami Vivekananda (Complete works - 9 volumes) - These I read over a period of 10 months.
Thomas A. Harris (I'm Ok. You're OK)
Thoreau, Henry David (Walden)

Poetry

Rabindranath Tagore (Gitanjali) - This is the only poetry book I had ever read (other than from the school text books). This is a paperback I had purchased from a bookstore rather casually.

Books on Software

This list does not specify the author and the title of the books. It is a general listing of topics only.

Programming: Java EE, Java 7, 8 and 9, Spring, JavaServer Faces, Haskell, Golang, NodeJS, JavaScript, HTML & CSS, Hadoop, MySQL.
Software Engineering: Data Structures & Algorithms, Unit Testing, Refactoring, Clean Code, Object Oriented Analysis & Design, UML.

Notes about my first PHP app

Prasad Saya — Sat, 07 Jan 2023 07:06:08 +0000

This is my first app written using PHP. I have been studying and trying coding PHP casually for the last couple of weeks. I am using the PHP Manual and other online free resources for my study.

I am an experienced programmer using other languages. I am able to select some basic and intermediate topics and was trying the code in small scripts. On the way, I thought of building a small example app. This would allow me try some features as I build the functionality for the app. It was a quiz app.

In this post I talk about the app and the PHP features I had used.

The Data
The App
Utility Files
Data Exceptions
Development
PHP Features
Conclusion

The Data

Like most of apps there is data, the quiz data. This is a Json file, quiz_data.txt, with an array of documents. Each document has the quiz question, options, option type, answers, and notes fields.

Here is a sample Json document:

{
  "id": 1,
  "question": "Is <code>1800</code> a leap year?.",
  "type": "S",
  "options": [
    "(a) True",
    "(b) False"
  ],
  "answers": [
    "b"
  ],
  "notes": [
    "False.",
    "A leap year is divisible by <code>4</code>, but not divisible by <code>100</code>.",
    "When divisible by <code>100</code>, must also be divisible by <code>400</code>."
  ]
}

I had used the PHP functions file_get_contents and json_decode to read the Json file and convert documents as associate arrays. This array data is used in the app.

The App

The app's code is in few files. These are .php, .js, .css and .txt files. Some of the PHP files are for starting the app, navigating to other pages and for performing user interaction. Other PHP files are utility files. These have class, function and constant definitions.

The app starter PHP script is the quiz_start.php. This reads the Json file and prepares data for using in the app. This page has navigation to the following quiz_form.php page.

The quiz_form page shows the quiz questions, allows user to select answers, checks the correctness of the selections, and then navigates to the next question. Finally, when the questions are exhausted, shows a result page quiz_result.php. That’s the end of the app. The user has an option to navigate back to the starter page from here or quit.

The user can also navigate to the starter page from quiz_form page.

The starter page also starts a session. The quiz data array is stored in a $_SESSION automatic global variable for usage in the app's other pages. Other data tracked as session variables are the quiz data counter and correct answers counter. These are used in accessing the quiz question to be displayed and for the result page to show the percentage success.

The quiz_form page reads the sessions variables and updates the counters. There is also a quiz_app.js JavaScript file with a function to evaluate the answer for each question. The user selects the answers from the options and clicks the Answer JavaScript button. This executes the JS function and shows if the user selections were correct or not.

Utility Files

These are the included files. These are accessed in the PHP pages using the require or include expressions. The app has three include files: file_util.php, exception_util.php and constants.php.

These have definitions of FileUtil, ExceptionUtil and Constants classes.

The FileUtil class has a function that reads the Json file, decodes it to PHP array and returns the array. The ExceptionUtil has a function to handle the exceptions that can occur in accessing, reading, and decoding the input file data. Constants class defines a set of constants used throughout the app (for example, the input Json data file name).

Data Exceptions

The app starts with reading the Json file.

There is a possibility that the input file may not exist (or have an incorrect name), or is empty or has incorrectly formatted Json. These three exceptional situations are handled in the app. In these scenarios the exception handling code shows appropriate message on the starter page and exits the app.

Development

The app is developed using PHP 8.2.0, Apache HTTP Server 2.4, HTML, CSS and JavaScript on a Windows computer.

The source code is structured in the directories (dir) as follows:

app root (dir)
    quiz_start.php
    quiz_form.php
    quiz_result.php
    redirect_start.php
    data (dir)
        quiz_data.txt
    public (dir)
        quiz_app.js
        quiz_app_styles.css
    includes (dir)
        constants.php
        file_util.php
        exception_util.php

PHP Features

These are some notable functions, class, variables, type, class, expression and OO features used in the app.

From the PHP Manual:

Functions: file_get_contents, json_decode, iconv, session_start, isset, header
Variables: $_SESSION, $_POST
Type: Arrays
Class: JsonException
Expression: require
OO Features: Classes, Static methods and Class constants
Configuration: include path

Conclusion

This is a picture of the app running in a web browser. It’s the quiz_form page. It shows the quiz data as the user selects the answer options and clicks the Answer button.

Writing the code as I was studying and learning helped me grasp the PHP features well. These are some of the features I had used in this app. Until now I am feeling quite good about the PHP.

Here is the app's source code at the GitHub: a-demo-quiz-app-using-php.

That said, there are two features I had explored but didn’t include in coding the app.

The first is the namespaces.

The second is usage of classes and objects for the quiz data. This also meant serialization of objects and the implementation of the two magic methods __serialize and __unserialize (for the object to be used as session variable). This coding worked fine, but it is more code and processing. I will be working on it further and try to use this efficiently.

Notes about a website and apps

Prasad Saya — Fri, 23 Dec 2022 15:36:36 +0000

As a software engineer you encounter various things - technologies for sure. As you learn, you also want to try those and share and show those. This is part of the experience. This post is about one such experience. This has various aspects and lets see what they are.

Back in year 2012, I was studying to upgrade my Java programmer certification to Java 7 (this certification was conducted by Oracle Corporation and was known as OCPJP). It was a challenging toipc. Java had included many newer features in Java 7 - newer language features, NIO 2, improvements in JDBC and concurrency. The topics were huge. I had to write lots of sample code for learning the features. My learning references were Java Language Specification, Java API docs and other online references. I was not using any preparation guides, but I had some prior knowledge of Java programming.

After completing the exam, I had to do something with all the material and the exam experience. I had this idea that I can share some of this with other Java programmers. So, this idea developed into a quiz app. I went on to create some quiz data, and wrote a Java Swing desktop app. The app was a quiz app.

How do I share this app with others?

After some research, I wanted to create a website and share this app. The result was a website hosted on a Java enabled webserver. The desktop quiz app was deployed as a Java Web Start application. Java Web Start technology allowed deploy a Java desktop app on a Java enabled webserver and have the app invoked on a website as a link.

The app was called Java Quiz Player.

Next, I went on and got a domain "javaquizplayer.com" for the website. It took sometime to figure the name and it sounded alright to me. I also found a Java webserver hosting provider to deploy the website and the app.

Finally, I had the website online on 1st November, 2012. It was a basic website with one page and with a few lines explaining the website and the app. It had one link to start the app. It was also the first time wrote HTML and CSS. To run the app you needed a JRE (Java Runtime) on your computer.

I went on to share the website link on some of the online forums and hence fulfilled my idea about sharing.

The website was http://www.javaquizplayer.com. The first installment of it looked like this:

Another thing I did was to get a copyright registration certificate for the website content as well as the computer programs.

Further in year 2013, I started writing few blog posts on my website.

Now, the website had more than one page. This was followed by an online Java quiz as a web app. The online quiz app was a Java web app written using Servlets, JSP, HTML, CSS and JavaScript. The website itself was self built with HTML4 and CSS.

As I wrote more, I was sharing these posts and quiz with other forums. It was a rich experience for me. Experience to create, learn, improve and share.

In year 2016, I started writing some demo desktop apps with detailed articles explaining the complete development of the apps. The apps were mostly Java Swing or JavaFX for GUI and database for storing the app's data. The databases included H2, HSQLDB, Apache Derby, MongoDB and used JPA, Spring JDBC, etc., for data access. These were posted as Examples on my website. There were more blog posts including some Java EE related articles.

I also started writing on other websites and forums.

As part of my programming journey, I was also attending various meetups held locally. These were on various technologies and Java was one of my favoured topic. At one of the meetups in year 2016 or 2017, a Dr. Venkat Subramaniam made a presentation on Java 8, Functional Programming, its features, advantages, usage, etc. It was a great presentation, I was impressed and taken to Java 8 immediately.

Thus, I started learning functional programming, not of Java, but using Haskell. After a couple of books and some coding I was confident about functional programming. Now, I started learning Java's functional programming features - the streams, functions, lambdas, method reference expressions, their application and usage in collections, file IO, etc. Then, I took the Java 8 upgrade certification successfully.

That done, I wrote a quiz cards app. The content was the Java 8's functional programming topics and related features as I had studied for the Java 8 certification. It was JavaFX GUI app and was deployed as a Java Web Start app on my website. The app was called as Java Quiz Card Player. This was in September 2017.

Since then, I had couple more articles and some refinements (HTML5 and better CSS is one of them) for the website and its content. The website's landing page as of now:

It was interesting for me to look back some ten years before and noted that lot of things happened during a website and its content.

Functional programming along with Object-Oriented capabilities makes Java a versatile programming language to learn and use. Also, notably the newer programming paradigm like Reactive programming is applied to Java too, the RxJava, and I came to learn little bit of that too.

DEV Community: Prasad Saya

Indexes in MySQL – Notes

Index Types

Creating Indexes

Verifying Index Usage

Useful Notes

Revisiting Photography after a long (, long...) time

Camera

Initial try

It is not about equipment

What next?

Access MySQL Document Store from NodeJS Application

The Data

The App

The Code

database.js:

index.js:

References

Using MySQL as a NoSQL Document Store - Introduction

MySQL Server

MySQL Shell

Requirement

Download and install

Starting and connecting to MySQL Server

Connect to the server

MySQL Shell Global Objects

Document Store

Collection - create, list and delete

CRUD Operations on the Collection

CREATE

READ

UPDATE

DELETE

Other Notable Features

Working with Result Sets

Working with Relational Tables

Conclusion

References

Learning C programming again...

My first personal website

Notes about a NodeJS program I wrote at work - 2nd March, 2023

MySQL Database Queries

The App's Client

Comments

Notes about a NodeJS program I wrote at work - 22nd Feb, 2023.

How to make a job run every n hours?

How to make a POST request using form data?

What logger?

Conclusion:

Revisiting my first PHP app after a month and its improvements...

The Data

The App

App's Screenshots:

Conclusion

Books I had read in the past decade or so...

Free eBooks

Fictional eBooks

Fictional Paperbacks

Non Fiction Books

Poetry

Books on Software

Notes about my first PHP app

Contents

The Data

The App

Utility Files

Data Exceptions

Development

PHP Features

Conclusion

Notes about a website and apps