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>