A CD Database for the Pocket PC

1
A CD Database for the Pocket PC
Matthew Wenner
CS 8628, Summer 2003
2
Sequence of Tasks

• 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.

3
Project Description

• 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 CDs

• This program was originally conceived as a
  database to maintain a complete CD database,
  including lists of artist, labels, CDs to
  purchase in the future, comments on CDs 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.

4
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
Song
Contains
Has
1, 1
Genre
1, N
gname
gid
stitle
sid
length

• This diagram shows the relationship between
  different entities within this projects 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.

5
Logical Schema

• 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.

6
Physical Schema (DDL)

• 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.

7
Publication Script

• 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 'hostlocalhost'

• 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

8
Synchronization Script

• 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 Mobilinks 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.

9
Difficulties Encountered

• 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.

10
Screen Snapshot 1

• This shows all three forms in the application
  before any data has been entered.

11
Screen Snapshot 2

• 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.

12
Screen Snapshot 3

• 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.

13
Screen Snapshot 4

• 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.

14
Screen Snapshot 5

• 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 cds were entered
  and deleted.

15
Screen Snapshot 6

• 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.

16
Code Sample 1

• 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.

17
Code Sample 2

• Private Sub btnDelete_Click()
• If Me.listAllArtists.ListIndex ltgt -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").IntegerVa
  lue
• '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

• 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 cds and cannot be deleted,
  otherwise the user is prompted for a confirmation
  and upon clicking OK, that row is deleted from
  the artist table.

18
Code Sample 3

• 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 CDs to
  scroll in alphabetical order on the main form.
  Otherwise they sorted by cdid which is not
  desirable

19
Code Sample 4

• 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").IntegerValu
  e
• 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.

20
Source Code

• frmMain
• frmAddArtist
• frmAddCd
• modDb.bas
• CDDatabase.vbp

21
Conclusion

• 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 GUIs
  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 DBMSs, 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.