Development

HowToUseSqLite

You must first sign up to be able to contribute.

Version 11 (modified by Riccardo, 9 years ago)
--

Symfony Howto: Use SQLite

SQLite with Symfony 1.2

Create your project.

1. Edit databases.yml as follows:

  • [...]
          dsn:          'sqlite:%SF_DATA_DIR%/name_of_your_database.db'
    [...]
    
  • you may also delete the user and password lines, which are useless for SQLite

2. Edit propel.ini as follows:

  • [...]
    propel.database            = sqlite
    [...]
    
  • delete the lines starting with the following:
    • propel.database.driver
    • propel.database.url
    • propel.database.creole.url
    • propel.database.user
    • propel.database.password
  • delete the MySQL-specific section of the file (it is marked as such in the comments)

Now the following commands should run without problems:

$ php symfony propel:build-model
$ php symfony propel:build-sql
$ php symfony propel:insert-sql

Additional notes

  • You do not need to create your database file in advance, symfony propel:insert-sql will do it for you
  • Be sure to make the data/ directory and your database file writeable by your webserver
  • A forward slash / as path separator in schema.yml works on Windows systems too
  • For Windows users: the $ in front of commands stands for C:\Path\To\Your\Project>
  • Primary key columns will not be defined as AUTOINCREMENT, even if you specify so in schema.yml. This is an issue with Propel, not with Symfony itself.

SQLite with Symfony 1.0.7 and Windows

done other steps but you will have path problems in windows if you use apache under "program files" because of the naming problem. you should not use " " space character in your paths in windows. yes there is a workaround for this. Edit propel.ini:

[..]
propel.database            = sqlite
propel.database.createUrl  = sqlite://C://Program Files/Apache Group/Apache2/htdocs/project_name/data/database.db
propel.database.url        = sqlite://C://Program Files/Apache Group/Apache2/htdocs/project_name/data/database.db
[..]

SQLite with Symfony 0.6x

Create your project, and populate config/schema.xml.

Edit databases.yml so it reads:

all:
  propel:
    class:          sfPropelDatabase
    param:
      dsn:          sqlite://localhost//..full..path..to../db.sqlite

Edit propel.ini:

[..]
propel.database            = sqlite
propel.database.createUrl  = sqlite://localhost//..full..path..to../db.sqlite
propel.database.url        = sqlite://localhost//..full..path..to../db.sqlite
[..]
$ symfony propel-build-model
$ symfony propel-build-sql
$ symfony propel-insert-sql

Be sure to make the db.sqlite file and the data/ directory writeable by your webserver.

SQLite with Symfony 0.5.x

Note that Symfony versions up to and including 0.4.4 (and possibly higher) do not support SQLite v3.x, yet. Use v2.x and lower for now.

To use a SQLite backend, you need to change your orm.yml configuration file (in application config directory):

all:
  adapter:  sqlite
  database: /path/to/yourproject/data/db.sqlite

and also change your propel.ini (in project config directory) to be able to generate sql schema file:

propel.database = sqlite
propel.database.url = /path/to/yourproject/data/db.sqlite

Now, you can create the model, ...

symfony build-model

... generate the SQL schema, ...

symfony build-sql

... and populate the database.

cd /path/to/yourproject
sqlite data/yourproject.db < data/sql/schema.sql

You *will* see errors on the first population of the database, looking like this:

drop table users;
SQL error: no such table: users
drop table groups;
SQL error: no such table: groups
drop table articles;
SQL error: no such table: articles
drop table comments;
SQL error: no such table: comments

This is caused by the DROP TABLE statements schema.sql contains. As there are no tables in an empty database, none can be dropped, and this results in an error.

Now clean the symfony cache...

symfony cc

... and test your website with the shiny new SQLite backend.

So, what about the other way around. Like, when you already have your SQLite database and need to generate a schema of it?

That, of course, can be done using the propel/creole backend of symfony, too. Unfortunately it's not really documented (or I missed it) how a sqlite DSN is expected to look like. But I found out anyway. And one could say it looks a bit weird:

This is what you need to set in /path/to/yourproject/config/propel.ini if your sqlite databse is located at /path/to/yourproject/data/yourproject.db :

propel.targetPackage = model
propel.project = yourproject
propel.database = sqlite
propel.database.url = sqlite://localhost//path/to/yourproject/data/yourproject.db
propel.output.dir = /path/to/yourproject

After that, you can create the schema:

cd /path/to/yourproject
symfony build-schema

which will then be available at /path/to/yourproject/config/schema.xml