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)

[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)

[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=71338=bml1YmFvNTZAZ21haWwuY29tfDcxMzM4fDE1NzM1NTM0NjA=
> >
> > .
> > NAML<
> http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer=instant_html%21nabble%3Aemail.naml=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace=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)

[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)

[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)

[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)

[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)

[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)

[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)

[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] SQLite clusters?

[JFC Morfin]

At 15:58 17/09/2013, Jason H wrote:
That's the whole point of using SQLite, it's not 'big iron' - it's a 
bunch of iron filings working together.
I'm just suggesting the amount of work to get such a thing going is 
not that much work, but I wanted to float it here to see if there 
were any good reasons why it was a bad idea. :-)


Hi! Folks,

I happen to read this question while I consider a similar idea and I 
am blocked because I do not know anything about the SQLite 
architectural behavior. I found a funny name to call this kind of 
issue: SQLity, as a portmanteau word for "Swarm QuaLity". How, when 
and why a bunch of SQLite modular uses could provide or not a better, 
simpler, cheaper solution than a big one.


I am not competent to discuss Jason's idea directly, so I wll only 
explain my idea for you guys to see if there is some form of 
convergence between them (and how to implement mine). From the user's 
point of view I understand that question is the same: there is a big 
piece of cake, will someone swallow it or a swarm eat it faster?


My need is for a 3.0 wiki or "dikty" solution based on MediaWiki 
("diktyos" in Greek means networked system) using SQLite. A wiki is a 
databased centered service, a dikty is a  wikipage centered use. Some 
opf the differences are:


- in wikipedia there is one page per concept so this page must be 
neutral. In diktypedia there can be an unlimited number so everyone 
can speak up his mind.
- in wikipedia there is a single big main system with subhosts, in 
diktypedia there can be an unlimited number of machine and a DNS 
forming a P system.
- in wikipedia there is a single back-up, in diktypedia everyone 
makes his own back-up and can move his data around the way he want.
- in wikipedia there is a big yearly budget, in diktypedia everyone 
can participate from his site.
- in wikipedia there is a need for controllers, in diktypedia 
everyone can create his own commented semanticpedia, in any language, 
from any opinion.
- in wikipedia rights of access are per system, in diktypedia it is 
the same, but the systems are micro/individual systems; like blogs.

- in wikipedia there is a blog per mediawiki project

Due to the way MediaWiki is supported and my limited resources, I 
thought the best would be swarms of basic MediaWiki modules each 
having its SQLite small/medium size file (local protection insured) 
and one can move around and easily back-up and replicate. For example 
I can have my dikitypages duplicated and maintained on my machine, or 
an USB key.


My problem is the server's architecture and CPU load. Ideally I would 
need a MediaWiki/PHP machine where I could just load or create a set 
of diktypages in loading/setting an SQLite file? But I have no idea 
how to organize this and how it will work.


If I add what I gather from Jason's question and mine, I would ask 
about the SQLity idea as: can I use in parallel clusters of multiple 
SQLite specialized (and networked) tasks (actually for the users they 
appears as real [my case] or virtual [as I understand Jason] 
sub-tasks)? Is that sound or crazy? What are the limits? What are the 
cons and pros? (in the case of diktypedia the big and most accessed 
systems can used other DBMS's).


jfc



Hi, Jason,
It might be that this is a little bit to big for sqLITE.
Maybe a "big iron" database like PostgreSQL or the Greenplum 
Database will fit your requirements better.

Best regards
Markus Schaber





> -Ursprüngliche Nachricht-
> Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] Im Auftrag von Jason H
> Gesendet: Montag, 16. September 2013 23:04
> An: sqlite-users@sqlite.org
> Betreff: [sqlite] SQLite clusters?
>
> I'm transitioning my job from embedded space to Hadoop space. I was
> wondering if it is possible to come up with a SQLite cluster
> adaptation.
>
> I will give you a crash course in hadoop. Basically we get a very
> large CSV, which is chopped up into 64MB chunks, and distributed to a
> number of nodes. The file is actually replicated 2 times for a total
> of 3 copies of all chunks on the cluster (no chunk is repeatedly
> stored on the same node). Then MapReduce logic is run, and the
> results are combined. Instrumental to this is the keys are returned
> in sorted order.
>
> All of this is done in java (70% slower than C, on average, and with
> some non-trivial start-up cost). Everyone is clamoring for SQL to be
> run on the nodes. Hive attempts to leverage SQL, and is successful to
> some degree. But being able to use Full SQL would be a huge
> improvement. Akin to Hadoop is HBase
>
> HBase is similar with Hadoop, but it approaches things in a more
> conventional columnar format It a copy of "BigTable" form google..
> Here, the notion of "column families" is important because column
> families are files. A row is made up of keys, at leas one column
> family. There is an implied join between the key, and each column
> family. As the table is viewed 

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)

[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

[sqlite] sqlite4 LSM storage: how many pages will be read in point search?

[baibaichen]

Hi all I read and debug sqlite4 codes for several days, the use of factional
cascading pointer will  reduce the overall key comparison numbers in the
point query, but it wouldn't decease the numbers of accessed pages. In other
words, say, we have L levels in the LSM tree, we will access L Pages  to
find the searched key in the worst case (assume all btree index pages are in
memory).* It means we have to do L random IOs in the worst case*.  If i
understand correct about page ios,  why don't use bloom filter to reduce
random io?Thanks



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/sqlite4-LSM-storage-how-many-pages-will-be-read-in-point-search-tp71343.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 tool that allows you to graphically change sqlite schema

[jcd]

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.


Give SQLite Expert a try if you're on Windows. It might run well under 
Wine for Linux users (untested by me).


http://www.sqliteexpert.com/

--
JcD 


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