CREATE TABLE data (
id INT NOT NULL,
title Varchar(200),
text Text,
number INT,
PRIMARY KEY (id)
)
|
$response = $cpsConn->sendRequest(new
CPS_Request('create-store'));
|
ALTER TABLE data
ADD date DATETIME
|
Not needed due to CPS’s schema-less model
|
ALTER TABLE data
DROP COLUMN title
|
Not needed due to CPS’s schema-less model
|
DROP TABLE data
|
$response = $cpsConn->sendRequest(new
CPS_Request('drop-store'));
|
CREATE INDEX idx_data_num
ON data(number)
|
$req = new CPS_Request('set-policy');
$req-> setExtraXmlParam('
.....
//document/number
index-numbers=yes
...
');
$response = $cpsConn->sendRequest($req);
|
INSERT INTO data
SET id=1,
title='News',
number=5
|
$cpsSimple->insertSingle('1', '5');
|
|
No direct analogue in most SQL systems – insert a record if it doesn’t exist, or
replace the existing one if it does
|
$cpsSimple->updateSingle('1', '5');
|
SELECT * FROM data WHERE id=1
|
$result = $cpsSimple->retrieveSingle('1');
|
SELECT * FROM data
WHERE title='News'
|
$docs = $cpsSimple->
search('="News"');
|
SELECT number FROM data
WHERE title='News'
|
$docs = $cpsSimple->
search('="News"', NULL, NULL,
array('document' => 'no',
'document/number' => 'yes'));
|
SELECT * FROM data
WHERE title='News'
ORDER BY number DESC
|
$docs = $cpsSimple->
search('="News"',
NULL, NULL, NULL,
CPS_NumericOrdering('number', 'desc'));
|
SELECT * FROM data
WHERE number > 5
|
$docs = $cpsSimple->
search('> 5');
|
SELECT * FROM data
WHERE number >= 3
AND number <= 10
|
$docs = $cpsSimple->
search('3 .. 10');
|
SELECT * FROM data
WHERE number < 3 OR title='News'
|
$docs = $cpsSimple->
search('{< 3 }');
|
SELECT * FROM data
WHERE number < 3
AND title!='News'
|
$docs = $cpsSimple->
search('(< 3 )');
|
SELECT AVG(number)
FROM data
WHERE number > 2;
|
$req = new CPS_SearchRequest(' > 2');
$req->setAggregate('AVG(number)');
$response = $cpsConn->sendRequest($req);
|
SELECT title, COUNT(*)
FROM data
WHERE number > 1
GROUP BY title
|
// Facets:
$req = new CPS_SearchRequest(' > 1');
$req->setFacet('title');
$response = $cpsConn->sendRequest($req);
|
SELECT * FROM data
WHERE title LIKE "News %"
OR title LIKE "% News"
OR title LIKE " News "
|
Not a direct analogy, as Clusterpint correctly tokenizes words around punctuation,
handles case insensitivity, etc.
$docs = $cpsSimple->
search('News');
|
SELECT * FROM data
WHERE title LIKE "% Breaking News%"
OR title LIKE "% Breaking News"
OR title LIKE " Breaking News "
|
Not a direct analogy, as Clusterpoint correctly tokenizes words around punctuation,
handles case insensitivity, etc.
$docs = $cpsSimple->
search(' "Breaking News "');
|
|
Proximity search – no analogue in SQL
|
$docs = $cpsSimple->search(' @5 Breaking News @');
|
|
Broad keyword search with relevance ordering – no universal analogue in SQL
|
$docs = $cpsSimple->
search('news', NULL, NULL, NULL,
CPS_RelevanceOrdering('descending'));
|
|
Broad keyword search with term boosting – no analogue in SQL
|
$docs = $cpsSimple->
search('^breaking^ news');
|
|
Wildcard search – no direct analogue in SQL but could be partially implemented with
LIKE “w%”
|
$docs = $cpsSimple->search('w*');
|
|
Stemming – no analogue in SQL
|
$docs = $cpsSimple->search('$news$');
|
|
Grouping – returns results with max of N documents selected from a group. No analogue
in SQL.
|
$req = new CPS_SearchRequest('News');
$req->setGroup('number', 2);
$response = $cpsConn->sendRequest($req);
|
UPDATE data
SET title="Breaking News"
WHERE id=1
|
$cpsSimple->partialReplaceSingle(1,'');
|
DELETE FROM data
WHERE id=1
|
$cpsSimple->deleteSingle(1);
|
DELETE FROM data
WHERE number >= 3
AND number <= 10
|
$cpsSimple->searchDelete
('3 .. 10');
|