[sqlite] applyng schema changes to several databases
On Tue, Feb 16, 2016 at 9:51 PM, Roger Binns wrote:
> The way I (and many others) do it is to use the user_version. It is
> an integer that starts out as zero, and can be read or written with
> pragma user_version. Startup code then looks like this:
>
> if user_version()==0 {
> BEGIN;
> CREATE TABLE ;
> CREATE INDEX ;
> pragma user_version=1;
> COMMIT;
> }
Thank you guys for opening my mind on the usage of pragmas.
The next question is: do I have a way to conditionally place the
pragma test into an sql file to make sqlite apply it? The above syntax
does not look familiar to me, and in the meantime I've wrapped the
user_version pragma get/set into a shell script.
Thanks,
Luca
[sqlite] applyng schema changes to several databases
On 17 Feb 2016, at 11:38am, Luca Ferrari wrote: > > The next question is: do I have a way to conditionally place the > pragma test into an sql file to make sqlite apply it? No. You can store SQL commands in a table if you like, but there's no way to make SQLite automatically execute them directly. You have to have your software do it. Simon.
[sqlite] applyng schema changes to several databases
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 17/02/16 03:38, Luca Ferrari wrote: > The above syntax> does not look familiar to me, It is made up to be concise and get the point across. > and in the meantime I've wrapped the user_version pragma get/set > into a shell script. You can't do if statements in SQL (beyond CASE), so a pure SQL solution isn't reasonable. While you can (heroically) do everything in shell script, I'd recommend you use a higher level language for easier testing, clearer semantics etc. For example any of TCL, Python, Perl, Ruby, PHP will meet your needs. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v2 iEYEARECAAYFAlbEuLcACgkQmOOfHg372QTd1gCgh0B5UO/Rb6zEnK3US0+V4oWz GYIAoJX31S2pg73eTtTsKiBT9whwofF/ =Ximu -END PGP SIGNATURE-
[sqlite] applyng schema changes to several databases
On 2/16/16, Roger Binns wrote:
>
> The way I (and many others) do it is to use the user_version. It is
> an integer that starts out as zero, and can be read or written with
> pragma user_version. Startup code then looks like this:
>
> if user_version()==0 {
> BEGIN;
> CREATE TABLE ;
> CREATE INDEX ;
> pragma user_version=1;
> COMMIT;
> }
Another approach is to use the sqlite3_table_column_metadata()
interface to check for the existence of various tables and columns,
and then run whatever CREATE TABLE and ALTER TABLE ADD COLUMN commands
are necessary based on those findings.
Whichever works best for you
--
D. Richard Hipp
drh at sqlite.org
[sqlite] applyng schema changes to several databases
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 15/02/16 01:01, Luca Ferrari wrote:
> While I'm pretty sure a simple sheel script that will execute,
> file per file, the alter table (within a transaction) will do the
> job I'm wondering if there's a better approach or a more automated
> one.
The way I (and many others) do it is to use the user_version. It is
an integer that starts out as zero, and can be read or written with
pragma user_version. Startup code then looks like this:
if user_version()==0 {
BEGIN;
CREATE TABLE ;
CREATE INDEX ;
pragma user_version=1;
COMMIT;
}
if user_version()==1 {
BEGIN;
CREATE TABLE ;
ALTER TABLE .;
pragma user_version=2;
COMMIT;
}
if user_version()==2 {
BEGIN;
DELETE INDEX ;
CREATE TABLE ;
pragma user_version=3;
COMMIT;
}
This way the schema will always end up as desired, even if the program
terminated while updating a schema. Even a restored backup will
adjust nicely.
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v2
iEYEARECAAYFAlbDi78ACgkQmOOfHg372QSgPACgxIpKdSlzUVznymQThe9aqqJM
pGkAnj99zXJbzO1Tm6/uyuIgXCt5jq42
=Y/zw
-END PGP SIGNATURE-
[sqlite] applyng schema changes to several databases
On 2016/02/15 11:01 AM, Luca Ferrari wrote: > Hi all, > this could sound trivial but I've got a few hundreds SQLite 3 database > files, all with the same schema, that I need to alter adding a few > columns here and there. > While I'm pretty sure a simple sheel script that will execute, file > per file, the alter table (within a transaction) will do the job I'm > wondering if there's a better approach or a more automated one. > > Any suggestion is welcome. If all the files are in the same folder and use the same extension, then a simple bash/batch script iterating the files feeding it into sqlite3 cli along with the column-altering script - is by far the easiest. If the file distribution is more complex, or table names differ, perhaps a custom little C program or something that allows you to pick files in bulk and then iterates the list pushing the SQL alterations to each one. I have in fact done this recently, but only on Windows in a small gui. If you are doing this on Windows, you are welcome to have the tool. As an aside, may I ask why the hundreds of similar db files? if they are in the same place, and the design doesn't absolutely require this fragmentation, it would be far more efficient to have one DB, in the table add an identifying column (perhaps the old DB name, but more efficient would be an Integer) to show where the row belongs to. This amalgamation would be easy to do too, for bulk files. Cheers, Ryan
[sqlite] applyng schema changes to several databases
Hi all, this could sound trivial but I've got a few hundreds SQLite 3 database files, all with the same schema, that I need to alter adding a few columns here and there. While I'm pretty sure a simple sheel script that will execute, file per file, the alter table (within a transaction) will do the job I'm wondering if there's a better approach or a more automated one. Any suggestion is welcome. Thanks, Luca
