Documentation on using the db methods

[vishakha]

Is there any documentation on how to use the db class methods
insert
update
delete

I’m running into issues with not being able to insert data while using this for a custom table,

$this->db = $GLOBALS[‘egw’]->db;
$this->db->insert(self::$table,array(
‘store_name’ => ‘testing1’),false,LINE,FILE);

Version
versions’ =>
array (size=5)
‘current_header’ => string ‘1.29’ (length=4)
‘maintenance_release’ => string ‘17.1.20180720’ (length=13)
‘api’ => string ‘17.1.003’ (length=8)
‘phpgwapi’ => string ‘17.1.003’ (length=8)
‘header’ => string ‘1.29’ (length=4

Maria db is ued
lighttpd server is used
PHP version is 7.2
installation type : archive instalation

[RalfBecker]

The documentation of each method is in the sources, either direct at the method or the header, e.g.

EGroupware/egroupware/blob/master/api/src/Db.php#L30

You IDE should show you available methods and parameters.

Ralf

[vishakha]

I followed this documentation

/

used the function like this and it doesn’t seem to save the data

$this->db = $GLOBALS[‘egw’]->db;
$this->db->insert(self::$table,array(
‘store_name’ => ‘testing1’),false,LINE,FILE);

[RalfBecker]

insert method requires the schema to be known to EGroupware / db-class AND $app to be the application registering the schema.

Have a look at our example app and video on how to create and register a database schema:

EGroupware/example

Ralf

[vishakha]

okay thanks,
is it a good practice to directly use the “query” method to insert/update/delete data instead of the insert, update and delete methods

[RalfBecker]

is it a good practice to directly use the “query” method to insert/update/delete data instead of the insert, update and delete methods

No, it’s NOT!

Good practice is to have a schema and let the db-class deal with the quoting.

Or use PDO with prepared statements:

use EGroupware\Api;

$pdo = Api\Db\Pdo::connection();

$stm = $pdo->prepare("SELECT * FROM my_table WHERE id = :id");
$stm->execute(['id' => 123]);

See the PHP documentation about PDO for available methods, but use the code as I showed to get the connection:
https://www.php.net/manual/en/class.pdo.php

Ralf

[vishakha]

I’m keen to use the insert/update/delete/select methods of the db class as opposed to directly using the query method, so I’ll register the schema as you mentioned above in the example.

Thanks

[vishakha]

Following the example app, I tried to to do same thing with my existing app, it’s already installed and setup for the run rights .
I’m doing development for this app. but it doesn’t seem to save with insert method.

[vishakha]

My version is 17.1

I checked my application already has run rights defined as shown in example app.
I used this with my app name

$this->db = $GLOBALS[‘egw’]->db;
$app = 'my-app-name';
$this->db->insert(self::$table, array(
   ‘store_name’ => ‘test’
), false, __LINE__, __FILE__, $app);

When I check in the db, the record is added, I can see the primary key updated but with no value for this column (store_name). After debugging through the db class and checking the insert and query method, I found that it returns nothing for the query string with blank field name and value, not sure what’s wrong

Please help, my development is stuck until I resolve this as I’m manipulating data in db with those db class methods( insert/update/delete/select)

[vishakha]

I manually created the custom database tables for my application.
Could this be the reason why the following function implodes an empty string of keys and values for the query string(field-value pairs) to insert data

function column_data_implode($glue,$array,$use_key=True,$only=False,$column_definitions=False)

Please help

[RalfBecker]

I manually created the custom database tables for my application.

Yes, without schema know to EGroupware, you can not use insert, select, update or delete methods, only query will work!

Creating the schema in EGroupware is really easy, and allow to run schema upgrades.

Or use the PHP PDO, as I suggested before, it uses the schema information from the database directly.

Ralf

[vishakha]

Can you guide me towards the documentation for creating schema and how to to allow schema upgrades.
I have a bunch of custom tables for my application

I followed the following code for egroupware filemanager app and put my custom table schema in the following file of my app

tables_current_inc.php

$phpgw_baseline = array(
	'egw_collab_member' => array(
		'fd' => array(
			'collab_member_id' => array('type' => 'auto','nullable' => False, 'comment' => 'Unique per user and session'),
			'collab_es_id' => array('type' => 'varchar','precision' => '64','nullable' => False, 'comment' => 'Related editing session id'),
			'collab_uid' => array('type' => 'varchar','precision' => '64'),
			'collab_color' => array('type' => 'varchar','precision' => '32'),
			'collab_is_active' => array('type' => 'int','precision' => '2', 'default'=>'0','nullable' => False),
			'collab_is_guest' => array('type' => 'int','precision' => '2','default' => '0','nullable' => False),
			'collab_token' => array('type' => 'varchar','precision' => '32'),
			'collab_status' => array('type' => 'int','precision' => '2','default' => '1','nullable' => False)
		),
		'pk' => array('collab_member_id'),
		'fk' => array(),
		'ix' => array(),
		'uc' => array()
	),

what is the following table used for

tables_update.inc.php

[RalfBecker]

Please follow the tutorial: https://github.com/EGroupware/example/tree/step3

The schema is in file $app/setup/tables_current.inc.php.

If you need a schema update, create it using the DBTools from old eTemplate app, which will create or enhance $app/setup/tables_updates.inc.php. Have a look for these files in existing apps. If you know the system well, they can be written manually, thought I create 99% of my updates with DBTools and I’m the one who wrote most of that parts of EGroupware.

Ralf