A CD Database for the Pocket PC

1. A CD Database for the Pocket PC Matthew Wenner CS 8628, Summer 2003

2. ER Diagram and Schema Created Table definition (DDL) including indexes, etc. Made the physical database Created Ultralite schema with Painter Wrote publication scripts in .usm file and in consolidated database Created new MobileVB project and associated with the .usm file Wrote application code (Using generic synchronization scripts) Once the application was running with vanilla scripts, added unique synchronization scripts. Once everything was working, deployed to the PocketPC! This provides a look at the big picture of the sequence of steps I used to create my project. Of these steps, writing the application code took the greatest amount of time. Second would be creating the database definitions. A well designed database can make or break any project. The same is true for the Ultralite Schema with Painter. Serious consideration must be given to which columns can be left out if any in the painter schema, and making sure that indexes are properly labeled and that all objects are labeled in such a way as to make communication between the remote and the consolidated database easier. Sequence of Tasks

3. CD Database Intended to be a complete method for tracking a CD library The final program maintains a list of CD names and the artist(s) that made them. Final project includes only two of the six tables in the design Artists can be associated with many CD’s This program was originally conceived as a database to maintain a complete CD database, including lists of artist, labels, CD’s to purchase in the future, comments on CD’s and individual CD tracks. Due to time limitations, only a small subset of that original idea was completed. The original project included over 15 tables. It was then scaled down to 6 tables which are shown in the ER diagram on the next slide and in the schema, and then down to the two tables actually used to make the application. The application at this point only allows entry of the cd title and the artist that made it. Project Description

4. This diagram shows the relationship between different entities within this project’s scope. The CD tables is the central table that brings the others together. With the exception of the song table, all other tables have only one entry per line in the CD table. Since a song can be associated with more than one CD, it will be necessary to create an intermediate table. The song table was designed this way because for example, there are many different recordings of Beethovens 5th symphony, and it has the same title in each recording. In order to keep the database size down, this structure makes the most sense. E-R Diagram aid aname lid lname Produced By Artist Label 1, 1 1, 1 Has cdid 1, N 1, N CD cdname 1, N 1, N 1, 1 Has Contains Song Genre 1, N gname gid stitle sid length

5. This is the logical schema for the entire project which corresponds with the ER diagram on the previous slide. The completed application only uses the artist and cd tables. In this diagram, underlined column names represent primary keys and names in italics represent foreign keys. All of the foreign keys are named the same in the foreign table as they are in their primary table. Since the cd table has a many to many relationship with the song table, it is necessary to create an intermediate table between them, which is the song_cd_map table. It is comprised of only two foreign keys. Logical Schema

6. The left column shows the two tables involved in the project, artist and cd. Table cd includes a foreign key (aid) to the aid column in the artist table. To avoid problems, the primary keys are set to default to autoincrement, meaning that if a value is not supplied, the next highest available number will be used. If a value is supplied for those columns, the server will attempt to use them first. The right column shows three indexes that were created so that lists could be sorted within the application. With the exception of the sync_test table, all tables and indexes were duplicated using the Ultralite Painter to create the schema for the Pocket PC. The sync_test table will be discussed in detail on the synchronization scripts slide. Physical Schema (DDL)

7. CREATE PUBLICATION pub_all_tables ( TABLE artist, TABLE cd ) CREATE SYNCHRONIZATION USER ml_user CREATE SYNCHRONIZATION SUBSCRIPTION TO pub_all_tables FOR ml_user TYPE 'tcpip' ADDRESS 'host=localhost' Most of these scripts were lifted from the examples in class and within the Ultralite documentation and modified for this project. Both tables in this project are set up under one publication. In this situation, there was no advantage to synchronizing or publishing these tables separately. I learned the hard way how important it was to set these scripts up properly in order to use unique synchronization scripts. Since it is possible to pass options when starting Mobilink to authenticate users who are not already listed and to also create vanilla scripts if they do not already exist, it was not immediately apparent how these scripts would be useful Publication Script

8. The begin_synchronization fires before Mobilink begins the synchronization process and the end_synchronization event fires when it is completed. It might be useful to keep statistics about how long it takes for this process to run on each table. All four of these scripts do the same thing, which is to write data to a separate table (sync_test) with the name and date/time stamp for the begin and end of the synchronization on each table. This would be useful if the synchronization process is taking longer than expected, to narrow it down to a particular table. Simple SQL queries could provide a useful report on Mobilink’s performance. The version of this synchronization is called cddatabase_sync and uses all standard scripts except for these four, which are not created by default. Please note that these were created with Sybase Central which is why they are not in standard SQL format. What is shown here is only the statement that is run and not the ‘ml_add_connection_script’ stored procedure There is also an autoincrementing primary key op_id in the sync_test table that can be used if a particular row needs to be associated with another table at some point in the future. Synchronization Script

9. Finding accurate documentation Determining what information to include in the Painter Schema Working with cursor calls in MobileVB (i.e. Table.MoveBeforeFirst, Table.FindBegin, etc.) Especially when attempting to search for values in different tables One of the biggest difficulties was sifting through the documentation and ending up with many dead ends, or information that did not answer my questions. Many of the methods available through MobileVB are not named the same as in the Ultralite documentation, which was a source of confusion. Web forums provided a great deal of help here. The tutorials that were spread throughout the Sybase and AppForge documentation were also very helpful. The step by step examples were easy to follow and the descriptions were insightful. It took some time and a few failed attempts to get the .usm schema to work properly. At different stages, it caused tables not to update properly, indexes not to sort properly, and synchronizations to fail. Since it only needs to be a subset of the consolidated database, it is important to take time to understand what information is needed in it. Since it is not possible to use SQL directly on the Ultralite Database, working with the cursor calls was very cumbersome and searching tables and searching between different tables required many lines of code that could be handled in one simple select statement. It took some time to become comfortable with the sequence of steps required to move through a table and search its contents. Difficulties Encountered

10. This shows all three forms in the application before any data has been entered. Screen Snapshot # 1

11. This picture shows the Mobilink log screen showing that a successful synchronization (using my unique scripts as shown in the window) has taken place. This also shows the main window of the application after having done the synchronization and the ‘Next’ button has been pressed. Screen Snapshot # 2

12. The first (leftmost) picture shows a new artist has been typed in, the next picture shows how that artist was added to the list after the add button has been pressed. The third picture shows that the artist ‘Evans, Bill’ was selected and the ‘Delete’ button was pressed. A dialog box appears to confirm delete as this is a permanent operation. The program is designed such that it will not delete an artist that is assigned to one or many cds. In the instance in the picture above, this artist was not assigned to a CD yet, so the delete was successful and the fourth picture shows that the artist has been successfully removed from the database. On the main form of the application, there is a delete button that works in a similar fashion. When a cd is selected and the delete button is pressed, the CD only is deleted, the artist is left untouched. Screen Snapshot # 3

13. The left picture shows that a cd title has been entered and an artist is selected. The right picture shows that the cd was successfully entered into the database. The database will not allow duplicate entries, and on the ‘Add CD’ screen, you must select an artist and type a cd name or a message box will prompt you to fill in all information. Screen Snapshot # 4

14. The left picture shows what the database looks like before a synchronization where data was entered and removed. The right picture shows the database after a successful synchronization. It can be seen that artists and cd’s were entered and deleted. Screen Snapshot # 5

15. This query shows the result of the unique synchronization scripts discussed in a previous slide. Every time a synchronization begins or ends, the time and an identifier for the event is written in this table. One can then write simple SQL queries to get information about how long it takes to synch. The picture below shows such a query. Screen Snapshot # 6

16. This code sample shows two versions of the synchronization routine. The left hand version shows the version added after the synchronization scripts were added. The right shows the original which was lifted verbatim from an example used in a website example. Since the –zu switch was passed when starting the server, the right hand version worked. When the synch scripts were added, I removed the –zu switch and replaced the right column code with the left column code and still got successful synchronizations. Code Sample # 1

17. Private Sub btnDelete_Click() If Me.listAllArtists.ListIndex <> -1 Then 'Get the aid of the selected name Dim a_name As String Dim a_id As Integer a_name = Me.listAllArtists.Text ArtistTable.MoveBeforeFirst ArtistTable.LookupBegin ArtistTable.Column("aname").StringValue = a_name ArtistTable.LookupForward a_id = ArtistTable.Column("aid").IntegerValue 'See if that aid is associated with any cds CdTable.FindBegin CdTable.Column("aid").IntegerValue = a_id CdTable.FindFirst If CdTable.EOF Then 'Delete the row If MsgBox("This cannot be undone. Are you sure?", vbOKCancel) = vbOK Then ArtistTable.Delete updateArtists End If Else 'There are CD's associated with the artist - it cannot be deleted MsgBox "Error: Artist is still associated with CD's" End If Else MsgBox "Error: You must select an item to delete!" End If End Sub This is the routine that fires when an artist is deleted. Since at the point when delete is pressed, all we have is the artist name (aname column), we must search the table for that name (all artist names must be unique). When we find the row, we assign the aid column to an integer variable and then search the cd table for that aid, if it is found, then that artist is associated with other cd’s and cannot be deleted, otherwise the user is prompted for a confirmation and upon clicking OK, that row is deleted from the artist table. Code Sample # 2

18. Private Sub Form_Activate() If CdTable.IsOpen Then CdTable.Close End If If ArtistTable.IsOpen Then ArtistTable.Close End If CdTable.Open ("index_cdname") ArtistTable.Open CdTable.MoveBeforeFirst ArtistTable.MoveBeforeFirst DisplayCurrentRow End Sub Private Sub Form_Deactivate() CdTable.Close ArtistTable.Close End Sub These two routines are present in some form on all three screens. These events are fired any time the screen becomes active. The main purpose for using these was that depending on the screen, the tables had to be opened specifying a different index. The IsOpen function returns true if the table is open, and obviously the table must be closed before it can be opened if the index has to be changed. The CdTable.Open(“index_cdname”) opens the CD table against the index_cdname which sorts by cdname. This was required in order to get the CD’s to scroll in alphabetical order on the main form. Otherwise they sorted by cdid which is not desirable Code Sample # 3

19. Private Sub DisplayCurrentRow() If CdTable.RowCount = 0 Then Me.txtCdName.Text = "" Me.txtArtistName.Text = "" Else Dim ArtRow As Integer Me.txtCdName.Text = CdTable.Column("cdname").StringValue 'Find the corresponding artist ArtRow = CdTable.Column("aid").IntegerValue ArtistTable.MoveBeforeFirst ArtistTable.FindBegin ArtistTable.Column("aid").IntegerValue = ArtRow ArtistTable.FindFirst If Not ArtistTable.EOF = True Then Me.txtArtistName.Text = ArtistTable.Column("aname").StringValue End If End If End Sub This code was lifted and modified from an example found on the web. It is used in frmMain and is called when the form opens and when either the ‘previous’ or ‘Next’ buttons are pressed. The RowCount function returns the row number the cursor is currently on, with 0 meaning that it is sitting before the first row. If the RowValue is not 0, then the StringValue of the cdname column of the cd table is put in the Cd Name textbox. Then the aid foreign key IntegerValue is assigned to a variable and then the artist table is searched for the that aid, when found, the aname StringValue is placed in the Artist textbox on the form. Code Sample # 4

20. frmMain frmAddArtist frmAddCd modDb.bas CDDatabase.vbp Source Code

21. AppForge, Ultralite, and Mobilink are excellent products Disappointing that SQL cannot be used directly within the MobileVB code. The provided methods are cumbersome. Sybase is a very flexible DBMS, however the GUI’s need some improvement. (Sybase Central, iSQL) Consistent interface for all devices? All of the middleware tools that were used to make this project were well designed and extremely flexible. Since ultralite and mobilink will work with most DBMS’s, expanding a current database application to be used on a Palm or Pocket PC can be done without disturbing a production system. One of the biggest enhancements that can be made to MobileVB would be to add the ability to make an SQL statement against the Ultralite database directly. Several lines of cursor calls could be reduced to a simple select statement with a where clause. I was disappointed in the quality of the Sybase graphical tools. Sybase central was very slow and caused my computer to crash many times (I could not even open task manager). ISQL was a nice tool, but was also very slow and would close at random times. These problems were consistent not only on my main desktop PC at home, but also on my laptop and on my development PC at work. It would be nice to see this technology extended beyond the Palm and Pocket PC to any other device. It would be convenient to have one consistent interface to a database regardless of what the remote device is. Conclusion