function

delete . database . sncode

VERSIONlatest Delete in the databases.

DELETE

NAME

delete - Delete in the databases.

SYNOPSIS

delete( [db:pointer,] tables:string, wantarray:true|false, count:integer, using:string,error:variable,delete_upload:true|false,delete_foreign:true|false, join:string, having:string, selectby:string, orderby:string, groupby:string, limit:string, args:string, uid:integer, page:integer, prefix:true|false, countperpage:integer);

DESCRIPTION

This function is used to delete elements in tables.

Since the database does not support page, limit, groupby having, join. A select is perform to get the list of uid and than a delete is perform on this list. As a consequence, the field uid must be present in the table.

Also, if delete_upload or delete_foreign is set to true (default), than a select is perform and the delete is done after.

In order to delete foreign element in table the following is perform:

If we want to delete uid 5 of table sn_test1:

  • Get the UID from sn_tables for sn_test1 =⇒ uid_table
  • If delete_foreign is true than
    • For each foreign_table and foreign field from table sn_fields where field_type = foreign and uid_sn_tables = uid_table
      • For each element in foreign_table where foreign_field = 5;
        • Recusively delete uid foreign_table, element
  • If delete_upload is true than
    • Select field_name from sn_fields where field_type = image and uid_sn_tables = uid_table
    • Select * from table sn_test1 where uid = 5;
    • Delete file if any

PARAMETERS

tables
Specify list of tables commads separated.
delete_upload
Delete uploaded file.
delete_foreign
Delete foreign elements.
using
Specify using keyword.
error
Put errors in this variable. This is useful in a loop to check if an error occur after the loop in the SQL statement.
wantarray
By default rows are return as a context with the name of the column. If this parameter is true, it return only the value.
count
If parameter page is used, then the function must calculate the number of element. If you already have this number, you can use this parameter to initialize it.
fields
Specify the list of fields commas separated.
join
Specify join statement for select.
having
Specify having statement for select.
selectby
Specify condition for the WHERE statement. See args \? is replase with ?.
noargs
By default ? in selectby are replace by values in args. If noargs is true, no replacement is done.
args
Specify arguments wihin selecby to quote and replace value. All ? are replace with one arguments.
orderby
Specify ORDER BY statement.
groupby
Specify GROUP BY statement.
limit
Specify LIMIT statement.
uid
Specify UID for the WHERE statement.
page
Specify page number based on count and countperpage.
prefix
Prefix name of field with table name followed by _.
countperpage
Number of elements in a page.
fields
List of fields with new values.
db
Specify alternate database pointer as return from connect function
Starting from version 5.6.2 if db is undefined, it will fall back to the default database of the project.

RETURN

This function return an has array with thw following elements:

sql
Build SQL statement.
nbcols
Number of columns in result.
offset
Offset calculated when page is specify. If not, the value is -1.
sqlerr
Error message if any.
count
Calculated count (or the one specify in parameter) if page is specify. -1 otherwise.
error
Specify if we got an error or not
sqlcode
SQL error code. 0 if no error.
nbrows
Number of rows in result
rows
List if rows. The rows can be an array of hash or an array of array if wantarray is true.

EXAMPLES

Note: In the followings examples, the _ between the { should be removed to make it work.

res={_{
                a=sql("drop table IF EXISTS ? ;","sn_test1");
                a=sql("drop table IF EXISTS ? ;","sn_test2");
                a=sql("create table sn_test1 (uid int, firstname varchar(255), lastname varchar(255");
                a=sql("create table sn_test2 (uid int, uid_sn_test_1 int, firstname varchar(255), lastname varchar(255))");
                for(i=1;i<=5;++i)
                        a=sql("insert into sn_test1 values('?', '?', '?');",i, "f" .+ i,"l" .+ i );
                        for(j=1;j<=5;++j)
                                a=sql("insert into sn_test2 values('?', '?', '?', '?');",i,j,"g" .+ i .+ j,"h" .+ i .+ j);
                        endfor
                endfor
                }}. return res=.

res={_{ update(); }}. return .*tables must be specify.*
res={_{ update(tables:"sn_test1"); }}. return .*fields must be specify.*
res={_{ update(tables:"sn_test1",fields:"aaaa",page:5,limit:"abc"); }}. return .*Parameter fields is not a context.*

res={_{
                        a=select(tables:"sn_test1",uid:1); a.rows;
                        a=update(tables:"sn_test1",uid:1,fields:{firstname:"laplante"}); a.error;
                        a=select(tables:"sn_test1",uid:1); a.rows;
        }}. return res=\[{"uid":"1","lastname":"l1","firstname":"f1"}\]false\[{"uid":"1","lastname":"l1","firstname":"laplante"}\].

res={_{
                        a=select(tables:"sn_test1"); a.rows;
                        a=update(tables:"sn_test1",page:1,countperpage:2,fields:{firstname:"laplante"}); a.error;
                        a=select(tables:"sn_test1"); a.rows;
        }}. return res=\[{"uid":"1","lastname":"l1","firstname":"laplante"},{"uid":"2","lastname":"l2","firstname":"f2"},{"uid":"3","lastname":"l3","firstname":"f3"},{"uid":"4","lastname":"l4","firstname":"f4"},{"uid":"5","lastname":"l5","firstname":"f5"}\]false\[{"uid":"1","lastname":"l1","firstname":"laplante"},{"uid":"2","lastname":"l2","firstname":"laplante"},{"uid":"3","lastname":"l3","firstname":"f3"},{"uid":"4","lastname":"l4","firstname":"f4"},{"uid":"5","lastname":"l5","firstname":"f5"}\].

res={_{
                        a=update(tables:"sn_test1",uid:1,fields:{frstname:"laplante"}); a.error;
                        a.sqlerr;
        }}.return res=trueUnknown column 'frstname' in 'field list'.
    

AUTHOR

Written by Pierre Laplante, <laplante@sednove.com>