CodeSpec: 2008

Friday, October 3, 2008

Backup MySQL Database

Full backup:
echo "============================================================"
echo "Backup $dbName database to $dbFile"

# The following commands will be put to the beginning of the database dump file.
echo " 1. Creating header.tmp temporary file"
echo "SET AUTOCOMMIT = 0;SET FOREIGN_KEY_CHECKS=0;" > $dbDir/header.tmp

# Dumping the database.
echo " 2. Creating $dbName.tmp temporary file"
mysqldump --opt --user=$dbUSER --password=$dbPASSWORD $dbName > $dbDir/$dbName.tmp

# The following commands will be put to the end of the database dump file.
echo " 3. Creating tailer.tmp temporary file"
echo "SET FOREIGN_KEY_CHECKS = 1;COMMIT;SET AUTOCOMMIT = 1;" > $dbDir/tailer.tmp

echo " 4. Create today's backup file : $dbFile"
cat $dbDir/header.tmp $dbDir/$dbName.tmp $dbDir/tailer.tmp > $dbDir/$dbFile

Backup Schema Only:
$ mysqldump --no-data --user=$dbUSER --password=$dbPASSWORD $dbName > Schema20081003.sql

Tuesday, August 26, 2008

Oracle Examples

Change user password:
alter user user_name identified by new_password;

Tuesday, August 19, 2008

Jim Cramer's HOG vs Leonard the Wonder Monkey

How good is it if it's down, Cramerwatch.org? In case you don't know what Cramerwatch.org does, the following is a copy of how they track the performance:

’We record his Lightning Round recommendations as he makes them on TV, and then we have our monkey make recomendations at random on the same stocks. We then wait 30 days, and see who came out on top.‘

Do you really believe their tracking results? If you do, then you'll be a "PIG" or a "HOG". Because Jim Cramer never told us to buy and hold on any stocks for 30 days. He told us to do your homeworks and make your own trading decision on buying more or jumping out of a train.

Cramerwatch.org does not compare Jim Cramer with the Monkey. After Jim Cramer's Lighting Round in his Mad Money show, Cramerwatch.org is doing a comparison between a "PIG"/"HOG" and Leonard the Wonder Monkey.

Thursday, July 31, 2008

vi commands

Switch to command mode <ESC>

ESC	start command mode ( it starts in command mode )
i	change to insert mode
:q	quit
:wq	write and quit
:w	write the file
:#	goto line #
/str	search down for "str"
?str	search up for "str"
dd	delete line
x	delete character
dw	delete word ( cut )
yy	yank line ( copy )
yw	yank word
p	paste
u	undo ( only limit is the last write )
.	repeat last change
cw	change word

Go to line number ## :##

Undo previous command: u

Undo all changes to line: U

Search backward for string: ?string

Search forward for string: /string

% (entire file) s (search and replace) /old text with new/ c (confirm) g (global - all): %s/oldstring/newstring/cg

Delete 5 lines (to buffer): 5dd

Delete lines 5-10:5,10d

string replacement

Sometimes you want to replace one string with another. This is done with the ex command "s". The form of the replacement is :

: line_range s/old/new/g

ex ( replace foo with bar from line 10 to 20 ) :

:10,20s/foo/bar/g

You can use visual mode to select the lines, when you type the colon to start the command it will use symbols for the visual line range.

Wednesday, June 4, 2008

Increment a variable in for-each loop

No can do.

XSLT is a declarative programming language. You can bind a value to a variable only once, you cannot change it later. <link>

Search by Element and print RANum and position()

XML:

<InvoiceAdj>
  <RAInfo RANumType="79">
     <RANum>RANumType_79</RANum>
  </RAInfo>
  <RAInfo RANumType="82">
     <RANum>RANumType_82</RANum>
  </RAInfo>
  <RAInfo RANumType="85">
     <RANum>RANumType_85</RANum>
  </RAInfo>
  <RAInfo RANumType="88">
     <RANum>RANumType_88</RANum>
  </RAInfo>
  <RAInfo RANumType="79">
     <RANum>RANumType_79_1</RANum>
  </RAInfo>
  <RAInfo RANumType="82">
     <RANum>RANumType_82_1</RANum>
  </RAInfo>
  <RAInfo RANumType="85">
     <RANum>RANumType_85_1</RANum>
  </RAInfo>
  <RAInfo RANumType="88">
     <RANum>RANumType_88_1</RANum>
  </RAInfo>
</InvoiceAdj>

XSLT: Search by attribute RANum = 'RANumType_88'

<xsl:for-each select="InvoiceAdj/RAInfo">
<xsl:choose>
  <xsl:when test="RANum = 'RANumType_88'">
    <tr>
      <td><xsl:value-of select="RANum"/></td>
      <td><xsl:value-of select="position()"/></td>
      <td></td>
    </tr>
  </xsl:when>
</xsl:choose>                
</xsl:for-each>

HTML:

<tr>
<td>RANumType_88</td>
<td>4</td>
<td></td>
</tr>

Search by Attribute and print RANum and position()

XML:


<InvoiceAdj>
<RAInfo RANumType="79">
   <RANum>RANumType_79</RANum>
</RAInfo>
<RAInfo RANumType="82">
   <RANum>RANumType_82</RANum>
</RAInfo>
<RAInfo RANumType="85">
   <RANum>RANumType_85</RANum>
</RAInfo>
<RAInfo RANumType="88">
   <RANum>RANumType_88</RANum>
</RAInfo>
<RAInfo RANumType="79">
   <RANum>RANumType_79_1</RANum>
</RAInfo>
<RAInfo RANumType="82">
   <RANum>RANumType_82_1</RANum>
</RAInfo>
<RAInfo RANumType="85">
   <RANum>RANumType_85_1</RANum>
</RAInfo>
<RAInfo RANumType="88">
   <RANum>RANumType_88_1</RANum>
</RAInfo>
</InvoiceAdj>

XSLT: Search by attribute RANumType = 79 or 88


<xsl:for-each select="InvoiceAdj/RAInfo">
<xsl:choose>
<xsl:when test="@RANumType = 79 or @RANumType = 88">
  <tr>
    <td><xsl:value-of select="RANum"/></td>
    <td><xsl:value-of select="position()"/></td>
    <td></td>
  </tr>
</xsl:when>
</xsl:choose>      
</xsl:for-each>

HTML:


<tr>
<td>RANumType_79</td>
<td>1</td>
<td></td>
</tr>
<tr>
<td>RANumType_88</td>
<td>4</td>
<td></td>
</tr>
<tr>
<td>RANumType_79_1</td>
<td>5</td>
<td></td>
</tr>
<tr>
<td>RANumType_88_1</td>
<td>8</td>
<td></td>
</tr>

xsl:for-each sample:


xml: <InvoiceAdj AdjType="CS" AdjNumType="BP">

1. <xsl:for-each select="InvoiceAdj">

2. <xsl:for-each select="InvoiceAdj[@AdjType='CS']">

3. <xsl:for-each select="InvoiceAdj[@AdjType='CS' and @AdjNumType = 'BP']">

Legal filter operators are:

1. =  (equal)
2. != (not equal)
3. &lt; less than
4. &gt; greater than

XSLT Examples

Thursday, March 27, 2008

Select between dates in Oracle SQL

SQL> with t as (select to_date('01/01/2007','dd/mm/yyyy') as update_date from dual union all
 2             select to_date('01/01/2007','dd/mm/yyyy') from dual union all
 3             select to_date('03/01/2007','dd/mm/yyyy') from dual union all
 4             select to_date('03/01/2007','dd/mm/yyyy') from dual union all
 5             select to_date('03/01/2007','dd/mm/yyyy') from dual union all
 6             select to_date('04/01/2007','dd/mm/yyyy') from dual union all
 7             select to_date('05/01/2007','dd/mm/yyyy') from dual union all
 8             select to_date('07/01/2007','dd/mm/yyyy') from dual union all
 9             select to_date('07/01/2007','dd/mm/yyyy') from dual)
10  -- end of test data
11  select to_char(x.UPDATE_DATE, 'YYYY-MM-DD') as update_date, DECODE(t.update_date, NULL, 0, count (*)) as count
12  from (select to_date('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')+(rownum-1) as update_date
13        from   dual
14        connect by rownum <= (to_date('2007-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') -
15                              to_date('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))) x
16        LEFT OUTER JOIN t ON (x.update_date = t.update_date)
17  where x.UPDATE_DATE >= to_date('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
18  and x.UPDATE_DATE < color="navy">'2007-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
19  group by to_char(x.UPDATE_DATE, 'YYYY-MM-DD'), t.update_date
20  order by 1
21  /

UPDATE_DAT      COUNT
---------- ----------
2007-01-01          2
2007-01-02          0
2007-01-03          3
2007-01-04          1
2007-01-05          1

Tuesday, March 11, 2008

Oracle Triggers

CREATE OR REPLACE TRIGGER EBP.INSERT_EBP_TRADING_PARTNERS
BEFORE INSERT
ON EBP.EBP_TRADING_PARTNERS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
v_fax_subject_line VARCHAR2(60);
BEGIN
tmpVar := 0;

SELECT SUBJECT_LINE INTO v_fax_subject_line FROM EBP_BILLERS WHERE BILLER_ID = :NEW.BILLER_ID;

IF :NEW.FAX_SUBJECT IS NULL THEN
:NEW.FAX_SUBJECT := v_fax_subject_line;
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Warning: The Biller_Id does not exist in EBP_BILLERS table.');

END ;
/

CONCAT Strings in Oracle v.s. MySQL

Oracle uses the CONCAT(string1, string2) function or the || operator. The Oracle CONCAT function can only take two strings so the above example would not be possible as there are three strings to be joined (FirstName, ' ' and LastName). To achieve this in Oracle we would need to use the || operator which is equivalent to the + string concatenation operator in SQL Server / Access.

-- Oracle
SELECT FirstName || ' ' || LastName As FullName FROM Customers

MySQL uses the CONCAT(string1, string2, string3...) function. The above example would appear as follows in MySQL

-- MySQL
SELECT CONCAT(FirstName, ' ', LastName) As FullName FROM Customers

Wednesday, January 30, 2008

文昌帝君陰騭文

帝君曰。吾一十七世為士大夫身。未嘗虐民酷吏。救人之難。濟人之急。憫人之孤。容人之過。廣行陰騭。上格蒼穹。人能如我存心。天必錫汝以福。於是訓於人曰。昔于公治獄。大興駟馬之門。竇氏濟人。高折五枝之桂。救蟻中狀元之選。埋蛇享宰相之榮。欲廣福田。須憑心地。行時時之方便。作種種之陰功。利物利人。修善修福。正直代天行化。慈祥為國救民。忠主孝親。敬兄信友。或奉真朝斗。或拜佛念經。報答四恩。廣行三教。濟急如濟涸轍之魚。救危如救密羅之雀。矜弧恤寡。敬老憐貧。措衣食周道路之饑寒。施棺槨免屍骸之暴露。家富提攜親戚。歲饑賑濟鄰朋。斗稱須要公平。不可輕出重入。奴婢待之寬恕。豈宜備責苛求。印造經文。創修寺院。捨藥材以拯疾苦。施茶水以解渴煩。或買物而放生。或持齋而戒殺。舉步常看蟲蟻。禁火莫燒山林。點夜燈以照人行。造河船以濟人渡。勿登山而網禽鳥。勿臨水而毒魚蝦。勿宰耕牛。勿棄字紙。勿謀人之財產。勿妒人之技能。勿淫人之妻女。勿唆人之爭訟。勿壞人之名利。勿破人之婚姻。勿因私讎。使人兄弟不和。勿因小利。使人父子不睦。勿倚權勢而辱善良。勿恃富豪而欺窮困。善人則親近之。助德行於身心。惡人則遠避之。杜災秧於眉睫。常須隱惡揚善。不可口是心非。剪礙道之荊榛。除當途之瓦石。修數百年崎嶇之路。造千萬人來往之橋。垂訓以格人非。捐貲以成人美。作事須循天理。出言要順人心。見先哲於羹牆。慎獨知於衾影。諸惡莫作。眾善奉行。永無惡曜加臨。常有吉神擁護。近報則在自己。遠報則在兒孫。百福駢臻。千祥雲集。豈不從陰騭中得來者哉。

CodeSpec