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

#include <yatishDBsqlite.h>

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...

void	SetLimit (bool b, int i)

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

bool	FillPlotData (RawData &)
	Fills a `RawData` (typedefined in yatishTypes.h). More...

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

void	AddToFilter (long)
	Adds a condition to the WHERE clause of FillList(). More...

wxString	GetFilter () const

void	ClearFilter ()

long	FilteredTotal (wxTimeSpan &) const
	Returns the total duration of currently viewed timeslots (and their count). More...

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

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	GetFirstDay () const

wxDateTime	GetLastDay () const

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

wxDateTime	wxFirstDay

wxDateTime	wxLastDay

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 formatting a timestamp in local time (if need be) must be performed by the application itself.
See also
ReadDates()

Definition at line 40 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 16 of file yatishDBsqlite.cpp.

◆ ~yatishDBsqlite()

yatishDBsqlite::~yatishDBsqlite ( )

Definition at line 49 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 499 of file yatishDBsqlite.cpp.

◆ AddToFilter() [1/2]

void yatishDBsqlite::AddToFilter ( long activityID )

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

Parameters

activityID the foreign key

Note: This overload is relevant to the timeslot table only (SQL error otherwise).

Definition at line 377 of file yatishDBsqlite.cpp.

◆ AddToFilter() [2/2]

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

Adds 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 352 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 108 of file yatishDBsqlite.cpp.

◆ ClearFilter()

void yatishDBsqlite::ClearFilter ( )

inline

Definition at line 55 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 516 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 62 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 219 of file yatishDBsqlite.cpp.

◆ FillPlotData()

bool yatishDBsqlite::FillPlotData ( RawData & v )

Fills a RawData (typedefined in yatishTypes.h).

Parameters

v	where to write (reference)

Returns: false if something went wrong

Definition at line 321 of file yatishDBsqlite.cpp.

◆ FilteredTotal()

long yatishDBsqlite::FilteredTotal ( wxTimeSpan & ts ) const

Returns the total duration of currently viewed timeslots (and their count).

Parameters

[out] ts where the total duration shall be written

Returns: the timeslot count

Definition at line 387 of file yatishDBsqlite.cpp.

◆ FilteredTotalFormatted()

wxString yatishDBsqlite::FilteredTotalFormatted ( )

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 400 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 433 of file yatishDBsqlite.cpp.

◆ GetFilter()

wxString yatishDBsqlite::GetFilter ( ) const

inline

Definition at line 54 of file yatishDBsqlite.h.

◆ GetFirstDay()

wxDateTime yatishDBsqlite::GetFirstDay ( ) const

inline

Definition at line 60 of file yatishDBsqlite.h.

◆ GetLastDay()

wxDateTime yatishDBsqlite::GetLastDay ( ) const

inline

Definition at line 61 of file yatishDBsqlite.h.

◆ 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 458 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 482 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)

Definition at line 562 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 546 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 646 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 588 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 617 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 680 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 201 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 415 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 424 of file yatishDBsqlite.cpp.

◆ SetLimit()

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

inline

Definition at line 49 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 148 of file yatishDBsqlite.cpp.

◆ StopTimeslot()

bool yatishDBsqlite::StopTimeslot ( )

Stops current timeslot.

Returns: false if something went wrong

Definition at line 186 of file yatishDBsqlite.cpp.

Member Data Documentation

◆ clientIDs

wxArrayLong yatishDBsqlite::clientIDs

private

Definition at line 77 of file yatishDBsqlite.h.

◆ currentTimeslotID

long yatishDBsqlite::currentTimeslotID

private

Definition at line 76 of file yatishDBsqlite.h.

◆ filter

wxString yatishDBsqlite::filter

private

Definition at line 78 of file yatishDBsqlite.h.

◆ firstDay

wxString yatishDBsqlite::firstDay

private

Definition at line 78 of file yatishDBsqlite.h.

◆ lastDay

wxString yatishDBsqlite::lastDay

private

Definition at line 78 of file yatishDBsqlite.h.

◆ limitRow

bool yatishDBsqlite::limitRow

private

Definition at line 74 of file yatishDBsqlite.h.

◆ projectIDs

wxArrayLong yatishDBsqlite::projectIDs

private

Definition at line 77 of file yatishDBsqlite.h.

◆ rowLimit

int yatishDBsqlite::rowLimit

private

Definition at line 75 of file yatishDBsqlite.h.

◆ slotCount

long yatishDBsqlite::slotCount

private

Definition at line 76 of file yatishDBsqlite.h.

◆ taskIDs

wxArrayLong yatishDBsqlite::taskIDs

private

Definition at line 77 of file yatishDBsqlite.h.

◆ toolIDs

wxArrayLong yatishDBsqlite::toolIDs

private

Definition at line 77 of file yatishDBsqlite.h.

◆ totalSpan

wxTimeSpan yatishDBsqlite::totalSpan

private

Definition at line 80 of file yatishDBsqlite.h.

◆ wxFirstDay

wxDateTime yatishDBsqlite::wxFirstDay

private

Definition at line 79 of file yatishDBsqlite.h.

◆ wxLastDay

wxDateTime yatishDBsqlite::wxLastDay

private

Definition at line 79 of file yatishDBsqlite.h.

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

/home/nicolas/sources/yatish/src/yatishDBsqlite.h
/home/nicolas/sources/yatish/src/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() [1/2]

◆ AddToFilter() [2/2]

◆ ChoiceSelector()

◆ ClearFilter()

◆ Delete()

◆ FillChoice()

◆ FillList()

◆ FillPlotData()

◆ FilteredTotal()

◆ FilteredTotalFormatted()

◆ First()

◆ GetFilter()

◆ GetFirstDay()

◆ GetLastDay()

◆ 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

◆ wxFirstDay

◆ wxLastDay