REVO5500 Book Library @ local sDatabaseID on preOpenStack local tDatabaseFile ## Connect to the SQLite database using the database file path put specialFolderPath("documents") & "/bookLibrary.sqlite" into tDatabaseFile if there is not a file tDatabaseFile then ## If the file does not exist it will be automatically created local tTableSQL ## Add a books table put revOpenDatabase("sqlite",tDatabaseFile) into sDatabaseID put "CREATE TABLE books(BookID INTEGER PRIMARY KEY AUTOINCREMENT, Title varchar(255),Author varchar(255),Status varchar(50),Comments varchar(500))" into tTableSQL revExecuteSQL sDatabaseID,tTableSQL ## Add some inital entries put "INSERT into books(Title,Author,Status) VALUES ('The Lord of the Rings','JRR Tolkien','On Shelf')" into tSQLStatement revExecuteSQL sDatabaseID,tSQLStatement put the result put "INSERT into books(Title,Author,Status) VALUES ('Pride and Prejudice','Jane Austen','On Shelf')" into tSQLStatement revExecuteSQL sDatabaseID,tSQLStatement put "INSERT into books(Title,Author,Status) VALUES ('His Dark Materials','Philip Pullman','On Shelf')" into tSQLStatement revExecuteSQL sDatabaseID,tSQLStatement else ## The database exists so simply connect to it put revOpenDatabase("sqlite",tDatabaseFile) into sDatabaseID end if end preOpenStack ## Add a book to the database command bookAdd pTitle, pAuthor, pStatus, pComments local tSQLStatement ## Construct the SQL statement and execute it on the database put "INSERT into books(Title,Author,Status,Comments) VALUES (" & quote & pTitle & quote & comma & quote & pAuthor & quote & comma & quote & pStatus & quote & comma & quote & pComments & quote & ")" into tSQLStatement revExecuteSQL sDatabaseID,tSQLStatement ## Give some feedback to the user if the result is an integer then answer "Book successfully added." else answer "Sorry, there was a problem adding that book." end if end bookAdd ## Update a book in the database command bookUpdate pBookID, pTitle, pAuthor, pStatus, pComments local tSQLStatement ## Construct the SQL statement and execute it on the database put "UPDATE books SET status='" & pStatus & "', title='" & pTitle & "',author='" & pAuthor & "',comments='" & pComments &"' WHERE BookID = '" & pBookID & "'" into tSQLStatement revExecuteSQL sDatabaseID,tSQLStatement ## Give some feedback to the user if the result is an integer then answer "Book successfully updated." else answer "Sorry, there was a problem updating that book." end if end bookUpdate ## Delete a book from the database command bookDelete pBookID local tSQLStatement ## Construct the SQL statement and execute it on the database put "DELETE from books WHERE BookID = '" & pBookID & "'" into tSQLStatement revExecuteSQL sDatabaseID,tSQLStatement ## Give some feedback to the user if the result is an integer then answer "Book successfully deleted." else answer "Sorry, there was a problem deleting that book." end if end bookDelete ## Retrieve a list of books from the database and return it function booksListAll local tSQLStatement, tList ## Construct the SQL statement and query the database put "SELECT Title,Author,Status,Comments, BookID from books" into tSQLStatement put revDataFromQuery(tab,return,sDatabaseID,tSQLStatement) into tList return tList end booksListAll ## Retrieve a list of books where the Title or Author matches a search term function booksSearch pSearchTerm local tSQLStatement, tList ## Construct the SQL statement and query the database put "SELECT Title,Author,Status,Comments,BookID from books WHERE title LIKE '%" & pSearchTerm & "%' OR author LIKE '%" & pSearchTerm & "%'" into tSQLStatement put revDataFromQuery(tab,return,sDatabaseID,tSQLStatement) into tList return tList end booksSearch on resizeStack send "layoutCard" to card "intro" send "layoutCard" to card "book data" end resizeStack Z U Segoe UI,unicode W Segoe UI W Segoe UI,unicode U Segoe UI W Segoe UI,unicode U Segoe UI,unicode cREVGeneral[AlreadyHidden] AlreadyHidden false #cREVGeneral[scripteditorselection] scripteditorselection 689 !cREVGeneral[scripteditorvscroll] scripteditorvscroll 716 "cREVGeneral[breakpointconditions] breakpointconditions cREVGeneral[breakpointstates] breakpointstates cREVGeneral[breakpoints] breakpoints cREVGeometryCache stackID 1051 cREVGeneral breakpoints breakpointstates scripteditorselection 1781breakpointconditions scripteditorvscroll 0stackfileversion 5.5scalefactor 1 intro POon layoutCard set the width of field "intro" of me to the width of this stack - 20 set the height of field "intro" of me to the height of this stack - 80 set the topLeft of field "intro" of me to 10,10 set the bottomRight of button "Go" of me to the width of this stack - 10, the height of this stack - 10 end layoutCard Y cREVGeneral scripteditorvscroll 0scripteditorselection 0 cREVGeometryCacheIDs 1407506164304 10491407506164422 1050 cREVGeometrycache total 2order book data P on preOpenCard layoutCard ## Clear the search and entry fields put empty into field "Search Term" clearFields ## Populate the list of books listBooks end preOpenCard command clearFields ## Clear the entry fields put empty into field "Title" put empty into field "Author" put empty into field "Comments" put empty into field "BookID" ## Set the Option menu to the default set the label of button "Status" to "On Shelf" end clearFields command editFields ## Make the entry fields editable set the lockText of field "Title" to false set the lockText of field "Author" to false set the lockText of field "Comments" to false set the enabled of button "Status" to true set the showBorder of field "Title" to true set the showBorder of field "Author" to true end editFields command lockFields ## Make the fields read only set the lockText of field "Title" to true set the lockText of field "Author" to true set the lockText of field "Comments" to true set the enabled of button "Status" to false set the showBorder of field "Title" to false set the showBorder of field "Author" to false end lockFields command listBooks local tList put booksListAll() into tList put tList into field "List" end listBooks command searchBooks local tSearchTerm, tList put field "Search Term" into tSearchTerm put booksSearch(tSearchTerm) into tList put tList into field "List" end searchBooks command showBookDetails local tLineNumber, tBookDetails ## Get the hilited line number of the list ## Get the text of that line put the hilitedLine of field "List" into tLineNumber put line tLineNumber of field "List" into tBookDetails ## Set the item delimiter to tab ## This allows us to refer to each tab separated item set the itemDelimiter to tab put item 1 of tBookDetails into field "Title" put item 2 of tBookDetails into field "Author" set the label of button "Status" to item 3 of tBookDetails put item 4 of tBookDetails into field "Comments" ## The BookID is an invisible field, used to identify the book put item 5 of tBookDetails into field "BookID" ## Make the fields non editbale lockFields ## Disable the Cancel and Save buttons set the disabled of button "Cancel" to true set the disabled of button "Save" to true end showBookDetails command saveBook local tTitle, tAuthor, tStatus, tComments, tBookID local tPath ## Retrieve the user input put field "Title" into tTitle put field "Author" into tAuthor put the label of button "Status" into tStatus put field "Comments" into tComments put field "BookID" into tBookID ## Check for return chracters in comments ## Not allowed as our data is line separated ## Replace with a space instead replace return with space in tComments if tBookID is empty then ## This is a new book ## Add the book to the database bookAdd tTitle, tAuthor, tStatus, tComments else ## This is an update ## Only the status can be updated bookUpdate tBookID, tTitle, tAuthor, tStatus, tComments end if ## Update the list of books ## Clear the entry fields lock screen listBooks clearFields unlock screen end saveBook ## Prepare the details pane for a new book entry command newBook lock screen clearFields ## Make the fields editbale editFields ## Enable the Cancel and Save buttons set the enabled of button "Cancel" to true set the enabled of button "Save" to true focus on field "title" unlock screen end newBook ## Prepare the details pane to edit a book entry command editBook lock screen showBookDetails ## Make the fields editbale editFields ## Enable the Cancel and Save buttons set the enabled of button "Cancel" to true set the enabled of button "Save" to true focus on field "title" unlock screen end editBook command deleteBook local tLineNumber, tBookID ## Get the hilited line number of the list ## Get the title of that book set the itemDel to tab put the hilitedLine of field "List" into tLineNumber put item 5 of line tLineNumber of field "List" into tBookID ## Check the user wants to delete the book answer "Are you sure you want to delete this book?" with "OK" or "Cancel" if it is "OK" then ## Delete the selected book bookDelete tBookID ## Refresh the list clearFields listBooks end if end deleteBook command layoutCard ## This handler lays out all controls based on the size of the stack local tLeftPaneWidth, tRightPaneWidth put round(the width of this stack/3) into tLeftPaneWidth put round(the width of this stack/3)*2 into tRightPaneWidth set the height of graphic "verticalDiv" of me to the height of this stack set the topLeft of graphic "verticalDiv" of me to tLeftPaneWidth,0 set the width of field "search term" of me to tLeftPaneWidth - 97 set the topLeft of field "search term" of me to 5,5 set the topLeft of button "search" of me to the right of field "search term" of me + 5, 5 set the width of field "list titles" of me to tLeftPaneWidth - 20 set the left of field "list titles" of me to 10 set the width of field "list" of me to tLeftPaneWidth - 20 set the height of field "list" of me to the height of this stack - 120 set the topLeft of field "list" of me to 10, the bottom of field "list titles" of me + 10 set the topLeft of button "Add Book" of me to 10, the bottom of field "list" of me + 10 set the topLeft of button "Edit Book" of me to the right of button "Add Book" of me + 5, the bottom of field "list" of me + 10 set the topRight of button "delete book" of me to the right of field "list" of me, the bottom of field "list" of me + 10 set the left of field "title label" of me to tLeftPaneWidth set the left of field "author label" of me to tLeftPaneWidth set the left of field "status label" of me to tLeftPaneWidth set the left of field "comments label" of me to tLeftPaneWidth set the left of field "title" of me to the right of field "title label" of me set the left of field "author" of me to the right of field "author label" of me set the left of button "status" of me to the right of field "status label" of me set the width of field "comments" of me to tRightPaneWidth - 120 set the height of field "comments" of me to the height of this stack - 150 set the topLeft of field "comments" of me to the topRight of field "comments label" of me set the topRight of button "save" of me to the right of field "comments" of me,the bottom of field "comments" of me + 10 set the topRight of button "cancel" of me to the topLeft of button "save" of me end layoutCard : #cREVGeneral[scripteditorselection] scripteditorselection 486 !cREVGeneral[scripteditorvscroll] scripteditorvscroll 0 cREVGeneral scripteditorvscroll 1301scripteditorselection 2448 cREVGeometryCacheIDs 1389019605679 10061389020339869 10391389020112623 10301389019713817 10121389020104260 10241389020358353 10421389020321682 10351389020104261 10251407507421440 10511389020109268 10281389019950916 10151404917390902 10471389020084310 10231389019580886 10041389019625224 10071389019988302 10161389020281842 10331389020053991 10181389020066600 1021 cREVGeometrycache total 19order Search Term ipaon returnInField ## This message is sent when the retun key is pressed ## By handling it here we "trap" it meaning there cannot be multiple lines in the field ## Call searchBooks so the list is filtered when return is pressed searchBooks end returnInField on enterInField ## As with return but for enter searchBooks end enterInField cREVGeneral revUniqueID 1389019580886scripteditorvscroll 0scripteditorselection 319 ' Search Ep 'on mouseUp searchBooks end mouseUp R Search book database cREVGeneral scripteditorvscroll 0revUniqueID 1389019605679scripteditorselection 10 verticalDiv K U ~ V V cREVGeneral revUniqueID 1389019625224 List Titles (A z cREVTable numbertabstops 1 leftfieldloc 10viewablecolumns 4scrollbarwidth 20currenthscroll 0topfieldloc 40 cellxspacing 100currentvscroll 0currentxmouseloc 119currentymouseloc 79 viewablerows 2 cellyspacing 16rightfieldloc 331currentview
Title Author Status
bottomfieldloc 61 cREVGeneral revUniqueID 1389019713817 ' T i t l e A u t h o r S t a t u s ' & Add Book ew #on mouseUp newBook end mouseUp Add a new book to the database + #cREVGeneral[scripteditorselection] scripteditorselection 21 !cREVGeneral[scripteditorvscroll] scripteditorvscroll 0 cREVGeneral scripteditorvscroll 0revUniqueID 1389019950916scripteditorselection 25 Delete Book ew &on mouseUp deleteBook end mouseUp - Delete the selected book - #cREVGeneral[scripteditorselection] scripteditorselection 0 !cREVGeneral[scripteditorvscroll] scripteditorvscroll 0 cREVGeneral scripteditorvscroll 0revUniqueID 1389019988302scripteditorselection 23 List )p =on selectionChanged showBookDetails end selectionChanged GA #cREVGeneral[scripteditorselection] scripteditorselection 59 !cREVGeneral[scripteditorvscroll] scripteditorvscroll 0 cREVTable numbertabstops 1 leftfieldloc 10viewablecolumns 5scrollbarwidth 16currenthscroll 0topfieldloc 71 cellxspacing 65currentvscroll 0currentxmouseloc 275currentymouseloc 321 viewablerows 41 cellyspacing 16rightfieldloc 331currentviewThe Lord of the Rings JRR Tolkien On Shelf 1
Pride and Prejudice Jane Austen On Loan 2
His Dark Materials Philip Pullman On Shelf 3
bottomfieldloc 719 cREVGeneral scripteditorvscroll 0revUniqueID 1389020053991scripteditorselection 29 [ T h e L o r d o f t h e R i n g s J R R T o l k i e n O n S h e l f 1 ' Z W P r i d e a n d P r e j u d i c e J a n e A u s t e n O n S h e l f 2 ' V c H i s D a r k M a t e r i a l s P h i l i p P u l l m a n O n S h e l f N o t e 3 ' b ` @ : H w a @ 0 { Title Label U d z cREVTable currentview Title: cREVGeneral revUniqueID 1389020066600 T i t l e : ' Title )@ 8 cREVGeneral revUniqueID 1389020084310 % H i s D a r k M a t e r i a l s ' $ Author Label U % d cREVTable currentview Author: cREVGeneral revUniqueID 1389020104260 A u t h o r : ' Author )@ % _ cREVGeneral revUniqueID 1389020104261 P h i l i p P u l l m a n ' Status Label U F d cREVTable currentview Status: cREVGeneral revUniqueID 1389020109268 S t a t u s : ' Comments Label U d d cREVTable currentview Comments: cREVGeneral revUniqueID 1389020112623 C o m m e n t s : ' Status e F On Shelf On Shelf On Loan cREVGeneral revUniqueID 1389020281842 Comments )` d2j } cREVTable currentviewHis Dark Materials is an epic trilogy of fantasy novels by Philip Pullman consisting of Northern Lights (1995, published as The Golden Compass in North America), The Subtle Knife (1997), and The Amber Spyglass (2000). It follows the coming of age of two children, Lyra Belacqua and Will Parry, as they wander through a series of parallel universes. The three novels have won various awards, most notably the 2001 Whitbread Book of the Year prize, won by The Amber Spyglass. Northern Lights won the Carnegie Medal for children's fiction in the UK in 1995. The trilogy took third place in the BBC's Big Read poll in 2003.
The story involves fantasy elements such as witches and armoured polar bears, and alludes to ideas from physics, philosophy and theology. The trilogy functions in part as a retelling and inversion of John Milton's epic Paradise Lost,[1] with Pullman commending humanity for what Milton saw as its most tragic failing, original sin.[2] The series has drawn criticism for its negative portrayal of Christianity and religion in general.[3]
Pullman's publishers have primarily marketed the series to young adults, but Pullman also intended to speak to both children and older adults.[4] North American printings of The Amber Spyglass have censored passages describing Lyra's incipient sexuality.[5][6]
Pullman has published two short stories related to His Dark Materials: "Lyra and the Birds", which appears with accompanying illustrations in the small hardcover book Lyra's Oxford (2003), and "Once Upon a Time in the North" (2008). He has been working on another, larger companion book to the series, The Book of Dust, for several years.
The National Theatre in London staged a major, two-part adaptation of the series in 2003–2004, and New Line Cinema released a film based on Northern Lights, titled The Golden Compass, in 2007.
cREVGeneral revUniqueID 1389020321682 N o t e ' ` @ e h } Save Mp $on mouseUp saveBook end mouseUp R #cREVGeneral[scripteditorselection] scripteditorselection 18 !cREVGeneral[scripteditorvscroll] scripteditorvscroll 0 cREVGeneral scripteditorvscroll 0revUniqueID 1389020339869scripteditorselection 20 Cancel Ep Uon mouseUp lock screen clearFields lockFields unlock screen end mouseUp G R #cREVGeneral[scripteditorselection] scripteditorselection 84 !cREVGeneral[scripteditorvscroll] scripteditorvscroll 0 cREVGeneral scripteditorvscroll 0revUniqueID 1389020358353 bookID !` v y cREVGeneral revUniqueID 1404917390902 3 ' intro cREVTable currentview %Book Database
This stack demonstrates how to create a simple, SQLite database.
An SQLite database is a local, single file SQL database. This sample stack shows you how to create a database file, add a table, query, add, update and delete the data in the database.
The SQLite file is named "bookLibraryData.sqlite" and is stored in the documents folder. When the stack is opened it attempts to connect to the database. If the database file exists then a connection is established, if not the file is created, a table added and some initial data is popuplated into the database as an example.
You can search within the book data, display details, add, update and delete books. You do need some knowlege of SQL to work with databases within LiveCode but if you are not experienced with SQL there are lots of great resources available.
The database handlers are implemented on the stack scripts and the UI based handlers on the card script. The code is fully commented throughout.
This stack is fully resizable, when the resizeStack message is received all controls are resized and laid out to fit the stack size.
This stack could easily be updated to use a server database such as MySQL or PostgreSQL.
cREVGeneral revUniqueID 1407506164304 B o o k D a t a b a s e # ' T h i s s t a c k d e m o n s t r a t e s h o w t o c r e a t e a s i m p l e , S Q L i t e d a t a b a s e . ' ' q A n S Q L i t e d a t a b a s e i s a l o c a l , s i n g l e f i l e S Q L d a t a b a s e . T h i s s a m p l e s t a c k s h o w s y o u h o w t o c r e a t e a d a t a b a s e f i l e , a d d a t a b l e , q u e r y , a d d , u p d a t e a n d d e l e t e t h e d a t a i n t h e d a t a b a s e . ' p ' T h e S Q L i t e f i l e i s n a m e d " b o o k L i b r a r y D a t a . s q l i t e " a n d i s s t o r e d i n t h e d o c u m e n t s f o l d e r . W h e n t h e s t a c k i s o p e n e d i t a t t e m p t s t o c o n n e c t t o t h e d a t a b a s e . I f t h e d a t a b a s e f i l e e x i s t s t h e n a c o n n e c t i o n i s e s t a b l i s h e d , i f n o t t h e f i l e i s c r e a t e d , a t a b l e a d d e d a n d s o m e i n i t i a l d a t a i s p o p u p l a t e d i n t o t h e d a t a b a s e a s a n e x a m p l e . ' ' Y o u c a n s e a r c h w i t h i n t h e b o o k d a t a , d i s p l a y d e t a i l s , a d d , u p d a t e a n d d e l e t e b o o k s . Y o u d o n e e d s o m e k n o w l e g e o f S Q L t o w o r k w i t h d a t a b a s e s w i t h i n L i v e C o d e b u t i f y o u a r e n o t e x p e r i e n c e d w i t h S Q L t h e r e a r e l o t s o f g r e a t r e s o u r c e s a v a i l a b l e . ' ' ! T h e d a t a b a s e h a n d l e r s a r e i m p l e m e n t e d o n t h e s t a c k s c r i p t s a n d t h e U I b a s e d h a n d l e r s o n t h e c a r d s c r i p t . T h e c o d e i s f u l l y c o m m e n t e d t h r o u g h o u t . ' ' T h i s s t a c k i s f u l l y r e s i z a b l e , w h e n t h e r e s i z e S t a c k m e s s a g e i s r e c e i v e d a l l c o n t r o l s a r e r e s i z e d a n d l a i d o u t t o f i t t h e s t a c k s i z e . ' ' T h i s s t a c k c o u l d e a s i l y b e u p d a t e d t o u s e a s e r v e r d a t a b a s e s u c h a s M y S Q L o r P o s t g r e S Q L . ' Go Eh b / @on mouseUp go to card "Book Data" end mouseUp #cREVGeneral[scripteditorselection] scripteditorselection 0 !cREVGeneral[scripteditorvscroll] scripteditorvscroll 0 cREVGeneral scripteditorvscroll 0revUniqueID 1407506164422 Edit Book ew $on mouseUp editBook end mouseUp - L Add a new book to the database Edit #cREVGeneral[scripteditorselection] scripteditorselection 18 !cREVGeneral[scripteditorvscroll] scripteditorvscroll 0 cREVGeneral scripteditorvscroll 0revUniqueID 1407507421440scripteditorselection 25