webmcp
annotate framework/env/param/update_relationship.lua @ 28:ea2e8f3a2776
allow webmcp path to be set in cgi script
this allows another script to include the webmcp script when the cwd is not the cgi-bin. the script needs to set the WEBMCP_PATH variable.
this allows another script to include the webmcp script when the cwd is not the cgi-bin. the script needs to set the WEBMCP_PATH variable.
| author | Daniel Poelzleithner <poelzi@poelzi.org> |
|---|---|
| date | Sun Sep 19 01:36:08 2010 +0200 (2010-09-19) |
| parents | 9fdfb27f8e67 |
| children |
| rev | line source |
|---|---|
| jbe/bsw@0 | 1 --[[-- |
| jbe/bsw@0 | 2 param.update_relationship{ |
| jbe/bsw@0 | 3 param_name = param_name, -- name of request GET/POST request parameters containing primary keys for model B |
| jbe/bsw@0 | 4 id = id, -- value of the primary key for model A |
| jbe/bsw@0 | 5 connecting_model = connecting_model, -- model used for creating/deleting entries referencing both model A and B |
| jbe/bsw@0 | 6 own_reference = own_reference, -- field name for foreign key in the connecting model referencing model A |
| jbe/bsw@0 | 7 foreign_reference = foreign_reference -- field name for foreign key in the connecting model referencing model B |
| jbe/bsw@0 | 8 } |
| jbe/bsw@0 | 9 |
| jbe/bsw@0 | 10 This function updates a many-to-many relationship using a specified 'connecting_model' referencing both models. |
| jbe/bsw@0 | 11 |
| jbe/bsw@0 | 12 --]]-- |
| jbe/bsw@0 | 13 |
| jbe/bsw@0 | 14 function param.update_relationship(args) |
| jbe/bsw@0 | 15 local param_name = args.param_name |
| jbe/bsw@0 | 16 local id = args.id |
| jbe/bsw@0 | 17 local connecting_model = args.connecting_model |
| jbe/bsw@0 | 18 local own_reference = args.own_reference |
| jbe/bsw@0 | 19 local foreign_reference = args.foreign_reference |
| jbe/bsw@0 | 20 local selected_ids = param.get_list(param_name, atom.integer) -- TODO: support other types than integer too |
| jbe/bsw@0 | 21 local db = connecting_model:get_db_conn() |
| jbe/bsw@0 | 22 local table = connecting_model:get_qualified_table() |
| jbe/bsw@0 | 23 if #selected_ids == 0 then |
| jbe/bsw@0 | 24 db:query{ |
| jbe/bsw@0 | 25 'DELETE FROM ' .. table .. ' WHERE "' .. own_reference .. '" = ?', |
| jbe/bsw@0 | 26 args.id |
| jbe/bsw@0 | 27 } |
| jbe/bsw@0 | 28 else |
| jbe/bsw@0 | 29 local selected_ids_sql = { sep = ", " } |
| jbe/bsw@0 | 30 for idx, value in ipairs(selected_ids) do |
| jbe/bsw@0 | 31 selected_ids_sql[idx] = {"?::int8", value} |
| jbe/bsw@0 | 32 end |
| jbe/bsw@0 | 33 db:query{ |
| jbe/bsw@0 | 34 'DELETE FROM ' .. table .. |
| jbe/bsw@0 | 35 ' WHERE "' .. own_reference .. '" = ?' .. |
| jbe/bsw@0 | 36 ' AND NOT "' .. foreign_reference .. '" IN ($)', |
| jbe/bsw@0 | 37 args.id, |
| jbe/bsw@0 | 38 selected_ids_sql |
| jbe/bsw@0 | 39 } |
| jbe/bsw@0 | 40 -- TODO: use VALUES SQL command, instead of this dirty array trick |
| jbe/bsw@0 | 41 db:query{ |
| jbe/bsw@0 | 42 'INSERT INTO ' .. table .. |
| jbe/bsw@0 | 43 ' ("' .. own_reference .. '", "' .. foreign_reference .. '")' .. |
| jbe/bsw@0 | 44 ' SELECT ?, "subquery"."foreign" FROM (' .. |
| jbe/bsw@0 | 45 'SELECT (ARRAY[$])[i] AS "foreign"' .. |
| jbe/bsw@0 | 46 ' FROM generate_series(1, ?) AS "dummy"("i")' .. |
| jbe/bsw@0 | 47 ' EXCEPT SELECT "' .. foreign_reference .. '" AS "foreign"' .. |
| jbe/bsw@0 | 48 ' FROM ' .. table .. |
| jbe/bsw@0 | 49 ' WHERE "' .. own_reference .. '" = ?' .. |
| jbe/bsw@0 | 50 ') AS "subquery"', |
| jbe/bsw@0 | 51 args.id, |
| jbe/bsw@0 | 52 selected_ids_sql, |
| jbe/bsw@0 | 53 #selected_ids, |
| jbe/bsw@0 | 54 args.id |
| jbe/bsw@0 | 55 } |
| jbe/bsw@0 | 56 end |
| jbe/bsw@0 | 57 end |