NOTE: pay particular attention to the “—store” name – maybe better not to rely on —store defaults??
NOTE: probably best to only run one node – it appears that starting up two (or more) nodes then trying to start just one does not work.
installation and initial setup:
###########sudo su; ## this line should NOT be cut-pasted, since what follows it will be ignored….
set -o vi;
sudo yum —assumeyes update ;
sudo yum —assumeyes group install “Development Tools” ;
sudo yum —assumeyes install golang ; ## cockroachdb requires golang
#
mkdir ~/go ;
export GOPATH=~/go ;
export PATH=$PATH:$GOPATH/bin ;
go version ;
##mkdir ~/cockroachdb ;
sudo curl https://binaries.cockroachdb.com/cockroach-latest.linux-amd64.tgz > ~/cockroach.tgz;
sudo gzip -d ~/cockroach.tgz ;
sudo tar -xvf ~/cockroach.tar ;
sudo rm -v -f ~/cockroach.tar;
###cd cockroach-latest.linux-amd64 ;
sudo mv —interactive —verbose ~/cockroach-latest.linux-amd64/cockroach /usr/local/bin/ ;
sudo rmdir —verbose ~/cockroach-latest.linux-amd64/ ;
- export PATH=$PATH:/root/cockroachdb/cockroach-latest.linux-amd64 ; ???
- cd ; ???
cockroach version ;
##
- NOTE: sudo cockroach DOES NOT WORK – root is not allowed to start cockroach!
cockroach start —background —insecure ; ## skip the CA certificate stuff (for now)
cockroach start —background —insecure —logtostderr ; ## note optional logging errors!
2016/09/29 17:51:34 (go1.7.1)
admin: http://localhost:8080
sql: postgresql://rootlocalhost:26257?sslmode=disable
logs: cockroach-data/logs
store0: path=cockroach-data
(http-host comes from the google-console in this case)
cockroach start —store=node2 —insecure —port=26258 —http-port=8081 —join=localhost:26257 —background ;
- this one did not work for some reason:
cockroach start —store=node2 \ —insecure \ —port=26258 \ —http-host=10.138.0.2 \ —http-port=8081 \ —join=localhost:26257 \ —background ;
build: beta-20160929 2016/09/29 17:51:34 (go1.7.1)
admin: http://10.138.0.2:8081
sql: postgresql://rootcockroach-db:26258?sslmode=disable
logs: node2/logs
store0: path=node2
join0: localhost:26257
cockroach node status —insecure ; ## shows status of all nodes (2017-10-27 added “—insecure)
cockroach quit —port=26257 —insecure ; ## stop node
========================================================
all examples are —insecure – how to secure:
(notice use of internal and external IP numbers for google VPS instance)
cockroach cert create-ca \ —certs-dir=./path-to-certs-directory \ —ca-key=./path-to-ca-key/ca-key ;
cockroach cert create-node localhost 35.196.129.11 \ —certs-dir=./path-to-certs-directory \ —ca-key=./path-to-ca-key/ca-key ;
cockroach cert create-client markedwards \ —certs-dir=./path-to-certs-directory \ —ca-key=./path-to-ca-key/ca-key ;
cockroach user set markedwards \ —certs-dir=./path-to-certs-directory \ —host=35.196.129.11 \ —password ;
cockroach start —certs-dir=./path-to-certs-directory \ —host=10.142.0.16 \ —http-host=localhost \ —background ;
cockroach node ls —certs-dir=./path-to-certs-directory \ —host=35.196.129.11 ;
cockroach node status —certs-dir=./path-to-certs-directory \ —host=35.196.129.11 ;
cockroach quit —host=35.196.129.11 \ —certs-dir=./path-to-certs-directory ;
cockroach sql -e ‘GRANT ALL ON DATABASE bank TO markedwards’ \ —certs-dir=./path-to-certs-directory \ —host=35.196.129.11 ;
cockroach sql -e ‘GRANT SELECT, INSERT, UPDATE ON bank.users TO markedwards’ \ —certs-dir=./path-to-certs-directory \ —host=35.196.129.11 ;
==
feathers file edits:
======================================================
- create a database “bank” , create a user “maxroach” and give maxroach priveleges
cockroach sql -e ‘CREATE DATABASE bank;’ —insecure ;
cockroach user set maxroach —insecure ; ## create user maxroach
cockroach sql -e ‘GRANT ALL ON DATABASE bank TO maxroach;’ —insecure ;
cockroach sql —user=maxroach —insecure; ## (2017-10-27 added “—insecure)
###############create database bank; SHOW DATABASES; /* notice comment has closed star-slash */ SET DATABASE=bank ; /* comments has to be closed like this */ CREATE TABLE accounts ( id INT PRIMARY KEY , balance DECIMAL , createdat TIMESTAMP , updatedat TIMESTAMP ) ; SHOW COLUMNS FROM accounts; INSERT INTO accounts VALUES ( unique_rowid(), 10000.50 ) ; /* notice unique_id() ! */ SELECT * FROM accounts ;========================
—insecure – seems to be the only way thus far to allow for anything other than “localhost”
https://www.cockroachlabs.com/docs/secure-a-cluster.html
============== N O D E ===
NODE installation:
sudo firewall-cmd —zone=dmz —add-port=26257/tcp —permanent ; sudo firewall-cmd —reload ;
sudo curl —silent —location https://rpm.nodesource.com/setup_8.x | sudo bash – ;
sudo yum —assumeyes install nodejs ;
node —version ; ## look for v8.8.1 (at the time of this writing)
== posgres example:
https://www.cockroachlabs.com/docs/stable/build-a-nodejs-app-with-cockroachdb.html – node.js app
(2017-10-27 !!)
mkdir ./node-test; cd ./node-test;
npm install async pg —save ;
curl https://raw.githubusercontent.com/cockroachdb/docs/master/_includes/app/basic-sample.js > basic-sample.js;
node basic-sample.js ; ## worked 2017-10-27
== sequelize example
https://www.cockroachlabs.com/docs/stable/build-a-nodejs-app-with-cockroachdb-sequelize.html
mkdir ~/sequelize-example; cd ~/sequelize-example;
curl https://raw.githubusercontent.com/cockroachdb/docs/master/_includes/app/sequelize-basic-sample.js > sequelize-basic-sample.js ;
npm install sequelize sequelize-cockroachdb —save ;
node sequelize-basic-sample.js ;
==============
https://www.cockroachlabs.com/docs/constraints.html
??????
update -what about this?
https://www.cockroachlabs.com/docs/serial.html
CREATE TABLE serial (a SERIAL PRIMARY KEY, b STRING, c BOOL);
???????
parent/child:
CREATE TABLE grandParentTable
( id INT PRIMARY KEY
, grandParentTable_name VARCHAR NOT NULL
);
CREATE TABLE parentTable
( id INT PRIMARY KEY
, grandParentTable INT NOT NULL REFERENCES grandParentTable(id)
, parentTable_name VARCHAR NOT NULL
, INDEX (grandParentTable)
);
CREATE TABLE childTable
( id INT PRIMARY KEY
, parentTable INT NOT NULL REFERENCES parentTable(id)
, childTable_name VARCHAR NOT NULL
, INDEX (parentTable)
);
CREATE TABLE newkey
( id INT PRIMARY KEY
, newkey_value VARCHAR NOT NULL
);
CREATE TABLE childTable
( id INT PRIMARY KEY
, parentTable INT NOT NULL REFERENCES parentTable (id)
, newkey INT NOT NULL REFERENCES newkey (id)
, childTable_name VARCHAR NOT NULL
, INDEX (parentTable )
, index (newkey)
);
GRANT SELECT, INSERT ON transanlys.childtable TO maxroach;
GRANT SELECT, INSERT ON transanlys.parenttable TO maxroach;
GRANT SELECT, INSERT ON transanlys.grandparenttable TO maxroach;
SHOW GRANTS ON transanlys.parenttable FOR maxroach;
——————-+—————+———————-
| Table | User | Privileges |
——————-+—————+———————-
| parentTable | maxroach | INSERT,SELECT |
——————-+—————+———————-
======================
SET database = ‘transanlys’;
DROP TABLE childtable;
CREATE TABLE childTable (id INT PRIMARY KEY, parentTable INT NOT NULL REFERENCES parentTable (id), newkey INT NOT NULL REFERENCES newkey (id), childTable_name VARCHAR NOT NULL, INDEX (parentTable ), index (newkey) );
DELETE FROM childtable; DELETE FROM parenttable; DELETE FROM grandparenttable; delete from newkey ;
GRANT SELECT, INSERT ON transanlys.childtable TO maxroach;
(run node script)
SELECT count(*) FROM parenttable
, newkey
, childtable
WHERE (newkey.id = childtable.newkey)
AND (parenttable.id = childtable.parenttable)
AND (newkey.id = 5);
===================================================
2017-04-07:
to install/run cockroachdb from docker (to get around the stupid -msse4.2 error)
curl -fsSL https://get.docker.com/ | sh ;
systemctl start docker;
systemctl status docker;
docker run hello-world ; ## test!
git clone https://github.com/cockroachdb/cockroach.git ;
git submodule update —init ;
(edit rocksdb/cgo_flags.go file and remove the -msse4.2 on line eight)
export GOPATH=~/go;
export PATH=$PATH/$GOPATH/bin ;
build/builder.sh make build ;
./cockroach version ;
============================================
2017-04-12 – create a deepstream cockroach table:
create table sequelizeTable
( sequelizeId SMALLSERIAL PRIMARY KEY
, teacherEmail VARCHAR
, sequelizeName VARCHAR
, sequelizeAddress VARCHAR
, transactionID VARCHAR
, createdAt TIMESTAMP
, updatedAt TIMESTAMP
) ;
============================================
FIREWALL ISSUE: to test:
yum —assumeyes install httpd ;
systemctl start httpd.service ;
echo ‘test apache test’ >/var/www/html/index.html ;
(see if page will load)
https://superuser.com/questions/794104/how-to-permanently-disable-firewall-in-red-hat-linux
service iptables save
service iptables stop
chkconfig iptables off
to enable default port 26257:
firewall-cmd —zone=public —add-port=26257/tcp —permanent ;
firewall-cmd —reload;
#########################################################
yum —assumeyes install jemalloc-devel ; ## ????? (in case you have to build) ???
======= LOAD-STRESS TEST ===========================
CREATE TABLE alternateParent
( id INT PRIMARY KEY
, alternateParent_value VARCHAR NOT NULL
);
CREATE TABLE grandParentTable
( id INT PRIMARY KEY
, grandParentTable_name VARCHAR NOT NULL
);
CREATE TABLE parentTable
( id INT PRIMARY KEY
, grandParentTable INT NOT NULL REFERENCES grandParentTable(id) ON DELETE CASCADE ON UPDATE CASCADE
, parentTable_name VARCHAR NOT NULL
, INDEX (grandParentTable)
);
CREATE TABLE childTable
( id INT PRIMARY KEY
, parentTable INT NOT NULL REFERENCES parentTable (id) ON DELETE CASCADE ON UPDATE CASCADE
, alternateParent INT NOT NULL REFERENCES alternateParent (id) ON DELETE CASCADE ON UPDATE CASCADE
, childTable_name VARCHAR NOT NULL
, INDEX (parentTable )
, index (alternateParent)
);
let maxGrandParent = 100;
let grandParentKey= 0;
for ( grandparentIncr=1; grandparentIncr <= maxGrandParent; grandparentIncr++ ) {
grandParentKey++;
var grandparentData = ‘Grandparent Value: ‘ + grandParentKey;
console.log(`INSERT INTO “grandparenttable” (“id”,“grandparenttable_name”) VALUES (${grandParentKey}, ‘${grandparentData}’); `);
}
cat loadParent.js
let maxParent = 50;
let parentKey = 0;
for ( var grandParentIncr = 1; grandParentIncr <= 100; grandParentIncr++ ) {
grandParentData = ‘Grandparent Value: ‘ + grandParentIncr;
for ( var parentIncr = 1; parentIncr <= maxParent; parentIncr++ ) {
parentKey++;
var parentData = grandParentData + ‘ Parent Data: ‘ + parentKey;
console.log(`INSERT INTO “parenttable” (“id”,“grandparenttable”,“parenttable_name”) VALUES (${parentKey}, ${grandParentIncr}, ‘${parentData}’); `);
}
}
cat loadAlternateParent.js let maxAlternateParent = 10000; let alternateParentKey= 0; for ( alternateparentIncr=1; alternateparentIncr <= maxAlternateParent; alternateparentIncr++ ) { alternateParentKey++; var saveKey = alternateParentKey * 100; var altData = ‘Alternative Parent! ‘ + saveKey; console.log(`INSERT INTO “alternateparent” (“id”,“alternateparent_value”) VALUES (${saveKey}, ‘${altData}’); `); }cat loadChild.js
let maxChild = 20;
let childKey = 0;
for ( var parentIncr = 1; parentIncr <= 5000; parentIncr++ ) {
for ( var childIncr = 1; childIncr <= maxChild; childIncr++ ) {
childKey++;
var childData = ‘Parent data: ‘ + parentIncr + ‘ Child Data! ‘ ;
var alternateParentKey = (Math.floor(childKey / 10)+1) * 100 ;
console.log(`INSERT INTO “childtable” (“id”,“parenttable”,“alternateparent”,“childtable_name”) VALUES (${childKey}, ${parentIncr}, ${alternateParentKey}, ‘${childData}’); `);
}
}
WITH SEQUELIZE CALLS:
cat loadChild.js
const sleep = require(‘sleep’);
const Sequelize = require(‘sequelize’);
const sequelize = new Sequelize(‘bank’, ‘feathersuser’, ‘aaaaaa’, {
host: ’159.203.111.167’,
port: 26257,
dialect: ‘postgres’,
dialectOptions: {
ssl: true
},
operatorsAliases: false,
pool: {
max: 5,
min: 0,
acquire: 30000,
idle: 10000
}
});
sequelize .authenticate() .then(() => { console.log(‘Connection has been established successfully.’); }) .catch(err => { console.error(‘Unable to connect to the database:’, err); });
let maxChild = 10;
let childKey = 0;
sequelize.query(“SELECT id, parentTable_name FROM parentTable WHERE 1=1”).spread( (results, metadata) => {
results.forEach((element, index, array) => {
console.log(element.id);
console.log(element.parenttable_name);
for ( var childIncr = 1; childIncr <= maxChild; childIncr++ ) {
childKey++;
var childData = element.parenttable_name + ‘ Child Data: ‘ + childKey;
sequelize.query(`INSERT INTO childTable (id, parentTable, alternateParent, childTable_name) VALUES (${childKey}, ${element.id}, 12345, ‘${childData}’); `);
}
})
})
================================================
