OpenEuphoria

The beauty of sqlite

• Posted by ChrisB (moderator) Feb 17, 2012
• 2462 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()