SQLitening Support Forum

Hi,

I am new to SQLite. I am using SQLite Manager Firefox add-on. I have created a database and a table. This table stores webcam recordings. Each record is a recording up to 3 min. I am trying to retrieve one record per continuous recording. If there is gap more than 3 min then it would be considered as a separate recording. Below is the script.

CREATE TABLE recordings (
    [key]          INTEGER        PRIMARY KEY ASC AUTOINCREMENT,
    filename       VARCHAR(50),
    start_datetime DATETIME,
    end_datetime   DATETIME,
    deleted        BOOLEAN
);

INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f1', '2013-08-26 00:00:00', '2013-08-26 00:03:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f2', '2013-08-26 00:03:01', '2013-08-26 00:06:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f3', '2013-08-26 00:06:01', '2013-08-26 00:09:00', 0);

INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f4', '2013-08-26 00:14:00', '2013-08-26 00:17:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f5', '2013-08-26 00:17:01', '2013-08-26 00:20:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f6', '2013-08-26 00:20:01', '2013-08-26 00:23:00', 0);

INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f7', '2013-08-26 00:30:00', '2013-08-26 00:33:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f8', '2013-08-26 00:33:01', '2013-08-26 00:36:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f9', '2013-08-26 00:36:01', '2013-08-26 00:39:00', 0);

INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f10', '2013-08-26 00:44:00', '2013-08-26 00:47:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f11', '2013-08-26 00:47:01', '2013-08-26 00:50:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f12', '2013-08-26 00:50:01', '2013-08-26 00:53:00', 0);

The result should be,

recording1 2013-08-26 00:00:00 2013-08-26 00:09:00
recording2 2013-08-26 00:14:00 2013-08-26 00:23:00
recording3 2013-08-26 00:30:00 2013-08-26 00:39:00
recording4 2013-08-26 00:44:00 2013-08-26 00:53:00

I would appreciate any help in resolving this.

Thanks,
Jdp

Do you have any PowerBASIC code to look at?
Where is the blob of the recording stored?

#COMPILE EXE
#DIM ALL
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
  LOCAL s AS STRING
  LOCAL x,columns AS LONG
  slOpen "recordings.db3","C"
  slexe "drop table if exists recordings"
  slexe "create table if not exists recordings " +_
      "(key integer primary key autoincrement," + _
      "filename,start_datetime,end_datetime,deleted)"
  s = "Begin Immediate;"
  s=s+"Insert into recordings values(null,'Recording1', '2013-08-26 00:00:00', '2013-08-26 00:03:00', 0);"
  s=s+"Insert into recordings values(null,'Recording2', '2013-08-26 00:03:01', '2013-08-26 00:06:00', 0);"
  s=s+"End"
  slExe s
  slSel "select * from recordings"
  columns = slGetColumnCount
  RESET s
  DO WHILE slgetrow
    FOR x = 1 TO columns
      s = s + slf(x) + " "
    NEXT
    s = s + $CR
  LOOP
  ? s,,"Results"
END FUNCTION


Hi cj,

No its not in PowerBASIC.

Sorry, I don't understand what your code is doing. I am trying to write T-SQL which retrieve me the result as I have posted. The binary data column I have not posted for simplicity.

Do you have any idea how can I achieve this?

Thanks,
Jdp

Sorry.
I only know how to call SQLitening using PowerBASIC or Visual Basic 6.

If it were me, I'd add another field ( 'NewRec DEFAULT 0' ) to the table.  Then I'd create a trigger to compare the start time of any new record to the latest end time of any existing record.  If the gap is greater than or equal to 3 min, NewRec = RowID.

Then it would be easy to retrive all records where NewRec > 0. That should be the first record of every "recording" if I understood the OP correctly.