Sqlite3 on my live server - question about command line

Rails Deployment
1

Hi there,

I just deployed my rails app using Capistrano, and all’s well for the
most part. I’m not used to using sqlite3 on the command line, though,
and I can’t figure out how to use the sqlite3 prompt to find my
production database. When I type in sqlite3 and then do “.databases”, it
doesn’t show up. Does anyone have any sqlite tips for a newbie? :slight_smile:

Dave

2

The database is stored in its own file (named in your database.yml)

To access it, on the commandline:
sqlite3 filename
on the server and directory where it is located (or use the full
path). The database will most likely be in the RAILS_ROOT directory
of your application.

3

Shawn Balestracci wrote:

The database is stored in its own file (named in your database.yml)

To access it, on the commandline:
sqlite3 filename
on the server and directory where it is located (or use the full
path). The database will most likely be in the RAILS_ROOT directory
of your application.

Okay, yeah, that makes sense. I did this:

sqlite3 db/production.sqlite3

which was the file that my database.yml file pointed me to. When I ran:

.databases

it gave me:

seq name file

0 main
/home/dave/public_html/statesidesoccer.net/releases/200802
1 temp /var/tmp/etilqs_u8MlqQJVLCpUUa1

I don’t know what that means… I assume “main” is the db I want, but I
don’t know how to select it.

4

Okay, that made sense to me, and it worked! I’ve been inserting data
like crazy, thanks!

I do have a bit of a follow up question, though. Does sqlite3 handle
booleans differently than MySQL? It seems to choke when I query based on
a boolean. For example:

In my controller, I have this:

@gameweek = Gameweek.find(:first, :conditions => [ “current = true and
league_id = ?”, @league.id ])

In my production log, I get this error:

ActiveRecord::StatementInvalid (SQLite3::SQLException: no such column:
true: SELECT * FROM gameweeks WHERE (current = true and league_id = 1)
LIMIT 1):

I have a “current” column, so I don’t see what the problem is…

Thanks again!

5

Yes, it uses 1 and 0 instead… change your query to this:

@gameweek = Gameweek.find(:first, :conditions => [ “current = ? and
league_id = ?”,true, @league.id ])

6

On 10/02/2008, at 3:27 PM, Dave A. wrote:

0 main
/home/dave/public_html/statesidesoccer.net/releases/200802
1 temp /var/tmp/etilqs_u8MlqQJVLCpUUa1

I don’t know what that means… I assume “main” is the db I want,
but I
don’t know how to select it.

Hi Dave

Each sqlite file is a database - in this case, db/production.sqlite3 -
there’s not multiple databases within it (I’m not sure if that’s
possible with sqlite).

So, now that db/production.sqlite3 exists, you should be able to run
your migrations on it:
rake db:migrate RAILS_ENV=production

Then the .tables command in sqlite should show all your tables in that
file.

Cheers

–
Pat
e: [email protected] || m: 0413 273 337
w: http://freelancing-gods.com || p: 03 9386 0928
discworld: http://ausdwcon.org || skype: patallan

7

Hi guys,

Am using SQlite3 in winXp. i want to create a databse in that, probably
the create commands like,

  1. $ sqlite3 db_name.db;
  2. create database db_name;
  3. sqlite3 db_name.sqlite;

are not working… can anyone say the exact command to create database?

thanks.