horizon.io !! CRUD interface !
to use a shortcut in data-explorer: var db = r.db(‘test’); db.tableList();
eqJoin with map
zip` is equivalent to `map(function (row) { return row(“left”).merge(row(“right”)) })
r.table(“a”).eqJoin(“thething”, r.table(“b”)).map(function (aObj, bObj) { return aObj.merge(bObj, { “id_a”: aObj(“id”), “id_b”: bObj(“id”) }) })`
function (both) { return both(“left”).merge(both(“right”), { “id_a”: both(“left”)(“id”), “id_b”: both(“right”)(“id”) }) }
============================================
initial commands to play with:
a href=‘https://www.rethinkdb.com/docs/system-tables/’>system tables— 2016-04-21
r.db(‘rethinkdb’).tableList() // all tables – notice tableList()
r.db(‘rethinkdb’).table(‘server_status’) // server status
r.db(‘rethinkdb’).table(‘permissions’)
r.db(‘rethinkdb’).table(‘users’)
r.db(‘rethinkdb’).table(‘server_status’)
//r.db(‘starTrek’).table(‘companies’).insert({ company: ‘Starfleet’, type: ‘paramilitary’})
//r.db(‘starTrek’).table(‘employees’).insert({name: ‘Jean-Luc Picard’, rank: “Captain”, company_id: ‘14e31205-a777-43b4-8d4b-b95a7bcc43c9’})
//r.db(‘starTrek’).table(‘employees’)
//r.db(‘starTrek’).table(‘employees’).filter({rank: ‘Captain’, company_id: ‘14e31205-a777-43b4-8d4b-b95a7bcc43c9’}).pluck(‘name’)
//r.db(‘starTrek’).table(‘employees’).eqJoin(‘company_id’,
r.db(‘starTrek’).table(‘companies’), {index:‘id’})
.zip().pluck([‘name’,‘rank’])
//r.db(‘starTrek’).tableDrop(‘companies’)
//r.db(‘starTrek’).tableDrop(‘employees’)
//r.db(‘starTrek’).tableCreate(‘companies’)
//r.db(‘starTrek’).table(‘companies’).insert({ id: 1, company: ‘Starfleet’, type: ‘paramilitary’})
//r.db(‘starTrek’).table(‘companies’)
//r.db(‘starTrek’).tableCreate(‘employees’)
//r.db(‘starTrek’).table(‘employees’).insert({id: 2, name: ‘Jean-Luc Picard’, rank: “Captain”, company_id: 1})
//r.db(‘starTrek’).info() // database information
r.uuid(“mark@edwardsmark.com”);
r.db(‘test’).table(‘myTable;).indexCreate(‘myColumn’)
r.db(‘test’).table(‘myTable;).indexDrop(‘myColumn’)
r.db(‘test’).table(‘myTable;).indexList()
r.db(‘test’).table(‘myTable;).indexStatus(‘myColumn’)
r.expr(“test query”).run(conn, {profile: true}, function(err, result) { var serverTime = result.profile0[‘duration(ms)’]; console.log(serverTime); });
compound embedded records:
.forEach (does not work the way i am thinking)
.bracket – shortcut for ()
r.db(‘test’).table(‘playTable’)(‘embedded field’).nth(0)(‘embedded-field-1-1’)
r.db(‘test’).table(‘playTable’).insert( { ‘tester’:‘one one one onee’ , ‘embedded field’: [ {‘embedded-field-1-1’:‘embedded field one-one value’, ‘embedded-field-1-2’:‘Embedded field one-two value’} , {‘embedded-field-2-1’:‘embedded field two-one value’, ‘embedded-field-2-2’:‘Embedded field two-two value’} , {‘embedded-field-3-1’:‘embedded field three-one value’, ‘embedded-field-3-2’:‘Embedded field three-two value’} ]
})======================================
2016-04-18
r.db(‘comptonTransAnlys’).table(‘Sound’).indexDrop(‘new_key’)
r.db(‘comptonTransAnlys’).table(‘Sound’).indexCreate
( ‘new_key’
, [ r.row(‘Sound_layout_name’)
, r.row(‘Sound_phoneme_1’)
, r.row(‘Sound_phoneme_2’)
, r.row(‘Sound_phoneme_3’)
]
)
r.db(‘comptonTransAnlys’).table(‘Context’).indexCreate
( ‘new_key’
, [ r.row(‘Context_layout_name’)
, r.row(‘Context_phoneme_1’)
, r.row(‘Context_phoneme_2’)
, r.row(‘Context_phoneme_3’)
, r.row(‘Context_position’)
]
)
r.db(‘comptonTransAnlys’).table(‘Stimword_position’).indexCreate
( ‘new_key’
, [ r.row(‘Stimword_position_layout_name’)
, r.row(‘Stimword_position_phoneme_1’)
, r.row(‘Stimword_position_phoneme_2’)
, r.row(‘Stimword_position_phoneme_3’)
, r.row(‘Stimword_position_position’)
]
)
r.db(‘comptonTransAnlys’).table(‘Context’) .eqJoin(‘new_key’, r.db(‘comptonTransAnlys’).table(‘Stimword_position’) , {index:‘new_key’})
======================================
eqjoin using RIGHT index:
r.db(‘test’).tableDrop(‘tableLeft’)
r.db(‘test’).tableDrop(‘tableRight’)
r.db(‘test’).tableCreate(‘tableLeft’)
r.db(‘test’).tableCreate(‘tableRight’)
r.db(‘test’).table(‘tableLeft’).insert({‘key_one’:‘11’, ‘key_two’:‘22’})
r.db(‘test’).table(‘tableRight’).insert({‘key_one’:‘11’, ‘key_two’:‘22’})
//r.db(‘test’).table(‘tableLeft’).indexCreate(‘table_key’, [r.row(‘key_one’),r.row(‘key_two’)])
r.db(‘test’).table(‘tableRight’).indexCreate(‘table_key’, [r.row(‘key_one’),r.row(‘key_two’)])
r.db(‘test’).table(‘tableLeft’).eqJoin(function( row ) { return [ row( ‘key_one’ ), row( ‘key_two’ ) ]; }, r.db(‘test’).table(‘tableRight’) , {index:‘table_key’})
r.table(‘tableLeft’) .eqJoin ( (row) => [row(‘key_one’), row(‘key_two’)] , r.table(‘tableRight’) , {index: “table_key”} );
========================================================
==
D U M P :
takes our rethinkdb and creates a gzip file out of it
- initial install
https://pip.pypa.io/en/stable/installing/
python get-pip.py ;
sudo pip install rethinkdb==2.2.0.post3 ## initial install
rethinkdb dump -c localhost:28015 -e starTrek.companies -f backup.tar.gz ;
==================================
rethinkdb import —file Frequency.json —table comptonTransAnlys.Frequency —pkey id ;
===================================
I N S T A L L A T I O N :
sudo wget http://download.rethinkdb.com/centos/7/`uname -m`/rethinkdb.repo \
-O /etc/yum.repos.d/rethinkdb.repo ;
sudo yum install rethinkdb ;
rethinkdb —version ;
systemctl stop firewalld; ## maybe or better yet, iptables:
iptables -I INPUT -p tcp —dport 8080 —syn -j ACCEPT ;
rethinkdb —bind 192.168.123.123 ;
then in any browser: http://192.168.123.123:8080 ;
U P G R A D E :
sudo yum upgrade rethinkdb ;
======== to set up to autoboot:
(https://groups.google.com/forum/#!topic/rethinkdb/ic4uICkFjew)
1) COPY CONFIGURATION FILE
2) CREATE tmpfiles.d
3) CREATE SYSTEM SERVICE FILE
4) SYSTEMCTL COMMAND
1) configuration file(s) are at: /etc/rethinkdb/instances.d/
cp /etc/rethinkdb/default.conf.sample /etc/rethinkdb/instances.d/instance1.conf ;
edit /etc/rethinkdb/instances.d/instance1.conf —and chance— “bind=all”
2) cat /usr/lib/tmpfiles.d/rethinkdb.conf ;
—————————————————
d /run/rethinkdb 0755 rethinkdb rethinkdb –
—————————————————
0644:
chmod —verbose 644 /usr/lib/tmpfiles.d/rethinkdb.conf ; ## make sure
3) cat /usr/lib/systemd/system/rethinkdb@.service ;
——————————————————————————————————
[Unit]
Description=RethinkDB database server for instance ‘%i’
[Service]
User=rethinkdb
Group=rethinkdb
ExecStart=/usr/bin/rethinkdb serve —config-file /etc/rethinkdb/instances.d/%i.conf
KillMode=process
PrivateTmp=true
[Install]
WantedBy=multi-user.target
—————————————————————————————————
chmod —verbose 644 /usr/lib/systemd/system/rethinkdb@.service ;
4) systemctl enable rethinkdb@instance1 ; systemctl start/stop rethinkdb ; ## not sure why rethinkdb@instance1 didnt work systemctl status rethinkdb ;
and be sure port 8080 is open!
==
==methink conversion
install node!
yum —assumeyes install mariadb-server;
systemctl start mariadb;
npm install chalk fibers mysql nopt prettyjson lodash async ;
/root/Methink/bin/mysql2rethink —version ;
/root/Methink/bin/mysql2rethink -h ‘localhost’ -u ‘comptonUsername’ -p ‘password’ -d ‘comptonTransactionalAnalysisDB’ -D ‘comptontransactionalAnanysisDB’
============================================
to install mysqlxxx to convert mysql into a json file:
- yum installs
yum —assumeyes install git mysql-devel ;
- install ruby
mkdir ~/ruby/;
cd ~/ruby/;
curl https://cache.ruby-lang.org/pub/ruby/2.3/ruby-2.3.0.tar.gz \ >ruby-2.3.0.tar.gz ;
tar -zxf ./ruby-2.3.0.tar.gz ;
cd ./ruby-2-3-0;
./configure ;
make ; make test ; make install ;
ruby -v ; gem -v ;
- install ruby gems
gem install activesupport —pre —verbose ;
gem install mysql2 multi_json mixlib-cli ;
- install mysql2json
mkdir ~/mysql2xxxx/;
cd ~/mysql2xxxx/;
git clone https://github.com/seamusabshere/mysql2xxxx.git ;
export RUBYLIB=/root/mysql2json/mysql2xxxx/lib/ ;
export PATH=$PATH:~/mysql2xxxx/mysql2xxxx/bin/ ;
- test installation
mysql2csv —help ;
mysql2json —help ;
mysql2xml —help ;
- now do something useful
mysql2json \ —user=marky \ —password=xxxxx \ —database=markysDatabase \ —execute=“select UUID ‘id’, marksTable.* from marksTable” ;
===============================================
php mysql 2 rethinkdb migration:
for filename in $(ls *.json;); do
echo ${filename/.json/};
rethinkdb import -f $filename —table test.${filename/.json/} —pkey id ;
done;
<?php
include ‘UUID.php’ ;
$hostname = ‘localhost’;
$username = ‘comptonUser’;
$password = ‘xxx’;
$database = ‘comptonTransAnlysTwo’;
$uuidTagName = ‘id’ ;
$uuidIndex = array();
/** // parents $Frequency_list_array = array(); $Session_names_array = array(); $Sound_array = array(); $Stimword_array= array(); $Teacher_array = array(); // children $Context_array = array(); $Client_master_array = array(); $Stimword_position_array = array(); $Client_context_array = array(); $Client_stimword_array = array(); ***/$Frequency_list_command = <<<END
SELECT UUID ‘:uuidTagName’
, Frequency_list.*
FROM Frequency_list
WHERE 1
ORDER BY Frequency_order
END;
$Stimword_command = <<<END
SELECT UUID ‘:uuidTagName’
, Stimword.*
FROM Stimword
WHERE 1
ORDER BY Stimword_auto_increment
END;
$Stimword_position_command = <<<END
SELECT UUID ‘:uuidTagName’
, Stimword_position.*
FROM Stimword_position
WHERE 1
ORDER BY Stimword_position_auto_increment
END;
$Sound_command = <<<END
SELECT UUID ‘:uuidTagName’
, Sound.*
FROM Sound
WHERE 1
ORDER BY Sound_auto_increment
END;
$Session_names_command = <<<END
SELECT UUID ‘:uuidTagName’
, Session_names.*
FROM Session_names
WHERE 1
ORDER BY Session_order_nbr
END;
$Teacher_command = <<<END
SELECT UUID ‘:uuidTagName’
, Teacher.*
FROM Teacher
WHERE 1
ORDER BY Teacher_email
END;
$Context_command = <<<END
SELECT UUID ‘:uuidTagName’
, Context.*
FROM Context
WHERE 1
ORDER BY
Context_auto_increment
END;
$Client_master_command = <<<END
SELECT UUID ‘:uuidTagName’
, Client_master.*
FROM Client_master
WHERE 1
ORDER BY
Client_master_auto_increment
END;
$Client_context_command = <<<END
SELECT UUID ‘:uuidTagName’
, Client_context.*
FROM Client_context
WHERE 1
ORDER BY
Client_context_auto_increment
END;
$Client_stimword_command = <<<END
SELECT UUID ‘:uuidTagName’
, Client_stimword.*
FROM Client_stimword
WHERE 1
ORDER BY
Client_stimword_auto_increment
END;
try { $dbh = new PDO (“mysql:host=$hostname;dbname=$database”, $username, $password); $dbh->setAttribute(PDO::ATTR_ERRMODE , PDO::ERRMODE_EXCEPTION); $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES , false ); $dbh->setAttribute(PDO::ATTR_STRINGIFY_FETCHES , false ); $stmt = $dbh->prepare(‘SET NAMES “utf8mb4”’); $stmt->execute();
// Frequency_list $Frequency_list_statement = $dbh->prepare(str_replace(’:uuidTagName’, $uuidTagName, $Frequency_list_command)); $Frequency_list_statement->execute(); $myfile = fopen(’./Frequency_list.json’, ‘w’) or die (‘Unable to open’); while($Frequency_list_record = $Frequency_list_statement->fetch(PDO::FETCH_ASSOC)) { fwrite($myfile, json_encode($Frequency_list_record , JSON_PRETTY_PRINT )); if ( ( is_null(trim($Frequency_list_record[‘Frequency’]) )) || ( $Frequency_list_record[‘Frequency’] '') || ( $Frequency_list_record['Frequency'] ‘ ‘) || ( $Frequency_list_record[‘Frequency’] null ) || ( ord($Frequency_list_record['Frequency']) 0) || ( !$Frequency_list_record[‘Frequency’] > ‘’) ) { $uuidIndex [‘Frequency_list’] [‘null!’] = $Frequency_list_record[$uuidTagName] ; } else { $uuidIndex [‘Frequency_list’] [$Frequency_list_record[‘Frequency’]] = $Frequency_list_record[$uuidTagName] ; } /** $uuidIndex [‘Frequency_list’] [ $Frequency_list_record[‘Frequency’] ? $Frequency_list_record[‘Frequency’] : ‘null!’ ] = $Frequency_list_record[$uuidTagName] ; **/ } fclose($myfile); // Stimword $Stimword_statement = $dbh->prepare(str_replace(’:uuidTagName’, $uuidTagName, $Stimword_command)); $Stimword_statement->execute(); $myfile = fopen(’./Stimword.json’, ‘w’) or die (‘Unable to open’); while($Stimword_record = $Stimword_statement->fetch(PDO::FETCH_ASSOC)) { //array_push($Stimword_array, $Stimword_record); fwrite($myfile, json_encode($Stimword_record , JSON_PRETTY_PRINT )); $uuidIndex [‘Stimword’] [$Stimword_record[‘Stimword_auto_increment’]] = $Stimword_record[$uuidTagName] ; } fclose($myfile); // Sound $Sound_statement = $dbh->prepare(str_replace(’:uuidTagName’, $uuidTagName, $Sound_command)); $Sound_statement->execute(); $myfile = fopen(’./Sound.json’, ‘w’) or die (‘Unable to open’); while($Sound_record = $Sound_statement->fetch(PDO::FETCH_ASSOC)) { //array_push($Sound_array, $Sound_record); fwrite($myfile, json_encode($Sound_record , JSON_PRETTY_PRINT )); $uuidIndex [‘Sound’] [$Sound_record[‘Sound_auto_increment’]] = $Sound_record[$uuidTagName] ; } fclose($myfile); // Session_names $Session_names_statement = $dbh->prepare(str_replace(’:uuidTagName’, $uuidTagName, $Session_names_command)); $Session_names_statement->execute(); $myfile = fopen(’./Session_names.json’, ‘w’) or die (‘Unable to open’); while($Session_names_record = $Session_names_statement->fetch(PDO::FETCH_ASSOC)) { //array_push($Session_names_array, $Session_names_record); fwrite($myfile, json_encode($Session_names_record , JSON_PRETTY_PRINT )); $uuidIndex [‘Session_names’] [$Session_names_record[‘Session_name’]] = $Session_names_record[$uuidTagName] ; } fclose($myfile); // Teacher $Teacher_statement = $dbh->prepare(str_replace(’:uuidTagName’, $uuidTagName, $Teacher_command)); $Teacher_statement->execute(); $myfile = fopen(’./Teacher.json’, ‘w’) or die (‘Unable to open’); while($Teacher_record = $Teacher_statement->fetch(PDO::FETCH_ASSOC)) { //array_push($Teacher_array ,$Teacher_record); fwrite($myfile, json_encode($Teacher_record , JSON_PRETTY_PRINT )); $uuidIndex [‘Teacher’] [$Teacher_record[‘Teacher_auto_increment’]] = $Teacher_record[$uuidTagName] ; } fclose($myfile); // Context $Context_statement = $dbh->prepare(str_replace(’:uuidTagName’, $uuidTagName, $Context_command)); $Context_statement->execute(); $myfile = fopen(’./Context.json’, ‘w’) or die (‘Unable to open’); while($Context_record = $Context_statement->fetch(PDO::FETCH_ASSOC)) { $Context_record[‘Session_names_id’] = $uuidIndex [‘Sound’] [$Context_record[‘Sound_auto_increment’]] ; //array_push($Context_array ,$Context_record); fwrite($myfile, json_encode($Context_record , JSON_PRETTY_PRINT )); $uuidIndex [‘Context’] [$Context_record[‘Context_auto_increment’]] = $Context_record[$uuidTagName] ; } fclose($myfile); // Stimword_position $Stimword_position_statement = $dbh->prepare(str_replace(’:uuidTagName’, $uuidTagName, $Stimword_position_command)); $Stimword_position_statement->execute(); $myfile = fopen(’./Stimword_position.json’, ‘w’) or die (‘Unable to open’); while($Stimword_position_record = $Stimword_position_statement->fetch(PDO::FETCH_ASSOC)) { $Stimword_position_record[‘Stimword_id’] = $uuidIndex [‘Stimword’] [$Stimword_position_record[‘Stimword_auto_increment’]] ; $Stimword_position_record[‘Context_id’] = $uuidIndex [‘Context’] [$Stimword_position_record[‘Context_auto_increment’]] ; //array_push($Stimword_position_array , $Stimword_position_record); fwrite($myfile, json_encode($Stimword_position_record , JSON_PRETTY_PRINT )); $uuidIndex [‘Stimword_position’] [$Stimword_position_record[‘Stimword_position_auto_increment’]] = $Stimword_position_record[$uuidTagName] ; } fclose($myfile); // Client_master $Client_master_statement = $dbh->prepare(str_replace(’:uuidTagName’, $uuidTagName, $Client_master_command)); $Client_master_statement->execute(); $myfile = fopen(’./Client_master.json’, ‘w’) or die (‘Unable to open’); while($Client_master_record = $Client_master_statement->fetch(PDO::FETCH_ASSOC)) { $Client_master_record[‘Session_names_id’] = $uuidIndex [‘Session_names’] [$Client_master_record[‘Client_master_session_name’]] ; $Client_master_record[‘Teacher_id’] = $uuidIndex [‘Teacher’] [$Client_master_record[‘Teacher_auto_increment’]] ; //array_push($Client_master_array ,$Client_master_record); fwrite($myfile, json_encode($Client_master_record , JSON_PRETTY_PRINT )); $uuidIndex [‘Client_master’] [$Client_master_record[‘Client_master_auto_increment’]] = $Client_master_record[$uuidTagName] ; } fclose($myfile); // Client_context $Client_context_statement = $dbh->prepare(str_replace(’:uuidTagName’, $uuidTagName, $Client_context_command)); $Client_context_statement->execute(); $myfile = fopen(’./Client_context.json’, ‘w’) or die (‘Unable to open’); while($Client_context_record = $Client_context_statement->fetch(PDO::FETCH_ASSOC)) { /* TO RETRIVE A PARENT_TABLE KEY TO STORE INTO THE CHILD_TABLE RECORD: $CHILD_TABLE_record[‘PARENT_TABLE_id’] = $uuidIndex [‘PARENT_TABLE’] [$CHILD_TABLE_record[‘PARENT_TABLE_auto_increment’]] TO STORE A PARENT_TABLE KEY FOR A FUTURE CHILD_TABLE REFERENCE: $uuidIndex [‘PARENT_TABLE’] [$PARENT_TABLE_record[‘PARENT_TABLE_auto_increment’]] = $Client_context_record[$uuidTagName] */ $Client_context_record[‘Context_id’] = $uuidIndex [‘Context’] [$Client_context_record[‘Context_auto_increment’]] ; $Client_context_record[‘Client_master_id’] = $uuidIndex [‘Client_master’] [$Client_context_record[‘Client_master_auto_increment’]] ; if ( ( is_null(trim($Client_context_record[‘Client_context_error_frequency’]) )) || ( $Client_context_record[‘Client_context_error_frequency’] '') || ( $Client_context_record['Client_context_error_frequency'] ‘ ‘) || ( $Client_context_record[‘Client_context_error_frequency’] null ) || ( ord($Client_context_record['Client_context_error_frequency']) 0) || ( !$Client_context_record[‘Client_context_error_frequency’] > ‘’) ) { $Client_context_record[‘Frequency_id’] = $uuidIndex [‘Frequency_list’] /*error 348 */ [‘null!’] ; } else { $Client_context_record[‘Frequency_id’] = $uuidIndex [‘Frequency_list’] /*error 354 */ [$Client_context_record[‘Client_context_error_frequency’]] ; } //array_push($Client_context_array ,$Client_context_record); fwrite($myfile, json_encode($Client_context_record , JSON_PRETTY_PRINT )); $uuidIndex [‘Client_context’] [$Client_context_record[‘Client_context_auto_increment’]] = $Client_context_record[$uuidTagName] ; } fclose($myfile); // Client_stimword $Client_stimword_statement = $dbh->prepare(str_replace(’:uuidTagName’, $uuidTagName, $Client_stimword_command)); $Client_stimword_statement->execute(); $myfile = fopen(’./Client_stimword.json’, ‘w’) or die (‘Unable to open’); while($Client_stimword_record = $Client_stimword_statement->fetch(PDO::FETCH_ASSOC)) { /* TO RETRIVE A PARENT_TABLE KEY TO STORE INTO THE CHILD_TABLE RECORD: $CHILD_TABLE_record[‘PARENT_TABLE_id’] = $uuidIndex [‘PARENT_TABLE’] [$CHILD_TABLE_record[‘PARENT_TABLE_auto_increment’]] */ $Client_stimword_record[‘Client_context_id’] = $uuidIndex [‘Client_context’] [$Client_stimword_record[‘Client_context_auto_increment’]] ; $Client_stimword_record[‘Stimword_position_id’] = $uuidIndex [‘Stimword_position’] [$Client_stimword_record[‘Stimword_position_auto_increment’]] ; //array_push($Client_stimword_array ,$Client_stimword_record); fwrite($myfile, json_encode($Client_stimword_record )); } fclose($myfile); $dbh = null; } catch(PDOException $e) { echo $e->getMessage(); }===========================================
script to generate test-data:
‘use strict’
var fs = require(‘fs’) ;
var uuid = require(‘node-uuid’) ;
var parentStream = fs.createWriteStream(’./parent.json’, {‘flags’:‘a’});
var childStream = fs.createWriteStream(’./child.json’, {‘flags’:‘a’});
for ( var x=0; x<500; x++ ) {
var parentId = uuid.v4() ;
