Better way to maintain a list in a database table??

[Ian Branch 128]

Hi Team,

I need to be able to create and add to a list in a database table.

ATT I am using a Memo field and simply adding lines, all text, to it.

Is this the best/most efficient way to do it?

 

Regards & TIA,

Ian

Edited January 23, 2023 by Ian Branch

[aehimself 399]

Depends on your application. I'd break the list up to elements (and store them in their separate table) because of two reasons:

- You can use simple VarChar fields instead of MEMO / CLOB, which is more easy on resources

- You can filter the list with your select if needed

 

Edit: by having their own table you can even extend each list element with further properties later on

Edited January 23, 2023 by aehimself

[microtronx 38]

If your entries can be saved within a varchar field, I would use a table with at least these fields:

id - autoinc

type - varchar

name - varchar

 

With such a table, you can filter by type ... use this list/lookup in different areas and add / delete entries as needed. Adding i.e. a field "sorted" (integer) would makes it possible to order them in your list as needed.

[Ian Branch 128]

Hi Guys,

Perhaps my describing the information as a list is missleading..

Basically it is a log of events, each entry looking roughly like..

"This has been done with that at this datetime"

Each time the event happens a new entry is added, notionally the same with the datetime being updated.

 

Ian

[FPiette 385]

11 minutes ago, Ian Branch said:

Basically it is a log of events, each entry looking roughly like..

"This has been done with that at this datetime"

Each time the event happens a new entry is added, notionally the same with the datetime being updated.

Depending on what you need to do with the log (For example searching), you may simply use a text file and append the line at the end of the file. That is how most log files are built.

 

If you are running on Windows, you may also use the Windows Event Log feature for which there is an API.

 

And of course using a database is also an option.

 

How to select between those solution and probably other depends on your use case that you don't describe at all...

[Lars Fosdal 1793]

Local text logs are very handy and low cost. 

Just to give you some ideas...

Since I have servers in many places, I ended up with a scheme that named the logfile something like this

AppName.MachineName.LogContext.yyyymmdd-day-hhmm.pid-nnnn.log where the timestamp is when the logfile was created.

That gives me a unique log name, across apps, servers and instances, and I can filter on the timestamp part of the name when I want to delete logs that is older than my limit.

 

In memory, I write log entries to a circular buffer, and I have a background thread that "burst" write the log every second to avoid having the logging thread lagging on file operations.

The logfile contains a "header" for each line which is applied when the log entry is added to the buffer.

line number {hh:mm:ss,nnn (threadid) memoryload} log text

Quote

   9929450 {11:22:04,217 (8844) 297907k} HTTP POST redacted/tik/v1/palletstatus {"tpackNo":"1412416726","sscc":"370380114124167260","reasonCode":"P27","lot":[{"articleNo":"6977","lotNo":"1412235400","lockState":"lsFree","expireDate":"2023-04-19T00:00:00.000Z"}]}

Also, ensure that you don't write secrets to the log file.

 

This particular logfile grows to about 250MB per day and rolls over to a new file at midnight.

So, make sure that you have a mechanism that ensure your logs don't grow to infinity and that you delete the old ones, or your storage will suddenly be depleted.

 

Database logs are great if accessing the machine where your process runs is cumbersome security-wise.

But:  If the database is unreachable, you have a problem - also, writing to a database is far more costly than writing to a local file.

[aehimself 399]

1 hour ago, Ian Branch said:

Basically it is a log of events, each entry looking roughly like..

If it's a log, definitely put them in a separate table, one log entry being one record, and store the timestamp in a separate field. Maybe even add a log category field, like "backgroundworker", "httpserver", "cache".

On the long run, being able to quickly search log entries between a specified timeframe is a lifesaver!

[FPiette 385]

1 hour ago, FPiette said:

you may simply use a text file and append the line at the end of the file

I forgot to say there is a standard for log file: SysLog. And there are provision to send the lines (Events) to a SysLog server using the Syslog protocol (RFC5424). I even wrote a pair of ICS components for that purpose: TSysLogServer and TSysLogClient. Those component allows to have a centralized, remote management system for the log. There are two corresponding demos in ICS. Look at the source code because documentation is almost non existent. Ask for your question in https://en.delphipraxis.net/forum/37-ics-internet-component-suite/

[Fr0sT.Brutal 900]

2 hours ago, Lars Fosdal said:

The logfile contains a "header" for each line which is applied when the log entry is added to the buffer.

line number {hh:mm:ss,nnn (threadid) memoryload} log text

So you have problems if you try to feed these logs to some generic log-reader software. They usually able to read JSON-lines files but your hybrid format will require customized scripts.

I personally like JSON-lines format for logs intended for automatic processing. Logs that are just informational and intended for reading by human are tab-separated sets of time-message level-sender-message columns

[Lars Fosdal 1793]

The format was made before JSON was a thing, but I should have had a tab as the separator after the header. 

The logs were meant to be human readable and referenceable text for RegEx searching.

 

 

[Lars Fosdal 1793]

Today, if you are in the enterprise, splunk logging is all the rage.