Dear Andrew: I'm so happy to see that someone's working on the DB. I really love the functionality of the new DB in OO2. If you need someone to farm some work out to or to test sample databases, I'd be more than happy to help.
-- Sonja > When I mentioned that I could put together some running commentary as I > build the example DB what I had in mind would be something like this. > > I have a number of people asking about images in Base and I am just > about wrapped up with an example. Here is the text file that will go up > to the > forum board along with it. Something similar is what I would expect to > post up with the sampe DB. Of course I won't go into any crashes along > the way :-( > > ------------------------------employees.txt---------------------------------- > I am going to create another example database, this one will let us > manage a list of employees. > > It will offer a chance to look at working with One to Many and One to > One data relationships. > > It will also work with Image fields, and Memo fields. You may be > surprised at just how easy these are to use in Base. > > Along with the items mentioned above it will also show how to use > Listboxes to control data entry, and the use of a VIEW to allow one > field in a table to reference another. > > [b][i]CREATE THE DATABASE FILE[/i][/b] > Lets start by creating a new database. I have choosen the OOBase native > database engine HSQLDB. > > Tools>New>Database > > Select Next > > Seletct Finished > > Save the database as Employees > > OK, now we have an empty database file. > > Select the Tables icon, from the icons on the left of the Base main window. > > Select "Use Wizard to Create Table" > > On the table wiZard dialog make sure the Category is Business. > > From the Sample Tables pull down box select Employees. > > Select the dblChevron ">>" button to move all fields over from > "Available fields" to "Selected fields" > > Now scroll thru the "selected fields" and find the field named "Photo", > move it back to "Available Fields" by clicking on the "<" button. > > Remove the field "Department" the same way. > > Finally remove the field "Notes" also. > > Click NEXT > > On this second screen in the list "Selected Fields" highlight the field > "EmployeeID". > > Change the AutoValue drop down list from "no" to "Yes". > > The Screen should change so that the control just below this now says > "IDENTITY". > > Click NEXT > > On the Set Primary Key page choose "Use an existing field as a primary > key". In the fieldname drop down list select EmployeeID. The checkbox > just to its right "Auto value" should now be checked, leave as such. > > Click NEXT > > Leave the table name as Employees and click Finish. > > OK > > Now select "Create Table in Design View" > > In the empty Table Design window ad the first FieldName as "EmployeeID" > Change the FieldType to "Integer". LEAVE the autovalue as false. > > Right click on the row marker square just to the left of the Field name, > and from the popup menu select "Primary Key". > > Add a second fieldname "Photo", Fieldtype of "Image[LONGVARBINARY]" > > Save the table as "EmployeePhotos" > > Select "Create Table in Design Veiw" again. > > Add a fieldname "EmployeeID", fieldtype "INTEGER", and make it the > primary key. > > Add a fieldname "Notes", fieldtype Memo[LONGVARCHHAR]. > > Save this table as EmployeeNotes > > Now for the final table. > Create a table using the table design dialog with the following fields. > ID of type IDENTITY (Integer with AutoValue = True), and Department > VARCHAR(50). Name the table "Departments" > > [b][i]CREATE THE RELATIONSHIPS[/i][/b] > Open the Relationship Dialog. Tools>Relationship > > The add tables box should be open. Add all FOUR tables Employees, > EmployeePhotos, EmployeeNotes and Departments to the relationship window. > > Drag the field ID in Departments to the field DepartmentID in Employees. > (notice that it pus a 1 just above the line and next to the table > Departments and an 'n' above the line and next to table Embployees. This > denotes a one to many relationship between the two tables.) > > Drag the field EmployeeID from the Employees table to the field > EmployeeID on the EmployeePhotos table. Do the same for the > EmployeeNotes table. > > OK, now why did I break the image and memo fields out of the main table. > Well, for performance reasons. When I am dealing with BLOB or CLOB > fields I don't want to be having to bring them from the disk into memory > when I am searching or joining tables. As you can see in the diagram by > making the EmployeeID field in each of the secondary tables > EmployeePhotos and EmployeeNotes the primary key for those tables I have > created a 1 to 1 relationship. Now we need to insure that if we ever > delete an employee record that we also delete its associated photo or > notes entry. > > Double click the line that runs from the Employees table to the > EmployeePhotos table. The Relations dialog will open. Under "Update > Options" select Update Cascade. (Normally we would never change a > primary key, but we will just be double sure and tell it that if somehow > we ever did do that, then update the key to match in the referenced table) > > Now for the "Delete Options", select "Delete cascade". > > Click OK > > Now do the same steps for the relationship (the line connecting > Employees to EmployeeNotes) with the EmployeeNotes table. > > The screen should look something like this. > > http://www.paintedfrogceramics.com/OpenOffice/employees/employeeRelations.png > > When you are finished save and close the Relation Design window. > > Our database structure is now complete. > > [b][i]CREATE THE EMPLOYEE FORM[/i][/b] > > At this point go ahead and save the database file. > > Alright then, lests build the Employee Record Form. > > Right mouse click on the table "Employees" and from the popup (context) > menu select "Form Wizard". > > A blank Writer document will be opened and the Form Wizard dialog will > be displayed with the table Employees selected. Move all of the fields > from the "Available Fields" list to the "Fields in the form" list by > clicking on the button ">>". > > Click NEXT > > On the second page put a check mark in the "Add Subform" checkbox. > > Make sure that "Sub form based on manual selection of fields" is selected. > > Click NEXT > > The next page lets us select the table for our sub-form. In the drop > down box select EmployeePhotos. > > In the list "Available Fields" you may notice that only the EmployeeID > field is listed. The field "Photos" is not. The wizard will not (at the > moment anyway) automatically add a Image field. But don't worry we can > add it later. For now just move the EmployeeID field over to the "Fields > in the form" list. > > Click NEXT > > This next page is "select the joins between your forms". In first row > of drop down controls select EmployeeID. > > Click NEXT > > On this page "Arrange the controls on your forms" we have 4 choices for > the fields in our Main form and our Sub form. Lets choose the format all > the way on the right "In Blocks - Labels Above" for both the form and > the sub-form. > > Click NEXT > > This next page is "Select the data entry mode". Leave the default > selections. > > Click NEXT > > Here we can choose a background color for the form. We can change the > border for the Edit Controls, en mass, but not the text labels. Changing > the background color will also change the default color for our label > text. Choose whatever you like. > > Click NEXT > > On the last page we give the form a name. The default is the table name, > so lets just keep it. Also we can decide if we want to imediatly edit > the form layout, or go right into editing data with the form. Lets edit > the latyout. Select "Modify the form". > > Click Finish > > [i]WELL, in the spirit of full disclosure at that moment OO.o crashed. > The recovery processed reported an error when I restarted Base and the > last three things I had done where gone...You gotta love it..right. In > all fairness, I tried to cheat at one point and attempted to change the > default value for two columns in Employees table. This generated an > error because they belonged to the relations I had just created, so > could not be dropped and re-added. This most likely caused the > crash..but am not sure. > > In fact I am sure of it. Looking at the Employee table in database now, > I see where the default value had changed to what I wanted, and the > Foregin key relastionship is gone. So the moral of this is - If you are > going to change a column that is part of a foregin key relationship, be > sure to drop the relationship first...[/i] > > > OK, now back to the form we just created. > > The form should be open in design mode. > > Here is the way the form looked for me, when the wizard was done. > > http://www.paintedfrogceramics.com/OpenOffice/employees/employeeForm1.png > > Not exactly what I would call a well layed out data entry form; and our > photo and notes fields are missing. > > So lets just clean it up. > > WELL, once again...after cleaning up the form. Adding an image control > and accidentaly hitting run...OO.o crashed. Upon restart the form was > completely gone. Jees, this is starting to feel like old home week... > > OK, well...not to be detered. Lets just press forward. > > >
