SQLite Tutorialfrom http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html
Copyright (c) 2004 by Mike Chirico mchirico@users.sourceforge.net Updated: Thu Jan 11 17:35:19 EST 2007 This article explores the power and simplicity of sqlite3, starting withcommon commands and triggers. It then covers the attach statement withthe union operation, introduced in a way that allows multiple tables, inseparate databases, to be combined as one virtual table, without theoverhead of copying or moving data. Next, I demonstrate the simple signfunction and the amazingly powerful trick of using this function in SQLselect statements to solve complex queries with a single pass throughthe data, after making a brief mathematical case for how the signfunction defines the absolute value and IF conditions.
Although the sign function currently does not exist in sqlite3, it isvery easy to create in the "/src/func.c" file so that this function willbe permanently available to all sqlite applications. Normally, userfunctions are created in C, Perl, or C++, which is also documented inthis article. sqlite3 has the ability to store "blob", binary data. Thesample program in the download, "eatblob.c", reads a binary file of anysize into memory and stores the data in a user-specified field. All examples can be found in sqlite_examples.tar.gz,and I encourage you to download these examples as you read thisdocument. This tutorial was made with sqlite3 version 3.0.8. Getting StartedCommon CommandsTo create a database file, run the command "sqlite3", followed by thedatabase name. For example, to create the database "test.db", run thesqlite3 command as follows: $ sqlite3 test.db The database file test.db will be created, if it does not already exist.Running this command will leave you in the sqlite3 environment. Thereare three ways to safely exit this environment: .q, .quit, and .exit. You do not have to enter the sqlite3 interactive environment. Instead,you could perform all commands at the shell prompt, which is ideal whenrunning bash scripts and commands in an ssh string. Here is an exampleof how you would create a simple table from the command prompt: $ sqlite3 test.db "create table t1 (t1key INTEGER After table t1 has been created, data can be inserted as follows: $ sqlite3 test.db "insert into t1 (data,num) values ('This is sample data',3);" As expected, doing a $ sqlite3 test.db "select * from t1 limit 2"; In the statement above, the limit clause is used, and only two rows aredisplayed. For a quick reference of SQL syntax statements available withSQLite, see the syntaxpage. There is an offset option for the limit clause. For instance,the third row is equal to the following: "limit 1 offset 2". $ sqlite3 test.db "select * from t1 order by t1key limit 1 offset 2"; The ".table" command shows the table names. For a more comprehensivelist of tables, triggers, and indexes created in the database, query themaster table "sqlite_master", as shown below. $ sqlite3 test.db ".table"
$ sqlite3 test.db "select * from sqlite_master"
All SQL information and data inserted into a database can be extractedwith the ".dump" command. Also, you might want to look for the "~/.sqlite_history" file.
$ sqlite3 test.db ".dump" The contents of the ".dump" can be filtered and piped to anotherdatabase. Below, table t1 is changed to t2 with the sed command,and it is piped into the test2.db database. $ sqlite3 test.db ".dump"|sed -e s/t1/t2/|sqlite3 test2.db TriggersAn insert trigger is created below in the file "trigger1". The CoordinatedUniversal Time (UTC) will be entered into the field "timeEnter", and thistrigger will fire after a row has been inserted into the table t1. -- ******************************************************************** The AFTER specification in ..."insert_t1_timeEnter AFTER..." isnecessary. Without the AFTER keyword, the rowid would not have beengenerated. This is a common source of errors with triggers, since AFTERis not the default, so it must be specified. If your triggerdepends on newly-created data in any of the fields from the created row(which was the case in this example, since we need the rowid), the AFTERspecification is needed. Otherwise, the trigger is a BEFORE trigger, andwill fire before rowid or other pertinent data is entered into thefield. Comments are preceded by "--". If this script were created in the file"trigger1", you could easily execute it as follows. $ sqlite3 test.db < trigger1 Now try entering a new record as before, and you should see the time inthe field timeEnter. $ sqlite3 test.db "insert into t1 (data,num) values ('First entry with timeEnter',19);"
$ sqlite3 test.db "select * from t1"; The last value has timeEnter filled automatically with CoordinatedUniversal Time, or UTC. If you want localtime, use For the examples that follow, the table "exam" and the database"examScript" will be used. The table and trigger are definedbelow. Just like the trigger above, UTC time will be used. -- *******************************************************************
CREATE TRIGGER insert_exam_timeEnter AFTER INSERT ON exam UPDATE exam SET timeEnter = DATETIME('NOW') Here's an example usage: $ sqlite3 examdatabase < examScript
$ sqlite3 examdatabase "select * from exam" 1|Bob|Anderson|1|75|2004-10-02 15:25:00 As you can see, the PRIMARY KEY and current UTC time have been updatedcorrectly. Logging All Inserts, Updates, and DeletesThe script below creates the table examlog and three triggers(update_examlog, insert_examlog, and delete_examlog) to record updates,inserts, and deletes made to the exam table. In other words, whenever achange is made to the exam table, the changes will be recorded in theexamlog table, including the old value and the new value. If you arefamiliar with MySQL, the functionality of this log table is similar toMySQL's binlog. See Tips 2, 24, and 25 if you would like more information on MySQL'slog file. -- *******************************************************************
-- Create an update trigger INSERT INTO examlog (ekey,ekeyOLD,fnOLD,fnNEW,lnOLD, values (new.ekey,old.ekey,old.fn,new.fn,old.ln, END; values (new.ekey,new.fn,new.ln,new.exam,new.score, END; -- Also create a DELETE trigger INSERT INTO examlog (ekey,fnOLD,lnNEW,examOLD,scoreOLD, values (old.ekey,old.fn,old.ln,old.exam,old.score, END; Since the script above has been created in the file examLOG, you canexecute the commands in sqlite3 as shown below. Also shown below is arecord insert, and an update to test these newly-created triggers. $ sqlite3 examdatabase < examLOG
$ sqlite3 examdatabase "insert into exam $ sqlite3 examdatabase "update exam set score=82 Now, by doing the select statement below, you will see that examlogcontains an entry for the insert statement, plus two updates. Althoughwe only did one update on the commandline, the trigger"insert_exam_timeEnter" performed an update for the field timeEnter;this was the trigger defined in "examScript". In the second update, wecan see that the score has been changed. The trigger is working. Anychange made to the table, whether by user interaction or anothertrigger, is recorded in the examlog. $ sqlite3 examdatabase "select * from examlog"
1|2||Bob||Anderson||2||80||INSERT|||2004-10-02 15:33:16 Again, pay particular attention to the AFTER keyword. Remember that bydefault, triggers are BEFORE, so you must specify AFTER to insure thatall new values will be available if your trigger needs to work with anynew values. UTC and LocaltimeNote that sqlite> select datetime('now');
sqlite> select datetime('now','localtime'); There is an advantage to inserting UTC time like we did with thetriggers above, since UTC can easily be converted to localtime after UTChas been entered in the table. See the command below. By inserting UTC,you avoid problems when working with multiple databases that may notshare the same timezone and/or dst settings. By starting with UTC, youcan always obtain the localtime. (Reference: Workingwith Time) CONVERTING TO LOCALTIME:
sqlite> select datetime(timeEnter,'localtime') from exam;
Other Date and Time CommandsIf 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: sqlite> select datetime('now','localtime','+3.5 seconds','+10 minutes'); It is also possible to get the weekday where0 = Sunday, 1 = Monday, 2 = Tuesday ... 6 = Saturday. sqlite> select datetime('now','localtime','+3.5 seconds','weekday 2'); The complete list of options, or modifiers as they are called in thisfile, are as follows: NNN days In addition, there is the "strftime" function, which willtake a timestring, and convert it to the specified format,with the modifications. Here is the format for this function: ** strftime( FORMAT, TIMESTRING, MOD, MOD, ...) Below is an example. sqlite> select strftime("%m-%d-%Y %H:%M:%S %s %w %W",'now','localtime'); The ATTACH Command: Build a Virtual Table that Spans Multiple Tables on Separate DatabasesThis is a very powerful concept. As you have seen, sqlite3 works with alocal database file. Within this local database, multiple tables can becreated. This section will examine a technique to combine multipletables with the same field layout that exist in separate database filesinto a single virtual table. On this single virtual table, you will seehow selects can be performed. There is no overhead in copying or movingdata. No data gets copied or moved, period. This is the ideal situationwhen working with very large tables. Suppose the computers on yournetwork record port scans from snort to a local sqlite3file. Provided you have access to the individual database files, viaNFS mount or samba mount, you could virtually combine the tables fromall your computers into one virtual table to perform database queriesin an effort to identify global patterns of attack against yournetwork. This example will be done with the examdatabase, since we still have thescripts that were used for the exam table. We can easily create a newdatabase "examdatabase2", along with a new exam table, by executing thefollowing script from the bash shell: $ sqlite3 examdatabase2 < examScript
$ sqlite3 examdatabase2 "select * from exam" To combine the two database files, use the After the "attach" database command is performed, the ".database"command can be used to show the location of the individual databasefiles. The location follows the alias. See the example below. $ sqlite3 To select all data from both tables, perform the union of two selectstatements as demonstrated below. Note that by adding 'e1' and 'e2' tothe respective selects, it is possible to identify which database thereturned records are coming from. sqlite> select 'e1',* from e1.exam union select 'e2',* from e2.exam;
e1|1|Bob|Anderson|1|75|2004-10-02 15:25:00 To summarize: A query was performed on two tables that resided inseparate databases. This union created the virtual table. The selectsyntax is as follows: Here is a query example performed on this virtual table. Suppose youwanted the maximum score by exam across databases. sqlite> select exam,max(score) from
1|89 No problem. You got the maximum score for each exam, but who does itbelow to? Find the ln and fn, but be careful; if you add "ln" and "fn"to the first part of the select, you will get the wrong answer. sqlite> select exam,max(score),ln,fn from
** THIS IS INCORRECT; it should be Carter|Sue. ** 1|89|Anderson|Bob "Anderson", "Bob" happens to be the name that dropped down in thisselect statement. It is not the correct answer. If, by chance, you gotthe correct answer by doing this query, it is because you entered thenames in a different order. If that is the case, perform the querybelow, which takes the min(score) and gets an error on one of theseexamples. Here, the min(score) is queried. By chance, because of the order inwhich data was entered into this table, the correct answer isdisplayed. sqlite> select exam,min(score),ln,fn from
** correct answer -- just chance ** 1|75|Anderson|Bob Clearly, there needs to be a better way of finding out who got themaximum and minimum scores for each exam. Here is the correct SQLstatement which will always give the correct answer: sqlite> select db,ln,fn,exam,score from
e2|Carter|Sue|1|89
Or it can be done as two independent select statements as follows: sqlite> select db,ln,fn,exam,score from
e2|Carter|Sue|1|89 sqlite> select db,ln,fn,exam,score from e2|Carter|Sue|2|100 A Pivot TableWhat if you wanted a pivot table in which the scores are listed acrossthe top as exam1,exam2,..examN for each person? For example: fn ln exam1 exam2 Also, is there a way to display the deltas between exams, to have afifth column that would show 7 points (82-75) or the delta between exam1and exam2 and similar data for Sue Carter? Such power select statements can be done with the sign function. Andunlike the case statement, the sign function can be placed in the GROUPBY and HAVING expressions of a SELECT statement. For example, taking alook at the general syntax of the SELECT statement, the sign functioncan be used anywhere you see an expression or expression-list. SELECT [ALL | DISTINCT] result [FROM table-list] The sign function does not exist in sqlite, but that is not a problem,since we can easily create it. As a side note, you may wonder why you should create the sign function.Instead, why not create an IF or IIF function? The main reason isthat the IF statement is not standard on all databases, and, on somedatabases where it is standard (MySQL), it was created incorrectly.Yes, if you are a MySQL user, take a look at the following LONGWINDED TIP 1 for an example of MySQL's incorrect IFstatement and how the sign function solves this problem. The Power of the Sign Function -- A Mathematical ExplanationIt may come as a shock, but the problems in the last section, and muchmore, can be solved using the sign function. This is just the simplefunction in which sign(-200)=-1,.. sign(-1)=-1, sign(0)=0, sign(1)=1,..sign(300)=1. So if the number is > 0 a 1 is returned. Zero is theonly number that returns zero. All negative numbers return -1. Again,this simple function does not exist in sqlite, but you can easily createit, permanently. The next section will focus on the creation of thisfunction, but here, the mathematical properties are explained. The sign function can define the absolute value function abs() as thevalue of a number times its sign, or sign(x)*(x), abbreviatedsign(x)(x). Here is a more detailed look at this function: sign(x)(x) = abs(x)
Example, assume x=3 sign(3)(3) = abs(3) Example, assume x=-3 sign(-3)(-3) = abs(-3) Example, assume x=0 sign(0)(0) = abs(0)
Comparisons can be made with the sign function between two variables xand y. For instance, if sign(x-y) is 1, then, x is greater than y. sign(x-y) is equal to 1 if x > y
sign(x-y) is equal to 0 if x = y sign(x-y) is equal to -1 if x < y
Now look closely at the three statements below. The sign function startsto resemble an IF statement; a 1 is returned if and only if x = y.Thoroughly understanding the statements below is important, as the restof the discussion quickly builds from these examples. IF ( X==Y ) return 1; ELSE return 0;
can be expressed as follows: 1 - abs(sign(x-y)) is equal to 0 if x > y 1 - abs(sign(x-y)) is equal to 1 if x = y 1 - abs(sign(x-y)) is equal to 0 if x < y It is possible to return a 1 if and only if x < y, otherwise return azero. IF ( X < Y ) return 1; ELSE return 0;
can be expressed as follows: 1-sign(1+sign(x-y)) is equal to 0 if x > y 1-sign(1+sign(x-y)) is equal to 0 if x = y 1-sign(1+sign(x-y)) is equal to 1 if x < y The last example is known as the delta for x<y, or Delta[x<y].This Delta notation will be used instead of writing it out in long formor using the IF statement. Therefore, the following is a summarizedtable of all the Delta functions or comparison operators. Delta[x=y] = 1 - abs(sign(x-y))
Delta[x!=y] = abs(sign(x-y)) Delta[x<y] = 1-sign(1+sign(x-y)) Delta[x<=y] = sign(1-sign(x-y)) Delta[x>y] = 1-sign(1-sign(x-y)) Delta[x>=y] = sign(1+sign(x-y))
Delta[z=x OR z=y] = sign( Delta[z=x]+Delta[z=y] ) Delta[z>x AND z<y] = sign( Delta[z>x]*Delta[z<y] ) ... more can be defined ... but you get the idea To summarize the following if statement, note the introduction of athird variable, z: if( x==y ) The above expression, in Delta notation, is the following: z*Delta[x=y] Here is an interesting example: create table logic (value int);
insert into logic (value) values (1);
First, take the Cartesian product to show all possible combinations ofx and y. sqlite> .header on
x.value y.value After the sign function is created (which we will do in the nextsection), using the above table, we could examine Delta[x!=y] asfollows; sqlite> .header on
Note that every time x is not equal to y, abs(sign(x.value-y.value))returns a 1. After the sign function is created, these example will run.This is extremely powerful. To show that we have created a conditionstatement without using the where or group by statements, consider thefollowing example. z.value will only be displayed in the right handcolumn when x.value != y.value. sqlite> select x.value,y.value,z.value,
x.value y.value z.value z.value*abs(sign(x.value-y.value))
Modifying the Source: Creating a Permanent Sign FunctionSqlite functions are defined in "./src/func.c". In this file, the name ofthis function will be "signFunc". The user will call this function insqlite as sign(n). It will hold only a single variable. It is helpful to model the sign function after the abs function"absFunc", since they are very similar. If fact, I would highlyrecommend looking at the abs function any time a new version of sqliteis released. You will want to follow these steps: First, copy the abs function"absFunc" and make the following changes:
For a closer look, below is the section that changed. Look here for thecomplete file: func.c. From ./src/func.c:
... cut ... iVal = ( iVal > 0) ? 1: ( iVal < 0 ) ? -1: 0; double rVal = sqlite3_value_double(argv[0]); } aFuncs[] = {
Using the New Sign FunctionNow, back to the problem of creating a pivot table for displaying examscores in a spreadsheet-like format. First, more data is is needed. Bythe way, if have not added any data, the following script, enterExamdata,will create the necessary tables and insert the data. $ sqlite3 examdatabase "insert into exam (ln,fn,exam,score) values ('Anderson','Bob',3,92)" Below is the select statement for generating a pivot table for fourexams on the table exams. select ln,fn, Below is the select statement, like the statement above. However, itworks on the virtual table, or the combined exam tables from thedatabases examdatabase and examdatabase2. $ sqlite3
ln fn exam1 exam2 exam3 exam4 sqlite> Taking a closer look at the results, it's very easy to see thatAnderson, Bob got 75 on the first exam, 82 on the second, 92 on thethird, and 95 on the forth. Likewise, Stoppard received 88, 90, 92, and95, respectively. ln fn exam1 exam2 exam3 exam4 Now back to the question of finding the top scores for each exam in oneselect statement. That is, finding the top scores for the combined tables.First, a look at all the data: $ sqlite3 Below, continuing with the same attached setup, is an example ofhorizontal averages and horizontal maximum values. sqlite> .headers on
Try finding the deltas, or the differences between each exam score. Forhints on this, see the end of this article in the LONGWINDED TIPS section. Pivot Table "Spreadsheet Format" to Normalized DataConsider the reverse: Suppose you had a pivot table, or the data in aspreadsheet-like format, and you wanted a normalized table of exams.For this example, the table nonormal is needed. This table is defined andcreated as follows: SQLite version 3.0.8
sqlite> .header on The nonormal table was created in the examdatabase, since "e1." wasgiven before the name. Again, the objective here is to go backwards andcreate a normalized table from the pivot table, a table that will listall exam scores in one field and all the exam numbers in another,without having a separate field for each exam. In addition, the goal isto do all this in one select statement without looping through the data.First, it is necessary to create a number table, "enum", and it musthave the field "e" from 1..N where N is the number of exams (which isfour in this case). sqlite> CREATE TABLE enum (e int); The coalesce function is used in an interesting way for this example. sqlite> .mode list
1*(1-abs(sign(e.e-1)))+ coalesce(0/(e.e-1),n.exam1)+ from enum as e,e1.nonormal as n;
For more examples, see thisarticle. Max Min ProblemsAssume you have the following table of names, ages, and salaries. Findthe age, name, and salary of the youngest person making the overallhighest salary, or first find the highest salary, then, from this group,select the youngest person. create table salary (name varchar(3),age int, salary double); Once you have the data entered, you will have the following; sqlite> .headers on The following select will give you the youngest person making the topsalary in the company: sqlite> select 1000-max(salary*1000-age)%1000 from salary;
1000-max(salary*1000-age)%1000 This is the correct answer. The highest salary is 94 for Bob and Tom.Tom is the youngest at 24. Why the number 1000? Well, no one lives to be 1000, so we knowthat age will never be >= 1000. Therefore, max(salary*1000-age) willclearly choose the highest salary independent of age, as long as salaryis >= 1. In cases of a tie in salary, the youngest person willsubtract the least amount from the salary, so this value will returnas the highest. It's easy to remove the salary part from thisnumber. Since salary is multiplied by 1000, it will disappear with mod1000, since it's a perfect factor of 1000. To understand how this works, it is helpful to break the statement intoseparate, smaller parts, as follows: sqlite> select salary*1000-age,salary*1000,-age from salary; But what about the negative value for age? With the non-Knuth method ofthe mod function, "%", when x < 0, then x % y will return x, ifabs(x) < abs(y). x % y is defined as follows: x % y == x - INT(x/y)*y and undefined for y == 0. C and Fortran use this method. In contrast, the Knuth method, found in Python and accepted inmathematics, defines this function as follows: x mod y == x - floor(x/y), and equal to x if y == 0 The difference between the two shows up with negative values for x. Or, put another way, as long as -x != y, then -x % y = -x. For example,assume x=4 and y=5, then -4 % 5 will return a -4. Here are a few otherexamples. Again this is not the Knuth method for the modfunction. -1 % 5 = -1 So what we are really doing is the following: 1000 + -1*(1000-age) = age C and C++ APISimple C ProgramThe following is a simple C program, simplesqlite3.c,which will open a database and execute a SQL string. #include <stdio.h>
int main(int argc, char **argv){ if( argc!=3 ){ The command to compile and run the program is shown below. Note the"-Wl,-R/usr/local/lib" options, which will be needed if you installedthe sqlite3 source, since the path "/usr/local/lib" may not be listed inyour "/etc/ld.so.conf" file. gcc -o simplesqlite3 simplesqlite3.c -Wall -W -O2 -Wl,-R/usr/local/lib -lsqlite3 You either have to use the compile option above or add the directorywhere the sqlite3 library "libsqlite3.so" is installed to the file"/etc/ld.so.conf", then run $ locate libsqlite3.so
$ echo "/usr/local/lib" >> /etc/ld.so.conf After you have entered and compiled the program, it will run as follows: $ ./simplesqlite3 test.db "create table notes (t text)"
$ ./simplesqlite3 test.db "insert into notes (t) values (' $ ./simplesqlite3 test.db "select * from notes" t = There are really only three important statements, A C++ Program -- Building a Class to Do the WorkIt is possible to build a class, SQLITE3 (defined below), which readsthe returned data into a vector. Note that instead of using the class SQLITE3 {
public: std::vector<std::string> vcol_head; SQLITE3 (std::string tablename="init.db"): zErrMsg(0), rc(0),db_open(0) { int exe(std::string s_exe) { if(vcol_head.size()<0) { vcol_head.clear(); }
~SQLITE3(){
The complete program can be found in thisexampleor viewed here: simplesqlite3cpp2.cc. With the class defined above, it can be used in std::string s;
s="select t from notes"; The following, still assuming this code will be entered in main or afunction, is an example of printing the data from a select. Note theheadings section and the data sections. if( sql.vcol_head.size() > 0 )
std::cout << std::endl << std::endl; std::cout << std::endl; Defining SQLite User FunctionsThere are two types of functions, aggregate functions and simplefunctions. Simple functions like sign(), which was created above, canbe used in any expression. Aggregate functions like "avg()" can only beused in the select statement. Some functions like "min" and "max" can bedefined as both. min() with 1 argument is an aggregate function, whereasmin() with an unlimited number of arguments is a simple function. Here is an example which illustrates the difference: $ sqlite3
sqlite> select * from junk where a=min(1,2,3,4,5); Note above that the min() function, with only one variable, is anaggregate function. Since it is only an aggregate function, it cannot beused after the sqlite> select min(a) from junk If you add a second argument, you're calling the simple function. Notebelow that each row is compared to 2.3. Look closely; there is a subtlebut important difference here. sqlite> select min(a,2.3) from junk Creating a User-defined Sign Function: msignBelow is an example of the sign function. It is called msignFunc soas not to interfere with the permanent sign function that was createdearlier. void msignFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
This function is initiated as follows: sqlite3_create_function(db, "msign", 1, SQLITE_UTF8, NULL, Note that "msign" is the name of the function in sqlite3. It is the nameyou would use in a select statement: "select msign(3);". The 1 is thenumber of arguments. The msign function here only takes one argument.SQLITE_UTF8 is for the text representation. Then, skipping over NULL,&msignFunc is the name of the C function. The last two values mustbe NULL for a simple function; again, a simple function can be used inany part of the select Aggregate FunctionsA good place to look for ideas on creating functions is the ./src/func.cfile in the sqlite3 source. Suppose you would like to create a new sumfunction call S. It will create the aggregate sum of the rows. The following data is used to explain this function. $ ./myfuncpp DATABASE "create table t(a integer, b integer, c integer)" Now for how the function S will create a list of the sums. Unlike thestandard aggregate sum() function, a list is returned. $ ./myfuncpp DATABASE "select S(a),S(b),S(c) from t" Note that a column which contains the values in the table (1,2,3,4,5)shows the cumulative sum (1,1+2=3,1+2+3=6,1+..) in a list. This isdifferent from any function defined in ./src/func.c, since the data mustbe in a string. To view this example and all other examples, see the download. Since a list is returned, this example will use the C++std::stringstream, since this is fast and well suited for all types ofdata, integer, double, and text. Since S is an aggregate function, there are two functions, "SStep" and"SFinalize". Aggregate functions always have a "Step" and a "Finalize"."Step" is called for each row, and after the last row, the "Finalize"function is called. Both the Step and the Finalize can make use of a structure for holdingthe cumulated data collected from each row. For this function, thestructure SCtx is defined below. std::stringstream is global. I wouldnot advise putting an additional variable in SCtx, "char *ss". You maythink that this could be dynamically increased with realloc, whichwill work. However, the problem is freeing the memory hanging off thestructure. There's a bit of confusion here. As the sqlite documentationcorrectly points out, the structure SCtx will be freed; but again, in mytesting, any additional memory allocated off members in the structurewill not. On the other hand, an array of std:stringstrings "BS" willhave to be kept for when this function is called more than once in thesame select "select S(a),S(b),...S(100th)" The overhead appears minimal. #define MAXSSC 100
typedef struct SCtx SCtx; std::stringstream ss[MAXSSC]; Below is the step function. p gets initialized the first time throughSStep. On the first pass, all the values in the SCtx structurewill be zeroed. This is a feature of "sqlite3_aggregate_context".Since std::stringstream s0 is defined as a global variable, carewill have to be taken to ensure that when S is called in the sameselect " static void SStep(sqlite3_context *context, int argc, sqlite3_value **argv){
std::string d; } p->sum += sqlite3_value_double(argv[0]);
} }
The line: p = (SCtx *) sqlite3_aggregate_context(context, sizeof(*p)); will initialize p->sum, p->cnt, and p->sscnt to zero on thefirst entry into this function. On each successive entry, the old valuesbe passed back. Although the std:stringstream ss variable is global,S(a) called in the select uses ss[0], S(b) will use ss[1], etc. Also note the comment "If the simple function is not used, thiscomes into play". Below that statement, i walks through the argumentcount. It is possible to have a function name "S", in this case definedas both an aggregate function and a simple function. The distinction ismade with the number of arguments in the calling function. This is setin sqlite3_create_function. For example, a name could be assigned to asimple function and an aggregate function. Normally, this is set up sothat the simple function takes two or more arguments max(1,2,3,4,5) andthe aggregate function just takes one argument max(a). Take a look at Here is the Finalize function: static void SFinalize(sqlite3_context *context){
ss[p->sscnt] << ")";
snprintf(buf,ss[p->sscnt].str().size(),"%s",ss[p->sscnt].str().c_str()); } After all the rows in The user functions "SStep" and "SFinalize" need to be added to the SQLlanguage interpreter. This is done with the "sqlite3_create_function": if (sqlite3_create_function(db, "S", 1, SQLITE_UTF8, NULL, NULL, &SStep,
Note the 1 for the third argument. This aggregate function is used whenone argument is passed. To have it both ways, to have "S" defined asboth an aggregate and a simple function, an SFunc would have to becreated. That could handle 2 to N variables. Once this function iscreated, the additional "sqlite3_create_function" would be defined in ... still in main Here is an example SFunc function: static void SFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
s.str(""); s << "("; } s << ")"; }
Now, S works as both a simple function and an aggregate function. Thesimple function can go in any expression, but the aggregate only worksafter the select. Hence, this goes back to the power of the signfunction, which is a simple function. ./myfuncpp DATABASE 'select S(1,2,3,4)' For a few more examples, take a look at myfuncpp.cc in the download.There are some interesting functions there. For instance, there is an Ior index function that works as follows: $ ./myfuncpp DATABASE "select S(1,2,3)"
$ ./myfuncpp DATABASE "select I(S(1,2,3),0)" $ ./myfuncpp DATABASE "select I(S(1,2,3),1)" , which takes the index in the list. The first index starts at zero. Reading Images (Blob data)First, a demonstration of how the program eatblob.cworks. This program is a C API which inserts binary (blob) data into atable. The program can be run in two ways. First, script commands can beredirected into it. For example, you can create the following scriptfile "sqlcommands": $ cat sqlcommands
create table blobtest (des varchar(80),b blob); Note the "?" on the line " $ ./eatblob test3.db test.png < sqlcommands The image file "test.png" will be read into the program and insertedinto the field b, since this is where the question mark is placed. The program also works interactively, as follows: $ ./eatblob test.db test.png The blob data is not shown. Instead, it is written to the fileoutdata.n.png, where n is the record number. Examining the C code in eatblob.cThe program works by reading all of the binary data from the filenamegiven as the third argument to the command. The complete file is readinto memory. One way to do this is to get the total file size first,then allocate that amount of memory with malloc. That approach is nottaken here. Instead, a more general approach is used. For instance,if you were to read data from a socket, you may not know beforehandhow big the file will be. This general approach will take advantageof the #define MULT_START 16384
long memindx = MULT_START; 1 long addmem(char **buf, long size) ... One line 3, the first time this function is called, the variable size isthe current number of bytes allocated. If no memory has been allocated(size is 0), the new size will be INIT_SIZE. For this program, INIT_SIZEis set to 1024 in the define statement. However, if size is greater thanzero, the new size will be the initial size plus memindx, which startsat 1024. Call memidx Number Returned size As you can see, the number returned increases exponentially. "memindx"is doubled each time this function is called. This doubled value isadded to the size. 1 long addmem(char **buf, long size)
... So, the function gives us a number that we can pass to realloc. realloc works as follows: If realloc is successful, it will copy thecontents pointed to by buf to a location of memory with the larger sizememnext, then free the old region of memory. This new region of memorywill be assigned to tbuf. Since the old location (the location pointedto by *buf) has been released, we need to assign the new value to *buf (*buf = tbuf). If realloc cannot get the new size memnext, *buf is left untouched andtbuf will be null. Note that in the above program, buf is a pointer to a pointer, *buf is apointer, and **buf is the first byte of data. Perl and sqlite3To use Perl with sqlite3, DBI and DBD::SQLite must be installed. Toinstall the packages from CPAN, use the following commands. # perl -MCPAN -e shell The following program will create a database and enter records: #!/usr/bin/perl
use DBI; $dbh = DBI->connect( "dbi:SQLite:data.dbl" ) || die "Cannot connect: $DBI::errstr"; $dbh->do( "CREATE TABLE authors ( lastname, firstname )" );
foreach( @$res ) { } $dbh->disconnect;
For a more elaborate Perl example that defines functions, see perlExample.plin the download. Also, consider using the Perl Debugger, for stepping through complex Perl sqlite programswhere you are not sure of what is returned. To get into the Perl debugger, execute thefollowing command, and to get out of the Perl Debugger type "q".
$ perl -de 42
A Simple Everyday Application -- Keeping Notes in a DatabaseThis simple bash script allows you to take notes. The notes consist of aline of text followed by an optional category. It doesn't require youto type $ n 'Take a look at sqlite3 transactions - This enters the text into a notes table under the category "sqlite3".Whenever a second field appears, it is considered the category. Toextract records for the day, enter "n -l" (which is similar to "l -l")to "note list". With just "n", help is listed for all the commands. $ n
n <option>
Dirty TricksThis trick works on sqlite version 3.2.7 and all historical versions. However, it isnot guaranteed to work on all future versions. Still, this is an interesting trick. Suppose you have two similar table structures and you want to update missing rows.This is easier to show with an example.
create table t0 (a int);
create table t1 (a int); Above when table t0 is compared to t1 values 1 and 3 are missing.It is possible to update these these rows as follows:
insert into t0 (a) select t1.a from t1 left join t0 on t0.a=t1.a where t0.a is NULL order by +t0.rowid; Note above the plus sign in "+t0.rowid" so that you do not get row lockingerrors.
References
|
|
Other Tutorials
Linux System Admin Tips: There are over 200 Linux tips and tricks in this article. That is over 150 pages covering topics from settingand keeping the correct time on your computer, permanently deleting documents with shred, making files "immutable" so that root cannot change or delete, setting up more than one IP address on a single NIC, monitering users and processes, setting log rotate to monthly with 12 months of backups in compressed format, creating passwords for Apache using the htpasswd command, common Perl commands, using cfengine, addingusers to groups, finding out which commands are aliased, query program text segment size and data segmentsize, trusted X11 forwarding, getting information on the hard drive including the current temperature,using Gnuplot, POVRAY and making animated GIFs, monitoring selective traffic with tcpdump and netstat, multiple examplesusing the find command, getting the most from Bash, plus a lot more. You can also down this articleas a text document here foreasy grepping.
Linux Quota Tutorial: This tutorial walks you through implementing disk quotas for both users and groups on Linux, using a virtual filesystem, which is a filesystem created from a disk file. Since quotas work on a per-filesystem basis, this is a way to implement quotas on a sub-section, or even multiple subsections of your drive, without reformatting. This tutorial also covers quotactl, or quota's C interface, by way of an example program that can store disk usage in a SQLite database for monitoring data usage over time.
Breaking Firewalls with OpenSSH and PuTTY: If the system administrator deliberately filters out all traffic except port 22 (ssh), to a single server, it is very likely that you can still gain access other computers behind the firewall. This article shows how remote Linux and Windows users can gain access to firewalled samba, mail, and http servers. In essence, it shows how openSSH and Putty can be used as a VPN solution for your home or workplace. Create a Live Linux CD - BusyBox and OpenSSH Included : These steps will show you how to create a functioning Linux system, with the latest 2.6 kernel compiled from source, and how to integrate the BusyBox utilities including the installation of DHCP. Plus, how to compile in the OpenSSH package on this CD based system. On system boot-up a filesystem will be created and the contents from the CD will be uncompressed and completely loaded into RAM -- the CD could be removed at this point for boot-up on a second computer. The remaining functioning system will have full ssh capabilities. You can take over any PC assuming, of course, you have configured the kernel with the appropriate drivers and the PC can boot from a CD. This tutorial steps you through the whole processes.
The Lemon Parser Tutorial: This article explains how to build grammars and programs using the lemon parser, which is faster than yacc. And, unlike yacc, it is thread safe. How to Compile the 2.6 kernel for Red Hat 9 and 8.0 and get Fedora Updates: This is a step by step tutorial on how to compile the 2.6 kernel from source. Virtual Filesystem: Building A Linux Filesystem From An Ordinary File. You can take a disk file, format it as ext2, ext3, or reiser filesystem and then mount it, just like a physical drive. Yes, it then possible to read and write files to this newly mounted device. You can also copy the complete filesystem, sinc\ e it is just a file, to another computer. If security is an issue, read on. This article will show you how to encrypt the filesystem, and mount it with ACL (Access Control Lists), which give you rights beyond the traditional read (r) write (w) and execute (x) for the 3 user groups file, owner and other. Working With Time: What? There are 61 seconds in a minute? We can go back in time? We still tell time by the sun?
|
Mike Chirico, a father of triplets (all girls) lives outside ofPhiladelphia, PA, USA. He has worked with Linux since 1996, has a Mastersin Computer Science and Mathematics from Villanova University, and hasworked in computer-related jobs from Wall Street to the University ofPennsylvania. His hero is Paul Erdos, a brilliant number theorist who wasknown for his open collaboration with others.
Mike's notes page is souptonuts. Foropen source consulting needs, please send an email to mchirico@gmail.com. All consulting work must include a donation to SourceForge.net.
802
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
