Discussion of article "SQLite: Native handling of SQL databases in MQL5" - page 9

New comment
 

Maxim Kuznetsov #:

SQLite is designed for one thread or one writer and many readers. Many writers are not about it, it's about other bases that are not "light".

It's not about MQL - it's just the way it is really and everywhere.

And that's another reason I'd like to see PostgreSQL work.

 
JRandomTrader #:

And that's another reason I'd like to see PostgreSQL work.

So do it, what's the problem ? Allow the DLL and use it.

I am not a PostgreSQL user, but I have used MySQL,MonetDB, SQL Relay and ODBC from mt5.

When there is a need, it is easier and faster to do it yourself than to wait for favours from nature

 
Maxim Kuznetsov #:

the query speed will drop and the memory requirements will increase....

but everything is OK :-)

SQLite is designed for one thread or one writer and many readers. Many writers are not about it, about it other bases that are not "light".

It's not about MQL - it's just the way it is really and everywhere.

Perhaps you do not know how to prepare SQLite, there are no bottlenecks in it, namely such as a single TCP server, which destroys all incoming streams, this server is in all other databases ...

SQLite is a gift, if it is properly prepared, the speed of write requests will be higher than Redis, and about reading itself, while when you read from the base threads do not block each other and everything happens as fast as possible resting in your system resources, you are also given the opportunity to deploy the database in RAM, it does not matter how, when creating, and you can through special software to create a disc in RAM and on it to place the database and create a symbolic link, if you use for example for advisors, which will provide the highest possible performance.

And perhaps you do not understand why cache sharing is necessary in SQLite..., it is necessary to save memory resources for input output, so that Expert Advisors and scripts consumed less memory in the end, and only because a separate thread is allocated for their work, a separate thread is not allocated for indicators.

When the shared cache mode is used, the connection with the base is opened for the whole process, not for separate threads, which saves resources, but as a consequence leads to similar errors that I described above and the shared cache slows down the work of the base.

The decision of MQL developers on the one hand is understandable to me, but on the other hand not very much, as I have a lot of experience with the platform and have not met cases that anyone ran more than 200 threads simultaneously, in 1 in the terminal limit of 100 charts, so you can attach 1 Expert Advisor and 1 script to each, but you can also run services on the number of services I think there is no limit, but it is at least not convenient and resource-intensive in any case, and if these threads opened their own connections to the database is a drop in the ocean of memory relatively then.

Such use of the terminal is at least inconvenient, and at most makes no sense, because a competent developer will make a multicurrency Expert Advisor as a single thread, and will not be engaged in such nonsense.

Dear developers, please remove the common cache or poke me in what I missed.

Took the gist about"SQLite Shared-Cache" out of the official documentation:

"This can significantly reduce the quantity of memory and IO required by the system."

Here's the link, read it.

 

In MetaEditor, these buttons (except Run) for working with the database don't work yet?


They are always locked.

 

This query returns nothing:

PRAGMA foreign_keys;

Attempting to include a FOREIGN KEY fails:

    string query = "PRAGMA foreign_keys=ON;";
    if(!DatabaseExecute(m_db_handle, query)) {
      Print(__FUNCTION__, " > Query failed with code: ", GetLastError());
    }

The documentation has this information: https: //www.sqlite.org/foreignkeys.html#fk_enable

Tip: If the command "PRAGMA foreign_keys" returns no data instead of a single row containing "0" or "1", then the version of SQLite you are using does not support foreign keys (either because it is older than 3.6.19 or

because it was compiled with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined).

SQLITE_OMIT_TRIGGER

Defining this option omits support for TRIGGER objects. Neither theCREATE TRIGGER or DROP TRIGGER commands are available in this case, and attempting to execute either will result in a parse error. This option also disables enforcement offoreign key constraints, since the code that implements triggers and which is omitted by this option is also used to implementforeign key actions.

SQLITE_OMIT_FOREIGN_KEY

If this option is defined, thenforeign key constraint syntax is not recognised.


There is nothing wrong with the version:

//---

How to enable FOREIGN KEY?

 
Anatoli Kazharski #:

This query does not return anything:

Attempting to include a FOREIGN KEY fails:

The documentation has this information: https: //www.sqlite.org/foreignkeys.html#fk_enable


There is nothing wrong with the version:

//---

How to enable FOREIGN KEY?

FOREIGN KEY is enabled by default, we do not use SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER.

PRAGMA foreign_keys=ON; should not be used.


.

To check the performance you can take the example https://www.sqlite.org/foreignkeys.html.

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
);

CREATE TABLE track(
  trackid     INTEGER, 
  trackname   TEXT, 
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

When working in the editor, everything works correctly without errors.

 
Renat Fatkhullin #:

FOREIGN KEY is enabled by default, we do not use SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER.

PRAGMA foreign_keys=ON; should not be used.


To check the performance you can take the example https://www.sqlite.org/foreignkeys.html.

When working in the editor, everything works correctly without errors.

Thanks for the answer!

For some reason I'm not getting the result I expect:

//---

Where is the error?

And this is what I would like to deal with too:

Forum on trading, automated trading systems and testing trading strategies

Discussion of the article "SQLite: native work with SQL databases in MQL5".

Anatoli Kazharski, 2022.10.05 01:54 AM

In MetaEditor these buttons (except Execute) for working with the database do not work yet?


They are always blocked.

 

Please ask someone to reproduce the example above.

The last row in the track table (highlighted) should not be added.

 

Tested the same schema in SQLiteStudio and everything works correctly.

This is what the warning should look like when trying to add a row with an ID that does not exist in the parent table:


 
Thanks, we'll check it out.
123456789101112
New comment