Re: [sqlite] Insert a structure
>From my experience I would recommend you to convert your structure into some >kind text format. JSON, with the excellent and well-performing yajl library, >is usually my favorite choice here. /eno On 16.05.2011, at 11:39, StyveA wrote: > > > > Enrico Thierbach-2 wrote: >> >> >> On 16.05.2011, at 11:08, Christoph P.U. Kukulies wrote: >> >>> Am 16.05.2011 10:51, schrieb StyveA: >>>> Hi all, >>>> >>>> I'm working on a code in C, and I would like to insert a structure into >>>> a >>>> table as BLOB type. >>>> >>>> Is-it possible to pass it entirely in one time? Or should I insert each >>>> parameters of my structure independently? >>> >>> A structure in C has a size and a storage address. So technically I see >>> no reason, why you can't do that. Just copy >>> the BLOB like you do a memcpy(). >>> But as soon as you cross architectures (big-endian, little-endian) or >>> you have structure padding >>> between different compilers etc., I would say you get into trouble. >>> >>> -- >>> Christoph >>> >> >> You should have no pointers in it also. And you should not reference it or >> its members via pointers and expect it to be at the same memory location >> ever again. >> >> /eno >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > Hum.. this is more restrictive than what I thought.. > Thanks for the tips, it helps me. > > Styve > > -- > View this message in context: > http://old.nabble.com/Insert-a-structure-tp31627295p31627590.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] Insert a structure
On 16.05.2011, at 11:08, Christoph P.U. Kukulies wrote: > Am 16.05.2011 10:51, schrieb StyveA: >> Hi all, >> >> I'm working on a code in C, and I would like to insert a structure into a >> table as BLOB type. >> >> Is-it possible to pass it entirely in one time? Or should I insert each >> parameters of my structure independently? > > A structure in C has a size and a storage address. So technically I see > no reason, why you can't do that. Just copy > the BLOB like you do a memcpy(). > But as soon as you cross architectures (big-endian, little-endian) or > you have structure padding > between different compilers etc., I would say you get into trouble. > > -- > Christoph > You should have no pointers in it also. And you should not reference it or its members via pointers and expect it to be at the same memory location ever again. /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?
> > A round robin queue is fine. Every so often, to kill off old records do > > SELECT max(rowid) FROM myTable > > then in your code subtract from it however many rows you want to keep, then do > > DELETE FROM myTable WHERE rowid < firstToRetain > > It won't work perfectly but it's simple and fast. > You could even do something like that in an ON INSERT trigger. And with an AUTOINCREMENT primary key (see http://www.sqlite.org/autoinc.html) you would not even have to SELECT max(rowid), as the max rowid is then the rowid of the new record. As a side note: even when run from a separate thread, inserting the log entries and deleting old entries would lock the database, thus affecting any main thread. You would probably want a separate database for that. /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?
On 10.05.2011, at 12:06, Stephan Beal wrote: > On Tue, May 10, 2011 at 11:52 AM, Enrico Thierbach wrote: > >> I don't think sqlite (or any SQL database, for that matter) is a perfect >> fit for a logger, because there is a certain amount of write overhead. >> Why do you think you would want to do this? >> > > ALL db insertions in a db are, in effect, some form of logging. In embedded > apps with no stdout/stderr (e.g. WinCE) using sqlite as a logging > destination can be quite useful (and easy to set up). Yes and no: logging is an (append-only) write to an already opened file or network socket, and no indexes need to be updated. While inserting a document into a database needs to fiddle with internal database structures, which is less performant than just writing a few bytes to an already handle. Of course, constraints on an embedded device are different than, say, on a Unix server, and logging to a database is easy to set up, especially if the database is already there :). In other scenarios file system logging generally wins, and not only performance wise, but because there are plenty of tools to work with those; unless, of course, there is a specific need to use a database. /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?
Hi Lynton, I don't think sqlite (or any SQL database, for that matter) is a perfect fit for a logger, because there is a certain amount of write overhead. Why do you think you would want to do this? /eno On 10.05.2011, at 10:09, Lynton Grice wrote: > Hi there, > > SQLite is a perfect fit for a logger, the only question I have is once > it is in production my database will grow rapidly, how can I implement / > mimic a type of "rotating log"? > > So in my mind I am thinking that perhaps I can LIMIT the size of the > SQLIte DB to say 5 MB? And once the DB reaches that size it starts > INSERTING new logs over the earliest records in the database? > > Is this possible? > > Thanks for the help > > Lynton > ___ > 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] Advice to choose an index for quad tree?
Hi, I think an R Tree is what you are after. http://www.sqlite.org/rtree.html /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User function calculates for every row before applying any where condition
On 11.04.2011, at 10:18, Maxim V. Shiyanovsky wrote: > Does SQlite (3.6.23.1 to be precise) perform any optimization when user > function appears in the statement? > > I defined custom function MY_FUNC(a TEXT, b TEXT) and bound it with > sqlite3_create_function. > > Suppose, we have 2 tables: > Tbl1(id1 INTEGER, id2 INTEGER, str TEXT) with 2 records > Tbl2(id INTEGER, Tbl1_id INTEGER, str TEXT) with 5000 records > > Using query: > SELECT Tbl2.id, MY_FUNC(Tbl1.str, Tbl2.str) FROM Tbl2 JOIN Tbl1 ON > Tbl1.id1=Tbl2.Tbl1_id WHERE Tbl2.id>1000 AND Tbl1.id2=1 LIMIT 50 > I hope MY_FUNC would be evaluated 50 times at most, but it appeared 1. > > Using subquery does not do anything with the problem. SELECT sq.id, MY_FUNC(sq.str1, sq.str2) FROM (SELECT Tbl2.id, Tbl1.str AS str1, Tbl2.str AS str2 FROM Tbl2 JOIN Tbl1 ON Tbl1.id1=Tbl2.Tbl1_id WHERE Tbl2.id>1000 AND Tbl1.id2=1 LIMIT 50) sq really doesn't help? /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Increment a row value...
On 06.04.2011, at 17:13, John D. Marinuzzi wrote: > Hello, > > > > Perhaps this is more of a SQL question, but I am curious if SQLite has some > kind of implementation for incrementing an integer within a row without > actually reading the value and then updating the record. Is that possible? > > UPDATE table SET number=number+1 WHERE id=4711 /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 bug in last_insert_rowid()
On 04.04.2011, at 15:59, Simon Slavin wrote:
>
> On 4 Apr 2011, at 2:48pm, Enrico Thierbach wrote:
>
>> isn't last_insert_rowid defined as the ID of the last row inserted
>
> Yes it is. But the FTS system does what it does by maintaining extra tables,
> and doing extra operations to them besides the ones that the programmer has
> asked for. Naturally this means that several things get done after each
> INSERT operation the programmer asked for, so last_insert_rowid() may as well
> be a random number.
>
I might have an exceptionally dumb day, but this sequence (from this post
http://www.mail-archive.com/sqlite-users@sqlite.org/msg34082.html ) looks
totally fine:
> insert into one (value) values ("hello1");
> select last_insert_rowid(); -- returns 1
> insert into one (value) values ("hello2");
> select last_insert_rowid(); -- returns 2
> update one set value="hello3" where id=1;
> select last_insert_rowid(); -- returns 3, but should return 2
until the update (assuming that the IDs reported are right, of course). The
last one would not be a problem in my understanding, as last_insert_rowid()
gets undefined by the UPDATE anyways. Or do I miss a point?
On the other hand, http://www.sqlite.org/c3ref/last_insert_rowid.html defines
the last insert rowid as
"This routine returns the rowid of the most recent successful INSERT into the
database from the database connection in the first argument. If no successful
INSERTs have ever occurred on that database connection, zero is returned."
As this doesn't mention non-INSERT changes I tend to see my understanding as
wrong :)
/eno
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 bug in last_insert_rowid()
On 04.04.2011, at 15:59, Simon Slavin wrote:
>
> On 4 Apr 2011, at 2:48pm, Enrico Thierbach wrote:
>
>> isn't last_insert_rowid defined as the ID of the last row inserted
>
> Yes it is. But the FTS system does what it does by maintaining extra tables,
> and doing extra operations to them besides the ones that the programmer has
> asked for. Naturally this means that several things get done after each
> INSERT operation the programmer asked for, so last_insert_rowid() may as well
> be a random number.
>
I might have an exceptionally dumb day, but this sequence (from this post
http://www.mail-archive.com/sqlite-users@sqlite.org/msg34082.html ) looks
totally fine:
> insert into one (value) values ("hello1");
> select last_insert_rowid(); -- returns 1
> insert into one (value) values ("hello2");
> select last_insert_rowid(); -- returns 2
> update one set value="hello3" where id=1;
> select last_insert_rowid(); -- returns 3, but should return 2
until the update (assuming that the IDs reported are right, of course). The
last one would be a problem, as last_insert_rowid() gets undefined in my
understanding by the UPDATE anyways. Or do I miss a point?
/eno
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 bug in last_insert_rowid()
Hi Nico,
this
>> Is this really a bug? I at least wouldn't expect last_insert_rowid to be
>> constant if the database gets modified.
>
was more a question for sake of my understanding. Both in the post Simon
referred to
> insert into one (value) values ("hello1");
> select last_insert_rowid(); -- returns 1
> insert into one (value) values ("hello2");
> select last_insert_rowid(); -- returns 2
> update one set value="hello3" where id=1;
> select last_insert_rowid(); -- returns 3, but should return 2
and in the OP's post
>
> sqlite> CREATE VIRTUAL TABLE fts USING fts4(a,TOKENIZE icu russian);
> sqlite> select last_insert_rowid();
> 0
> sqlite> insert into fts(a) values ('test');
> sqlite> insert into fts(a) values ('test');
> sqlite> select last_insert_rowid();
> 2
> sqlite> delete from fts where rowid=2;
> sqlite> select last_insert_rowid();
> 3
there is some some change to the database after the last insert, and it is this
change that apparently changes the last_insert_rowid. And as I said: neither
would I expect it to change nor would I expect it not to change; I just
wouldn't expect anything here. But there might be something in the SQL specs or
somewhere else that states otherwise.
>
> INSTEAD OF triggers that don't actually insert anything? Then there's
> the re-entrance issue we have in this case. I tend to thing that
> last_insert_rowid() is best avoided because it's an optimization (no
> need to run a query to find what your last statement did) that is not
> needed if you manage your primary keys directly (i.e., don't rely on
> the RDBMS to do autoincrement or any other form of primary key
> allocation).
I don't see the re-entrance issue: isn't last_insert_rowid defined as the ID of
the last row inserted *within the current connection*? (And, as my
understanding, only
valid directly after the INSERT). For your avoidance of last_insert_rowid at
all: do you always generate the primary key client-side? How do you guarantee
uniqueness then?
BTW: last_insert_rowid is not necessarily a query, as it is already reported to
the client by the C-interface.
/eno
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 bug in last_insert_rowid()
Is this really a bug? I at least wouldn't expect last_insert_rowid to be
constant if the database gets modified.
/eno
On 04.04.2011, at 13:28, Alexey Pechnikov wrote:
> $ sqlite3
> SQLite version 3.7.6
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE VIRTUAL TABLE fts USING fts4(a,TOKENIZE icu russian);
> sqlite> select last_insert_rowid();
> 0
> sqlite> insert into fts(a) values ('test');
> sqlite> insert into fts(a) values ('test');
> sqlite> select last_insert_rowid();
> 2
> sqlite> delete from fts where rowid=2;
> sqlite> select last_insert_rowid();
> 3
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help me understand the sqlite query optimizer
Hi Simon, I just stumbled about this post. > On 21.03.2011, at 06:34, Simon Slavin wrote: > > Suppose you have a TABLE employees with ten thousand rows and no indexes, and > you execute > > SELECT id,firstname,surname FROM employees WHERE firstname='Guilherme' AND > age=46 > > The query optimizer has a choice. It can first pick out all the Guilhermes, > and then find those who are the right age, or it can first pick out all the > 46 year olds, then find any with the right firstname. It has to do two > operations, and it must do the first one on the entire list of members. The > only way to make things faster is if the second operation needs to worry > about the smallest number of rows possible. There are only two options: name > first then age, or age first then name. > Is it really that way? I somehow assumed in that non-index case there is just a single table scan involved? Fetching each record, calculating firstname='Guilherme' AND age=46 for each, and selecting the matching records? confused, /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 39, Issue 20
Hi Udon, to come to an possibly productive end here: if you think an amd64 compilation would be useful to you (I do doubt that though :(, drop me a note and I will fire up a compiler run on one of my servers, and tar the results. /eno > > @eno > I'm sure RPMs are really cool. But previously I just included all the dynamic > libraries in a directory so LabVIEW knew where to find them and everything > worked fine. The end user just unzipped the API to a directory and started > developing. No "download this, then do that" or "oh and by the way you need > this > and also that is a dependency". Simple for the end-user and for me (I just > need > to zip a directory). From what people tell me, unlike the MAC, SQlite isn't > part > of any default install in linux and they would have to do as you suggest and > download something (and I would have to support that installation too-which I > cannot). Additionally, thAny solution that involves linux is really off the > menu. It needs to be a cross-compile or I just drop linux support. But at > least > I can then say I have made an effort for linux users - even though I'm not > one > myself. > > The platform is whatever the SQLite from sqlite.org was compiled for > (preferably > with the aforementioned switches, but if that's too hard; then with default). > The windows and VX works libraries have those defines, but if it's not > possible, > then linux users won't get full text search and they'll have to use low level > functions (rather than the easy API) for foreign keys. They'll just have to > live > with it as a limitation in linux as MAC users do. > > Regards > Udon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite.so dynamic library-linux
Hi Udon, > > But to answer your question. more fully.LabVIEW is cross platform (MAC, > Win,VXWorks & Linux). Therefore the API I provide can (and does) work under > all > those systems (and ,many users have been using it in those OSs for some time) > - > you just need the SQLite library compiled for the particular OS. MAC comes > with > it already. I can already compile for Windows and VXWorks, so that just > leaves > Linux (which I used to download from the SQLite site). > Well, Linuxes do have sqlite bundled, too. One would only have to install it using whatever package manager (apt, rpm ...) is provided on the specific linux distro. Tell your users they would have to do just this, and everything should be good. As for compiling: not only is installing Linux in a virtual machine and compiling there basically a no-brainer; there are a few issues with distributing compiled libraries outside the package management, and you having not specified the Linux platform (i386, amd64, ...) wouldn't help either. /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining deepest descendents of parent records from one table
I think you should limit yourself to one table, at least for the purpose of parent<->child relationships. I am working on a similar problem right now. (I started with sqlite, but moved to mysql for reasons outside of this problem scope.) My solution, which performs fine, is as follows: I put all objects into a single table. The parent_id attribute is used to associate the parent of an entry. Now a query like that SELECT a.id AS content_id, a.parent_id AS ancestor_id FROM contents a WHERE a.parent_id IS NOT NULL UNION SELECT a.id AS content_id, b.parent_id AS ancestor_id FROM contents a INNER JOIN contents b ON a.parent_id=b.id WHERE b.parent_id IS NOT NULL UNION SELECT a.id AS content_id, c.parent_id AS ancestor_id FROM contents a INNER JOIN contents b ON a.parent_id=b.id INNER JOIN contents c ON b.parent_id=c.id WHERE c.parent_id IS NOT NULL selects content_is, ancestor_id pairs up to 4 levels deep. This performs fine with a dataset of 5 elements, and an average depth of 3..4 I tried this once with 5 levels and it still felt ok. Even faster than that was a query like this SELECT a.id AS aid, a.parent_id AS bid, b.parent_id AS cid, d.parent_id AS did FROM contents a LEFT JOIN contents b ON a.parent_id=b.id LEFT JOIN contents c ON b.parent_id=c.id which obviously might contain NULL entries (e.g. 1, 2, NULL, NULL) in case there are no 4 levels. Another approach would be to maintain a relation table holding all those entries which is updated using triggers. May be this is a direction you could go, too? /eno > I have 5 tables that store records for 5 different types of documents. > Each type of document can be a descendent of a document in one of the > other tables. > > For example, let's call the tables A, B, C, D and E. > > If you create a record in A, and then create a record in B, you can > associate the document in B with the record in A. You could then do > the same with a record in C, D, and E. > > One wrinkle is that you can skip documents, so it's not always a > direct chain. For example, you could have A > B > E. > > Another wrinkle is that you don't have to start at A, so you could > have B > C > D > E. You can actually start at any point in the cycle. > > Another wrinkle is that you can loop back around to other tables > again, so something like A > B > E > A (not the same record as the > first A) > C > E is possible. Of course, you can't link back to a > record that is already in the chain though, so as to avoid and > infinite loop. > > A final wrinkle is that each parent record can have more than one > linked child in other tables. > > Each table has 2 columns: ParentTable and ParentRecordID that hold the > associated record from the immediate parent table for each child > record (or null for records that are at the top of a chain, or > completely unchained at this point). > > Now Table A is kind of special in that I want to be able to figure out > the deepest descendents of all of the records in Table A in any other > tables (if any) and display data from the deepest linked records > (using group_concat on a fields in those linked records). > > So, for example, if we had record chains such as: > D1 < B1 < A1 > D2 < C1 < A3 > E1 < B3 < A1 > E2 < C2 < A1 > C3 < B2 > > I want to be able to show a cell for each record in Table A like: > > TABLE A > A.ID A.TitleDeepestDocuments > -- > 1 Doc1 E.Doc1, E.Doc2 > 2 Doc2 Null > 3 Doc3 D.Doc2 > > You'll notice that even though A1 has descendents in tables B, C and > D, only descendents in Table E are listed in the DeepestDocuments cell > because that is the deepest table with descendents. > > I've got what I need to work by manually looping through records and > running up the chain to see when I get a null parent record to > determine the top level reference, and then concatenating all of the > deepest records fields as required, but it is quite slow. On 100 rows > it can take 3-6 seconds on a reasonably fast computer, but table A > could conceivably contain a few thousand records, which would make it > far too slow for my needs. > > I'm wondering if anyone has any ideas on how I might accomplish what I > need using SQLite supported SQL. I suspect I need to do something with > UNION statements, but my experience with them is non-existent, and > nothing I've tried yet has worked. > > Thanks in advance for any help in this matter. > Jason > ___ > 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] memory usage after VACUUM
On 09.03.2011, at 16:23, Nick Hodapp wrote: > I'm using sqlite in an iOS app, via the popular FMDB wrapper. > > My profiling tool is showing me that the app is using 2.5 MB of memory > before a VACUUM, and nearly 6MB after. The tool shows that the extra memory > was allocated by sqlite3MemMalloc(). If I close and re-open the database > then the extra memory usage goes away, returning to 2.5 MB. > > Is there any sqlite function I can call, or some other technique, to reduce > the memory allocated and hung-onto by sqlite, particularly during a VACUUM? > > It's possible but unlikely that the FMDB wrapper is affecting things. I > haven't removed it from the equation to test, however. > > Nick Hodap Hi nick, just a shot in the dark: is there any chance that probably FMDB releases the memory properly, but the corresponding auto release pool is not yet released? In that case the memory would still be held there. For anyone not into iOS development: that is Cocoa's memory management solution. /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Timezone/DST question
Hi Igor,
Dealing with this time zone business always makes me feel dizzy :) Your
suggestion works pretty fine, in fact, see:
> 1.8.7 ~ > sqlite3
> sqlite> select datetime('2011-03-08 14:00', 'utc');
> 2011-03-08 13:00:00
> sqlite> select datetime('2011-04-08 14:00', 'utc');
> 2011-04-08 12:00:00
The latter case is in CEST, and sqlite seems to know this. And again with the
timezone pinned to UTC:
> 1.8.7 ~ > TZ=UTC sqlite3
> sqlite> select datetime('2011-03-08 14:00', 'utc');
> 2011-03-08 14:00:00
> sqlite> select datetime('2011-04-08 14:00', 'utc');
> 2011-04-08 14:00:00
I would say: just perfect!
/eno
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Timezone/DST question
On 08.03.2011, at 14:29, Igor Tandetnik wrote:
> Enrico Thierbach wrote:
>> I have to add a number of times into the database. They come from "real
>> live" sources, that, for example, say: "Sunday, March
>> 13th, 2pm", or "Sunday, April 3rd, 2pm".
>>
>> Here in Central Europe we switch to DST on the last weekend of March,
>> therefore the first time is "2011-03-13 14:00 UTC+1" (or
>> "2011-03-13 14:00 CET"), while the latter is "2011-04-03 14:00 UTC+2" (or
>> "2011-04-03 14:00 CEST").
>>
>> Now sqlite already knows about DST. Does anyone see a way to use that
>> already? I would hate to reinvent the wheel and to code
>> this myself :)
>
> select datetime('2011-03-12 14:00', 'utc'), datetime('2011-03-13 14:00',
> 'utc');
> 2011-03-12 19:00:00 | 2011-03-13 18:00:00
>
> That's for EST (New York time), which switches to DST on 3/13 at 2 am.
> --
> Igor Tandetnik
>
Thanks, Igor.
Your example, however, is just the other way around :) I want to enter a time
without explicitely stating a timezone, e.g. "2011-03-13 19:00:00",
and have sqlite convert it correctly into datetime('2011-03-13 15:00', 'utc'),
because this time is meant to be meaningful in New York, and "2011-03-13 15:00"
is on DST already.
In other words: I would like to state in which place a time is meant to be
meaningful, and have sqlite come up
with the proper timezone and DST correction depending on place and time. Sorry
for not making that clear in the beginning.
Is there a way to do that? How do you guys solve this?
/eno
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] jquery sqlite3
On 07.03.2011, at 23:57, Simon Slavin wrote: > > On 7 Mar 2011, at 10:46pm, Enrico Thierbach wrote: > >> (which, btw, says: "Android ships with SQLite version 3.4.0") As a related >> question: how far back is sqlite 3.4.0 ? > > June 2007 > > http://www.sqlite.org/changes.html > Ouch, that is quite old :) Luckily, some people report newer versions, see http://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Timezone/DST question
Hallo list, how would you tackle this problem? I have to add a number of times into the database. They come from "real live" sources, that, for example, say: "Sunday, March 13th, 2pm", or "Sunday, April 3rd, 2pm". Here in Central Europe we switch to DST on the last weekend of March, therefore the first time is "2011-03-13 14:00 UTC+1" (or "2011-03-13 14:00 CET"), while the latter is "2011-04-03 14:00 UTC+2" (or "2011-04-03 14:00 CEST"). Now sqlite already knows about DST. Does anyone see a way to use that already? I would hate to reinvent the wheel and to code this myself :) Thank you, /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] jquery sqlite3
On 07.03.2011, at 23:36, Matt Young wrote: > I looked up Noah Hart and found a very nice C# inerface to the SQLIite > system. That sound's ideal, under Chrome OS so with other vendor support I > hear. So specialized blog readers can use widgets built with c#/ and get it > all, in the bowser Not to forget the sqlite adapter in the android SDK. No need to do C# :) http://developer.android.com/reference/android/database/sqlite/package-summary.html (which, btw, says: "Android ships with SQLite version 3.4.0") As a related question: how far back is sqlite 3.4.0 ? /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] jquery sqlite3
It is noteworthy, however, that this spec won't make it into the standard; so browser vendors are basically free to drop it anytime. (yea that's sad.) /Eno On 07.03.2011, at 03:56, Simon Slavin wrote: > > On 7 Mar 2011, at 2:49am, Matt Young wrote: > >> Trying to understand it. How do I specifically open sqlite from a jquery >> widget. >> Or best simple example. > > jquery is just a JavaScript library. JavaScript can't open a file on your > hard disk. Because if JavaScript could open a file on your hard disk, anyone > who wrote a web site you visited could grab a copy of one of your files. > > However, HTML5 does have facilities in for using SQL: > > http://www.w3.org/TR/webdatabase/ > > and as it turns out, everyone who implemented this implemented it by building > SQLite into their JavaScript engine. It just uses private databases rather > than allowing you to specify a directory path. > > 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] Feature request: hash function for fast indices on text and blob fields
On 06.03.2011, at 14:22, Alexey Pechnikov wrote: > Sqlite does not has ability to compress indices. There is huge > performance problem with populating a database with indexed > text or blob fields. But we can emulate indices compression > by using index on hashed field (and populate the hash field by > trigger as example). I think one or more hash functions may be > included into core SQLite. > > Note: I use the public domain licensed murmur hash algorithm > (as extension) but this need custom build of SQLite or external > extension. > > Does anybody has the same problem? > Hey Alexey, couldn't you just define any hash function you like using SQLite's interface as outlined here? http://www.sqlite.org/c3ref/create_function.html /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adjusting strategy for ROWIDs
Hi list, I should have omitted the word "random" from the original post :) What was answered (but not specifically asked) is - sqlite has a pretty good PRNG. - yes, there is the birthday paradoxon. BTW, the calculation I wrote down in the other post regarding the birthday paradox was wrong. The probability of a conflict between two unique sets of 1000 numbers from a 64-bit-space is not 1000 * 2**-32, but about 1000 * 1000 * 2**-64, i.e. roughly 2e-14. (As a comparison: the probability to have all numbers right in a 6 of 49 lottery is ~10 times that). To reach a probability for a conflict each set would need to have 2**32 numbers. But these were not the original questions: > Does anyone have an idea, whether or not it is possible either > > a) to adjust the id in a trigger and have the last_insert_row_id return the > adjusted value, or > b) to adjust the strategy how sqlite generates row ids for newly generated > records. > > Any hint is greatly appreciated. So can we please come back to that? I will give this approach a try: I preset the sqlite_sequence entries for each autoincrement table on each database instance to some very large number. This should give each database a distinct "namespace". I see how far I can go with that. thanks, /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adjusting strategy for ROWIDs
On 05.03.2011, at 02:21, Simon Slavin wrote:
>
> On 5 Mar 2011, at 1:18am, Enrico Thierbach wrote:
>
>> I do have a working solution for synching my databases (with randomly
>> generated ROWIDs). The problem I face
>> is that I cannot get the rowid of a newly created record to pass thru back
>> into the application using last_insert_row_id.
>
> I think you have no real reason to use rowid at all here. Make a different
> field called 'uniqueKey' or something, and use that for your random numbers.
Hi simon, hi list,
using a different field would break associations between tables. For example:
Assume we have tables a and b, and a join table as_to_bs. The a and b table
both have a uniqueKey column, as you suggest, that will be set by a trigger.
If two database applications create a and b objects that are joined (e.g.
a_rowid = INSERT INTO a VALUES("a")
b_rowid = INSERT INTO b VALUES("b")
INSERT INTO as_to_bs (a_id, b_id)
both databases would end up with (1, 1) entries in the as_to_bs table. Now I
could merge the a and b tables quite fine. In this process, however, the
entries in the a and b tables would be assigned new rowids. If I would then
merge the as_to_bs tables the association between the newly merged objects in A
and B would be lost.
Remember: as the application should not know about this replication thingy it
would still use the id or rowid to refer its object and not a uniqueKey column
or something.
So, the question remains: how can I either adjust the way rowids are
automatically generated or adjust each specific rowid when a row is inserted
and have this one returned in the last_row_id.
/eno
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adjusting strategy for ROWIDs
On 04.03.2011, at 22:21, Drake Wilson wrote: > Quoth Enrico Thierbach , on 2011-03-04 22:11:07 +0100: >> If I insert a record into a table with a primary key column id, >> Sqlite assigns a ROWID as outlined here >> http://www.sqlite.org/autoinc.html. However, I would like to assign >> a totally random rowid. > > Why do you want to do this? In particular, why would it not work to > randomize the values from the application side during inserts? > > Random numbers collide faster than you might expect if you're not > familiar with the birthday paradox; normally, truly-random values that > are expected to have no collisions are generated using entropy from > the environment and are at least 128 bits long. I suspect this is not > what you want. If you just want them to be "random-looking" then it > may be more convenient to relate the underlying ID and the exterior ID > through a suitable permutation of the 64-bit integer space. If > neither of those is true, you're probably looking at probing several > times to avoid collisions, and that's not something the stock "pick a > new row ID" mechanism handles AFAIK. > Hi drake, thanks for your answer. I am trying to build a solution where two or more databases can synchronize with each other. As in my problem area the databases are pretty small (probably less than a 1000 rows), the collision probability given roughly 64 bit of randomness would be about 1000 / 2^32. This is less than 10^-6, which is good enough for me. However, I am aware that there are better ways to generate IDs that are guaranteed to be different in different databases. I do have a working solution for synching my databases (with randomly generated ROWIDs). The problem I face is that I cannot get the rowid of a newly created record to pass thru back into the application using last_insert_row_id. To explicitely generate an ID in the application, however, is not a suitable solution for me. This must work out of the box (i.e. on a database) with any application that uses this database. Therefore I am somewhat limited to what SQLite can give me via SQL (or probably by extending SQLite via its C interface). /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Adjusting strategy for ROWIDs
Hi everyone, I just stumbled about a problem with sqlite that I am not able to solve. I hope to find the most experienced sqlite users here, maybe you know of a solution :) If I insert a record into a table with a primary key column id, Sqlite assigns a ROWID as outlined here http://www.sqlite.org/autoinc.html. However, I would like to assign a totally random rowid. While I can adjust the rowid with a trigger a la CREATE TRIGGER table_insert AFTER insert ON table BEGIN UPDATE table SET id=random() WHERE rowid=new.rowid; END; I don't find a way to get the id adjusted like this via last_inserted_row_id. I then tried to adjust the inserted id via fiddling with the SQLITE_SEQUENCE table (not without some nervous hesitation :). A quick test in the sqlite3 command line tool, however, showed that one can only increase the next automatically id, but not decrease it. Hence, no avail. Does anyone have an idea, whether or not it is possible either a) to adjust the id in a trigger and have the last_insert_row_id return the adjusted value, or b) to adjust the strategy how sqlite generates row ids for newly generated records. Any hint is greatly appreciated. Thank you, /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
