# square_database_structure (WIP)
## about
square_database_structure is a Python module that provides a structured and modular way to define database schemas and
tables using SQLAlchemy. It is designed to work with **PostgreSQL** databases, which natively support a
database-schema-table hierarchy. This structure allows for clear separation between schemas within a single database,
making it easier to manage large and complex database systems.
This module abstracts the database structure from the rest of the application logic, making schema definitions reusable
and easy to maintain across multiple projects.
### goals
- Centralize Database Schema Definitions: Serve as a single source of truth for defining database structures across
multiple projects.
- Modular Design: Allow databases and schemas to be independently defined and easily extended.
- PostgreSQL Compatibility: Utilize PostgreSQL's native support for database-schema-table hierarchy, allowing for
separation of concerns and scalability.
- Replaceability: The module should be easy to substitute with another implementation if necessary, while still
following the same mechanisms for schema and data definition.
- Explicit Structure: Ensure a clear, standardized structure that is easy to follow, making the design more explicit for
users.
## installation
```shell
pip install square_database_structure
```
## usage
This module organizes database schemas in a standardized folder structure where each top-level folder represents a
database, and subfolders within it represent schemas. All mandatory components, such as `__init__.py` and tables.py,
data.py, stored_procedures_and_functions need to follow this structure.
### Folder Structure
Here’s how you should organize your project when using this module:
```
square_database_structure/
├───main.py # Global definition file (mandatory)
├───create_database.py # Global database creation file (mandatory)
└───database1/ # Each folder corresponds to a separate database
├───__init__.py # Contains the global name for the database (mandatory)
└───schema1/ # Each subfolder corresponds to a schema within the database
├───__init__.py # Contains the global name for the schema (mandatory)
├───data.py # Contains the data for insertion for the schema (mandatory)
├───enums.py # Defines Enums to be used in the schema (optional)
├───tables.py # Defines tables of the schema (mandatory)
└───stored_procedures_and_functions/ # Contains stored procedures and functions for the schema (mandatory)
├───__init__.py # Contains logic to discover sql files (mandatory)
└───function.sql # function or stored procedure sql file (optional)
```
- Top-level folders: Represent individual databases (e.g., database1).
- Subfolders: Represent schemas within each database (e.g., public, schema1).
- Mandatory files:
- `__init__.py` (both at the database and schema level).
- tables.py within each schema.
- data.py within each schema.
- stored_procedures_and_functions package within each schema.
### Defining Database and Schema Names in `__init__.py`
Each database and schema folder must contain an `__init__.py` file where the database and schema names are defined
as
global variables.
#### Example for Database:
```python
# database1/__init__.py
global_string_database_name = "database1" # Mandatory: Database name
```
#### Example for Schema:
```python
# database1/schema1/__init__.py
global_string_schema_name = "schema1" # Mandatory: Schema name
```
### Defining Tables in tables.py
Each schema folder must contain a tables.py file where:
- You must declare a Base object tied to the schema.
- You can define table classes, extending the Base object.
#### Example tables.py:
```python
# database1/schema1/tables.py
from sqlalchemy import Column, Integer, String, MetaData
from sqlalchemy.ext.declarative import declarative_base
from database1.schema1 import global_string_schema_name
# 1. Mandatory: Declare Base with MetaData pointing to the schema
Base = declarative_base(metadata=MetaData(schema=global_string_schema_name))
# 2. Optional: Define table classes by extending Base
class App(Base):
__tablename__ = 'app'
id = Column(Integer, primary_key=True)
app_name = Column(String, nullable=False)
```
### Defining data in data.py
- You must declare a data_to_insert list to store optional data that may be inserted into the schema's tables.
```python
from database1.schema1.tables import App
# 1. Mandatory: Initialize a list for optional data insertion
data_to_insert = []
# Optional: Append data to be inserted into the table
data_to_insert.append(App(app_name="example_app"))
```
### Defining function or stored procedure in stored_procedures_and_functions package
- paste this logic in the `__init__.py` of this package to discover all sql files.
```python
from pathlib import Path
directory = Path(__file__).parent
stored_procedures_and_functions = []
for file_path in directory.iterdir():
if file_path.is_file() and file_path.suffix == ".sql":
with file_path.open("r") as file:
content = file.read()
stored_procedures_and_functions.append(content)
```
- You can keep raw sql files each containing ideally 1 stored procedure or function.
- The name of the file should ideally correspond to the function / procedure name.
- This raw sql should be compatible with postgres database.
```sql
CREATE OR REPLACE FUNCTION add_user(
p_username VARCHAR,
p_email VARCHAR
) RETURNS TEXT AS $$
BEGIN
-- Insert a new user into the users table
INSERT INTO users (username, email)
VALUES (p_username, p_email);
-- Return a success message
RETURN 'User added successfully!';
END;
$$ LANGUAGE plpgsql;
```
### Centralized Definitions in main.py
The main.py file is mandatory and contains a global list that maps databases to schemas and their corresponding table
definitions. This list is manually created by the user (for now).
#### Example main.py:
```python
# main.py
from database1.schema1 import global_string_schema_name as schema1_name
from database1.schema1.tables import Base as Schema1Base
from database1.schema1.data import data_to_insert as schema1_data
from database1.schema1.stored_procedures_and_functions import (
stored_procedures_and_functions as schema1_stored_procedures_and_functions)
from database1 import global_string_database_name as database1_name
# Global list that maps databases and schemas
global_list_create = [
{
"database": database1_name, # Mandatory: Database name
"schemas": [
{
"schema": schema1_name, # Mandatory: Schema name
"base": Schema1Base, # Mandatory: Base for this schema
"data_to_insert": schema1_data, # Mandatory: Data to insert (even if empty)
"stored_procedures_and_functions": schema1_stored_procedures_and_functions,
# Mandatory: stored procedures and functions (even if empty)
},
],
}
]
```
This file centralizes the definition of each database and schema, including the associated Base and data_to_insert for
table definitions.
### Creating Tables
Once you have defined your databases, schemas, and tables, you can create them in your PostgreSQL database by using the
`create_database_and_tables` function.
```python
from square_database_structure import create_database_and_tables
# Define the database connection details
db_username = "your_username"
db_password = "your_password"
db_ip = "localhost"
db_port = 5432
# Call the function to create the database and tables
create_database_and_tables(db_username, db_password, db_ip, db_port)
```
## env
- python>=3.12.0
## note
this module is planned to deviate a bit from the SemVer system for version numbers Major.x and x.Minor.x increments will
denote changes in application logic, while x.x.Patch increments will denote changes in database data or structure.
## changelog
### v2.1.0
- add testing framework.
### v2.0.0
- replace force_recreate_tables optional param to drop_if_exists.
### v1.4.0
- add force_recreate_tables optional param to create_database_and_tables.
### v1.3.4
- remove app raspi_home inside square->public->app.
- remove app_id column from square->greeting->greeting.
### v1.3.3
- add new app raspi_home inside square->public->app.
- add new schema square->greeting with 1 table Greeting.
### v1.3.2
- update to versioning plan.
- add test app in square->public->app.
### v1.3.1
- update license in setup.py.
### v1.3.0
- add support for stored procedures and functions.
### v1.2.0
- move data to separate file for each schema.
- add enums file for each schema.
### v1.1.0
- add database, schema and table creation logic (from square database) (removed logs).
### v1.0.3
- change structure of square->authentication->UserApp and square->authentication->UserSession (due to complications with
Composite Key).
- change default data in square->public->app.
### v1.0.2
- replace file_purpose with app_id in file_storage.
### v1.0.1
- add main.py file to have explicit mapping and ordering for schemas to be created.
- move database and schema names to `__init__.py`.
- add app table in public, change user, remove profile and add user app and remove enums.
### v1.0.0
- initial commit.
## Feedback is appreciated. Thank you!
Raw data
{
"_id": null,
"home_page": "https://github.com/thepmsquare/square_database_structure",
"name": "square-database-structure",
"maintainer": null,
"docs_url": null,
"requires_python": null,
"maintainer_email": null,
"keywords": null,
"author": "thePmSquare",
"author_email": "thepmsquare@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/1e/85/3d82f8d3fa84462d3bb6de6a99858733047523234e11239fd398fa3fa3c4/square_database_structure-2.1.0.tar.gz",
"platform": null,
"description": "# square_database_structure (WIP)\n\n## about\n\nsquare_database_structure is a Python module that provides a structured and modular way to define database schemas and\ntables using SQLAlchemy. It is designed to work with **PostgreSQL** databases, which natively support a\ndatabase-schema-table hierarchy. This structure allows for clear separation between schemas within a single database,\nmaking it easier to manage large and complex database systems.\n\nThis module abstracts the database structure from the rest of the application logic, making schema definitions reusable\nand easy to maintain across multiple projects.\n\n### goals\n\n- Centralize Database Schema Definitions: Serve as a single source of truth for defining database structures across\n multiple projects.\n- Modular Design: Allow databases and schemas to be independently defined and easily extended.\n- PostgreSQL Compatibility: Utilize PostgreSQL's native support for database-schema-table hierarchy, allowing for\n separation of concerns and scalability.\n- Replaceability: The module should be easy to substitute with another implementation if necessary, while still\n following the same mechanisms for schema and data definition.\n- Explicit Structure: Ensure a clear, standardized structure that is easy to follow, making the design more explicit for\n users.\n\n## installation\n\n```shell\npip install square_database_structure\n```\n\n## usage\n\nThis module organizes database schemas in a standardized folder structure where each top-level folder represents a\ndatabase, and subfolders within it represent schemas. All mandatory components, such as `__init__.py` and tables.py,\ndata.py, stored_procedures_and_functions need to follow this structure.\n\n### Folder Structure\n\nHere\u2019s how you should organize your project when using this module:\n\n```\nsquare_database_structure/\n\u251c\u2500\u2500\u2500main.py # Global definition file (mandatory)\n\u251c\u2500\u2500\u2500create_database.py # Global database creation file (mandatory)\n\u2514\u2500\u2500\u2500database1/ # Each folder corresponds to a separate database\n \u251c\u2500\u2500\u2500__init__.py # Contains the global name for the database (mandatory)\n \u2514\u2500\u2500\u2500schema1/ # Each subfolder corresponds to a schema within the database\n \u251c\u2500\u2500\u2500__init__.py # Contains the global name for the schema (mandatory)\n \u251c\u2500\u2500\u2500data.py # Contains the data for insertion for the schema (mandatory)\n \u251c\u2500\u2500\u2500enums.py # Defines Enums to be used in the schema (optional)\n \u251c\u2500\u2500\u2500tables.py # Defines tables of the schema (mandatory)\n \u2514\u2500\u2500\u2500stored_procedures_and_functions/ # Contains stored procedures and functions for the schema (mandatory)\n \u251c\u2500\u2500\u2500__init__.py # Contains logic to discover sql files (mandatory)\n \u2514\u2500\u2500\u2500function.sql # function or stored procedure sql file (optional)\n```\n\n- Top-level folders: Represent individual databases (e.g., database1).\n- Subfolders: Represent schemas within each database (e.g., public, schema1).\n- Mandatory files:\n - `__init__.py` (both at the database and schema level).\n - tables.py within each schema.\n - data.py within each schema.\n - stored_procedures_and_functions package within each schema.\n\n### Defining Database and Schema Names in `__init__.py`\n\nEach database and schema folder must contain an `__init__.py` file where the database and schema names are defined\nas\nglobal variables.\n\n#### Example for Database:\n\n```python\n# database1/__init__.py\n\nglobal_string_database_name = \"database1\" # Mandatory: Database name\n```\n\n#### Example for Schema:\n\n```python\n# database1/schema1/__init__.py\n\nglobal_string_schema_name = \"schema1\" # Mandatory: Schema name\n```\n\n### Defining Tables in tables.py\n\nEach schema folder must contain a tables.py file where:\n\n- You must declare a Base object tied to the schema.\n- You can define table classes, extending the Base object.\n\n#### Example tables.py:\n\n```python\n# database1/schema1/tables.py\n\nfrom sqlalchemy import Column, Integer, String, MetaData\nfrom sqlalchemy.ext.declarative import declarative_base\nfrom database1.schema1 import global_string_schema_name\n\n# 1. Mandatory: Declare Base with MetaData pointing to the schema\n\nBase = declarative_base(metadata=MetaData(schema=global_string_schema_name))\n\n\n# 2. Optional: Define table classes by extending Base\n\nclass App(Base):\n __tablename__ = 'app'\n\n\nid = Column(Integer, primary_key=True)\napp_name = Column(String, nullable=False)\n```\n\n### Defining data in data.py\n\n- You must declare a data_to_insert list to store optional data that may be inserted into the schema's tables.\n\n```python\nfrom database1.schema1.tables import App\n\n# 1. Mandatory: Initialize a list for optional data insertion\ndata_to_insert = []\n# Optional: Append data to be inserted into the table\ndata_to_insert.append(App(app_name=\"example_app\"))\n```\n\n### Defining function or stored procedure in stored_procedures_and_functions package\n\n- paste this logic in the `__init__.py` of this package to discover all sql files.\n\n```python\nfrom pathlib import Path\n\ndirectory = Path(__file__).parent\nstored_procedures_and_functions = []\n\nfor file_path in directory.iterdir():\n if file_path.is_file() and file_path.suffix == \".sql\":\n with file_path.open(\"r\") as file:\n content = file.read()\n stored_procedures_and_functions.append(content)\n```\n\n- You can keep raw sql files each containing ideally 1 stored procedure or function.\n- The name of the file should ideally correspond to the function / procedure name.\n- This raw sql should be compatible with postgres database.\n\n```sql\nCREATE OR REPLACE FUNCTION add_user(\n p_username VARCHAR,\n p_email VARCHAR\n) RETURNS TEXT AS $$\nBEGIN\n -- Insert a new user into the users table\n INSERT INTO users (username, email)\n VALUES (p_username, p_email);\n\n -- Return a success message\n RETURN 'User added successfully!';\nEND;\n$$ LANGUAGE plpgsql;\n\n```\n\n### Centralized Definitions in main.py\n\nThe main.py file is mandatory and contains a global list that maps databases to schemas and their corresponding table\ndefinitions. This list is manually created by the user (for now).\n\n#### Example main.py:\n\n```python\n# main.py\n\nfrom database1.schema1 import global_string_schema_name as schema1_name\nfrom database1.schema1.tables import Base as Schema1Base\nfrom database1.schema1.data import data_to_insert as schema1_data\nfrom database1.schema1.stored_procedures_and_functions import (\n stored_procedures_and_functions as schema1_stored_procedures_and_functions)\nfrom database1 import global_string_database_name as database1_name\n\n# Global list that maps databases and schemas\n\nglobal_list_create = [\n {\n \"database\": database1_name, # Mandatory: Database name\n \"schemas\": [\n {\n \"schema\": schema1_name, # Mandatory: Schema name\n \"base\": Schema1Base, # Mandatory: Base for this schema\n \"data_to_insert\": schema1_data, # Mandatory: Data to insert (even if empty)\n \"stored_procedures_and_functions\": schema1_stored_procedures_and_functions,\n # Mandatory: stored procedures and functions (even if empty)\n },\n ],\n }\n]\n```\n\nThis file centralizes the definition of each database and schema, including the associated Base and data_to_insert for\ntable definitions.\n\n### Creating Tables\n\nOnce you have defined your databases, schemas, and tables, you can create them in your PostgreSQL database by using the\n`create_database_and_tables` function.\n\n```python\nfrom square_database_structure import create_database_and_tables\n\n# Define the database connection details\ndb_username = \"your_username\"\ndb_password = \"your_password\"\ndb_ip = \"localhost\"\ndb_port = 5432\n\n# Call the function to create the database and tables\ncreate_database_and_tables(db_username, db_password, db_ip, db_port)\n```\n\n## env\n\n- python>=3.12.0\n\n## note\n\nthis module is planned to deviate a bit from the SemVer system for version numbers Major.x and x.Minor.x increments will\ndenote changes in application logic, while x.x.Patch increments will denote changes in database data or structure.\n\n## changelog\n\n### v2.1.0\n\n- add testing framework.\n\n### v2.0.0\n\n- replace force_recreate_tables optional param to drop_if_exists.\n\n### v1.4.0\n\n- add force_recreate_tables optional param to create_database_and_tables.\n\n### v1.3.4\n\n- remove app raspi_home inside square->public->app.\n- remove app_id column from square->greeting->greeting.\n\n### v1.3.3\n\n- add new app raspi_home inside square->public->app.\n- add new schema square->greeting with 1 table Greeting.\n\n### v1.3.2\n\n- update to versioning plan.\n- add test app in square->public->app.\n\n### v1.3.1\n\n- update license in setup.py.\n\n### v1.3.0\n\n- add support for stored procedures and functions.\n\n### v1.2.0\n\n- move data to separate file for each schema.\n- add enums file for each schema.\n\n### v1.1.0\n\n- add database, schema and table creation logic (from square database) (removed logs).\n\n### v1.0.3\n\n- change structure of square->authentication->UserApp and square->authentication->UserSession (due to complications with\n Composite Key).\n- change default data in square->public->app.\n\n### v1.0.2\n\n- replace file_purpose with app_id in file_storage.\n\n### v1.0.1\n\n- add main.py file to have explicit mapping and ordering for schemas to be created.\n- move database and schema names to `__init__.py`.\n- add app table in public, change user, remove profile and add user app and remove enums.\n\n### v1.0.0\n\n- initial commit.\n\n## Feedback is appreciated. Thank you!\n\n\n",
"bugtrack_url": null,
"license": null,
"summary": "database layer for my personal server.",
"version": "2.1.0",
"project_urls": {
"Homepage": "https://github.com/thepmsquare/square_database_structure"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "923a63cf25f4f7dffc09a6506f5861478bc29f534b50968947dd5b18694393b2",
"md5": "c7501d05b20140a18a1a26a45f2bca0a",
"sha256": "3cddae92be66648abcc516343ff6a7ee473b53ef178c17a7a7a9ea8acd012fb0"
},
"downloads": -1,
"filename": "square_database_structure-2.1.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "c7501d05b20140a18a1a26a45f2bca0a",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 27976,
"upload_time": "2024-12-30T00:56:18",
"upload_time_iso_8601": "2024-12-30T00:56:18.647546Z",
"url": "https://files.pythonhosted.org/packages/92/3a/63cf25f4f7dffc09a6506f5861478bc29f534b50968947dd5b18694393b2/square_database_structure-2.1.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "1e853d82f8d3fa84462d3bb6de6a99858733047523234e11239fd398fa3fa3c4",
"md5": "a12200e39bac37fd778ccda69e41c128",
"sha256": "578ee11201896c6fc9d9183fcf9bb477df11e5ac10bee152b2ba81150556678e"
},
"downloads": -1,
"filename": "square_database_structure-2.1.0.tar.gz",
"has_sig": false,
"md5_digest": "a12200e39bac37fd778ccda69e41c128",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 24209,
"upload_time": "2024-12-30T00:56:21",
"upload_time_iso_8601": "2024-12-30T00:56:21.092387Z",
"url": "https://files.pythonhosted.org/packages/1e/85/3d82f8d3fa84462d3bb6de6a99858733047523234e11239fd398fa3fa3c4/square_database_structure-2.1.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-12-30 00:56:21",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "thepmsquare",
"github_project": "square_database_structure",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "square-database-structure"
}
JSON
