Re: [sqlite] Schema updates across threads in WAL & multithread mode

[Ben Asher]

Hi D. Richard Hipp. Sorry for the late reply. I had some email settings
wrong somewhere, and I didn't realize I had a reply until I remembered this
and checked the archives months later. You had written:

>>
>> Is there something we need to do proactively to ensure that schema update
>> appears immediately from other threads?
>>
>
>When a database connection has a read transaction open, it continues
>to see a snapshot of the database as it existed when the read
>transaction was first opened.  Outside changes to the database,
>including schema changes, are invisible to the connection holding the
>transaction.  This is the "I" in "ACID".
>
>As soon as you release the read transaction and start another, all
>changes will immediately become visible.
>
>If you are not deliberately holding a read transaction open, perhaps
>you are doing so accidentally by failing to sqlite3_reset() or
>sqlite3_finalize() a prepared statement.  You can perhaps figure out
>which statement that is by running:
>
>   SELECT sql FROM sqlite_stmt WHERE busy;

That is very interesting. We definitely don't expect a read transaction to
be open at that point. This is super helpful. I'll check on this next week.

Thank you!

Ben


On Fri, Aug 16, 2019 at 12:49 PM Ben Asher  wrote:

> To clarify, we add a column on our writer connection, and then "SELECT *
> FROM table" on the reader connection does not include the column that was
> added.
>
> Ben
>
> On Fri, Aug 16, 2019 at 11:32 AM Ben Asher  wrote:
>
>> Hi folks! We're running (sqlite 3.27.2) into an issue where we make a
>> schema update (adding a column to a table) on our writer connection, but
>> then the schema update isn't immediately available on the read-only
>> connections that we use on other threads, which causes a crash in our
>> application (app expects the column to exist at that point). I've verified
>> that the column does indeed get added, and everything works fine after
>> restarting the application (i.e. all connections loaded fresh pickup the
>> schema update).
>>
>> Is there something we need to do proactively to ensure that schema update
>> appears immediately from other threads?
>>
>> Some notes about our setup:
>>
>> sqlite 3.27.2
>> Using multithread mode (SQLITE_OPEN_NOMUTEX)
>> Using WAL mode
>>
>> Thanks!
>>
>> Ben
>>
>
>
> --
> Ben
>


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

Re: [sqlite] Schema updates across threads in WAL & multithread mode

[Ben Asher]

Hi Simon. Sorry for the late reply. I had some email settings wrong
somewhere, and I didn't realize I had a reply until I remembered this and
checked the archives months later. You had written:

>Can I ask the maximum number of columns you expect to exist in that table
?  I'm working up to trying to convince you to add a row to something
instead, but I want to make sure you're doing what I think you're doing.
>
>Other people may be able to answer your question.

It's a small number of columns– less than 10. The table already has data,
and we added the column with a default value.

Thanks!

Ben

On Fri, Aug 16, 2019 at 12:49 PM Ben Asher  wrote:

> To clarify, we add a column on our writer connection, and then "SELECT *
> FROM table" on the reader connection does not include the column that was
> added.
>
> Ben
>
> On Fri, Aug 16, 2019 at 11:32 AM Ben Asher  wrote:
>
>> Hi folks! We're running (sqlite 3.27.2) into an issue where we make a
>> schema update (adding a column to a table) on our writer connection, but
>> then the schema update isn't immediately available on the read-only
>> connections that we use on other threads, which causes a crash in our
>> application (app expects the column to exist at that point). I've verified
>> that the column does indeed get added, and everything works fine after
>> restarting the application (i.e. all connections loaded fresh pickup the
>> schema update).
>>
>> Is there something we need to do proactively to ensure that schema update
>> appears immediately from other threads?
>>
>> Some notes about our setup:
>>
>> sqlite 3.27.2
>> Using multithread mode (SQLITE_OPEN_NOMUTEX)
>> Using WAL mode
>>
>> Thanks!
>>
>> Ben
>>
>
>
> --
> Ben
>


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

Re: [sqlite] Schema updates across threads in WAL & multithread mode

[Ben Asher]

Hi José. Sorry for the late reply. I had some email settings wrong
somewhere, and I didn't realize I had a reply until I remembered this and
checked the archives months later. You had asked:

> Are you using BEGIN and END before and after the schema update?

Yes that's correct. We are doing the schema updates inside of an explicit
transaction.

Thanks!

Ben

On Fri, Aug 16, 2019 at 12:49 PM Ben Asher  wrote:

> To clarify, we add a column on our writer connection, and then "SELECT *
> FROM table" on the reader connection does not include the column that was
> added.
>
> Ben
>
> On Fri, Aug 16, 2019 at 11:32 AM Ben Asher  wrote:
>
>> Hi folks! We're running (sqlite 3.27.2) into an issue where we make a
>> schema update (adding a column to a table) on our writer connection, but
>> then the schema update isn't immediately available on the read-only
>> connections that we use on other threads, which causes a crash in our
>> application (app expects the column to exist at that point). I've verified
>> that the column does indeed get added, and everything works fine after
>> restarting the application (i.e. all connections loaded fresh pickup the
>> schema update).
>>
>> Is there something we need to do proactively to ensure that schema update
>> appears immediately from other threads?
>>
>> Some notes about our setup:
>>
>> sqlite 3.27.2
>> Using multithread mode (SQLITE_OPEN_NOMUTEX)
>> Using WAL mode
>>
>> Thanks!
>>
>> Ben
>>
>
>
> --
> Ben
>


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

Re: [sqlite] Schema updates across threads in WAL & multithread mode

[Ben Asher]

To clarify, we add a column on our writer connection, and then "SELECT *
FROM table" on the reader connection does not include the column that was
added.

Ben

On Fri, Aug 16, 2019 at 11:32 AM Ben Asher  wrote:

> Hi folks! We're running (sqlite 3.27.2) into an issue where we make a
> schema update (adding a column to a table) on our writer connection, but
> then the schema update isn't immediately available on the read-only
> connections that we use on other threads, which causes a crash in our
> application (app expects the column to exist at that point). I've verified
> that the column does indeed get added, and everything works fine after
> restarting the application (i.e. all connections loaded fresh pickup the
> schema update).
>
> Is there something we need to do proactively to ensure that schema update
> appears immediately from other threads?
>
> Some notes about our setup:
>
> sqlite 3.27.2
> Using multithread mode (SQLITE_OPEN_NOMUTEX)
> Using WAL mode
>
> Thanks!
>
> Ben
>


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

Re: [sqlite] Schema updates across threads in WAL & multithread mode

[Richard Hipp]

On 8/16/19, Ben Asher  wrote:
>
> Is there something we need to do proactively to ensure that schema update
> appears immediately from other threads?
>

When a database connection has a read transaction open, it continues
to see a snapshot of the database as it existed when the read
transaction was first opened.  Outside changes to the database,
including schema changes, are invisible to the connection holding the
transaction.  This is the "I" in "ACID".

As soon as you release the read transaction and start another, all
changes will immediately become visible.

If you are not deliberately holding a read transaction open, perhaps
you are doing so accidentally by failing to sqlite3_reset() or
sqlite3_finalize() a prepared statement.  You can perhaps figure out
which statement that is by running:

   SELECT sql FROM sqlite_stmt WHERE busy;


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Schema updates across threads in WAL & multithread mode

[Simon Slavin]

On 16 Aug 2019, at 7:32pm, Ben Asher  wrote:

> we make a
> schema update (adding a column to a table) on our writer connection

Can I ask the maximum number of columns you expect to exist in that table ?  
I'm working up to trying to convince you to add a row to something instead, but 
I want to make sure you're doing what I think you're doing.

Other people may be able to answer your question.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Schema updates across threads in WAL & multithread mode

[Jose Isaias Cabrera]

Ben Asher, on Friday, August 16, 2019 02:32 PM, wrote...
>
> Hi folks! We're running (sqlite 3.27.2) into an issue where we make a
> schema update (adding a column to a table) on our writer connection, but
> then the schema update isn't immediately available on the read-only
> connections that we use on other threads, which causes a crash in our
> application (app expects the column to exist at that point). I've verified
> that the column does indeed get added, and everything works fine after
> restarting the application (i.e. all connections loaded fresh pickup the
> schema update).
>
> Is there something we need to do proactively to ensure that schema update
> appears immediately from other threads?

Are you using BEGIN and END before and after the schema update?

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

[sqlite] Schema updates across threads in WAL & multithread mode

[Ben Asher]

Hi folks! We're running (sqlite 3.27.2) into an issue where we make a
schema update (adding a column to a table) on our writer connection, but
then the schema update isn't immediately available on the read-only
connections that we use on other threads, which causes a crash in our
application (app expects the column to exist at that point). I've verified
that the column does indeed get added, and everything works fine after
restarting the application (i.e. all connections loaded fresh pickup the
schema update).

Is there something we need to do proactively to ensure that schema update
appears immediately from other threads?

Some notes about our setup:

sqlite 3.27.2
Using multithread mode (SQLITE_OPEN_NOMUTEX)
Using WAL mode

Thanks!

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

[sqlite] Schema-less JSON SQLite DB?

[Eduardo Morras]

On Wed, 15 Jul 2015 17:22:36 +0200
Sergej Jure?ko  wrote:

> An idea I?ve had a while ago was to implement functions for json
> documents (using sqlite3_create_function_v2)
> 
> Json would be stored in a text column. You would need to define 2
> functions:
> - docget(document,key1,subval1,subval2,?)
> - docset(document,key1,subval1,subval2,..,val)
> 
> Last parameter of docset is value you wish to set.
> 
> So for instance one would write
> INSERT INTO mytable VALUES (1,?{key : 10, subdoc : {subkey : ?a"}}?);
> SELECT id,doc FROM mytable WHERE docget(doc,?key") > 10;
> SELECT id,doc FROM mytable WHERE docget(doc,?key?,?subdoc?,?subkey?)
> = ?a?; UPDATE doc FROM mytable WHERE id=1 SET docset(doc,?key?,12);
> 
> One could even implement indexes on keys within documents using
> additional tables.
> 
> What do you guys think? Is it stupid, could it be improved?

Perhaps Dr. Hipps, R. unql implementation should be resurrected or at least, 
used as good/bad example. It has an extension to sql to query unstructured data 
and a layer on top sqlite to store json data. It's abandonware now afaik.

http://unql.sqlite.org/index.html/wiki?name=UnQL
http://www.dataversity.net/unql-a-standardized-query-language-for-nosql-databases/
http://www.couchbase.com/press-releases/unql-query-language


> 
> Sergej


---   ---
Eduardo Morras 

[sqlite] Schema-less JSON SQLite DB?

[Sergej Jurečko]

I will probably get around to doing it in the next few months. Hopefully I
have the time, right now I'm very busy. I will post to this message board
when I'm done.


Sergej

[sqlite] Schema-less JSON SQLite DB?

[Sergej Jurečko]

An idea I?ve had a while ago was to implement functions for json documents
(using sqlite3_create_function_v2)

Json would be stored in a text column. You would need to define 2 functions:
- docget(document,key1,subval1,subval2,?)
- docset(document,key1,subval1,subval2,..,val)

Last parameter of docset is value you wish to set.

So for instance one would write
INSERT INTO mytable VALUES (1,?{key : 10, subdoc : {subkey : ?a"}}?);
SELECT id,doc FROM mytable WHERE docget(doc,?key") > 10;
SELECT id,doc FROM mytable WHERE docget(doc,?key?,?subdoc?,?subkey?) = ?a?;
UPDATE doc FROM mytable WHERE id=1 SET docset(doc,?key?,12);

One could even implement indexes on keys within documents using additional
tables.

What do you guys think? Is it stupid, could it be improved?


Sergej

[sqlite] Schema-less JSON SQLite DB?

[Hayden Livingston]

I'm beginning to understand some of the implementation challenges here.

Eric, it'd be great to get that open sourced. I can tell you at least
I will jump and prototype on it.

Sergej and Roger, I do like this potential idea as well. Of course,
now it's a custom sqlite, but at least the job gets done. Do you think
you'll want to do this anytime soon as an open source project?


On Wed, Jul 15, 2015 at 2:55 PM, J Decker  wrote:
> On Wed, Jul 15, 2015 at 10:03 AM, Roger Binns  
> wrote:
>
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> On 07/15/2015 08:22 AM, Sergej Jure?ko wrote:
>> > What do you guys think? Is it stupid, could it be improved?
>>
>> I recommend looking at Mongodb  and Postgres first to see how they do
>> queries.  It would be better to be compatible with them where
>> practical, rather than being gratuitously different.
>>
>>
> (mentions postresql methods and MongoDB)
> http://thebuild.com/presentations/pg-as-nosql-pgday-fosdem-2013.pdf
> http://www.postgresql.org/docs/9.1/static/hstore.html
>
> My question is, how do you have a intelligent (orderly, structured) query
> of unstructured data?
> I can imagine ways to store and retrieve such things, but how do you query
> for 'give me the count of what I don't know is available' ?
>
>
> http://docs.mongodb.org/manual/reference/sql-comparison/ - good comparison
> of sorts...
>
> but then when it gets to how it get implemented... they're relying on
> external library isntead of 'SQL' or really any sort of query language.
> (day1-2 and day 3-4 links...)
> https://www.mongodb.com/blog/post/mongodb-vs-sql-day-1-2?jmp=docs&_ga=1.94603548.1409473473.1436992997
> https://www.mongodb.com/blog/post/mongodb-vs-sql-day-3-5?jmp=docs&_ga=1.94603548.1409473473.1436992997
>
> From an application standpoint, this doesn't resemble my interface to using
> SQL and getting results back into code; Java looks horrible.  But then
> again my library was built as an abstraction around ODBC and cleverly hides
> details so applications can actually just do work
> http://api.mongodb.org/c/current/executing-command.html (the C interface to
> mongodb to execute a command)
>
> At least the hstore is a extension to the query language, and not a library.
>
> ---
> I guess I've been looking at this strictly from a SQL standpoint, rather
> than using sqlite3_* interfaces to do the work... since My library just has
> 3 commands... GetSQLConnection, DoSQLCommand, (okay and a set...
> GetSQLRecord, GetSQLNext, EndSQL) then using these, I can defiantly see how
> MonogoDB can be built as a layer on top of that using a hash storage
> technique...
>
> table object( objid int, parent_objid int, type int, name_id int, value
> varchar )
> table object_names(  name_id int, name varchar unique )
>
> if they're grouped into some document also can add table doc( docid, name,
> root_objid )
>
> where type is
> 0 - array, and all object with this objid as it's parent_id is in this
> array, (ignore this_object's value; query of sub-data will ignore name...
> well actually the name should be the index into the array so the order can
> be preserved)
> 1 - object,  (NULL value)
> 2 - data (use value as int, float, bool or string)
>
> I would also build a name dictionary and store a name_id instead of the
> literal name since typically Int comparisons are faster... at least in a
> well(long) named schema it would save a few bytes overall
>
> In the day 1-2 MongoDB link he uses a 'user' master object with
> 'contact_number' detail object...
>
> select_contact()
> {
>  select objid from object where name = 'user', and type = 1/*object*/
> and parent=0
>  for each object
>   select * from object where name='contact_number' and type =
> 1/*object*/ and parent=objid
>   /* do something to store in variant result data type */
> }
> viola JSON query in SQL via a library; that's certainly what the internals
> of MongoDB result in
>
> and again maybe RECURSIVE and WITH operators in sqlite's SQL might be
> useful for simplifying the looping
>
> // join user.contact.numbers .. users that have the same numbers
> join_contacts() {
>  select objid from object where name='user' and type=1 and parent=0
>  for each objid
>  select objid as cn,value from object where name='contact_number'
> and type=1 and parent=objid
>  for each cn /* contact number objid */
>   results = select_by_number( value ) /* routine which is a
> select user.contact_number=value */
>   foreach result
>   if( result.objid == objid ) continue /* skip this one */
>   /* add to variant result */
> }
> some of the above could be selected with an inner join into one select, but
> for simplicity broke it into more atomic steps...
>
> /* och = object contact number, ou = object user */
>select ocn.value,ocn.objid as contact_objid from object as ou join
> object as ocn on ocn.parent_objid=ou.objid 

[sqlite] Schema-less JSON SQLite DB?

[J Decker]

On Wed, Jul 15, 2015 at 10:03 AM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 07/15/2015 08:22 AM, Sergej Jure?ko wrote:
> > What do you guys think? Is it stupid, could it be improved?
>
> I recommend looking at Mongodb  and Postgres first to see how they do
> queries.  It would be better to be compatible with them where
> practical, rather than being gratuitously different.
>
>
(mentions postresql methods and MongoDB)
http://thebuild.com/presentations/pg-as-nosql-pgday-fosdem-2013.pdf
http://www.postgresql.org/docs/9.1/static/hstore.html

My question is, how do you have a intelligent (orderly, structured) query
of unstructured data?
I can imagine ways to store and retrieve such things, but how do you query
for 'give me the count of what I don't know is available' ?


http://docs.mongodb.org/manual/reference/sql-comparison/ - good comparison
of sorts...

but then when it gets to how it get implemented... they're relying on
external library isntead of 'SQL' or really any sort of query language.
(day1-2 and day 3-4 links...)
https://www.mongodb.com/blog/post/mongodb-vs-sql-day-1-2?jmp=docs&_ga=1.94603548.1409473473.1436992997
https://www.mongodb.com/blog/post/mongodb-vs-sql-day-3-5?jmp=docs&_ga=1.94603548.1409473473.1436992997

>From an application standpoint, this doesn't resemble my interface to using
SQL and getting results back into code; Java looks horrible.  But then
again my library was built as an abstraction around ODBC and cleverly hides
details so applications can actually just do work
http://api.mongodb.org/c/current/executing-command.html (the C interface to
mongodb to execute a command)

At least the hstore is a extension to the query language, and not a library.

---
I guess I've been looking at this strictly from a SQL standpoint, rather
than using sqlite3_* interfaces to do the work... since My library just has
3 commands... GetSQLConnection, DoSQLCommand, (okay and a set...
GetSQLRecord, GetSQLNext, EndSQL) then using these, I can defiantly see how
MonogoDB can be built as a layer on top of that using a hash storage
technique...

table object( objid int, parent_objid int, type int, name_id int, value
varchar )
table object_names(  name_id int, name varchar unique )

if they're grouped into some document also can add table doc( docid, name,
root_objid )

where type is
0 - array, and all object with this objid as it's parent_id is in this
array, (ignore this_object's value; query of sub-data will ignore name...
well actually the name should be the index into the array so the order can
be preserved)
1 - object,  (NULL value)
2 - data (use value as int, float, bool or string)

I would also build a name dictionary and store a name_id instead of the
literal name since typically Int comparisons are faster... at least in a
well(long) named schema it would save a few bytes overall

In the day 1-2 MongoDB link he uses a 'user' master object with
'contact_number' detail object...

select_contact()
{
 select objid from object where name = 'user', and type = 1/*object*/
and parent=0
 for each object
  select * from object where name='contact_number' and type =
1/*object*/ and parent=objid
  /* do something to store in variant result data type */
}
viola JSON query in SQL via a library; that's certainly what the internals
of MongoDB result in

and again maybe RECURSIVE and WITH operators in sqlite's SQL might be
useful for simplifying the looping

// join user.contact.numbers .. users that have the same numbers
join_contacts() {
 select objid from object where name='user' and type=1 and parent=0
 for each objid
 select objid as cn,value from object where name='contact_number'
and type=1 and parent=objid
 for each cn /* contact number objid */
  results = select_by_number( value ) /* routine which is a
select user.contact_number=value */
  foreach result
  if( result.objid == objid ) continue /* skip this one */
  /* add to variant result */
}
some of the above could be selected with an inner join into one select, but
for simplicity broke it into more atomic steps...

/* och = object contact number, ou = object user */
   select ocn.value,ocn.objid as contact_objid from object as ou join
object as ocn on ocn.parent_objid=ou.objid where ou.value='john' and
ou.type=1 and ocn.type=2 /*and ocn.name='work'*/

if you know that you have something resembling structure to the data...

J

[sqlite] Schema-less JSON SQLite DB?

[Alek Paunov]

Hi Hayden,

On 14.07.2015 03:43, Hayden Livingston wrote:
>   Is there a concept of a schema-less JSON SQLite DB?
>
>   My reason is simple: versioning. We have lot of business metrics that
>   get updated let's say once a month, and we need to be agile to get
>   them. Right now, we just put the version in the SQLite file, and then
>   make sure no queries cross the boundaries.
>
>   Secondly, we have requirements for slightly hierarchal data, i.e.
>   mostly row form, but then some guy wants to put an object.
>
>   What's the SQLite community heading towards if at all?

Given the number of the threads in the list, it seems a
lot of people want hierarchical data in SQLite :-)

>
>   Do others have experiences and requirements similar to this?

My experience:

Few years ago, during small project about translating set of
procedures from one to another SQL dialect, we initially
tried to use XML database for the task, but finally become
to solution to manipulate the parsed procedures in SQLite
using a schema with following simplified core:

doc(d, name)
   node(dn, d, x, y, type, value)
 attribute(dn, type, value)
 reference(dn, type, ref_dn)

[Where (x, y) are equal to (row, column) when one dump the
tree to text file with indent = 1]

This schema is a variation of the BaseX encoding schema:

http://docs.basex.org/wiki/Node_Storage

where:
   - we used y (the depth of the node) instead of DIS
 (distance to the parent)
   - we omitted SIZ (size of the subtree) - it is easily
 calculable (next(on same y).x - x).
   - we used single integer .type as replacement of NS, KIND, Tag
 name + expected scalar type
   - we split the scalar attributes (XML attributes) in
 separate table - attribute and moved the first (only
 in our case) text() value to node.value.
   - we added reference (links between nodes)

Actually there were other modifications like e.g. 
node_range_offset(dn_from, dn_to, offset) table, which we
applied for faking insertions and deletions of whole subtrees
(logical node.x was function of that table and stored
node.x).

Sometime next year I am planning to revive that experiment,
this time for storing/generating configuration data (for
Linux configuration parsed with the augeas tool). Now with the
presence of the powerful CTE feature in SQLite the things
may be a way more easier.

Meantime (given that you have shared with the list that your
project is a business oriented one), I would like to propose
to you, to initiate a sponsorship and a team including
interested list members for digging the subject further.

The team could consolidate the different approach ideas,
existing open source components and discuss what will be
most appropriate and light as implementation resources for
use cases like yours + eventually report to the the SQLite
core team which additional APIs would make the implementation
more efficient.

Kind regards,
Alek

[sqlite] Schema-less JSON SQLite DB?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/15/2015 08:22 AM, Sergej Jure?ko wrote:
> What do you guys think? Is it stupid, could it be improved?

I recommend looking at Mongodb  and Postgres first to see how they do
queries.  It would be better to be compatible with them where
practical, rather than being gratuitously different.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlWmkmwACgkQmOOfHg372QTHIACgi64Sm5nAmc4FyWrF6mimT5Ng
15sAoJJoY51QW/3QM/exRLJYHNtFKBse
=EgGt
-END PGP SIGNATURE-

[sqlite] Schema-less JSON SQLite DB?

[Eric Rubin-Smith]

Given the number of the threads in the list, it seems a

> lot of people want hierarchical data in SQLite :-)
>

Throwing in another $.02 here, my company had a need to ingest JSON of
various formats into SQLite databases in low-power embedded devices.  We
wrote the utility program described here:
https://www.aterlo.com/wp-content/uploads/2015/04/json2sqlite.pdf

The program works well as long as your JSON's depth is bounded. I would not
claim that it supports schemaless data models, but we would love to hear
suggestions for design improvements.

We would be happy to clean this up for open source publication if there is
enough interest.

Eric

[sqlite] Schema-less JSON SQLite DB?

[Hick Gunter]

The record sort order is:

NULLs
Numeric by value
Text by collating function
Blob by memcmp order

So your result set will contain all rows having a FooColumn with numeric value 
greater than 50, a text value or a blob value.

-Urspr?ngliche Nachricht-
Von: Hayden Livingston [mailto:halivingston at gmail.com]
Gesendet: Dienstag, 14. Juli 2015 19:22
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Schema-less JSON SQLite DB?

Wow, this is the killer feature no-one told me about. I can get rid of types? 
How does this work under-the-hood?

SELECT * FROM TABLE WHERE FooColumn > 50

And I've stored "Something" in that column in some row. What's the behavior?

I don't want to take up your time, so if there's a document I can read about 
it'd be great.

On Mon, Jul 13, 2015 at 8:34 PM, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 07/13/2015 08:00 PM, Hayden Livingston wrote:
>> Does your code also map object hierarchies in json?
>
> Yes, but thankfully I don't have much of them.  Essentially the top
> level of the object has a unique id (SQLite allocated), and then other
> tables are used to join zero or more child objects to the top level.
>
>> What general format do you use?
>
> Ultimately I use Python dictionaries which are supersets of JSON
> objects.  Some (ugly) code can convert both ways
>
>> Each object type gets stored in a separate table?
>
> In my case yes but that is because the underlying data represents
> known entities and was actually originally in Postgres and then
> exported to hideous inconsistent XML which I then convert/denormalise
> back into JSON.
>
> Do remember that SQLite does not require you to specify a type for
> each column, nor does it care about the values in a column being
> different types between rows.  That means I don't have to worry about
> types, only the big picture top level of something being an object, a
> list, or a scalar.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1
>
> iEYEARECAAYFAlWkg0MACgkQmOOfHg372QRTbQCfRobbh4uU1Eqf0SD9LJxABFYj
> iv8AoKUvXNQEgGVvmPiZ/tQgHyU7Q0yL
> =S7AM
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Schema-less JSON SQLite DB?

[J Decker]

On Wed, Jul 15, 2015 at 2:14 AM, Alek Paunov  wrote:

> Hi Hayden,
>
> On 14.07.2015 03:43, Hayden Livingston wrote:
>
>>   Is there a concept of a schema-less JSON SQLite DB?
>>
>>   My reason is simple: versioning. We have lot of business metrics that
>>   get updated let's say once a month, and we need to be agile to get
>>   them. Right now, we just put the version in the SQLite file, and then
>>   make sure no queries cross the boundaries.
>>
>>   Secondly, we have requirements for slightly hierarchal data, i.e.
>>   mostly row form, but then some guy wants to put an object.
>>
>>   What's the SQLite community heading towards if at all?
>>
>
> Given the number of the threads in the list, it seems a
> lot of people want hierarchical data in SQLite :-)
>
>
>>   Do others have experiences and requirements similar to this?
>>
>
> My experience:
>
>
I have an option map stored hierarchical.  I would tackle storing something
like XML similarly I suppose where I'd have...

node( n, d, parent_node, node_order, name )
   value( n, value )

(for XML...  doc( d, name, n ) )
element( n, parent_element, node_order, name )
   attributes( n, name, value )
(where d, n are primary keys)

I didn't have separate maps (like separate xml docs), I have a 'root' which
is ID 0 (GUID 0), and for each value under that I grab where parent is the
node and the name is what I'm looking for... or enumerating all is order by
node_order and parent_id.  For document support, the document would store
the root node ID to prevent replicating the doc ID in all nodes.  The
treeview only queries on the branches you expand so it's not a large query
even with lots of options.  Other developers in Access tried to use it but
ended up sucking in the entire tree and reparsing it with normalized names
that were the full path instead of treating it as a hierarchy.

The other place I used a similar structure was saving a neural network
(really a wiring diagram of nodes and connections)

I don't know much about RECURSE operator that's available... like to select
'option, program, display, render_interface' which is a 4 deep query...

But I did find that reversing the direction of the pointer made it work
pretty well... just not with SQL stored procedures, instead relying on code
support external to the database...  (reversing being that normally you
have node->node->node ... where the storage is actually more like
node<-node<-node  )




> Few years ago, during small project about translating set of
> procedures from one to another SQL dialect, we initially
> tried to use XML database for the task, but finally become
> to solution to manipulate the parsed procedures in SQLite
> using a schema with following simplified core:
>
> doc(d, name)
>   node(dn, d, x, y, type, value)
> attribute(dn, type, value)
> reference(dn, type, ref_dn)
>
> [Where (x, y) are equal to (row, column) when one dump the
> tree to text file with indent = 1]
>
> This schema is a variation of the BaseX encoding schema:
>
> http://docs.basex.org/wiki/Node_Storage
>
> where:
>   - we used y (the depth of the node) instead of DIS
> (distance to the parent)
>   - we omitted SIZ (size of the subtree) - it is easily
> calculable (next(on same y).x - x).
>   - we used single integer .type as replacement of NS, KIND, Tag
> name + expected scalar type
>   - we split the scalar attributes (XML attributes) in
> separate table - attribute and moved the first (only
> in our case) text() value to node.value.
>   - we added reference (links between nodes)
>
> Actually there were other modifications like e.g.
> node_range_offset(dn_from, dn_to, offset) table, which we
> applied for faking insertions and deletions of whole subtrees
> (logical node.x was function of that table and stored
> node.x).
>
> Sometime next year I am planning to revive that experiment,
> this time for storing/generating configuration data (for
> Linux configuration parsed with the augeas tool). Now with the
> presence of the powerful CTE feature in SQLite the things
> may be a way more easier.
>
> Meantime (given that you have shared with the list that your
> project is a business oriented one), I would like to propose
> to you, to initiate a sponsorship and a team including
> interested list members for digging the subject further.
>
> The team could consolidate the different approach ideas,
> existing open source components and discuss what will be
> most appropriate and light as implementation resources for
> use cases like yours + eventually report to the the SQLite
> core team which additional APIs would make the implementation
> more efficient.
>
> Kind regards,
> Alek
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Schema-less JSON SQLite DB?

[Kees Nuyt]

On Tue, 14 Jul 2015 10:22:02 -0700, Hayden Livingston
 wrote:

> Wow, this is the killer feature no-one told me about.
> I can get rid of types? How does this work under-the-hood?
>
> SELECT * FROM TABLE WHERE FooColumn > 50
>
> And I've stored "Something" in that column in some row.
> What's the behavior?

That's easy to test by yourself.
By the way, you can enforce datatype to some extend with
constraints:
CREATE TABLE T1 (
  id INTEGER PRIMARY KEY NOT NULL
, anint INTEGER 
  CONSTRAINT int_anint CHECK (typeof(anint) == 'integer')
);
INSERT INTO T1 (anint) VALUES (1);
INSERT INTO T1 (anint) VALUES ('two');


> I don't want to take up your time, so if there's a document I can read
> about it'd be great.

https://sqlite.org/datatype3.html 

All docs:
https://sqlite.org/docs.html


-- 
Regards, Cordialement, Groet,

Kees Nuyt

[sqlite] Schema-less JSON SQLite DB?

[Simon Slavin]

On 14 Jul 2015, at 6:22pm, Hayden Livingston  wrote:

> Wow, this is the killer feature no-one told me about. I can get rid of
> types? How does this work under-the-hood?
> 
> SELECT * FROM TABLE WHERE FooColumn > 50
> 
> And I've stored "Something" in that column in some row. What's the behavior?

SQLite uses column affinities not column types.  You can declare a column with 
a type, but store any type of value in any row of that column. Once stored you 
can discover what type was stored using something like

SELECT typeof(foo),foo FROM myTable

If you insist on sorting column foo when there are different types of value 
stored in it, you will get a consistent but not especially useful sort order.

There is a particular restriction which is the result of SQLite trying to be 
useful.  As an example, if you define a column as REAL and store the string 
'123.456' in it, SQLite will automatically convert it to a number before 
storing it, on the assumption that you will want '123.456' to be sorted after 
'12.345'.  So if you intend to save many different types in a column, it may be 
best to define it as having an affinity of BLOB.

For more information read about affinity in section 2.0 of



Simon.

[sqlite] Schema-less JSON SQLite DB?

[Hayden Livingston]

that does indeed look interesting. Embedded DB is the only problem here.

On Mon, Jul 13, 2015 at 11:01 PM, Darren Duncan  
wrote:
> Have a look at PostgreSQL 9.4 as well and its new JSONB data type.  Gives
> you the goods of relational and hierarchical databases in one place,
> including the querying and indexing. -- Darren Duncan
>
>
> On 2015-07-13 5:43 PM, Hayden Livingston wrote:
>>
>>   Is there a concept of a schema-less JSON SQLite DB?
>>
>>   My reason is simple: versioning. We have lot of business metrics that
>>   get updated let's say once a month, and we need to be agile to get
>>   them. Right now, we just put the version in the SQLite file, and then
>>   make sure no queries cross the boundaries.
>>
>>   Secondly, we have requirements for slightly hierarchal data, i.e.
>>   mostly row form, but then some guy wants to put an object.
>>
>>   What's the SQLite community heading towards if at all?
>>
>>   Do others have experiences and requirements similar to this?
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Schema-less JSON SQLite DB?

[Hayden Livingston]

Wow, this is the killer feature no-one told me about. I can get rid of
types? How does this work under-the-hood?

SELECT * FROM TABLE WHERE FooColumn > 50

And I've stored "Something" in that column in some row. What's the behavior?

I don't want to take up your time, so if there's a document I can read
about it'd be great.

On Mon, Jul 13, 2015 at 8:34 PM, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 07/13/2015 08:00 PM, Hayden Livingston wrote:
>> Does your code also map object hierarchies in json?
>
> Yes, but thankfully I don't have much of them.  Essentially the top
> level of the object has a unique id (SQLite allocated), and then other
> tables are used to join zero or more child objects to the top level.
>
>> What general format do you use?
>
> Ultimately I use Python dictionaries which are supersets of JSON
> objects.  Some (ugly) code can convert both ways
>
>> Each object type gets stored in a separate table?
>
> In my case yes but that is because the underlying data represents
> known entities and was actually originally in Postgres and then
> exported to hideous inconsistent XML which I then convert/denormalise
> back into JSON.
>
> Do remember that SQLite does not require you to specify a type for
> each column, nor does it care about the values in a column being
> different types between rows.  That means I don't have to worry about
> types, only the big picture top level of something being an object, a
> list, or a scalar.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1
>
> iEYEARECAAYFAlWkg0MACgkQmOOfHg372QRTbQCfRobbh4uU1Eqf0SD9LJxABFYj
> iv8AoKUvXNQEgGVvmPiZ/tQgHyU7Q0yL
> =S7AM
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Schema-less JSON SQLite DB?

[Darren Duncan]

Have a look at PostgreSQL 9.4 as well and its new JSONB data type.  Gives you 
the goods of relational and hierarchical databases in one place, including the 
querying and indexing. -- Darren Duncan

On 2015-07-13 5:43 PM, Hayden Livingston wrote:
>   Is there a concept of a schema-less JSON SQLite DB?
>
>   My reason is simple: versioning. We have lot of business metrics that
>   get updated let's say once a month, and we need to be agile to get
>   them. Right now, we just put the version in the SQLite file, and then
>   make sure no queries cross the boundaries.
>
>   Secondly, we have requirements for slightly hierarchal data, i.e.
>   mostly row form, but then some guy wants to put an object.
>
>   What's the SQLite community heading towards if at all?
>
>   Do others have experiences and requirements similar to this?

[sqlite] Schema-less JSON SQLite DB?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/13/2015 08:00 PM, Hayden Livingston wrote:
> Does your code also map object hierarchies in json?

Yes, but thankfully I don't have much of them.  Essentially the top
level of the object has a unique id (SQLite allocated), and then other
tables are used to join zero or more child objects to the top level.

> What general format do you use?

Ultimately I use Python dictionaries which are supersets of JSON
objects.  Some (ugly) code can convert both ways

> Each object type gets stored in a separate table?

In my case yes but that is because the underlying data represents
known entities and was actually originally in Postgres and then
exported to hideous inconsistent XML which I then convert/denormalise
back into JSON.

Do remember that SQLite does not require you to specify a type for
each column, nor does it care about the values in a column being
different types between rows.  That means I don't have to worry about
types, only the big picture top level of something being an object, a
list, or a scalar.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlWkg0MACgkQmOOfHg372QRTbQCfRobbh4uU1Eqf0SD9LJxABFYj
iv8AoKUvXNQEgGVvmPiZ/tQgHyU7Q0yL
=S7AM
-END PGP SIGNATURE-

[sqlite] Schema-less JSON SQLite DB?

[Hayden Livingston]

We use SQLite as a library and we don't want to migrate to a server situation.

So it seems using the month-to-month approach you outline is
reasonable and not outrageous. Good to know.

Thanks for the tip on schema modifications which get automatically
upgraded on read. This is a great feature. I think I might try that
out for my application.

Does your code also map object hierarchies in json? What general
format do you use? Each object type gets stored in a separate table?

On Mon, Jul 13, 2015 at 7:03 PM, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 07/13/2015 05:43 PM, Hayden Livingston wrote:
>> Is there a concept of a schema-less JSON SQLite DB?
>
> What exactly is it you want?  A schema-less database?  A JSON
> database? Using SQLite to store JSON?  (It is unclear if you mean
> something like SQLite, or SQLite itself).  Something small and light?
>  A library not a server?
>
> Be aware that you can dynamically update the SQLite schema, and that
> it takes no time to do so.  Another database I won't name locks the
> database and rewrites each record.  This can take hours or even days.
>  SQLite just updates the schema table, and fills in defaults for
> records written before the schema change, on reads.  I wrote some code
> the other day that takes JSON and does the schema manipulation,
> although it is annoying.  (It would be really great if SQLite
> supported arrays like Postgres does.)
>
> You can also store arbitrary JSON in a column.  You won't be able to
> query it effectively, but you can duplicate values into columns you
> can do queries and indexes on.
>
>> My reason is simple: versioning. We have lot of business metrics
>> that get updated let's say once a month, and we need to be agile to
>> get them. Right now, we just put the version in the SQLite file,
>> and then make sure no queries cross the boundaries.
>
> You can also use multiple databases, and attach them.  For example if
> you operate on a month by month basis, then you can put each month's
> data in a separate SQLite file, then attach last month's as
> 'lastmonth', and use in queries (eg SELECT blah blah WHERE bar.foo >
> lastmonth.bar.foo)
>
>> Do others have experiences and requirements similar to this?
>
> All the time.  I use SQLite when I don't need networked access, a
> running database server, and need less things that can go wrong.
> Mapping JSON into this is painful but possible.
>
> When I can use a database server, I prefer Mongodb as it is very good
> at arbitrary JSON in, the same arbitrary JSON back out.  It is
> especially pleasant that the query syntax has the same shape as the
> underlying JSON data.  Also JSON maps trivially to Python which I use
> the most.  (Note however that Mongodb does have some issues, but so
> does much other software out there.  Production use does require
> effort & planning as does other software.)
>
> In the longer term Postgres is getting increasingly better JSON
> support.  Hopefully it will give Mongodb a run for its money soon.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1
>
> iEYEARECAAYFAlWkbfsACgkQmOOfHg372QTFiwCgzzf5fEzfJdWn84bk0e1fusGE
> qeAAnAhR+sHb39Gsha2Owq3SXsdZVRex
> =9jcT
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Schema-less JSON SQLite DB?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/13/2015 05:43 PM, Hayden Livingston wrote:
> Is there a concept of a schema-less JSON SQLite DB?

What exactly is it you want?  A schema-less database?  A JSON
database? Using SQLite to store JSON?  (It is unclear if you mean
something like SQLite, or SQLite itself).  Something small and light?
 A library not a server?

Be aware that you can dynamically update the SQLite schema, and that
it takes no time to do so.  Another database I won't name locks the
database and rewrites each record.  This can take hours or even days.
 SQLite just updates the schema table, and fills in defaults for
records written before the schema change, on reads.  I wrote some code
the other day that takes JSON and does the schema manipulation,
although it is annoying.  (It would be really great if SQLite
supported arrays like Postgres does.)

You can also store arbitrary JSON in a column.  You won't be able to
query it effectively, but you can duplicate values into columns you
can do queries and indexes on.

> My reason is simple: versioning. We have lot of business metrics
> that get updated let's say once a month, and we need to be agile to
> get them. Right now, we just put the version in the SQLite file,
> and then make sure no queries cross the boundaries.

You can also use multiple databases, and attach them.  For example if
you operate on a month by month basis, then you can put each month's
data in a separate SQLite file, then attach last month's as
'lastmonth', and use in queries (eg SELECT blah blah WHERE bar.foo >
lastmonth.bar.foo)

> Do others have experiences and requirements similar to this?

All the time.  I use SQLite when I don't need networked access, a
running database server, and need less things that can go wrong.
Mapping JSON into this is painful but possible.

When I can use a database server, I prefer Mongodb as it is very good
at arbitrary JSON in, the same arbitrary JSON back out.  It is
especially pleasant that the query syntax has the same shape as the
underlying JSON data.  Also JSON maps trivially to Python which I use
the most.  (Note however that Mongodb does have some issues, but so
does much other software out there.  Production use does require
effort & planning as does other software.)

In the longer term Postgres is getting increasingly better JSON
support.  Hopefully it will give Mongodb a run for its money soon.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlWkbfsACgkQmOOfHg372QTFiwCgzzf5fEzfJdWn84bk0e1fusGE
qeAAnAhR+sHb39Gsha2Owq3SXsdZVRex
=9jcT
-END PGP SIGNATURE-

[sqlite] Schema-less JSON SQLite DB?

[Hayden Livingston]

 Is there a concept of a schema-less JSON SQLite DB?

 My reason is simple: versioning. We have lot of business metrics that
 get updated let's say once a month, and we need to be agile to get
 them. Right now, we just put the version in the SQLite file, and then
 make sure no queries cross the boundaries.

 Secondly, we have requirements for slightly hierarchal data, i.e.
 mostly row form, but then some guy wants to put an object.

 What's the SQLite community heading towards if at all?

 Do others have experiences and requirements similar to this?

[sqlite] Looking for Ready-To-Use (Instant) SQLite Schema (and Sample Data) Scripts

[Gerald Bauer]

Hello,

  As posted before I've started an awesome collection about all things
SQLite [1].

  One section collections ready-to-use (instant) SQLite schemas (and
sample data) scripts. So far listed are:

  - football.db  - teams, competitions, seasons, matches, goals,
rounds, groups, etc.
  - beer.db  - beer, brand, brewery, etc.
  - Chinook Database  - online music store with sample data script
(e.g. chinook.sqlite) - inspired by the "classic" Microsoft Northwind
example


   If you know any others, let us know. Thanks. Cheers.

[1] https://github.com/planetopendata/awesome-sqlite

Re: [sqlite] Looking for SQLite schema doc generator (in

[Erik Ejlskov Jensen]

Thanks, I simply adapted this: https://sqldbdoc.codeplex.com/




And you can modify the xlst file if you have the guts






Sendt fra Windows Mail





Fra: gerald.ba...@gmail.com
Sendt: ‎tirsdag‎, ‎3‎. ‎februar‎ ‎2015 ‎18‎:‎29
Til: Erik Ejlskov Jensen
Cc: rsm...@rsweb.co.za, sqlite-users@sqlite.org





Hello,

>  SQLite Toolbox

   Thanks for highlighting. The HTML pages for the schema looks great. Cheers.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Looking for SQLite schema doc generator (in HTML) for tables, fields, etc

[Gerald Bauer]

Hello,

  Thanks. Good point. Will rename fields to columns in schema.json.
Was trying initially to (re)use the Tabular Data Package format (in
json) [1] from the Open Knowledge Foundation (OKFN) but it's not
really a good fit.

  Sorry for the broken link. The schemadoc repo (that generates the
json for the schema from the sqlite db) lives @
https://github.com/rubylibs/schemadoc   (will get updated later today
- it's a scaffold gem now).  Cheers.

[1] http://data.okfn.org/doc/tabular-data-package
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Looking for SQLite schema doc generator (in HTML) for tables, fields, etc

[Dominique Devienne]

On Wed, Feb 4, 2015 at 9:18 AM, Gerald Bauer  wrote:

> [1a] New schemadoc Ruby Gem -> https://github.com/rubylibs/schemadoc
> 


[DD] 404 for me


> [1b ] Templates ->
> https://github.com/book-templates/schema


FWIW, in
https://github.com/book-templates/schema/blob/gh-pages/_data/schema.json
you use "tables" and "fields". Personally I reserve type+field to the
logical model, and table+column to the physical model.
Perhaps using table+column instead of table+field would be more logical and
common. My $0.02. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Looking for SQLite schema doc generator (in HTML) for tables, fields, etc

[Gerald Bauer]

Hello,

> I had something that I would use to draw a few pictures of the table schema...

  You're invited to share your code - my first version [1] is
text-only so far.  Always good to have some diagrams - a picture is
worth a thousand words etc. ;-)  Keep it up. Cheers.

[1a] New schemadoc Ruby Gem -> https://github.com/rubylibs/schemadoc
[1b] Templates -> https://github.com/book-templates/schema
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Looking for SQLite schema doc generator (in

[Gerald Bauer]

Hello,
   Thanks for the pointer to the template. I'm using a plain HTML
template w/ Liquid tags [1]. You can see a rendered (live) version
online. [2] Thanks for the inspiration. Cheers.

[1] https://github.com/book-templates/schema/blob/gh-pages/index.html
[2] http://book-templates.github.io/schema
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Looking for SQLite schema doc generator (in

[Erik Ejlskov Jensen]

Thanks, I simply adapted this: https://sqldbdoc.codeplex.com/




And you can modify the xlst file if you have the guts






Sendt fra Windows Mail





Fra: gerald.ba...@gmail.com
Sendt: ‎tirsdag‎, ‎3‎. ‎februar‎ ‎2015 ‎18‎:‎29
Til: Erik Ejlskov Jensen
Cc: rsm...@rsweb.co.za, sqlite-users@sqlite.org





Hello,

>  SQLite Toolbox

   Thanks for highlighting. The HTML pages for the schema looks great. Cheers.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Looking for SQLite schema doc generator (in HTML) for tables, fields, etc

[Mohit Sindhwani]

On 4/2/2015 12:20 AM, Gerald Bauer wrote:

Hello,
 I have started two hours ago ;-) to put together a little script.
The idea is to "dump" the schema as json and then use a static site
generator w/ html templates to generate the docu.

So far the script that generates the json dump (is twenty lines of
Ruby). You can see an example, for the football.db online [1]. Cheers.

[1] https://github.com/book-templates/schema/blob/master/_data/schema.json

PS: The static site generator (e.g. Jekyll) is actually built into
GitHub - so the idea is you upload the schema in json and the docu
auto-builds itself (incl. free hosting thanks to GitHub Pages).


I had something that I would use to draw a few pictures of the table 
schema... since you're using Ruby, I'm tempted to revisit that...


Best Regards,
Mohit.


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

Re: [sqlite] Looking for SQLite schema doc generator (in

[Erik Ejlskov Jensen]

You can use my Visual Studio addin: SQLite Toolbox to do this:


http://erikej.blogspot.dk/2014/08/sqlite-toolbox-40-visual-guide-of.html








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

Re: [sqlite] Looking for SQLite schema doc generator (in

[Gerald Bauer]

Hello,

>  SQLite Toolbox

   Thanks for highlighting. The HTML pages for the schema looks great. Cheers.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Looking for SQLite schema doc generator (in

[Drago, William @ CSG - NARDAEAST]

I was just about to post a link to this. I am still using it. Handy tool, it 
does a great job with the HTML doc.

--
Bill Drago
Senior Engineer
L3 Communications / Narda Microwave East
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Erik Ejlskov Jensen
> Sent: Tuesday, February 03, 2015 12:11 PM
> To: rsm...@rsweb.co.za; gerald.ba...@gmail.com
> Cc: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Looking for SQLite schema doc generator (in
>
> You can use my Visual Studio addin: SQLite Toolbox to do this:
>
>
> http://erikej.blogspot.dk/2014/08/sqlite-toolbox-40-visual-guide-
> of.html
>
>
>
>
>
>
>
>
> Sendt fra Windows Mail
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Looking for SQLite schema doc generator (in

[Erik Ejlskov Jensen]

You can use my Visual Studio addin: SQLite Toolbox to do this:


http://erikej.blogspot.dk/2014/08/sqlite-toolbox-40-visual-guide-of.html








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

Re: [sqlite] Looking for SQLite schema doc generator (in HTML) for tables, fields, etc

[Gerald Bauer]

Hello,
I have started two hours ago ;-) to put together a little script.
The idea is to "dump" the schema as json and then use a static site
generator w/ html templates to generate the docu.

   So far the script that generates the json dump (is twenty lines of
Ruby). You can see an example, for the football.db online [1]. Cheers.

[1] https://github.com/book-templates/schema/blob/master/_data/schema.json

PS: The static site generator (e.g. Jekyll) is actually built into
GitHub - so the idea is you upload the schema in json and the docu
auto-builds itself (incl. free hosting thanks to GitHub Pages).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Looking for SQLite schema doc generator (in HTML) for tables, fields, etc

[Gerald Bauer]

Hello,
   Thanks. Good point. Wrapping into a html pre tag and it's a great
cheat sheet as a single html page.

Ideally looking for something with an index page, table
cross-references as links, etc.  Cheers.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Looking for SQLite schema doc generator (in HTML) for tables, fields, etc

[RSmith]

On 2015/02/03 17:34, Gerald Bauer wrote:

Hello,

I'm looking for a little tool that reads in an SQLite schema (e.g.
beer.db, football.db, etc.)  and outputs (generates)  documentation
for tables, fields etc. as a single HTML page or as HTML pages.Any
insight appreciated?


Actually I was just in the process of making such a tool - maybe we could help each other. Could you send me an example schema and 
resulting document that you would like it to produce?

No need to be complex, but it needs to contain every plausible kind of result 
needed. Triggers and Indices too. Thanks!



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

Re: [sqlite] Looking for SQLite schema doc generator (in HTML) for tables, fields, etc

[Rich Shepard]

On Tue, 3 Feb 2015, Gerald Bauer wrote:


  I'm looking for a little tool that reads in an SQLite schema (e.g.
beer.db, football.db, etc.) and outputs (generates) documentation for
tables, fields etc. as a single HTML page or as HTML pages. Any insight
appreciated?


Gerald,

  While not in html format, try the .schema command from the command line.

  Invoke sqlite3, then type .help to see all options. The .sc option dumps
the entire schema to the display; if you specify a table name then the
schema for only that table is displayed.

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

[sqlite] Looking for SQLite schema doc generator (in HTML) for tables, fields, etc

[Gerald Bauer]

Hello,

   I'm looking for a little tool that reads in an SQLite schema (e.g.
beer.db, football.db, etc.)  and outputs (generates)  documentation
for tables, fields etc. as a single HTML page or as HTML pages.Any
insight appreciated?

  Cheers.
___
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

[William Drago]

I like SQLite Expert too. Very nice tool, you don't have to 
run Firefox to use it, and the personal edition is free. 
I've gotten a lot of good use out of it. Windows only is the 
one drawback.


-Bill

On 9/18/2013 5:09 AM, jcd 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.


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


-
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3408 / Virus Database: 3222/6678 - Release 
Date: 09/18/13





___
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 <niuba...@gmail.com>
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

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, <sqlite-users-requ...@sqlite.org> wrote:

> Message: 3
> Date: Wed, 18 Sep 2013 13:54:34 -0700 (PDT)
> From: niubao <niuba...@gmail.com>
> 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)

[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 <niuba...@gmail.com> 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://user/SendEmail.jtp?type=node=71353=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<
> 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://user/SendEmail.jtp?type=node=71353=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<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

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
<reid.thomp...@ateb.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

+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://user/SendEmail.jtp?type=node=71349=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-tp71338p71349.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-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" <slav...@bigfraud.org> wrote:

>
> On 18 Sep 2013, at 1:49am, niubao <niuba...@gmail.com> 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)

[Tim Streater]

On 18 Sep 2013 at 01:49, niubao <niuba...@gmail.com> 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 <niuba...@gmail.com> 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 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

Re: [sqlite] a tool that allows you to graphically change sqlite schema

[Jason H]

After thinking about your post,  the sqlite alter limitation is sqlite 
specific. Maybe their sqlite Bryson takes this info account? I usually use the 
free odbc one but it is slow...

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 tool that allows you to graphically change sqlite schema

[Jason H]

Try sql maelstro 

Sent from Yahoo! Mail on Android

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

[sqlite] a tool that allows you to graphically change sqlite schema

[Bao Niu]

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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[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

[sqlite] schema creation

[Paolo Délano Alonso]

I noticed that the schema for my table is not being created even though my
object class has the proper attributes (PrimaryKey, Autoincrement).

My object class is located in a different solution in my VS2012 project. 

I moved the object class to the same solution where I´m calling the SQLite
methods and the schemas are created correctly.

 

Any thoughts on this?

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

Re: [sqlite] (UML) Visualisation of a SQLite schema ?

[Fredrik Karlsson]

Hi,

Just thought I'd tell you that I went into solving this issue myself.
The resulting script is here:

https://github.com/dargosch/sqlite2dot

* Requires Tcl and the sqlite3 package for Tcl.

It generates a .dot file for use with Graphviz. Very simple script,
but it solves the issue I wanted to solve :-)

Please suggest enhancements.

/Fredrik

On Fri, Apr 15, 2011 at 12:40 PM, Gary_Gabriel
 wrote:
> Hi Fredrik,
>> I'm looking for a tool that would generate a visualisation (UML?) of
>> the SQLite database schema I'm using, with table constraints and
>> references between tables if possible. Is there such a tool (for Mac)?
>>
> If you are interested in doing something yourself- then this might be a
> gentle reminder of a thread on the mailing list.
> Subject: [sqlite] Creating directed graphs and simple examples To:
> sqlite-users@sqlite.org From: Gary Briggs  Date: 15,
> Mar 2011
>
> Archive:  http://www.mail-archive.com/sqlite-users@sqlite.org/msg59602.html
>
> Graphviz: http://www.graphviz.org/
>
> As you probably know Graphviz integrates into Tcl/Tk with TclDot which
> is available for Posix.
>
> Good luck with your search- Gary Gabriel
>
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
"Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (UML) Visualisation of a SQLite schema ?

[Gary_Gabriel]

Hi Fredrik,
> I'm looking for a tool that would generate a visualisation (UML?) of
> the SQLite database schema I'm using, with table constraints and
> references between tables if possible. Is there such a tool (for Mac)?
>   
If you are interested in doing something yourself- then this might be a
gentle reminder of a thread on the mailing list.
Subject: [sqlite] Creating directed graphs and simple examples To:
sqlite-users@sqlite.org From: Gary Briggs  Date: 15,
Mar 2011

Archive:  http://www.mail-archive.com/sqlite-users@sqlite.org/msg59602.html

Graphviz: http://www.graphviz.org/

As you probably know Graphviz integrates into Tcl/Tk with TclDot which
is available for Posix.

Good luck with your search- Gary Gabriel






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

[sqlite] (UML) Visualisation of a SQLite schema ?

[Fredrik Karlsson]

Hi,

I'm looking for a tool that would generate a visualisation (UML?) of
the SQLite database schema I'm using, with table constraints and
references between tables if possible. Is there such a tool (for Mac)?

I've looked into SQL::Translator (produces reasonable output, but does
not cover the entire SQLite syntax) and SchemaSpy (the javasqlite has
pointer problems on my mac).

Any suggestion would be very helpful.

/Fredrik

-- 
"Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Schema and database layout

[Kavita Raghunathan]

- Original Message -
From: "P Kishor" <punk.k...@gmail.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Wednesday, September 16, 2009 9:00:29 AM GMT -06:00 US/Canada Central
Subject: Re: [sqlite] Schema and database layout



>Google for help with designing relational databases. You will get 36
>million hits.


KR: You lied; I only got 4 million hits :-)
Thanks!

Kavita

>
> CREATE TABLE TypeNumbers (
>    ID INTEGER PRIMARY KEY AUTOINCREMENT,
>    table_type INTEGER,
>    MainID INTEGER,
>    Type INTEGER,
>    CurrentNumber INTEGER);
>
> And here is you "table_type" that will have numbers 1 to 10 and you
> will be able "to pass it to SQLite" (whatever that means).
>
>>> Would there be a way I can pass the schema as well as data into SQLite ?
>>
>> Elaborate please what do you want to do, where do you want to pass
>> "schema" (do you mean table name here?) and data?
>>
>> KR: The above should answer this question.
>
> It doesn't.
>
>> KR: Different threads are going to access different tables of the same table 
>> type.
>
> Even more puzzles here. I thought your table type is just one table,
> it seems that you have several tables of the same type but why? And
> what's the difference between tables of different types? Does this
> mean that you have more than 10 tables? Do you have only 10 "table
> types"?
>
>> Maybe I need to think this through, even as I write this:-)
>
> That definitely will help. :-)
>
>
> Pavel
>
> On Tue, Sep 15, 2009 at 6:01 PM, Kavita Raghunathan
> <kavita.raghunat...@skyfiber.com> wrote:
>> Please see clarifications below:
>> - Original Message -
>> From: "Pavel Ivanov" <paiva...@gmail.com>
>> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>> Sent: Tuesday, September 15, 2009 4:36:19 PM GMT -06:00 US/Canada Central
>> Subject: Re: [sqlite] Schema and database layout
>>
>>> 1) dont see a way to reuse schemas. In other words, I have say 10 tables 
>>> with the same schema. How would I prevent doing 10 CREATE table commands ?
>>
>> Create 1 table with 1 additional column "schema" which will contain
>> numbers from 1 to 10.
>>
>> KR: The 10 tables contain different data, but instead of doing this 
>> statement (example below) over
>> and over, I want to just pass this table "type" because all my tables look 
>> exactly
>> like table "TypeNumbers", but they have different data, depending on the 
>> entity-id.
>>
>> strcpy(sqlStr,"CREATE TABLE TypeNumbers (");
>>       strcat(sqlStr,"ID INTEGER PRIMARY KEY AUTOINCREMENT,");
>>       strcat(sqlStr,"MainID INTEGER,");
>>       strcat(sqlStr,"Type INTEGER,");
>>       strcat(sqlStr,"CurrentNumber INTEGER);");
>>
>>> Would there be a way I can pass the schema as well as data into SQLite ?
>>
>> Elaborate please what do you want to do, where do you want to pass
>> "schema" (do you mean table name here?) and data?
>>
>> KR: The above should answer this question.
>>
>>> 2)Also, do you reccomend a different .db file for each schema so that 
>>> multiple threads can easily operate at the same time ?
>>
>> It depends on how your threads will operate, how often they will issue
>> sql statements, what type of sql statements and all other details
>> about your application functionality.
>>
>> KR: Different threads are going to access different tables of the same table 
>> type.
>> Maybe I need to think this through, even as I write this:-)
>>
>> Thanks, Pavel!
>>
>>
>> On Tue, Sep 15, 2009 at 5:27 PM, Kavita Raghunathan
>> <kavita.raghunat...@skyfiber.com> wrote:
>>> All,
>>> I have a couple of questions:
>>>
>>> I've been studying the C/C++ interface, and
>>> 1) dont see a way to reuse schemas. In other words, I have say 10 tables 
>>> with the same schema. How would I prevent doing 10 CREATE table commands ? 
>>> Would there be a way I can pass the schema as well as data into SQLite ?
>>>
>>> 2)Also, do you reccomend a different .db file for each schema so that 
>>> multiple threads can easily operate at the same time ?
>>>
>>> I apologise in advance if I missed it in the documentation.
>>>
>>> Thanks again to this group for your awesome support. I've got a lot of 
>>> ideas from you a

Re: [sqlite] Schema and database layout

[Pavel Ivanov]

>   Would this be a better design ?

That would be definitely a better design at least because you will be
able to keep only one prepared statement where one of bindings will be
your entity id. Also you will be able to add more types of entities
without changing database schema. Just don't forget to add proper
index where first field will be entity id.
But each design has its caveats of course. And if your application
works in 10 threads with shared cache mode turned on and each thread
updates or adds only one type of entities then with 10 different
tables you'll have better concurrent updates than with 1 table. It
doesn't matter though if your application works in 10 different
processes.

>   Can you point me to some database design docs that dictates that tables of
>   the same type should be 1 single table ?

Sorry, can't help with this.

Pavel

On Wed, Sep 16, 2009 at 9:57 AM, Kavita Raghunathan
<kavita.raghunat...@skyfiber.com> wrote:
>
> - Original Message -
> From: "Pavel Ivanov" <paiva...@gmail.com>
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Sent: Wednesday, September 16, 2009 6:40:18 AM GMT -06:00 US/Canada Central
> Subject: Re: [sqlite] Schema and database layout
>
>> KR: The 10 tables contain different data, but instead of doing this 
>> statement (example below) over
>> and over, I want to just pass this table "type" because all my tables look 
>> exactly
>> like table "TypeNumbers", but they have different data, depending on the 
>> entity-id.
>
> You better stop telling puzzles and explain everything in thorough
> details so that we could understand it.
> "entity-id" - what is it? There's no such column in the table, you've
> never spoken about any entity, so we can just guess... "this table
> "type" - what table type? Give the definition please what you're
> calling "table type". "I want to just pass this table "type" - pass
> what, where and doing what? What are you doing that is hard and with
> "passing table type" will be easier?
> And continuing to look into my crystal ball I still don't understand
> why my suggestion doesn't suit you. You have 10 tables with exactly
> the same structure, exactly the same set of columns but with different
> types of data. But type of data doesn't matter when it comes to
> storage of this data in the database. The only thing that matters here
> is the set of columns. So you can create table like this:
>
>>KR:
>   I apologise, I did not intend to be vague; you made me realize how
>   vague I was. Also, I'm new to database design. I surely
>   appreciate your help, Pavel.
>
>  Here it is:
>   I have 10 tables with the exact same schema:
>   int, int, varchar(10), varchar(20). All the 10 tables have this same schema.
>   Each of the 10 tables belongs to 1 entity, uniquely identified by an 
> entity-id.
>   I was planning on having 10 tables named like so: typenumber_.
>   and not include entity_id into the schema.
>   But from reading your email, it sounds like i could just have 1 table, and
>   add entity id to it. So, add a 5th column like this:
>   int, int, varchar(10), varchar(20), int(this last one is the entity id and
>   it would be identical every 10 entries or so)
>   Would this be a better design ?
>   Can you point me to some database design docs that dictates that tables of
>   the same type should be 1 single table ?
>
>
>
> CREATE TABLE TypeNumbers (
>    ID INTEGER PRIMARY KEY AUTOINCREMENT,
>    table_type INTEGER,
>    MainID INTEGER,
>    Type INTEGER,
>    CurrentNumber INTEGER);
>
> And here is you "table_type" that will have numbers 1 to 10 and you
> will be able "to pass it to SQLite" (whatever that means).
>
>>> Would there be a way I can pass the schema as well as data into SQLite ?
>>
>> Elaborate please what do you want to do, where do you want to pass
>> "schema" (do you mean table name here?) and data?
>>
>> KR: The above should answer this question.
>
> It doesn't.
>
>> KR: Different threads are going to access different tables of the same table 
>> type.
>
> Even more puzzles here. I thought your table type is just one table,
> it seems that you have several tables of the same type but why? And
> what's the difference between tables of different types? Does this
> mean that you have more than 10 tables? Do you have only 10 "table
> types"?
>
>> Maybe I need to think this through, even as I write this:-)
>
> That definitely will help. :-)
>
>
> Pavel
>
> On Tue, Sep 15, 2009 at 6:01 PM, Kavita Raghunathan
> <ka

Re: [sqlite] Schema and database layout

[P Kishor]

On Wed, Sep 16, 2009 at 8:57 AM, Kavita Raghunathan
<kavita.raghunat...@skyfiber.com> wrote:
>
> - Original Message -
> From: "Pavel Ivanov" <paiva...@gmail.com>
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Sent: Wednesday, September 16, 2009 6:40:18 AM GMT -06:00 US/Canada Central
> Subject: Re: [sqlite] Schema and database layout
>
>> KR: The 10 tables contain different data, but instead of doing this 
>> statement (example below) over
>> and over, I want to just pass this table "type" because all my tables look 
>> exactly
>> like table "TypeNumbers", but they have different data, depending on the 
>> entity-id.
>
> You better stop telling puzzles and explain everything in thorough
> details so that we could understand it.
> "entity-id" - what is it? There's no such column in the table, you've
> never spoken about any entity, so we can just guess... "this table
> "type" - what table type? Give the definition please what you're
> calling "table type". "I want to just pass this table "type" - pass
> what, where and doing what? What are you doing that is hard and with
> "passing table type" will be easier?
> And continuing to look into my crystal ball I still don't understand
> why my suggestion doesn't suit you. You have 10 tables with exactly
> the same structure, exactly the same set of columns but with different
> types of data. But type of data doesn't matter when it comes to
> storage of this data in the database. The only thing that matters here
> is the set of columns. So you can create table like this:
>
>>KR:
>   I apologise, I did not intend to be vague; you made me realize how
>   vague I was. Also, I'm new to database design. I surely
>   appreciate your help, Pavel.
>
>  Here it is:
>   I have 10 tables with the exact same schema:
>   int, int, varchar(10), varchar(20). All the 10 tables have this same schema.
>   Each of the 10 tables belongs to 1 entity, uniquely identified by an 
> entity-id.
>   I was planning on having 10 tables named like so: typenumber_.
>   and not include entity_id into the schema.
>   But from reading your email, it sounds like i could just have 1 table, and
>   add entity id to it. So, add a 5th column like this:
>   int, int, varchar(10), varchar(20), int(this last one is the entity id and
>   it would be identical every 10 entries or so)
>   Would this be a better design ?

Yes, this would be a better design.

>   Can you point me to some database design docs that dictates that tables of
>   the same type should be 1 single table ?
>
>

Google for help with designing relational databases. You will get 36
million hits.


>
> CREATE TABLE TypeNumbers (
>    ID INTEGER PRIMARY KEY AUTOINCREMENT,
>    table_type INTEGER,
>    MainID INTEGER,
>    Type INTEGER,
>    CurrentNumber INTEGER);
>
> And here is you "table_type" that will have numbers 1 to 10 and you
> will be able "to pass it to SQLite" (whatever that means).
>
>>> Would there be a way I can pass the schema as well as data into SQLite ?
>>
>> Elaborate please what do you want to do, where do you want to pass
>> "schema" (do you mean table name here?) and data?
>>
>> KR: The above should answer this question.
>
> It doesn't.
>
>> KR: Different threads are going to access different tables of the same table 
>> type.
>
> Even more puzzles here. I thought your table type is just one table,
> it seems that you have several tables of the same type but why? And
> what's the difference between tables of different types? Does this
> mean that you have more than 10 tables? Do you have only 10 "table
> types"?
>
>> Maybe I need to think this through, even as I write this:-)
>
> That definitely will help. :-)
>
>
> Pavel
>
> On Tue, Sep 15, 2009 at 6:01 PM, Kavita Raghunathan
> <kavita.raghunat...@skyfiber.com> wrote:
>> Please see clarifications below:
>> - Original Message -
>> From: "Pavel Ivanov" <paiva...@gmail.com>
>> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>> Sent: Tuesday, September 15, 2009 4:36:19 PM GMT -06:00 US/Canada Central
>> Subject: Re: [sqlite] Schema and database layout
>>
>>> 1) dont see a way to reuse schemas. In other words, I have say 10 tables 
>>> with the same schema. How would I prevent doing 10 CREATE table commands ?
>>
>> Create 1 table with 1 additional column "schema" which will contain
>> numbers from 1 to 10.
>>

Re: [sqlite] Schema and database layout

[Kavita Raghunathan]

- Original Message -
From: "Pavel Ivanov" <paiva...@gmail.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Wednesday, September 16, 2009 6:40:18 AM GMT -06:00 US/Canada Central
Subject: Re: [sqlite] Schema and database layout

> KR: The 10 tables contain different data, but instead of doing this statement 
> (example below) over
> and over, I want to just pass this table "type" because all my tables look 
> exactly
> like table "TypeNumbers", but they have different data, depending on the 
> entity-id.

You better stop telling puzzles and explain everything in thorough
details so that we could understand it.
"entity-id" - what is it? There's no such column in the table, you've
never spoken about any entity, so we can just guess... "this table
"type" - what table type? Give the definition please what you're
calling "table type". "I want to just pass this table "type" - pass
what, where and doing what? What are you doing that is hard and with
"passing table type" will be easier?
And continuing to look into my crystal ball I still don't understand
why my suggestion doesn't suit you. You have 10 tables with exactly
the same structure, exactly the same set of columns but with different
types of data. But type of data doesn't matter when it comes to
storage of this data in the database. The only thing that matters here
is the set of columns. So you can create table like this:

>KR: 
   I apologise, I did not intend to be vague; you made me realize how
   vague I was. Also, I'm new to database design. I surely 
   appreciate your help, Pavel.

  Here it is:
   I have 10 tables with the exact same schema:
   int, int, varchar(10), varchar(20). All the 10 tables have this same schema.
   Each of the 10 tables belongs to 1 entity, uniquely identified by an 
entity-id.
   I was planning on having 10 tables named like so: typenumber_.
   and not include entity_id into the schema.
   But from reading your email, it sounds like i could just have 1 table, and
   add entity id to it. So, add a 5th column like this:
   int, int, varchar(10), varchar(20), int(this last one is the entity id and 
   it would be identical every 10 entries or so)
   Would this be a better design ?
   Can you point me to some database design docs that dictates that tables of 
   the same type should be 1 single table ?



CREATE TABLE TypeNumbers (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
table_type INTEGER,
MainID INTEGER,
Type INTEGER,
CurrentNumber INTEGER);

And here is you "table_type" that will have numbers 1 to 10 and you
will be able "to pass it to SQLite" (whatever that means).

>> Would there be a way I can pass the schema as well as data into SQLite ?
>
> Elaborate please what do you want to do, where do you want to pass
> "schema" (do you mean table name here?) and data?
>
> KR: The above should answer this question.

It doesn't.

> KR: Different threads are going to access different tables of the same table 
> type.

Even more puzzles here. I thought your table type is just one table,
it seems that you have several tables of the same type but why? And
what's the difference between tables of different types? Does this
mean that you have more than 10 tables? Do you have only 10 "table
types"?

> Maybe I need to think this through, even as I write this:-)

That definitely will help. :-)


Pavel

On Tue, Sep 15, 2009 at 6:01 PM, Kavita Raghunathan
<kavita.raghunat...@skyfiber.com> wrote:
> Please see clarifications below:
> - Original Message -
> From: "Pavel Ivanov" <paiva...@gmail.com>
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Sent: Tuesday, September 15, 2009 4:36:19 PM GMT -06:00 US/Canada Central
> Subject: Re: [sqlite] Schema and database layout
>
>> 1) dont see a way to reuse schemas. In other words, I have say 10 tables 
>> with the same schema. How would I prevent doing 10 CREATE table commands ?
>
> Create 1 table with 1 additional column "schema" which will contain
> numbers from 1 to 10.
>
> KR: The 10 tables contain different data, but instead of doing this statement 
> (example below) over
> and over, I want to just pass this table "type" because all my tables look 
> exactly
> like table "TypeNumbers", but they have different data, depending on the 
> entity-id.
>
> strcpy(sqlStr,"CREATE TABLE TypeNumbers (");
>       strcat(sqlStr,"ID INTEGER PRIMARY KEY AUTOINCREMENT,");
>       strcat(sqlStr,"MainID INTEGER,");
>       strcat(sqlStr,"Type INTEGER,");
>       strcat(sqlStr,"CurrentNumber INTEGER);");
>
>> Would there b

Re: [sqlite] Schema and database layout

[Pavel Ivanov]

> KR: The 10 tables contain different data, but instead of doing this statement 
> (example below) over
> and over, I want to just pass this table "type" because all my tables look 
> exactly
> like table "TypeNumbers", but they have different data, depending on the 
> entity-id.

You better stop telling puzzles and explain everything in thorough
details so that we could understand it.
"entity-id" - what is it? There's no such column in the table, you've
never spoken about any entity, so we can just guess... "this table
"type" - what table type? Give the definition please what you're
calling "table type". "I want to just pass this table "type" - pass
what, where and doing what? What are you doing that is hard and with
"passing table type" will be easier?
And continuing to look into my crystal ball I still don't understand
why my suggestion doesn't suit you. You have 10 tables with exactly
the same structure, exactly the same set of columns but with different
types of data. But type of data doesn't matter when it comes to
storage of this data in the database. The only thing that matters here
is the set of columns. So you can create table like this:

CREATE TABLE TypeNumbers (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
table_type INTEGER,
MainID INTEGER,
Type INTEGER,
CurrentNumber INTEGER);

And here is you "table_type" that will have numbers 1 to 10 and you
will be able "to pass it to SQLite" (whatever that means).

>> Would there be a way I can pass the schema as well as data into SQLite ?
>
> Elaborate please what do you want to do, where do you want to pass
> "schema" (do you mean table name here?) and data?
>
> KR: The above should answer this question.

It doesn't.

> KR: Different threads are going to access different tables of the same table 
> type.

Even more puzzles here. I thought your table type is just one table,
it seems that you have several tables of the same type but why? And
what's the difference between tables of different types? Does this
mean that you have more than 10 tables? Do you have only 10 "table
types"?

> Maybe I need to think this through, even as I write this:-)

That definitely will help. :-)


Pavel

On Tue, Sep 15, 2009 at 6:01 PM, Kavita Raghunathan
<kavita.raghunat...@skyfiber.com> wrote:
> Please see clarifications below:
> - Original Message -
> From: "Pavel Ivanov" <paiva...@gmail.com>
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Sent: Tuesday, September 15, 2009 4:36:19 PM GMT -06:00 US/Canada Central
> Subject: Re: [sqlite] Schema and database layout
>
>> 1) dont see a way to reuse schemas. In other words, I have say 10 tables 
>> with the same schema. How would I prevent doing 10 CREATE table commands ?
>
> Create 1 table with 1 additional column "schema" which will contain
> numbers from 1 to 10.
>
> KR: The 10 tables contain different data, but instead of doing this statement 
> (example below) over
> and over, I want to just pass this table "type" because all my tables look 
> exactly
> like table "TypeNumbers", but they have different data, depending on the 
> entity-id.
>
> strcpy(sqlStr,"CREATE TABLE TypeNumbers (");
>       strcat(sqlStr,"ID INTEGER PRIMARY KEY AUTOINCREMENT,");
>       strcat(sqlStr,"MainID INTEGER,");
>       strcat(sqlStr,"Type INTEGER,");
>       strcat(sqlStr,"CurrentNumber INTEGER);");
>
>> Would there be a way I can pass the schema as well as data into SQLite ?
>
> Elaborate please what do you want to do, where do you want to pass
> "schema" (do you mean table name here?) and data?
>
> KR: The above should answer this question.
>
>> 2)Also, do you reccomend a different .db file for each schema so that 
>> multiple threads can easily operate at the same time ?
>
> It depends on how your threads will operate, how often they will issue
> sql statements, what type of sql statements and all other details
> about your application functionality.
>
> KR: Different threads are going to access different tables of the same table 
> type.
> Maybe I need to think this through, even as I write this:-)
>
> Thanks, Pavel!
>
>
> On Tue, Sep 15, 2009 at 5:27 PM, Kavita Raghunathan
> <kavita.raghunat...@skyfiber.com> wrote:
>> All,
>> I have a couple of questions:
>>
>> I've been studying the C/C++ interface, and
>> 1) dont see a way to reuse schemas. In other words, I have say 10 tables 
>> with the same schema. How would I prevent doing 10 CREATE table commands ? 
>> Would there be a way I can pass th

Re: [sqlite] Schema and database layout

[Kavita Raghunathan]

Please see clarifications below:
- Original Message -
From: "Pavel Ivanov" <paiva...@gmail.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Tuesday, September 15, 2009 4:36:19 PM GMT -06:00 US/Canada Central
Subject: Re: [sqlite] Schema and database layout

> 1) dont see a way to reuse schemas. In other words, I have say 10 tables with 
> the same schema. How would I prevent doing 10 CREATE table commands ?

Create 1 table with 1 additional column "schema" which will contain
numbers from 1 to 10.

KR: The 10 tables contain different data, but instead of doing this statement 
(example below) over
and over, I want to just pass this table "type" because all my tables look 
exactly
like table "TypeNumbers", but they have different data, depending on the 
entity-id.

strcpy(sqlStr,"CREATE TABLE TypeNumbers (");
   strcat(sqlStr,"ID INTEGER PRIMARY KEY AUTOINCREMENT,");
   strcat(sqlStr,"MainID INTEGER,");
   strcat(sqlStr,"Type INTEGER,");
   strcat(sqlStr,"CurrentNumber INTEGER);");

> Would there be a way I can pass the schema as well as data into SQLite ?

Elaborate please what do you want to do, where do you want to pass
"schema" (do you mean table name here?) and data?

KR: The above should answer this question.

> 2)Also, do you reccomend a different .db file for each schema so that 
> multiple threads can easily operate at the same time ?

It depends on how your threads will operate, how often they will issue
sql statements, what type of sql statements and all other details
about your application functionality.

KR: Different threads are going to access different tables of the same table 
type.
Maybe I need to think this through, even as I write this:-)

Thanks, Pavel!


On Tue, Sep 15, 2009 at 5:27 PM, Kavita Raghunathan
<kavita.raghunat...@skyfiber.com> wrote:
> All,
> I have a couple of questions:
>
> I've been studying the C/C++ interface, and
> 1) dont see a way to reuse schemas. In other words, I have say 10 tables with 
> the same schema. How would I prevent doing 10 CREATE table commands ? Would 
> there be a way I can pass the schema as well as data into SQLite ?
>
> 2)Also, do you reccomend a different .db file for each schema so that 
> multiple threads can easily operate at the same time ?
>
> I apologise in advance if I missed it in the documentation.
>
> Thanks again to this group for your awesome support. I've got a lot of ideas 
> from you all.
>
> Regards,
> Kavita
> ___
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Schema and database layout

[Simon Slavin]

On 15 Sep 2009, at 10:27pm, Kavita Raghunathan wrote:

> 1) dont see a way to reuse schemas. In other words, I have say 10  
> tables with the same schema. How would I prevent doing 10 CREATE  
> table commands ? Would there be a way I can pass the schema as well  
> as data into SQLite ?

If you find yourself doing this, you might find it better to  
reorganise your data.  Merge all the tables into one table, and have  
an extra column ('category' ?) to say which type each row is.

> 2)Also, do you reccomend a different .db file for each schema so  
> that multiple threads can easily operate at the same time ?

If you do this, then question 1 is answered: duplicate the database  
file (using file-handling calls, not SQL calls) and you get duplicate  
contents.

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

Re: [sqlite] Schema and database layout

[Pavel Ivanov]

> 1) dont see a way to reuse schemas. In other words, I have say 10 tables with 
> the same schema. How would I prevent doing 10 CREATE table commands ?

Create 1 table with 1 additional column "schema" which will contain
numbers from 1 to 10.

> Would there be a way I can pass the schema as well as data into SQLite ?

Elaborate please what do you want to do, where do you want to pass
"schema" (do you mean table name here?) and data?

> 2)Also, do you reccomend a different .db file for each schema so that 
> multiple threads can easily operate at the same time ?

It depends on how your threads will operate, how often they will issue
sql statements, what type of sql statements and all other details
about your application functionality.


Pavel

On Tue, Sep 15, 2009 at 5:27 PM, Kavita Raghunathan
 wrote:
> All,
> I have a couple of questions:
>
> I've been studying the C/C++ interface, and
> 1) dont see a way to reuse schemas. In other words, I have say 10 tables with 
> the same schema. How would I prevent doing 10 CREATE table commands ? Would 
> there be a way I can pass the schema as well as data into SQLite ?
>
> 2)Also, do you reccomend a different .db file for each schema so that 
> multiple threads can easily operate at the same time ?
>
> I apologise in advance if I missed it in the documentation.
>
> Thanks again to this group for your awesome support. I've got a lot of ideas 
> from you all.
>
> Regards,
> Kavita
> ___
> 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

[sqlite] Schema and database layout

[Kavita Raghunathan]

All,
I have a couple of questions:

I've been studying the C/C++ interface, and 
1) dont see a way to reuse schemas. In other words, I have say 10 tables with 
the same schema. How would I prevent doing 10 CREATE table commands ? Would 
there be a way I can pass the schema as well as data into SQLite ? 

2)Also, do you reccomend a different .db file for each schema so that multiple 
threads can easily operate at the same time ?

I apologise in advance if I missed it in the documentation.

Thanks again to this group for your awesome support. I've got a lot of ideas 
from you all.

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

[sqlite] Generate an sqlite schema from a postgres dump

[Paolo Tell]

I googled and checked the archive of this mailing list without finding an
answer to this topic.

Is there a way to automaticaly create a db from a postgres dump? Including
an appropriate conversion of the features that are different in the two
database engines.

Before starting doing it manually I would like to know if there is such a
tool/script/way.

Thank you in advance.
P. Tell
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Schema design and/or SELECT construction

[Craig Smith]

Chris and Jay:

Thank you both very much for  your comments; that solves it.  I am not  
a trained database designer, but I have resources on normalization; I  
simply neglected to consult them, thinking that this was a SELECT  
problem, not a design problem.  Your input was just what I was hoping  
for.

> The concept of core and other keywords is a bit arbitrary.
> What is important (i.e. core) today might not be so tomorrow.
>
> Parsing comma separated lists in a single attribute is
> likely to be a bother.

>  Generally, I would call this a bad idea.  By most people's
>  thinking it also breaks First Normal Form.
>
>  Besides, you can't do database operations on comma separated lists.
>  If you need a one-to-many (one pic to many keywords) then do it right
>  and build an actual one-to-many relationship between two tables.

Craig Smith
cr...@macscripter.net



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

Re: [sqlite] Schema design and/or SELECT construction

[Jay A. Kreibich]

On Mon, Jun 01, 2009 at 09:21:40PM -0700, Craig Smith scratched on the wall:
> Hello:
> 
> I have about 3000 electronic images that I am preparing to distribute  
> to my family members.  They use a variety of operating systems, so I  
> am providing the images on remote hard drives, with the images divided  
> into folders based on years the images were created.  All images were  
> obtained via scanning of negatives and slides, and each image has been  
> keyworded in EXIF format.
> 
> What I want is to create a master SQLite database catalog of all the  
> images, and I have come up with a proposed schema:
> 
> TABLE main (all pertinent image data such as date, location in folder,  
> etc.)
> TABLE core_keywords (id, name) --This table would only hold the names  
> of my immediate family members, each with a corresponding id
> TABLE other_keyword (id,name) -- all other keywords

> I have thought to create two keyword fields in the main table, one to  
> hold the ids of the core_keywords (comma separated) and one to hold  
> the ids of the other_keywords, also comma separated.

  Generally, I would call this a bad idea.  By most people's
  thinking it also breaks First Normal Form.
  
  Besides, you can't do database operations on comma separated lists.
  If you need a one-to-many (one pic to many keywords) then do it right
  and build an actual one-to-many relationship between two tables.

  Although, in this case, I'd say it is actually many-to-many.

  Might I suggest:

TABLE pictures (pic_id INTEGER PRIMARY KEY, filename NOT NULL UNIQUE, . );
TABLE keywords (key_id INTEGER PRIMARY KEY, is_core bool, word NOT NULL UNIQUE);
TABLE pic_to_key (pic_id, key_id, PRIMARY KEY (pic_id, key_id));
INDEX pic_to_key (key_id, pick_id);  -- just because

  Define your pictures.
  Define your keywords.  You can flag specific keywords as "core."
  Define which pics have which keywords.

  You can then use a keyword to find pictures, or use pictures to find
  keywords.

> What I cannot  
> devise is an elegant method to SELECT based on the core_keywords to  
> achieve the following sorts:
> 
> 1- Find images with a single core_keyword id, that is, only images of  
> a single person, no other core persons in the image

  Lookup the key_id.  Find all the pics with that key idea.  Find all
  the key ids for those pictures.  Count them, select the pics with 1.

> 2- Find images with a specific set of core_keyword ids, such as 1 and  
> 6 or 2 and 5 and 7, etc., with no other core persons in the image

  Same basic idea, left as an exercise to the reader.

  I'll offer this hint, however:

  This is a situation that will sometimes lead to the need to do
  Relational Divison, something that isn't directly supported by SQL.
  Have a look at Celko's article on it:
  http://www.dbazine.com/ofinterest/oi-articles/celko1

  You'll often end up with a "vertical" list of key_ids (a picture
  joined against pic_to_key to get a list of keys) and what you need is
  a "horizontal" list to run through your logic statements.  That all
  points to Relational Division.

  Or you can just move the selection logic to your applicaiton.


   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Schema design and/or SELECT construction

[Chris Peachment]

What's wrong with a properly normalised schema like this:

create table main (id, name,...);

create table keyword (id, label);

create table crossref (id_main, id_keyword);

The concept of core and other keywords is a bit arbitrary.
What is important (i.e. core) today might not be so tomorrow.

Parsing comma separated lists in a single attribute is
likely to be a bother.


On Mon, 2009-06-01 at 21:21 -0700, Craig Smith wrote:
> Hello:
> 
> I have about 3000 electronic images that I am preparing to distribute  
> to my family members.  They use a variety of operating systems, so I  
> am providing the images on remote hard drives, with the images divided  
> into folders based on years the images were created.  All images were  
> obtained via scanning of negatives and slides, and each image has been  
> keyworded in EXIF format.
> 
> What I want is to create a master SQLite database catalog of all the  
> images, and I have come up with a proposed schema:
> 
> TABLE main (all pertinent image data such as date, location in folder,  
> etc.)
> TABLE core_keywords (id, name) --This table would only hold the names  
> of my immediate family members, each with a corresponding id
> TABLE other_keyword (id,name) -- all other keywords
> 
> I have thought to create two keyword fields in the main table, one to  
> hold the ids of the core_keywords (comma separated) and one to hold  
> the ids of the other_keywords, also comma separated.  What I cannot  
> devise is an elegant method to SELECT based on the core_keywords to  
> achieve the following sorts:
> 
> 1- Find images with a single core_keyword id, that is, only images of  
> a single person, no other core persons in the image
> 2- Find images with a specific set of core_keyword ids, such as 1 and  
> 6 or 2 and 5 and 7, etc., with no other core persons in the image
> 
> The idea is to create a document with lists of all images that are  
> exclusive to single individuals, specific pairs, etc., so that family  
> members can easily find themselves or groups, regardless of image  
> catalog software they use on their particular systems, which may or  
> may not be able to perform these types of sorts.
> 
> I am not asking anyone to actually write the SELECT statements for me,  
> but rather point me toward the operands that would achieve my goal.  I  
> have read through the documentation, and I cannot seem to generate the  
> logic in my head to SELECT WHERE core_id is only 4.
> 
> If anyone has an idea on a more efficient database design, or TABLE  
> schema, please do not hesitate to proffer your thoughts.  I am hoping  
> to have it all figured out BEFORE I load up the tables with data.  (I  
> am actually still scanning images at this stage, but trying to prepare  
> for the next phase.)
> 
> Thank you very much for your time and consideration.
> 
> 
> Craig Smith
> cr...@macscripter.net
> 
> 
> 
> ___
> 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

[sqlite] Schema design and/or SELECT construction

[Craig Smith]

Hello:

I have about 3000 electronic images that I am preparing to distribute  
to my family members.  They use a variety of operating systems, so I  
am providing the images on remote hard drives, with the images divided  
into folders based on years the images were created.  All images were  
obtained via scanning of negatives and slides, and each image has been  
keyworded in EXIF format.

What I want is to create a master SQLite database catalog of all the  
images, and I have come up with a proposed schema:

TABLE main (all pertinent image data such as date, location in folder,  
etc.)
TABLE core_keywords (id, name) --This table would only hold the names  
of my immediate family members, each with a corresponding id
TABLE other_keyword (id,name) -- all other keywords

I have thought to create two keyword fields in the main table, one to  
hold the ids of the core_keywords (comma separated) and one to hold  
the ids of the other_keywords, also comma separated.  What I cannot  
devise is an elegant method to SELECT based on the core_keywords to  
achieve the following sorts:

1- Find images with a single core_keyword id, that is, only images of  
a single person, no other core persons in the image
2- Find images with a specific set of core_keyword ids, such as 1 and  
6 or 2 and 5 and 7, etc., with no other core persons in the image

The idea is to create a document with lists of all images that are  
exclusive to single individuals, specific pairs, etc., so that family  
members can easily find themselves or groups, regardless of image  
catalog software they use on their particular systems, which may or  
may not be able to perform these types of sorts.

I am not asking anyone to actually write the SELECT statements for me,  
but rather point me toward the operands that would achieve my goal.  I  
have read through the documentation, and I cannot seem to generate the  
logic in my head to SELECT WHERE core_id is only 4.

If anyone has an idea on a more efficient database design, or TABLE  
schema, please do not hesitate to proffer your thoughts.  I am hoping  
to have it all figured out BEFORE I load up the tables with data.  (I  
am actually still scanning images at this stage, but trying to prepare  
for the next phase.)

Thank you very much for your time and consideration.


Craig Smith
cr...@macscripter.net



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

Re: [sqlite] Schema syntax error

[D. Richard Hipp]

On Mar 18, 2009, at 9:39 PM, Tristan Seligmann wrote:

> Divmod Axiom[1] is a Python ORM built on SQLite; one of the book
> keeping tables it creates in the database has a column named
> "indexed", which became a reserved word around SQLite 3.6.4 (?). The
> "obvious" fix for this problem is to simply quote the column name
> using "", but the problem is that it is now impossible to load older
> databases which didn't have the column created with the name quoted:
>
> Error: malformed database schema (axiom_attributes) - near "indexed":
> syntax error
>
> What sort of migration path exists for converting / fixing these old
> databases? Ideally there would be some mechanism that does not require
> reinstalling an older version of SQLite.

See http://www.sqlite.org/cvstrac/chngview?cn=6370

Beginning with the next release, the parser has been modified to allow  
INDEXED to be used as the name of a table or index or column.


>
> -- 
> mithrandi, i Ainil en-Balandor, a faer Ambar
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Schema syntax error

[Kees Nuyt]

On Thu, 19 Mar 2009 03:39:11 +0200, Tristan Seligmann
 wrote:

>Divmod Axiom[1] is a Python ORM built on SQLite; one of the book
>keeping tables it creates in the database has a column named
>"indexed", which became a reserved word around SQLite 3.6.4 (?). The
>"obvious" fix for this problem is to simply quote the column name
>using "", but the problem is that it is now impossible to load older
>databases which didn't have the column created with the name quoted:
>
>Error: malformed database schema (axiom_attributes) - near "indexed":
>syntax error
>
>What sort of migration path exists for converting / fixing these old
>databases? Ideally there would be some mechanism that does not require
>reinstalling an older version of SQLite.

Digging in the mailing list archives, I found this:

==
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] FTS and upgrades
From: d...@hwaci.com
Date: Tue, 10 Jul 2007 22:26:21 +

I probably shouldn't tell you this, but

There is a pragma:

   PRAGMA writable_schema=ON;

Which when enabled allows you to UPDATE or 
DELETE against the sqlite_master table.
==

Using this PRAGMA, you can UPDATE the sql column in the
sqlite_master table. Of course this is undocumented and
unsupported, and you risk corrupting your databases.
Backups and rigorous testing required.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Schema syntax error

[Tristan Seligmann]

Divmod Axiom[1] is a Python ORM built on SQLite; one of the book
keeping tables it creates in the database has a column named
"indexed", which became a reserved word around SQLite 3.6.4 (?). The
"obvious" fix for this problem is to simply quote the column name
using "", but the problem is that it is now impossible to load older
databases which didn't have the column created with the name quoted:

Error: malformed database schema (axiom_attributes) - near "indexed":
syntax error

What sort of migration path exists for converting / fixing these old
databases? Ideally there would be some mechanism that does not require
reinstalling an older version of SQLite.
-- 
mithrandi, i Ainil en-Balandor, a faer Ambar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema optimization question

[Stephen Oberholtzer]

On Fri, May 23, 2008 at 3:01 PM, Jeff Gibson <[EMAIL PROTECTED]> wrote:

> I'm sorry if this is an obvious question - I'm new to databases.  I have
> an application where the database is used to log a large number of
> simulation events.  The database is written once and read many times
> (i.e., there are never any inserts or updates after database creation).
> The three most interesting tables I have are:
>
> CREATE TABLE events (eid INTEGER PRIMARY KEY, time INTEGER, aid INTEGER,
> subtype INTEGER);
>
> CREATE TABLE actions (aid INTEGER PRIMARY KEY, type INTEGER, seqnum
> INTEGER, tid INTEGER, instid INTEGER);
>
> CREATE TABLE subtypes (type INTEGER, subtype INTEGER, name TEXT, verbose
> INTEGER, PRIMARY KEY(type,subtype) );
>
> The column names are such that columns in different tables with the same
> name act as foreign keys.  The largest (and most often queried) table is
> events, and it can have many millions of entries.  The actions table is
> also large (about a fifth as big as events) and subtypes is very small
> (dozens of entries).  My application involves querying events many
> times, but very common queries include events that match a particular
> verbose value and/or a particular type value.  This leads to queries
> that have one or two joins, and such queries are substantially slower
> than just a query on just the events table.
>The question is, what can I do to speed up those queries?  The
> obvious answer would be to put type and verbose as columns in the events
> table, but they would be redundant.  Is that par for the course, or is
> there some best practice I'm overlooking?
>Thanks,
>Jeff
>

Well, I'd like to note that in general, reporting databases are denormalized
in order to improve performance.
However, you shouldn't need to do that for a case this simple.

Let's start by creating your database and having fun with EXPLAIN QUERY
PLAN.  I created the tables as you provided and did this:

create view eventdetail as select e.eid as eid, e.time as time, a.aid as
aid, a.seqnum as seqnum, a.tid as tid, a.instid as instid, s.type as type,
s.subtype as subtype, s.name as name, s.verbose as verbose from events e
join actions a on a.aid=e.aid join subtypes s on s.subtype = e.subtype;


This makes life a lot easier.  Besides, SQLite is extremely well-written and
will handle this stuff beautifully.
( I noticed that you have a 'type' on both the 'actions' table and the
'subtypes' table. I assume that they are the same thing and used the version
from subtypes. )

sqlite> explain query plan select * from eventdetail where type=123;
orde  from   deta
  -  
0 0  TABLE events AS e
1 1  TABLE actions AS a USING PRIMARY KEY
2 2  TABLE subtypes AS s WITH INDEX
sqlite_autoindex_subtypes_1

You can see here that we are table-scanning 'events'.   This is bad.  The
solution here is to add an index so events can be searched by subtype:

[[ create index events_subtype_ix on events(subtype); ]]

sqlite> explain query plan select * from eventdetail where type=123;
orde  from   deta
  -  
0 2  TABLE subtypes AS s WITH INDEX
sqlite_autoindex_subtypes_1
1 0  TABLE events AS e WITH INDEX events_subtype_ix
2 1  TABLE actions AS a USING PRIMARY KEY

As you can see here, SQLite is actually figuring out which subtypes have
type=123, then looking up that subset of the 'events' table using the newly
created index, then joining to 'actions' based on the 'aid' column.  All in
all, if there are very many different top-level types, the first one will
only find a few subtypes (the term is 'high selectivity').

In general, EXPLAIN QUERY PLAN  will give you a good idea on what
SQLite is doing to perform the requested actions.  Index tweaking and
ANALYZE (http://www.sqlite.org/lang_analyze.html) will enable you to filter
out the amount of data SQLite has to consider when returning a resultset.

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema optimization question

[Keith Goodman]

On Fri, May 23, 2008 at 12:01 PM, Jeff Gibson <[EMAIL PROTECTED]> wrote:
> I'm sorry if this is an obvious question - I'm new to databases.  I have
> an application where the database is used to log a large number of
> simulation events.  The database is written once and read many times
> (i.e., there are never any inserts or updates after database creation).
> The three most interesting tables I have are:
>
> CREATE TABLE events (eid INTEGER PRIMARY KEY, time INTEGER, aid INTEGER,
> subtype INTEGER);
>
> CREATE TABLE actions (aid INTEGER PRIMARY KEY, type INTEGER, seqnum
> INTEGER, tid INTEGER, instid INTEGER);
>
> CREATE TABLE subtypes (type INTEGER, subtype INTEGER, name TEXT, verbose
> INTEGER, PRIMARY KEY(type,subtype) );
>
> The column names are such that columns in different tables with the same
> name act as foreign keys.  The largest (and most often queried) table is
> events, and it can have many millions of entries.  The actions table is
> also large (about a fifth as big as events) and subtypes is very small
> (dozens of entries).  My application involves querying events many
> times, but very common queries include events that match a particular
> verbose value and/or a particular type value.  This leads to queries
> that have one or two joins, and such queries are substantially slower
> than just a query on just the events table.
>The question is, what can I do to speed up those queries?  The
> obvious answer would be to put type and verbose as columns in the events
> table, but they would be redundant.  Is that par for the course, or is
> there some best practice I'm overlooking?

I'm new to databases too. But since no one has answered I'll take a
stab at it: make an index for each table on the column that you join.
That should speed up the joins. But I don't think it will be as fast
as putting everything in one table and making an index on columns you
do WHERE on.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] schema optimization question

[Jeff Gibson]

I'm sorry if this is an obvious question - I'm new to databases.  I have 
an application where the database is used to log a large number of 
simulation events.  The database is written once and read many times 
(i.e., there are never any inserts or updates after database creation).  
The three most interesting tables I have are:

CREATE TABLE events (eid INTEGER PRIMARY KEY, time INTEGER, aid INTEGER, 
subtype INTEGER);

CREATE TABLE actions (aid INTEGER PRIMARY KEY, type INTEGER, seqnum 
INTEGER, tid INTEGER, instid INTEGER);

CREATE TABLE subtypes (type INTEGER, subtype INTEGER, name TEXT, verbose 
INTEGER, PRIMARY KEY(type,subtype) );

The column names are such that columns in different tables with the same 
name act as foreign keys.  The largest (and most often queried) table is 
events, and it can have many millions of entries.  The actions table is 
also large (about a fifth as big as events) and subtypes is very small 
(dozens of entries).  My application involves querying events many 
times, but very common queries include events that match a particular 
verbose value and/or a particular type value.  This leads to queries 
that have one or two joins, and such queries are substantially slower 
than just a query on just the events table.
The question is, what can I do to speed up those queries?  The 
obvious answer would be to put type and verbose as columns in the events 
table, but they would be redundant.  Is that par for the course, or is 
there some best practice I'm overlooking?
Thanks,
Jeff


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

Re: [sqlite] schema design question

[Jay A. Kreibich]

On Fri, Apr 11, 2008 at 01:54:43PM -0700, Richard Klein scratched on the wall:
> Jay A. Kreibich wrote:
> >On Thu, Apr 10, 2008 at 05:58:59PM -0700, Richard Klein scratched on the 
> >wall:
> >
> >>My advice would be to try it and see.  If table creation takes too long,
> >>you can always remove the UNIQUE constraint, and then write a routine to
> >>check the table for uniqueness after it's created.
> >
> >  That "routine" could simply be creating an explicit unique index on
> >  the column after all the data is loaded.  If the index can be
> >  created, you're good to go.
> >
> >  This is still a slow process, but I know the creation of indexes on
> >  existing tables is one area the development team hopes to speed up.
> >  I'm not sure what the priority of that is, however.
> 
> Wouldn't this take just as long as creating the index immediately after
> creating the table, and then letting each INSERT update the index?

  There are savings to be found when you have the whole set of indexes
  available to you, just as you get some savings from batching up
  INSERTs.  If you have a large enough page cache, you might be able to
  do a substantial part of the sort in one disk pass.  If the sorter has
  the whole set avaliable, there's also the issue of how you deal
  with disk management and what sorting algorithm is used.  Current
  thought is that there is room for improvement:

  http://www.mail-archive.com/sqlite-users@sqlite.org/msg31481.html

  Although that obviously doesn't help in the here and now.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema design question

[Jeff Gibson]

Thanks for all the suggestions.  My schema is now a lot cleaner, and my 
application runs 30% faster!
Jeff

Richard Klein wrote:
>> Jeff Gibson wrote:
>> 
>>> One thing your earlier suggestion brought up.  The way I was hooking up 
>>> tables before was something along the lines of:
>>>
>>> CREATE TABLE primary(id1 INTEGER PRIMARY KEY, );
>>> CREATE TABLE secondary(id2 INTEGER PRIMARY KEY, );
>>> CREATE TABLE link(id1 INTEGER, id2 INTEGER);
>>>
>>> My understanding of your suggestion is:
>>>
>>> CREATE TABLE primary(id1 INTEGER PRIMARY KEY, id2 INTEGER, 
>>> );
>>> CREATE TABLE secondary(id2 INTEGER PRIMARY KEY, );
>>>
>>> with the understanding that id2 in primary will often be NULL.  Are 
>>> there any circumstances where the first alternative is more 
>>> appropriate?  I'm pretty new to databases, but I got my original idea 
>>> from a few schemas that I've seen.  I'm just trying to understand the 
>>> trade-offs.
>>> Thanks a lot for your help,
>>> Jeff
>>>
>>>   
>> These different forms of linking the records are used for different 
>> types of relations. The two tables can have records that are related in 
>> a various combinations of one or many to one or many.
>>
>>  one to one
>>  many to one
>>  one to many
>>  many to many
>>
>> Using a third table is required to implement a many to many relation. 
>> Each record in the third table stores one item of the relation (i.e 
>> which record in the first table is related to which record in the second 
>> table).
>>
>> A one to many relation is created by assigning an id to the record in 
>> the one side of the relation and referencing that id in a column on the 
>> many side of the relation. A many to one relation is the same a one to 
>> many relation, with the order of the tables reversed. This is what you 
>> have shown as Richard's suggestion.
>>
>> A one to one relation can be created by assigning an id to one record 
>> and using that same id as the primary key on the related record.
>>
>> For your case, you need a one to one relation between the primary and 
>> secondary tables. This can be done by using the same id for the related 
>> record in the secondary table as was assigned to the record in the 
>> primary table.
>>
>> CREATE TABLE primary(id INTEGER PRIMARY KEY, );
>> CREATE TABLE secondary(id INTEGER PRIMARY KEY, );
>>
>> insert into primary values(null, );
>> insert into secondary values(last_insert_rowid(), );
>>
>> When you want to retrieve the records you can use a join
>>
>> select * from primary join secondary using(id);
>>
>> or you can use a second select to retrieve the secondary fields using 
>> the id obtained from the primary field.
>>
>> select * from primary;
>> if (has_secondary())
>>  select * from secondary where id = primary.id;
>>
>> This does not waste any space storing unnecessary null fields. You 
>> should only resort the more complex foreign keys when you need to 
>> represent a more complex relation.
>>
>> HTH
>> Dennis Cote
>> 
>
> As Dennis points out, I had assumed that the relationship between the
> primary and secondary tables was many-to-one, i.e. that several entries
> in the primary table could refer to the same entry in the secondary
> table.
>
> If that is not the case -- if the relationship is in fact one-to-one --
> then Dennis's solution is the best one.
>
> I would use Dennis's two-SELECT approach rather than the join if speed
> is an issue.
>
> - Richard
> ___
> 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] schema design question

[Richard Klein]

Jay A. Kreibich wrote:
> On Thu, Apr 10, 2008 at 05:58:59PM -0700, Richard Klein scratched on the wall:
> 
>> My advice would be to try it and see.  If table creation takes too long,
>> you can always remove the UNIQUE constraint, and then write a routine to
>> check the table for uniqueness after it's created.
> 
>   That "routine" could simply be creating an explicit unique index on
>   the column after all the data is loaded.  If the index can be
>   created, you're good to go.
> 
>   This is still a slow process, but I know the creation of indexes on
>   existing tables is one area the development team hopes to speed up.
>   I'm not sure what the priority of that is, however.
> 
>-j
> 

Wouldn't this take just as long as creating the index immediately after
creating the table, and then letting each INSERT update the index?

Creating the index after all the data is loaded requires that the first
row be inserted into the index Btree, then the second row be inserted
into the Btree at the proper place, then the third row, and so on,
re-balancing the Btree as necessary.

Isn't this exactly the same process that takes place when you create
the index first and then insert each row?

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

Re: [sqlite] schema design question

[Richard Klein]

> Jeff Gibson wrote:
>> One thing your earlier suggestion brought up.  The way I was hooking up 
>> tables before was something along the lines of:
>>
>> CREATE TABLE primary(id1 INTEGER PRIMARY KEY, );
>> CREATE TABLE secondary(id2 INTEGER PRIMARY KEY, );
>> CREATE TABLE link(id1 INTEGER, id2 INTEGER);
>>
>> My understanding of your suggestion is:
>>
>> CREATE TABLE primary(id1 INTEGER PRIMARY KEY, id2 INTEGER, 
>> );
>> CREATE TABLE secondary(id2 INTEGER PRIMARY KEY, );
>>
>> with the understanding that id2 in primary will often be NULL.  Are 
>> there any circumstances where the first alternative is more 
>> appropriate?  I'm pretty new to databases, but I got my original idea 
>> from a few schemas that I've seen.  I'm just trying to understand the 
>> trade-offs.
>> Thanks a lot for your help,
>> Jeff
>>
> 
> These different forms of linking the records are used for different 
> types of relations. The two tables can have records that are related in 
> a various combinations of one or many to one or many.
> 
>  one to one
>  many to one
>  one to many
>  many to many
> 
> Using a third table is required to implement a many to many relation. 
> Each record in the third table stores one item of the relation (i.e 
> which record in the first table is related to which record in the second 
> table).
> 
> A one to many relation is created by assigning an id to the record in 
> the one side of the relation and referencing that id in a column on the 
> many side of the relation. A many to one relation is the same a one to 
> many relation, with the order of the tables reversed. This is what you 
> have shown as Richard's suggestion.
> 
> A one to one relation can be created by assigning an id to one record 
> and using that same id as the primary key on the related record.
> 
> For your case, you need a one to one relation between the primary and 
> secondary tables. This can be done by using the same id for the related 
> record in the secondary table as was assigned to the record in the 
> primary table.
> 
> CREATE TABLE primary(id INTEGER PRIMARY KEY, );
> CREATE TABLE secondary(id INTEGER PRIMARY KEY, );
> 
> insert into primary values(null, );
> insert into secondary values(last_insert_rowid(), );
> 
> When you want to retrieve the records you can use a join
> 
> select * from primary join secondary using(id);
> 
> or you can use a second select to retrieve the secondary fields using 
> the id obtained from the primary field.
> 
> select * from primary;
> if (has_secondary())
>  select * from secondary where id = primary.id;
> 
> This does not waste any space storing unnecessary null fields. You 
> should only resort the more complex foreign keys when you need to 
> represent a more complex relation.
> 
> HTH
> Dennis Cote

As Dennis points out, I had assumed that the relationship between the
primary and secondary tables was many-to-one, i.e. that several entries
in the primary table could refer to the same entry in the secondary
table.

If that is not the case -- if the relationship is in fact one-to-one --
then Dennis's solution is the best one.

I would use Dennis's two-SELECT approach rather than the join if speed
is an issue.

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

Re: [sqlite] schema design question

[Dennis Cote]

Jeff Gibson wrote:
> 
> One thing your earlier suggestion brought up.  The way I was hooking up 
> tables before was something along the lines of:
> 
> CREATE TABLE primary(id1 INTEGER PRIMARY KEY, );
> CREATE TABLE secondary(id2 INTEGER PRIMARY KEY, );
> CREATE TABLE link(id1 INTEGER, id2 INTEGER);
> 
> My understanding of your suggestion is:
> 
> CREATE TABLE primary(id1 INTEGER PRIMARY KEY, id2 INTEGER, 
> );
> CREATE TABLE secondary(id2 INTEGER PRIMARY KEY, );
> 
> with the understanding that id2 in primary will often be NULL.  Are 
> there any circumstances where the first alternative is more 
> appropriate?  I'm pretty new to databases, but I got my original idea 
> from a few schemas that I've seen.  I'm just trying to understand the 
> trade-offs.
> Thanks a lot for your help,
> Jeff
> 

These different forms of linking the records are used for different 
types of relations. The two tables can have records that are related in 
a various combinations of one or many to one or many.

 one to one
 many to one
 one to many
 many to many

Using a third table is required to implement a many to many relation. 
Each record in the third table stores one item of the relation (i.e 
which record in the first table is related to which record in the second 
table).

A one to many relation is created by assigning an id to the record in 
the one side of the relation and referencing that id in a column on the 
many side of the relation. A many to one relation is the same a one to 
many relation, with the order of the tables reversed. This is what you 
have shown as Richard's suggestion.

A one to one relation can be created by assigning an id to one record 
and using that same id as the primary key on the related record.

For your case, you need a one to one relation between the primary and 
secondary tables. This can be done by using the same id for the related 
record in the secondary table as was assigned to the record in the 
primary table.

CREATE TABLE primary(id INTEGER PRIMARY KEY, );
CREATE TABLE secondary(id INTEGER PRIMARY KEY, );

insert into primary values(null, );
insert into secondary values(last_insert_rowid(), );

When you want to retrieve the records you can use a join

select * from primary join secondary using(id);

or you can use a second select to retrieve the secondary fields using 
the id obtained from the primary field.

select * from primary;
if (has_secondary())
 select * from secondary where id = primary.id;

This does not waste any space storing unnecessary null fields. You 
should only resort the more complex foreign keys when you need to 
represent a more complex relation.

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

Re: [sqlite] schema design question

[Jay A. Kreibich]

On Thu, Apr 10, 2008 at 05:58:59PM -0700, Richard Klein scratched on the wall:

> My advice would be to try it and see.  If table creation takes too long,
> you can always remove the UNIQUE constraint, and then write a routine to
> check the table for uniqueness after it's created.

  That "routine" could simply be creating an explicit unique index on
  the column after all the data is loaded.  If the index can be
  created, you're good to go.

  This is still a slow process, but I know the creation of indexes on
  existing tables is one area the development team hopes to speed up.
  I'm not sure what the priority of that is, however.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema design question

[Richard Klein]

Jeff -

I'm pretty new to databases myself, but I have seen examples of
schemas that resemble your first alternative, i.e. that involve
the creation of a third table containing just the linkages
between the primary and secondary tables.

And in fact, I think that is the right solution for your appli-
cation.  Although the link table duplicates the id1 column, it
contains no wasted (NULL) entries.

The second alternative (my proposal) doesn't duplicate id1, but
90% of the id2's are NULL.  Since the primary table is very large,
this represents an excessive waste of space.

- Richard


Jeff Gibson wrote:
> Right.  Hence my hesitation.  :-)  I suppose it's possible to check 
> uniqueness once at the end in O(N), but it would also take O(N) extra 
> storage, and I doubt sqlite is doing that...
> 
> One thing your earlier suggestion brought up.  The way I was hooking up 
> tables before was something along the lines of:
> 
> CREATE TABLE primary(id1 INTEGER PRIMARY KEY, );
> CREATE TABLE secondary(id2 INTEGER PRIMARY KEY, );
> CREATE TABLE link(id1 INTEGER, id2 INTEGER);
> 
> My understanding of your suggestion is:
> 
> CREATE TABLE primary(id1 INTEGER PRIMARY KEY, id2 INTEGER, 
> );
> CREATE TABLE secondary(id2 INTEGER PRIMARY KEY, );
> 
> with the understanding that id2 in primary will often be NULL.  Are 
> there any circumstances where the first alternative is more 
> appropriate?  I'm pretty new to databases, but I got my original idea 
> from a few schemas that I've seen.  I'm just trying to understand the 
> trade-offs.
> Thanks a lot for your help,
> Jeff
> 
> Richard Klein wrote:
>> On second thought, checking the entire table for uniqueness would seem
>> to require O(N log N), regardless of whether it is done one INSERT at
>> a time, or all at once after the table is created!
>>
>> - Richard
>>
>> Richard Klein wrote:
>>   
>>> Quoting from the description of CREATE TABLE in the SQL Syntax section of
>>> the SQLite documentation:
>>>
>>> "The UNIQUE constraint causes an index to be created on the specified 
>>> columns.
>>> This index must contain unique keys."
>>>
>>> The creation of an index would seem to imply an O(log N) search on each
>>> insertion, so you should be okay.
>>>
>>> My advice would be to try it and see.  If table creation takes too long,
>>> you can always remove the UNIQUE constraint, and then write a routine to
>>> check the table for uniqueness after it's created.
>>>
>>> - Richard
>>>
>>>
>>> Jeff Gibson wrote:
>>> 
 I see.  Fortunately my application simplifies this since the database is 
 created once and read many times, but is never modified after creation 
 time.  Regarding constraints, I was thinking it might be helpful to add 
 a few where applicable (whether foreign key constraints or even simple 
 uniqueness constraints) basically as assertions, but I was worried about 
 the overhead it would take to enforce them when I'm creating the 
 database.  Do you know if a uniqueness constraint, for instance, does an 
 O(N) search on each insertion?  If so, it sounds prohibitive.
 Thanks,
 Jeff

 Richard Klein wrote:
   
> Jeff,
>
> I think that's the right way to go for your application.  There are a few
> things you should be aware of regarding this approach.
>
> A column in one table that references a column in another table is called
> a "foreign key" in database lingo.
>
> An issue with foreign keys is that it is important to keep the referencing
> table (big_table) in sync with the referenced table (secondary_table).
>
> For example, if you delete an entry from secondary_table, you want to 
> update
> the foreign key column in all entries in big_table that reference that 
> entry.
>
> What's the proper way to update the foreign key?  It depends on your 
> appli-
> cation.  You might want to set the foreign key in the referencing entries 
> to
> NULL, or you might want to delete the referencing entries, or you might 
> want
> to do something else.
>
> In standard, full-blown SQL, you can define the synchronization behavior 
> you
> want with a "foreign key constraint".  That is, you might create big_table
> as follows:
>
> CREATE TABLE big_table (
> idINTEGER PRIMARY KEY,
> col1  INTEGER,
> col2  REAL,
> col3  TEXT,
> col4  BLOB,
> col5  INTEGER,
> CONSTRAINT col5_fk FOREIGN KEY(col5)
>REFERENCES secondary_table(id) ON DELETE SET NULL,
> );
>
> This would define col5 as a foreign key referencing the id column of 
> secondary_
> table, and would specify that col5 should be set to NULL in all 
> referencing
> entries in big_table when an entry in secondary_table is deleted.
>
> Unfortunately, SQLite does not implement foreign key constraints.  More 
> precisely,
> they 

Re: [sqlite] schema design question

[Jeff Gibson]

Right.  Hence my hesitation.  :-)  I suppose it's possible to check 
uniqueness once at the end in O(N), but it would also take O(N) extra 
storage, and I doubt sqlite is doing that...

One thing your earlier suggestion brought up.  The way I was hooking up 
tables before was something along the lines of:

CREATE TABLE primary(id1 INTEGER PRIMARY KEY, );
CREATE TABLE secondary(id2 INTEGER PRIMARY KEY, );
CREATE TABLE link(id1 INTEGER, id2 INTEGER);

My understanding of your suggestion is:

CREATE TABLE primary(id1 INTEGER PRIMARY KEY, id2 INTEGER, 
);
CREATE TABLE secondary(id2 INTEGER PRIMARY KEY, );

with the understanding that id2 in primary will often be NULL.  Are 
there any circumstances where the first alternative is more 
appropriate?  I'm pretty new to databases, but I got my original idea 
from a few schemas that I've seen.  I'm just trying to understand the 
trade-offs.
Thanks a lot for your help,
Jeff

Richard Klein wrote:
> On second thought, checking the entire table for uniqueness would seem
> to require O(N log N), regardless of whether it is done one INSERT at
> a time, or all at once after the table is created!
>
> - Richard
>
> Richard Klein wrote:
>   
>> Quoting from the description of CREATE TABLE in the SQL Syntax section of
>> the SQLite documentation:
>>
>> "The UNIQUE constraint causes an index to be created on the specified 
>> columns.
>> This index must contain unique keys."
>>
>> The creation of an index would seem to imply an O(log N) search on each
>> insertion, so you should be okay.
>>
>> My advice would be to try it and see.  If table creation takes too long,
>> you can always remove the UNIQUE constraint, and then write a routine to
>> check the table for uniqueness after it's created.
>>
>> - Richard
>>
>>
>> Jeff Gibson wrote:
>> 
>>> I see.  Fortunately my application simplifies this since the database is 
>>> created once and read many times, but is never modified after creation 
>>> time.  Regarding constraints, I was thinking it might be helpful to add 
>>> a few where applicable (whether foreign key constraints or even simple 
>>> uniqueness constraints) basically as assertions, but I was worried about 
>>> the overhead it would take to enforce them when I'm creating the 
>>> database.  Do you know if a uniqueness constraint, for instance, does an 
>>> O(N) search on each insertion?  If so, it sounds prohibitive.
>>> Thanks,
>>> Jeff
>>>
>>> Richard Klein wrote:
>>>   
 Jeff,

 I think that's the right way to go for your application.  There are a few
 things you should be aware of regarding this approach.

 A column in one table that references a column in another table is called
 a "foreign key" in database lingo.

 An issue with foreign keys is that it is important to keep the referencing
 table (big_table) in sync with the referenced table (secondary_table).

 For example, if you delete an entry from secondary_table, you want to 
 update
 the foreign key column in all entries in big_table that reference that 
 entry.

 What's the proper way to update the foreign key?  It depends on your appli-
 cation.  You might want to set the foreign key in the referencing entries 
 to
 NULL, or you might want to delete the referencing entries, or you might 
 want
 to do something else.

 In standard, full-blown SQL, you can define the synchronization behavior 
 you
 want with a "foreign key constraint".  That is, you might create big_table
 as follows:

 CREATE TABLE big_table (
 idINTEGER PRIMARY KEY,
 col1  INTEGER,
 col2  REAL,
 col3  TEXT,
 col4  BLOB,
 col5  INTEGER,
 CONSTRAINT col5_fk FOREIGN KEY(col5)
REFERENCES secondary_table(id) ON DELETE SET NULL,
 );

 This would define col5 as a foreign key referencing the id column of 
 secondary_
 table, and would specify that col5 should be set to NULL in all referencing
 entries in big_table when an entry in secondary_table is deleted.

 Unfortunately, SQLite does not implement foreign key constraints.  More 
 precisely,
 they don't cause syntax errors, but they aren't enforced.  Therefore, you 
 will
 have to implement the desired synchronization behavior yourself.  
 Fortunately,
 this is easy to do with the use of TRIGGERs, which *are* implemented in 
 SQLite.

 Here are some links that might be useful:

 Foreign keys: http://en.wikipedia.org/wiki/Foreign_key
 SQLite triggers:  http://www.sqlite.org/lang_createtrigger.html

 Hope this helps,
 - Richard

   
 
> Thanks!  I'll give that a try.
>Jeff
>
> Richard Klein wrote:
> 
>   
>>> Whether or not the the secondary columns are needed is a function of 
>>> one 
>>> of the primary columns.  That 

Re: [sqlite] schema design question

[Richard Klein]

On second thought, checking the entire table for uniqueness would seem
to require O(N log N), regardless of whether it is done one INSERT at
a time, or all at once after the table is created!

- Richard

Richard Klein wrote:
> Quoting from the description of CREATE TABLE in the SQL Syntax section of
> the SQLite documentation:
> 
> "The UNIQUE constraint causes an index to be created on the specified columns.
> This index must contain unique keys."
> 
> The creation of an index would seem to imply an O(log N) search on each
> insertion, so you should be okay.
> 
> My advice would be to try it and see.  If table creation takes too long,
> you can always remove the UNIQUE constraint, and then write a routine to
> check the table for uniqueness after it's created.
> 
> - Richard
> 
> 
> Jeff Gibson wrote:
>> I see.  Fortunately my application simplifies this since the database is 
>> created once and read many times, but is never modified after creation 
>> time.  Regarding constraints, I was thinking it might be helpful to add 
>> a few where applicable (whether foreign key constraints or even simple 
>> uniqueness constraints) basically as assertions, but I was worried about 
>> the overhead it would take to enforce them when I'm creating the 
>> database.  Do you know if a uniqueness constraint, for instance, does an 
>> O(N) search on each insertion?  If so, it sounds prohibitive.
>> Thanks,
>> Jeff
>>
>> Richard Klein wrote:
>>> Jeff,
>>>
>>> I think that's the right way to go for your application.  There are a few
>>> things you should be aware of regarding this approach.
>>>
>>> A column in one table that references a column in another table is called
>>> a "foreign key" in database lingo.
>>>
>>> An issue with foreign keys is that it is important to keep the referencing
>>> table (big_table) in sync with the referenced table (secondary_table).
>>>
>>> For example, if you delete an entry from secondary_table, you want to update
>>> the foreign key column in all entries in big_table that reference that 
>>> entry.
>>>
>>> What's the proper way to update the foreign key?  It depends on your appli-
>>> cation.  You might want to set the foreign key in the referencing entries to
>>> NULL, or you might want to delete the referencing entries, or you might want
>>> to do something else.
>>>
>>> In standard, full-blown SQL, you can define the synchronization behavior you
>>> want with a "foreign key constraint".  That is, you might create big_table
>>> as follows:
>>>
>>> CREATE TABLE big_table (
>>> idINTEGER PRIMARY KEY,
>>> col1  INTEGER,
>>> col2  REAL,
>>> col3  TEXT,
>>> col4  BLOB,
>>> col5  INTEGER,
>>> CONSTRAINT col5_fk FOREIGN KEY(col5)
>>>REFERENCES secondary_table(id) ON DELETE SET NULL,
>>> );
>>>
>>> This would define col5 as a foreign key referencing the id column of 
>>> secondary_
>>> table, and would specify that col5 should be set to NULL in all referencing
>>> entries in big_table when an entry in secondary_table is deleted.
>>>
>>> Unfortunately, SQLite does not implement foreign key constraints.  More 
>>> precisely,
>>> they don't cause syntax errors, but they aren't enforced.  Therefore, you 
>>> will
>>> have to implement the desired synchronization behavior yourself.  
>>> Fortunately,
>>> this is easy to do with the use of TRIGGERs, which *are* implemented in 
>>> SQLite.
>>>
>>> Here are some links that might be useful:
>>>
>>> Foreign keys: http://en.wikipedia.org/wiki/Foreign_key
>>> SQLite triggers:  http://www.sqlite.org/lang_createtrigger.html
>>>
>>> Hope this helps,
>>> - Richard
>>>
>>>   
 Thanks!  I'll give that a try.
Jeff

 Richard Klein wrote:
 
>> Whether or not the the secondary columns are needed is a function of one 
>> of the primary columns.  That function involves values from another 
>> table, though, so the general case would require a join.  That other 
>> table is small, however, so I generally cache it outside the database.  
>> Some pseudocode for my expected use would be something like:
>>
>> prepare("SELECT primary_columns FROM big_table WHERE some_criterion")
>> while(step()) {
>>
>>if( F(primary_column_values) ) {
>>   Fetch secondary values
>>   }
>>
>> do something with primary and maybe secondary values;
>>
>> }
>>
>> Where F would be implemented outside the database.
>> Thanks,
>> Jeff
>> 
>> 
> I assume that the primary SELECT shown above can be made suitably fast
> by creating the appropriate indices on big_table.
>
> If the secondary columns are kept in a separate, secondary_table, and
> a fifth primary column is added that contains the ROWID of the approp-
> riate entry in the secondary_table (or NULL if the secondary_table is
> not needed), then the "Fetch secondary values" operation should be very
> fast as well.

Re: [sqlite] schema design question

[Richard Klein]

Quoting from the description of CREATE TABLE in the SQL Syntax section of
the SQLite documentation:

"The UNIQUE constraint causes an index to be created on the specified columns.
This index must contain unique keys."

The creation of an index would seem to imply an O(log N) search on each
insertion, so you should be okay.

My advice would be to try it and see.  If table creation takes too long,
you can always remove the UNIQUE constraint, and then write a routine to
check the table for uniqueness after it's created.

- Richard


Jeff Gibson wrote:
> I see.  Fortunately my application simplifies this since the database is 
> created once and read many times, but is never modified after creation 
> time.  Regarding constraints, I was thinking it might be helpful to add 
> a few where applicable (whether foreign key constraints or even simple 
> uniqueness constraints) basically as assertions, but I was worried about 
> the overhead it would take to enforce them when I'm creating the 
> database.  Do you know if a uniqueness constraint, for instance, does an 
> O(N) search on each insertion?  If so, it sounds prohibitive.
> Thanks,
> Jeff
> 
> Richard Klein wrote:
>> Jeff,
>>
>> I think that's the right way to go for your application.  There are a few
>> things you should be aware of regarding this approach.
>>
>> A column in one table that references a column in another table is called
>> a "foreign key" in database lingo.
>>
>> An issue with foreign keys is that it is important to keep the referencing
>> table (big_table) in sync with the referenced table (secondary_table).
>>
>> For example, if you delete an entry from secondary_table, you want to update
>> the foreign key column in all entries in big_table that reference that entry.
>>
>> What's the proper way to update the foreign key?  It depends on your appli-
>> cation.  You might want to set the foreign key in the referencing entries to
>> NULL, or you might want to delete the referencing entries, or you might want
>> to do something else.
>>
>> In standard, full-blown SQL, you can define the synchronization behavior you
>> want with a "foreign key constraint".  That is, you might create big_table
>> as follows:
>>
>> CREATE TABLE big_table (
>> idINTEGER PRIMARY KEY,
>> col1  INTEGER,
>> col2  REAL,
>> col3  TEXT,
>> col4  BLOB,
>> col5  INTEGER,
>> CONSTRAINT col5_fk FOREIGN KEY(col5)
>>REFERENCES secondary_table(id) ON DELETE SET NULL,
>> );
>>
>> This would define col5 as a foreign key referencing the id column of 
>> secondary_
>> table, and would specify that col5 should be set to NULL in all referencing
>> entries in big_table when an entry in secondary_table is deleted.
>>
>> Unfortunately, SQLite does not implement foreign key constraints.  More 
>> precisely,
>> they don't cause syntax errors, but they aren't enforced.  Therefore, you 
>> will
>> have to implement the desired synchronization behavior yourself.  
>> Fortunately,
>> this is easy to do with the use of TRIGGERs, which *are* implemented in 
>> SQLite.
>>
>> Here are some links that might be useful:
>>
>> Foreign keys: http://en.wikipedia.org/wiki/Foreign_key
>> SQLite triggers:  http://www.sqlite.org/lang_createtrigger.html
>>
>> Hope this helps,
>> - Richard
>>
>>   
>>> Thanks!  I'll give that a try.
>>>Jeff
>>>
>>> Richard Klein wrote:
>>> 
> Whether or not the the secondary columns are needed is a function of one 
> of the primary columns.  That function involves values from another 
> table, though, so the general case would require a join.  That other 
> table is small, however, so I generally cache it outside the database.  
> Some pseudocode for my expected use would be something like:
>
> prepare("SELECT primary_columns FROM big_table WHERE some_criterion")
> while(step()) {
>
>if( F(primary_column_values) ) {
>   Fetch secondary values
>   }
>
> do something with primary and maybe secondary values;
>
> }
>
> Where F would be implemented outside the database.
> Thanks,
> Jeff
> 
> 
 I assume that the primary SELECT shown above can be made suitably fast
 by creating the appropriate indices on big_table.

 If the secondary columns are kept in a separate, secondary_table, and
 a fifth primary column is added that contains the ROWID of the approp-
 riate entry in the secondary_table (or NULL if the secondary_table is
 not needed), then the "Fetch secondary values" operation should be very
 fast as well.

 It seems to me that this approach would be faster than a join, and
 would consume less space than an 8-column table containing mostly
 NULLs in the secondary columns.

 Of course, this approach would cost you some extra space, in the form
 of the 5th primary column containing the secondary ROWID.

 - Richard Klein

 

Re: [sqlite] schema design question

[Jeff Gibson]

I see.  Fortunately my application simplifies this since the database is 
created once and read many times, but is never modified after creation 
time.  Regarding constraints, I was thinking it might be helpful to add 
a few where applicable (whether foreign key constraints or even simple 
uniqueness constraints) basically as assertions, but I was worried about 
the overhead it would take to enforce them when I'm creating the 
database.  Do you know if a uniqueness constraint, for instance, does an 
O(N) search on each insertion?  If so, it sounds prohibitive.
Thanks,
Jeff

Richard Klein wrote:
> Jeff,
>
> I think that's the right way to go for your application.  There are a few
> things you should be aware of regarding this approach.
>
> A column in one table that references a column in another table is called
> a "foreign key" in database lingo.
>
> An issue with foreign keys is that it is important to keep the referencing
> table (big_table) in sync with the referenced table (secondary_table).
>
> For example, if you delete an entry from secondary_table, you want to update
> the foreign key column in all entries in big_table that reference that entry.
>
> What's the proper way to update the foreign key?  It depends on your appli-
> cation.  You might want to set the foreign key in the referencing entries to
> NULL, or you might want to delete the referencing entries, or you might want
> to do something else.
>
> In standard, full-blown SQL, you can define the synchronization behavior you
> want with a "foreign key constraint".  That is, you might create big_table
> as follows:
>
> CREATE TABLE big_table (
> idINTEGER PRIMARY KEY,
> col1  INTEGER,
> col2  REAL,
> col3  TEXT,
> col4  BLOB,
> col5  INTEGER,
> CONSTRAINT col5_fk FOREIGN KEY(col5)
>REFERENCES secondary_table(id) ON DELETE SET NULL,
> );
>
> This would define col5 as a foreign key referencing the id column of 
> secondary_
> table, and would specify that col5 should be set to NULL in all referencing
> entries in big_table when an entry in secondary_table is deleted.
>
> Unfortunately, SQLite does not implement foreign key constraints.  More 
> precisely,
> they don't cause syntax errors, but they aren't enforced.  Therefore, you will
> have to implement the desired synchronization behavior yourself.  Fortunately,
> this is easy to do with the use of TRIGGERs, which *are* implemented in 
> SQLite.
>
> Here are some links that might be useful:
>
> Foreign keys: http://en.wikipedia.org/wiki/Foreign_key
> SQLite triggers:  http://www.sqlite.org/lang_createtrigger.html
>
> Hope this helps,
> - Richard
>
>   
>> Thanks!  I'll give that a try.
>>Jeff
>>
>> Richard Klein wrote:
>> 
 Whether or not the the secondary columns are needed is a function of one 
 of the primary columns.  That function involves values from another 
 table, though, so the general case would require a join.  That other 
 table is small, however, so I generally cache it outside the database.  
 Some pseudocode for my expected use would be something like:

 prepare("SELECT primary_columns FROM big_table WHERE some_criterion")
 while(step()) {

if( F(primary_column_values) ) {
   Fetch secondary values
   }

 do something with primary and maybe secondary values;

 }

 Where F would be implemented outside the database.
 Thanks,
 Jeff
 
 
>>> I assume that the primary SELECT shown above can be made suitably fast
>>> by creating the appropriate indices on big_table.
>>>
>>> If the secondary columns are kept in a separate, secondary_table, and
>>> a fifth primary column is added that contains the ROWID of the approp-
>>> riate entry in the secondary_table (or NULL if the secondary_table is
>>> not needed), then the "Fetch secondary values" operation should be very
>>> fast as well.
>>>
>>> It seems to me that this approach would be faster than a join, and
>>> would consume less space than an 8-column table containing mostly
>>> NULLs in the secondary columns.
>>>
>>> Of course, this approach would cost you some extra space, in the form
>>> of the 5th primary column containing the secondary ROWID.
>>>
>>> - Richard Klein
>>>
>>> ___
>>> 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
>> 
> ___
> 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

Re: [sqlite] schema design question

[Richard Klein]

Jeff,

I think that's the right way to go for your application.  There are a few
things you should be aware of regarding this approach.

A column in one table that references a column in another table is called
a "foreign key" in database lingo.

An issue with foreign keys is that it is important to keep the referencing
table (big_table) in sync with the referenced table (secondary_table).

For example, if you delete an entry from secondary_table, you want to update
the foreign key column in all entries in big_table that reference that entry.

What's the proper way to update the foreign key?  It depends on your appli-
cation.  You might want to set the foreign key in the referencing entries to
NULL, or you might want to delete the referencing entries, or you might want
to do something else.

In standard, full-blown SQL, you can define the synchronization behavior you
want with a "foreign key constraint".  That is, you might create big_table
as follows:

CREATE TABLE big_table (
idINTEGER PRIMARY KEY,
col1  INTEGER,
col2  REAL,
col3  TEXT,
col4  BLOB,
col5  INTEGER,
CONSTRAINT col5_fk FOREIGN KEY(col5)
   REFERENCES secondary_table(id) ON DELETE SET NULL,
);

This would define col5 as a foreign key referencing the id column of secondary_
table, and would specify that col5 should be set to NULL in all referencing
entries in big_table when an entry in secondary_table is deleted.

Unfortunately, SQLite does not implement foreign key constraints.  More 
precisely,
they don't cause syntax errors, but they aren't enforced.  Therefore, you will
have to implement the desired synchronization behavior yourself.  Fortunately,
this is easy to do with the use of TRIGGERs, which *are* implemented in SQLite.

Here are some links that might be useful:

Foreign keys: http://en.wikipedia.org/wiki/Foreign_key
SQLite triggers:  http://www.sqlite.org/lang_createtrigger.html

Hope this helps,
- Richard

> Thanks!  I'll give that a try.
>Jeff
> 
> Richard Klein wrote:
>>> Whether or not the the secondary columns are needed is a function of one 
>>> of the primary columns.  That function involves values from another 
>>> table, though, so the general case would require a join.  That other 
>>> table is small, however, so I generally cache it outside the database.  
>>> Some pseudocode for my expected use would be something like:
>>>
>>> prepare("SELECT primary_columns FROM big_table WHERE some_criterion")
>>> while(step()) {
>>>
>>>if( F(primary_column_values) ) {
>>>   Fetch secondary values
>>>   }
>>>
>>> do something with primary and maybe secondary values;
>>>
>>> }
>>>
>>> Where F would be implemented outside the database.
>>> Thanks,
>>> Jeff
>>> 
>> I assume that the primary SELECT shown above can be made suitably fast
>> by creating the appropriate indices on big_table.
>>
>> If the secondary columns are kept in a separate, secondary_table, and
>> a fifth primary column is added that contains the ROWID of the approp-
>> riate entry in the secondary_table (or NULL if the secondary_table is
>> not needed), then the "Fetch secondary values" operation should be very
>> fast as well.
>>
>> It seems to me that this approach would be faster than a join, and
>> would consume less space than an 8-column table containing mostly
>> NULLs in the secondary columns.
>>
>> Of course, this approach would cost you some extra space, in the form
>> of the 5th primary column containing the secondary ROWID.
>>
>> - Richard Klein
>>
>> ___
>> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users