Forum: Rails deployment sqlite3 on my live server - question about command line

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? :)

Dave

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.

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.

On 10/02/2008, at 3:27 PM, Dave Amos 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: pat@freelancing-gods.com    || m: 0413 273 337
w: http://freelancing-gods.com || p: 03 9386 0928
discworld: http://ausdwcon.org || skype: patallan

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!

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

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

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.