Multiple tables: update/insert/delete: in one form

[Vineet]

Hi,
I have a task related to the below-referred thread (but with a
different requirement).
I have multiple MySQL tables to be tackled in one form.
The user will make changes to certain fields.
As a result,
table1 might require : insert or update or delete or no operation;
table2 might require : insert or update or delete or no operation;
table3 might require : insert or update or delete or no operation;

I can't predict as to what table will require which operation.
So it's not possible to hardcode the operation like
form[0].insert(2,TR.......etc.

(In VFP, I am using a class called as CursorAdaptor (a visual DAL tool
out there), which monitors these operations on tables and executes
them to the database).

A similar functionality would be of a great value to me in web2py.

Is there any way to achieve this in web2py?

Thanks in advance for your advice.

Vineet.
================================================

On Nov 21 2008, 7:05 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> You have the option of using form=SQLFORM and then form[0].insert(2,TR
> (...)) the rows from the other table.
> It may be easier to create the form using form_factory.
>
> Massimo
>
> On Nov 21, 12:34 am, mmstud <mms...@gmail.com> wrote:

>
> > Im thinking to create an insert and update form with fields taken from
> > multiple tables. Person has address, email and phone instances on
> > separate tables, but for simple interaction, they should be present on
> > same form view. What is the best way to solve this kind of situation
> > at the moment, does SQLFORM the job?
>
> > On update form there would be combobox with input and selectbox
> > combined. Input for new email, address, phone entries and select for
> > possible older entries.
>
> > -Marko

[Massimo Di Pierro]

Can you make a concrete example? to some extend you can do this but in
order to be general, it requires some programming. Provide an example
I can can show I would do it.

[Vineet]

I will give a very simplified example of my task (pl. excuse me if it
is still verbose).

A form deals with "Automobile Vehicle Models - Warranty Master".
It displays the model names (of vehicles).
User selects a model to modify warranty details.
The form displays : 1) select basic warranty and 2) select extended
warranty.

Vehicle model master--
db.define_table('mdl',
Field('mdlid','id'),
Field('mdlnm',unique=True),
format='%(mdlnm)s')

Basic warranty master--
db.define_table('wr',
Field('wrid', 'id'),
Field('wrnm',unique=True),
format='%(wrnm)s')

Extended warranty master--
db.define_table('extwr',
Field('extwrid', 'id'),
Field('extwrnm',unique=True),
format='%(extwrnm)s')
(these tables are filled from separate forms)

In our form, the user may add/edit/delete data in these tables through
a single form--

Table for recording modelwise basic warranty--
db.define_table('mdlwr',
Field('mdlwrid','id'),
Field('mdlid',db.mdl),
Field('wrid', db.wr)

Table for recording modelwise extended warranty--
db.define_table('mdlextwr',
Field('mdlextwrid','id'),
Field('mdlid',db.mdl),
Field('extwrid', db.extwr)

(why 2 tables when 1 would be sufficient? because these are required
for normalizing MySQL table-structures)

If any further explanation of my task is required, pl. tell me.

Thanks,

Vineet
=============================================================================

On May 11, 12:21 am, Massimo Di Pierro <massimo.dipie...@gmail.com>
wrote:

> Can you make a concrete example? to some extend you can do this but in
> order to be general, it requires some programming. Provide an example
> I can can show I would do it.
>
> On May 10, 12:26 pm, Vineet <vineet.deod...@gmail.com> wrote:

>
> > Hi,
> > I have a task related to the below-referred thread (but with a
> > different requirement).

> > I havemultipleMySQL tables to be tackled in one form.

> > The user will make changes to certain fields.
> > As a result,
> > table1 might require : insert or update or delete or no operation;
> > table2 might require : insert or update or delete or no operation;
> > table3 might require : insert or update or delete or no operation;
>

> > I can't predict as to whattablewill require which operation.

> > So it's not possible to hardcode the operation like
> > form[0].insert(2,TR.......etc.
>
> > (In VFP, I am using a class called as CursorAdaptor (a visual DAL tool
> > out there), which monitors these operations on tables and executes
> > them to the database).
>
> > A similar functionality would be of a great value to me inweb2py.
>
> > Is there any way to achieve this inweb2py?
>
> > Thanks in advance for your advice.
>
> > Vineet.
> > ================================================
>
> > On Nov 21 2008, 7:05 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
> > > You have the option of using form=SQLFORM and then form[0].insert(2,TR
> > > (...)) the rows from the othertable.
> > > It may be easier to create the form using form_factory.
>
> > > Massimo
>
> > > On Nov 21, 12:34 am, mmstud <mms...@gmail.com> wrote:
>
> > > > Im thinking to create an insert and update form with fields taken from

> > > >multipletables. Person has address, email and phone instances on

[pbreit]

Normalizing is not always the best approach. If the database has not yet been designed, perhaps reconsider. I still don't really understand what you are trying to do but 5 small tables might not be necessary.

I would suggest getting some code working with 2 tables an then go from there.

Some options:

http://web2py.com/book/default/chapter/07#One-form-for-multiple-tables

http://web2py.com/book/default/chapter/07#Multiple-forms-per-page

Another approach would to use FORM or SQLFORM.factory and then coding the database updates. You lose some of the Web2py features but it might still be the easiest way to do it.

But my first suggestion would be to try and simplify what you are intending to do.

[Vineet]

pbreit,
As you say, wherever possible or required, I have already denormalized
the table-structures.
Apart from normalization / denormalization issue, in many other forms,
I need to insert/update/delete records in multiple tables from one
form.
(as I mentioned earlier, there will be a mix of insert/update/delete
of record(s) in different tables.)

Massimo,
If you too haven't understood what I intend to do with these 5 tables,
I can elaborate the idea.
Pl. tell me accordingly.
The very success of developing my project using web2py hinges on this
functionality.

---Vineet
================================================

On May 11, 5:17 am, pbreit <pbreitenb...@gmail.com> wrote:
> Normalizing is not always the best approach. If the database has not yet
> been designed, perhaps reconsider. I still don't really understand what you
> are trying to do but 5 small tables might not be necessary.
>
> I would suggest getting some code working with 2 tables an then go from
> there.
>

> Some options:http://web2py.com/book/default/chapter/07#One-form-for-multiple-tableshttp://web2py.com/book/default/chapter/07#Multiple-forms-per-page

[Simon Ashley]

Since no one has replied for a couple of days, I'll toss my 2 cents
in. Don't how much value this could be, as we just evaluating web2py
and python but in another environment we could have written data in
the normalised records to a temporary record in a denormalised,
virtual table, update and saved this record, and then do selective
updates, insertions and deletions on each normalised record in the
original tables. Not that elegant but ...

On May 13, 9:17 pm, Vineet <vineet.deod...@gmail.com> wrote:
> pbreit,
> As you say, wherever possible or required, I have already denormalized
> the table-structures.
> Apart from normalization / denormalization issue, in many other forms,
> I need to insert/update/delete records in multiple tables from one
> form.
> (as I mentioned earlier, there will be a mix of insert/update/delete
> of record(s) in different tables.)
>
> Massimo,
> If you too haven't understood what I intend to do with these 5 tables,
> I can elaborate the idea.
> Pl. tell me accordingly.
> The very success of developing my project using web2py hinges on this
> functionality.
>
> ---Vineet
> ================================================
>
> On May 11, 5:17 am, pbreit <pbreitenb...@gmail.com> wrote:
>
>
>
>
>
>
>
> > Normalizing is not always the best approach. If the database has not yet
> > been designed, perhaps reconsider. I still don't really understand what you
> > are trying to do but 5 small tables might not be necessary.
>
> > I would suggest getting some code working with 2 tables an then go from
> > there.
>

> > Some options:http://web2py.com/book/default/chapter/07#One-form-for-multiple-table...

[Vineet]

Simon,
That is a good idea.
In fact, almost the same approach is being followed in VFP cursor
adaptor (the difference being that, data from MySQL tables is
reproduced in same table structures, i.e cursors).

Here, by virtual table, do you mean to say that we should reproduce
the data into dictionaries by fetching a dict cursor from MySQLdb?
(in DABO, they have an excellent concept of bizobj for this task).

I am not asking for exact code, but can you suggest an approach to
tackle the task?

Dear Massimo & other friends,
can you pl. suggest something to solve my problem?

Thanks,
Vineet.

=================================================================

On May 15, 5:07 pm, Simon Ashley <gregs...@gmail.com> wrote:
> Since no one has replied for a couple of days, I'll toss my 2 cents
> in. Don't how much value this could be, as we just evaluating web2py
> and python but in another environment we could have written data in
> the normalised records to a temporary record in a denormalised,

> virtualtable, update and saved this record, and then do selective

> updates, insertions and deletions on each normalised record in the
> original tables. Not that elegant but ...
>
> On May 13, 9:17 pm, Vineet <vineet.deod...@gmail.com> wrote:
>
>
>
>
>
>
>
> > pbreit,
> > As you say, wherever possible or required, I have already denormalized
> > thetable-structures.
> > Apart from normalization / denormalization issue, in many other forms,

> > I need to insert/update/delete records inmultipletables from one

[villas]

Hi Vineet

I'm sorry you didn't get a satisfactory answer, but I imagine there
are group members like myself who would love to contribute but don't
quite understand. Let's see if I got this right...

You have a tricky form which might affect several different tables
depending on which fields are completed? I see the data model, but
what is the problem?

Are you having difficulty writing some tricky logic into a
controller? If so, what do you have so far and what is the issue?

Maybe we could see the form?

Best regards,
D

[Simon Ashley]

Memory tables referred to have no ability to write to disk. Generally
we've used these for report accumulations. You could use a MySQL table
and delete the record at the end (if need be). Crude and there should
be a more pythonic method, but should work.

Define temp table fields as per the following:

1. rec id
2. table1.uid
3. table1.variable
4. table2.uid
5. table2.variable
6. table3.uid
7. table3.variable

Populate the fields before the load. The uids will of course be hidden
on the form but used after the submit in separate database actions.

Zero uid's would trigger inserts, > 0 for deletions where variables
are blank or zero, edits where not. There is a level of programming
involved; Massimo implied that.

In your case, I would be reluctant to denormalise as further
complexity could be cumbersome.
(if not unworkable. Can think of examples in an application that we
are looking at converting that would make anyone's mind spin).

YMMV

[Vineet]

Dear Simon & villas,
Thanks for your replies.
Let me digest your ideas by putting them to work.
Then I can ask for your help on fine-tuning the form.
--------------------------------

In reply to villas's post, I have this to write---
Dear villas,
Seems that you have got my problem almost correctly, with one
difference.
Let me brief the problem here again.

A form is handling table-1, table-2, table-3.
User will manipulate the data so that there may be an insert statement
for table-1, update statement for table-2, delete statement for
table-3 (or any other combination of DML statements for each table).
I can't hardcode DML statements for tables, since I can't predict
which table would be receiving
what DML statement.
If that were the case, I would have used form=SQLFORM and then
form[0].insert(2,TR(...))
OR use of SQLFORM.Factory

As such, I could not get started by writing a controller (since I am
stuck-up with how to tackle this logic).
(If you require, I can attach a screenshot of such form prepared in
VFP to get the idea of why this is required.)

So, in a nutshell, my question is this ---
How to identify which DML statement is required for
which table.
Which components of web2py can be used in building a generic
controller for handling such kind of form.

Thanks,
Vineet
===========================================

[villas]

Hi Vineet

I think I am understanding more, but still not enough to properly
help you :(

The business logic of your system is entirely in your own hands. The
group cannot really help with that.

If you have a tricky form which will generate a multitude of
outcomes, you should try making a flowchart for the logic. If the
data can be entered in multiple stages, you might have to design your
form as more of a wizard.

You can make a form with whatever fields you want, validate the data
however you want, accept the form and then decide what queries need
to be run, and then run them. It doesn't matter how many tables
there are.

If this can be done in one step great, otherwise you'll have to think
about the wizard. You should investigate what happens if the process
should cancel/fail part way through, will you have to reverse some
entries? If so, you should prob make your queries inside a
transaction and then roll-back (if your DB can support this).

How long will the queries take to run? If they take a long time, you
may prefer to create some kind of queue. Hopefully this can be
avoided.

Regards, D

[Vineet]

Villas,
Your direction is perfect.
While programming in VFP, in some of my forms, I had designed multiple
steps (wizard).
After breaking down the form into logical steps, further breaking down
is not possible.
Let me give a concrete example here. (back end database is MySQL).

Consider an automobile workshop for repairing vehicles.
A jobcard is prepared for every vehicle attended to.
It contains complaints, repair operations, spare parts required,
mechanic details, dates & times of vehicle in/repaired/billed/out,
etc.
These things are picked-up from their respective masters data from
separate tables.
The jobcard 'wizard' is divided into steps like 'receive the vehicle',
'enter repair operations', 'enter spare parts requirement',
'enter spare parts issued', 'billing', etc.

Now, for an example, consider the step of 'enter repair operations'.
Here, the data is saved through a transaction into multiple semi-
normalised tables (e.g. separate tables for warranty repairs, paid
repairs, free-services, tax-details, mechanic details, etc.).
When a user re-opens earlier jobcard, he/she may change an amount,
delete a repair operation, add another one, and so on...
For every table, it is not possible to break-down the form in steps,
since it would become messier & impractical.

Now I hope you can judge as to why I am trying so much to get around
the issue of handling multiple tables from a single form (or through a
step in a form-wizard).

Should you need any other detail for helping me, pl. let me know.
In the meantime, I am figuring out how to fit Simon's idea in the app.

Thanks,
Vineet
===================================================

[villas]

It seems you have a very difficult workflow, but have at least
established how your data should look.

So, ignore the tables a little for now, try to visualise what the
user will see on the screen. How would your users like to enter their
data into the forms? What data needs to be entered, by whom, at
what point? How will those screens look?

Once you have a better idea of the workflow and how the screens might
appear, you should be able to design some forms and get down to
processing the data. Try sketching some flowcharts and mock-up some
screens to see how the gui could work.

Web2py has all the ingredients you need to split a big horrible job in
smaller easier ones. It is extremely flexible for specifying queries
depending on your 'decision-tree'.

[Vineet]

Simon,
I thought on the idea of adding a MySQL temp table to manage insert/
del/update on multiple tables.
But there would be a large no. of such forms requiring this
functionality.
It is better to avoid fiddling with the schema (which is the database-
tier) for the sake of business-logic tier.

What if I add a temporary table in DAL itself (along with the other
linked tables)?
Would it break the web2py logic of generating, validating, and saving
the form (if this table is used for generating the form)?
I mean, if a form is generated from such a temp DAL table, whether it
is possible to save data in multiple different tables.

---Vineet
============================================

[Vineet]

Villas,
Let me give a little background.
I have this entire massive application successfully running at client-
sites in Microsoft Visual FoxPro.
I have decided to shift from licensed platform to open-source
platform.
Chose Python for programming language & web2py for framework.

My present task is not to define the app from scratch.
Because the database-logic, business logic, UI is already there.
I am trying to port the existing app to Python web framework.

I am encouraged by your saying that ---

"Web2py has all the ingredients you need to split a big horrible job
in
smaller easier ones. It is extremely flexible for specifying queries
depending on your 'decision-tree'. "

Pl. help me solve my said issue in 'web2py'.

--Vineet
=============================================

[Vineet]

Dear Massimo,
Can I benefit from your expert advise too?

I am banking on Python & especially web2py for porting my apps from
Microsoft platform.
I tested turbogears but abandoned it since I found it too complex for
simple tasks, a lot of dependencies, memory savvy, weak docs, less
active community, etc.
Web2Py attracted me as it excels on all the above-referred points and
many others.

I am trying to port an ERP (enterprise resource planning) system for
automobile dealerships from VFP to web2py.
After this, there are be many more apps waiting to be ported.
My apps are complex & database oriented. (not just like 'yet another
blog site' or image upload, bulletin board, etc.)

Hope to get the helping hand from w2p community.

Thanks,
Vineet

[pbreit]

There might have been some confusion. You don't have to break your form or pages into multiple steps. But you can break the code in your controller into different steps. One controller can work with multiple tables.

You can do stuff like:

form_car = SQLFORM(db.car)

form_customer = SQLFORM(db.customer)

if form_car.accepts(request.vars, session, dbio=False):

    if form_customer.accepts(request.vars, session, dbio=False):

        do something

return dict(form_car=form_car, form_customer=form_customer)

You might need to use dbio=False so that Web2py doesn't automatically update the database. This can all be done on one web page.

[Massimo Di Pierro]

I have not closely followed this thread so I am missing something but
this:

if form_car.accepts(request.vars, session, dbio=False):
if form_customer.accepts(request.vars, session, dbio=False):
do something

will not work because you cannot have nested accepts.

[pbreit]

Oops, my bad. Sorry about that.

I was probably mis-thinking about having them at the same level. This might make the logi more difficult or impossible.

if form_car.accepts(request.vars, session, dbio=False): 
    do something

[villas]

Hi Vineet,

If you already wrote the app in Foxpro then it should be much easier
to port. Take each Foxpro screen and make something similar in
Web2py?

I appreciate that might sound too facile, but when you tell me what's
wrong with that idea, I might be able to see better where your
difficulty lies.

Regards, D

[Jay]

Vineet,

I wonder if this is what you want?

I use this all the time. I have company info in a table and address in
another, and my form contains company and address fields.

db.define_table('company',
Field('name'),
Field('addressid', db.address),
migrate='company.table')
db.define_table('address',
Field('street'),
Field('city'),
Field('state')
Field('zip'),
migrate='address.table')

In the controller: (company.py):

def newcompany():
'Get the data for a new company'
fields = 'name street city state zip'.split(' ')
form = SQLFORM.factory(db.address,db.company, fields=fields,
formstyle='divs', submit_button='Save
Company')

if form.accepts(request.vars, session):
coid =
db.company.insert(**db.company._filter_fields(form.vars))
addrid =
db.address.insert(**db.address._filter_fields(form.vars))
db(db.company.id == coid).update(addrid = addrid)
elif form.errors

[Jay]

See the very bottom, I am so used to emacs keystrokes, that I
accidentally posted this before I was done. :(
Similar code works for update too.

Jay

return dict(form=form) # this line was missing

[Simon Ashley]

Vineet,

Agreed, the temp file should be in the DAL.
I don't see at this stage why it would be break the logic.

[Simon Ashley]

Vineet,

We're doing the same in a different client server environment.
Lots of business logic, 700+ tables, 10,000+ tasks to translate.
We intend writing a high level tool as a web2py code generator, that
will serve as a gateway between the 2.
However we're probably at a lower level on the learning curve.

[Vineet]

At last, after much of sweat & toil, I could write a code for managing
multiple tables (conditional insert or update or delete) from single
form.
I agree, the code is in very raw form & may not be ‘pythonic’.
There are code repeatitions.
But at least I have something to go ahead & build a refined structure.
I present my code as under. (Comments are given to explain the flow)
I would highly welcome any ideas/suggestions for improvements.
##########
Code starts here
##########
MODELS:
db.define_table('mdlmst',
Field('mdlmstid','id'),
Field('mdlmstcd'),
Field('mdlmstnm'),
migrate=False,
format='%(mdlmstnm)s'
)

db.define_table('wrmst',
Field('wrmstid','id'),
Field('wrmstcd'),
Field('wrmstnm'),
migrate=False,
format='%(wrmstnm)s'
)

db.define_table('extwrmst',
Field('extwrmstid','id'),
Field('extwrmstcd'),
Field('extwrmstnm'),
migrate=False,
format='%(extwrmstnm)s'
)

# from the FORM, data will be populated in the following two tables

db.define_table('mdlwr',
Field('mdlwrid','id'),

Field('mdlmstid',db.mdlmst),
Field('wrmstid',db.wrmst),
migrate=False
)

db.define_table('mdlextwr',
Field('mdlextwrid','id'),

Field('mdlmstid',db.mdlmst),
Field('extwrmstid',db.extwrmst),
migrate=False
)

CONTROLLERS:
### ‘modelwar’ controller will render the records from ‘mdlmst’ table
def modelwar():
models =
db(db.mdlmst.mdlmstid>0).select(orderby=db.mdlmst.mdlmstnm)
return dict(models=models)

### after clicking a particular record, ‘war_edit’ controller will
manage the tables – ‘mdlwr’ & ‘mdlextwr’

def war_edit():
mdl_id = request.args(0) # variable identifying the
‘mdlmstid’ (which record to be modified)
mdl_nm = request.args(1) # variable for getting ‘mdlmstnm’
warset = db(db.mdlwr.mdlmstid==mdl_id) # fetch a set
extwarset = db(db.mdlextwr.mdlmstid==mdl_id)
warlist = db(db.mdlwr.mdlmstid==mdl_id).select() # get a ROW
object
extwarlist = db(db.mdlextwr.mdlmstid==mdl_id).select()

form_war=FORM(TABLE(TR("Basic Warranty",
SELECT(_type="select",_name="baswar",*[OPTION(x.wrmstnm,_value=x.wrmstid)for
x in db().select(db.wrmst.ALL)]),
TR("Extended Warranty",
SELECT(_type="select",_name="extwar",*[OPTION(x.extwrmstnm,_value=x.extwrmstid)for
x in db().select(db.extwrmst.ALL)]),
TR("", INPUT(_type='submit',_value='Save')), ))))

# pre-populate the fields in‘form_war’
if len(warlist)>0:
form_war.vars.baswar = warlist[0].wrmstid

if len(extwarlist)>0:
form_war.vars.extwar = extwarlist[0].extwrmstid

# after successful form submission, manage the table 'mdlwr'
if form_war.accepts(request.vars, session):
if len(warlist)>0: # if there was any record in the list
fetched from database & sent to FORM,
if form_war.vars.baswar=='': # delete if value returned
from FORM field is blank
warset.delete()
else:
warset.update(wrmstid=form_war.vars.baswar) # else
update,
else:
db.mdlwr.insert(mdlmstid=mdl_id,
wrmstid=form_war.vars.baswar) # else insert

# Similarly, manage the table 'mdlextwr'
if len(extwarlist)>0:
if form_war.vars.extwar=='':
extwarset.delete()
else:
extwarset.update(extwrmstid=form_war.vars.extwar)
else:
db.mdlextwr.insert(mdlmstid=mdl_id,
extwrmstid=form_war.vars.extwar)

response.flash = 'Warranty definition saved'
return dict(form_war=form_war,mdlnm=mdl_nm)

VIEW for 'mdlmst' table

{{response.files.append(URL(r=request,c='static',f='jquery.dataTables.min.js'))}}
{{response.files.append(URL(r=request,c='static',f='demo_table.css'))}}
{{extend 'layout.html'}}
<script>jQuery(document).ready(function()
{ jQuery('.smarttable').dataTable();});</script>
<h1>Modelwise Warranty Master</h1>
<table class="smarttable">
<thead> <tr> <th>Model ID</th><th>Model Code</th><th>Model Name</
th> </tr></thead>
<tbody>{{for model in models:}}
<tr>
<td>{{=model.mdlmstid}}</td>
<td>{{=model.mdlmstcd}}</td>
<td>{{=model.mdlmstnm}}</td>
<td>{{=A('edit
warranty',_href=URL('war_edit',args=[model.mdlmstid,model.mdlmstnm]))}}
</tr>
{{pass}}
</tbody>
</table>
####
Code ends here
####

A generic class is needed to be written for managing actions (insert/
update/delete) based on the results fetched from database & return
values from FORM. Hopefully, the same might be useful to many other
web2py programmers also.

I look forward to your ideas for enhancement.

Thanks,
Vineet

[Vineet]

Can anybody toss his/her ideas on my code given in the previous post?
---Vineet

[trr]

Have you switched over from VFP to web2py? If so I would like to take your guidance for a similar data intensive app.