Script to generate schema (models) from PostgreSQL (and possibly others)

[Mariano Reingart]

Hi, attached is a PostgreSQL enhanced versión of Alexandre Andrade mysql script.

It uses information_schema (ANSI Standard), so it might support others
databases too.
It connects to live databases (doesn't need SQL dump), and supports:
keyed tables, most data types, default values, constraints (unique/not
null/referential fk) and comments.

Best regards,

Mariano Reingart
http://www.sistemasagiles.com.ar
http://reingart.blogspot.com

On Mon, Oct 4, 2010 at 1:30 AM, Álvaro Justen [Turicas]
<alvaro...@gmail.com> wrote:
> On Sun, Oct 3, 2010 at 19:37, Alexandre Andrade
> <alexand...@gmail.com> wrote:
>> Alvaro,
>>
>> I didn't see this post before.
>>
>> this can be a great feature and I will trye to adapt it to postgresql and
>> mysql, at least.
>>
>> if massimo already don't use it, with sqlite, postresql and mysql, it can
>> worth the value.
>
> Please post news about this. :-)
>
> --
> Álvaro Justen - @turicas
>  http://blog.justen.eng.br/
>  21 9898-0141
>

[Johann Spies]

Thanks for this one. I had no success with the mysql script and so
far more success with this one. I see it creates definitions like
this one:

Field('journal_id', type='reference journal'),

about which web2py complains.

I suppose I have to clean that up by hand.

Regards
Johann

--
 May grace and peace be yours in abundance through the full knowledge
of God and of Jesus our Lord!  His divine power has given us
everything we need for life and godliness through the full knowledge
of the one who called us by his own glory and excellence.
                                                    2 Pet. 1:2b,3a

[Stef Mientki]

On 05-10-2010 01:10, Mariano Reingart wrote:
> Hi, attached is a PostgreSQL enhanced versi�n of Alexandre Andrade mysql script.

>
> It uses information_schema (ANSI Standard), so it might support others
> databases too.
> It connects to live databases (doesn't need SQL dump), and supports:
> keyed tables, most data types, default values, constraints (unique/not
> null/referential fk) and comments.
>

Is it not necessary to put "cur.close() before return succesfull ?
cheers,
Stef

def query(conn, sql,*args):
"Execute a SQL query and return rows as a list of dicts"
cur = conn.cursor()
ret = []
try:
if DEBUG: print >> sys.stderr, "QUERY: ", sql % args
cur.execute(sql, args)
for row in cur:
dic = {}
for i, value in enumerate(row):
field = cur.description[i][0]
dic[field] = value
if DEBUG: print >> sys.stderr, "RET: ", dic
ret.append(dic)
cur.close () ??
return ret
finally:
cur.close()

[Mariano Reingart]

On Tue, Oct 5, 2010 at 10:49 AM, Stef Mientki <stef.m...@gmail.com> wrote:
>  On 05-10-2010 01:10, Mariano Reingart wrote:

>> Hi, attached is a PostgreSQL enhanced versión of Alexandre Andrade mysql script.

cur.close is at finally clause (it always gets executed)

[Mariano Reingart]

On Tue, Oct 5, 2010 at 5:38 AM, Johann Spies <johann...@gmail.com> wrote:
> Thanks for this one.  I had no success with the mysql script and so
> far more success with this one.  I see it creates definitions like
> this one:
>
> Field('journal_id', type='reference journal'),
>
> about which web2py complains.
>
> I suppose I have to clean that up by hand.
>

Yes, there are some known issues now:
* you have to order table definitions correctly to prevent references
to undefined tables
* you cannot mix normal tables (with id field) and keyed tables (with
primarykey)

Regards,

[Stef Mientki]

On 05-10-2010 17:36, Mariano Reingart wrote:
> On Tue, Oct 5, 2010 at 10:49 AM, Stef Mientki <stef.m...@gmail.com> wrote:
>> On 05-10-2010 01:10, Mariano Reingart wrote:

>>> Hi, attached is a PostgreSQL enhanced versi�n of Alexandre Andrade mysql script.

didn't know that ...
,,, unitl now.
Thanks,
cheers,
Stef