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
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]
-
Re: [sqlite] Sqlite Rename table
On 11/6/07, Valerio Bontempi <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] -
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/
*
Re: [sqlite] Sqlite Rename table
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]
-
[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
