Re: [sqlite] Sqlite Rename table
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 <[EMAIL PROTECTED]>:
>
> @ 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 <[EMAIL PROTECTED]>:
> >
> > 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 < [EMAIL PROTECTED]>:
> > >>
> > >> Hi Valerio,
> > >>
> > >> On Mon, 5 Nov 2007 18:51:20 +0100, "Valerio Bontempi"
> > >> <[EMAIL PROTECTED] > 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 [EMAIL PROTECTED]
> > >>
> > >>
> > -
> >
> > >>
> > >>
> > >
> > >
> >
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> > -
> >
> >
>
>
> --
> *
> Valerio Bontempi
> Blog: http://mithland.wordpress.com/
> *
>
--
*
Valerio Bontempi
Blog: http://mithland.wordpress.com/
*
Re: [sqlite] Sqlite Rename table
@ 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 <[EMAIL PROTECTED]>:
>
> 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 <[EMAIL PROTECTED]>:
> >>
> >> Hi Valerio,
> >>
> >> On Mon, 5 Nov 2007 18:51:20 +0100, "Valerio Bontempi"
> >> <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]
> >>
> >>
> -
> >>
> >>
> >
> >
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
--
*
Valerio Bontempi
Blog: http://mithland.wordpress.com/
*
Re: [sqlite] Sqlite Rename table
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 <[EMAIL PROTECTED]>:
>
>
> Hi Valerio,
>
> On Mon, 5 Nov 2007 18:51:20 +0100, "Valerio Bontempi"
> <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]
>
> -
>
>
--
*
Valerio Bontempi
Blog: http://mithland.wordpress.com/
*
[sqlite] Sqlite Rename table
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
