cockroachdb | Marks Notes

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!

build: beta-20160929

 2016/09/29 17:51:34 (go1.7.1)
  admin:     http://localhost:8080
  sql:       postgresql://root

localhost:26257?sslmode=disable logs: cockroach-data/logs store⁰: 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://root

cockroach-db:26258?sslmode=disable logs: node2/logs store⁰: path=node2 join⁰: 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:

./src/sequelize.js: dialect: ‘postgres’, dialectOptions: { ssl: true }, /* added dialectOptions */ ./config/default.json: “postgres”: “postgres://markedwards:aaaaaa@35.196.129.11:26257/bank”

======================================================

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) );

INSERT INTO grandparenttable ( id , grandParentTable_name ) VALUES ( unique_rowid() , ‘grandParentTable-1’ ) ; INSERT INTO parenttable ( id , grandParentTable , parentTable_name ) SELECT unique_rowid() , id , ‘parentTable-1’ FROM grandparenttable WHERE 1 AND grandParentTable_name = ‘grandParentTable-1’ ; INSERT INTO childtable ( id , parentTable , childTable_name ) SELECT unique_rowid() , id , ‘childTable-1’ FROM parenttable WHERE 1 AND parentTable_name = ‘parentTable-1’ ; SHOW INDEXES from childtable; SHOW CONSTRAINTS from childtable;

======================

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)
);

cat loadGrandParent.js

cat loadParent.js

}

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}’); `); } })
})

================================================