OpenEuphoria

1. The beauty of sqlite

• Posted by ChrisB (moderator) Feb 17, 2012
• 2460 views

Hi

I've been watching the EDB threads, and had a quick read of that new book on programming euphoria (looks very good by the way), and just thought that sqlite is so much easier to do than all this opening and locking of databases, and parent databases and so on.

To this end, I just chucked this bare minimum telephone database program together in an hour, just to show how EASY it is to use.

I appreciate that EDB is written in euphoria, but surely this isn't the point - its the ease of use that counts. And remember that sqlite has access to almost the complete SQL language, and is capable of some very elegant searches.

Cheers Chris

--using eusqlite, a practical example 
--simple telephone database, no fancy graphics 
--stripped to the bare minimum 
 
include eusqlite3.ew 
include std/filesys.e 
include std/text.e 
 
--database filed enumerators 
constant ID = 1, 
         NAME = 2, 
         TEL = 3 
 
------------------------------------------------------------------------------ 
procedure initialise() 
------------------------------------------------------------------------------ 
sequence cmd, data 
 
--a database is a file 
--a database can contain one or more tables 
--tables are made up of rows of data arranged in colums 
--one piece of data is a field 
 
if file_exists("tel_data.sql") then 
        return 
end if 
 
cmd = "CREATE TABLE Rolo (id INTEGER PRIMARY KEY, name TEXT, tel TEXT)" 
data = sqlite_query_database("tel_data.sql", cmd) 
 
--INTEGER PRIMARY KEY will auto increment 
 
end procedure 
 
------------------------------------------------------------------------------ 
procedure new_entry() 
------------------------------------------------------------------------------ 
sequence name, tel 
sequence cmd, data 
 
name = prompt_string("Name :") 
tel = prompt_string("Tel :") 
 
--note strings are surrounded by single quotes 
cmd = sprintf("INSERT INTO Rolo (name, tel) values (%s, %s)", {"'" & name & "'", "'" & tel & "'"}) 
--uncomment this to see what is sent to sqlite 
--puts(1, cmd & "\n") 
--note same command to query the database 
data = sqlite_query_database("tel_data.sql", cmd) 
 
--there are some returns and error codes from the sqlite3 wrapper - explore it to see other returns 
if sqlite_last_err_no = SQLITE_OK then 
        puts(1, "Entry successful! (press a key)\n") 
else 
        puts(1, "Something went wrong! (press a key)\n") 
end if 
 
wait_key() 
 
end procedure 
 
 
------------------------------------------------------------------------------ 
procedure find_entry() 
------------------------------------------------------------------------------ 
sequence cmd = "", data 
sequence name, tel 
 
name = prompt_string("Name :") 
tel = prompt_string("Tel :") 
 
if length(name) > 0 and length(tel) = 0 then 
        cmd = sprintf("SELECT * FROM Rolo WHERE name LIKE %s", {"'%" & name & "%'" }) 
        --the '%' symbol in an sql statement is a wildcard 
        --it replaces a string with anything 
        --try adding Smith as a name, and searching for 'it' 
        --LIKE is the sql command for equal in strings - you can also use equal 
end if 
 
if length(name) = 0 and length(tel) > 0 then 
        cmd = sprintf("SELECT * FROM Rolo WHERE tel LIKE %s", {"'%" & tel & "%'" }) 
        --see how easy it is to search on a different field 
end if 
 
if length(name) > 0 and length(tel) > 0 then 
        --searches on multiple fields 
        cmd = sprintf("SELECT * FROM Rolo WHERE name LIKE %s AND tel LIKE %s", {"'%" & name & "%'", "'%" & tel & "%'" }) 
end if 
 
if length(cmd) = 0 then return end if 
 
data = sqlite_query_database("tel_data.sql", cmd) 
 
if length(data) > 0 then 
        for i = 2 to length(data) do 
                puts(1, data[i][ID] & ", " & data[i][NAME] & ", " & data[i][TEL] & "\n")              
        end for 
end if 
 
if sqlite_last_err_no = SQLITE_OK then 
        puts(1, "Search successful! (press a key)\n") 
else 
        puts(1, "Something went wrong! (press a key)\n") 
end if 
 
wait_key() 
 
 
end procedure 
 
------------------------------------------------------------------------------ 
procedure list_entries() 
------------------------------------------------------------------------------ 
sequence cmd, data 
 
cmd = "SELECT * FROM Rolo" 
data = sqlite_query_database("tel_data.sql", cmd) 
 
if length(data) = 0 then 
        --a return of an empty dataset - no data found 
        puts(1, "No data!\n") wait_key() 
        return 
end if 
 
for i = 1 to length(data) do 
        --note that the first row of data are the column headers 
        puts(1, data[i][ID] & ", " & data[i][NAME] & ", " & data[i][TEL] & "\n") 
        --note the use of the constants for easy identification of the fields 
end for 
wait_key() 
 
--do what you will with the data  
 
end procedure 
 
------------------------------------------------------------------------------ 
procedure main() 
--everything starts from here 
------------------------------------------------------------------------------ 
integer key 
initialise() 
 
--main_menu 
while 1 do 
        clear_screen() 
        position (1,1) 
        puts(1, "Demo telephone database\n======================\n") 
        puts(1, "1. New entry\n") 
        puts(1, "2. Find entry\n") 
        puts(1, "3. List all entries\n") 
        puts(1, "Q. quit\n\n") 
 
        key = upper(wait_key()) 
 
        if key = 'Q' then exit 
 
        elsif key = '1' then new_entry() 
 
        elsif key = '2' then find_entry() 
 
        elsif key = '3' then list_entries() 
 
        end if 
 
end while 
 
end procedure 
main() 

3. Re: The beauty of sqlite

• Posted by ChrisB (moderator) Feb 18, 2012
• 2325 views

Hi

It hasn't been on RDS for a while

http://eusqlite.wikispaces.com

Chris

5. Re: The beauty of sqlite

• Posted by ChrisB (moderator) Feb 23, 2012
• 2068 views

Hi Greg

EDS is an excellent database system. My post wasn't about how good EDS vs sqlite (in fact I've written some tests that show that EDS is just as fast at some searches as sqlite is on largish tables)

My point is about writing it - what would an equivalent EDS based program to the above example look like?

I am an sqlite fan, and advocate, no doubt, and I'll not say that people shouldn't use EDS, but they should be aware that there is a far simpler data storage and retrieval system available than EDS. (IMHO)

A quick extract from the definitive guide to sqlite

Who Uses SQLite 
Today, SQLite is used in a wide variety of software and products. It is used in Apple’s Mac OS X  
operating system as a part of their CoreData application framework. It is also used in the system’s  
Safari web browser, Mail.app email program, RSS manager, as well as Apple’s Aperture photog- 
raphy software. SQLite can be found in Sun’s Solaris operating environment, specifically the  
database backing the Service Management Facility that debuted with Solaris 10, a core compo- 
nent of its predictive self-healing technology. SQLite is in the Mozilla Project’s mozStorage  
C++/JavaScript API layer, which will be the backbone of personal information storage for  
Firefox, Thunderbird, and Sunbird. SQLite has been added as part of the PHP 5 standard  
library. It also ships as part of Trolltech’s cross-platform Qt C++ application framework, which  
is the foundation of the popular KDE window manager, and many other software applications.  
SQLite is especially popular in embedded platforms. Much of Richard Hipp’s SQLite-related  
business has been porting SQLite to various proprietary embedded platforms. Symbian uses  
SQLite to provide SQL support in the native Symbian OS platform. SQLite is also a core  
component in the new Linux-based Palm OS, targeted for smart phones. It is also included in  
commercial development products for cell phone applications. 
Although it is rarely advertised, SQLite is also used in a variety of consumer products, as  
some tech-savvy consumers have discovered in the course of poking around under the hood.  
Examples include the D-Link Media Lounge, Slim Devices Squeezebox music player, and the  
Philips GoGear personal music player. I recently saw online that some clever consumers found  
a SQLite database embedded in the Complete New Yorker DVD set—a digital library of every  
issue of the New Yorker magazine—apparently used by its accompanying search software. 
You can find SQLite as an alternate back-end storage facility for a wide array of open source  
projects such as Yum—the package manager for Fedora Core, Movable Type, DSPAM, Edgewall  
Software’s excellent Trac SCM and project management system, and KDE’s Amarok audio  
player, to name just a few. Even parts of SQLite’s core utilities can be found in other open source  
projects. One such example is its Lemon parser generator, which the lighttpd web server project  
uses for generating the parser code for reading its configuration file. Indeed there seems to be  
such a variety of uses for SQLite that Google took notice and awarded Richard Hipp with “Best  
Integrator” at O’Reilly’s 2005 Open Source Convention.