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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

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.
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3

On 18 Sep 2013, at 1:49am, niubao <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Tim Streater-3
In reply to this post by niubao
On 18 Sep 2013 at 01:49, niubao <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

niubao
In reply to this post by Simon Slavin-3
Linux is the only operating system.
On Sep 18, 2013 5:03 AM, "Simon Slavin" <[hidden email]> wrote:

>
> On 18 Sep 2013, at 1:49am, niubao <[hidden email]> 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

jreidthompson

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
Reply | Threaded
Open this post in threaded view
|

Re: 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]" <[hidden email]> 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



To unsubscribe from A graphical tool to handle sqlite schema change(more than ALTER TABLE), click here.
NAML
Reply | Threaded
Open this post in threaded view
|

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

Kees Nuyt
In reply to this post by jreidthompson
On Wed, 18 Sep 2013 20:46:47 +0000, Reid Thompson
<[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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?

Reply | Threaded
Open this post in threaded view
|

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

Jason H
In reply to this post by niubao
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
Reply | Threaded
Open this post in threaded view
|

Re: 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] <[hidden email]> 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



To unsubscribe from A graphical tool to handle sqlite schema change(more than ALTER TABLE), click here.
NAML

Reply | Threaded
Open this post in threaded view
|

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

niubao
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 <[hidden email]> 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] <
> [hidden email]> 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://user/SendEmail.jtp?type=node&node=71353&i=0>
> > 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<
>
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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3

On 19 Sep 2013, at 3:50am, Bao Niu <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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.

Reply | Threaded
Open this post in threaded view
|

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

phaworth
In reply to this post by niubao
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, <[hidden email]> wrote:

> Message: 3
> Date: Wed, 18 Sep 2013 13:54:34 -0700 (PDT)
> From: niubao <[hidden email]>
> To: [hidden email]
> Subject: Re: [sqlite] A graphical tool to handle sqlite schema
>         change(more than ALTER TABLE)
> Message-ID:
>         <
> [hidden email]>
> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Kees Nuyt
In reply to this post by niubao
On Wed, 18 Sep 2013 20:53:31 -0700 (PDT), niubao <[hidden email]>
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 <schemafile.sql

Hope this helps

--
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users