<%
string sqlTitle = String.Empty;
string sqlStatement = String.Empty;
switch(Convert.ToInt32(Session["CurrentSQLQuery"]))
{
//------------------------------------------------------------------------------------------------
//SQL STATEMENT 1
case 1:
sqlTitle = "Pseudo pdb file";
sqlStatement =
@"
SELECT 'ATOM', c.atom_number, i.atom_name, i.residue, i.residue_number, c.x_coord, c.z_coord, c.y_coord
FROM dbo.[Coord_678] AS c
JOIN dbo.ID AS i
ON ( i.struct_id = c.struct_id
and i.atom_number = c.atom_number )
WHERE step=500
ORDER BY atom_number ";
break;
//------------------------------------------------------------------------------------------------
//SQL STATEMENT 2
case 2:
sqlTitle = "Distance between two chosen atoms over a simulation";
sqlStatement =
@"
SELECT
SQRT(((POWER(c1.x_coord - c.x_coord , 2)) + (POWER(c1.y_coord - c.y_coord , 2)) + (POWER(c1.z_coord - c.z_coord , 2))))
from dbo.[coord_678] AS c
join dbo.[coord_678] AS c1
on (c.step = c1.step)
WHERE c.atom_number = 1
and c1.atom_number = 10
order by c1.step;";
break;
//------------------------------------------------------------------------------------------------
//SQL STATEMENT 3
case 3:
sqlTitle = "Fraction of native contacts vs RMSD";
sqlStatement =
@"
SELECT
c.native/n.native AS Fract_nat_cont,
c1.rmsd
FROM dbo.[contact_678] as c
JOIN dbo.[RMSD_678] as c1
ON (c.step = c1.step)
JOIN ( SELECT CAST(d.native AS REAL) AS native
FROM dbo.[contact_678] as d
WHERE d.step=0 ) AS n
ON 1=1
ORDER BY c1.step;";
break;
//------------------------------------------------------------------------------------------------
//SQL STATEMENT 4
case 4:
sqlTitle = "CA distance with a 10 angstrom cutoff";
sqlStatement =
@"
SELECT i2.residue_number
, i.residue_number
, SQRT(((POWER(c1.x_coord - c.x_coord , 2))
+ (POWER(c1.y_coord - c.y_coord , 2))
+ (POWER(c1.z_coord - c.z_coord , 2))))
FROM dbo.[coord_678] AS c
JOIN dbo.[coord_678] AS c1
ON ( c1.struct_id = c.struct_id
AND c1.step = c.step
AND c.atom_number > c1.atom_number )
JOIN dbo.ID AS i
ON ( i.struct_id = c.struct_id
AND i.atom_number = c.atom_number )
JOIN dbo.ID AS i2
ON ( i2.struct_id = c1.struct_id
AND i2.atom_number = c1.atom_number )
WHERE i.atom_name = 'CA'
AND i2.atom_name = 'CA'
AND c1.step=1
AND (((POWER(c1.x_coord - c.x_coord , 2))
+ (POWER(c1.y_coord - c.y_coord , 2))
+ (POWER(c1.z_coord - c.z_coord , 2)))) < 100
ORDER BY i2.[residue_number];";
break;
//------------------------------------------------------------------------------------------------
//SQL STATEMENT 5
case 5:
sqlTitle = "List all phi psi angles for a protein at a given time frame";
sqlStatement =
@"
SELECT Distinct i.residue_number, i.residue, c.phi, c.psi
FROM dbo.[PhiPsi_678] AS c
JOIN dbo.ID AS i
ON ( i.struct_id = c.struct_id
and c.residue_number = i.residue_number)
WHERE c.step=500
ORDER BY i.residue_number";
break;
//------------------------------------------------------------------------------------------------
//SQL STATEMENT 6
case 6:
sqlTitle = "Heavy atom contact count";
sqlStatement =
@"
with Neighboring_Residues (res1, res2, [time]) as (
select distinct residue_number_x, residue_number_y, [time] from
(select
a.residue_number as residue_number_x, a.residue as residue_x, a.atom_number as atom_number_x, a.atom_name as atom_name_x,
b.residue_number as residue_number_y, b.residue as residue_y, b.atom_number as atom_number_y, b.atom_name as atom_name_y,
POWER( (b.x_coord - a.x_coord) * (b.x_coord - a.x_coord)+
(b.y_coord - a.y_coord) * (b.y_coord - a.y_coord)+
(b.z_coord - a.z_coord) * (b.z_coord - a.z_coord),
0.5) as distance,
a.step as step
from
dbo.[vCoord_678] a
inner join dbo.[vCoord_678] b
on a.step = b.step and
a.[vid] = b.[vid] and
a.[pid] = b.pid and
a.[temp] = b.temp and
a.[run] = b.run and
a.[cid] = b.cid
where
a.atom_name = 'ca' and
b.atom_name = 'ca' and
a.step <= 5000 and
a.[Run] = 1 and
a.[Temp] =498 and
a.[Conditions] = 'cs=.4,nbcycl=3,cor=8' and
a.atom_number < b.atom_Number
) ca_dist
where distance < 10.0
)
select residue_number_x, atom_name_x, residue_number_y, atom_name_y, count(*) as timecounts from
(select
a.residue_number as residue_number_x, a.residue as residue_x, a.atom_number as atom_number_x, a.atom_name as atom_name_x,
b.residue_number as residue_number_y, b.residue as residue_y, b.atom_number as atom_number_y, b.atom_name as atom_name_y,
POWER( (b.x_coord - a.x_coord) * (b.x_coord - a.x_coord)+
(b.y_coord - a.y_coord) * (b.y_coord - a.y_coord)+
(b.z_coord - a.z_coord) * (b.z_coord - a.z_coord),
0.5) as distance
from
dbo.[vCoord_678] a
inner join dbo.[vCoord_678] b
on a.step = b.step and
a.[vid] = b.[vid] and
a.[pid] = b.pid and
a.[temp] = b.temp and
a.[run] = b.run and
a.[cid] = b.cid
inner join Neighboring_Residues n
on n.res1 = a.Residue_Number and
n.res2 = b.Residue_Number and
n.step = a.step
where
a.atom_type = 1 and
b.atom_type = 1 and
a.[time] <= 1000 and
a.[Run] = 1 and
a.[Temp] =498 and
a.[Conditions] = 'cs=.4,nbcycl=3,cor=8' and
a.atom_number < b.atom_Number
) ca_dist
where distance <
(case when substring(atom_name_x,1,1) = 'C' and
substring(atom_name_y,1,1) = 'C'
then 5.4 else 4.6 end)
group by residue_number_x, atom_name_x, residue_number_y, atom_name_y";
break;
//------------------------------------------------------------------------------------------------
//SQL STATEMENT 7
case 7:
sqlTitle = "Calculate Ramachandran Maps";
sqlStatement =
@"
DECLARE @maxphi AS REAL
DECLARE @maxpsi AS REAL
DECLARE @minphi AS REAL
DECLARE @minpsi AS REAL
DECLARE @start_time_step AS INT
SET @minphi = -180.0
SET @minpsi = -180.0
SET @maxphi = 180.0
SET @maxpsi = 180.0
Select b.sim_id, b.struct_id, b.struct_inst, b.residue,
case when b.phibin = 180 then b.phibin-5.0 else b.phibin end as phibin,
case when b.psibin = 180 then b.psibin-5.0 else b.psibin end as psibin,
(b.cnt)as cnt into dbo.[Rama_1enh]
from (
Select c.sim_id, c.struct_id, c.struct_inst, d.residue_number, d.residue,
@minphi + floor((c.phi-@minphi)/((@maxphi-@minphi)/72.0))*
((@maxphi-@minphi)/72.0) as phibin,
@minpsi + floor((c.psi- @minpsi)/((@maxpsi-@minpsi)/72.0)) *
((@maxpsi-@minpsi)/72.0) as psibin,
Count(*) as cnt
FROM dbo.[phipsi_678] as c
join dbo.[id] as d on c.struct_id=d.struct_id and c.residue_number=d.residue_number
Where c.[step] between 0 and 500000
and d.[atom_name]='CA'
Group by
c.sim_id, c.struct_id, c.struct_inst, d.residue_number, d.residue,
@minphi + floor((c.phi-@minphi)/((@maxphi-@minphi)/72.0))*
((@maxphi-@minphi)/72.0), @minpsi + floor((c.psi- @minpsi)/((@maxpsi- @minpsi)/72.0)) *
((@maxpsi-@minpsi)/72.0))as b
GROUP by b.sim_id, b.struct_id, b.struct_inst, b.residue_number, b.residue, b.phibin, b.psibin, b.cnt
ORDER BY phibin, psibin";
break;
//------------------------------------------------------------------------------------------------
//SQL STATEMENT 8
case 8:
sqlTitle = "Calculate CA distances Using a Cursor";
sqlStatement =
@"
DECLARE @pdb4 VARCHAR(4)
DECLARE @table_prefix VARCHAR(64)
DECLARE @server_name VARCHAR(32)
DECLARE @database_name VARCHAR(32)
DECLARE @sim_id INT
DECLARE @start_step INT
DECLARE @end_step INT
DECLARE @first BIT
DECLARE @view_cmd VARCHAR(MAX)
DECLARE @struct_id INT
DECLARE @residues INT
SET @first = 1
DECLARE sim_id_cursor CURSOR FOR
select b.server_name, b.database_name, a.sim_id, a.start_step, a.end_step from [helix].prep.dbo.[transition_states] as a
join
[helix].directory.dbo.[master_property_v] as b
on a.sim_id=b.sim_id
where a.start_step is not null and b.property_abbrev='coord'
OPEN sim_id_cursor
FETCH NEXT FROM sim_id_cursor INTO @server_name, @database_name, @sim_id, @start_step, @end_step
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_prefix = '[' + @server_name + '].[' + @database_name + '].'
SET @view_cmd =
'
SELECT i2.residue_number as residue_number_i,
i2.residue as residue_i,
i.residue_number as residue_number_j,
i.residue as residue_j
, SQRT(((POWER(c1.x_coord - c.x_coord , 2))
+ (POWER(c1.y_coord - c.y_coord , 2))
+ (POWER(c1.z_coord - c.z_coord , 2)))) as [CA-CA_distance]
INTO dbo.[CA_dist_TS_'+ CAST(@sim_id AS VARCHAR) +']
FROM ' + @table_prefix + 'dbo.[coord_'+ CAST(@sim_id AS VARCHAR) +'] AS c
JOIN ' + @table_prefix + 'dbo.[coord_'+ CAST(@sim_id AS VARCHAR) +'] AS c1
ON ( c1.struct_id = c.struct_id
AND c1.[step] = c.[step]
AND c.atom_number > c1.atom_number )
JOIN ' + @table_prefix + 'dbo.ID AS i
ON ( i.struct_id = c.struct_id
AND i.atom_number = c.atom_number )
JOIN ' + @table_prefix + 'dbo.ID AS i2
ON ( i2.struct_id = c1.struct_id
AND i2.atom_number = c1.atom_number )
WHERE i.atom_name = ''CA''
AND i2.atom_name = ''CA''
AND c1.[step] between '+ CAST(@start_step AS VARCHAR) +' and '+ CAST(@end_step AS VARCHAR) +'
ORDER BY i2.[residue_number], i.[residue_number];
'
FETCH NEXT FROM sim_id_cursor INTO @server_name, @database_name, @sim_id, @start_step, @end_step
--PRINT @view_cmd
EXEC (@view_cmd)
END
CLOSE sim_id_cursor
DEALLOCATE sim_id_cursor";
break;
//------------------------------------------------------------------------------------------------
//DEFAULT STATEMENT
default:
sqlTitle = "Pseudo pdb file";
sqlStatement =
@"
SELECT 'ATOM', c.atom_number, i.atom_name, i.residue, i.residue_number, c.x_coord, c.z_coord, c.y_coord
FROM dbo.[Coord_1A2P-1_1] AS c
JOIN dbo.ID AS i
ON ( i.struct_id = c.struct_id
and i.atom_number = c.atom_number )
WHERE [time]=10
ORDER BY atom_number";
break;
}
%>
Previous QueryNext Query