Sqlite i skrypty batch

Dostęp do SQLite jest możliwy nie tylko z poziomu aplikacji. Dzięki kilku sztuczkom, można w łatwy sposób wykonywać operacje na tej świetnej bazie danych z poziomu zwykłych skryptów batch w Windowsie. Więcej na ten temat można przeczytać w poniższym wpisie:
http://bigcode.wordpress.com/2010/12/12/accessing-sqlite-from-a-dos-batch-file/

Na tej bazie stworzyłam sobie bardzo prostą aplikację logującą w bazie danych mój czas pracy. Aplikacja (w zasadzie to zestaw skryptów) jest wykonana głównie w celach poglądowych, aczkolwiek gdyby dopracować ją, mogłaby stanowić „niskobudżetowy” licznik czasu pracy.

Przygotowałam sobie następujący schemat danych:

DROP TABLE IF EXISTS workEventsDict;
DROP TABLE IF EXISTS workTime;
DROP TABLE IF EXISTS daySummary;
DROP INDEX IF EXISTS eventDescrIndex;

CREATE TABLE workEventsDict (
  id INTEGER PRIMARY KEY,
  symbol TEXT,-- short symbol: START itp.
  eventDescr TEXT
);

CREATE INDEX eventDescrIndex ON workEventsDict (eventDescr);

CREATE TABLE workTime (
  id INTEGER PRIMARY KEY,
  eventDate DATE,
  eventTime TIME,
  eventDescr REFERENCES workEvents(eventDescr)
    ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE daySummary (
  id INTEGER PRIMARY KEY,
  day DATE,
  workTimeSummary TIME,
  overTime TIME,
  note TEXT-- holiday or other notes
);

INSERT INTO workEventsDict (id, symbol, eventDescr)
  VALUES (1, "START", "system started");
INSERT INTO workEventsDict (id, symbol, eventDescr)
  VALUES (2, "STOP", "system stopped");
INSERT INTO workEventsDict (id, symbol, eventDescr)
  VALUES (3, "LOCK", "profile locked");
INSERT INTO workEventsDict (id, symbol, eventDescr)
  VALUES (4, "UNLOCK", "profile unlocked");

W dalszej części wpisu prezentuję dalsze kody źródłowe.

Schemat zawiera trzy proste tabelki:
workTime – główna tabela, zawierająca dane zdarzeń związanych z czasem pracy data, godzina i znacznik zdarzenia,
workEventsDict – tabela słownikowa, zawierająca znaczniki zdarzeń i ich krótki opis,
daySummary – podsumowanie dnia – pod koniec dnia, gdy wyłączamy komputer, wywołane jest zapytanie, które liczy, ile czasu przepracowaliśmy oraz wylicza ewentualne nadgodziny. Z tego powodu zapytania wykonywane podczas wyłączania komputera, są najdłuższe.

Można dorobić, aby od czasu pracy odliczany był czas spędzony z dala od komputera – w tym celu należałoby odjąć czas pomiędzy zalogowaniami taga „LOCK” i „UNLOCK”.

 

W przypadku nastąpienia zdarzeń (uruchomienie, zamknięcie systemu) mają zostać wywołane następujące skrypty, zapisane odpowiednio w postaci plików SQL:

  • uruchomienie komputera – start.sql i log_startup_time.bat :
 INSERT INTO workTime (eventDate, eventTime, eventDescr)
VALUES (DATE('now'), TIME('now', 'localtime'), "START"); 
  • wyłączenie komputera – stop.sql i log_shutdown_time.bat :
INSERT INTO workTime (eventDate, eventTime, eventDescr)
VALUES (DATE('now'), TIME('now', 'localtime'), "STOP");

INSERT INTO daySummary (day, workTimeSummary) VALUES
(
  DATE('now'),
  (
    SELECT
    TIME(
      sum(
        strftime('%s', (SELECT eventTime FROM workTime WHERE eventDate LIKE DATE('now') AND eventDescr LIKE "STOP"))
        - strftime('%s', (SELECT eventTime FROM workTime WHERE eventDate LIKE DATE('now') AND eventDescr LIKE "START"))
      ) ,'unixepoch'
    )
  )
);

UPDATE daysummary SET overtime =
(
  SELECT overtime FROM
  (
    SELECT
      TIME(
      sum(
        strftime('%s', (SELECT workTimeSummary FROM daySummary WHERE id = last_insert_rowid()))
        - strftime('%s', (SELECT(TIME('08:00'))))
      ) ,'unixepoch'
    ) AS overtime
  )
)
WHERE id=last_insert_rowid();
  • zablokowanie komputera – lock.sql i log_lock_time.bat:
INSERT INTO workTime (eventDate, eventTime, eventDescr)
VALUES (DATE('now'), TIME('now', 'localtime'), "LOCK"); 
  • odblokowanie (ponowne zalogowanie do systemu) – unlock.sql i log_unlock_time.bat :
INSERT INTO workTime (eventDate, eventTime, eventDescr)
VALUES (DATE('now'), TIME('now', 'localtime'), "UNLOCK"); 

 

Pliki ze skryptami bat są praktycznie identyczne, z wyjątkiem wywołania pliku SQL. Skrypty logują także informacje do zwykłego pliku tekstowego (startstop.txt).

  • log_shutdown_time.bat:
set hour=%time:~0,2%
if "%hour:~0,1%" == " " set hour=0%hour:~1,1%
set min=%time:~3,2%
if "%min:~0,1%" == " " set min=0%min:~1,1%

X:\pathToDirectory\sqlite3.exe worktime < stop.sql

echo %date%, STOP: %hour%:%min% >>  X:\pathToDirectory\startstop.txt
  • log_startup_time.bat:
set hour=%time:~0,2%
if "%hour:~0,1%" == " " set hour=0%hour:~1,1%
set min=%time:~3,2%
if "%min:~0,1%" == " " set min=0%min:~1,1%

X:\pathToDirectory\sqlite3.exe worktime < start.sql

echo %date%, START: %hour%:%min% >>  X:\pathToDirectory\startstop.txt 

 

Skrypty te podpięłam pod harmonogram zadań Windowsa:

Start->Uruchom->gpedit.msc

Konfiguracja komputera->Ustawienia systemu Windows->Skrypty:
Autostart -> Dodaj -> ścieżka do skryptu log_startup_time.bat
Autostart -> Dodaj -> ścieżka do skryptu log_shutdown_time.bat

Logowanie_czasu_pracy_batch80

 

Niestety okazało się, że jest to sposób „zbyt słaby” – owszem, dane poprawnie logowały się do mojego kontrolnego pliku tekstowego, aczkolwiek skrypt nie potrafił już wywołać SQLite3.exe. Uruchamiając skrypty bat ręcznie, dane poprawie logowały się do bazy, co pozwala mi sądzić, że brak możliwości wywoływania innych plików ma związek zapewne z ograniczeniami tego systemowego narzędzia.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *