User:Birgit bachler/Freeswitch/sqltable

db sqlite3

create table extensions(nr INTEGER, outthere INTEGER, UNIQUE(nr));
create index ext_idx on extensions(nr);
create table calls(id INTEGER PRIMARY KEY, caller varchar(22), extension varchar(6), recording INTEGER, start_time DATETIME, end_time DATETIME);
create table recording(id INTEGER PRIMARY KEY, filename varchar(33), start_time DATETIME end_time DATETIME);


insert into calls (caller, extension, start_time) values("+3167892348", "12345", DATETIME("NOW"));
insert into recording (filename) values ("foo1");
update calls set recording=1, end_time=DATETIME("NOW") where id=1;
update recording set rec_length=TIME("5:03") where id=1;

insert into calls (caller, extension, start_time) values("Nobody", "12345", DATETIME("NOW"));
insert into recording (filename) values ("foo2");
update calls set recording=2 where id=3;
update calls set end_time=DATETIME("NOW") where id=2;
update calls set end_time=DATETIME("NOW") where id=3;
update recording set rec_length=TIME("NOW") where id=2;

import sqlite3
 sql = sqlite3.connect("test.db")
c = sql.cursor()

c.execute("select * from calls")
c.fetchall()

c.execute("select * from calls where extension=%s" % 12345)
c.fetchall()

c.execute("select * from calls where id=%s" % 3)
c.fetchall()

c.execute("insert into calls (caller,extension,start_time) values (+123456,00034,DATETIME(\"NOW\"))")
sql.commit()
c.execute("select * from calls where extension=%s" % 3)
c.fetchall()

c.execute("select * from calls where extension=%s" % 34)
c.fetchall()