[sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)

[niubao]

Is there a tool that allows you to graphically change sqlite schema as simple
as editing a spreadsheet? For example if I wanted to change a column name
from "my_driving_hours" to "driving_time", instead of writing a line of
code, I can just click on that column and type in the new name, letting the
tool automatically create a new database with the new schema and migrate the
old data into the new one. I found it very useful.

I am not sure if there exists such a tool after a long google search. Could
any pros give some thoughts? Many thanks. 



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/A-graphical-tool-to-handle-sqlite-schema-change-more-than-ALTER-TABLE-tp71338.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)

[Simon Slavin]

On 18 Sep 2013, at 1:49am, niubao  wrote:

> Is there a tool that allows you to graphically change sqlite schema as simple
> as editing a spreadsheet?

What Operating System ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)

[Tim Streater]

On 18 Sep 2013 at 01:49, niubao  wrote: 

> Is there a tool that allows you to graphically change sqlite schema as simple
> as editing a spreadsheet? For example if I wanted to change a column name
> from "my_driving_hours" to "driving_time", instead of writing a line of
> code, I can just click on that column and type in the new name, letting the
> tool automatically create a new database with the new schema and migrate the
> old data into the new one. I found it very useful.
>
> I am not sure if there exists such a tool after a long google search. Could
> any pros give some thoughts? Many thanks.

Navicat for SQLite Lite appears to do this. I copied a db, and used it to 
rename a table. I then used the sqlite command tool on the db and entered 
.schema which showed that the table name was changed. The following had taken 
place:

Before:

  create table mytab1 ( … );
  create table mytab2 ( … );

After:

  create table mytab1 ( … );
  create table "wiggy" ( … );

Note the quotes around the altered name. What it's doing internally I don't 
know, and whether it's actually doing everything it would need to do to do the 
job properly, I don't know either. I didn't do extensive testing, but entering:

  select * from wiggy;

at the command line produced the expected results.

This was Navicat for SQLite Lite 9.1.5 under OS X.

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)

[Bao Niu]

Linux is the only operating system.
On Sep 18, 2013 5:03 AM, "Simon Slavin"  wrote:

>
> On 18 Sep 2013, at 1:49am, niubao  wrote:
>
> > Is there a tool that allows you to graphically change sqlite schema as
> simple
> > as editing a spreadsheet?
>
> What Operating System ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)

[Reid Thompson]

On Wed, 2013-09-18 at 11:02 -0700, Bao Niu wrote:

> > > Is there a tool that allows you to graphically change sqlite schema as
> > simple
> > > as editing a spreadsheet?

https://code.google.com/p/sqlite-manager/   should meet your needs


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)

[niubao]

Thank you very much, but this tool does not allow me to change column
names, and this is not a trivial feature. Am I missing something here?
On Sep 18, 2013 1:44 PM, "jreidthompson [via SQLite]" <
ml-node+s1065341n71349...@n5.nabble.com> wrote:

>
> On Wed, 2013-09-18 at 11:02 -0700, Bao Niu wrote:
>
> > > > Is there a tool that allows you to graphically change sqlite schema
> as
> > > simple
> > > > as editing a spreadsheet?
>
> https://code.google.com/p/sqlite-manager/   should meet your needs
>
>
> ___
> sqlite-users mailing list
> [hidden email] 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> --
>  If you reply to this email, your message will be added to the discussion
> below:
>
> http://sqlite.1065341.n5.nabble.com/A-graphical-tool-to-handle-sqlite-schema-change-more-than-ALTER-TABLE-tp71338p71349.html
>  To unsubscribe from A graphical tool to handle sqlite schema change(more
> than ALTER TABLE), click 
> here
> .
> NAML
>




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/A-graphical-tool-to-handle-sqlite-schema-change-more-than-ALTER-TABLE-tp71338p71350.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)

[Kees Nuyt]

On Wed, 18 Sep 2013 20:46:47 +, Reid Thompson
 wrote:

>
>On Wed, 2013-09-18 at 11:02 -0700, Bao Niu wrote:
>
>> > > Is there a tool that allows you to graphically change sqlite schema as
>> > simple
>> > > as editing a spreadsheet?
>
>https://code.google.com/p/sqlite-manager/   should meet your needs

+1

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)

[Jason H]

It kinda is whe. All you nees is a select into

Sent from Yahoo! Mail on Android

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)

[niubao]

Thank you very much, i actually tried it earlier, but this Firefox tool
does not allow me to change column
names, and this is not a trivial feature that is missing. Am I missing
something here?




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/A-graphical-tool-to-handle-sqlite-schema-change-more-than-ALTER-TABLE-tp71338p71352.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)

[niubao]

I'm sorry I don't quite understand your last reply, would you please
explain a bit more? Thanks.


On Wed, Sep 18, 2013 at 2:01 PM, Jason H [via SQLite] <
ml-node+s1065341n7135...@n5.nabble.com> wrote:

> It kinda is whe. All you nees is a select into
>
> Sent from Yahoo! Mail on Android
>
> ___
> sqlite-users mailing list
> [hidden email] 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> --
>  If you reply to this email, your message will be added to the discussion
> below:
>
> http://sqlite.1065341.n5.nabble.com/A-graphical-tool-to-handle-sqlite-schema-change-more-than-ALTER-TABLE-tp71338p71353.html
>  To unsubscribe from A graphical tool to handle sqlite schema change(more
> than ALTER TABLE), click 
> here
> .
> NAML
>




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/A-graphical-tool-to-handle-sqlite-schema-change-more-than-ALTER-TABLE-tp71338p71354.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)

[Bao Niu]

After a thorough trial I've finally got this firefox add-on to work
properly. And it's great! It basically provides everything that I needed,
except for one: changing foreign key constraints from the gui. Is it
possible to do it from sqlite manager add-on?

And more generally, when a database is already in production and loaded
with records, is it possible to add/remove a foreign key constraint on it?
Thanks.


On Wed, Sep 18, 2013 at 2:26 PM, niubao  wrote:

> I'm sorry I don't quite understand your last reply, would you please
> explain a bit more? Thanks.
>
>
> On Wed, Sep 18, 2013 at 2:01 PM, Jason H [via SQLite] <
> ml-node+s1065341n7135...@n5.nabble.com> wrote:
>
> > It kinda is whe. All you nees is a select into
> >
> > Sent from Yahoo! Mail on Android
> >
> > ___
> > sqlite-users mailing list
> > [hidden email] 
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > --
> >  If you reply to this email, your message will be added to the discussion
> > below:
> >
> >
> http://sqlite.1065341.n5.nabble.com/A-graphical-tool-to-handle-sqlite-schema-change-more-than-ALTER-TABLE-tp71338p71353.html
> >  To unsubscribe from A graphical tool to handle sqlite schema change(more
> > than ALTER TABLE), click here<
> http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=71338&code=bml1YmFvNTZAZ21haWwuY29tfDcxMzM4fDE1NzM1NTM0NjA=
> >
> > .
> > NAML<
> http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml
> >
> >
>
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/A-graphical-tool-to-handle-sqlite-schema-change-more-than-ALTER-TABLE-tp71338p71354.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)

[Simon Slavin]

On 19 Sep 2013, at 3:50am, Bao Niu  wrote:

> After a thorough trial I've finally got this firefox add-on to work
> properly. And it's great! It basically provides everything that I needed,
> except for one: changing foreign key constraints from the gui. Is it
> possible to do it from sqlite manager add-on?

FOREIGN KEYs are a relatively new addition to SQLite.  A lot of apps which work 
with SQLite haven't been updated to cope with them yet.

> And more generally, when a database is already in production and loaded
> with records, is it possible to add/remove a foreign key constraint on it?

SQL does not allow any such thing as 'in production and loaded'.  You can make 
all the kinds of changes at any time, unless they would put the database in an 
inconsistent state.

So, for example, you can add a UNIQUE index after your database has lots of 
data in it ... unless some of that data means that two entries in that index 
would have the same data.  And you can delete a table at any time ... unless a 
foreign key points to that table.

However, FOREIGN KEYs are part of the table definition.  There's no way to 
delete them without deleting the table.  To fake it, the same way that GUI 
fakes changing a column definition, you'd have to define a new table, copy the 
data across, delete the old table, then rename the new table to the old name.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)

[niubao]

Thank you very much Simon, for your detailed and very clear explanation on
this. I wonder if there is some materials, a tutorial or something, that
are dedicated to SQLite schema change for beginners?

There seems to be so many things to consider.




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/A-graphical-tool-to-handle-sqlite-schema-change-more-than-ALTER-TABLE-tp71338p71357.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)

[Peter Haworth]

I have a tool that will do this for you and just about any other schema
changte you can think of while preservbing the data and integrity of your
database. Runs on WIndows and OSX and I could produce a Linux version if
necessary.  Check out SQLiteAdmin at www.lcsql.com
Pete

On Thu, Sep 19, 2013 at 9:00 AM,  wrote:

> Message: 3
> Date: Wed, 18 Sep 2013 13:54:34 -0700 (PDT)
> From: niubao 
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] A graphical tool to handle sqlite schema
>     change(more than ALTER TABLE)
> Message-ID:
> <
> cafluvw4ltqasttefwnmupqvd1ogsqe5o54aetud78+4yodl...@mail.gmail.com>
> Content-Type: text/plain; charset=us-ascii
>
> Thank you very much, but this tool does not allow me to change column
> names, and this is not a trivial feature. Am I missing something here?
>



Pete
lcSQL Software <http://www.lcsql.com>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)

[Kees Nuyt]

On Wed, 18 Sep 2013 20:53:31 -0700 (PDT), niubao 
wrote:

>Thank you very much Simon, for your detailed and very clear explanation on
>this. I wonder if there is some materials, a tutorial or something, that
>are dedicated to SQLite schema change for beginners?
>
>There seems to be so many things to consider.

In the "Structure" tab of the Firefox "SQLite Manager" add-on, you can
export the schema for a table to a text file [1], then edit the text
file and feed it back into the database with the menu item Database /
Import, or with the sqlite command line tool [2].

[1] can be compared with .dump in the sqlite command line tool,
  as in echo .dump | sqlite3 yourdbfile >schemafile.sql
edit schemafile.sql to reflect your modifications

[2] sqlite3 yourdbile http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users