Introduction

The Web storage api is a set of mechanisms that enable browsers to store key-value pairs. Before HTML5, application data had to be sorted in cookies, included in every server request. Its intended to be far more user-friendly than using cookies.

Web storage is more secure, and large amounts of data can be stored locally, without affecting website performance.

There are 2 types of web storage,

1. Local Storage
2. Session Storage

We already have cookies. Why additional objects?

Unlike cookies, web storage objects are not sent to server with each request. Because of that, we can store much more. Most modern browsers allow at least 5 megabytes of data (or more) and have settings to configure that.

Also unlike cookies, the server can’t manipulate storage objects via HTTP headers. Everything’s done in JavaScript.The storage is bound to the origin (domain/protocol/port triplet). That is, different protocols or subdomains infer different storage objects, they can’t access data from each other.

In this guide, you will learn/refresh about LocalStorage.

LocalStorage

The localStorage is property of the window (browser window object) interface allows you to access a Storage object for the Document’s origin; the stored data is saved across browser sessions.

1. Data is kept for a longtime in local storage (with no expiration date.). This could be one day, one week, or even one year as per the developer preference ( Data in local storage maintained even if the browser is closed).
2. Local storage only stores strings. So, if you intend to store objects, lists or arrays, you must convert them into a string using JSON.stringfy()
3. Local storage will be available via the window.localstorage property.
4. What’s interesting about them is that the data survives a page refresh (for sessionStorage) and even a full browser restart (for localStorage).

Functionalities

// setItem normal strings
window.localStorage.setItem("name", "goku");

// getItem 
const name = window.localStorage.getItem("name");
console.log("name from localstorage, "+name);

// Storing an Object without JSON stringify

const data = {
  "commodity":"apple",
  "price":43
};
window.localStorage.setItem('commodity', data);
var result = window.localStorage.getItem('commodity');
console.log("Retrived data without jsonified, "+ result);

// Storing an object after converting to JSON string. 
var jsonifiedString = JSON.stringify(data);
window.localStorage.setItem('commodity', jsonifiedString);
var result = window.localStorage.getItem('commodity');
console.log("Retrived data after jsonified, "+ result);

// remove item 
window.localStorage.removeItem("commodity");
var result = window.localStorage.getItem('commodity');
console.log("Data after removing the key "+ result);

//length
console.log("length of local storage " + window.localStorage.length);

// clear
window.localStorage.clear();
console.log("length of local storage - after clear " + window.localStorage.length);

When to use Local Storage

1. Data stored in Local Storage can be easily accessed by third party individuals.
2. So its important to know that any sensitive data must not sorted in Local Storage.
3. Local Storage can help in storing temporary data before it is pushed to the server.
4. Always clear local storage once the operation is completed.

Where the local storage is saved ?

Windows

• Firefox: C:\Users\\AppData\Roaming\Mozilla\Firefox\Profiles\\webappsstore.sqlite, %APPDATA%\Mozilla\Firefox\Profiles\\webappsstore.sqlite
• Chrome: %LocalAppData%\Google\Chrome\User Data\Default\Local Storage\

Linux

• Firefox: ~/.mozilla/firefox//webappsstore.sqlite
• Chrome: ~/.config/google-chrome/Default/Local Storage/

Mac

• Firefox: ~/Library/Application Support/Firefox/Profiles//webappsstore.sqlite, ~/Library/Mozilla/Firefox/Profiles//webappsstore.sqlite
• Chrome: ~/Library/Application Support/Google/Chrome//Local Storage/, ~/Library/Application Support/Google/Chrome/Default/Local Storage/

Downside of Localstorage

The majority of local storage’s drawbacks aren’t really significant. You may still not use it, but your app will run a little slower and you’ll experience a tiny developer inconvenience. Security, however, is distinct. Knowing and understanding the security model of local storage is crucial since it will have a significant impact on your website in ways you might not have anticipated.

Local storage also has the drawback of being insecure. In no way! Everyone who stores sensitive information in local storage, such as session data, user information, credit card information (even momentarily! ), and anything else you wouldn’t want shared publicly on social media, is doing it incorrectly.

The purpose of local storage in a browser for safe storage was not intended. It was intended to be a straightforward key/value store for strings only that programmers could use to create somewhat more complicated single page apps.

General Preventions

1. For example, if we are using third party JavaScript libraries and they are injected with some scripts which extract the storage objects, our storage data won’t be secure anymore. Therefore it’s not recommended to save sensitive data as
   • Username/Password
   • Credit card info
   • JWT tokens
   • API keys
   • Personal info
   • Session ids
2. Do not use the same origin for multiple web applications. Instead, use subdomains since otherwise, the storage will be shared with all. Reason is, for each subdomain it will have an unique localstorage; and they can’t communicate between subdomain instances.
3. Once some data are stored in Local storage, the developers don’t have any control over it until the user clears it. If you want the data to be removed once the session ends, use SessionStorage.
4. Validate, encode and escape data read from browser storage
5. Encrypt data before saving

As part of the ACID Series, i am refreshing on consistency. In this blog, i jot down notes on consistency (correctness) in postgres database.

What is Consistency?

Consistency ensures that a transaction brings the database from one valid state to another, adhering to predefined rules such as constraints, triggers, and relational integrity. If a transaction violates these rules, it is aborted, and the database remains unchanged. This guarantees that only valid data exists in the database.

Consistency works together with other ACID properties:

• Atomicity ensures the “all-or-nothing” execution of a transaction.
• Isolation ensures transactions don’t interfere with each other.
• Durability guarantees committed transactions persist despite system failures

Key Aspects of Consistency in PostgreSQL

1. Constraints
   • Primary Key: Ensures uniqueness of rows.
   • Foreign Key: Maintains referential integrity.
   • Check Constraints: Enforces custom business rules.
   • Not Null: Ensures that specific columns cannot have null values.
2. Triggers
   • Custom logic executed before or after specific database events.
3. Rules
   • Enforce application-specific invariants on the database.
4. Transactions
   • Changes are made in a controlled environment, ensuring consistency even in the event of errors or system failures.

Practical Examples of Consistency in PostgreSQL

1. Primary Key Constraint

Ensures that no two rows in a table have the same primary key value.

CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    account_holder_name VARCHAR(255) NOT NULL,
    balance NUMERIC(15, 2) NOT NULL CHECK (balance >= 0)
);

-- Attempt to insert duplicate primary keys.
INSERT INTO accounts (account_id, account_holder_name, balance)
VALUES (1, 'Alice', 1000.00);

INSERT INTO accounts (account_id, account_holder_name, balance)
VALUES (1, 'Bob', 2000.00); -- This will fail.

2. Foreign Key Constraint

Enforces referential integrity between tables.

CREATE TABLE transactions (
    transaction_id SERIAL PRIMARY KEY,
    account_id INT NOT NULL REFERENCES accounts(account_id),
    amount NUMERIC(15, 2) NOT NULL,
    transaction_type VARCHAR(10) NOT NULL CHECK (transaction_type IN ('credit', 'debit')),
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Attempt to insert a transaction for a non-existent account.
INSERT INTO transactions (account_id, amount, transaction_type)
VALUES (999, 500, 'credit'); -- This will fail.

3. Check Constraint

Validates custom business rules.

-- Ensure account balance cannot go negative.
INSERT INTO accounts (account_holder_name, balance)
VALUES ('Charlie', -500); -- This will fail due to the CHECK constraint.

4. Trigger for Business Logic

Ensures derived data or additional checks are implemented.

CREATE OR REPLACE FUNCTION enforce_minimum_balance()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.balance < 0 THEN
        RAISE EXCEPTION 'Balance cannot be negative';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_balance_before_insert
BEFORE INSERT OR UPDATE ON accounts
FOR EACH ROW EXECUTE FUNCTION enforce_minimum_balance();

-- Attempt to update an account with a negative balance.
UPDATE accounts SET balance = -100 WHERE account_id = 1; -- This will fail.

5. Transactions to Maintain Consistency

A transaction groups multiple operations into a single unit, ensuring all succeed or none.

BEGIN;

-- Deduct from sender's account.
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;

-- Credit to receiver's account.
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

-- If any operation fails, rollback the transaction.
COMMIT;

If the system crashes before the COMMIT, the database remains unchanged, ensuring consistency.

How Consistency Works with Other ACID Properties

1. With Atomicity: If any step in a transaction violates a constraint, the entire transaction is rolled back, ensuring that the database remains consistent.
2. With Isolation: Concurrent transactions operate independently, preventing inconsistent states caused by interference.
3. With Durability: Once a transaction is committed, its consistency guarantees persist even in the event of a crash.

Benefits of Consistency

1. Data Integrity: Prevents invalid data from being stored.
2. Application Reliability: Reduces the need for additional application-level checks.
3. Simplified Maintenance: Developers can rely on the database to enforce business rules and relationships.
4. Error Prevention: Constraints and triggers act as safeguards, catching mistakes early.

Database models define the structure, relationships, and operations that can be performed on a database. Different database models are used based on the specific needs of an application or organization. Here are the most common types of database models:

1. Hierarchical Database Model

• Structure: Data is organized in a tree-like structure with a single root, where each record has a single parent but can have multiple children.
• Usage: Best for applications with a clear hierarchical relationship, like organizational structures or file systems.
• Example: IBM’s Information Management System (IMS).
• Advantages: Fast access to data through parent-child relationships.
• Disadvantages: Rigid structure; difficult to reorganize or restructure.

2. Network Database Model

• Structure: Data is organized in a graph structure, where each record can have multiple parent and child records, forming a network of relationships.
• Usage: Useful for complex relationships, such as in telecommunications or transportation networks.
• Example: Integrated Data Store (IDS).
• Advantages: Flexible representation of complex relationships.
• Disadvantages: Complex design and navigation; can be difficult to maintain.

3. Relational Database Model

• Structure: Data is organized into tables (relations) where each table consists of rows (records) and columns (fields). Relationships between tables are managed through keys.
• Usage: Widely used in various applications, including finance, retail, and enterprise software.
• Example: MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server.
• Advantages: Simplicity, data integrity, flexibility in querying through SQL.
• Disadvantages: Can be slower for very large datasets or highly complex queries.

4. Object-Oriented Database Model

• Structure: Data is stored as objects, similar to objects in object-oriented programming. Each object contains both data and methods for processing the data.
• Usage: Suitable for applications that require the modeling of complex data and relationships, such as CAD, CAM, and multimedia databases.
• Example: db4o, ObjectDB.
• Advantages: Seamless integration with object-oriented programming languages, reusability of objects.
• Disadvantages: Complexity, not as widely adopted as relational databases.

5. Document-Oriented Database Model

• Structure: Data is stored in document collections, with each document being a self-contained piece of data often in JSON, BSON, or XML format.
• Usage: Ideal for content management systems, real-time analytics, and big data applications.
• Example: MongoDB, CouchDB.
• Advantages: Flexible schema design, scalability, ease of storing hierarchical data.
• Disadvantages: May require denormalization, leading to potential data redundancy.

6. Key-Value Database Model

• Structure: Data is stored as key-value pairs, where each key is unique, and the value can be a string, number, or more complex data structure.
• Usage: Best for applications requiring fast access to simple data, such as caching, session management, and real-time analytics.
• Example: Redis, DynamoDB, Riak.
• Advantages: High performance, simplicity, scalability.
• Disadvantages: Limited querying capabilities, lack of complex relationships.

7. Column-Family Database Model

• Structure: Data is stored in columns rather than rows, with each column family containing a set of columns that are logically related.
• Usage: Suitable for distributed databases, handling large volumes of data across multiple servers.
• Example: Apache Cassandra, HBase.
• Advantages: High write and read performance, efficient storage of sparse data.
• Disadvantages: Complexity in design and maintenance, not as flexible for ad-hoc queries.

8. Graph Database Model

• Structure: Data is stored as nodes (entities) and edges (relationships) forming a graph. Each node represents an object, and edges represent the relationships between objects.
• Usage: Ideal for social networks, recommendation engines, fraud detection, and any scenario where relationships between entities are crucial.
• Example: Neo4j, Amazon Neptune.
• Advantages: Efficient traversal and querying of complex relationships, flexible schema.
• Disadvantages: Not as efficient for operations on large sets of unrelated data.

9. Multimodel Database

• Structure: Supports multiple data models (e.g., relational, document, graph) within a single database engine.
• Usage: Useful for applications that require different types of data storage and querying mechanisms.
• Example: ArangoDB, Microsoft Azure Cosmos DB.
• Advantages: Flexibility, ability to handle diverse data requirements within a single system.
• Disadvantages: Complexity in management and optimization.

10. Time-Series Database Model

• Structure: Specifically designed to handle time-series data, where each record is associated with a timestamp.
• Usage: Best for applications like monitoring, logging, and real-time analytics where data changes over time.
• Example: InfluxDB, TimescaleDB.
• Advantages: Optimized for handling and querying large volumes of time-stamped data.
• Disadvantages: Limited use cases outside of time-series data.

11. NoSQL Database Model

• Structure: An umbrella term for various non-relational database models, including key-value, document, column-family, and graph databases.
• Usage: Ideal for handling unstructured or semi-structured data, and scenarios requiring high scalability and flexibility.
• Example: MongoDB, Cassandra, Couchbase, Neo4j.
• Advantages: Flexibility, scalability, high performance for specific use cases.
• Disadvantages: Lack of standardization, potential data consistency challenges.

Summary

Each database model serves different purposes, and the choice of model depends on the specific requirements of the application, such as data structure, relationships, performance needs, and scalability. While relational databases are still the most widely used, NoSQL and specialized databases have become increasingly important for handling diverse data types and large-scale applications.

DuckDB's support for custom functions is a crucial feature that allows users to extend the database's capabilities by incorporating their logic and operations. Custom functions are user-defined functions (UDFs) that can be implemented in languages such as Python and then seamlessly integrated into DuckDB. This extensibility is invaluable when users encounter specific analytical challenges not addressed by the built-in functions. For instance, SQL often struggles to infer datetime formats, leading to the need for complex case-when statements. The parse_dates custom function showcased here, leveraging Pandas capabilities, becomes a powerful solution to overcome this limitation.

The parse_dates Function

The parse_dates function, in the provided Python code, is a practical example of a custom function designed to handle date parsing within DuckDB. This function leverages the popular Pandas library to parse dates based on user-defined formats. The flexibility of the function allows users to specify date formats and handles different scenarios gracefully, using Pandas' pd.to_datetime method.

def parse_dates(col, fmt):
    """
    Method to parse the dates based on the format provided,
    this will be created as a UDF in DuckDB
    """
    try:
        if fmt[0].lower() == "y":
            return pd.to_datetime(col, yearfirst=True, errors="coerce")
        if fmt[0].lower() == "m":
            return pd.to_datetime(col, dayfirst=True, errors="coerce")
    except (IndexError, ValueError):
        pass
    return None

This function is particularly useful in scenarios where the date formats in the dataset might vary, providing a flexible solution for date parsing within DuckDB.

Integrating parse_dates into DuckDB

The process of integrating the parse_dates function into DuckDB involves creating a corresponding function within the database. The create_function method checks whether the function already exists and, if not, registers it with DuckDB. The provided SQL query ensures that there are no duplicate entries before attempting to create the function.

def create_function(conn):
    """
    Create a function in DuckDB. Currently, it's hardcoded
    we can modify later based on the use case
    """
    function_check = """SELECT DISTINCT  function_name
                        FROM duckdb_functions()
                        WHERE lower(function_type) = 'scalar'
                        AND lower(function_name) in ('parse_dates')
                        ORDER BY function_name;"""

    function_check_output = conn.query(function_check)
    try:
        if not function_check_output:
            conn.create_function("parse_dates", parse_dates, [VARCHAR, VARCHAR], TIMESTAMP)
    except (duckdb.Error, ValueError) as error:
        raise ValueError(
            f"Failed to create function 'parse_dates': {str(error)}"
        ) from error

This step ensures that the custom function is available for use in DuckDB's SQL queries.

Unregistering the Custom Function

The unregister_function method allows users to remove the custom function from DuckDB. If, for any reason, users want to unregister the parse_dates function, this method facilitates the removal of the function from DuckDB.

def unregister_function(conn):
    """
    Unregister a function in DuckDB.
    """
    conn.remove_function("parse_dates")

This feature emphasizes the dynamic nature of DuckDB, allowing users to manage and tailor the set of available functions according to their evolving needs.

Conclusion

The integration of custom functions, such as the parse_dates example, exemplifies DuckDB's commitment to providing users with a customizable and extensible platform for data analysis. As users explore and create their custom functions, they gain the ability to enhance DuckDB's capabilities to address unique challenges in data analysis workflows. Custom functions not only open up new possibilities but also empower users to shape their analytical environment to suit their specific requirements, making DuckDB a versatile and user-friendly database for diverse analytical tasks.

Definition for SQL

Structured query language (SQL) is a programming language for storing and processing information in a relational database. A relational database stores information in tabular form, with rows and columns representing different data attributes and the various relationships between the data values. You can use SQL statements to store, update, remove, search, and retrieve information from the database. You can also use SQL to maintain and optimize database performance.

In this article,we are going to discuss how to create a table in a database,insert the values in the table.First start the mariadb server,follow the steps in the article if mariadb is not installed in your system.Refer the Installation guide for mariadb is below link.

https://opendiaryofgowthamg.wordpress.com/2024/02/14/installation-mariadb/

After opening the terminal,we need to know the databases presented in mariadb.So that,use..,

SHOW DATABASES;

It will show the available databases.Next we need to create a database and switch into that new database.Using Capital letters for keywords is a good practice in SQL.

CREATE DATABASE database_name;
USE database_name;

Now,we created a database and switched into it.We need to create a table for this database.

CREATE TABLE table_name (Sno int(3),Name varchar (30),Mobile No int (13));

We created a new table with three columns.
Sno has Integer datatype and it can stores upto 3 integer values (i.e up-to 999).
Name has varchar datatype and it stores up-to 30 Characters.
Mobile No has Integer datatype and stores up-to 13 integer value

DESC table_name;

This command allow us to view the description of that table.Practice is the database name which i was created and testing is the name i given to this table.We will discuss about NULL,Key,Default,Extra are in the later sessions.

This is how we create a simple basic table.Let’s see hoe to insert values to this table.

INSERT INTO table_name values (1,"person_1",1231231231);
INSERT INTO table_name values (2,"person_2",97979797),(3,"person_3",34343434),(4,'person_4',456789123);

First line shows,inserting only a single row data to the table.Whereas,second one shows multi data insertion to the table.We can use either Double Quote (” “) or Single Quote (‘ ‘) to the Character datatype.

Let’s see how to view this table entirely or only a specific column.

SELECT * FROM table_name;
SELECT column_name FROM table_name;

The first image shows the full table contents.
Second image shows only a particular column named “books”.

EXIT;

Exit from mariadb.

We will discuss how to edit,update the table in next article.

That’s it..!

Definition for SQL

Structured query language (SQL) is a programming language for storing and processing information in a relational database. A relational database stores information in tabular form, with rows and columns representing different data attributes and the various relationships between the data values. You can use SQL statements to store, update, remove, search, and retrieve information from the database. You can also use SQL to maintain and optimize database performance.

In this article,we are going to discuss how to create a table in a database,insert the values in the table.First start the mariadb server,follow the steps in the article if mariadb is not installed in your system.Refer the Installation guide for mariadb is below link.

https://opendiaryofgowthamg.wordpress.com/2024/02/14/installation-mariadb/

After opening the terminal,we need to know the databases presented in mariadb.So that,use..,

SHOW DATABASES;

It will show the available databases.Next we need to create a database and switch into that new database.Using Capital letters for keywords is a good practice in SQL.

CREATE DATABASE database_name;
USE database_name;

Now,we created a database and switched into it.We need to create a table for this database.

CREATE TABLE table_name (Sno int(3),Name varchar (30),Mobile No int (13));

We created a new table with three columns.
Sno has Integer datatype and it can stores upto 3 integer values (i.e up-to 999).
Name has varchar datatype and it stores up-to 30 Characters.
Mobile No has Integer datatype and stores up-to 13 integer value

DESC table_name;

This command allow us to view the description of that table.Practice is the database name which i was created and testing is the name i given to this table.We will discuss about NULL,Key,Default,Extra are in the later sessions.

This is how we create a simple basic table.Let’s see hoe to insert values to this table.

INSERT INTO table_name values (1,"person_1",1231231231);
INSERT INTO table_name values (2,"person_2",97979797),(3,"person_3",34343434),(4,'person_4',456789123);

First line shows,inserting only a single row data to the table.Whereas,second one shows multi data insertion to the table.We can use either Double Quote (” “) or Single Quote (‘ ‘) to the Character datatype.

Let’s see how to view this table entirely or only a specific column.

SELECT * FROM table_name;
SELECT column_name FROM table_name;

The first image shows the full table contents.
Second image shows only a particular column named “books”.

EXIT;

Exit from mariadb.

We will discuss how to edit,update the table in next article.

That’s it..!

Let’s know the installation of mariadb in debian.

sudo apt update
sudo apt install mariadb-server mariadb-client

First of all,we need to update the package manager and then type the install command.

Here we are going to install two packages i.e.., for server side and client side.You can skip the “mariadb-client” if you don’t need.

sudo systemctl status mariadb.service

Using this command,we can check the status of mariadb.Refer the image.

Now,we successfully done with the installation.After this we need to set a password to the root user.Because,by default the root user doesn’t have any password.So,we need to use this command..,

sudo mysql_secure_installation

Simply,press enter and then press ‘N’ next press ‘Y’ for further steps.

Also,you can create a new user.Let’s see how to create a new user.

CREATE USER 'your_username'@'localhost' IDENTIFIED BY 'your_password';

GRANT ALL PRIVILEGES ON *.* TO 'your_username'@'localhost' WITH GRANT OPTION;

FLUSH PRIVILEGES;

User has been created.

Let’s enter into mariadb.

mariadb -u your_username -p

NOTE: Don’t pass “@localhost” here.Simply pass username only.

Now,enter the created password.

If the output displayed like this,you were done…

SHOW DATABASES;

Here,we can see the databases.

We can discuss it later on MySQL topics.

EXIT;

Exit from mariadb.

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Getting this configuration file,we can change some configuration.

NOTE :

After configure,we must restart the mariadb.

sudo systemctl start mariadb.service
sudo systemctl stop mariadb.service
sudo systemctl enable mariadb.service
sudo systemctl disable mariadb.service
sudo systemctl restart mariadb.service

We can use these commands as per our convenience like we discussed in Apache installation.

That’s it..!

Let’s know the installation of mariadb in debian.

sudo apt update
sudo apt install mariadb-server mariadb-client

First of all,we need to update the package manager and then type the install command.

Here we are going to install two packages i.e.., for server side and client side.You can skip the “mariadb-client” if you don’t need.

sudo systemctl status mariadb.service

Using this command,we can check the status of mariadb.Refer the image.

Now,we successfully done with the installation.After this we need to set a password to the root user.Because,by default the root user doesn’t have any password.So,we need to use this command..,

sudo mysql_secure_installation

Simply,press enter and then press ‘N’ next press ‘Y’ for further steps.

Also,you can create a new user.Let’s see how to create a new user.

CREATE USER 'your_username'@'localhost' IDENTIFIED BY 'your_password';

GRANT ALL PRIVILEGES ON *.* TO 'your_username'@'localhost' WITH GRANT OPTION;

FLUSH PRIVILEGES;

User has been created.

Let’s enter into mariadb.

mariadb -u your_username -p

NOTE: Don’t pass “@localhost” here.Simply pass username only.

Now,enter the created password.

If the output displayed like this,you were done…

SHOW DATABASES;

Here,we can see the databases.

We can discuss it later on MySQL topics.

EXIT;

Exit from mariadb.

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Getting this configuration file,we can change some configuration.

NOTE :

After configure,we must restart the mariadb.

sudo systemctl start mariadb.service
sudo systemctl stop mariadb.service
sudo systemctl enable mariadb.service
sudo systemctl disable mariadb.service
sudo systemctl restart mariadb.service

We can use these commands as per our convenience like we discussed in Apache installation.

That’s it..!