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>