Metadata-Version: 2.4
Name: sql_querystring_builder
Version: 0.1.0
Summary: Wraps string building of MS SQL queries
Author-email: Wael GRIBAA <g.wael@outlook.fr>
License-Expression: GPL-3.0-only
Keywords: sql,query,string
Classifier: Programming Language :: Python :: 3
Classifier: Operating System :: OS Independent
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Dynamic: license-file

# SqlQueryBuilder
Wraps string building of MS SQL queries.
It provides first level validation.

## Current features
1. Provides a string building facility for:
    - [x] Table
    - [x] Temporary Table
    - [x] Field
    - [x] Function on field
    - [x] String
2. Using:
    - [x] Select
    - [x] Update
    - [x] From
    - [x] InnerJoin
    - [x] Where
    - [x] OrderBy
3. Handling automatically:
    - [x] Table aliases
    - [x] Field aliases

## Not implemented (yet)
- [ ] Insert
- [ ] Compound (sub-)queries
- [ ] Functions
- [ ] Proper Conditions

## What it does not do
    Connect to a database server and run the queries by itself.
    It gives you the query as a string, it's then up to yopu to pass it to a SQL engine.

## Example
1. Define the elements of your query:
```
# Defines a Table for customers
customers_table = Table("Customers").add("id", "customerId", "mainPhone")
# Defines a Table for accounts
accounts_table = Table("Accounts", "acc").add("id", "customerId", "accountId")
# Selects the fields
select = Select(customers_table, accounts_table.fields.accountId)
# Sets an alias for an existing field
customers_table.fields.customerId.set_alias("cid")
# Specifies the table to takes as a FROM
from_ = From(customers_table)
# Specifies a temporary table for the select
atemptable = select.to_temptable("a_temp_table")
# Defines the InnerJoin
innerjoin = InnerJoin(accounts_table, accounts_table.fields.customerId, customers_table)
# Defines an Order By a defined field
orderby = OrderBy(customers_table.fields.customerId, descending=True)
# Adds a Where filter
where = Where(accounts_table.fields.accountId, "gt 20000 AND", customers_table.fields.customerId, "LIKE 'A1C%'")
```
2. Inspect what those elements are made of:
```
# See what the object Select looks like
print(select)
# > Select(fields=[Field(name='id', alias='None', table='Customers'),
# ...    Field(name='customerId', alias='cid', table='Customers'),
# ...    Field(name='mainPhone', alias='None', table='Customers'),
# ...    Field(name='accountId', alias='None', table='Accounts')])

# Initializes the QueryBuilder
query_builder = QueryBuilder(select, where, orderby, innerjoin, from_)
```
3. Build your query:
```
# Builds the query
query_str: str = query_builder.build()
print(query_str)
# > SELECT Customers.id, Customers.customerId AS cid, Customers.mainPhone, acc.accountId
# > INTO #a_temp_table
# > FROM Customers
# > INNER JOIN Accounts acc on acc.customerId = Customers.customerId
# > WHERE acc.accountId gt 20000 AND Customers.customerId LIKE 'A1C%'
# > ORDER BY cid DESC
```
4. Manipulate temporary tables which components are inferred:
```
# Is able to infer fields from temporary tables and use it as with a regular table.
temp_select = Select(atemptable)
print(temp_select)
# > Select(fields=[Field(name='id', alias='None', table='#a_temp_table'),
# ...    Field(name='cid', alias='None', table='#a_temp_table'),
# ...    Field(name='mainPhone', alias='None', table='#a_temp_table'),
# ...    Field(name='accountId', alias='None', table='#a_temp_table')])

# Builds the query from the temporary table
print(QueryBuilder(
        temp_select,
        From(atemptable)
    ).build())
# > SELECT #a_temp_table.id, #a_temp_table.cid, #a_temp_table.mainPhone, #a_temp_table.accountId
# > FROM #a_temp_table
```

(Find it directly in the module example.py)

## Compatible versions
Python 3.10 and above.

## Dependencies
(None)

## Changelog
### 0.1.0
This release changes the generated SQL: check your queries when upgrading.
- WHERE conditions and the right side of `INNER JOIN ... on` use the field's qualified name (`t1.KeyId`)
  instead of its SELECT alias, which SQL Server only accepts in ORDER BY. ORDER BY still uses the alias.
- `UPDATE ... SET` no longer writes `AS alias` after aliased fields (`SET acc.a=1`).
- Temporary tables made with `Select.to_temptable()` name their columns after the field aliases, as
  `SELECT ... INTO` does.
- Adding the same field name twice in one call (`Table("t").add("a", "a")`), including duplicate
  temporary-table columns, raises `AlreadyExistingFieldException`.
- An `Update` mixing two tables with the same name but different aliases raises `IncorrectUpdateException`.
- `NonExistingFieldException` is now an `AttributeError`: `hasattr()` and `getattr(..., default)` work on
  `table.fields`, and tables can be copied and pickled.
- Requires Python 3.10 or later (was 3.7).
- The license metadata uses the SPDX expression `GPL-3.0-only`.

## Authors
Wael GRIBAA
Contact: g.wael@outlook.fr
Made in September 2024
