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

bcache

Bcache is a caching method which can be used to utilize the power and speed of SSDs to speed up HDDs.

  • Backingdevice has to be a block device (no nfs or other networkfs)

  • raid is possible

There are multiple methods how the cache is populated:

  • wirte to disk and cache

  • write to disk only (writearound)

  • write only to cache

bcache needs to be enabled because its not a default setting.

zypper in bcache-tools

modprobe bcache

reboot?

now there are two files in /sys/fs/bcache/

-register -register_quiet

man make-bcache

to circumvent the process of matching the backing device with the caching device manually its recommended to create them simultaniously.

also make sure you unmounted the partitions you want to associate

umount /dev/sdXn

now we are ready for creation

make-bache -C /dev/sdXn -B /dev/sdXn

-C is caching device

-B is backingdevice obv..

i case theres an error use wipefs -a /dev/sdXn (the caching device) to clear the remaining filesystem

now there is a UUID in /sys/fs/bcache which represents the caching device by its UUID

you can also check in /proc/partitions if you find your bcache device(its a file => cat)

now you can set a filesystem for your newly created bcache device

mkfs -t btrfs -f /dev/bcache0

you can also use ext4 or xfs etc..

to make changes persistent put

/dev/bcache0 /home fstype defaults 1 2

in your fstab

remount your destination e.g mount /home

aaand you are DONE

Read More