webmcp
annotate framework/env/param/update_relationship.lua @ 2:72860d232f32
Version 1.0.2
Fixed bug with explicit garbage collection (requests > 256kB caused an error)
Views prefixed with an underscore can't be called externally
ui.paginate now displays the last page, if the selected page number is too high.
Fixed bug with explicit garbage collection (requests > 256kB caused an error)
Views prefixed with an underscore can't be called externally
ui.paginate now displays the last page, if the selected page number is too high.
| author | jbe/bsw |
|---|---|
| date | Thu Dec 10 12:00:00 2009 +0100 (2009-12-10) |
| 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 |