mysql database backup

backup mysql databases

for all databases owned by $user

mysqldump -u $user -p --all-databases > backup.sql

for a single database owned by $user

mysqldump -u $user -p $database > backup.sql

to restore all databases

mysql -u $user -p < backup.sql

to restore a specific database

mysql -u $user -p $database < backup.sql

Read More

database and sql [sqlite]

in sqlite interpreter:

.dump => dumps all inputs as SQLStatements

echo .dump | sqlite3 database.db > output.sql

.mode colum => provides nice columned output

.header on => headlines with columnnames

.tables => displays all tables

id INTEGER primary key autoincrement

.schema tablename => displays the schema of the table

INSERT:

BEGIN TRANSACTION

INSERT INTO medium VALUES (2,'3-486-27214-4','Einfuehrung in z/OS und OS/390','Basiswissen ueber z/OS und OS/390', 3);

COMMIT;

SELECTS:

select * from table_name; => displays all content of table

select column from table where id<=>value order by column asc||desc;

=> < = =< are valid matchers

order by column||title

asc = ascending

desc = descending

=> automatically increments the primary key in table

select m.titel, o.raum, o.regal, o.fach from medium m, ort o where ort_id = o.id;

=> inner join (only print values for condition)

select m.titel, o.zimmer, o.regal, o.fach from medium m left join ort o on (m.ort_id = o.id);

=> left || right join

VIEWS:

create view buchliste as select p.vname, m.titel, b.ausleihdatum from person p, medium m, besitzt_verleiht b where b.person_id = p.id and b.medium_id=m.id;

=> creates temp table and acts as an alias => select * from buchliste

UPDATE:

update person set telefon='12345' where id=0;

wildcard in sql = %

update person set telefon='123' where nname like "%string%";

ALTER:

alter table person add column alter integer;

=> alters the table per se

TRIGGER:

UPDATE, DELETE, INSERT or SELECT

call certain events as defined

i.e. delete machine => delete reservation

create trigger add_medium after insert on medium

begin

insert into besitzt_verleiht(person_id, medium_id) values(0,new.id);

end;

Read More

db and ruby

sudo gem install sequel 

sqlite3 for ruby needs to be installed

sudo gem install sqlite3


require 'sequel'

Connecting

DB = Sequel.connect('sqlite://test.db') # for local database in this directory

Get Data (table)

table = DB.from(:tablename)
table.all
table.first

table.where(:column => 'value')

method chaining is allowed

table.where(:name => 'me').select(:birthday)

updating can be tricky, probably a better solution, but could not find out right now

counter = reviewer.where(:first_name => 'jxs').select(:reviews)
reviewer.where(:first_name => 'jxs').update(:reviews => counter.first.values.first.next)

mapping / selecting

reviewer.map([:gh_name]).flatten.select{|user| user unless user.nil?}

Read More