Table I created table name is “movies”

TASK 1 : Show details for Vijay acted movies.

SELECT * FROM movies WHERE actor=’vijay’;

TASK 2 : Show Vijay movies names only.

SELECT movie FROM movies WHERE actor=’vijay’;

TASK 3 : Show details, lokesh kanagaraj directed movie and actor name starts with ‘k’ and ends with vowels[a,e,i,o,u].

SELECT * FROM movies WHERE director=’lokesh kanagaraj’ AND actor LIKE ‘k%’ AND actor SIMILAR TO ‘%[aeiou]’;

TASK 4 : Show movie name only, lokesh kanagaraj directed movie and actor name starts with ‘k’ and ends with vowels[a,e,i,o,u].

SELECT movie FROM movies WHERE director=’lokesh kanagaraj’ AND actor LIKE ‘k%’ AND actor SIMILAR TO ‘%[aeiou]’;

TASK 5 : Show movie name and actor name only, lokesh kanagaraj directed movie and actor name starts with ‘k’ and ends with vowels[a,e,i,o,u].

SELECT movie,actor FROM movies WHERE director=’lokesh kanagaraj’ AND actor LIKE ‘k%’ AND actor SIMILAR TO ‘%[aeiou]’;

TASK 6 : Show the details, director name starts and ends with same letter.

SELECT * FROM movies WHERE LEFT(director, 1)=RIGHT(director, 1);

TASK 7 : Show the movie name only, the director name starts and ends with same letter.

SELECT movie FROM movies WHERE LEFT(director, 1)=RIGHT(director, 1);

TASK 8 : Show the director name only, the director name start and ends with same letter.

SELECT director FROM movies WHERE LEFT(director, 1)=RIGHT(director, 1);

TASK 9 : Show the movie name, the actors have only five character.

SELECT movie FROM movies WHERE LENGTH(actor)=5;

TASK 10 : Show the movie name and actors name, actors have only five character.

SELECT movie,actor FROM movies WHERE LENGTH(actor)=5;

TASK 11 : Add a column salary_in_crore and values.

ALTER TABLE movies ADD COLUMN salary_in_crore INT;

UPDATE movies SET salary_in_crore =100 WHERE actor = ‘vijay’;

UPDATE movies SET salary_in_crore =70 WHERE actor = ‘kamal’;

UPDATE movies SET salary_in_crore =90 WHERE actor = ‘ajith’;

UPDATE movies SET salary_in_crore =40 WHERE actor = ‘karthi’;

UPDATE movies SET salary_in_crore =110 WHERE actor = ‘rajini’;

UPDATE movies SET salary_in_crore =50 WHERE actor = ‘dhanush’;

UPDATE movies SET salary_in_crore =5 WHERE actor = ‘soori’;

SELECT * FROM movies;

TASK 12 : Show actor name and salary,order by salary high to low.

SELECT actor, salary_in_crore FROM movies ORDER BY salary_in_crore DESC;

TASK 13 : Show top 3 actor based on salary.

SELECT DISTINCT actor, salary_in_crore FROM movies ORDER BY salary_in_crore DESC LIMIT 3;

Insert values From a .csv file

Step 1: Create a .csv or .xlsx file

Step 2: Insert values from a .csv or .xlsx file

Step 3: Copy the file and paste in to the location where pSQL installed:

-> “/var/lib/postgresql” and paste the file

Step 4: Go to the terminal and connect the database.

Step 5: Create table in the database with the columns given in the file header.

Step 6: Enter the follwing query in psql

-> COPY table_name FROM ‘/var/lib/postgresql/fine_name.csv’ WITH CSV HEADER;

Here, “movies” is the table name and the location where the “movies.csv” file is present.

9 values copied

Step 7: Then, write a select query to fetch all the rows.

successfully import the datas in to postgres database from csv or excel file

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.

In the city of Data, the citizens relied heavily on organizing their information. The city was home to many different types of data numbers, names, addresses, and even some exotic types like images and documents. But as the city grew, so did the complexity of managing all this information.

One day, the city’s leaders called a meeting to discuss how best to handle the growing data. They were split between two different systems

1. the old and trusted Relational Database Management System (RDBMS)
2. the new, flashy NoSQL databases.

Enter Relational Databases:

Relational databases were like the city’s libraries. They had rows of neatly organized shelves (tables) where every book (data entry) was placed according to a specific category (columns).

Each book had a unique ID (primary key) so that anyone could find it quickly. These libraries had been around for decades, and everyone knew how to use them.

The RDBMS was more than just a library. It enforced rules (constraints) to ensure that no book went missing, was duplicated, or misplaced. It even allowed librarians (queries) to connect different books using relationships (joins).

If you wanted to find all the books by a particular author that were published in the last five years, the RDBMS could do it in a heartbeat.

The Benefits of RDBMS:

The citizens loved the RDBMS because it was:

1. Organized: Everything was in its place, and data was easy to find.
2. Reliable: The rules ensured data integrity, so they didn’t have to worry about inconsistencies.
3. Powerful: It could handle complex queries, making it easy to get insights from their data.
4. Secure: Access to the data could be controlled, keeping it safe from unauthorized users.

The Rise of NoSQL:

But then came the NoSQL databases, which were more like vast, sprawling warehouses. These warehouses didn’t care much about organization; they just stored everything in a big open space. You could toss in anything, and it would accept it—no need for strict categories or relationships. This flexibility appealed to the tech-savvy citizens who wanted to store newer, more diverse types of data like social media posts, images, and videos.

NoSQL warehouses were fast. They could handle enormous amounts of data without breaking a sweat and were perfect for real-time applications like chat systems and analytics.

The PostgreSQL Advantage:

PostgreSQL was a superstar in the world of RDBMS. It combined the organization and reliability of traditional relational databases with some of the flexibility of NoSQL. It allowed citizens to store structured data in tables while also offering support for unstructured data types like JSON. This made PostgreSQL a versatile choice, bridging the gap between the old and new worlds.

For installing postgres : https://www.postgresql.org/download/

The Dilemma: PostgreSQL vs. NoSQL:

The city faced a dilemma. Should they stick with PostgreSQL, which offered the best of both worlds, or fully embrace NoSQL for its speed and flexibility? The answer wasn’t simple. It depended on what the city valued more: the structured, reliable nature of PostgreSQL or the unstructured, flexible approach of NoSQL.

For applications that required strict data integrity and complex queries, PostgreSQL was the way to go. But for projects that needed to handle massive amounts of unstructured data quickly, NoSQL was the better choice.

Conclusion:

In the end, the city of Data realized that there was no one-size-fits-all solution. They decided to use PostgreSQL for applications where data relationships and integrity were crucial, and NoSQL for those that required speed and flexibility with diverse data types.

And so, the citizens of Data lived happily, managing their information with the right tools for the right tasks, knowing that both systems had their place in the ever-growing city.

Hosting your own WordPress website is interesting right!! Ok, come on let’s do it!!

We are going to do this practical from Scratch. From the Creation of our Own VPC, Subnets, Internet Gateway, Route tables to Deployment of WordPress.

Here, we are going to use Amazon Web Service’s RDS service for hosting our own WordPress site. Before that, let’s take a look at a basic introduction to RDS service.

Amazon Relational Database Service is a distributed relational database service by Amazon Web Services (AWS). It is a web service running in the cloud designed to simplify the setup, operation, and scaling of a relational database for use in applications. Administration processes like patching the database software, backing up databases and enabling point-in-time recovery are managed automatically.

Features of AWS RDS

• Lower administrative burden. Easy to use
• Performance. General Purpose (SSD) Storage
• Scalability. Push-button compute scaling
• Availability and durability. Automated backups
• Security. Encryption at rest and in transit
• Manageability. Monitoring and metrics
• Cost-effectiveness. Pay only for what you use

Ok, let’s jump onto the practical part!!

We will do this practical from scratch. Since it will be big, so we divided this into 5 small parts namely

• Creating a MySQL database with RDS
• Creating an EC2 instance
• Configuring your RDS database
• Configuring WordPress on EC2
• Deployment of WordPress website

Creating a MySQL database with RDS

Before that, we have to do two pre-works namely the Creation of Virtual Private Cloud(VPC), Subnets and Security groups. These are more important because in order to have a reliable connection between WordPress and MySQL database, they should be located in the same VPC and should have the same Security Group.

Since Instances are launched on Subnets only, Moreover RDS will launch your MySQL database in EC2 instance only that we cannot able to see since it is fully managed by AWS.

We are going to create our own VPC. For that, we have to specify IP range and CIDR. We specified IP and CIDR as 192.168.0.0/16.

What is CIDR?. I explained this in my previous blog in very detail. You can refer here.

Lets come to the point. After specifying the IP range and CIDR, enter your VPC name.

Now, VPC is successfully created with our specified details.

Next step is to launch the subnet in the above VPC.

For Creating Subnets, you have to specify which VPC the lab should launch. We already have our own VPC named “myvpc123”.

And then we have to specify the range of Subnet IP and CIDR. Please note that the Subnet range should come under VPC range, it should not exceed VPC range.

For achieving the property of High Availability, We have to launch minimum two subnets, so that Amazon RDS will launch its database in two subnets, if one subnet collapsed means, it won’t cause any trouble.

Now, two Subnets with their specified range of IPs and CIDR are launched successfully inside our own VPC and they are available.

Next step is to create a security group in order to secure the WordPress and MySQL databases. Note that both should have the same Security Group or else it won’t connect.

For creating a Security Group, we have to specify which VPC it should be launched and adding a Description is mandatory.

Then we have to specify inbound rules, for making this practical simple, we are allowing all traffic to access our instance.

Now, the Security Group is successfully created with our specified details.

Now let’s jump into part 1 which is about Creating a MySQL database with RDS.

Select Create database, then select Standard create and specify the database type.

Then you have to specify the Version. Version plays a major role in MySQL when integrating with WordPress, so select the compactible version or else it will cause serious trouble at the end. Then select the template, here we are using Free-tier since it won’t be chargeable.

Then you have to specify the credentials such as Database Instance name, Master username and Master password.

Most important part is a selection of VPC, you should select the same VPC where you will launch your EC2 instance for your WordPress and we can’t modify the VPC once the database is created. Then select the Public access as No for providing more security to our database. Now, the people outside of your VPC can’t connect to your database.

Then you have to specify the security group for your database. Note that the Security Group for your database and WordPress should be the same or else it will cause serious trouble.

Note that Security Groups is created per VPC. After selecting Security Group, then click Ok to create the RDS database.

Creating an EC2 instance

Before creating an instance, there should be two things you configured namely Internet Gateway and Route tables. It is used for providing outside internet connectivity to an instance launched in the subnet.

Internet Gateway is created per VPC. First, we have to create one new Internet Gateway with the specified details.

Then you have to attach Internet Gateway to the VPC

Next step is to create Routing tables. Note that Route table is created per Subnet.

We have to specify which VPC in which your subnet is available to attach routing table with it, specify Name and click create to create the route table.

Then click Edit route to edit the route details namely destination and target. Enter destination as 0.0.0.0/0 for accessing any IP anywhere on the Internet and target is your Internet Gateway.

After entering the details, click Save routes.

We created a Route table, then we have to attach that table to your Subnet. For that click Edit route table association and select your subnet where you want to attach the route table with it.

Now, lets jump into the task of creating an EC2 instance.

First, you have to choose the AMI image in which you used for creating an EC2 instance, here I selected Amazon Linux 2 AMI for that.

Then you have to select Instance type, here I selected t2.micro since it comes under free tier.

Then you have to specify the VPC, Subnet for your instance and you have to enable Auto-assign Public IP in order to get your Public IP to your instance.

Then you have to add storage for your instance. It is optional only.

Then you have to specify the tags which will be more useful especially for automation.

Then you have to select the Security Group for your instance. It should be the same as your database have.

And click Review and Launch. Then you have to add Keypair to launch your EC2 instance. If you didn’t have Keypair means, you can create at that time.

Configuring your RDS database

At this point, you have created an RDS database and an EC2 instance. Now, we will configure the RDS database to allow access to specific entities.

You have to run the below command in your EC2 instance in order to establish the connection with your database.

export MYSQL_HOST=<your-endpoint>

You can find your endpoint by clicking database in the RDS dashboard. Then you have to run the following command.

mysql --user=<user> --password=<password> dbname

This output shows the database is successfully connected to an EC2 instance.

In the MySQL command terminal, you have to run the following commands in order to get all privileges to your account.

CREATE USER 'vishnu' IDENTIFIED BY 'vishnupassword';
GRANT ALL PRIVILEGES ON dbname.* TO vishnu;
FLUSH PRIVILEGES;
Exit

Configuring WordPress on EC2

For Configuring WordPress on EC2 instance, the first step is to configure the webserver, here I am using Apache webserver. For that, you have to run the following commands.

sudo yum install -y httpd

sudo service httpd start

Next step would be download the WordPress application from the internet by using wget command. Run the following code to download the WordPress application.

wget https://wordpress.org/latest.tar.gz
tar -xzf latest.tar.gz

Then we have to do some configuration, for this follow the below steps.

cd wordpress
cp wp-config-sample.php wp-config.php
cd wp-config.php

Go inside the wp-config.php file and enter your credentials (including your password too)

Then, Goto this link and copy all and paste it to replace the existing lines of code.

Next step is to deploy the WordPress application. For that, you have to run the following commands in order to solve the dependencies and deploy WordPress in the webserver.

sudo amazon-linux-extras install -y lamp-mariadb10.2-php7.2 php7.2

sudo cp -r wordpress/* /var/www/html/

sudo service httpd restart

That’s it. You have a live, publicly-accessible WordPress installation using a fully-managed MySQL database on Amazon RDS.

Then if you enter your WordPress instance IP in your browser, you will land your WordPress home page.

After you filled in your credentials, you will get your own homepage.

That’s it. You launched your own application in your own instance and your database is managed by AWS RDS service.

Thank you all for your reads. Stay tuned for my next article.

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.

Problem Statement

We have a PostgreSQL database with multiple schemas and tables. Some users have read-only access to the database and and they relay on Devops/Support team to refresh their access to view any new schemas or tables added to the database. We need to provide a solution to allow read-only users to refresh their access so they can view new schemas and tables as they are added.

Named Read-only User Group

Function 1: Will create a user and create a read_only group not available. If the group is available, it will create the user and password, attach it to the read_only group, and add all existing schema read-only access.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE or replace FUNCTION create_users_and_grant_access(users text[]) RETURNS void AS $$
DECLARE
    READONLY_GROUP text := 'readonly';
    password text;
    user_name text;
    schemata text;
BEGIN
    FOREACH user_name IN ARRAY users LOOP
        -- Check if the user already exists
        PERFORM 1 FROM pg_user WHERE usename = user_name;
        IF NOT FOUND THEN
            -- Generate a random password for the new user
            password := encode(gen_random_bytes(12), 'base64');


            -- Create the database user with the hashed password
            RAISE NOTICE 'Creating database user: %', user_name;
            RAISE NOTICE 'Password: %', password;
            EXECUTE format('CREATE USER %I WITH PASSWORD %L', user_name, password);

            -- Create the read-only group if it does not exist
            PERFORM 1 FROM pg_roles WHERE rolname = READONLY_GROUP;
            IF NOT FOUND THEN
                RAISE NOTICE 'Creating read-only group: %', READONLY_GROUP;
                EXECUTE format('CREATE ROLE %I', READONLY_GROUP);
            END IF;

            -- Add the user to the read-only group
            RAISE NOTICE 'Adding user to read-only group: %', READONLY_GROUP;
            EXECUTE format('GRANT %I TO %I', READONLY_GROUP, user_name);
        ELSE
            RAISE NOTICE 'User already exists: %', user_name;
        END IF;
    END LOOP;

    -- Grant read-only access to all schemas for the read-only group
    FOR schemata IN SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name != 'information_schema' LOOP
        -- Check if the read-only group already has access to the schema
        PERFORM 1 FROM information_schema.role_table_grants WHERE grantee = READONLY_GROUP AND table_schema = schemata;
        IF NOT FOUND THEN
            -- Grant read-only access to the schema for the read-only group
            RAISE NOTICE 'Granting read-only access to schema: %', schemata;
            EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', schemata, READONLY_GROUP);
            EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO %I', schemata, READONLY_GROUP);
            EXECUTE format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA %I TO %I', schemata, READONLY_GROUP);
        ELSE
            RAISE NOTICE 'Read-only access already granted to schema: %', schemata;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Function 2:

This function will enable users to refresh read_only permissions, so they don’t have to rely on DevOps

CREATE OR REPLACE FUNCTION grant_readonly_access(schematabe text DEFAULT NULL)
RETURNS void
SECURITY DEFINER
AS $$
DECLARE
  READONLY_GROUP text := 'readonly';
BEGIN
  IF schematabe IS NOT NULL THEN
    -- Grant read-only access to specified schema for the user and read-only group
    PERFORM 1 FROM information_schema.schemata WHERE schema_name = schematabe;
    IF FOUND THEN
      RAISE NOTICE 'Granting read-only access to schema: % for user: %', schematabe, READONLY_GROUP;
      EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', schematabe, readonly_group);
      EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO %I', schematabe, readonly_group);
      EXECUTE format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA %I TO %I', schematabe, readonly_group);
      EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', schematabe, READONLY_GROUP);
      EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO %I', schematabe, READONLY_GROUP);
      EXECUTE format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA %I TO %I', schematabe, READONLY_GROUP);
    ELSE
      RAISE EXCEPTION 'Schema not found: %', schematabe;
    END IF;
  ELSE
    -- Grant read-only access to all schemas for the user and read-only group
    FOR schematabe IN SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name != 'information_schema' LOOP
      -- Check if the read-only group already has access to the schema
      PERFORM 1 FROM information_schema.role_table_grants WHERE grantee = readonly_group AND table_schema = schematabe;
      IF NOT FOUND THEN
        -- Grant read-only access to the schema for the read-only group
        RAISE NOTICE 'Granting read-only access to schema: % for user: %', schematabe, READONLY_GROUP;
        EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', schematabe, readonly_group);
        EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO %I', schematabe, readonly_group);
        EXECUTE format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA %I TO %I', schematabe, readonly_group);
        EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', schematabe, READONLY_GROUP);
        EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO %I', schematabe, READONLY_GROUP);
        EXECUTE format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA %I TO %I', schematabe, READONLY_GROUP);
      ELSE
        RAISE NOTICE 'Read-only access already granted to schema: % for user: %', schematabe, READONLY_GROUP;
      END IF;
    END LOOP;
  END IF;
END;
$$ LANGUAGE plpgsql;

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..!