databaser 0.9.45
A small package to generate SQL for postgreSQL
Requires Python: >=3.6

# Databaser

## Introduction

It is query builder that can be connected to the database (Currently, Postgresql), 
and gives you the ability to insert and select using python dictionary making development more flexible and easier than other production ORMs.

### Databases Supported

- Postgresql

## Design Principles

- Having a small learning curve
- Manage databases easily, and quickly prototype/develop
- Focus on readability
- Avoiding SQL typos and mistakes


# Documentation 

### Project Components

1) Query Builder:
   This component is responsible for building SQL Query using simple python object

2) Table Structure:
   This is a query builder for table structure

3) Data Model:
    Data models represents a table in the postgres database, holds:
   1) Table Name
   2) Schema Name
   3) Fields
    
4) PG Engine
    A simple query/statement executor built-in for data models

### Query Builder 
The query class contains the crud operations as internal methods

1) Find
2) Insert
3) Update
4) Delete

The method used to return the raw SQL statement is .get_sql() inside the Query Class 

### A) Find

This is used for select statements in PG SQL

#### Basic SELECT

A simple SELECT query will mostly look like this
    
    SELECT * FROM tableA

Let's convert it to the python Code
    
    Query("pgsql").find("tableA").get_sql()

How about calling fields?
Hint: table names are added by default next to a field name automatically to avoid ambiguous fields in joins
 
    SELECT "tableA"."id", "tableA"."name" FROM tableA

Let's convert it to the python Code
    
    Query("pgsql").find("tableA", ["id", "name"]).get_sql()

Want to limit the results to 10 only? easy
 
    SELECT "tableA"."id", "tableA"."name" FROM tableA LIMIT 10

Let's convert it to the python Code
    
    Query("pgsql").find("tableA", ["id", "name"], limit=10).get_sql()

Offset? get results between certain ranges?
 
    SELECT "tableA"."id", "tableA"."name" FROM tableA LIMIT 10 OFFSET 10

Let's convert it to the python Code
    
    Query("pgsql").find("tableA", ["id", "name"], limit=10, skip=10).get_sql()

##### Where Conditions

Let's say we want to limit our query to name = "John"
    
    SELECT * FROM "tableA" WHERE "tableA"."name" = 'John' LIMIT 10;

Python:

Type the field name inside an object and inside that a new dictionary that holds the condition name you need like below

    condition = {
        "name": {
            "$value": "John"
        },
    }

    Query("pgsql").find("tableA", condition=condition)
    
Reference

Before continuing the documentation, here are some notations that are used below.

These are keywords to use on the conditions, inspired from mongodb querying

    1- $value: This requires a value and means literal equal (=)
    2- $like: This represents the LIKE in SQL and the value is expected to contain the wildcards (%)
    3- $in: Corresponds to Not IN, in SQL => Expecting a string separated with a comma ("a,b,c")
    4- $nin: Corresponds to Not IN, in SQL => Expect the same as $in
    5- $group: grouping statments like, ((X = 1 AND Y = 1) OR Z = 2)
        a- Requires $type => ( OR, AND )
            eg:- WHEN $type = OR, (A = 1 OR B = 1)
            eg:- WHEN $type = AND, (A = 1 AND B = 1)

Continuing the tutorial...

The rest of the tutorial of the conditions will only contain the condition dictionary

    WHERE "name" = 'John';

Corresponds to ...

    condition = {
        "name": {
            "$value": "John"
        }
    }

Getting names that starts with John

    WHERE "name" LIKE 'John%';

Corresponds to ...

    condition = {
        "name": {
            "$like": "John%"
        }
    }

Getting records that has one of those names John, Mike, Zack

    WHERE "name" IN ('John', 'Mike', 'Zack');

Corresponds to ...

    condition = {
        "name": {
            "$in": "John,Mike,Zack"
        }
    }

Getting records that not in one of those names John, Mike

    WHERE "name" IN ('John', 'Mike');

Corresponds to ...

    condition = {
        "name": {
            "$nin": "John,Mike"
        }
    }

Grouping conditions 

    WHERE ("name" = 'John' OR "name" = 'Mike') AND "name" = 'Zack';

Corresponds to ...

    condition = {
        "name": {
            "$value": "Zack"
        },
        "$group": {
            "$type": "OR",
            "name": {
                "$value": "John",
            },
            "name": {
                "$value": "Mike",
            },
        }
    }

And now run this to get the result

    Query("pgsql").find("tableA", condition=condition).get_sql()

This allows for a lot of flexibility when writing code

What about joins? Well, the same idea goes here too.

    SELECT * FROM "tableA" INNER JOIN "tableB" ON "tableA"."columnX" = "tableB"."columnY";

Create a dictionary and follow the instructions below

    joins = {
        "tableB": {
            "$table": "$this",
            "$type": "innerJoin",  # innerJoin, leftJoin, rightJoin
            "$on": {
                "$type": "$eq",
                "$tableA": "columnX",
                "$tableB": "columnY",
            }
        },
    }

To follow with the join instructions, there are some notation for it

    1) $table => References the table joined
    2) $schema_name => Schema Name for all tables joined, default "public"
    3) $this => Used with the notation above to indicate that the joining table is the currently selected table
    
    4) $on => Corresponds to the (ON) keyword in SQL used in joins
        i) $type => type of join, Equijoin, non-equijoin
            a) $eq => Equal (=)
            a) $gt => Greater Than (>)
            a) $gte => Greater Than or Equal (>=)
            a) $lt => Less Than (<)
            a) $lte => Less Than or Equal (<=)
            a) $ne => Not Equal (<>)

        ii) $tableA => References the table on the left when joining, the value is the field joining on
        iii) $tableB => References the table on the right when joining, the value is the field joining on

Again, the rest of the joining documentation will continue with the join dictionary only

    INNER JOIN "tableB" ON "tableA"."columnX" = "tableB"."columnY";

Corresponds to ...

    joins = {
        "tableB": {
            "$table": "$this",  # This table is the table joined 
            "$type": "innerJoin",  # innerJoin, leftJoin, rightJoin
            "$on": {
                "$type": "$eq",  # eq, gt, gte, lt, lte, ne
                "$tableA": "columnX",
                "$tableB": "columnY",
            }
        },
    }

How about adding another condition in the join? easy

    INNER JOIN "tableB" ON "tableA"."columnX" = "tableB"."columnY" AND "tableA"."columnX" = "tableB"."columnY";

Corresponds to ...

    joins = {
        "tableB": {
            "$table": "$this",
            "$type": "innerJoin",  # innerJoin, leftJoin, rightJoin
            "$on": {
                "$type": "$eq",
                "$tableA": "columnX",
                "$tableB": "columnY",
                "$and": {  # $and, $or
                    "$table": "$this",
                    "$on": {
                        "$type": "$eq",
                        "$tableA": "columnX",
                        "$tableB": "columnY",
                    }
                }
            }
        },
    }

#### Insert

Inserting statements is much easier

    INSERT INTO "table_name" ("a", "b", "c") VALUES ('1', '2', '3');

Create a dictionary called data and call the following

    data = {
        "a": 1,
        "b": 2,
        "c": 3,
    }
    sql = Query("pgsql").insert("table_name", data=data, value_quote=True).get_sql()

Realize the value_quote=True parameter? this is used to add single quotes for string values

Insert from a select statement?

    INSERT INTO table_name (a,b,c) SELECT * FROM tableB;

Create your select query first

    select = Finder("tableB", group_by=["name", "id"])

Then ...
    
    InsertFromSelect("table_name", ['a','b','c',], select).get_sql()

#### Update

The update class supports both, the data and the conditions dictionaries as parameters 

To generate this ...

    UPDATE "table_name" SET "name" = 'John' WHERE "name" = 'Mike';

Add this ...

    conditions = {
        "name": {
            "$value": "Mike"
        }
    }
    data = {
        "name": "John"
    }
    sql = Query("pgsql").update("table_name", data=data, conditions=conditions).get_sql()


#### Delete

To delete a record
    
    DELETE FROM "table_name" WHERE "name" = 'Mike';

Add ...

    conditions = {
        "name": {
            "$value": "Mike"
        }
    }
    sql = Query("pgsql").delete("table_name", conditions=conditions).get_sql()


### Table Stucture

Create a table structure with the following 
    
    CREATE TABLE "XYZ" (
        id serial not null primary key,
        name character varying not null
    );

Python Code:

    fields = [
        TableField(**{
            "name": "id",
            "data_type": "serial",
            "not_null": True,
            "primary_key": True,
        }),
        TableField(**{
            "name": "name",
            "data_type": "character varying",
            "not_null": True,
        }),
    ]
    sql = TableStructure("pgsql").create_table("table_name", fields).get_sql()

To add a column
    
    ALTER TABLE "tablename" ADD COLUMN "field_name" character varying not null;

Python Code ...

    TableStructure("pgsql").add_column("tablename", "field_name", "character varying", not_null=True).get_sql()



### Data Model

A data model represents a table in the database, there are two types of models

1) Data Model:
   - Represents a simple table in the database

2) Many 2 Many Data Model (Under Development)
   - represents a many-2-many table in the database, supporting automatic joins on data selection.

For a data model to work, create a class with the name of the table, inherit the class  (DataModel), and assign the internal properties like the following

There are 3 main properties

    table_name: The Name of the table
    schema_name: by default set to public
    fields: a list that holds the fields, 
    -----
    Hint: A field is a TableField class used from the table structure in the query builder, go back to the table structure section
        to find the remaining of the TableField properties, like foreign key, and context_data_type

Full code ...

    class User(DataModel):
        table_name = 'user_user'
        
        fields = [
            TableField(name="user_id", data_type="serial", primary_key=True, not_null=True,),
        ]

The sole purpose of data models is to make all CRUD operations easier, so it supports all the above query building operations internally.

Let's select 5 users with name that starts with mike.

    conditions = {
        "name": {
            "$like": "mike%"
        }
    }

    User().get(
        condition=conditions,
        limit=5,
    ).show()

Date models support these methods internally,
    
    - get
    - get_one
    - count
    - insert
    - insert_many (Under Development)
    - update
    - delete

A follow-up documentation will talk more about the details ...


### Database Engine

    conn_string = {'host': 'localhost', 'database': 'db_name', 'user': 'postgres', 'password': 'mysecretpassword'}
    

    DatabaseEngine(**conn_string).execute("SELECT * FROM X")

The DatabaseEngine Class responds with an ExecutionResult data type

    class ExecutionResult(BaseModel):
        has_values: bool
        sql: str
        result: Optional[Union[List[Dict], Dict]]

The result parameter will return a list by default, 

But if the execute method has return_many=False, logically used when the limit is = 1, but not enforced

    DatabaseEngine(**conn_string).execute("SELECT * FROM X", return_many=False)