Changeset: 5409c3557055 for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=5409c3557055 Modified Files: sql/src/test/LSST/Tests/All sql/src/test/LSST/Tests/cleanup.sql sql/src/test/LSST/Tests/dbQuery000.sql sql/src/test/LSST/Tests/dbQuery001.sql sql/src/test/LSST/Tests/dbQuery002.sql sql/src/test/LSST/Tests/dbQuery003.sql sql/src/test/LSST/Tests/dbQuery004.sql sql/src/test/LSST/Tests/dbQuery005.sql sql/src/test/LSST/Tests/dbQuery007.sql sql/src/test/LSST/Tests/dbQuery008.sql sql/src/test/LSST/Tests/dbQuery009.sql sql/src/test/LSST/Tests/dbQuery010.sql sql/src/test/LSST/Tests/dbQuery011.sql sql/src/test/LSST/Tests/dbQuery012.sql sql/src/test/LSST/Tests/dbQuery013.sql sql/src/test/LSST/Tests/dbQuery014.sql sql/src/test/LSST/Tests/dbQuery015.sql sql/src/test/LSST/Tests/dbQuery016.sql sql/src/test/LSST/Tests/dbQuery017.sql sql/src/test/LSST/Tests/dbQuery018.sql sql/src/test/LSST/Tests/dbQuery019.sql sql/src/test/LSST/Tests/dbQuery020.sql sql/src/test/LSST/Tests/dbQuery022.sql sql/src/test/LSST/Tests/dbQuery024.sql sql/src/test/LSST/Tests/dbQuery025.sql sql/src/test/LSST/Tests/dbQuery026.sql sql/src/test/LSST/Tests/dbQuery027.sql sql/src/test/LSST/Tests/dbQuery028.sql sql/src/test/LSST/Tests/dbQuery029.sql sql/src/test/LSST/Tests/dbQuery030.sql sql/src/test/LSST/Tests/dbQuery031.sql sql/src/test/LSST/Tests/dbQuery032.sql sql/src/test/LSST/Tests/dbQuery034.sql sql/src/test/LSST/Tests/dbQuery035.sql sql/src/test/LSST/Tests/dbQuery036.sql sql/src/test/LSST/Tests/dbQuery037.sql sql/src/test/LSST/Tests/dbQuery038.sql sql/src/test/LSST/Tests/dbQuery041.sql sql/src/test/LSST/Tests/dbQuery042.sql sql/src/test/LSST/Tests/dbQuery043.sql sql/src/test/LSST/Tests/dbQuery044.sql sql/src/test/LSST/Tests/dbQuery045.sql sql/src/test/LSST/Tests/dbQuery046.sql sql/src/test/LSST/Tests/dbQuery047.sql sql/src/test/LSST/Tests/dbQuery048.sql sql/src/test/LSST/Tests/dbQuery053.sql sql/src/test/LSST/Tests/dbQuery054.sql sql/src/test/LSST/Tests/dbQuery055.sql sql/src/test/LSST/Tests/dbQuery056.sql sql/src/test/LSST/Tests/dbQuery066.sql sql/src/test/LSST/Tests/schema_3_1_88.sql Branch: default Log Message:
The test cases for LSST. diffs (truncated from 2755 to 300 lines): diff -r da7578b49268 -r 5409c3557055 sql/src/test/LSST/Tests/All --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sql/src/test/LSST/Tests/All Sat Nov 13 10:32:53 2010 +0100 @@ -0,0 +1,50 @@ +schema_3_1_88 +#dbQuery000 +#dbQuery001 +#dbQuery002 +#dbQuery003 +#dbQuery004 +#dbQuery005 +dbQuery007 +#dbQuery008 +dbQuery009 +#dbQuery010 +#dbQuery011 +#dbQuery012 +#dbQuery013 +#dbQuery014 +#dbQuery015 +#dbQuery016 +#dbQuery017 +#dbQuery018 +#dbQuery019 +#dbQuery020 +#dbQuery022 +#dbQuery024 +#dbQuery025 +#dbQuery026 +#dbQuery027 +#dbQuery028 +#dbQuery029 +#dbQuery030 +#dbQuery031 +#dbQuery032 +#dbQuery034 +dbQuery035 +dbQuery036 +#dbQuery037 +#dbQuery038 +#dbQuery041 +#dbQuery042 +dbQuery043 +#dbQuery044 +#dbQuery045 +#dbQuery046 +#dbQuery047 +#dbQuery048 +#dbQuery053 +#dbQuery054 +#dbQuery055 +#dbQuery056 +#dbQuery066 +#cleanup diff -r da7578b49268 -r 5409c3557055 sql/src/test/LSST/Tests/cleanup.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sql/src/test/LSST/Tests/cleanup.sql Sat Nov 13 10:32:53 2010 +0100 @@ -0,0 +1,102 @@ + -- Taken from http://dev.lsstcorp.org/trac/browser/DMS/cat/trunk/sql/lsstSchema4mysqlDC3b.sql + -- Modified to be acceptable to MonetDB + -- Author: M.L. Kersten + -- Date : Nov 2, 2010 + + -- LSST Database Schema + -- $Author$ + -- $Revision$ + -- $Date$ + -- + -- See <http://lsstdev.ncsa.uiuc.edu/trac/wiki/Copyrights> + -- for copyright information. + + + ALTER TABLE prv_cnf_PolicyKey DROP CONSTRAINT FK_prv_cnf_PolicyKey_prv_PolicyKey + FOREIGN KEY (policyKeyId) REFERENCES prv_PolicyKey (policyKeyId); + + ALTER TABLE prv_cnf_SoftwarePackage DROP CONSTRAINT FK_prv_cnf_SoftwarePackage_prv_SoftwarePackage + FOREIGN KEY (packageId) REFERENCES prv_SoftwarePackage (packageId); + + ALTER TABLE prv_PolicyKey DROP CONSTRAINT FK_prv_PolicyKey_prv_PolicyFile + FOREIGN KEY (policyFileId) REFERENCES prv_PolicyFile (policyFileId); + + ALTER TABLE _mops_EonQueue DROP CONSTRAINT FK__mopsEonQueue_MovingObject + FOREIGN KEY (movingObjectId,movingObjectVersion) REFERENCES MovingObject (movingObjectId,movingObjectVersion); + + ALTER TABLE _mops_MoidQueue DROP CONSTRAINT FK__mops_MoidQueue_MovingObject + FOREIGN KEY (movingObjectId,movingObjectVersion) REFERENCES MovingObject (movingObjectId,movingObjectVersion); + + ALTER TABLE mops_SSM DROP CONSTRAINT FK_mopsSSM_mopsSSMDesc + FOREIGN KEY (ssmDescId) REFERENCES mops_SSMDesc (ssmDescId); + + ALTER TABLE mops_Tracklet DROP CONSTRAINT FK_mopsTracklets_mopsSSM + FOREIGN KEY (ssmId) REFERENCES mops_SSM (ssmId); + + ALTER TABLE Source_pt2 DROP CONSTRAINT FK_Source_Object + FOREIGN KEY (objectId) REFERENCES Object (objectId); + + + DROP TABLE AAA_Version_3_1_88 (version CHAR); + DROP TABLE prv_Activity; + DROP TABLE prv_cnf_PolicyKey; + DROP TABLE prv_cnf_SoftwarePackage; + DROP TABLE prv_Filter; + DROP TABLE prv_PolicyFile; + DROP TABLE prv_PolicyKey; + DROP TABLE prv_Run; + DROP TABLE prv_SoftwarePackage; + DROP TABLE _MovingObjectToType; + DROP TABLE _ObjectToType; + DROP TABLE _qservChunkMap; + DROP TABLE _qservObjectIdMap; + DROP TABLE _qservSubChunkMap; + DROP TABLE _tmpl_Id; + DROP TABLE _tmpl_IdPair; + DROP TABLE _tmpl_MatchPair; + DROP TABLE Ccd_Detector; + DROP TABLE Durations; + DROP TABLE Filter; + DROP TABLE Logs; + DROP TABLE ObjectType; + DROP TABLE sdqa_ImageStatus; + DROP TABLE sdqa_Metric; + DROP TABLE sdqa_Rating_ForScienceAmpExposure; + DROP TABLE sdqa_Rating_ForScienceCcdExposure; + DROP TABLE sdqa_Threshold; + DROP TABLE _mops_Config; + DROP TABLE _mops_EonQueue; + DROP TABLE _mops_MoidQueue; + DROP TABLE _tmpl_mops_Ephemeris; + DROP TABLE _tmpl_mops_Prediction; + DROP TABLE mops_Event; + DROP TABLE mops_Event_OrbitDerivation; + DROP TABLE mops_Event_OrbitIdentification; + DROP TABLE mops_Event_TrackletAttribution; + DROP TABLE mops_Event_TrackletPrecovery; + DROP TABLE mops_MovingObjectToTracklet; + DROP TABLE mops_SSM; + DROP TABLE mops_SSMDesc; + DROP TABLE mops_Tracklet; + DROP TABLE mops_TrackletToDiaSource; + DROP TABLE mops_TrackToTracklet; + DROP TABLE _Raw_Ccd_ExposureToVisit; + DROP TABLE FpaMetadata; + DROP TABLE RaftMetadata; + DROP TABLE Raw_Amp_Exposure; + DROP TABLE Raw_Amp_Exposure_Metadata; + DROP TABLE Raw_Ccd_Exposure; + DROP TABLE Raw_Ccd_Exposure_Metadata; + DROP TABLE Science_Amp_Exposure; + DROP TABLE Science_Amp_Exposure_Metadata; + DROP TABLE Science_Ccd_Exposure; + DROP TABLE Science_Ccd_Exposure_Metadata; + DROP TABLE Visit; + DROP TABLE CalibSource; + DROP TABLE DiaSource; + DROP TABLE ForcedSource; + DROP TABLE MovingObject; + DROP TABLE Object; + DROP TABLE ObjectExtras; + DROP TABLE Source_pt1; + DROP TABLE Source_pt2; diff -r da7578b49268 -r 5409c3557055 sql/src/test/LSST/Tests/dbQuery000.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sql/src/test/LSST/Tests/dbQuery000.sql Sat Nov 13 10:32:53 2010 +0100 @@ -0,0 +1,34 @@ +-- http://dev.lsstcorp.org/trac/wiki/dbQuery000 +-- the queries assume the following views and table +-- exist in addition to Precursor Schema: + +DROP VIEW Galaxy; +CREATE VIEW Galaxy AS + SELECT Object.*, + ObjectPhotoZ.redshift, ObjectPhotoZ.redshiftErr + FROM Object + JOIN _Object2Type USING (objectId) + JOIN ObjectType USING (typeId) + JOIN ObjectPhotoZ ON (Object.objectId=ObjectPhotoZ.objectId) + WHERE ObjectType.description = "galaxy" + AND _Object2Type.probability > 90; -- 0-100% + +DROP VIEW Star; +CREATE VIEW Star AS + SELECT Object.*, + ObjectPhotoZ.redshift, ObjectPhotoZ.redshiftErr + FROM Object + JOIN _Object2Type USING (objectId) + JOIN ObjectType USING (typeId) + JOIN ObjectPhotoZ ON (Object.objectId=ObjectPhotoZ.objectId) + WHERE ObjectType.description = "star" + AND _Object2Type.probability > 90; -- 0-100% + +--DROP TABLE IF EXISTS Neighbors; +--DROP TABLE Neighbors; +CREATE TABLE Neighbors ( + objectId BIGINT, + neighborObjectId BIGINT, + distance FLOAT, + neighborType TINYINT +); diff -r da7578b49268 -r 5409c3557055 sql/src/test/LSST/Tests/dbQuery001.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sql/src/test/LSST/Tests/dbQuery001.sql Sat Nov 13 10:32:53 2010 +0100 @@ -0,0 +1,5 @@ +-- http://dev.lsstcorp.org/trac/wiki/dbQuery001 +SELECT taiMidPoint, modelMag, modelMagErr +FROM Source +WHERE objectId = 123 + AND filterId = 3; diff -r da7578b49268 -r 5409c3557055 sql/src/test/LSST/Tests/dbQuery002.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sql/src/test/LSST/Tests/dbQuery002.sql Sat Nov 13 10:32:53 2010 +0100 @@ -0,0 +1,6 @@ +-- http://dev.lsstcorp.org/trac/wiki/dbQuery002 +SELECT * +FROM VarObject +JOIN _Object2Type USING(objectId) +WHERE typeId = 2 +AND probability = 0.34; diff -r da7578b49268 -r 5409c3557055 sql/src/test/LSST/Tests/dbQuery003.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sql/src/test/LSST/Tests/dbQuery003.sql Sat Nov 13 10:32:53 2010 +0100 @@ -0,0 +1,8 @@ +-- http://dev.lsstcorp.org/trac/wiki/dbQuery003 +SELECT * +FROM Object +WHERE ra BETWEEN 1 AND 2 + AND decl BETWEEN 1 AND 2 + AND zMag BETWEEN 1 AND 2 + AND grColor BETWEEN 1 AND 2 + AND izColor BETWEEN 1 AND 2; diff -r da7578b49268 -r 5409c3557055 sql/src/test/LSST/Tests/dbQuery004.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sql/src/test/LSST/Tests/dbQuery004.sql Sat Nov 13 10:32:53 2010 +0100 @@ -0,0 +1,4 @@ +-- http://dev.lsstcorp.org/trac/wiki/dbQuery004 +SELECT uMag, gMag, rMag, iMag, zMag, yMag +FROM Object +WHERE (objectId % 100 )= 0.01; diff -r da7578b49268 -r 5409c3557055 sql/src/test/LSST/Tests/dbQuery005.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sql/src/test/LSST/Tests/dbQuery005.sql Sat Nov 13 10:32:53 2010 +0100 @@ -0,0 +1,7 @@ +-- http://dev.lsstcorp.org/trac/wiki/dbQuery005 +SELECT objectId +FROM Alert +JOIN _Alert2Type USING (alertId) +JOIN AlertType USING (alertTypeId) +WHERE alertTypeDescr = 'newTransients' + AND Alert.timeGenerated BETWEEN 1 AND 2; diff -r da7578b49268 -r 5409c3557055 sql/src/test/LSST/Tests/dbQuery007.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sql/src/test/LSST/Tests/dbQuery007.sql Sat Nov 13 10:32:53 2010 +0100 @@ -0,0 +1,8 @@ +-- http://dev.lsstcorp.org/trac/wiki/dbQuery007 +SELECT * +FROM Source_pt1 +JOIN Filter USING(filterId) +WHERE areaSpec_box(raMin, :declMin, :raMax, :declMax) + AND filterName = 'u' + AND variability BETWEEN :varMin AND :varMax +ORDER BY objectId, taiObs ASC; diff -r da7578b49268 -r 5409c3557055 sql/src/test/LSST/Tests/dbQuery008.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sql/src/test/LSST/Tests/dbQuery008.sql Sat Nov 13 10:32:53 2010 +0100 @@ -0,0 +1,5 @@ +-- http://dev.lsstcorp.org/trac/wiki/dbQuery008 +SELECT * +FROM VarObject +WHERE ra BETWEEN 1 AND 2 + AND decl BETWEEN 2 AND 3; diff -r da7578b49268 -r 5409c3557055 sql/src/test/LSST/Tests/dbQuery009.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sql/src/test/LSST/Tests/dbQuery009.sql Sat Nov 13 10:32:53 2010 +0100 @@ -0,0 +1,4 @@ +-- http://dev.lsstcorp.org/trac/wiki/dbQuery009 +SELECT * +FROM Object +WHERE objectId = :objectId; diff -r da7578b49268 -r 5409c3557055 sql/src/test/LSST/Tests/dbQuery010.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sql/src/test/LSST/Tests/dbQuery010.sql Sat Nov 13 10:32:53 2010 +0100 @@ -0,0 +1,27 @@ +-- http://dev.lsstcorp.org/trac/wiki/dbQuery010 +SELECT s1.objectId AS objectId1, + s2.objectId AS objectId2 +FROM Star AS s1 +JOIN Star AS s2 USING (objectId) +JOIN Neighbors AS N ON (s2.objectId = N.neighborObjectId) +WHERE N.distance < 0.5/60 -- distance is 1/2 arc second or less +-- AND s1.run != s2.run -- observations are from two different runs (SDSS) + AND s1.uMag BETWEEN 1 AND 27 -- magnitudes are reasonable + AND s1.gMag BETWEEN 1 AND 27 + AND s1.rMag BETWEEN 1 AND 27 + AND s1.iMag BETWEEN 1 AND 27 + AND s1.zMag BETWEEN 1 AND 27 + AND s1.yMag BETWEEN 1 AND 27 + AND s2.uMag BETWEEN 1 AND 27 + AND s2.gMag BETWEEN 1 AND 27 + AND s2.rMag BETWEEN 1 AND 27 + AND s2.iMag BETWEEN 1 AND 27 + AND s2.zMag BETWEEN 1 AND 27 _______________________________________________ Checkin-list mailing list [email protected] http://mail.monetdb.org/mailman/listinfo/checkin-list
