Everyday SQL statements

Tools

Status

SHOW status where Variable_name like ‘Th%’ or Variable_name like ‘%Connec%’ ;
SHOW [GLOBAL | SESSION] STATUS [LIKE ‘pattern’ | WHERE expr]

Check/Repair tables

mysqlcheck -u root -p***** –auto-repair –check –optimize –all-databases

Profiling

watch -n 0.5 ‘mysql -u root -ppass -e “SHOW FULL PROCESSLIST” | grep Query’

http://opendba.blogspot.com/2008/03/mysql-finally-ability-to-traceprofile.html

Dump

pg_dump -U test arachnid_archiv_test –inserts -h chaos.spider.bg –encoding=utf8 -f pgsql.sql

Dump for full backup with flushing of the log files

mysqldump -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASS
–single-transaction –all-databases –delete-master-logs –flush-logs –master-data=2
> backup_sunday_1_PM.sql

Encoding problems

http://www.hostbulgaria.com/tutorials/mysql-charset-encoding.aspx

SHOW VARIABLES LIKE ‘character_set_%’;
curl -i http://system3.spider.bg

Creating a database

create database re_production DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

Creating a user

GRANT ALL PRIVILEGES ON arachnid_production.* TO ‘payak’@’%’ IDENTIFIED BY ‘payakpassword’ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON system3_production.* TO ‘payak’@’%’ IDENTIFIED BY ‘payakpassword’ WITH GRANT OPTION;

mysqladmin -u [user] -h localhost -p password ‘[new_password]’

SQL for a table

SHOW CREATE TABLE tblname;

mysql tunel to another machine

ssh -N -f -l root -L 0.0.0.0:3307:91.196.240.132:3306 s1
open port 3307 on the local machine to 91.196.240.132:3306 and login into s1 with root

Replace text

UPDATE script_histories SET cod_script = replace(cod_script,”observer.ArchiveObserver(siteId)”,”observer.ArchiveObserver(siteId, script_id, owned_source_id)”);

Copy from one table to another

DELETE FROM system3_production.articles;
INSERT INTO system3_production.articles SELECT * FROM arachnid_from_screen.articles;

Sessions for Rails

select count(*) from sessions where updated_at < DATE_SUB(now(), INTERVAL 3 DAY);

Binnary loging

http://dev.mysql.com/doc/refman/5.0/en/recovery-from-backups.html

Check this attachment here: mysql-presentation on replication etc.

  • See the status of the log files
    SHOW BINARY LOGS;
    SHOW MASTER STATUS;
  • Clean the binary logs instantly
    RESET MASTER;
  • Clean binary logs to date/name
    PURGE BINARY LOGS TO ”mysqld-bin.00XXXX’;
  • Configurations in my.cnf
    log-bin
    server-id = 1
    expire_logs_days = 1
    max_binlog_size = 100M

Configuration

max_allowed_packet = 50M
wait_timeout=720
max_connections=1000
connect_timeout=20

query_cache_limit=8M #~~~ removed, 1M def. max pozwl. razmer za cache-hirane na edna zajawka
query_cache_size=128M #~~~ 32M, 0 def.
query_cache_type=1

Restoring the maintian Debian User

Dual Monitors with NVidia

aptitude install nvidia-glx-new
aptitude install nvidia-settings

Some options that you can put in the Screen section

/etc/X11/xorg.conf
Option “NoLogo” “True”
Option “RenderAccel” “True”
# This turns on NVidias TwinView
Option “TwinView”
# Option “MetaModes” “1280×1024” “1152×864” “1024×768”
# LeftOf, RightOf, Below, Above, or Clone.
# Option “TwinViewOrientation” “LeftOf”

will_paginate with ajax

It is very easy to handle ajax pagination as described on this post here.

I want to give one full example.

Here is how my form looks:

Here is how my will_paginate helper looks:

I have used the improved version of the will_paginate:

Update for will_paginate 2.3.x

Rails 2.0 scaffolding

The most valuable usage for scaffolding is when you use it in the administration part of the site. The administration is usually separated in separate module/folder.

The new rails scaffolding is not good because:

  • It is time taking to setup nested scaffold
  • I have tons of uselss source code in the scaffolding like respond_to ... that is usable very rare or never.
  • The HTML that is generated is ugly and not usable for CSS skin
  • Not using the nice ruby style iterations .each instead of for's
  • Code repetition, the “new” and the “edit” views has the same form elements
  • Not easy to find documentation

I find Akita article for scaffolding. It is is great that he has covered some real cases. It is a lot of reading and I must read/copy/paste every time I need to make a scaffold. Here is step by step on making scaffold for administration.

  • ruby script/generate scaffold admin::BadArticle NOT PLURAL!
    • user:references
    • description:text,string
    • fixed:boolean
    • timestamps are automaticaly added
  • Add in routes.rb

  • Model
    • Rename admin::BadArticles -> BadArticle,
    • move it to the model directory
    • rename the file
  • Fix the migration file:
    • remove the prefix from the migration class,
    • remove the prefix from table name
    • rename the file
  • URL references
    • admin_bad_articles_url
    • redirect_to([:admin, bad_article])
  • Controller
    • rename all Admin::BadArticles -> BadArticle except in the controller name
    • Probably you will want to make your scaffold inhired your base admin controller



  • Index.rhtml.erb


  • edit.rhtm

  • show.html.erb

  • new.rhtml


Traps:

The order of the routes in routes.rb does matter! In order to make the REST work you must have this block at the bottom of your routes.rb like this:


Remove simply_helpful plugin if you get the error:

Debuging tools:

rake routes

Tips:

How to add custom actions to the REST

Then in the controller:

And in the view:

More reading here

Resources:

Game

Once in the dark DOS ages I have one game with which you can make wonderfull animations. In the “Windows” era I miss this game a lot….until I found this nice replacement.

http://phun.cs.umu.se/wiki/Download

Firefox extensions

Here is the list of the firefox plugins that I use.

Rails code-snipplets

auto_scope

source: http://blog.teksol.info/archives/2007/3

class Contact < ActiveRecord::Base
auto_scope \
:old => {:find => {:conditions => [born_on < ?, 30.years.ago]}},
:young => {:find => {:conditions => [born_on > ?, 1.year.ago]}}
end

class Testimonial < ActiveRecord::Base
auto_scope \
:approved => {
:find => {:conditions => [approved_at < ?, proc {Time.now}]},
:create => {:approved_at => proc {Time.now}}},
:unapproved => {
:find => {:conditions => approved_at IS NULL},
:create => {:approved_at => nil}}
end

Rails internals!?

http://localhost:3000/rails/info/properties

Threads