Sqlite Rename table

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Sqlite Rename table

Valerio Bontempi
Hi All,

I'm new in your mailing list.
I need to use sqlite and not sqlite3 because of php still support only the
first version.
I read that sqlite3 support table renaming with common sql syntax
alter table table1 rename to table2

but, although it works fine on sqlite3, it doesn't work on sqlite

Can anyone confirm that table renaming isn't supported in sqlite first
version?
Moreover, if so, is there another way to do it?

Thanks and regards

Valerio Bontempi
Reply | Threaded
Open this post in threaded view
|

Re: Sqlite Rename table

Kees Nuyt

Hi Valerio,

On Mon, 5 Nov 2007 18:51:20 +0100, "Valerio Bontempi"
<[hidden email]> wrote:

>Hi All,
>
>I'm new in your mailing list.
>I need to use sqlite and not sqlite3 because of php still support only the
>first version.
>I read that sqlite3 support table renaming with common sql syntax
>alter table table1 rename to table2
>
>but, although it works fine on sqlite3, it doesn't work on sqlite
>
>Can anyone confirm that table renaming isn't supported in sqlite first
>version?
>Moreover, if so, is there another way to do it?
>
>Thanks and regards
>
>Valerio Bontempi

If the name of your table plus a leading and trailing space
doesn't exist anywhere in your data, you could do :

Windows:
sqlite old_db .dump |
awk "{sub(/ oldtable /,\" newtable \");print}" |
sqlite new_db

Unix:
sqlite old_db .dump |
awk '{sub(/ oldtable /," newtable ");print}' |
sqlite new_db

(all on one line, but I added linefeeds after every pipe
character for clarity)

Untested, parenthesis in positions where i assume spaces might
cause some problems, but you get the idea.

HTH
--
  (  Kees Nuyt
  )
c[_]

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Sqlite Rename table

Valerio Bontempi
Hi Kees,

thanks for your solution, it is a very interesting solution.
But I need to rename a table using sql from php.
(this is also the reason for my need of sqlite and not sqlite3, not
supported yet by php)

Thanks a lot

Valerio

2007/11/5, Kees Nuyt <[hidden email]>:

>
>
> Hi Valerio,
>
> On Mon, 5 Nov 2007 18:51:20 +0100, "Valerio Bontempi"
> <[hidden email]> wrote:
>
> >Hi All,
> >
> >I'm new in your mailing list.
> >I need to use sqlite and not sqlite3 because of php still support only
> the
> >first version.
> >I read that sqlite3 support table renaming with common sql syntax
> >alter table table1 rename to table2
> >
> >but, although it works fine on sqlite3, it doesn't work on sqlite
> >
> >Can anyone confirm that table renaming isn't supported in sqlite first
> >version?
> >Moreover, if so, is there another way to do it?
> >
> >Thanks and regards
> >
> >Valerio Bontempi
>
> If the name of your table plus a leading and trailing space
> doesn't exist anywhere in your data, you could do :
>
> Windows:
> sqlite old_db .dump |
> awk "{sub(/ oldtable /,\" newtable \");print}" |
> sqlite new_db
>
> Unix:
> sqlite old_db .dump |
> awk '{sub(/ oldtable /," newtable ");print}' |
> sqlite new_db
>
> (all on one line, but I added linefeeds after every pipe
> character for clarity)
>
> Untested, parenthesis in positions where i assume spaces might
> cause some problems, but you get the idea.
>
> HTH
> --
>   (  Kees Nuyt
>   )
> c[_]
>
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [hidden email]
>
> -----------------------------------------------------------------------------
>
>


--
*********************************************
Valerio Bontempi
Blog: http://mithland.wordpress.com/
*********************************************
Reply | Threaded
Open this post in threaded view
|

Re: Sqlite Rename table

Puneet Kishor-2
On 11/6/07, Valerio Bontempi <[hidden email]> wrote:

> Hi Kees,
>
> thanks for your solution, it is a very interesting solution.
> But I need to rename a table using sql from php.
> (this is also the reason for my need of sqlite and not sqlite3, not
> supported yet by php)
>
> Thanks a lot
>
> Valerio
>

Use the SQL suggested below in your PHP program. Don't worry about
sqlite3 (that is all I have). Just change that to sqlite, and it
should work.

Lucknow:~/Data/punkish punkish$ sqlite3 foo
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> CREATE TABLE foo (a, b);
sqlite> .s
CREATE TABLE foo (a, b);
sqlite> INSERT INTO foo VALUES (1, 'blah');
sqlite> INSERT INTO foo VALUES (2, 'booh');
sqlite> SELECT * FROM foo;
1|blah
2|booh
sqlite> BEGIN TRANSACTION;
sqlite> CREATE TEMPORARY TABLE foo_backup (a, b);
sqlite> INSERT INTO foo_backup SELECT a, b FROM foo;
sqlite> DROP TABLE foo;
sqlite> CREATE TABLE bar (a, b);
sqlite> INSERT INTO bar SELECT a, b FROM foo_backup;
sqlite> DROP TABLE foo_backup;
sqlite> COMMIT;
sqlite> SELECT * FROM bar;
1|blah
2|booh
sqlite> .s
CREATE TABLE bar (a, b);
sqlite> .q

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Sqlite Rename table

John Stanton-3
In reply to this post by Valerio Bontempi
Sqlite3 is supported by PHP using PDO.

Valerio Bontempi wrote:

> Hi Kees,
>
> thanks for your solution, it is a very interesting solution.
> But I need to rename a table using sql from php.
> (this is also the reason for my need of sqlite and not sqlite3, not
> supported yet by php)
>
> Thanks a lot
>
> Valerio
>
> 2007/11/5, Kees Nuyt <[hidden email]>:
>>
>> Hi Valerio,
>>
>> On Mon, 5 Nov 2007 18:51:20 +0100, "Valerio Bontempi"
>> <[hidden email]> wrote:
>>
>>> Hi All,
>>>
>>> I'm new in your mailing list.
>>> I need to use sqlite and not sqlite3 because of php still support only
>> the
>>> first version.
>>> I read that sqlite3 support table renaming with common sql syntax
>>> alter table table1 rename to table2
>>>
>>> but, although it works fine on sqlite3, it doesn't work on sqlite
>>>
>>> Can anyone confirm that table renaming isn't supported in sqlite first
>>> version?
>>> Moreover, if so, is there another way to do it?
>>>
>>> Thanks and regards
>>>
>>> Valerio Bontempi
>> If the name of your table plus a leading and trailing space
>> doesn't exist anywhere in your data, you could do :
>>
>> Windows:
>> sqlite old_db .dump |
>> awk "{sub(/ oldtable /,\" newtable \");print}" |
>> sqlite new_db
>>
>> Unix:
>> sqlite old_db .dump |
>> awk '{sub(/ oldtable /," newtable ");print}' |
>> sqlite new_db
>>
>> (all on one line, but I added linefeeds after every pipe
>> character for clarity)
>>
>> Untested, parenthesis in positions where i assume spaces might
>> cause some problems, but you get the idea.
>>
>> HTH
>> --
>>   (  Kees Nuyt
>>   )
>> c[_]
>>
>>
>> -----------------------------------------------------------------------------
>> To unsubscribe, send email to [hidden email]
>>
>> -----------------------------------------------------------------------------
>>
>>
>
>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Sqlite Rename table

Valerio Bontempi
@ Kishor:
thank you for the idea:
although it wasn't perfectly what I needed (I have to write a generic method
to rename a table, so I don't know the structure of the table before) it
took me the right idea and lastly I used the following sql:

create table new as select * from old

It still doesn't allow to recreate the exact structure of the prevoius table
(eg indexes and keys) but it should be useful for my needs.
:-)

@John: thanks a lot for your suggest :-)
in a future release of our opensource project probably we will translate our
database support class (mysql, postgres and sqlite) using PDO in order to
use sqlite3


Regards

Valerio


2007/11/6, John Stanton <[hidden email]>:

>
> Sqlite3 is supported by PHP using PDO.
>
> Valerio Bontempi wrote:
> > Hi Kees,
> >
> > thanks for your solution, it is a very interesting solution.
> > But I need to rename a table using sql from php.
> > (this is also the reason for my need of sqlite and not sqlite3, not
> > supported yet by php)
> >
> > Thanks a lot
> >
> > Valerio
> >
> > 2007/11/5, Kees Nuyt <[hidden email]>:
> >>
> >> Hi Valerio,
> >>
> >> On Mon, 5 Nov 2007 18:51:20 +0100, "Valerio Bontempi"
> >> <[hidden email]> wrote:
> >>
> >>> Hi All,
> >>>
> >>> I'm new in your mailing list.
> >>> I need to use sqlite and not sqlite3 because of php still support only
> >> the
> >>> first version.
> >>> I read that sqlite3 support table renaming with common sql syntax
> >>> alter table table1 rename to table2
> >>>
> >>> but, although it works fine on sqlite3, it doesn't work on sqlite
> >>>
> >>> Can anyone confirm that table renaming isn't supported in sqlite first
> >>> version?
> >>> Moreover, if so, is there another way to do it?
> >>>
> >>> Thanks and regards
> >>>
> >>> Valerio Bontempi
> >> If the name of your table plus a leading and trailing space
> >> doesn't exist anywhere in your data, you could do :
> >>
> >> Windows:
> >> sqlite old_db .dump |
> >> awk "{sub(/ oldtable /,\" newtable \");print}" |
> >> sqlite new_db
> >>
> >> Unix:
> >> sqlite old_db .dump |
> >> awk '{sub(/ oldtable /," newtable ");print}' |
> >> sqlite new_db
> >>
> >> (all on one line, but I added linefeeds after every pipe
> >> character for clarity)
> >>
> >> Untested, parenthesis in positions where i assume spaces might
> >> cause some problems, but you get the idea.
> >>
> >> HTH
> >> --
> >>   (  Kees Nuyt
> >>   )
> >> c[_]
> >>
> >>
> >>
> -----------------------------------------------------------------------------
> >> To unsubscribe, send email to [hidden email]
> >>
> >>
> -----------------------------------------------------------------------------
> >>
> >>
> >
> >
>
>
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [hidden email]
>
> -----------------------------------------------------------------------------
>
>


--
*********************************************
Valerio Bontempi
Blog: http://mithland.wordpress.com/
*********************************************
Reply | Threaded
Open this post in threaded view
|

Re: Sqlite Rename table

Valerio Bontempi
Hi All,

about renaming table in sqlite (not sqlite3)
we can use this sql
'SELECT sql,name,type FROM sqlite_master WHERE tbl_name = 'table' ORDER BY
type DESC;'
to create a new table like the first one, and then use insert into new table
from select * from the previous one.
By this way we can keep all the structure and the indexes of the previous
table.



2007/11/6, Valerio Bontempi <[hidden email]>:

>
> @ Kishor:
> thank you for the idea:
> although it wasn't perfectly what I needed (I have to write a generic
> method to rename a table, so I don't know the structure of the table before)
> it took me the right idea and lastly I used the following sql:
>
> create table new as select * from old
>
> It still doesn't allow to recreate the exact structure of the prevoius
> table (eg indexes and keys) but it should be useful for my needs.
> :-)
>
> @John: thanks a lot for your suggest :-)
> in a future release of our opensource project probably we will translate
> our database support class (mysql, postgres and sqlite) using PDO in order
> to use sqlite3
>
>
> Regards
>
> Valerio
>
>
> 2007/11/6, John Stanton <[hidden email]>:
> >
> > Sqlite3 is supported by PHP using PDO.
> >
> > Valerio Bontempi wrote:
> > > Hi Kees,
> > >
> > > thanks for your solution, it is a very interesting solution.
> > > But I need to rename a table using sql from php.
> > > (this is also the reason for my need of sqlite and not sqlite3, not
> > > supported yet by php)
> > >
> > > Thanks a lot
> > >
> > > Valerio
> > >
> > > 2007/11/5, Kees Nuyt < [hidden email]>:
> > >>
> > >> Hi Valerio,
> > >>
> > >> On Mon, 5 Nov 2007 18:51:20 +0100, "Valerio Bontempi"
> > >> <[hidden email] > wrote:
> > >>
> > >>> Hi All,
> > >>>
> > >>> I'm new in your mailing list.
> > >>> I need to use sqlite and not sqlite3 because of php still support
> > only
> > >> the
> > >>> first version.
> > >>> I read that sqlite3 support table renaming with common sql syntax
> > >>> alter table table1 rename to table2
> > >>>
> > >>> but, although it works fine on sqlite3, it doesn't work on sqlite
> > >>>
> > >>> Can anyone confirm that table renaming isn't supported in sqlite
> > first
> > >>> version?
> > >>> Moreover, if so, is there another way to do it?
> > >>>
> > >>> Thanks and regards
> > >>>
> > >>> Valerio Bontempi
> > >> If the name of your table plus a leading and trailing space
> > >> doesn't exist anywhere in your data, you could do :
> > >>
> > >> Windows:
> > >> sqlite old_db .dump |
> > >> awk "{sub(/ oldtable /,\" newtable \");print}" |
> > >> sqlite new_db
> > >>
> > >> Unix:
> > >> sqlite old_db .dump |
> > >> awk '{sub(/ oldtable /," newtable ");print}' |
> > >> sqlite new_db
> > >>
> > >> (all on one line, but I added linefeeds after every pipe
> > >> character for clarity)
> > >>
> > >> Untested, parenthesis in positions where i assume spaces might
> > >> cause some problems, but you get the idea.
> > >>
> > >> HTH
> > >> --
> > >>   (  Kees Nuyt
> > >>   )
> > >> c[_]
> > >>
> > >>
> > >>
> > -----------------------------------------------------------------------------
> >
> > >> To unsubscribe, send email to [hidden email]
> > >>
> > >>
> > -----------------------------------------------------------------------------
> >
> > >>
> > >>
> > >
> > >
> >
> >
> >
> > -----------------------------------------------------------------------------
> > To unsubscribe, send email to [hidden email]
> >
> > -----------------------------------------------------------------------------
> >
> >
>
>
> --
> *********************************************
> Valerio Bontempi
> Blog: http://mithland.wordpress.com/
> *********************************************
>



--
*********************************************
Valerio Bontempi
Blog: http://mithland.wordpress.com/
*********************************************