WHAT DO YOU MEAN YOU WERE ONLY JOKING WHEN YOU SAID "DROP DATABASE *" WAS A REPAIR COMMAND -- [[lol]]


==== REFERENCE ====

  * https://secure.wikimedia.org/wikipedia/en/wiki/SQL
  * http://rzr.online.fr/isbn/2-7440-1184-3 # [[Book]]


==== mysql ====

Emulate [[Oracle]]'s ROWNUM :

<code>
DROP TABLE scores ;

CREATE TABLE scores (
id int(8) NOT NULL AUTO_INCREMENT,
value int(8) DEFAULT 0, 
rank int(8) DEFAULT 0,
PRIMARY KEY (id)
);

INSERT INTO scores 
(id, value, rank) 
VALUES ('1', '10', '0'), ('2', '20', '0');

UPDATE scores
SET value = value+1;

SET @rownum =0; 
UPDATE scores t,
(
SELECT @rownum := @rownum +1 rownum, scores. *
FROM scores
ORDER BY value DESC
) r
SET t.rank = r.rownum 
WHERE ( t.id = r.id);

SELECT * from scores
ORDER BY rank ASC;

SELECT *,@bonus:=CONCAT(rank,value) 
FROM scores
ORDER BY @bonus;

</code>


Misc:

  * http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
  * http://jimmod.com/blog/2008/09/displaying-row-number-rownum-in-mysql/


  mysqldump --add-drop-table --extended-insert -u ${USER} -p ${PASSWORD} ${USER}

  * http://refcards.com/download/bj/mySQL-4.02b.pdf


Howto :

  sudo aptitude install mysql-server
  mysql> create database tmp

  * http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
  * http://dev.mysql.com/doc/mysql/en/cj-faq.html # [[obsolete]]


==== SQLLITE ====

<code>

DROP TABLE lines ;

CREATE TABLE lines (
id int(8) PRIMARY KEY,
line TEXT
);

/* SQL error: lines.id may not be NULL */
INSERT INTO lines (id,line) VALUES ( 1, 'one') ;
INSERT INTO lines (id,line) VALUES ( 2, 'two') ;
INSERT INTO lines  VALUES ( NULL, 'three') ;
INSERT INTO lines (id,line) VALUES ( null, 'four') ;
INSERT INTO lines (line) VALUES ( 'five' ) ;

SELECT COUNT(*) FROM lines;
/* #| 5 */


#  sqlite3 "${file}" ".dump"
</code>

  * http://www.sqlite.org/faq.html#q1 http://www.sqlite.org/autoinc.html
  * http://www.sqlite.org/lang_createtable.html# [[lang]]


<code>
 cd $HOME/.config/f-spot
 file=photos.db  
 sqlite3 "${file}" ".dump" | tee $file.sql
 grep -o  "file://[^\']*" $file.sql | sort | uniq | tee $file.txt

RAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
/**** ERROR: (10) disk I/O error *****/


</code>

@more: [[mono]] [[db]] [[pictures]] [[f-spot]] [[database]]

==== todo ====

  * xml to sql : http://raphaelstolt.blogspot.com/2007/05/transforming-data-centered-xml-into-sql.html


===== mysql =====

"mysqldump: Got error: 1045: Access denied for user"

Checking for corrupt, not cleanly closed and upgrade needing tables..

  * http://packages.qa.debian.org/m/mysql-dfsg-5.1.html

<code>

sudo aptitude reinstall --purge mysql-server-5.0
sudo dpkg-reconfigure --force --priority=low mysql-server-5.0
Stopping MySQL database server: mysqld.
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..

mysqlcheck --repair --all-databases
mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

</code>

  * http://bugs.debian.org/444107
  * http://www.debian-administration.org/articles/442

<code>

mysql --user=root

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.0.51a-17 (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database tmp;

http://dev.mysql.com/doc/refman/5.0/en/adding-users.html
</code>

  * http://mentalaxis.com/ref/mysqlcheatsheet.pdf
  * http://stackoverflow.com/questions/8484722/access-denied-for-user-rootlocalhost-while-attempting-to-grant-privileges


===== SQLITE =====

  * http://f-spot.org/User_Guide

How to replace paths in fspot datatabase :

  sudo apt-get install sqlite3

  sqlite3 -list ~/.config/f-spot/photos.db ".dump" | less

How to export / merge tags :

  * http://ubuntuforums.org/showthread.php?t=726466


===== TSQL : MICROSOFT =====

  * http://www.billyshaw.com/2010/03/14/using-mssql-from-linux/
  * http://stackoverflow.com/questions/175415/how-do-i-get-list-of-all-tables-in-a-database-using-tsql
  * http://stackoverflow.com/questions/124205/how-can-i-do-the-equivalent-of-show-tables-in-t-sql
  * http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=650005
  * http://www.freetds.org/

  sudo apt-get install freetds-bin
  tsql -S "$host" -U "$user" -P "$password" -D "$database"
  SELECT * FROM information_schema.tables
  go


==== misc ====

  * http://gulliver.eu.org/~scollilieux/installation_wikini.txt
  * [[php]] : http://stackoverflow.com/questions/3831513/how-to-do-to-use-different-connections-in-doctrine

{{http://imgs.xkcd.com/comics/exploits_of_a_mom.png}}


==== tags ====

@TaG: [[ToDo]] [[Server]] [[PHP]] [[MySql]] [[Database]] [[db]] [[Query]] [[Language]]  [[db]] [[stucture]] [[query]] [[language]] [[database]] [[data]] [[base]]


{{http://www.addedbytes.com/cheat-sheets/download/mysql-cheat-sheet-v1.png}}