SQLite: Tutorial - Revision history

Onnowpurbo at 23:04, 28 January 2021

2021-01-28T23:04:37Z

← Older revision		Revision as of 23:04, 28 January 2021
Line 44:		Line 44:
	You do not have to enter the sqlite3 interactive environment. Instead, you could perform all commands at the shell prompt, which is ideal when running bash scripts and commands in an ssh string. Here is an example of how you would create a simple table from the command prompt:		You do not have to enter the sqlite3 interactive environment. Instead, you could perform all commands at the shell prompt, which is ideal when running bash scripts and commands in an ssh string. Here is an example of how you would create a simple table from the command prompt:

	$ sqlite3 test.db "create table t1 (t1key INTEGER		$ sqlite3 test.db "create table t1 (t1key INTEGER PRIMARY KEY,data TEXT,num double,timeEnter DATE);"
	PRIMARY KEY,data TEXT,num double,timeEnter DATE);"

	After table t1 has been created, data can be inserted as follows:		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);"		$ sqlite3 test.db "insert into t1 (data,num) values ('This is sample data',3);"
	$ sqlite3 test.db "insert into t1 (data,num) values ('More sample data',6);"		$ sqlite3 test.db "insert into t1 (data,num) values ('More sample data',6);"
	$ sqlite3 test.db "insert into t1 (data,num) values ('And a little more',9);"		$ sqlite3 test.db "insert into t1 (data,num) values ('And a little more',9);"

	As expected, doing a select returns the data in the table. Note that the primary key "t1key" auto increments; however, there are no default values for timeEnter. To populate the timeEnter field with the time, an update trigger is needed. Note that you should not use the abbreviation "INT" when working with the PRIMARY KEY. You must use "INTEGER" for the primary key to update.		As expected, doing a select returns the data in the table. Note that the primary key "t1key" auto increments; however, there are no default values for timeEnter. To populate the timeEnter field with the time, an update trigger is needed. Note that you should not use the abbreviation "INT" when working with the PRIMARY KEY. You must use "INTEGER" for the primary key to update.

	$ sqlite3 test.db "select * from t1 limit 2";		$ sqlite3 test.db "select * from t1 limit 2";
	1\|This is sample data\|3\|
	2\|More sample data\|6\|		1\|This is sample data\|3\|
			2\|More sample data\|6\|

	In the statement above, the limit clause is used, and only two rows are displayed. For a quick reference of SQL syntax statements available with SQLite, see the syntax page. There is an offset option for the limit clause. For instance, the third row is equal to the following: "limit 1 offset 2".		In the statement above, the limit clause is used, and only two rows are displayed. For a quick reference of SQL syntax statements available with SQLite, see the syntax page. 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";		$ sqlite3 test.db "select * from t1 order by t1key limit 1 offset 2";
	3\|And a little more\|9\|		3\|And a little more\|9\|

	The ".table" command shows the table names. For a more comprehensive list of tables, triggers, and indexes created in the database, query the master table "sqlite_master", as shown below.		The ".table" command shows the table names. For a more comprehensive list of tables, triggers, and indexes created in the database, query the master table "sqlite_master", as shown below.

	$ sqlite3 test.db ".table"		$ sqlite3 test.db ".table"
	t1		t1

	$ sqlite3 test.db "select * from sqlite_master"		$ sqlite3 test.db "select * from sqlite_master"
	table\|t1\|t1\|2\|CREATE TABLE t1 (t1key INTEGER		table\|t1\|t1\|2\|CREATE TABLE t1 (t1key INTEGER PRIMARY KEY,data TEXT,num double,timeEnter DATE)
	PRIMARY KEY,data TEXT,num double,timeEnter DATE)


	All SQL information and data inserted into a database can be extracted with the ".dump" command. Also, you might want to look for the "~/.sqlite_history" file.		All SQL information and data inserted into a database can be extracted with the ".dump" command. Also, you might want to look for the "~/.sqlite_history" file.

	$ sqlite3 test.db ".dump"		$ sqlite3 test.db ".dump"
	BEGIN TRANSACTION;
	CREATE TABLE t1 (t1key INTEGER		PRAGMA foreign_keys=OFF;
	PRIMARY KEY,data TEXT,num double,timeEnter DATE);		BEGIN TRANSACTION;
	INSERT INTO "t1" VALUES(1, 'This is sample data', 3, NULL);		CREATE TABLE t1 (t1key INTEGER PRIMARY KEY,data TEXT,num double,timeEnter DATE);
	INSERT INTO "t1" VALUES(2, 'More sample data', 6, NULL);		INSERT INTO t1 VALUES(1,'This is sample data',3.0,NULL);
	INSERT INTO "t1" VALUES(3, 'And a little more', 9, NULL);		INSERT INTO t1 VALUES(2,'More sample data',6.0,NULL);
	COMMIT;		INSERT INTO t1 VALUES(3,'And a little more',9.0,NULL);
			COMMIT;


	The contents of the ".dump" can be filtered and piped to another database. Below, table t1 is changed to t2 with the sed command, and it is piped into the test2.db database.		The contents of the ".dump" can be filtered and piped to another database. Below, table t1 is changed to t2 with the sed command, and it is piped into the test2.db database.

Onnowpurbo at 09:55, 28 March 2014

2014-03-28T09:55:34Z

Show changes

Onnowpurbo at 09:51, 28 March 2014

2014-03-28T09:51:32Z

← Older revision		Revision as of 09:51, 28 March 2014
Line 17:		Line 17:
	bagaimana fungsi sign mendefinisikan nilai absolut dan kondisi IF.		bagaimana fungsi sign mendefinisikan nilai absolut dan kondisi IF.

			Meskipun fungsi sign tidak ada di sqlite3, pada dasarnya sangat mudah untuk membuatnya di file "/src/func.c" sehingga fungsi tersebut ada secara permanen untuk semua aplikasi sqlite. Umumnya, fungsi user dibuat di C, Perl, atau C++ yang di dokumentasikan di tulisan ini. sqlite3 mempunyai kemampuan untuk menyimpan "blob", binary data. Contoh program "eatblob.c", membaca file binary apapun besarnya ke dalam memory dan menyimpan data di tempat yang di tentukan oleh user.

	~~Although the sign function currently does not exist in sqlite3, it is very easy to create in the "/src/func~~.~~c" file so that this function will be permanently available to all sqlite applications~~. ~~Normally~~, ~~user functions are created in C, Perl, or C++, which is also documented in this article. sqlite3 has the ability to store "blob", binary data. The sample program in the~~ download~~, "eatblob.c", reads a binary file of any size into memory and stores the data in a user-specified field~~.		Semua contoh dapat di temukan di sqlite_examples.tar.gz, dan sangat di sarankan untuk men-download contoh ini sambil kita membaca tulisan ini.

	~~All examples can be found in sqlite_examples.tar.gz, and I encourage you to download these examples as you read this document.~~

	This tutorial was made with sqlite3 version 3.0.8.		This tutorial was made with sqlite3 version 3.0.8.

Onnowpurbo at 07:36, 28 March 2014

2014-03-28T07:36:50Z

← Older revision		Revision as of 07:36, 28 March 2014
Line 2:		Line 2:


	~~SQLite Tutorial~~		Copyright (c) 2004 by Mike Chirico mchirico@users.sourceforge.net
			This material may be distributed only subject to the terms and conditions set forth in the Open Publication License v1.0, 8 June 1999 or later.
			The latest update of this document .

	~~Copyright (c) 2004 by Mike Chirico mchirico@users.sourceforge.net~~		Updated: Thu Jan 11 17:35:19 EST 2007
	~~This material may be distributed only subject to the terms and conditions set forth in the Open Publication License v1.0, 8 June 1999 or later.~~
	~~The latest update of this document .~~

	~~Updated: Thu Jan 11 17:35:19 EST 2007~~

	~~This article explores the power and simplicity of~~ sqlite3, ~~starting with common~~ commands ~~and~~ triggers. ~~It then covers the attach statement with the~~ union ~~operation~~, ~~introduced in a way that allows multiple tables~~, ~~in separate databases~~, ~~to be combined as one~~ virtual ~~table~~, ~~without the overhead of copying or moving~~ data. ~~Next~~, ~~I demonstrate the simple~~ sign ~~function and the amazingly powerful trick of using this function in~~ SQL ~~select statements to solve complex queries with a single~~ pass ~~through the~~ data, ~~after making a brief mathematical~~ case ~~for how the~~ sign ~~function defines the absolute value and~~ IF ~~conditions~~.		Tulisan ini mengeksplorasi kekuatan dan kesederhanaan sqlite3, mulai dengan commands biasa dan triggers.
			Tulisan ini akan membahas pernyataan penggabungan dengan operasi union, pendahuluan cara untuk membuat banyak tabel, di database yang berbeda,
			yang akan digabungkan sebagai satu virtual tabel, tanpa perlu mengcopy dan memindahkan data.

			Selanjutnya, akan di demonstrasikan fungsi sign sederhana dan trik penggunaan fungsi ini di perintah select SQL untuk
			mengatasi query yang kompleks dengan sekali pass ke data yang ada, setelah menggunakan case matematik sederhana
			bagaimana fungsi sign mendefinisikan nilai absolut dan kondisi IF.


	Although the sign function currently does not exist in sqlite3, it is very easy to create in the "/src/func.c" file so that this function will be permanently available to all sqlite applications. Normally, user functions are created in C, Perl, or C++, which is also documented in this article. sqlite3 has the ability to store "blob", binary data. The sample program in the download, "eatblob.c", reads a binary file of any size into memory and stores the data in a user-specified field.		Although the sign function currently does not exist in sqlite3, it is very easy to create in the "/src/func.c" file so that this function will be permanently available to all sqlite applications. Normally, user functions are created in C, Perl, or C++, which is also documented in this article. sqlite3 has the ability to store "blob", binary data. The sample program in the download, "eatblob.c", reads a binary file of any size into memory and stores the data in a user-specified field.

Onnowpurbo: /* Referensi */

2013-12-29T13:36:36Z

Referensi

← Older revision		Revision as of 13:36, 29 December 2013
Line 1,627:		Line 1,627:

	* http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html		* http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html
			* http://www.tutorialspoint.com/sqlite/
			* http://www.tutorialspoint.com/sqlite/sqlite_quick_guide.htm
			* http://www.tutorialspoint.com/sqlite/sqlite_php.htm

	==Pranala Menarik==		==Pranala Menarik==

Onnowpurbo at 22:11, 12 February 2012

2012-02-12T22:11:09Z

← Older revision		Revision as of 22:11, 12 February 2012
Line 17:		Line 17:

	This tutorial was made with sqlite3 version 3.0.8.		This tutorial was made with sqlite3 version 3.0.8.
	Getting Started
	Common Commands		==Getting Started==

			==Common Commands==

	To create a database file, run the command "sqlite3", followed by the database name. For example, to create the database "test.db", run the sqlite3 command as follows:		To create a database file, run the command "sqlite3", followed by the database name. For example, to create the database "test.db", run the sqlite3 command as follows:
Line 77:		Line 79:
	$ sqlite3 test.db ".dump"\|sed -e s/t1/t2/\|sqlite3 test2.db		$ sqlite3 test.db ".dump"\|sed -e s/t1/t2/\|sqlite3 test2.db

	Triggers		==Triggers==

	An insert trigger is created below in the file "trigger1". The Coordinated Universal Time (UTC) will be entered into the field "timeEnter", and this trigger will fire after a row has been inserted into the table t1.		An insert trigger is created below in the file "trigger1". The Coordinated Universal Time (UTC) will be entered into the field "timeEnter", and this trigger will fire after a row has been inserted into the table t1.
Line 257:		Line 259:


	Other Date and Time Commands		==Other Date and Time Commands==

	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:		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:
Line 451:		Line 453:

	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 is that the IF statement is not standard on all databases, and, on some databases 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 IF statement and how the sign function solves this problem.		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 is that the IF statement is not standard on all databases, and, on some databases 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 IF statement and how the sign function solves this problem.
	The Power of the Sign Function -- A Mathematical Explanation
			==The Power of the Sign Function -- A Mathematical Explanation==

	It may come as a shock, but the problems in the last section, and much more, can be solved using the sign function. This is just the simple function 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 the only number that returns zero. All negative numbers return -1. Again, this simple function does not exist in sqlite, but you can easily create it, permanently. The next section will focus on the creation of this function, but here, the mathematical properties are explained.		It may come as a shock, but the problems in the last section, and much more, can be solved using the sign function. This is just the simple function 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 the only number that returns zero. All negative numbers return -1. Again, this simple function does not exist in sqlite, but you can easily create it, permanently. The next section will focus on the creation of this function, but here, the mathematical properties are explained.
Line 625:		Line 628:


	Modifying the Source: Creating a Permanent Sign Function		==Modifying the Source: Creating a Permanent Sign Function==

	Sqlite functions are defined in "./src/func.c". In this file, the name of this function will be "signFunc". The user will call this function in sqlite as sign(n). It will hold only a single variable.		Sqlite functions are defined in "./src/func.c". In this file, the name of this function will be "signFunc". The user will call this function in sqlite as sign(n). It will hold only a single variable.
Line 707:		Line 710:


	Using the New Sign Function		==Using the New Sign Function==

	Now, back to the problem of creating a pivot table for displaying exam scores in a spreadsheet-like format. First, more data is is needed. By the way, if have not added any data, the following script, enterExamdata, will create the necessary tables and insert the data.		Now, back to the problem of creating a pivot table for displaying exam scores in a spreadsheet-like format. First, more data is is needed. By the way, if have not added any data, the following script, enterExamdata, will create the necessary tables and insert the data.
Line 815:		Line 818:

	Try finding the deltas, or the differences between each exam score. For hints on this, see the end of this article in the LONGWINDED TIPS section.		Try finding the deltas, or the differences between each exam score. For hints on this, see the end of this article in the LONGWINDED TIPS section.
	Pivot Table "Spreadsheet Format" to Normalized Data
			==Pivot Table "Spreadsheet Format" to Normalized Data==

	Consider the reverse: Suppose you had a pivot table, or the data in a spreadsheet-like format, and you wanted a normalized table of exams. For this example, the table nonormal is needed. This table is defined and created as follows:		Consider the reverse: Suppose you had a pivot table, or the data in a spreadsheet-like format, and you wanted a normalized table of exams. For this example, the table nonormal is needed. This table is defined and created as follows:
Line 880:		Line 884:

	For more examples, see this article.		For more examples, see this article.
	Max Min Problems
			==Max Min Problems==

	Assume you have the following table of names, ages, and salaries. Find the age, name, and salary of the youngest person making the overall highest salary, or first find the highest salary, then, from this group, select the youngest person.		Assume you have the following table of names, ages, and salaries. Find the age, name, and salary of the youngest person making the overall highest salary, or first find the highest salary, then, from this group, select the youngest person.
Line 959:		Line 964:

	C and C++ API		C and C++ API
	Simple C Program
			==Simple C Program==

	The following is a simple C program, simplesqlite3.c, which will open a database and execute a SQL string.		The following is a simple C program, simplesqlite3.c, which will open a database and execute a SQL string.
Line 1,034:		Line 1,040:

	There are really only three important statements, sqlite3_open(), which takes the name of the database and a database pointer, sqlite3_exec(), which executes the SQL commands in argv[2] and lists the callback function used to display the results, and sqlite3_close(), which closes the database connection.		There are really only three important statements, sqlite3_open(), which takes the name of the database and a database pointer, sqlite3_exec(), which executes the SQL commands in argv[2] and lists the callback function used to display the results, and sqlite3_close(), which closes the database connection.
	A C++ Program -- Building a Class to Do the Work
			==A C++ Program -- Building a Class to Do the Work==

	It is possible to build a class, SQLITE3 (defined below), which reads the returned data into a vector. Note that instead of using the sqlite3_exec() function, sqlite3_get_table() is used instead. It copies the result of the SQL statement into the variable array of string result. Note this variable must be freed with sqlite3_free_table() after it has been used to copy the returned SQL headings and data into the vectors vcol_head and vdata. Note that the first row is the heading.		It is possible to build a class, SQLITE3 (defined below), which reads the returned data into a vector. Note that instead of using the sqlite3_exec() function, sqlite3_get_table() is used instead. It copies the result of the SQL statement into the variable array of string result. Note this variable must be freed with sqlite3_free_table() after it has been used to copy the returned SQL headings and data into the vectors vcol_head and vdata. Note that the first row is the heading.
Line 1,124:		Line 1,131:
	}		}

	Defining SQLite User Functions		==Defining SQLite User Functions==

	There are two types of functions, aggregate functions and simple functions. Simple functions like sign(), which was created above, can be used in any expression. Aggregate functions like "avg()" can only be used in the select statement. Some functions like "min" and "max" can be defined as both. min() with 1 argument is an aggregate function, whereas min() with an unlimited number of arguments is a simple function.		There are two types of functions, aggregate functions and simple functions. Simple functions like sign(), which was created above, can be used in any expression. Aggregate functions like "avg()" can only be used in the select statement. Some functions like "min" and "max" can be defined as both. min() with 1 argument is an aggregate function, whereas min() with an unlimited number of arguments is a simple function.
Line 1,160:		Line 1,167:
	1		1

	Creating a User-defined Sign Function: msign		==Creating a User-defined Sign Function: msign==

	Below is an example of the sign function. It is called msignFunc so as not to interfere with the permanent sign function that was created earlier.		Below is an example of the sign function. It is called msignFunc so as not to interfere with the permanent sign function that was created earlier.
Line 1,391:		Line 1,398:

	, which takes the index in the list. The first index starts at zero.		, which takes the index in the list. The first index starts at zero.
	Reading Images (Blob data)
			==Reading Images (Blob data)==

	First, a demonstration of how the program eatblob.c works. This program is a C API which inserts binary (blob) data into a table.		First, a demonstration of how the program eatblob.c works. This program is a C API which inserts binary (blob) data into a table.
Line 1,487:		Line 1,495:

	Note that in the above program, buf is a pointer to a pointer, buf is a pointer, and *buf is the first byte of data.		Note that in the above program, buf is a pointer to a pointer, buf is a pointer, and *buf is the first byte of data.
	Perl and sqlite3
			==Perl and sqlite3==

	To use Perl with sqlite3, DBI and DBD::SQLite must be installed. To install the packages from CPAN, use the following commands.		To use Perl with sqlite3, DBI and DBD::SQLite must be installed. To install the packages from CPAN, use the following commands.
Line 1,559:		Line 1,568:
	-d delete last entry		-d delete last entry

	Dirty Tricks		==Dirty Tricks==

	This trick works on sqlite version 3.2.7 and all historical versions. However, it is not guaranteed to work on all future versions. Still, this is an interesting trick.		This trick works on sqlite version 3.2.7 and all historical versions. However, it is not guaranteed to work on all future versions. Still, this is an interesting trick.

Onnowpurbo: New page: Sumber: http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html SQLite Tutorial Copyright (c) 2004 by Mike Chirico mchirico@users.sourceforge.net This material may be distributed ...

2012-02-12T12:24:51Z

Show changes