1. Vladimir Esipov
  2. Valentina Database ADK
  3. Понедельник, Июль 22 2024, 09:55 AM
  4.  Подписаться через email
Hello, VDB team, as well as everyone involved and passing by!

I'm trying to solve the problem of intercepting records opened by clients for editing.

I am creating a procedure that should be called by a trigger when the database is opened and which will create a RAM table named REC_LOCK.
The table will contain the OIDs of records opened by clients for editing.
When a client saves a record, its reference is removed from the REC_LOCK table.
-- OID of the locked entry.
-- REC_UTS - timestamp.
-- REC_USR - who edits.
Also, I set the IO encoding to windows-1251

CREATE PROCEDURE "SET_ENV"()
BEGIN
SET @TXTSQL = 'CREATE TEMPORARY RAM TABLE IF NOT EXISTS REC_LOCK(
OID_REC ULLONG NOT NULL,
REC_UTS ULONG DEFAULT METHOD( ''unix_timestamp(now())'' ) NOT NULL,
REC_USR STRING ( 3 ) DEFAULT METHOD( ''current_user()'' ) NOT NULL );

SET PROPERTY IOEncoding OF DATABASE TO ''windows-1251'';';
EXECUTE @TXTSQL;
END

Is it possible to call a procedure that performs DDL statment from a trigger for opening a database?

CREATE TRIGGER START_DB AFTER STARTUP ON DATABASE
BEGIN
CALL "SET_ENV"();
END

=> Kernel Error: 0x91505. Trigger "START_DB": action "DB_StartUp " is not supported in this context.
The same for the AFTER LOGON ON DATABASE event.

What do you advise?

Perhaps the CREATE TRIGGER on DB
page should provide more detailed information, with examples of practical use.
I only managed to write something to the log, for example:

PRINT 'Ося и Киса были здесь!'

By the way, the example given on the CREATE TABLE page also not working

-- Starting with v5 you can also define TEMPORARY RAM fields.
-- This can be an interesting feature in combination with another new feature v5 - triggers on database event

CREATE TRIGGER trig_after_open_CreatePersonHas
AFTER STARTUP ON DATABASE
BEGIN
ALTER TABLE T ADD COLUMN fldMyHash FixedBinary(16) TEMPORARY RAM;
END

Firstly, the parser does not understand the RAM attribute for the field:
=> Kernel Error: 0x71000. line 4:66: expecting ';', found 'RAM' , although on page erformance:ram_objects" target="_blank" rel="nofollow">RAM Temporary Field this is presented as a unique opportunity for VDB.
But in fact, it is impossible add a RAM-field to a DISK-table, only temporary field, in «.tmp» volume
Secondly, even if we remove it, we will still get the same result:
=> Kernel Error: 0x91505. Trigger "trig_after_open_CreatePersonHas": action "DB_StartUp " is not supported in this context.

It may be worth correcting the documentation or making the stated capabilities work.
Комментарий
There are no comments made yet.
Ivan Smahin Ответ принят
Hi Vladimir,

=> Kernel Error: 0x91505. Trigger "START_DB": action "DB_StartUp " is not supported in this context.
The same for the AFTER LOGON ON DATABASE event.


I cannot reproduce it. Please give me the exact steps you do to get this error.
Комментарий
There are no comments made yet.
Ivan Smahin Ответ принят
Is it possible to call a procedure that performs DDL statment from a trigger for opening a database?


Sure, no problem, but you create some tmp table there...

Look, the trigger will be fired on each db.Open() call. So, each time it will be an attempt to create that table.
"IF NOT EXISTS" helps to avoid errors if the table exists, it's ok if you want to create it once, but TMP or/and RAM table dies after db.close().
So you will create a new table on each db.open().
Комментарий
There are no comments made yet.
Vladimir Esipov Ответ принят
Hi Ivan,
I can create a procedure SET_ENV() in the DB, but I cannot create the above trigger in this DB
(open on the server or locally).
I have provided the trigger creation code and error code above.

CREATE TRIGGER START_DB AFTER STARTUP ON DATABASE
BEGIN
CALL "SET_ENV"();
END

I'm on WIN11, VALENTINA 14.1.2
-------------------------------------------------------------
If I understand correctly:

1. The server starts.
2. After the start, the database opens (automatically). There are no connected clients yet.
3. At the moment of opening the database, a trigger fires (once), which calls a procedure that creates a RAM table (once). The table exists as long as the server is running.
4. The client connects. At this point, the database was already open and the table had already been created.
(Or do I misunderstand and when a client connects, the database is opened again, as many times as the clients will connect?)
5. Then, when opening and editing any record in any table by any client, in the table REC_LOСK a new record is added indicating the OID of the record being edited (from the client program or through the “before update” trigger).
6. If another client wants to edit the same record, some code is triggered (a message is displayed or the editing form is prohibited from opening...)
7. After a commit or cancellation, an entry from the REC_LOCK table is removed (also, from the program or at the database level by the “after update” trigger - I haven’t decided which is better yet).
Комментарий
There are no comments made yet.
Vladimir Esipov Ответ принят
I cannot reproduce it. Please give me the exact steps you do to get this error.


By the way, the above example with the trigger from the WIKI, which appeared back in version 5,
I could not get to work in any of the versions. Didn't want to bother you earlier.

Думал, авось само рассосётся :p
Комментарий
There are no comments made yet.
Vladimir Esipov Ответ принят
HI Ivan
If I run the SQL script below and create the schema from scratch, the triggers I need are created,
but they do not work either on AFTER STARTUP or AFTER LOGON.

-- Valentina 14 SQL dump --
----------------------------------------------------
;

-- Create database
-- Note - the database creation will be skipped in case of database exists
-- But SET PROPERTY will be applied anyway
----------------------------------------------------
;

CREATE DATABASE IF NOT EXISTS "CRLDB" WITH
SET PROPERTY
"Mode",
"IdentsCaseSensitive",
"SegmentSize",
"SchemaVersion",
"DateTimeFormat",
"DateSeparator",
"TimeSeparator",
"KeyDelimiter",
"LocaleName",
"StorageEncoding",
"FrenchCollation",
"AlternateHandling",
"CaseFirst",
"CaseLevel",
"NormalizationMode",
"Strength",
"HiraganaQuaternaryMode",
"NumericCollation"
of DATABASE TO
'kDsc_DatBlb_Ind',
false,
32768,
1,
'kYMD',
'-',
':',
'.',
'ru_RU',
'ibm-5347_P100-1998',
'kOff',
'kNonIgnorable',
'kOff',
'kOff',
'kOff',
'kPrimary',
'kOff',
'kOn';


SET PROPERTY "Comment" of DATABASE TO 'Логи CORALL';
;

USE "CRLDB";

-- Create Types
CREATE TYPE eTGR AS ENUM8( '---', '88', '7090', '7092', '7093', '7094', '7097', 'DS021', 'DS022', 'DS023', 'INT', 'ОСМП');

-- Dump for 'JRN' table
----------------------------------------------------
;

CREATE TABLE "JRN" (
"DTM" ULONG NOT NULL,
"ELP" USHORT NOT NULL DEFAULT '0',
"TGR" eTGR NOT NULL DEFAULT '---',
"TSL" USHORT NOT NULL DEFAULT '0',
"PH1" USHORT NOT NULL DEFAULT '0',
"DST" BYTE NOT NULL DEFAULT '0',
"TYP" BYTE NOT NULL DEFAULT '0',
"PH2" LLONG NOT NULL DEFAULT '0',
"RDR" STRING (1) NOT NULL DEFAULT '-',
"FLD" STRING (1) NOT NULL DEFAULT '-',
"PRF" STRING (1) NOT NULL DEFAULT '-',
"ANS" BOOLEAN NOT NULL DEFAULT '0');

-- Table properties --
----------------------------------------------------
;
SET PROPERTY
"LocaleName",
"CollationFrenchCollation",
"CollationAlternateHandling",
"CollationCaseFirst",
"CollationCaseLevel",
"CollationNormalizationMode",
"CollationStrength",
"CollationHiraganaQuaternaryMode",
"CollationNumericCollation"
of TABLE "JRN" TO
'ru_RU',
'kOff',
'kNonIgnorable',
'kOff',
'kOff',
'kOff',
'kPrimary',
'kOff',
'kOn';

SET PROPERTY "comment" of TABLE "JRN" TO 'Соединения за 2023 год';
SET PROPERTY "comment" of FIELD "JRN"."DTM" TO 'Дата вызова, в секундах';
SET PROPERTY "comment" of FIELD "JRN"."ELP" TO 'Длительность, в секундах';
SET PROPERTY "comment" of FIELD "JRN"."TSL" TO 'СЛ, по которой был сделан вызов';
SET PROPERTY "comment" of FIELD "JRN"."PH1" TO 'Внутренний номер (100-499, 600-699)';
SET PROPERTY "comment" of FIELD "JRN"."DST" TO '0 исходящий, 1 входящий, 2 принимающий';
SET PROPERTY "comment" of FIELD "JRN"."TYP" TO '0 ЛОК, 1 ГОР, 2 МГ, 3 МН, 4 ПРМ';
SET PROPERTY "comment" of FIELD "JRN"."PH2" TO 'Телефон абонента 2';
SET PROPERTY "comment" of FIELD "JRN"."RDR" TO '- A Y Переадресация';
SET PROPERTY "comment" of FIELD "JRN"."FLD" TO '- N U NULL';
SET PROPERTY "comment" of FIELD "JRN"."PRF" TO '- N E из потока NULL';
SET PROPERTY "comment" of FIELD "JRN"."ANS" TO 'Неотвеченный вызов';

-- Constraints and Indexes --
----------------------------------------------------
;

-- Dump for 'SET_ENV' stored procedure
----------------------------------------------------
;

CREATE PROCEDURE "SET_ENV"()
BEGIN
SET @TXTSQL = 'CREATE TEMPORARY RAM TABLE IF NOT EXISTS REC_LOCK(
OID_REC ULLONG NOT NULL,
REC_UTS ULONG DEFAULT METHOD( ''unix_timestamp(now())'' ) NOT NULL,
REC_USR STRING ( 3 ) DEFAULT METHOD( ''current_user()'' ) NOT NULL );

SET PROPERTY IOEncoding OF DATABASE TO ''windows-1251'';';
EXECUTE @TXTSQL;
END;

-- Dump for 'DB_START' trigger
----------------------------------------------------
;

CREATE TRIGGER DB_START AFTER STARTUP ON DATABASE
BEGIN
CALL SET_ENV();
END;

-- Dump for 'DB_LOGON' trigger
----------------------------------------------------
;

CREATE TRIGGER DB_LOGON AFTER LOGON ON DATABASE
BEGIN
ALTER TABLE JRN ADD COLUMN LOCK_REC BOOLEAN DEFAULT '0' NOT NULL TEMPORARY;
END;

It was not possible to create them from the Studio SQL-editor in an existing database.
I tried to set VERBOSELEVEL=3, but I also couldn’t find any traces of triggers fired in the logs.
Комментарий
There are no comments made yet.
Ivan Smahin Ответ принят
Hi Vladimir,

but they do not work either on AFTER STARTUP or AFTER LOGON.

It is fixed already in v.14.1.3 (will be available nearest days)
Комментарий
There are no comments made yet.
Vladimir Esipov Ответ принят
Thank you, great news..
Ivan, while you are here, I have long wanted to ask:
VDB fundamentally does not support temporary indexes?
It’s just that for every combination of fields in the ORDER BY, VDB forcibly creates indexes that are needed occasionally...
Of course, in the program I try to take as much data as possible from the server and perform sorting in the grid locally, but this is not always possible.
Question:
Is it possible to teach VDB to use only indexes that are explicitly specified at the time of design, and create the rest as temporary ones, or do without them altogether?
I remember in early versions of VDB, they were forced to create indexes on both INT16 and INT32 fields, but then you tweaked something in the engine, and when ORDER BY for these types, VDB somehow manages without indexes (except for BYTE fields and INT64).
Комментарий
There are no comments made yet.
Ivan Smahin Ответ принят
As for me, it's a good point, but we need to discuss it here first. Would you mind registering it as a feature request in the Mantis?
Комментарий
There are no comments made yet.
Vladimir Esipov Ответ принят
Got it, I'll do it with pleasure..
But only tomorrow, now I have to leave.
Well done, guys!:D
Комментарий
There are no comments made yet.
  • Страница :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories