headers
Dave Everitt | 15 May 2012 19:39
Picon
Gravatar

setting up the SQLite database

I know this isn't Python, but I'd like to get a view on the 'one  
obvious' way to set up an SQLite (or other) database and its location  
per-app. I've got a bit lost with the Camping 2 changes and various  
code snippets I have kicking around.

1.
is it best to set up the DB creation/connection:

1.1
at the end of the app

AppName::Models::Base.establish_connection(
  :adapter => 'sqlite3',
  :database => '/path/to/my/app/myApp.db'
)
run AppName #from an old snippet

1.2
OR
like this (postgres) example [Magnus]:

def List.create
  List::Models::Base.establish_connection(
    :adapter => "postgresql",
    :username => "root",
    :password => "toor,
    :database => "list"
  )
  List::Models.create_schema
end
(Continue reading)

Permalink | Reply |
headers
David Susco | 15 May 2012 20:49
Picon

Re: setting up the SQLite database

1) I usually do 1.3:

def self.connect env
  App::Models::Base.establish_connection App.config[env]['db_connection']
  App::Models::Base.logger = Logger.new File.dirname(__FILE__) +
App.config[env]['db_log']
end

def self.create env = 'dev'
  self.connect env
  self.create_schema
end

2) I've never tried it without specifying the adapter, I figure it
can't hurt to keep it in there. Plus it makes all the env's in my
config file look the same.

3) So long as it's using migrations (the < V /d after the class name)
it won't delete anything by running a connect again after the db has
been created.

class CreateApp < V 1.0
  def self.up
    create_table User.table_name do |t|
      t.string     :name
      t.timestamps
    end
  end

  def self.down
(Continue reading)

Permalink | Reply |
headers
Nokan Emiro | 16 May 2012 17:59
Picon

Re: setting up the SQLite database

Hi,


Just a comment:  I don't think it's a good practice to set the database
details (establish_connection() call) in the application itself.  It's an
environmental issue, and this way you lose the possibility to move
your app between dev, ((devtest)), (test) and production environments
without modifications.


On Tue, May 15, 2012 at 7:39 PM, Dave Everitt <deveritt-PAToI0hbvL4qdlJmJB21zg@public.gmane.org> wrote:
I know this isn't Python, but I'd like to get a view on the 'one obvious' way to set up an SQLite (or other) database and its location per-app. I've got a bit lost with the Camping 2 changes and various code snippets I have kicking around.

1.
is it best to set up the DB creation/connection:

1.1
at the end of the app

AppName::Models::Base.establish_connection(
 :adapter => 'sqlite3',
 :database => '/path/to/my/app/myApp.db'
)
run AppName #from an old snippet

1.2
OR
like this (postgres) example [Magnus]:

def List.create
 List::Models::Base.establish_connection(
  :adapter => "postgresql",
  :username => "root",
  :password => "toor,
  :database => "list"
 )
 List::Models.create_schema
end

1.3
in a config/database.yml file [Magnus] (probably not worth it for SQLite):
---
adapter: postgresql
username: root
password: toor
database: mycampingdb

And then do:

require 'yaml'

def AppName.create
 AppName::Models::Base.establish_connection(YAML.load(File.read("database.yml")))
 AppName::Models.create_schema
end


2.
since sqlite is the default, is it necessary to set :adapter explicitly if that's what I'm using?

def AppName.create
 AppName::Models::Base.establish_connection(
 :adapter => 'sqlite3',
 :database => '/path/to/my/app/.camping.db'
)
end


3.
Since .create is *only needed once* to set up the database (Magnus: "if you connect to a database which already has the tables, DON'T run `AppName::Models.create_schema` as this will probably delete the whole database.") what do we do with this after the first run:

def AppName.create
 AppName::Models.create_schema
end

3.1 delete it after the db is created on the first run?
3.2 check if the db already exists (best way, please)?
3.3 check like this (never understood ':assume'?):
AppName::Models.create_schema :assume => (AppName::Models:: table_name.table_exists? ? 1.0 : 0.0)
or could we do something simpler like (?):
AppName::Models.create_schema unless table_exists?(table_name)
?

4.
There's also this from a previous post (opinions please?):

"On the part of migrations ... "def self.up" and "def self.down" ... gave me errors for some reason. But ... it should be updated to "def self.change" ... that's the modern way of doing it."

DaveE

_______________________________________________
Camping-list mailing list
Camping-list <at> rubyforge.org
http://rubyforge.org/mailman/listinfo/camping-list

_______________________________________________
Camping-list mailing list
Camping-list@...
http://rubyforge.org/mailman/listinfo/camping-list
Permalink | Reply |
headers
Philippe Monnet | 21 May 2012 03:58

Re: setting up the SQLite database

I personally always use a config/database.yml file as I like to keep the configuration out of the code.
Plus it makes it easier to switch between development and production configuration.

E.g. for Heroku:
        dbconfig = YAML.load(File.read('config/database.yml'))
        # On Heroku the production database,yml is configured automatically
        environment = ENV['DATABASE_URL'] ? 'production' : 'development'
        Camping::Models::Base.establish_connection  dbconfig[environment]

On 5/15/2012 11:39 AM, Dave Everitt wrote:
I know this isn't Python, but I'd like to get a view on the 'one obvious' way to set up an SQLite (or other) database and its location per-app. I've got a bit lost with the Camping 2 changes and various code snippets I have kicking around.

1.
is it best to set up the DB creation/connection:

1.1
at the end of the app

AppName::Models::Base.establish_connection(
 :adapter => 'sqlite3',
 :database => '/path/to/my/app/myApp.db'
)
run AppName #from an old snippet

1.2
OR
like this (postgres) example [Magnus]:

def List.create
 List::Models::Base.establish_connection(
   :adapter => "postgresql",
   :username => "root",
   :password => "toor,
   :database => "list"
 )
 List::Models.create_schema
end

1.3
in a config/database.yml file [Magnus] (probably not worth it for SQLite):
---
adapter: postgresql
username: root
password: toor
database: mycampingdb

And then do:

require 'yaml'

def AppName.create
 AppName::Models::Base.establish_connection(YAML.load(File.read("database.yml")))
 AppName::Models.create_schema
end


2.
since sqlite is the default, is it necessary to set :adapter explicitly if that's what I'm using?

def AppName.create
 AppName::Models::Base.establish_connection(
  :adapter => 'sqlite3',
  :database => '/path/to/my/app/.camping.db'
)
end


3.
Since .create is *only needed once* to set up the database (Magnus: "if you connect to a database which already has the tables, DON'T run `AppName::Models.create_schema` as this will probably delete the whole database.") what do we do with this after the first run:

def AppName.create
 AppName::Models.create_schema
end

3.1 delete it after the db is created on the first run?
3.2 check if the db already exists (best way, please)?
3.3 check like this (never understood ':assume'?):
AppName::Models.create_schema :assume => (AppName::Models:: table_name.table_exists? ? 1.0 : 0.0)
or could we do something simpler like (?):
AppName::Models.create_schema unless table_exists?(table_name)
?

4.
There's also this from a previous post (opinions please?):

"On the part of migrations ... "def self.up" and "def self.down" ... gave me errors for some reason. But ... it should be updated to "def self.change" ... that's the modern way of doing it."

DaveE

_______________________________________________
Camping-list mailing list
Camping-list-GrnCvJ7WPxnNLxjTenLetw@public.gmane.org
http://rubyforge.org/mailman/listinfo/camping-list 


_______________________________________________
Camping-list mailing list
Camping-list@...
http://rubyforge.org/mailman/listinfo/camping-list
Permalink | Reply |
headers
Dave Everitt | 21 May 2012 10:52
Picon
Gravatar

Re: setting up the SQLite database

Thanks Nokan, Dave, Philippe for your replies, it's good to get a  
measure of standard practice even for things as simple as this.

There just remains no. 4 (from a question by Isak Andersson
   http://comments.gmane.org/gmane.comp.lang.ruby.camping.general/1751)

for which I'd like an opinion, since I can't find a definitive answer  
from the AR docs... and can only fond a reference to it on the Ember  
GitHub readme:
   https://github.com/EmberAds/acts_as_uuid

or slide 21 of this AR intro:
   http://www.slideshare.net/blazingcloud/active-record-introduction-3

since I've only ever used 'up' and 'down' (and don't use Rails) this  
isn't obvious to me :-)

Finally, what's a good approach to security (SQL injection?) for a  
public app?

DaveE

>> 4.
>> There's also this from a previous post (opinions please?):
>>
>> "On the part of migrations ... "def self.up" and "def  
>> self.down" ... gave me errors for some reason. But ... it should be  
>> updated to "def self.change" ... that's the modern way of doing it."
>>
>> DaveE
Permalink | Reply |
headers
David Susco | 21 May 2012 14:53
Picon

Re: setting up the SQLite database

On 4:

http://api.rubyonrails.org/classes/ActiveRecord/Migration.html#label-Reversible+Migrations

Looks like you just define the up, AR takes care of the rest. Never
tried it, it'll save a few lines of code though.

On injection, AR sanitizes almost everything I believe. The only thing
I know to avoid is using a user set variable straight in a string:

"thing = #{ <at> input.user_var}"

That's dangerous, you're supposed to do this:

"thing = ?",  <at> input.user_var

Dave

On Mon, May 21, 2012 at 4:52 AM, Dave Everitt <deveritt@...> wrote:
> Thanks Nokan, Dave, Philippe for your replies, it's good to get a measure of
> standard practice even for things as simple as this.
>
> There just remains no. 4 (from a question by Isak Andersson
>  http://comments.gmane.org/gmane.comp.lang.ruby.camping.general/1751)
>
> for which I'd like an opinion, since I can't find a definitive answer from
> the AR docs... and can only fond a reference to it on the Ember GitHub
> readme:
>  https://github.com/EmberAds/acts_as_uuid
>
> or slide 21 of this AR intro:
>  http://www.slideshare.net/blazingcloud/active-record-introduction-3
>
> since I've only ever used 'up' and 'down' (and don't use Rails) this isn't
> obvious to me :-)
>
> Finally, what's a good approach to security (SQL injection?) for a public
> app?
>
> DaveE
>
>
>>> 4.
>>> There's also this from a previous post (opinions please?):
>>>
>>> "On the part of migrations ... "def self.up" and "def self.down" ... gave
>>> me errors for some reason. But ... it should be updated to "def self.change"
>>> ... that's the modern way of doing it."
>>>
>>> DaveE
>
>
> _______________________________________________
> Camping-list mailing list
> Camping-list@...
> http://rubyforge.org/mailman/listinfo/camping-list

--

-- 
Dave
Permalink | Reply |

Gmane