Migration from MySQL to SQLite

I worked with MySQL database but since I work with Rails I understand t开发者_运维知识库hat I must use SQLite. Now I have two questions :

1 - Now I have MySQL in my computer , If I install SQLite , will any problems happen? For example are there any conflicts between them?

2 - What differences do they have in syntax ?

From this question..

SQLite is used primarily for development purposes only because it is quite simple to setup a database without much frustration, however it is certainly less efficient in terms of concurrency (which is highly likely for web applications) than something like MySQL. So regardless if you use SQLLite in development or not, it is highly advisable to use MySQL (or something equivalent) in production.

For completeness sake, SQLite is also used in "all in one package" software (such as mobile development), in which you can easily bundle a SQLite file with your application.

As stated on SQLite Website:

SQLite is not intended to be an enterprise database engine. It is not designed to compete with Oracle or PostgreSQL.

and

Another way to look at SQLite is this: SQLite is not designed to replace Oracle. It is designed to replace fopen().

In terms of the benefits with Ruby, there really isn't much benefit as libraries/ORMs (i.e. Active Record) really abstract the differences between the two systems to make a consistent access layer in a single wrapper.

Check this question from stackoverflow And check this Google.com :)

Read this

The question of when SQLite should be used and when MySQl should be used has come up again and again. To know when and where it is appropriate to use either SQLite or MySQL databases management systems, it is first and foremost important to know what the difference is between them, if at all there is any difference.

In a nutshell, SQLite is an Open Source library that implements a self-contained transactional SQL database engine which requires no server and works on little or no configuration. MySQL on the other hand is also and Open Source Relational Database Management System.

Diving right into the task of answering this question, I have provided an itemized list of some the things SQLite is capable of doing well and I have compared the same with MySQL

SQLite is:

• easy to set up and in many cases no configuration or installation is necessary great enough to use for databases you would only need on a temporary basis or for test purposes
• not suitable where user management is needed as SQLite uses the file systems permissions so there is no way you will be able to use SQL statements such as GRANT and REVOKE.
• suitable for using in embedded applications and installations and embedding into applications themselves
• great for rapid development
• lacking in performance measurement features
• not suitable where concurrency transactions on the databases is required
• not good for large scale databases as SQLite stores the database in a single file and this can fall under the restrictions of the operating system where SQLite is not capable of splitting the data across volumes.
• not suitable for use in any situation where a Client/Server Architecture is needed suitable for using on small to medium website. These are websites with average 100K or less hits per day.
• Not readily scalable. Altering tables is not permitted in SQLite except for adding columns and renaming tables
• suitable in replacement of Ad-hoc file storage commonly where applications access files using fopen(), fread() and fwrite().
• Is not suited in a situation where Stored procedures are needed and where certain types of joins are needed

MySQL is:

• far more difficult to set up and configuration of users is a must good for creating temporary databases as well as for test purposes. This would only be practical if you have the MySQL database server and client already set up quite suited for managing users and their permissions
• not suitable for embedding in some hardware as you would still need the server component of the database. MySQL though is suitable for embedding into application using provided libmysql libraries
• great for rapid development in some situations
• perfect for concurrency transactions on the data and is well suited for multi-user environment
• great for large scale production applications which scale even over clustered database configurations
• perfect for using in a Client/Server Architecture set up
• suitable for use on small, medium and large scale websites taking in billions of hits a day
• highly scalable as far as the MySQL data and tables go and can be manipulated any time the MySQL DBA. This scaling capabilities transcends disks, physical servers and location
• not intended to replace fopen(), fread() and fwrite(). MySQL manages its own data files and not the operating system
• fully compatible with stored procedures, triggers, view and other operations common with other major Relational Database Management Systems. MySQL only provides these features with selected storage engines

With these tips, I sure hope they are a competent guide to shove you in the right direction when the choice come between choosing between SQLite or MySQL for your next project.

More edits:-- Follow these guides to learn rails.

http://pragprog.com/book/rails4/agile-web-development-with-rails

http://railsforzombies.org/

http://www.amazon.com/dp/0596518773/

http://railscasts.com/

http://guides.rubyonrails.org/

Quick solution for using mysql. I am assuming u are using rails 3, Add

  gem 'mysql2' 

in your gem file and run

  bundle install

and replace your database.yml file with this..

        # MySQL. Versions 4.1 and 5.0 are recommended.
        #
        # Install the MySQL driver:
        # gem install mysql2
        #
        # And be sure to use new-style password hashing:
        # http://dev.mysql.com/doc/refman/5.0/en/old-client.html
        development:
          adapter: mysql2
          encoding: utf8
          reconnect: false
          database: my_database_development
          pool: 5
          username: root
          password:
          host: localhost

        # Warning: The database defined as "test" will be erased and
        # re-generated from your development database when you run "rake".
        # Do not set this db to the same as development or production.
        test:
          adapter: mysql2
          encoding: utf8
          reconnect: false
          database: my_database_test
          pool: 5
          username: root
          password:
          host: localhost

        production:
          adapter: mysql2
          encoding: utf8
          reconnect: false
          database: my_database_production
          pool: 5
          username: root
          password:
          host: localhost

I hope his answer make sense for you. Now a better quick tip for you. start using google.

You can easily use mysql with rails.

In your Gemfile:

gem 'mysql'

In your database.yml

development:
  adapter: mysql
  username: ...
  password: ...
  database: ...