[sqlite] Slow query
I have the next table table1( id INTEGER, name VARCHAR, id2 INTEGER, name2 VARCHAR, year INTEGER ); I have created the next indexes : index1( name ), index2( id2 ), index3( name2 ); The database have about 200,000 records. The next query takes about 2 seconds and I think its too much. SELECT id, name, id2, name2 GROUP BY id ORDER BY name2, year DESC LIMIT 0, 15 If I remove the sorting condition the query is instantaneous. How can I make it faster? Thanks in advance J.Rios ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Frédéric BERTIN/Levallois/mediametrie est absent(e).
Je serai absent(e) à partir du 29/06/2010 de retour le 30/06/2010. Je répondrai à votre message dès mon retour. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Accessing an sqlite db from two different programs
absolutely no problem with sqlite. ensure you are doing smallest update as possible. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (python) how to define unchangeable global ID in a table?
On 28 Jun 2010, at 7:31pm, Pavel Ivanov wrote: >> Such freedom is not suitable for data interchange between two systems. Not >> that SQLite or any other database would change the PK during import-export, >> but they are free to do so as long as the *intramural* integrity is >> preserved. > > Can you point out some documentation supporting this claim? > I've no time now to search internet on this matter but I believe DBMS > changing data that *I stored* in it is fundamentally broken. I > wouldn't advise anybody to use it. DBMS is allowed to change only > internal details which do not appear in CREATE TABLE and INSERT > statements (like ROWID for example). This /is/ how SQLite behaves. If you allow it to do its own thing with _rowid_, and never refer to it, it feels free to change _rowid_ values any time it likes. If you define a column and it says "Hey, I can save space because I already have _rowid_ which fits those requirements." then since it knows you can access the values in that column it won't ever change them. Makes perfect sense to me. By the way, these cases where SQLite may change _rowid_ values are not mundane. I think the only place I've seen it documented is for VACUUM or for autovacuums. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Building OpenWrt-style ipkg package?
Hello Using the script below to build an ipkg package for OpenWrt, I need to add the command-line exe, ie. sqlite3.exe on Windows: = # cat sqlite3.mk # # sqlite3 for the Blackfin # David Rowe March 2008 # # usage: make -f sqlite3.mk sqlite3-package # # Thanks OpenWRT for build options include rules.mk SQLITE3_VERSION=3.5.6 SQLITE3_DIRNAME=sqlite-$(SQLITE3_VERSION) SQLITE3_DIR=$(BUILD_DIR)/$(SQLITE3_DIRNAME) SQLITE3_SITE=http://www.sqlite.org SQLITE3_SOURCE=sqlite-$(SQLITE3_VERSION).tar.gz SQLITE3_CONFIGURE_OPTS = --host=bfin-linux-uclibc \ --prefix=$(TARGET_DIR) \ --enable-shared \ --disable-static \ --disable-tcl \ --libdir=$(STAGING_LIB) TARGET_DIR=$(BUILD_DIR)/tmp/sqlite3/ipkg/sqlite3 PKG_NAME:=sqlite3 PKG_VERSION:=$(SQLITE3_VERSION) PKG_RELEASE:=1 PKG_BUILD_DIR:=$(BUILD_DIR)/tmp/sqlite3 STAGING_INC=$(STAGING_DIR)/usr/include STAGING_LIB=$(STAGING_DIR)/usr/lib export CFLAGS=-mfdpic -Os -I$(STAGING_INC) export LDFLAGS=-L$(STAGING_LIB) $(DL_DIR)/$(SQLITE3_SOURCE): mkdir -p dl wget -P $(DL_DIR) $(SQLITE3_SITE)/$(SQLITE3_SOURCE) $(SQLITE3_DIR)/.unpacked: $(DL_DIR)/$(SQLITE3_SOURCE) zcat $(DL_DIR)/$(SQLITE3_SOURCE) | tar -C $(BUILD_DIR) -xf - touch $(SQLITE3_DIR)/.unpacked $(SQLITE3_DIR)/.configured: $(SQLITE3_DIR)/.unpacked cd $(SQLITE3_DIR); ./configure $(SQLITE3_CONFIGURE_OPTS) touch $(SQLITE3_DIR)/.configured sqlite3: $(SQLITE3_DIR)/.configured cd $(SQLITE3_DIR); make install mkdir -p $(TARGET_DIR)/bin mkdir -p $(TARGET_DIR)/lib cp $(STAGING_LIB)/libsqlite3.so.0.8.6 $(TARGET_DIR)/lib ln -sf libsqlite3.so.0.8.6 $(TARGET_DIR)/lib/libsqlite3.so ln -sf libsqlite3.so.0.8.6 $(TARGET_DIR)/lib/libsqlite3.so.0 $(STRIP) $(TARGET_DIR)/lib/libsqlite3.so.0.8.6 # mv include files to staging cp $(TARGET_DIR)/include/* $(STAGING_INC) rm -Rf $(TARGET_DIR)/include # doc mkdir -p $(TARGET_DIR)/usr/doc cp doc/sqlite3.txt $(TARGET_DIR)/usr/doc touch $(PKG_BUILD_DIR)/.built all: sqlite3 dirclean: rm -rf $(SQLITE3_DIR) define Package/$(PKG_NAME) SECTION:=utils CATEGORY:=Utilities TITLE:=SQLite (v3.x) database engine DESCRIPTION:=\ SQLite is a small C library that implements a self-contained, \\\ embeddable, zero-configuration SQL database engine. URL:=http://www.sqlite.org/ endef # post installation define Package/$(PKG_NAME)/postinst endef # pre-remove define Package/$(PKG_NAME)/prerm endef $(eval $(call BuildPackage,$(PKG_NAME))) sqlite3-package: sqlite3 $(PACKAGE_DIR)/$(PKG_NAME)_$(VERSION)_$(PKGARCH).ipk = I have no experience with this sort of thing: If someone's already done this, would you tell me what to add to the above script? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (python) how to define unchangeable global ID in a table?
My remarks were made in the context of AUTOINCREMENTING primary keys. With
auto-incremented keys, the database is free to implement the incrementation
in the manner it sees fit. It may skip numbers. It may re-generate keys on
import/restore and cascade the changes out to child tables. Given these
possibilities, it is not best practice to exchange autoincremented keys
between systems but to use instead an alternate unique key that is
guaranteed to remain constant.
Regards
Tim Romano
Swarthmore PA
On Mon, Jun 28, 2010 at 2:31 PM, Pavel Ivanov wrote:
> > Such freedom is not suitable for data interchange between two systems.
> Not
> > that SQLite or any other database would change the PK during
> import-export,
> > but they are free to do so as long as the *intramural* integrity is
> > preserved.
>
> Can you point out some documentation supporting this claim?
> I've no time now to search internet on this matter but I believe DBMS
> changing data that *I stored* in it is fundamentally broken. I
> wouldn't advise anybody to use it. DBMS is allowed to change only
> internal details which do not appear in CREATE TABLE and INSERT
> statements (like ROWID for example). And AFAIK semantically primary
> key is no difference with unique constraint (except ability to be
> referenced by foreign key of course). And even more: I believe in a
> completely normalized database there couldn't be any unique
> constraints other than primary key. And to advise to developers either
> to not use primary key or to declare all columns referencing to it as
> foreign key is too much of a restrain.
>
>
> Pavel
>
> On Mon, Jun 28, 2010 at 2:15 PM, Tim Romano
> wrote:
> > Pavel,
> > Although you are right that SQLite persists the rowid for INTEGER PRIMARY
> > KEYS across VACUUMs and suchlike, I too am right.
> >
> > I was focusing on the OP's use of the words "guaranteed" and "globally"
> and
> > on this requirement:
> >
> > The OP wrote:
> > "BTW, in my story it is necessary to store the unique IDs as an integer
> > type not something like "uuid" or "hash" because the unique ID also
> > standard for a position in a string in exchanging protocol between 2
> > system."
> >
> > Since no SQL standard requires the primary key to do anything other than
> be
> > unique within the relation and with respect to its foreign references.
> As
> > long as the database maintains meets those requirements, it is free to
> > change the PK value as an "implementation detail" -- provided RI is not
> > broken in the process. The purist in me says the PK is for nothing but
> > uniqueness. It should have no other meaning whatsoever.
> >
> > Such freedom is not suitable for data interchange between two systems.
> Not
> > that SQLite or any other database would change the PK during
> import-export,
> > but they are free to do so as long as the *intramural* integrity is
> > preserved. Once you move into the extra-mural realm (data exchange, or
> > replication) I would advise against relying upon the PK value.
> >
> > The safest "guaranteed" way to achieve what the OP wants is to add
> another
> > column to the table and to make it an alternate unique key. This value
> > carries for him the specific meaning "position in a string in exchange
> > protocol between 2 systems".
> >
> > Regards
> > Tim Romano
> > Swarthmore PA
> >
> >
> > On Mon, Jun 28, 2010 at 10:23 AM, Pavel Ivanov
> wrote:
> >
> >> > the primary key column [id] is defined as INTEGER PRMARY KEY; so
> defined,
> >> > SQLite will treat this column as an alias for the ROWID. There is no
> >> > guarantee that ROWID will remain constant over time: its job is very
> >> simple:
> >> > to be unique. There is no "be constant" clause in its contract, so to
> >>
> >> Tim, you are not right here. You are right that as is ROWID is not
> >> guaranteed to be constant over time. But if you define some column as
> >> an alias to ROWID (i.e. if you have column INTEGER PRIMARY KEY) then
> >> SQLite guarantees that the value of this column will persist through
> >> any internal operations (like VACUUM or dumping and loading database
> >> again). Of course nobody will block UPDATEs on this column (as long as
> >> uniqueness remains valid), but that's a different story.
> >>
> >>
> >> Pavel
> >>
> >> On Mon, Jun 28, 2010 at 8:39 AM, Tim Romano
> >> wrote:
> >> > In this example:
> >> >
> >> > CREATE TABLE tableA {
> >> > id INTEGER PRIMARY KEY AUTOINCREMENT,
> >> > name TEXT NOT NULL UNIQUE,
> >> > myspecialvalue TEXT NOT NULL UNIQUE
> >> > }
> >> >
> >> >
> >> > the primary key column [id] is defined as INTEGER PRMARY KEY; so
> defined,
> >> > SQLite will treat this column as an alias for the ROWID. There is no
> >> > guarantee that ROWID will remain constant over time: its job is very
> >> simple:
> >> > to be unique. There is no "be constant" clause in its contract, so to
> >> > speak. Therefore, you should add another column [myspecialvalue] and
> make
> >> it
> >> > unique if you want to associ
Re: [sqlite] (python) how to define unchangeable global ID in a table?
On Mon, Jun 28, 2010 at 02:15:01PM -0400, Tim Romano scratched on the wall: > Since no SQL standard requires the primary key to do anything other than be > unique within the relation and with respect to its foreign references. As > long as the database maintains meets those requirements, it is free to > change the PK value as an "implementation detail" -- provided RI is not > broken in the process. The purist in me says the PK is for nothing but > uniqueness. It should have no other meaning whatsoever. That is definitely not true in the SQL world or the Relational Model. Not in the general case, anyways. One must assume that a PK (in SQL) or a candidate key (RM) contains real data that consist of meaningful values. For example, a store register database might use UPC for a PK value. The concept of a primary key (or more specifically, a candidate key) is an attribute that is applied to existing columns, not the other way around. This becomes even more apparent when you consider that the Relational Model does not have PKs, but only candidate keys-- and, unlike SQL PKs, a relation (table) is allowed to have more than one candidate key. They can't all be arbitrary. Now, it is true that many database designs choose to omit a meaningful PK and use a so-called "surrogate key." This is typically an arbitrary numeric ID field, but it could be anything. Surrogate keys are commonly used when there isn't a very strong natural PK (such as with people) or when the natural PK is clumsy or large (say, several long text columns). It is also true that this technique is so common and so popular that many databases (including SQLite) support some type of auto-assignment system (such as INTEGER PRIMARY KEY). But it is equally valid to define a text field or a manually assigned integer field as the PK of a table. Just because a PK can be arbitrary doesn't mean it must be. While you might argue that the database engine can do as it pleases with surrogate keys, that is most definitely not true of PKs in general, and I think there are strong arguments to say that it would be a Bad Idea to do it with surrogate keys. For one thing, you'd have to very strictly notify the database when it was dealing with a surrogate key. Simply having something be an INTEGER PRIMARY KEY would not be good enough, for example. I can still define a PK as such and then enter my own meaningful numbers. If you speak of internal cross references, such as ROWIDs, that's a whole different story. General ROWIDs, OIDs, or whatever the database wants to call them, are typically the domain of the database engine, and not to be used or exposed. They're still used as keys (for example, index record references) and I would quickly agree that they are open to changes and modifications whenever and however the database system wants. SQLite does this with a VACUUM, for example. ROWID values can change, but PK values cannot be changed or altered. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (python) how to define unchangeable global ID in a table?
> Such freedom is not suitable for data interchange between two systems. Not
> that SQLite or any other database would change the PK during import-export,
> but they are free to do so as long as the *intramural* integrity is
> preserved.
Can you point out some documentation supporting this claim?
I've no time now to search internet on this matter but I believe DBMS
changing data that *I stored* in it is fundamentally broken. I
wouldn't advise anybody to use it. DBMS is allowed to change only
internal details which do not appear in CREATE TABLE and INSERT
statements (like ROWID for example). And AFAIK semantically primary
key is no difference with unique constraint (except ability to be
referenced by foreign key of course). And even more: I believe in a
completely normalized database there couldn't be any unique
constraints other than primary key. And to advise to developers either
to not use primary key or to declare all columns referencing to it as
foreign key is too much of a restrain.
Pavel
On Mon, Jun 28, 2010 at 2:15 PM, Tim Romano wrote:
> Pavel,
> Although you are right that SQLite persists the rowid for INTEGER PRIMARY
> KEYS across VACUUMs and suchlike, I too am right.
>
> I was focusing on the OP's use of the words "guaranteed" and "globally" and
> on this requirement:
>
> The OP wrote:
> "BTW, in my story it is necessary to store the unique IDs as an integer
> type not something like "uuid" or "hash" because the unique ID also
> standard for a position in a string in exchanging protocol between 2
> system."
>
> Since no SQL standard requires the primary key to do anything other than be
> unique within the relation and with respect to its foreign references. As
> long as the database maintains meets those requirements, it is free to
> change the PK value as an "implementation detail" -- provided RI is not
> broken in the process. The purist in me says the PK is for nothing but
> uniqueness. It should have no other meaning whatsoever.
>
> Such freedom is not suitable for data interchange between two systems. Not
> that SQLite or any other database would change the PK during import-export,
> but they are free to do so as long as the *intramural* integrity is
> preserved. Once you move into the extra-mural realm (data exchange, or
> replication) I would advise against relying upon the PK value.
>
> The safest "guaranteed" way to achieve what the OP wants is to add another
> column to the table and to make it an alternate unique key. This value
> carries for him the specific meaning "position in a string in exchange
> protocol between 2 systems".
>
> Regards
> Tim Romano
> Swarthmore PA
>
>
> On Mon, Jun 28, 2010 at 10:23 AM, Pavel Ivanov wrote:
>
>> > the primary key column [id] is defined as INTEGER PRMARY KEY; so defined,
>> > SQLite will treat this column as an alias for the ROWID. There is no
>> > guarantee that ROWID will remain constant over time: its job is very
>> simple:
>> > to be unique. There is no "be constant" clause in its contract, so to
>>
>> Tim, you are not right here. You are right that as is ROWID is not
>> guaranteed to be constant over time. But if you define some column as
>> an alias to ROWID (i.e. if you have column INTEGER PRIMARY KEY) then
>> SQLite guarantees that the value of this column will persist through
>> any internal operations (like VACUUM or dumping and loading database
>> again). Of course nobody will block UPDATEs on this column (as long as
>> uniqueness remains valid), but that's a different story.
>>
>>
>> Pavel
>>
>> On Mon, Jun 28, 2010 at 8:39 AM, Tim Romano
>> wrote:
>> > In this example:
>> >
>> > CREATE TABLE tableA {
>> > id INTEGER PRIMARY KEY AUTOINCREMENT,
>> > name TEXT NOT NULL UNIQUE,
>> > myspecialvalue TEXT NOT NULL UNIQUE
>> > }
>> >
>> >
>> > the primary key column [id] is defined as INTEGER PRMARY KEY; so defined,
>> > SQLite will treat this column as an alias for the ROWID. There is no
>> > guarantee that ROWID will remain constant over time: its job is very
>> simple:
>> > to be unique. There is no "be constant" clause in its contract, so to
>> > speak. Therefore, you should add another column [myspecialvalue] and make
>> it
>> > unique if you want to associate a value with a tuple and also want to
>> > guarantee that the associated value is both unique and remains
>> unchanging.
>> > Of course you have to prevent edits to the associated value to enforce
>> its
>> > immutability.
>> >
>> > Regards
>> > Tim Romano
>> > Swarthmore PA
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > On Sat, Jun 26, 2010 at 11:34 AM, kee wrote:
>> >
>> >> Dear all
>> >>
>> >> I have 2 string lists, listA and listB as raw data which need to be
>> >> store in the SQLITE database, both of them may have duplicated records
>> >>
>> >> listA listB
>> >> ===
>> >> orange japan
>> >> pear china
>> >> orange
Re: [sqlite] alternative to UNIQUE CONSTRAINT
Oliver Peters wrote: > example_01: > -- > CREATE TABLE doesntwork( > idINTEGER PRIMARY KEY AUTOINCREMENT, > someint INTEGER, > sometext TEXT, > UNIQUE(someint) > ); > > INSERT INTO doesntwork(someint,sometext) VALUES(2,'Douglas Adams'); > > example_02: > -- > CREATE TABLE works( > idINTEGER PRIMARY KEY AUTOINCREMENT, > someint INTEGER, > sometext TEXT > ); > > INSERT INTO works(someint,sometext) VALUES(1,'Hitchhikers guide to galaxy'); > INSERT INTO works(someint,sometext) VALUES(2,'Douglas Adams'); > >> Are you having problems with all UNIQUE constraints or just some of them? >> Perhaps the difference is whether or not the column in question has nulls >> in it. > > there are no NULLS in my example and I don't believe in a frontend-problem (I > wouldn't interpret the SQL.LOG this way). You may not have inserted any NULLs but your table definition allows for the storage of nulls. Try making all of your column definitions NOT NULL and see if that makes any difference. That is, see if this works: CREATE TABLE wasdoesntwork( idINTEGER PRIMARY KEY AUTOINCREMENT, someint INTEGER NOT NULL, sometext TEXT NOT NULL, UNIQUE(someint) ); Also, can you simplify your examples further? If you take away the sometext columns from both examples, do you get the same failure or success? What if you take away the id column and only have the someint? (I don't recall if you said the UNIQUE only didn't work if the primary key was used.) Separately, as was reported in another reply, this issue is something you should report as a bug to the OpenOffice people, since I think you said an alternate connection method, MS Access worked fine? -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (python) how to define unchangeable global ID in a table?
Pavel,
Although you are right that SQLite persists the rowid for INTEGER PRIMARY
KEYS across VACUUMs and suchlike, I too am right.
I was focusing on the OP's use of the words "guaranteed" and "globally" and
on this requirement:
The OP wrote:
"BTW, in my story it is necessary to store the unique IDs as an integer
type not something like "uuid" or "hash" because the unique ID also
standard for a position in a string in exchanging protocol between 2
system."
Since no SQL standard requires the primary key to do anything other than be
unique within the relation and with respect to its foreign references. As
long as the database maintains meets those requirements, it is free to
change the PK value as an "implementation detail" -- provided RI is not
broken in the process. The purist in me says the PK is for nothing but
uniqueness. It should have no other meaning whatsoever.
Such freedom is not suitable for data interchange between two systems. Not
that SQLite or any other database would change the PK during import-export,
but they are free to do so as long as the *intramural* integrity is
preserved. Once you move into the extra-mural realm (data exchange, or
replication) I would advise against relying upon the PK value.
The safest "guaranteed" way to achieve what the OP wants is to add another
column to the table and to make it an alternate unique key. This value
carries for him the specific meaning "position in a string in exchange
protocol between 2 systems".
Regards
Tim Romano
Swarthmore PA
On Mon, Jun 28, 2010 at 10:23 AM, Pavel Ivanov wrote:
> > the primary key column [id] is defined as INTEGER PRMARY KEY; so defined,
> > SQLite will treat this column as an alias for the ROWID. There is no
> > guarantee that ROWID will remain constant over time: its job is very
> simple:
> > to be unique. There is no "be constant" clause in its contract, so to
>
> Tim, you are not right here. You are right that as is ROWID is not
> guaranteed to be constant over time. But if you define some column as
> an alias to ROWID (i.e. if you have column INTEGER PRIMARY KEY) then
> SQLite guarantees that the value of this column will persist through
> any internal operations (like VACUUM or dumping and loading database
> again). Of course nobody will block UPDATEs on this column (as long as
> uniqueness remains valid), but that's a different story.
>
>
> Pavel
>
> On Mon, Jun 28, 2010 at 8:39 AM, Tim Romano
> wrote:
> > In this example:
> >
> > CREATE TABLE tableA {
> > id INTEGER PRIMARY KEY AUTOINCREMENT,
> > name TEXT NOT NULL UNIQUE,
> > myspecialvalue TEXT NOT NULL UNIQUE
> > }
> >
> >
> > the primary key column [id] is defined as INTEGER PRMARY KEY; so defined,
> > SQLite will treat this column as an alias for the ROWID. There is no
> > guarantee that ROWID will remain constant over time: its job is very
> simple:
> > to be unique. There is no "be constant" clause in its contract, so to
> > speak. Therefore, you should add another column [myspecialvalue] and make
> it
> > unique if you want to associate a value with a tuple and also want to
> > guarantee that the associated value is both unique and remains
> unchanging.
> > Of course you have to prevent edits to the associated value to enforce
> its
> > immutability.
> >
> > Regards
> > Tim Romano
> > Swarthmore PA
> >
> >
> >
> >
> >
> >
> >
> >
> > On Sat, Jun 26, 2010 at 11:34 AM, kee wrote:
> >
> >> Dear all
> >>
> >> I have 2 string lists, listA and listB as raw data which need to be
> >> store in the SQLITE database, both of them may have duplicated records
> >>
> >> listA listB
> >> ===
> >> orangejapan
> >> pearchina
> >> orangechina
> >> apple american
> >> cherry india
> >> lemon china
> >> lemon japan
> >> strawberry korea
> >> banana thailand
> >> australia
> >>
> >> I want all items in listA and listB have a runtime defined global ID and
> >> fix it, which means no matter how the lists changed later after the
> >> first time running, all the item always have an unique int type ID bind
> >> with, looks like:
> >> A B
> >>
> >> 1orange 1 japan
> >> 2pear 2 china
> >> 3apple 3 american
> >> 4cherry 4 india
> >> 5lemon5 taiwan
> >> 6strawberry6 korea
> >> 7banana 7 thailand
> >>
Re: [sqlite] Accessing an sqlite db from two different programs
Ian, You might try Berkeley DB 11gR2 (read: Berkeley DB and SQLite combined) it allows you to run multi-process access to a SQLite database and scales really well. http://download.oracle.com/berkeley-db/db-5.0.21.tar.gz Give it a whirl and then let me know what you think. -greg > -Original Message- > From: Ian Hardingham [mailto:i...@mode7games.com] > Sent: Tuesday, June 22, 2010 12:44 PM > To: General Discussion of SQLite Database > Subject: [sqlite] Accessing an sqlite db from two different programs > > Hey guys, > > If I have program 1 and program 2 which both open the same db file, but > they never write to the same table (but might be reading one written by > another), do I need to do a lot of locking? I'm not worried about race > conditions. > > Thanks, > Ian > ___ > 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] Oracle joins the SQLite Consortium
Igor, Happy to help. :) First IANAL and what I'm about to say is a GROSS simplification of intricate intellectual property law so with that in mind. The Sleepycat License basically says, "anything that includes/uses/calls-into Sleepycat Licensed software (in this case Berkeley DB) and is redistributed to a 3rd party must itself be software made available under and open source license (or be in the public domain)." If you write a proprietary application and use the BDB libraries then before you redistribute it to a 3rd party you will need a license to do so. That's the dual license in a nutshell, that's how we make money on BDB products. Over time the minor differences identified by Roger Binns and others (Mike Owens for instance) will be smoothed out to be as close to SQLite-standard (bug-for-bug, feature-for-feature, behavior-for-behavior) identical except where we are trying to bring new value to SQLite. The goal is for a SQLite programmer or program to switch to BDB SQL and find instant benefit in certain use cases. This is like what happened to MySQL with its various storage engines. Most of the time you just swapped out one for the other and had different storage features available but sometimes there was a bit more leg work to be done. MySQL and SQLite serve different communities, we don't ever expect them to seriously overlap. There is a lot of new storage, data, and processing power in applications on devices that don't do well running a client/server database with a DBA. That's the market for BDB 11gR2 (read: BDB+SQLite). That's how we draw the line, I hope it makes sense to you. Oracle's mobile and embedded database of choice is Berkeley DB 11gR2 (read: BDB+SQLite). We believe that it has the right balance of features/functionality for those environments. The environments being a) in devices, b) in applications needing structured local storage, c) in other places where small+SQL is a win. In all those cases we hope to bring benefit by allowing synchronization of data from SQLite or BDB 11gR2 (read: BDB+SQLite) databases back to Oracle Database datacenters. SAP bought Sybase for their mobile platform which was a mistake when you consider that there is a better/equivalent/low-cost mobile database (which now has an enterprise app management and data sync server) out there that is far more popular and pervasive. This is not an embrace/extend play, we're joining into a successful ecosystem and working to improve it in ways that enterprise customers require (which isn't always the primary focus of SQLite development, and for good reason). We are a good friend and partner to open source project communities. This is a strong and sensible mobile database strategy. If you're not on the steamroller, you're part of the asphalt - right? -greg > -Original Message- > From: Igor Sereda [mailto:ser...@gmail.com] > Sent: Wednesday, June 23, 2010 8:44 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Oracle joins the SQLite Consortium > > > Greg, > > Thanks for the explanation. So it's Sleepycat license, ok, but we still > can't use it in an application with proprietary code, right? > > It would be interesting to track the progress of SQLite/BDB. Roger > Binns has > noted some important issues, but granted those are solved, would you > say > SQLite/BDB is going to be an acceptable solution as an in-process > server-side database? > > Is there a vision how BDB, now with SQLite, fits into the family of > Oracle's > products? Clearly it's far from Oracle Database, but do you foresee > competition with MySQL team? Just asking :) > > Igor > > > > Gregory Burd-2 wrote: > > > > Hello, > > > > My name is Greg, I'm one of the product managers within Oracle > working on > > the Berkeley DB products. I joined Oracle when Sleepycat was > acquired but > > I've been working on BDB for nearly nine years now. I was the one > who > > pushed hard to integrate SQLite and BDB, I think the two products go > well > > together without damaging either one. I am also the guy responsible > for > > most of the messaging on the Oracle.com website (with a lot of > editing > > oversight and marketing input), so if you want to question something > there > > please just email me. > > > > We here in the Berkeley DB team within Oracle's Embedded Database > group > > are thrilled to have Oracle join the SQLite Consortium. Today and in > the > > past our goal with open source collaborations has been to work > closely > > together, help each other out, keep things informal-yet-formal, and > give > > credit where credit is due. The SQLite product is excellent, we > don't > > want or need to fork it. The SQLite3 ANSI C API is like the BDB ANSI > C > > key/value API, de-facto standards in their respective spaces. From > our > > view this combination is like chocolate and peanut butter, two great > > products that go well together. Some will like this combo and find
Re: [sqlite] alternative to UNIQUE CONSTRAINT
> step 3: try to delete/update records from the table "doesntwork" from within > the > frontend (OpenOffice 3.2.1) > > The result from within Base is as the tablename indicates (doesn't work). And > from that point I don't know what to do besides saying: "that has to be an > error > in the ODBC-driver". Nope. :) You should say at this point "That has to be an error in OpenOffice", go find some OpenOffice bug tracker or OpenOffice-related discussion list and bring this issue to their attention. You can also hope that somebody from their team is reading this and will answer your concerns or somebody using the same frontend is reading this and will direct you where you should file a bug... Pavel On Mon, Jun 28, 2010 at 12:23 PM, Oliver Peters wrote: > Pavel Ivanov writes: > > [...] > >> And as no one experienced problems like yours before then I guess we >> can switch contexts and now "frontend" will mean the app that uses >> this ODBC driver (probably you use it through some wrapper or >> something else is standing in the way). > > o.k. - good hint: I tried the same with MS ACCESS and had no problem; so the > problem is an ODBC-app-problem (like you already wrote) > But I can't change my frontend because I have to do it with OpenOffice Base > (and > I have at least my dissatisfying trigger solution) > > >> I think you should reduce your >> case to some few calls to ODBC and post it here if it still doesn't >> work. > > I don't know what exactly you mean but I reduced the problem by dropping much > SQL-Code and leaving the necessary rest: > > > step 1: produce the tables > > > CREATE TABLE doesntwork( > id INTEGER PRIMARY KEY AUTOINCREMENT, > someint INTEGER, > sometext TEXT, > UNIQUE(someint) > ); > > INSERT INTO doesntwork(someint,sometext) VALUES(2,'Douglas Adams'); > > CREATE TABLE works( > id INTEGER PRIMARY KEY AUTOINCREMENT, > someint INTEGER, > sometext TEXT > ); > > INSERT INTO works(someint,sometext) VALUES(1,'Hitchhikers guide to galaxy'); > INSERT INTO works(someint,sometext) VALUES(2,'Douglas Adams'); > > > step2: create the DSN with the ODBC-driver from > http://www.ch-werner.de/sqliteodbc/ > > > step 3: try to delete/update records from the table "doesntwork" from within > the > frontend (OpenOffice 3.2.1) > > > The result from within Base is as the tablename indicates (doesn't work). And > from that point I don't know what to do besides saying: "that has to be an > error > in the ODBC-driver". > > Oliver > > ___ > 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] alternative to UNIQUE CONSTRAINT
Pavel Ivanov writes: [...] > And as no one experienced problems like yours before then I guess we > can switch contexts and now "frontend" will mean the app that uses > this ODBC driver (probably you use it through some wrapper or > something else is standing in the way). o.k. - good hint: I tried the same with MS ACCESS and had no problem; so the problem is an ODBC-app-problem (like you already wrote) But I can't change my frontend because I have to do it with OpenOffice Base (and I have at least my dissatisfying trigger solution) > I think you should reduce your > case to some few calls to ODBC and post it here if it still doesn't > work. I don't know what exactly you mean but I reduced the problem by dropping much SQL-Code and leaving the necessary rest: step 1: produce the tables CREATE TABLE doesntwork( idINTEGER PRIMARY KEY AUTOINCREMENT, someint INTEGER, sometext TEXT, UNIQUE(someint) ); INSERT INTO doesntwork(someint,sometext) VALUES(2,'Douglas Adams'); CREATE TABLE works( idINTEGER PRIMARY KEY AUTOINCREMENT, someint INTEGER, sometext TEXT ); INSERT INTO works(someint,sometext) VALUES(1,'Hitchhikers guide to galaxy'); INSERT INTO works(someint,sometext) VALUES(2,'Douglas Adams'); step2: create the DSN with the ODBC-driver from http://www.ch-werner.de/sqliteodbc/ step 3: try to delete/update records from the table "doesntwork" from within the frontend (OpenOffice 3.2.1) The result from within Base is as the tablename indicates (doesn't work). And from that point I don't know what to do besides saying: "that has to be an error in the ODBC-driver". Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] open db cx to fork(2)d children
On Mon, Jun 28, 2010 at 11:30:49AM -0400, Eric Smith wrote: > From the docs: > > > Under Unix, you should not carry an open SQLite database across a > > fork() system call into the child process. Problems will result if you > > do. > > What if I fork a process that promises not to use the handle, and > furthermore the child process certainly dies before the parent does? Libraries could well (and many do) make use of pthread_atfork(3C) to render "handles" unusable in a child process. If an API is described as caller "should not" or "must not" follow fork(2) then you need to be prepared for it to use pthread_atfork(3C) as described, either now or in the future. > Will this still cause problems? > > What if I add an additional assumption that the parent process is the > only process that ever accesses the database? I strongly recommend that you always make the child side of fork(2) either exit(2) or exec(2) immediately. Self re-exec()ing is fine, if you can pass in whatever state you need preserved on the command-line and/or pipe and/or temp file. If you're going to fork(2) and neither exit nor exec then you should be careful to do whatever cleanup the various libraries demand, and stick to whatever fork-safety protocol they insist on, if any. Not all fork-unsafe libraries will give you a way to re-initialize them on the child-side of fork(2); the only fool-proof way forward is to exec(2) in the child of fork(2). With respect to SQLite3, there are two sets of fork-safety issues: file descriptor offsets (use USE_PREAD to avoid this issue), and POSIX file byte range locks. There may be other fork-safety issues too that don't leap to my mind at this time. Basically, fork(2) must be handled with extreme care. The easiest thing for library developers to do is to declare their libraries to be fork-unsafe and make the matter a problem for their callers, which in a sufficiently layered system means many libraries become inherently fork-unsafe, which leads one to conclude even more strongly that one must always exec(2) or exit(2) the child of fork(2). What you really need from libraries is a way to recover whatever state you'll want to re-establish after exec()ing in the child. SQLite3 has no such state (what state it has the caller already knows how to re-create). (Think of crypto APIs, where the state of a cipher might not be feasible to reproduce with ease; there you need the library to give you a way to export the cipher's state.) Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] AUTO: Bret Patterson/Austin/IBM is out of the office (returning 07/01/2010)
I am out of the office until 07/01/2010. I'm out of the office but checking email once or twice a day and will respond to any high importance issues as quickly as possible. Note: This is an automated response to your message "sqlite-users Digest, Vol 30, Issue 27" sent on 6/28/10 6:00:02. This is the only notification you will receive while this person is away. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] alternative to UNIQUE CONSTRAINT
Israel Lins Albuquerque writes: > > > maybe this works for you! > > http://www.patthoyts.tk/sqlite3odbc.html in the first sentence he writes: go to http://www.ch-werner.de/sqliteodbc/ -> outdated > http://wiki.services.openoffice.or/wiki/SummerOfCode2006#Native_SQLite_driver -> very outdated Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] alternative to UNIQUE CONSTRAINT
There are some here: http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers (I've found this page through google ;-) ). But AFAIR yours one is the most popular. And as no one experienced problems like yours before then I guess we can switch contexts and now "frontend" will mean the app that uses this ODBC driver (probably you use it through some wrapper or something else is standing in the way). I think you should reduce your case to some few calls to ODBC and post it here if it still doesn't work. Pavel On Mon, Jun 28, 2010 at 11:24 AM, Oliver Peters wrote: > Pavel Ivanov writes: > > [...] >> >> If your ODBC driver doesn't allow you to have any UNIQUE constraint >> then, as Darren said, you better consider using some other driver, not >> a workaround for this one. I believe there are several ODBC drivers >> for SQLite out there. >> > > I strongly believe in human potential for doing good deeds but sometimes I > need > a proof ;-) - so where are the other ODBC drivers you are writing about (I use > http://www.ch-werner.de/sqliteodbc/) ? I wasn't able to find an alternative - > and yes I know how to google ;-) > > [...] > > Oliver > > > ___ > 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] alternative to UNIQUE CONSTRAINT
maybe this works for you! http://www.patthoyts.tk/sqlite3odbc.html http://wiki.services.openoffice.org/wiki/SummerOfCode2006#Native_SQLite_driver - "Oliver Peters" escreveu: > Pavel Ivanov writes: > > [...] > > > > If your ODBC driver doesn't allow you to have any UNIQUE constraint > > then, as Darren said, you better consider using some other driver, not > > a workaround for this one. I believe there are several ODBC drivers > > for SQLite out there. > > > > I strongly believe in human potential for doing good deeds but sometimes I > need > a proof ;-) - so where are the other ODBC drivers you are writing about (I > use > http://www.ch-werner.de/sqliteodbc/) ? I wasn't able to find an alternative - > and yes I know how to google ;-) > > [...] > > Oliver > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Atenciosamente/Regards, Israel Lins Albuquerque Desenvolvimento/Development Polibrás Brasil Software Ltda. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] open db cx to fork(2)d children
>From the docs: > Under Unix, you should not carry an open SQLite database across a > fork() system call into the child process. Problems will result if you > do. What if I fork a process that promises not to use the handle, and furthermore the child process certainly dies before the parent does? Will this still cause problems? What if I add an additional assumption that the parent process is the only process that ever accesses the database? -- Eric A. Smith Slurm, n.: The slime that accumulates on the underside of a soap bar when it sits in the dish too long. -- Rich Hall, "Sniglets" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] alternative to UNIQUE CONSTRAINT
Pavel Ivanov writes: [...] > > If your ODBC driver doesn't allow you to have any UNIQUE constraint > then, as Darren said, you better consider using some other driver, not > a workaround for this one. I believe there are several ODBC drivers > for SQLite out there. > I strongly believe in human potential for doing good deeds but sometimes I need a proof ;-) - so where are the other ODBC drivers you are writing about (I use http://www.ch-werner.de/sqliteodbc/) ? I wasn't able to find an alternative - and yes I know how to google ;-) [...] Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Column names in SQL
On Mon, Jun 28, 2010 at 10:07 AM, Serdar Genc wrote:
> I have already tried it but not working.. :(
>
Works for me.
punk...@lucknow ~$sqlite3
-- Loading resources from /Users/punkish/.sqliterc
SQLite version 3.6.23
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t ("a[b]");
sqlite> INSERT INTO t VALUES ('foo');
sqlite> INSERT INTO t VALUES (3);
sqlite> SELECT * FROM t;
a[b]
--
foo
3
sqlite> SELECT "a[b]" FROM t WHERE "a[b]" = 3;
a[b]
--
3
sqlite>
You are doing something else. You are not describing the entire
problem. How are you accessing your database?
> On Mon, Jun 28, 2010 at 6:01 PM, P Kishor wrote:
>
>> On Mon, Jun 28, 2010 at 9:58 AM, Serdar Genc
>> wrote:
>> > Hi everyone,
>> >
>> > I have a problem related to column names . I have a column name as a[b]
>> in
>> > my table but
>> > this creates a problem when using SELECT statement as
>> > SELECT a[b] from Table. I know [] is a special character but How would I
>> > tell SQlite that field
>> > name is a[b] and I am not using [] with a special purpose..
>>
>> Try SELECT "a[b]"
>>
>>
>> >
>> > Thanks in advance,
>> > Serdar
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>>
>>
>>
>> --
>> Puneet Kishor http://www.punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> ---
>> Assertions are politics; backing up assertions with evidence is science
>> ===
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
>
> Serdar Genç
> web: http://www.iptakip.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Column names in SQL
I have already tried it but not working.. :( On Mon, Jun 28, 2010 at 6:01 PM, P Kishor wrote: > On Mon, Jun 28, 2010 at 9:58 AM, Serdar Genc > wrote: > > Hi everyone, > > > > I have a problem related to column names . I have a column name as a[b] > in > > my table but > > this creates a problem when using SELECT statement as > > SELECT a[b] from Table. I know [] is a special character but How would I > > tell SQlite that field > > name is a[b] and I am not using [] with a special purpose.. > > Try SELECT "a[b]" > > > > > > Thanks in advance, > > Serdar > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > Puneet Kishor http://www.punkish.org > Carbon Model http://carbonmodel.org > Charter Member, Open Source Geospatial Foundation http://www.osgeo.org > Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor > Nelson Institute, UW-Madison http://www.nelson.wisc.edu > --- > Assertions are politics; backing up assertions with evidence is science > === > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Serdar Genç web: http://www.iptakip.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] alternative to UNIQUE CONSTRAINT
> ODBC-Driver == frontend ??? In this context - probably, yes. > I'am pretty sure that it's an odbc-driver problem (so nothing to worry in this > group?) and I posted her for a workaround as short and elegant like the UNIQUE > constraint I am not allowed to use. If your ODBC driver doesn't allow you to have any UNIQUE constraint then, as Darren said, you better consider using some other driver, not a workaround for this one. I believe there are several ODBC drivers for SQLite out there. Pavel On Mon, Jun 28, 2010 at 11:00 AM, Oliver Peters wrote: > Pavel Ivanov writes: > >> >> > there are no NULLS in my example and I don't believe in a frontend-problem >> > (I >> > wouldn't interpret the SQL.LOG this way). >> >> If you don't believe that it's your frontend problem then go ahead and >> reproduce it in sqlite3 command line utility. If you were able to >> reproduce it there then it would be indeed an SQLite library problem. >> Otherwise it's frontend problem believe it or not. > > ODBC-Driver == frontend ??? > > I'am pretty sure that it's an odbc-driver problem (so nothing to worry in this > group?) and I posted her for a workaround as short and elegant like the UNIQUE > constraint I am not allowed to use. > > [...] > > Oliver > > > ___ > 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] Column names in SQL
On Mon, Jun 28, 2010 at 9:58 AM, Serdar Genc wrote: > Hi everyone, > > I have a problem related to column names . I have a column name as a[b] in > my table but > this creates a problem when using SELECT statement as > SELECT a[b] from Table. I know [] is a special character but How would I > tell SQlite that field > name is a[b] and I am not using [] with a special purpose.. Try SELECT "a[b]" > > Thanks in advance, > Serdar > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] alternative to UNIQUE CONSTRAINT
Pavel Ivanov writes: > > > there are no NULLS in my example and I don't believe in a frontend-problem > > (I > > wouldn't interpret the SQL.LOG this way). > > If you don't believe that it's your frontend problem then go ahead and > reproduce it in sqlite3 command line utility. If you were able to > reproduce it there then it would be indeed an SQLite library problem. > Otherwise it's frontend problem believe it or not. ODBC-Driver == frontend ??? I'am pretty sure that it's an odbc-driver problem (so nothing to worry in this group?) and I posted her for a workaround as short and elegant like the UNIQUE constraint I am not allowed to use. [...] Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Column names in SQL
Hi everyone, I have a problem related to column names . I have a column name as a[b] in my table but this creates a problem when using SELECT statement as SELECT a[b] from Table. I know [] is a special character but How would I tell SQlite that field name is a[b] and I am not using [] with a special purpose.. Thanks in advance, Serdar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create virtual table if not exists table_id???
Thanks a lot for this link! 2010/5/12 Roger Binns > > > http://www.sqlite.org/cvstrac/tktview?tn=2604 > > To fix it requires code changes to SQLite and the SQLite team haven't > deemed > this necessary (yet). > > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] alternative to UNIQUE CONSTRAINT
> there are no NULLS in my example and I don't believe in a frontend-problem (I > wouldn't interpret the SQL.LOG this way). If you don't believe that it's your frontend problem then go ahead and reproduce it in sqlite3 command line utility. If you were able to reproduce it there then it would be indeed an SQLite library problem. Otherwise it's frontend problem believe it or not. Pavel On Mon, Jun 28, 2010 at 4:29 AM, Oliver Peters wrote: > Darren Duncan writes: > > [...] > >> What efforts have you made in trying to fix the front-end instead? >> > > > Nothing because I'm not a programmer but I reduced complexity: > > > example_01: > -- > CREATE TABLE doesntwork( > id INTEGER PRIMARY KEY AUTOINCREMENT, > someint INTEGER, > sometext TEXT, > UNIQUE(someint) > ); > > > INSERT INTO doesntwork(someint,sometext) VALUES(2,'Douglas Adams'); > > > example_02: > -- > CREATE TABLE works( > id INTEGER PRIMARY KEY AUTOINCREMENT, > someint INTEGER, > sometext TEXT > ); > > > INSERT INTO works(someint,sometext) VALUES(1,'Hitchhikers guide to galaxy'); > INSERT INTO works(someint,sometext) VALUES(2,'Douglas Adams'); > > > I use OpenOffice 3.2.1 in connection with http://www.ch-werner.de/sqliteodbc/. > If I try to delete or change records in the example_01 this doesn't work. I > just use tables not even a form. > > >> If the front-end complains about doing something as common and proper as >> using >> UNIQUE constraints (which are the best solution for the job), then I >> wouldn't be >> surprised if its also going to give you trouble with other reasonable things >> you'd want to do. >> >> Are you having problems with all UNIQUE constraints or just some of them? >> Perhaps the difference is whether or not the column in question has nulls >> in it. > > there are no NULLS in my example and I don't believe in a frontend-problem (I > wouldn't interpret the SQL.LOG this way). > > [...] > >> >> An alternate thing you could do is split your tables so each column you want >> unique is in its own table and then you can make that a primary key. >> Not that I >> actually advise this since then you're just gaining a new problem or two in >> place of the one you lost, such as ensuring there's not more than one row >> in the >> other table per row in the parent. >> > > I use Triggers now but that is just a workaround - as you write: UNIQUE > constraints are something very common in DBMs and should work > > thx for your thoughts > Oliver > > ___ > 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] (python) how to define unchangeable global ID in a table?
> the primary key column [id] is defined as INTEGER PRMARY KEY; so defined,
> SQLite will treat this column as an alias for the ROWID. There is no
> guarantee that ROWID will remain constant over time: its job is very simple:
> to be unique. There is no "be constant" clause in its contract, so to
Tim, you are not right here. You are right that as is ROWID is not
guaranteed to be constant over time. But if you define some column as
an alias to ROWID (i.e. if you have column INTEGER PRIMARY KEY) then
SQLite guarantees that the value of this column will persist through
any internal operations (like VACUUM or dumping and loading database
again). Of course nobody will block UPDATEs on this column (as long as
uniqueness remains valid), but that's a different story.
Pavel
On Mon, Jun 28, 2010 at 8:39 AM, Tim Romano wrote:
> In this example:
>
> CREATE TABLE tableA {
> id INTEGER PRIMARY KEY AUTOINCREMENT,
> name TEXT NOT NULL UNIQUE,
> myspecialvalue TEXT NOT NULL UNIQUE
> }
>
>
> the primary key column [id] is defined as INTEGER PRMARY KEY; so defined,
> SQLite will treat this column as an alias for the ROWID. There is no
> guarantee that ROWID will remain constant over time: its job is very simple:
> to be unique. There is no "be constant" clause in its contract, so to
> speak. Therefore, you should add another column [myspecialvalue] and make it
> unique if you want to associate a value with a tuple and also want to
> guarantee that the associated value is both unique and remains unchanging.
> Of course you have to prevent edits to the associated value to enforce its
> immutability.
>
> Regards
> Tim Romano
> Swarthmore PA
>
>
>
>
>
>
>
>
> On Sat, Jun 26, 2010 at 11:34 AM, kee wrote:
>
>> Dear all
>>
>> I have 2 string lists, listA and listB as raw data which need to be
>> store in the SQLITE database, both of them may have duplicated records
>>
>> listA listB
>> ===
>> orange japan
>> pear china
>> orange china
>> apple american
>> cherry india
>> lemon china
>> lemon japan
>> strawberry korea
>> banana thailand
>> australia
>>
>> I want all items in listA and listB have a runtime defined global ID and
>> fix it, which means no matter how the lists changed later after the
>> first time running, all the item always have an unique int type ID bind
>> with, looks like:
>> A B
>>
>> 1 orange 1 japan
>> 2 pear 2 china
>> 3 apple 3 american
>> 4 cherry 4 india
>> 5 lemon 5 taiwan
>> 6 strawberry 6 korea
>> 7 banana 7 thailand
>> 8 australia
>>
>>
>> So I defined table with such structure:
>> CREATE TABLE tableA {
>> uinque_id INTEGER PRIMARY KEY AUTOINCREMENT,
>> name TEXT NOT NULL UNIQUE,
>> }
>> CREATE TABLE tableB {
>> uinque_id INTEGER PRIMARY KEY AUTOINCREMENT,
>> name TEXT NOT NULL UNIQUE,
>> }
>>
>> and my plan is to use "INSERT OR FAIL" to insert data into those tables.
>>
>> Here comes my QUESTION 1, is it possible no matter what the list
>> changed, all items always get an unique ID, should any other limitation
>> should be added into the defination, and if I use "CREATE TABLE
>> table_dst AS SELECT * FROM table_src" to duplicate tables later, can
>> those definition be copied either?
>>
>>
>> Then, it may need to make a matrix for 2 tables: I want list all
>> possible combination of 2 lists, for example:
>>
>> listC = listA * listB
>>
>> japan orange
>> china orange
>> american orange
>> india orange
>> ...
>> thailand banana
>> australia banana
>>
>> I also want to use same table structure to store the combination result
>> and assigned unique ID for those combined items same as before:
>> CREATE TABLE tableC {
>> uinque_id INTEGER PRIMARY KEY AUTOINCREMENT,
>> name_combination TEXT NOT NULL UNIQUE,
>> }
>> Here comes my QUESTION 2, is it also reasonable using such a structure
>> store the combination or should there be a better way to do it?
>> I means will such a structure be a problem if the listA and listB be
>> changed, should I store uniqueIDA and uniqueIDB replace the
>> name_combination field will be a better solution?
>>
>> BTW, I using the python as the interface insert the lists into those
>> tables, also the uinque_id in database
Re: [sqlite] (python) how to define unchangeable global ID in a table?
You could also define your primary key as INT PRIMARY KEY (rather than
INTEGER PRIMARY KEY) and in that case SQLite will treat it as a normal
column and it will remain immutable over time (unless you change it).
However, I would advise against using INT PRIMARY KEY inasmuch as this
subtle (yet documented) difference between INT and INTEGER in primary keys
in SQLite has already been a cause of significant confusion and is an
"accident waiting to happen" IMO since not all implementations of SQLite may
be hep to the nuance. What you want is a simple ersatz value for the entity
(i.e. an "alternate unique key"); a second column which *explicitly* acts
in this manner will be clearer.
Regards
Tim Romano
Swarthmore PA
On Mon, Jun 28, 2010 at 8:43 AM, Tim Romano wrote:
> And myspecialvalue can be INTEGER|TEXT.
>
>
> On Mon, Jun 28, 2010 at 8:39 AM, Tim Romano wrote:
>
>> In this example:
>>
>> CREATE TABLE tableA {
>>
>> id INTEGER PRIMARY KEY AUTOINCREMENT,
>> name TEXT NOT NULL UNIQUE,
>> myspecialvalue TEXT NOT NULL UNIQUE
>> }
>>
>>
>>
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (python) how to define unchangeable global ID in a table?
And myspecialvalue can be INTEGER|TEXT.
On Mon, Jun 28, 2010 at 8:39 AM, Tim Romano wrote:
> In this example:
>
> CREATE TABLE tableA {
>
> id INTEGER PRIMARY KEY AUTOINCREMENT,
> name TEXT NOT NULL UNIQUE,
> myspecialvalue TEXT NOT NULL UNIQUE
> }
>
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (python) how to define unchangeable global ID in a table?
In this example:
CREATE TABLE tableA {
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
myspecialvalue TEXT NOT NULL UNIQUE
}
the primary key column [id] is defined as INTEGER PRMARY KEY; so defined,
SQLite will treat this column as an alias for the ROWID. There is no
guarantee that ROWID will remain constant over time: its job is very simple:
to be unique. There is no "be constant" clause in its contract, so to
speak. Therefore, you should add another column [myspecialvalue] and make it
unique if you want to associate a value with a tuple and also want to
guarantee that the associated value is both unique and remains unchanging.
Of course you have to prevent edits to the associated value to enforce its
immutability.
Regards
Tim Romano
Swarthmore PA
On Sat, Jun 26, 2010 at 11:34 AM, kee wrote:
> Dear all
>
> I have 2 string lists, listA and listB as raw data which need to be
> store in the SQLITE database, both of them may have duplicated records
>
> listA listB
> ===
> orangejapan
> pearchina
> orangechina
> apple american
> cherry india
> lemon china
> lemon japan
> strawberry korea
> banana thailand
> australia
>
> I want all items in listA and listB have a runtime defined global ID and
> fix it, which means no matter how the lists changed later after the
> first time running, all the item always have an unique int type ID bind
> with, looks like:
> A B
>
> 1orange 1 japan
> 2pear 2 china
> 3apple 3 american
> 4cherry 4 india
> 5lemon5 taiwan
> 6strawberry6 korea
> 7banana 7 thailand
>8 australia
>
>
> So I defined table with such structure:
> CREATE TABLE tableA {
>uinque_id INTEGER PRIMARY KEY AUTOINCREMENT,
>name TEXT NOT NULL UNIQUE,
> }
> CREATE TABLE tableB {
>uinque_id INTEGER PRIMARY KEY AUTOINCREMENT,
>name TEXT NOT NULL UNIQUE,
> }
>
> and my plan is to use "INSERT OR FAIL" to insert data into those tables.
>
> Here comes my QUESTION 1, is it possible no matter what the list
> changed, all items always get an unique ID, should any other limitation
> should be added into the defination, and if I use "CREATE TABLE
> table_dst AS SELECT * FROM table_src" to duplicate tables later, can
> those definition be copied either?
>
>
> Then, it may need to make a matrix for 2 tables: I want list all
> possible combination of 2 lists, for example:
>
> listC = listA * listB
>
> japan orange
> china orange
> american orange
> india orange
> ...
> thailand banana
> australiabanana
>
> I also want to use same table structure to store the combination result
> and assigned unique ID for those combined items same as before:
> CREATE TABLE tableC {
>uinque_id INTEGER PRIMARY KEY AUTOINCREMENT,
>name_combination TEXT NOT NULL UNIQUE,
> }
> Here comes my QUESTION 2, is it also reasonable using such a structure
> store the combination or should there be a better way to do it?
> I means will such a structure be a problem if the listA and listB be
> changed, should I store uniqueIDA and uniqueIDB replace the
> name_combination field will be a better solution?
>
> BTW, I using the python as the interface insert the lists into those
> tables, also the uinque_id in database is not need to be reused if some
> items in listA and listB been deleted, just remain as is because it will
> never get to sqlite limitation.
>
> BTW, in my story it is necessary to store the unique IDs as an integer
> type not something like "uuid" or "hash" because the unique ID also
> standard for a position in a string in exchanging protocol between 2
> system.
>
>
> And : a more general question:
> Anyone has better solution to solve my problem in sqlite - the items in
> a list need to be bind with an unchangeable integer type unique ID no
> matter what the list will be modified?
>
> Any comments and suggestions will be highly appreciated!
>
> Thanks!
>
> Rgs,
>
> KC
>
>
>
>
>
> ___
> 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:
Re: [sqlite] alternative to UNIQUE CONSTRAINT
Tim Romano writes: > > Could there be an issue with the character-encoding of the text column? > Regards > Tim Romano > Swarthmore PA > [...] I don't see this as a possibility because: 1. my encoding is utf-8 2. the simple example http://article.gmane.org/gmane.comp.db.sqlite.general/57581 has no special characters (only ascii) 3. I don't use COLLATIONs or PRAGMA ENCODING greetings Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] alternative to UNIQUE CONSTRAINT
Could there be an issue with the character-encoding of the text column? Regards Tim Romano Swarthmore PA On Fri, Jun 25, 2010 at 12:35 PM, Oliver Peters wrote: > Igor Tandetnik writes: > > [...] > > > Isn't that exactly what you were asking for - a different syntax to > achieve > the same end result? > > Not really because the assumed ODBC-Driver problem has nothing to do with > the > syntax but the underlying mechanism (sounds logically for me ;-) ) because > I get > an error about "invalid descriptor index" and a few errors in the > SQL.LOG-file I > produced under Win XP (http://paste.ubuntuusers.de/398565/). > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] alternative to UNIQUE CONSTRAINT
Darren Duncan writes: [...] > What efforts have you made in trying to fix the front-end instead? > Nothing because I'm not a programmer but I reduced complexity: example_01: -- CREATE TABLE doesntwork( idINTEGER PRIMARY KEY AUTOINCREMENT, someint INTEGER, sometext TEXT, UNIQUE(someint) ); INSERT INTO doesntwork(someint,sometext) VALUES(2,'Douglas Adams'); example_02: -- CREATE TABLE works( idINTEGER PRIMARY KEY AUTOINCREMENT, someint INTEGER, sometext TEXT ); INSERT INTO works(someint,sometext) VALUES(1,'Hitchhikers guide to galaxy'); INSERT INTO works(someint,sometext) VALUES(2,'Douglas Adams'); I use OpenOffice 3.2.1 in connection with http://www.ch-werner.de/sqliteodbc/. If I try to delete or change records in the example_01 this doesn't work. I just use tables not even a form. > If the front-end complains about doing something as common and proper as > using > UNIQUE constraints (which are the best solution for the job), then I > wouldn't be > surprised if its also going to give you trouble with other reasonable things > you'd want to do. > > Are you having problems with all UNIQUE constraints or just some of them? > Perhaps the difference is whether or not the column in question has nulls > in it. there are no NULLS in my example and I don't believe in a frontend-problem (I wouldn't interpret the SQL.LOG this way). [...] > > An alternate thing you could do is split your tables so each column you want > unique is in its own table and then you can make that a primary key. > Not that I > actually advise this since then you're just gaining a new problem or two in > place of the one you lost, such as ensuring there's not more than one row > in the > other table per row in the parent. > I use Triggers now but that is just a workaround - as you write: UNIQUE constraints are something very common in DBMs and should work thx for your thoughts Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
