📄 readme_sqlite_tutorial.html
字号:
</p>
<p>
<h3>Logging All Inserts, Updates, and Deletes </h3>
<p></p>
<p>
The script below creates the table examlog and three triggers update_examlog,
insert_examlog, and delete_examlog to record update, inserts, and deletes
made to the exam table. In other words, anytime a change is made to the
exam table, the changes will be recorded in the examlog table, including
the old value and the new value. By the way if you are familiar with MySQL, the
functionality of this log table is similiar to MySQL's binlog. See
<a href=http://voxel.dl.sourceforge.net/sourceforge/souptonuts/README_mysql.txt>
( TIP 2, TIP 24 and TIP 25)</a> if you would like more information on
MySQL's log file.
</p>
<pre>
-- *******************************************************************
-- examLog: Script for creating log table and related triggers
-- Usage:
-- $ sqlite3 examdatabase < examLOG
--
--
-- *******************************************************************
-- *******************************************************************
CREATE TABLE examlog (lkey INTEGER PRIMARY KEY,
ekey INTEGER,
ekeyOLD INTEGER,
fnNEW VARCHAR(15),
fnOLD VARCHAR(15),
lnNEW VARCHAR(30),
lnOLD VARCHAR(30),
examNEW INTEGER,
examOLD INTEGER,
scoreNEW DOUBLE,
scoreOLD DOUBLE,
sqlAction VARCHAR(15),
examtimeEnter DATE,
examtimeUpdate DATE,
timeEnter DATE);
-- Create an update trigger
CREATE TRIGGER update_examlog AFTER UPDATE ON exam
BEGIN
INSERT INTO examlog (ekey,ekeyOLD,fnOLD,fnNEW,lnOLD,
lnNEW,examOLD,examNEW,scoreOLD,
scoreNEW,sqlAction,examtimeEnter,
examtimeUpdate,timeEnter)
values (new.ekey,old.ekey,old.fn,new.fn,old.ln,
new.ln,old.exam, new.exam,old.score,
new.score, 'UPDATE',old.timeEnter,
DATETIME('NOW'),DATETIME('NOW') );
END;
--
-- Also create an insert trigger
-- NOTE AFTER keyword ------v
CREATE TRIGGER insert_examlog AFTER INSERT ON exam
BEGIN
INSERT INTO examlog (ekey,fnNEW,lnNEW,examNEW,scoreNEW,
sqlAction,examtimeEnter,timeEnter)
values (new.ekey,new.fn,new.ln,new.exam,new.score,
'INSERT',new.timeEnter,DATETIME('NOW') );
END;
-- Also create a DELETE trigger
CREATE TRIGGER delete_examlog DELETE ON exam
BEGIN
INSERT INTO examlog (ekey,fnOLD,lnNEW,examOLD,scoreOLD,
sqlAction,timeEnter)
values (old.ekey,old.fn,old.ln,old.exam,old.score,
'DELETE',DATETIME('NOW') );
END;
-- *******************************************************************
-- *******************************************************************
</pre>
<p>
Since the script above has been created in the file examLOG, you can execute
the commands in sqlite3 as shown below. Also shown below is a record insert,
and an update to test these newly created triggers.
</p>
<pre>
$ sqlite3 examdatabase < examLOG
$ sqlite3 examdatabase "insert into exam
(ln,fn,exam,score)
values
('Anderson','Bob',2,80)"
$ sqlite3 examdatabase "update exam set score=82
where
ln='Anderson' and fn='Bob' and exam=2"
</pre>
<p>
Now, by doing the select statement below, you will see that
examlog contains an entry for the insert statement, plus two
updates. Although we only did one update on the command line,
the trigger "insert_exam_timeEnter" performed an update
for the field timeEnter -- this was the trigger defined in
"examScript". On the second update we can see that the
score has been changed. The trigger is working. Any change
made to the table, whether by user interaction or another
trigger is recorded in the examlog.
</p>
<pre>
$ sqlite3 examdatabase "select * from examlog"
1|2||Bob||Anderson||2||80||INSERT|||2004-10-02 15:33:16
2|2|2|Bob|Bob|Anderson|Anderson|2|2|80|80|UPDATE||2004-10-02 15:33:16|2004-10-02 15:33:16
3|2|2|Bob|Bob|Anderson|Anderson|2|2|82|80|UPDATE|2004-10-02 15:33:16|2004-10-02 15:33:26|2004-10-02 15:33:26
</pre>
<p>
Again, pay particular attention to the AFTER keyword. Remember by
default triggers are BEFORE, so you must specify AFTER to insure that
all new values will be available, if your trigger needs to work
with any new values.
</p>
<p><h3>UTC and Localtime</h3><p></p>
<p>
Note, select DATETIME('NOW') returns UTC or Coordinated Universal Time. But
select datetime('now','localtime') returns the current time.
</p>
<pre>
sqlite> select datetime('now');
2004-10-18 23:32:34
sqlite> select datetime('now','localtime');
2004-10-18 19:32:46
</pre>
<p>
There is an advantage to inserting UTC time like
we did with the triggers above, since UTC can
easily be converted to localtime after UTC has
been entered in the table. See the command
below. By inserting UTC, you avoid problems when working
with multiple databases that may not share the same
timezone and or dst settings. By starting with UTC, you
can always obtain the localtime.
<br> (Reference: <a href=http://prdownloads.sourceforge.net/souptonuts/README_Working_With_Time.html?download>Working
with Time</a>)
</p>
<pre>
CONVERTING TO LOCALTIME:
sqlite> select datetime(timeEnter,'localtime') from exam;
</pre>
<p>
<h2> Other Date and Time Commands </h2>
</p>
<p>
If you look in the sqlite3 source file "./src/date.c", you
will see that datetime takes other options. For example,
to get the localtime, plus 3.5 seconds, plus 10 minutes,
you would execute the following command:
</p>
<pre>
sqlite> select datetime('now','localtime','+3.5 seconds','+10 minutes');
2004-11-07 15:42:26
</pre>
<p>
It is also possible to get the weekday where
0 = Sunday, 1 = Monday, 2 = Tuesday ... 6 = Saturday.
</p>
<pre>
sqlite> select datetime('now','localtime','+3.5 seconds','weekday 2');
2004-11-09 15:36:51
</pre>
<p>
The complete list of options, or modifiers as they are called in this
file, are as follows:
</p>
<pre>
NNN days
NNN hours
NNN minutes
NNN.NNNN seconds
NNN months
NNN years
start of month
start of year
start of week
start of day
weekday N
unixepoch
localtime
utc
</pre>
<p>
In addition, there is the "strftime" function, which will
take a timestring, and convert it to the specified format,
with the modifications. Here is the format for this function:
</p>
<pre>
** strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
**
** Return a string described by FORMAT. Conversions as follows:
**
** %d day of month
** %f ** fractional seconds SS.SSS
** %H hour 00-24
** %j day of year 000-366
** %J ** Julian day number
** %m month 01-12
** %M minute 00-59
** %s seconds since 1970-01-01
** %S seconds 00-59
** %w day of week 0-6 sunday==0
** %W week of year 00-53
** %Y year 0000-9999
</pre>
<p>
Below is an example.
</p>
<pre>
sqlite> select strftime("%m-%d-%Y %H:%M:%S %s %w %W",'now','localtime');
11-07-2004 16:23:15 1099844595 0 44
</pre>
<p>
<h2>
ATTACH Command: Build a Virtual Table that
Spans Multiple Tables on Separate Databases.
</h2>
<p>
<p> This is a very powerful concept. As you have seen, sqlite3 works with a local
database file. And within this local database multiple tables can be created.
This section will examine a technique to combine multiple tables, with
the same field layout that exist in separate database files, into a single
virtual table. On this single virtual table, you will see how selects
can be performed. The name "virtual table" will be used. There is no overhead
in copying or moving data. No data gets copied or moved, period. This
is the ideal situation, when working with very large tables. Suppose the
computers on your network record port scans from snort to a local sqlite3
file. Then, provided you have access to the individual database files,
via NFS mount or samba mount, you could virtually combine the tables from
all your computers into one virtual table to perform database queries
in an effort to identify global patterns of attack against your network.
</p>
<p>
This example will be done with the examdatabase, since
we still have the scripts that were used for the exam table.
We can easily create a new database "examdatabase2", along
with a new exam table, by executing the following
script from the bash shell as follows:
</p>
<pre>
$ sqlite3 examdatabase2 < examScript
$ sqlite3 examdatabase2 < examLOG
$ sqlite3 examdatabase2 "insert into exam (ln,fn,exam,score) values ('Carter','Sue',1,89);
insert into exam (ln,fn,exam,score) values ('Carter','Sue',2,100);"
$ sqlite3 examdatabase2 "select * from exam"
1|Sue|Carter|1|89|2004-10-02 16:04:12
2|Sue|Carter|2|100|2004-10-02 16:04:12
</pre>
<p>
To combine the two database files, use the
attach command. The alias for examdatabase
will be e1, and the alias for examdatabase2 will be
e2. The shorter names will come in handy when
the tables are joined with the union clause. Yes, that is
how tables are combined: "union" which
is a standard SQL command.
</p>
<p>
After the "attach" database command is performed, the
".database" command can be used to show the location of the individual
database files. The location follows the alias. See the example below.
</p>
<pre>
$ sqlite3
SQLite version 3.0.8
Enter ".help" for instructions
sqlite> attach database 'examdatabase' as e1;
sqlite> attach database 'examdatabase2' as e2;
sqlite> .database
seq name file
--- --------------- ----------------------------------------------------------
0 main
2 e1 /work/cpearls/src/posted_on_sf/sqlite_examples/sqlite_exam
3 e2 /work/cpearls/src/posted_on_sf/sqlite_examples/sqlite_exam
sqlite>
</pre>
<p>
To select all data from both tables, perform the union
of two select statements as demonstrated below. Note by
add 'e1' and 'e2' to the respective selects, it is possible
to identify which database the returned records are coming
from.
</p>
<pre>
sqlite> select 'e1',* from e1.exam union select 'e2',* from e2.exam;
e1|1|Bob|Anderson|1|75|2004-10-02 15:25:00
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -