Re: [SQLObject] Creating database tables from queries based on ORM API?

[Oleg B. <ph...@ph...>]

On Sat, Apr 13, 2019 at 07:12:37PM +0200, Markus Elfring <Mar...@we...> wrote:
> > Do you want to implement it?
> 
> I guess that I am not familiar enough with corresponding software
> design constraints (at the moment).
> 
> Will such software extensions become more feasible?

Yes, I think so. The API perhaps would be simple, something like

    class SQLObject(...):
        @classmethod
        def createAsSelectSQL(cls, selectFrom):
            ...

        @classmethod
        def createAsSelect(cls, selectFrom):
            conn = connection or cls._connection
            query = cls.createAsSelectSQL(selectFrom)
            conn.query(query)

where ``selectFrom`` is ``SelectResults`` instance. The method shod
construct ``CREATE TABLE ... AS SELECT ...`` but it also must construct
a list of columns (instances of ``col.Col`` classes) and call
``cls.sqlmeta.addColumn(columnDef)`` for every column in the list.

And here I foresee a problem. It's possible to create a table in the
database but how to recreate the class with all columns on the second
run of the program or in another program that will use the class? The
code must recreate the list of columns again without executing ``CREATE
TABLE AS SELECT`` query. Perhaps an API to produce a list of columns
from ``selectFrom`` should be a separate API that could be called eve
when the table is already exists.

> Regards,
> Markus

Oleg.
-- 
    Oleg Broytman            https://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.