Interacts with yatish tables in a SQLite database. More...

#include <yatishDBsqlite.h>

Inheritance diagram for yatishDBsqlite:

Collaboration diagram for yatishDBsqlite:

Public Member Functions
	yatishDBsqlite ()
	Mainly connects to the `yatish.sqlite` database. More...

	~yatishDBsqlite ()

bool	FillChoice (wxChoice *, tableID)
	Fills a `wxChoice`. More...

int	ChoiceSelector (tableID, long)
	Finds the proper index for selection in a `wxChoice`. More...

bool	StartTimeslot (int, int, int)
	Starts a new row in the timeslot table. More...

bool	StopTimeslot ()
	Stops current timeslot. More...

int	RunningTimeslots ()
	Determines the number n of unfinished timeslots. More...

bool	FillList (wxListCtrl *, tableID)
	Fills a wxListCtrl. More...

void	AddToFilter (tableID, int)
	Add a condition to the WHERE clause of FillList(). More...

void	SetLimit (bool b, int i)

wxString	FilteredTotal ()
	Writes the total and average durations of currently viewed timeslots. More...

void	ClearFilter ()

void	SetFirstDay (const wxDateTime &)
	Sets private member `firstDay` (for future SQL queries). More...

void	SetLastDay (const wxDateTime &)
	Sets private member `lastDay` (for future SQL queries). More...

wxDateTime	First ()
	Obtains the minimum time in column start from currently selected rows of table yatish_timeslot. More...

wxDateTime	Last ()
	Obtains the maximum time in column start from currently selected rows of table yatish_timeslot. More...

long	LastActivity ()
	Reads the last activity_id in yatish_timeslot. More...

long	Activity (long)
	Reads the activity_id in a record of yatish_timeslot. More...

bool	Delete (tableID, long)
	Marks a local record for deletion. More...

wxString	ReadName (tableID, long)
	Reads a `wxString` from the name column. More...

bool	ReadDates (long, wxDateTime &, wxDateTime &)
	Reads the start and stop fields of a yatish_timeslot record. More...

bool	RecordName (tableID, long, const wxString &)
	Modifies or creates a record with only one field (called name). More...

bool	RecordProject (long, const wxString &, int)
	Modifies or creates a record in the yatish_project table. More...

bool	RecordActivity (long, int, int, int)
	Modifies or creates a record in the yatish_activity table. More...

bool	RecordTimeslot (long, const wxDateTime &, const wxDateTime &, int, int, int)
	Modifies or creates a record in the yatish_timeslot table. More...

Private Attributes
bool	limitRow

int	rowLimit

long	currentTimeslotID

long	slotCount

wxArrayLong	clientIDs

wxArrayLong	projectIDs

wxArrayLong	taskIDs

wxArrayLong	toolIDs

wxString	firstDay

wxString	lastDay

wxString	filter

wxTimeSpan	totalSpan

Additional Inherited Members
Static Public Attributes inherited from yatishDB
static const char *	tableName []
	Must be defined in the same order as `enum tableID`. More...

Protected Member Functions inherited from yatishDB
wxDatabase *	GetDatabase (const wxString &)
	Sets up a (configuration) flux using its argument then calls `wxDatabase::GetDatabase()`. More...

bool	TablesOk (wxDatabase *)
	Returns `false` if any of the yatish table is missings. More...

Static Protected Attributes inherited from yatishDB
static wxDatabase *	masterDB = nullptr

Detailed Description

Interacts with yatish tables in a SQLite database.

Cf. the application example distributed with the wxDatabase library.

Database schema

The sync field of each table only exists in the local (SQlite) database, with values among:

S the row is synchronized with the remote (MySQL/MariaDB) database
I the row was inserted in the local table (and needs publishing to the remote DB)
U the row was updated in the local table (and needs publishing to the remote DB)
D the row was deleted in the local table (and needs publishing to the remote DB)

Timezone management

The timestamps are generated locally by SQLite's date('now'). Such timestamps are stored by SQLite as TEXT fields in the UTC timezone (i.e. we don't use date('now','localtime')).
The SQL datatype used for the MySQL/MariaDB server is DATETIME: thus a timestamp is not modified by the timezone the server is running in (as would be the case with the TIMESTAMP type).
This choice means that the application does not need to be in the same timezone as the server and can also move from a timezone to another one. The drawback is that formating a timestamp in local time (if need be) must be performed by the application itself.
See also
ReadDates()

Definition at line 39 of file yatishDBsqlite.h.

Constructor & Destructor Documentation

◆ yatishDBsqlite()

yatishDBsqlite::yatishDBsqlite ( )

Mainly connects to the yatish.sqlite database.

(initializing protected member yatishDB::masterDB).

Definition at line 17 of file yatishDBsqlite.cpp.

◆ ~yatishDBsqlite()

yatishDBsqlite::~yatishDBsqlite ( )

Definition at line 50 of file yatishDBsqlite.cpp.

Member Function Documentation

◆ Activity()

long yatishDBsqlite::Activity ( long id )

Reads the activity_id in a record of yatish_timeslot.

Parameters

id	the record to read

Returns: wxNOT_FOUND if table is empty

Definition at line 445 of file yatishDBsqlite.cpp.

◆ AddToFilter()

void yatishDBsqlite::AddToFilter	(	tableID	tid,
		int	choice
	)

Add a condition to the WHERE clause of FillList().

Parameters

tid	the table used to build the new condition
choice	the item in this table (as returned from a previously filled `wxChoice`)

Definition at line 322 of file yatishDBsqlite.cpp.

◆ ChoiceSelector()

int yatishDBsqlite::ChoiceSelector	(	tableID	tid,
		long	id
	)

Finds the proper index for selection in a wxChoice.

The selection corresponds to a given record in the refering table (cf. foreign keys in the yatish database schema).

Parameters

tid	either `client_tid`, `project_tid`, `task_tid` or `tool_tid`
id	of the foreign key to read from the refering table

Returns: the wxChoice index (or wxNOT_FOUND in case of problem)

Definition at line 109 of file yatishDBsqlite.cpp.

◆ ClearFilter()

void yatishDBsqlite::ClearFilter ( )

inline

Definition at line 52 of file yatishDBsqlite.h.

◆ Delete()

bool yatishDBsqlite::Delete	(	tableID	tid,
		long	id
	)

Marks a local record for deletion.

Actually deletes it if its sync status was 'I'.

Parameters

tid	Which table?
id	Which record?

Returns: false if something went wrong or deletion was cancelled

Definition at line 462 of file yatishDBsqlite.cpp.

◆ FillChoice()

bool yatishDBsqlite::FillChoice	(	wxChoice *	choices,
		tableID	tid
	)

Fills a wxChoice.

Corresponding values of the SQL id field are stored for future reference.

Parameters

choices	the control to fill
tid	either `client_tid`, `project_tid`, `task_tid` or `tool_tid`

Returns: false if something went wrong

Definition at line 63 of file yatishDBsqlite.cpp.

◆ FillList()

bool yatishDBsqlite::FillList	(	wxListCtrl *	lst,
		tableID	tid
	)

Fills a wxListCtrl.

The SQL id is stored as the row data.

Parameters

lst	the control to fill
tid	the type of data to fetch

Returns: false if something went wrong

Definition at line 220 of file yatishDBsqlite.cpp.

◆ FilteredTotal()

wxString yatishDBsqlite::FilteredTotal ( )

Writes the total and average durations of currently viewed timeslots.

One work day amounts to 7 hours.

Returns: a properly formatted wxString, or wxEmptyString if the currently viewed table is not yatish_timeslot

Definition at line 348 of file yatishDBsqlite.cpp.

◆ First()

wxDateTime yatishDBsqlite::First ( )

Obtains the minimum time in column start from currently selected rows of table yatish_timeslot.

This time is provided in the local timezone.

Returns: Now() if something went wrong

Definition at line 379 of file yatishDBsqlite.cpp.

◆ Last()

wxDateTime yatishDBsqlite::Last ( )

Obtains the maximum time in column start from currently selected rows of table yatish_timeslot.

This time is provided in the local timezone.

Returns: Now() if something went wrong

Definition at line 404 of file yatishDBsqlite.cpp.

◆ LastActivity()

long yatishDBsqlite::LastActivity ( )

Reads the last activity_id in yatish_timeslot.

Returns: wxNOT_FOUND if table is empty

Definition at line 428 of file yatishDBsqlite.cpp.

◆ ReadDates()

bool yatishDBsqlite::ReadDates	(	long	id,
		wxDateTime &	dt1,
		wxDateTime &	dt2
	)

Reads the start and stop fields of a yatish_timeslot record.

Parameters

id	Which record?
dt1	where to write the start value
dt2	where to write the stop value

Returns: false if something went wrong (or activity still running)

N.B. wxWidgets assumes the string from the database is local time which is wrong (it's UTC from SQLite) thus dt1.GetTicks() and dt2.GetTicks() would be biased (by the same offset: no problem when computing their difference). However wxDateTime::MakeFromUTC() appears to reverse the (fake) local->UTC transformation initially applied by the wxDateTime constructor, so that we can get the correct local time afterward (wxWidgets output is always local time). Furthermore GetTicks() is then OK.

However this trick is not satisfying when the Daylight Saving Time changes between (i) writing and (ii) reading a record.

Todo:: fix the DST issue when reading a wxDateTime from the database

Definition at line 520 of file yatishDBsqlite.cpp.

◆ ReadName()

wxString yatishDBsqlite::ReadName	(	tableID	tid,
		long	id
	)

Reads a wxString from the name column.

Parameters

tid	Which table?
id	Which record?

Returns: "" if something went wrong

Definition at line 492 of file yatishDBsqlite.cpp.

◆ RecordActivity()

bool yatishDBsqlite::RecordActivity	(	long	id,
		int	project,
		int	task,
		int	tool
	)

Modifies or creates a record in the yatish_activity table.

Parameters

id	Which record? A new record is created if `id == wxNOT_FOUND`.
project,task,tool	indexes selected in previously filled `wxChoice`s

Returns: false if something went wrong

Definition at line 604 of file yatishDBsqlite.cpp.

◆ RecordName()

bool yatishDBsqlite::RecordName	(	tableID	tid,
		long	id,
		const wxString &	str
	)

Modifies or creates a record with only one field (called name).

Parameters

tid	Which table?
id	Which record? A new record is created if `id == wxNOT_FOUND`.
str	the new name

Returns: false if something went wrong

Definition at line 546 of file yatishDBsqlite.cpp.

◆ RecordProject()

bool yatishDBsqlite::RecordProject	(	long	id,
		const wxString &	str,
		int	client_choice
	)

Modifies or creates a record in the yatish_project table.

Parameters

id	Which record? A new record is created if `id == wxNOT_FOUND`.
str	the new name
client_choice	index selected in a previously filled `wxChoice`

Returns: false if something went wrong

Definition at line 575 of file yatishDBsqlite.cpp.

◆ RecordTimeslot()

bool yatishDBsqlite::RecordTimeslot	(	long	id,
		const wxDateTime &	dt1,
		const wxDateTime &	dt2,
		int	project,
		int	task,
		int	tool
	)

Modifies or creates a record in the yatish_timeslot table.

Also inserts the corresponding row in the activity table, if necessary.

See also: StartTimeslot()

Parameters

id	Which record? A new record is created if `id == wxNOT_FOUND`.
dt1,dt2	the new start and stop
project,task,tool	indexes selected in previously filled `wxChoice`s

Returns: false if something went wrong

Definition at line 638 of file yatishDBsqlite.cpp.

◆ RunningTimeslots()

int yatishDBsqlite::RunningTimeslots ( )

Determines the number n of unfinished timeslots.

Returns: -1 in case of problem (including n > 1)

Definition at line 202 of file yatishDBsqlite.cpp.

◆ SetFirstDay()

void yatishDBsqlite::SetFirstDay ( const wxDateTime & dt )

Sets private member firstDay (for future SQL queries).

firstDay is a UTC time string in ISO format.

See also: SetLastDay()

Definition at line 363 of file yatishDBsqlite.cpp.

◆ SetLastDay()

void yatishDBsqlite::SetLastDay ( const wxDateTime & dt )

Sets private member lastDay (for future SQL queries).

lastDay is a UTC time string in ISO format.

See also: SetFirstDay()

Definition at line 371 of file yatishDBsqlite.cpp.

◆ SetLimit()

void yatishDBsqlite::SetLimit	(	bool	b,
		int	i
	)

inline

Definition at line 50 of file yatishDBsqlite.h.

◆ StartTimeslot()

bool yatishDBsqlite::StartTimeslot	(	int	project,
		int	task,
		int	tool
	)

Starts a new row in the timeslot table.

Also inserts the corresponding row in the activity table, if necessary.

Parameters

project,task,tool 0-based index of the item currently selected in panel #1

Returns: false if something went wrong

Definition at line 149 of file yatishDBsqlite.cpp.

◆ StopTimeslot()

bool yatishDBsqlite::StopTimeslot ( )

Stops current timeslot.

Returns: false if something went wrong

Definition at line 187 of file yatishDBsqlite.cpp.

Member Data Documentation

◆ clientIDs

wxArrayLong yatishDBsqlite::clientIDs

private

Definition at line 70 of file yatishDBsqlite.h.

◆ currentTimeslotID

long yatishDBsqlite::currentTimeslotID

private

Definition at line 69 of file yatishDBsqlite.h.

◆ filter

wxString yatishDBsqlite::filter

private

Definition at line 71 of file yatishDBsqlite.h.

◆ firstDay

wxString yatishDBsqlite::firstDay

private

Definition at line 71 of file yatishDBsqlite.h.

◆ lastDay

wxString yatishDBsqlite::lastDay

private

Definition at line 71 of file yatishDBsqlite.h.

◆ limitRow

bool yatishDBsqlite::limitRow

private

Definition at line 67 of file yatishDBsqlite.h.

◆ projectIDs

wxArrayLong yatishDBsqlite::projectIDs

private

Definition at line 70 of file yatishDBsqlite.h.

◆ rowLimit

int yatishDBsqlite::rowLimit

private

Definition at line 68 of file yatishDBsqlite.h.

◆ slotCount

long yatishDBsqlite::slotCount

private

Definition at line 69 of file yatishDBsqlite.h.

◆ taskIDs

wxArrayLong yatishDBsqlite::taskIDs

private

Definition at line 70 of file yatishDBsqlite.h.

◆ toolIDs

wxArrayLong yatishDBsqlite::toolIDs

private

Definition at line 70 of file yatishDBsqlite.h.

◆ totalSpan

wxTimeSpan yatishDBsqlite::totalSpan

private

Definition at line 72 of file yatishDBsqlite.h.

The documentation for this class was generated from the following files:

/home/nicolas/sources/yatish/yatishDBsqlite.h
/home/nicolas/sources/yatish/yatishDBsqlite.cpp

Public Member Functions

Private Attributes

Additional Inherited Members

Detailed Description

Database schema

Timezone management

Constructor & Destructor Documentation

◆ yatishDBsqlite()

◆ ~yatishDBsqlite()

Member Function Documentation

◆ Activity()

◆ AddToFilter()

◆ ChoiceSelector()

◆ ClearFilter()

◆ Delete()

◆ FillChoice()

◆ FillList()

◆ FilteredTotal()

◆ First()

◆ Last()

◆ LastActivity()

◆ ReadDates()

◆ ReadName()

◆ RecordActivity()

◆ RecordName()

◆ RecordProject()

◆ RecordTimeslot()

◆ RunningTimeslots()

◆ SetFirstDay()

◆ SetLastDay()

◆ SetLimit()

◆ StartTimeslot()

◆ StopTimeslot()

Member Data Documentation

◆ clientIDs

◆ currentTimeslotID

◆ filter

◆ firstDay

◆ lastDay

◆ limitRow

◆ projectIDs

◆ rowLimit

◆ slotCount

◆ taskIDs

◆ toolIDs

◆ totalSpan