Archive for May, 2007

Notes while working on SQLite with Ruby on Rails

I have been going through the Ruby on Dave Thomas’s Agile Web Development with Rails on Puppy. Because MySQL is rather large, I chose to instead use sqlite3 for the database instead. Here are the changes I made.

Pointing the Application to the Right Database

In a rails project, the config/database.yml file controls which database the application access and the database adapter it uses. There should be a section for development, test, and production.

By default, you’ll see the following section:

development:
adapter: mysql
database:
username: root
password:
host: localhost

We need to change 2 lines. The first being the adapter, which we chnage from mysql to sqlite3. The second line we change is the location of the database, which we specify using a relative path from the project root. The username, password, and host field is unused. Sqlite database is a file, not a server. You should make sure that the user you will use to access sqlite3 have read/write access to the database file.

development:
adapter: sqlite3
database: db/depot_development.db

When you create the database, make sure you use the exact name (don’t for example create depot_development when your file references depot_development.db).

Creating the table

Just like MySQL, you write a script to create the database tables. Usually, we create a database for each stage of the project, so when you have the project depot, we will create depot_development, depot_test, and depot_production.

In MySQL, you would log into as mysql admin and create 3 databases and grant the developer permission to the database. Because sqlite3 database are basically flat files, you can just create the database file and make sure that the developer has read/write access to the file.

The product table is created by the following script:

DROP TABLE IF EXISTS products;
CREATE TABLE PRODUCTS
(
id int not null auto_increment,
title varchar(100) not null,
description text not null,
image_url varchar(200) not null,
price decimal(10,2) not null,
primary key (id)
);

If you try to run this in sqlite3, it will fail. Unfortunately, sqlite3 isn’t all that great with error messages, since it didn’t generated any. You have to rewrite the script to:

DROP TABLE IF EXISTS products;
CREATE TABLE PRODUCTS
(
id integer primary key
title varchar(100) not null,
description text not null,
image_url varchar(200) not null,
price decimal(10,2) not null
);

In sqlite3, you can declare a field of int, but the primary key field only works on the type of integer, so you have to use the data type of integer instead of int. A field that is integer with primary key is automatically incremented. There is no need to say “not null” because primary key fields cannot be null.

Of course, I did not know this and got complaints that the id filed is null.

Confusing Error Message

Suppose you using the web application you created and add an item, but get the following error:

SQLite3::SQLException: SQL logic error or missing database: INSERT INTO products ("image_url", "date_available", "price", "title", "description") VALUES('', '2007-05-25 06:10:00', NULL, '', '')

You could get this message if you can’t access the database or you have violated a database rule. In the above example, I had attempted to insert a null value into a field
that is not null.

Basically, the error messages are not very descriptive.

There is no Now() in Sqlite3

In one of the examples, the following query is executed:

select * from products where date_available >= now()

This query will bomb in sqlite3 because there is no now() function. You have to change the syntax to:

select * from products where date_available >= date('now')

Keep in mind that date(‘now’) returns UTC. If you want to return the local time, use date(‘now’, ‘localtime’)

Advertisements

May 29, 2007 at 8:47 am 2 comments

Removing auto dhcp on startup for Puppy Linux

In Puppy Linux, when you select “Auto DHCP”, the interface will ask if you want to save the configuration. Unfortunately, one side effect or this is when you boot up and you are not near a network, your machine will hang until it times-out on DHCP (though you can press OK to bypass). How do you undo that auto dhcp on startup?

When you save configuration, the file is saved to a file name /etc/{interface}mode. The interface name you can get by running Puppy Network Setup. On my machine, the interface for my wireless is rausb0, so the interface file is /etc/rausb0mode. When you startup Puppy, it runs /etc/rc.network, which calls all of the /etc/*mode files.

To fix the problem, edit the script to remove the dhcp or delete it entirely. In my case, I just deleted the /etc/{interface}mode file.

May 29, 2007 at 7:40 am Leave a comment

Upgrading Puppy 2.15CE to 2.16

Puppy 2.16 has been release recently and I decided to upgrade from 2.15CE to 2.16. I backed up the pup_save file and then use Puppy 2.16’s universal installer to install the software onto the USB key. The upgrade did not work as expected since Puppy did not boot into X-Window as I expected to. I did encountered several problems:

  • The /etc/X11/rc.d/rc.modules were overwritten. I had thought that my changes were saved to my pup_save file, but apparently not. Fortunately, I had documented the changes in a blog. I just basically copy the settings back. In the future, I will save a copy of the rc.modules file.
  • My dev_215.sfs was removed. I downloaded dev_216.sfs in its place.
  • There are two drives on my desktop. I deleted the older one.
  • My Open Office is missing. I downloaded the new Open Office 2.2 sfs file.
  • SeaMonkey’s home page is the 2.15CE community page, which no longer exists. Fixing it was as simple as setting it to a new home page.

There is an additional step after you install the sfs files. You need to select menu->System->Bootmanager Configure. Click on the button to select the SFS file and move the two sfs file to the right. Reboot machine. Actually, the first time I tried this, it did not work. I unchecked the option to ignore the entry and manually move the files again and reboot. This time it works for some reason.

May 21, 2007 at 12:30 am Leave a comment

What does DotPet use as a signature

In Puppy 2.14 or later, you can register your DotPet Package. After registration, you can upgrade Puppy without overwriting your package. You can also uninstall the package. Recently, I created a DotPet for Ruby and Ruby documentation. I had named the two packages as Ruby-1.8.6-i486 and Ruby-1.8.6-doc-i486. Much to my surprise, when I installed the two packages, the second package overwrote the first.

I wondered what sort of signature Puppy was using as the DotPet signature. Examination of the dir2pet script indicated that the config file only logged whether to register the package or not, so I had guess that it had to be filename.

Expermientation indicated that the signature comes from the filename before the dash version number. So if the package is ruby-1.8.6-i486, the signature is ruby. The reason the doc install overwrite the package is because ruby-1.8.6-doc-i486 also resolve to a signature of ruby. To get a different signature, you need to add the “-doc” before the version number.

I rename the packages to “ruby-1.8.6-i486” and “ruby-doc-1.8.6-i486”, and it worked fine. When you create the ruby-doc, make sure that you enter “ruby” as the dependency.

May 6, 2007 at 5:46 pm Leave a comment


Calendar

May 2007
M T W T F S S
« Apr   Jun »
 123456
78910111213
14151617181920
21222324252627
28293031  

Posts by Month

Posts by Category