DB logger sample by matejk · Pull Request #4759 · pocoproject/poco

matejk · 2024-11-06T16:51:06Z

Resolves #4750

Data/samples/DBLogger/src/DBLogger.cpp

aleks-f

Functionality ok, but it should be tidied up a bit, there's too much functionality crammed into a single class. Something along these lines:

poco/Util/samples/SampleServer/src/SampleServer.cpp

Lines 108 to 112 in c8dade8

    
           TaskManager tm; 
        
           tm.start(new SampleTask); 
        
           waitForTerminationRequest(); 
        
           tm.cancelAll(); 
        
           tm.joinAll();

Data/samples/DBLogger/src/DBLogger.cpp

matejk · 2024-11-09T19:39:35Z

Functionality ok, but it should be tidied up a bit, there's too much functionality crammed into a single class. Something along these lines:

poco/Util/samples/SampleServer/src/SampleServer.cpp

Lines 108 to 112 in c8dade8

TaskManager tm;

tm.start(new SampleTask);

waitForTerminationRequest();

tm.cancelAll();

tm.joinAll();

Extracted scanning and inserting functionality to a separate class SQLLogInserter.

Is that what you had in mind by tidying up?

Data/samples/DBLogger/src/DBLogger.cpp

Data/samples/DBLogger/src/SQLLogInserter.h

aleks-f

Need to have DBLogger.properties file, with all the commend option settings and logger settings, something like this:

logging.loggers.root.channel = appSplitter
logging.loggers.root.level = information

logging.channels.appFile.class = FileChannel
logging.channels.appFile.pattern = %L%Y-%m-%d %H:%M:%S.%i [%p] %s<%I>: %t
logging.channels.appFile.path = ${application.baseName}.log
logging.channels.appFile.times = local
logging.channels.appFile.rotation = daily
logging.channels.appFile.archive = number
logging.channels.appFile.purgeCount = 10

logging.channels.sql.class = SQLChannel
logging.channels.sql.name = DBLogger
logging.channels.sql.connector = SQLite
logging.channels.sql.connect = DBLogger.db3
logging.channels.sql.timeout = 3000
logging.channels.sql.minBatch = 5
logging.channels.sql.maxBatch = 1000
logging.channels.sql.bulk = true
logging.channels.sql.table = T_LOG
logging.channels.sql.async = false
logging.channels.sql.throw = false
logging.channels.sql.file = ${application.baseName}.sql

logging.channels.console.class = ColorConsoleChannel
logging.channels.console.pattern = %L%Y-%m-%d %H:%M:%S.%i [%p] %s<%I>: %t

logging.channels.appSplitter.class = SplitterChannel
logging.channels.appSplitter.channels = console, appFile, sql

aleks-f

Sample should work out of the box, but it doesn't:

$ bin/Linux/aarch64/DBLogger
Directory: 
Database: , 
Number of workers: 2
Not found
Created 0 messages, processed 0 messages in 0 ms.

There should be a reasonable initial default setup so that workers can keep up with sql file generation; as it is now, the filesystem gets clogged with sql files.
logging configuration, as mentioned here
SQL statements in the files should insert more than a single row; should be achievable with minBatch property

)

…rks with SQLite) (#4750)

…_iterator and std::thread.

…to class SQLLogInserter.

…ges option.

matejk · 2024-11-14T11:14:59Z

* [ ]  SQL statements in the files should insert more than a single row; should be achievable with `minBatch` property

@aleks-f :

Is it a matter of SQL channel configuration to insert multiple SQL statements into the same file or shall DB logger have its own configuration to merge multiple sql files together?

aleks-f · 2024-11-15T04:03:05Z

* [ ]  SQL statements in the files should insert more than a single row; should be achievable with `minBatch` property
@aleks-f :

Is it a matter of SQL channel configuration to insert multiple SQL statements into the same file or shall DB logger have its own configuration to merge multiple sql files together?

Number of rows inserted in one statement should be controllable with minBatch, maxBatch and maxSQL property, when >1 the channel should concatenate multiple rows so they are inserted in one execution:

poco/Data/src/SQLChannel.cpp

Lines 56 to 58 in cb91880

    
           const std::string SQLChannel::SQL_INSERT_STMT = "INSERT INTO %s " \ 
        
           	"(Source, Name, ProcessId, Thread, ThreadId, Priority, Text, DateTime)" \ 
        
           	" VALUES %s";

The maxSQL property should be set not to exceed the max SQL statement length allowed by back end:

poco/Data/src/SQLChannel.cpp

Lines 400 to 408 in cb91880

    
           if (++batch == _maxBatch || (os.str().length() + tmp.str().length()) >= _maxSQL) 
        
           { 
        
           	os << ";\n"; 
        
           	doLog(); 
        
           	os.str(""); sql.clear(); 
        
           	Poco::format(sql, SQL_INSERT_STMT, _table, std::string()); 
        
           	os << sql << '\n' << tmp.str(); 
        
           	batch = 0; 
        
           }

See eg. Batch size for SQL Server

matejk · 2024-11-15T08:49:12Z

* [ ]  SQL statements in the files should insert more than a single row; should be achievable with `minBatch` property
@aleks-f :
Is it a matter of SQL channel configuration to insert multiple SQL statements into the same file or shall DB logger have its own configuration to merge multiple sql files together?
Number of rows inserted in one statement should be controllable with minBatch, maxBatch and maxSQL property, when >1 the channel should concatenate multiple rows so they are inserted in one execution:

Thanks, the latest code uses SQLLogger demo channel configuration from DBLogger.properties where all of its properties can be tuned.

matejk added this to the Release 1.14.0 milestone Nov 6, 2024

matejk requested a review from aleks-f November 6, 2024 16:51

aleks-f requested changes Nov 7, 2024

View reviewed changes

aleks-f marked this pull request as draft November 8, 2024 06:50

matejk requested a review from aleks-f November 8, 2024 18:12

aleks-f requested changes Nov 9, 2024

View reviewed changes

Data/samples/DBLogger/src/DBLogger.cpp Outdated Show resolved Hide resolved

matejk requested a review from aleks-f November 9, 2024 19:40

matejk force-pushed the 4750-db-logger-sample branch from 60ac4fc to 67c39e9 Compare November 10, 2024 11:15

aleks-f reviewed Nov 11, 2024

View reviewed changes

Data/samples/DBLogger/src/DBLogger.cpp Outdated Show resolved Hide resolved

Data/samples/DBLogger/src/SQLLogInserter.h Outdated Show resolved Hide resolved

aleks-f assigned matejk Nov 11, 2024

matejk requested a review from aleks-f November 11, 2024 19:45

matejk marked this pull request as ready for review November 11, 2024 22:16

aleks-f requested changes Nov 11, 2024

View reviewed changes

matejk requested a review from aleks-f November 12, 2024 15:42

aleks-f requested changes Nov 13, 2024

View reviewed changes

matejk and others added 14 commits November 13, 2024 19:34

sample(DBLogger): New sample to demonstrate DB logging to a file. (#4750

6b01528

)

sample(DBLogger): Create messages via SQL logger in a thread (#4750)

e4d795d

sample(DBLogger): Save messages from SQL files to an SQL database (wo…

93230d6

…rks with SQLite) (#4750)

chore(Makefile): Data samples depend on PocoUtil (#4750)

2f7e38f

sample(DBLogger): Refactored DBLogger with std::filesystem::directory…

3a34c67

…_iterator and std::thread.

sample(DBLogger): Add missing include <condition_variable>

75faa3c

sample(DBLogger): Extracted log scanning and inserting functionality …

57413e9

…to class SQLLogInserter.

sample(DBLogger): Create new logging table only when using demo messa…

17ca42a

…ges option.

feat(DBLogger): VS projects

9159327

sample(DBLogger): Acquire options from configuration file.

a7744fe

feat(DBLogger): regenerate VS projects (progen file change)

be5272c

sample(DBLogger): Add example DBLogger.properties file.

577c0a6

sample(DBLogger): Process as much as possible when stopping processing.

8cef3d9

sample(DBLogger): Meaningful defaults in properties file.

99e0ea9

sample(DBLogger): Verify validity of database session on startup.

94ebcfa

matejk force-pushed the 4750-db-logger-sample branch from e627a51 to 94ebcfa Compare November 13, 2024 20:02

sample(DBLogger): Configure demo SQL channel in properties file.

04b3db1

matejk requested a review from aleks-f November 15, 2024 08:50

chore(DBLogger): style and warnings

d029667

aleks-f approved these changes Nov 18, 2024

View reviewed changes

matejk merged commit 52959b9 into main Nov 18, 2024

matejk deleted the 4750-db-logger-sample branch November 19, 2024 11:29

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

DB logger sample#4759

DB logger sample#4759
matejk merged 17 commits intomainfrom
4750-db-logger-sample

matejk commented Nov 6, 2024

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

aleks-f left a comment

Uh oh!

Uh oh!

matejk commented Nov 9, 2024 •

edited

Loading

Uh oh!

Uh oh!

Uh oh!

aleks-f left a comment

Uh oh!

aleks-f left a comment •

edited by matejk

Loading

Uh oh!

matejk commented Nov 14, 2024

Uh oh!

aleks-f commented Nov 15, 2024 •

edited

Loading

Uh oh!

matejk commented Nov 15, 2024

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

	TaskManager tm;
	tm.start(new SampleTask);
	waitForTerminationRequest();
	tm.cancelAll();
	tm.joinAll();

Conversation

matejk commented Nov 6, 2024

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

aleks-f left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

matejk commented Nov 9, 2024 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Uh oh!

Uh oh!

aleks-f left a comment

Choose a reason for hiding this comment

Uh oh!

aleks-f left a comment • edited by matejk Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

matejk commented Nov 14, 2024

Uh oh!

aleks-f commented Nov 15, 2024 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

matejk commented Nov 15, 2024

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

matejk commented Nov 9, 2024 •

edited

Loading

aleks-f left a comment •

edited by matejk

Loading

aleks-f commented Nov 15, 2024 •

edited

Loading