Metadata-Version: 2.4
Name: dbwidgets
Version: 0.1.0
Summary: Database aware widgets for PySide6
Home-page: https://github.com/ilkermanap/dbwidgets
Author: Ilker Manap
Author-email: ilkermanap@gmail.com
License: LGPL-3.0-or-later
Classifier: Programming Language :: Python :: 3
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Environment :: X11 Applications :: Qt
Classifier: Topic :: Database :: Front-Ends
Classifier: Topic :: Software Development :: User Interfaces
Classifier: Development Status :: 4 - Beta
Requires-Python: >=3.9
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: PySide6
Provides-Extra: postgresql
Requires-Dist: psycopg2-binary; extra == "postgresql"
Provides-Extra: mysql
Requires-Dist: PyMySQL; extra == "mysql"
Provides-Extra: mariadb
Requires-Dist: PyMySQL; extra == "mariadb"
Provides-Extra: test
Requires-Dist: pytest; extra == "test"
Requires-Dist: pgserver; extra == "test"
Requires-Dist: psycopg2-binary; extra == "test"
Requires-Dist: PyMySQL; extra == "test"
Provides-Extra: docs
Requires-Dist: sphinx; extra == "docs"
Requires-Dist: sphinx_rtd_theme; extra == "docs"
Requires-Dist: rst2pdf; extra == "docs"
Dynamic: author
Dynamic: author-email
Dynamic: classifier
Dynamic: description
Dynamic: description-content-type
Dynamic: home-page
Dynamic: license
Dynamic: license-file
Dynamic: provides-extra
Dynamic: requires-dist
Dynamic: requires-python
Dynamic: summary

# dbwidgets

Creates an object representation of a given database, and a set of data aware
PySide6 widgets on top of it. Master detail relationships are derived from the
foreign keys already defined in the database.

The widgets follow Delphi's *Data Controls* palette: the relationship lives on
the dataset, and every control attached to a data source follows the current
record without knowing about the other controls.

SQLite, PostgreSQL, MySQL and MariaDB are implemented. Column information,
primary keys and foreign keys are extracted automatically.

![demo](https://raw.githubusercontent.com/ilkermanap/dbwidgets/master/demo_dbcontrols.png)

## Install

    pip install PySide6
    pip install psycopg2-binary   # only for postgresql
    pip install PyMySQL           # only for mysql or mariadb

Run the demo:

    python demo_dbcontrols.py

## The three layers

| Module | Delphi counterpart | What it holds |
|---|---|---|
| `dbwidgets` | `TDatabase` | `DB`, `DBSQLite`, `DBPostgres`, `DBMySQL` / `DBMariaDB` -- the extracted schema |
| `dbwidgets.dataset` | `TDataSet`, `TDataSource`, `TQuery` | `DataSet`, `QueryDataSet`, `DataSource`, `Field` -- cursor, edit state, master detail |
| `dbwidgets.dbcontrols` | Data Controls palette | the data aware widgets |
| `dbwidgets.binding` | -- | `bind`, `ui_loader` -- wiring a form from Designer |
| `dbwidgets.project` | -- | `Project` -- reading `dbwidgets.json` |
| `dbwidgets.connection` | -- | `ConnectionDialog` -- setting the connection up |
| `dbwidgets.designer_plugin` | -- | the Qt Designer plugin |
| `dbwidgets.widgets` | -- | the original API, kept for compatibility |

## Data controls

| Delphi | dbwidgets |
|---|---|
| `TDBText` | `DBText` |
| `TDBEdit` | `DBEdit` |
| `TDBMemo` | `DBMemo` |
| `TDBRichEdit` | `DBRichEdit` |
| `TDBImage` | `DBImage` |
| `TDBCheckBox` | `DBCheckBox` |
| `TDBRadioGroup` | `DBRadioGroup` |
| `TDBComboBox` | `DBComboBox` |
| `TDBListBox` | `DBListBox` |
| `TDBLookupComboBox` | `DBLookupComboBox` |
| `TDBLookupListBox` | `DBLookupListBox` |
| `TDBNavigator` | `DBNavigator` |
| `TDBGrid` | `DBGrid` |
| `TColumn` | `GridColumn` |
| `TDBCtrlGrid` | `DBCtrlGrid` |
| `TField` (`fkCalculated`) | `DataSet.add_calculated_field` |
| `TField` (`fkLookup`) | `DataSet.add_lookup_field` |

The whole Data Controls palette is covered except `TDBChart`, which would
pull in a plotting library.

## Schema of the test database

    CREATE TABLE city (
	   id INTEGER NOT NULL, 
	   name VARCHAR, 
	   photo BLOB,
	   PRIMARY KEY (id)
    );

    CREATE TABLE district (
	   id INTEGER NOT NULL, 
	   name VARCHAR, 
	   city_id INTEGER, 
	   PRIMARY KEY (id), 
	   FOREIGN KEY(city_id) REFERENCES city (id)
    );

## Reading the schema

First, create the database object by giving connection parameters.
For sqlite, file name is sufficient.

    db = DBSQLite("test.db")
    db.extract()

From that point, `db` has a dictionary of tables, table names being the key
value. A table has a dictionary of columns, column names being the key value.

If a foreign key is defined for any column, the referring table name is
`foreign_key_table` and the referring column name is `foreign_key_column` as
attributes of the column object.

    customer = db.record("customers", "id", 123)

## Datasets and master detail

A `DataSet` is a cursor over one table. It has a current record, an edit state
and, optionally, a master.

    from dbwidgets.dataset import DataSet, DataSource

    city = DataSet(db, "city", order_by='"name"').open()
    city_source = DataSource(city)

    district = DataSet(db, "district")
    district.set_master_source(city_source)
    district.open()

`set_master_source` was called without column names, so the link was read from
the foreign key `district.city_id -> city.id`. Pass `master_fields` and
`detail_fields` when you want to name them yourself.

Moving the master moves the detail:

    city.locate("name", "Ankara")
    print(district.record_count)      # districts of Ankara

Navigation follows the Delphi idiom:

    city.first()
    while not city.eof:
        print(city["name"])
        city.next()

So does editing. The dataset has the four states `INACTIVE`, `BROWSE`, `EDIT`
and `INSERT`, and every statement it runs is parameter bound:

    city.edit()
    city["name"] = "Ankara"
    city.post()                       # or city.cancel()

    district.insert()
    district["name"] = "Yeni ilce"    # city_id is prefilled from the master
    district.post()

    district.delete()

## Widgets

Every control takes a data source and a field name, and there is nothing else
to wire up:

    from dbwidgets.dbcontrols import DBGrid, DBNavigator, DBEdit, DBText

    DBGrid(parent, city_source)              # master grid
    DBNavigator(parent, city_source)         # first/prior/next/last/insert/...

    DBText(parent, city_source, "name")      # shows the selected city
    DBGrid(parent, district_source)          # shows its districts
    DBEdit(parent, district_source, "name")  # edits the current district

Selecting a row in the grid moves the dataset cursor, and moving the cursor
from anywhere else moves the selection, so the grid, the navigator and the
edit controls always sit on the same record. The navigator enables and
disables its buttons from the dataset state, the way the VCL one does.

### Grid columns and sorting

A grid shows every column by default. Name them to control the header, the
width, the alignment and the formatting, the way `TDBGrid`'s Columns editor
does:

    from dbwidgets.dbcontrols import DBGrid, GridColumn

    DBGrid(parent, city_source, columns=[
        GridColumn("id", title="No", width=60, alignment=Qt.AlignRight),
        GridColumn("name", title="İl adı"),
        GridColumn("salary", format="{:.2f}", read_only=True),
    ])

Clicking a header reorders the dataset; click again to reverse it. Pass
`sortable=False` to turn that off.

### Pictures

`city.photo` in `test.db` holds a small photograph of every city, so
`DBImage` has something real to show:

    DBImage(parent, city_source, "photo", stretch=True)

`DBImage` reads a blob and draws it, and unless it is read only it writes one
back. The VCL shortcuts work: Ctrl+C, Ctrl+V, Ctrl+X and Delete. So does
dropping an image file on it. The context menu carries the same commands plus
load and save, and the same things are available from code:

    image.load_from_file("view.jpg")
    image.save_to_file("view.jpg")
    image.paste()          # from the clipboard
    image.copy()
    image.cut()
    image.clear_image()    # sets the field to NULL

`stretch` scales the picture to the control, `center` places it when it is
not stretched, and `image_format` chooses what a written picture is encoded
as. With `auto_display` off the control does not decode the blob until
`load_picture()` is called or the control is double clicked, which is what
you want for a table of large pictures.

The photographs are fetched by `tools/make_city_photos.py`. They are the lead
images of the Turkish Wikipedia articles for each province, taken only when
the file is hosted on Wikimedia Commons, which accepts freely licensed work
only. Each is cropped to 4:3 and reduced to the same 160x120, and every
author and licence is listed in `CITY_PHOTO_CREDITS.md`, as those licences
require. To rebuild the column, at a different size if you like:

    python tools/make_city_photos.py --size 200x150 --quality 80

A blob shows as its size, not its bytes, anywhere a grid or a label would
otherwise print it, and a blob column cannot be edited in place.


`DBImage` reads a blob field and, unless it is read only, writes one back.
It has a context menu with copy, paste, load and clear, and the same
operations are available from code:

    image = DBImage(parent, pics_source, "photo", stretch=True)
    image.load_from_file("photo.png")
    image.paste()          # from the clipboard
    image.clear_image()    # sets the field to NULL

### Queries and joins

`DataSet` covers one table. For anything else, including joins, use
`QueryDataSet`. Parameters are written `:name` and are filled either from
`params` or from the current record of the master source:

    from dbwidgets.dataset import QueryDataSet

    report = QueryDataSet(db, '''
        select c.name as il, d.name as ilce
        from district d join city c on c.id = d.city_id
        where c.id = :id
        order by d.name
    ''')
    report.set_master_source(city_source)
    report.open()

The rows of a join cannot be written back to a single table, so a query
dataset is read only: editing is refused and every control bound to it goes
read only on its own.

### One panel per record

`DBCtrlGrid` repeats a panel down the page, one per record. Write a builder
that fills a single panel, and bind its controls to the panel's own source:

    def build_card(panel, source):
        layout = QFormLayout(panel)
        layout.addRow("No", DBText(panel, source, "id"))
        layout.addRow("Ad", DBEdit(panel, source, "name"))

    DBCtrlGrid(parent, district_source, build_card, rows=5)

Delphi paints its panel once per record, so only the current one is live.
Here each panel has its own `RecordSource`, so the controls on all of them
are real and editable. Clicking a panel makes its record current.

### Calculated and lookup fields

A dataset can carry fields that are not columns of the table. They show up
in grids and controls like any other field, and they are read only:

    # value pulled from another dataset, Delphi's fkLookup
    district.add_lookup_field("il_adi", "city_id", city_source, "id", "name")

    # value worked out per record, Delphi's fkCalculated
    district.add_calculated_field(
        "etiket", lambda record: f"{record['il_adi']} / {record['name']}")

    DBText(parent, district_source, "etiket")

### New records

`insert()` leaves the database defaults to the database, because some
drivers report them as SQL expressions rather than values. To prefill a new
record yourself, the counterpart of Delphi's `DefaultExpression`:

    district.defaults["name"] = "isimsiz"

While a dataset is inserting, a grid on it grows a blank row at the bottom,
the way Delphi's does, and that row is where the new record is typed.

### Lookups

`DBLookupComboBox` works two ways. With a `datasource` and a `datafield` it
edits a foreign key column of the current record:

    DBLookupComboBox(parent, list_source=city_source, key_field="id",
                     list_field="name", datasource=district_source,
                     datafield="city_id", navigates=False)

Without them it is a selector over the list source, moving that dataset's
cursor, so every detail follows the selection:

    DBLookupComboBox(parent, list_source=city_source,
                     key_field="id", list_field="name")

## Qt Designer

Every control can be laid out in Designer, and the form can show **real data
while you are designing it**.

### The controls in the widget box

Point Designer at the `designer_plugins` folder and the controls appear in a
`dbwidgets` group, with `dataSourceName`, `dataField` and the rest in the
property editor:

    PYSIDE_DESIGNER_PLUGINS=/path/to/dbwidgets/designer_plugins pyside6-designer

**It has to be that folder, not the repository.** Designer executes every
Python file in the directory it is given, so pointing it at the repository
would run the demo applications and hang. That is also why
`designer_plugins/` holds one file and nothing else.

Two more things about how Designer loads a Python plugin, both of which bite
silently: it runs the file without setting `__file__`, and it runs it with
separate globals and locals, so a function defined in that file cannot see
the imports above it. `register_dbwidgets.py` is written around both.

On macOS, `pyside6-designer` may stop with a `dyld` error about a missing
`Python3.framework` under `/Applications/Xcode.app`. That happens when only
the command line tools are installed. Run Designer directly and point it at
the framework that is actually there:

    FRAMEWORKS=/Library/Developer/CommandLineTools/Library/Frameworks
    export DYLD_INSERT_LIBRARIES=$FRAMEWORKS/Python3.framework/Versions/3.9/Python3
    export PYSIDE_DESIGNER_PLUGINS=$PWD/designer_plugins
    .venv/lib/python3.9/site-packages/PySide6/Designer.app/Contents/MacOS/Designer designer_demo.ui

Without the plugin the controls still work through promotion: drop the base
widget, right click, *Promote to...*, class `DBEdit`, header
`dbwidgets.dbcontrols`.

### Live data while designing

A form names its sources instead of holding them. A project file next to the
`.ui` says what those names mean:

    // dbwidgets.json
    {
      "database": {"driver": "sqlite", "filename": "test.db"},
      "sources": {
        "city":     {"table": "city", "order_by": "\"name\""},
        "district": {"table": "district", "master": "city"},
        "report":   {"sql": "select ... where c.id = :id", "master": "city"}
      }
    }

The plugin opens it read only and hands the sources to the controls, so
typing `district` into `dataSourceName` fills the grid with real rows there
and then, master detail included. The last part of `dbwidgets_demo.mp4`
shows exactly that, inside a real Designer. Read only means laying out a form can
never change data. Set `DBWIDGETS_PROJECT` to point at a specific file,
otherwise the plugin walks up from the working directory looking for
`dbwidgets.json`.

### Setting up the connection from inside Designer

The plugin puts a **dbwidgets** menu on Designer's menu bar. *Data sources...*
opens a dialog with two tabs:

- **Connection** picks the driver (SQLite, PostgreSQL, MySQL, MariaDB), the
  database file or the server details, and *Test connection* says straight
  away whether it works and how many tables it found. A password is only
  written to the project file if you tick the box; otherwise it is used for
  the design time connection and forgotten.
- **Sources** lists the tables. Tick the ones the form needs, name them, and
  set an order. The master of each table is filled in from its foreign key
  and can be changed.

Saving writes `dbwidgets.json` and reopens the sources, so **the widgets on
the open form refill immediately** with data from the new database. Nothing
has to be restarted. *Reload project* does the same for a file that was
edited by hand.

The dialog is not tied to Designer, so a project can be set up from a script
too:

    from dbwidgets.connection import ConnectionDialog
    ConnectionDialog.edit_project("dbwidgets.json")

### Running the form

**`pyside6-uic` drops properties it does not recognise**, so the
`dataSourceName` set in Designer never reaches the generated Python. Load
the `.ui` at run time instead, which keeps them:

    from dbwidgets.binding import bind, ui_loader
    from dbwidgets.project import Project

    form = ui_loader().load("designer_demo.ui")
    bind(form, Project("dbwidgets.json").sources)

That is the whole application: the form says which source each widget wants,
the project file says what the sources are, and `bind` puts them together.
`designer_demo.ui` and `designer_demo.py` in the repository are a working
example.

If you would rather compile with `pyside6-uic`, promotion still works, you
just wire the sources yourself after `setupUi`:

    self.setupUi(self)
    self.nameEdit.set_data_source(self.district_source, "name")

## The original API

`dbwidgets.widgets` still provides `DBComboBox`, `DBTableWidget` and
`DBNavigatorWidget` with their original constructors, signals and methods.
They are now built on top of the dataset layer. `testapp.py` is the example:

    self.db = DBSQLite("test.db")
    self.db.extract()

    self.city = DBComboBox(self.widget1, self.db, "city", "name", "id", 34)
    self.district = DBComboBox(self.widget2, self.db, "district", "name", "id")
    self.district.setMaster(self.city, "city_id")

    self.districtlist = DBTableWidget(self.widget3, self.db, "district")
    self.citylist = DBTableWidget(self.widget4, self.db, "city")
    self.districtlist.setMaster(self.citylist, "city_id")

`DBComboBox(placeholder, db, table, textcolumn, idcolumn, default_id)` fills a
combo from a table, `setMaster(masterwidget, mycolumn)` connects a detail
widget to a master one, and `DBNavigatorWidget(placeholder, db, table)` now
builds an editor per column plus a working button row.

## Documentation and examples

`docs/` holds a walkthrough that goes from an empty directory to a form drawn
in Qt Designer showing live data, and a reference for every class.
`dbwidgets.pdf` is the same thing as one file.

Every code block in the walkthrough is a file in `examples/`, included in the
document as it is on disk, and each one is run by the test suite, so the code
in the documentation is the code that works:

| Example | Shows |
|---|---|
| `01_dataset_basics.py` | the schema, a cursor, edit and post -- no widgets |
| `02_first_form.py` | a grid, a navigator and two editors on one source |
| `03_master_detail.py` | a detail linked through the foreign key |
| `04_lookup_and_picture.py` | the two jobs of a lookup combo, and a blob |
| `05_query_and_fields.py` | a join, a lookup field, a calculated field |
| `06_project_and_bind.py` | the same thing described in `dbwidgets.json` |
| `07_designer_form.py` | loading and binding a form built in Designer |
| `08_other_databases.py` | PostgreSQL, MySQL and MariaDB |

Run one with:

    python examples/01_dataset_basics.py

To build the documentation. The PDF is drawn by rst2pdf, so no LaTeX is
needed:

    pip install sphinx sphinx_rtd_theme rst2pdf
    cd docs
    python -m sphinx -b html source _build/html
    python -m sphinx -b pdf source _build/pdf

The pictures of Qt Designer in the walkthrough are not screenshots taken by
hand. Designer is a Qt application, so it is run under the offscreen platform
and the plugin, which lives inside its process, photographs it:

    python tools/capture_designer_docs.py

## Tests

    pip install pytest
    pytest

The suite runs headless, on a private copy of `test.db`, so it never touches
the database file in the repository.

The postgresql tests need a server. `pgserver` brings its own binaries and
runs them over a unix socket, so nothing has to be installed system wide:

    pip install pgserver psycopg2-binary
    pytest

Without those two packages `tests/test_postgres.py` skips itself.

## Trademarks

Delphi and VCL are trademarks of Embarcadero Technologies, Inc. Borland is a
trademark of its respective owner. This project is not affiliated with,
endorsed by, or sponsored by any of them. Those names are used here only to
describe which well known components the classes in this library are modelled
on. dbwidgets is an independent implementation written from scratch; no
Delphi, VCL or Lazarus source code was used.

## Demo

`dbwidgets_demo.mp4` walks through the whole thing, from `git clone` to a
form built in Qt Designer: every control with real rows in it, master detail
and editing, and the Designer integration with live data.

![video](https://raw.githubusercontent.com/ilkermanap/dbwidgets/master/dbwidgets_demo.mp4)

It is generated, not recorded, so it can be rebuilt after any change:

    pip install PySide6            # and ffmpeg on the path
    python tools/make_demo_video.py

The terminal panels hold the real output of the commands that were run, and
the widget panels are the real controls rendered offscreen against
`test.db`. Nothing in it is mocked up.

The earlier recording of the original API is still here:

![video](https://raw.githubusercontent.com/ilkermanap/dbwidgets/master/testapp.mp4)
