/// <summary>
/// Return stream private extenstions, just for testing..
/// </summary>
/// <param name="streamID"></param>
/// <returns></returns>
public static Hashtable GetStreamPrivateExtensions(int streamID)
{
String query = "select privextns from stream where stream_id=" + streamID;
if (!DatabaseUtility.openAndExecute(query))
{
return(null);
}
try
{
if (reader.Read())
{
System.Data.SqlTypes.SqlBinary sb = reader.GetSqlBinary(0);
byte[] ba = sb.Value;
object o = Utility.ByteArrayToObject(ba);
Hashtable h = (Hashtable)o;
return(h);
}
}
finally
{
DatabaseUtility.cleanUpConnection();
}
return(null);
}
/// <summary>
/// Get the first frame for payload/cname after start and before end. Return null
/// if such frame does not exist in the database.
/// </summary>
/// <param name="payload"></param>
/// <param name="cname"></param>
/// <param name="start"></param>
/// <param name="end"></param>
/// <returns></returns>
public static byte[] GetFirstFrame(PayloadType payload, String cname, String name, long start, long end)
{
// get the indices and stream_id:
//DateTime startDt = DateTime.Parse(start);
//DateTime endDt = DateTime.Parse(end);
String query = "select top 1 s.stream_id, f.frame_id , f.raw_start, f.raw_end " +
"from participant p join stream s on p.participant_id=s.participant_id " +
"join frame f on s.stream_id=f.stream_id where s.payload_type='" +
payload.ToString() + "' and p.cname='" + cname + "' ";
if ((name != null) && (name.Trim() != ""))
{
query += "and s.name='" + name + "' ";
}
query += "and f.frame_time >= " + start.ToString() + " and f.frame_time < " +
end.ToString() + " order by frame_time ";
if (!DatabaseUtility.openAndExecute(query))
{
return(null);
}
int stream_id = -1;
int frame_id = -1;
int raw_start = -1;
int raw_end = -1;
try
{
if (reader.Read())
{
stream_id = reader.GetInt32(0);
frame_id = reader.GetInt32(1);
raw_start = reader.GetInt32(2);
raw_end = reader.GetInt32(3);
}
}
finally
{
DatabaseUtility.cleanUpConnection();
}
//Debug.WriteLine("stream_id="+stream_id.ToString());
if (stream_id != -1)
{
BufferChunk ret = new BufferChunk(raw_end - raw_start + 1);
LoadBuffer(stream_id, raw_start, raw_end, ref ret);
return(ret.Buffer);
}
return(null);
}
/// <summary>
/// A variation of GetStreams that also returns the start time for each stream. Unused for now.
/// </summary>
/// <param name="participantID"></param>
/// <returns></returns>
public static Stream[] GetStreams2(int participantID)
{
String query = "SELECT s.stream_id, s.name, s.payload_type, "+
"( SELECT MIN (frame_time) FROM frame WHERE stream_id = s.stream_id ) as st_time, " +
"( SELECT COUNT( frame_id) FROM frame WHERE stream_id = s.stream_id ) as frames, " +
"( SELECT (MAX( frame_time ) - MIN(frame_time))/10000000 FROM frame WHERE stream_id = s.stream_id ) as seconds, " +
"( SELECT datalength(data) FROM rawStream WHERE stream_id = s.stream_id) as bytes " +
"FROM stream as s " +
"WHERE s.participant_id = " + participantID.ToString() + " AND " +
"(SELECT datalength(data) FROM rawStream WHERE stream_id = s.stream_id) > 1";
if (!DatabaseUtility.openAndExecute(query))
{
return(null);
}
ArrayList al = new ArrayList();
try
{
while (reader.Read())
{
al.Add(new Stream(
reader.GetInt32(0), //stream id
reader.GetString(1), // name
reader.GetString(2), // payload,
reader.GetInt64(3), //start time
reader.GetInt32(4), // frames
reader.IsDBNull(5) ? 0L : reader.GetInt64(5), // seconds
reader.GetInt32(6))); // bytes
}
}
finally
{
DatabaseUtility.cleanUpConnection();
}
if (al.Count != 0)
{
return((Stream[])al.ToArray(typeof(Stream)));
}
return(null);
}
/// <summary>
/// For the given cname, stream name, payload and time range, return an array of relevant stream_id's
/// The array is in order by earliest frame time. If the stream name is null it will be excluded
/// from the where clause.
/// </summary>
public static int[] GetStreams(PayloadType payload, String cname, String name, long start, long end)
{
String query = "select s.stream_id, min(f.frame_time) as st_start " +
"from participant p " +
"join stream s on p.participant_id=s.participant_id " +
"join frame f on s.stream_id=f.stream_id " +
"where s.payload_type='" + payload.ToString() + "' " +
"and p.cname='" + cname + "' ";
if ((name != null) && (name.Trim() != ""))
{
query += "and s.name='" + name + "' ";
}
query += "and f.frame_time > " + start.ToString() + " " +
"and f.frame_time < " + end.ToString() + " " +
"group by s.stream_id " +
"order by st_start";
if (!DatabaseUtility.openAndExecute(query))
{
return(null);
}
ArrayList al = new ArrayList();
try
{
while (reader.Read())
{
al.Add(reader.GetInt32(0));
}
}
finally
{
DatabaseUtility.cleanUpConnection();
}
if (al.Count != 0)
{
return((int[])al.ToArray(typeof(int)));
}
return(null);
}
/// <summary>
/// Submit a query to return a single Int32
/// </summary>
/// <returns></returns>
private static int numericQuery(String query)
{
if (!DatabaseUtility.openAndExecute(query))
{
return(-1);
}
int count = -1;
try
{
if (reader.Read())
{
count = reader.GetInt32(0);
}
}
finally
{
DatabaseUtility.cleanUpConnection();
}
return(count);
}
public static long GetConferenceStartTime(PayloadType payload, String cname, long start, long end)
{
String query = "select c.start_dttm " +
"from conference c " +
"join participant p on c.conference_id=p.conference_id " +
"join stream s on p.participant_id=s.participant_id " +
"join frame f on s.stream_id=f.stream_id " +
"where s.payload_type='" + payload.ToString() + "' " +
"and p.cname='" + cname + "' " +
"and f.frame_time > " + start.ToString() + " " +
"and f.frame_time < " + end.ToString() + " " +
"group by c.start_dttm";
//Debug.WriteLine(query);
if (!DatabaseUtility.openAndExecute(query))
{
return(0);
}
long ret = 0;
try
{
if (reader.Read())
{
DateTime dt = reader.GetDateTime(0);
ret = dt.Ticks;
}
}
finally
{
DatabaseUtility.cleanUpConnection();
}
return(ret);
}
/// <summary>
/// Return the first frame for the given stream.
/// </summary>
/// <param name="streamID"></param>
/// <returns></returns>
public static byte[] GetFirstFrame(int streamID)
{
String query = "select top 1 f.raw_start, f.raw_end " +
"from stream s join frame f on s.stream_id=f.stream_id " +
"where s.stream_id=" + streamID.ToString() + " " +
"order by f.frame_time ";
if (!DatabaseUtility.openAndExecute(query))
{
return(null);
}
int raw_start = -1;
int raw_end = -1;
try
{
if (reader.Read())
{
raw_start = reader.GetInt32(0);
raw_end = reader.GetInt32(1);
}
}
finally
{
DatabaseUtility.cleanUpConnection();
}
if (raw_start != -1)
{
BufferChunk ret = new BufferChunk(raw_end - raw_start + 1);
LoadBuffer(streamID, raw_start, raw_end, ref ret);
return(ret.Buffer);
}
return(null);
}
/// <summary>
/// Like get streams, but doesn't return the frame count. It turns out that is the expensive part of the query.
/// Consequently this can run a bit faster.
/// </summary>
public static Stream[] GetStreamsFaster(int participantID)
{
String query = "SELECT s.stream_id, s.name, s.payload_type, "+
"( SELECT (MAX( frame_time ) - MIN(frame_time))/10000000 " +
"FROM frame WHERE stream_id = s.stream_id ) as seconds, " +
"( SELECT datalength(data) FROM rawStream WHERE stream_id = s.stream_id) as bytes " +
"FROM stream as s WHERE s.participant_id = " + participantID.ToString() +
" AND (SELECT datalength(data) FROM rawStream WHERE stream_id = s.stream_id) > 1 " +
"ORDER BY [name] ASC";
if (!DatabaseUtility.openAndExecute(query))
{
return(null);
}
ArrayList al = new ArrayList();
try {
while (reader.Read())
{
Stream stream = new Stream(
reader.GetInt32(0), //stream id
reader.GetString(1), // name
reader.GetString(2), // payload,
1000, // frames
reader.IsDBNull(3) ? 0L : reader.GetInt64(3), // seconds
reader.GetInt32(4)); // bytes
al.Add(stream);
}
}
finally {
DatabaseUtility.cleanUpConnection();
}
return((Stream[])al.ToArray(typeof(Stream)));
}