<%@ Page Title="Dynameomics - SQL Queries" Language="C#" MasterPageFile="~/MasterPages/Dynameomics_Nav.master" AutoEventWireup="true" CodeFile="SQLQueries.aspx.cs" Inherits="DBDescription_SQLQueries" %> <% 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 Query    Next Query

<%= sqlTitle %>

<%= QueryTransform.SQL_Transform(sqlStatement) %>