> Two users – members of staff – enter data. Each user enters a new
> invoice. One of these entries gets rolled back. What should their
> software do ? Or should it just return an error message to the user ?
Multi-user data entry is not a part of my intended use case. I think other
This is a request for a small change to the handling of multiple
connections. I think it would significantly enhance the usefulness there
via allowing multiple "views" of the data.
Consider that I have two simultaneous connections to one file, named Con1
and Con2. They could be in one process or
I agree that Mailman is archaic. I worry about the security on it. I don't
enjoy using 3rd-party mirrors for searching it. I'd like to propose that we
upgrade to something more modern and secure like Sympa or mlmmj, or even a
more drastic system upgrade to something like Redmine -- a project
I have this query:
UPDATE nodes SET parent = ? WHERE SUBSTR(name, 0, ?) = ?
EXPLAIN QUERY PLAN tells me that it is going to do a table scan. At the
same time, the query plan for this:
SELECT * FROM nodes WHERE SUBSTR(name, 0, ?) = ?
tells me that it can and will use the (primary key) index on the
>I recommend you execute the SQL command ANALYZE then try various of those
SELECTs again. This can cause the SQLite optimizer to make different
choices about which indexes to use.
Thanks for the suggestion. I am familiar with the Analyze command and have
been using it. However, it doesn't seem
I've got this (simplified) table schema:
CREATE TABLE [Services] ([Id] INTEGER PRIMARY KEY, [AssetId] INTEGER NULL,
[Name] TEXT NOT NULL);
CREATE TABLE [Telemetry] ([Id] INTEGER PRIMARY KEY, [ServiceId] INTEGER
NULL, [Name] TEXT NOT NULL)
CREATE TABLE [Events] ([Id] INTEGER PRIMARY
Dragging a database onto the sqlite.exe in explorer works.
> I downloaded the command line utility, and it is an exe file
> that automatically opens the sqlite prompt. Given this, how
> am I supposed to open or create databases? The only way I'm
> aware so far is that you have to specify the
a "normal C long" on 64 bit linux would be the same data type as
sqlite_int64. On a 32bit system, just cast it (assuming you know that you'll
never have more than 2 billion rows).
> What I'm not clear about is that the documentation says that
> rowid is an alias for the primary key column of
One quick question, is that with memory management enabled in the sqlite
compile or not? I've done some extensive memory checking with memory
management disabled and never found a leak. However, with memory management
enabled, all bets are off.
> I believe the memory leak exists. The following is
> Thanks for the info. Points 2, 4 and 5 are covered by the
> Makefile (DB.h is generated with javah), but I'll patch the
> project for the variable declarations and the cast. Though I
> have a feeling the cast is unncessesary, did VC throw an
> error or warning for that?
It was just a
To compile the binary with VC71, I had to
1. move a dozen variable declarations to the top of the function
2. download the DB.h file separately from the build tree
3. change the jstrlen to end with "return (int)(s - str) - suppChars"
4. change my sqlite3 lib build to #define
You'll have to change that in the makefile before building the
"target_source" code, which means you'll need Linux or a similar build
environment on Windows. I don't think you want to do that anyway. MS's
memory handlers don't like that at all. For proof (once you get it enabled)
put this into
The VC6 compiler is from 1998. The VC 7.1 or 8.0 compilers produce better
compilations. I'm certain any Borland or GNU compiler of the past 3 years
would also produce better assembly than VC6. And if somebody has their hands
on a PathScale or Intel compiler, please post some benchmarks!
>> select rowid from table limit 1 offset -1;
> Two ways to do this:
>SELECT rowid FROM table ORDER BY rowid DESC LIMIT 1;
>SELECT max(rowid) FROM table;
Yes, but neither one of those would be as fast as this query, true?
SELECT rowid FROM table LIMIT 1
I guess I was thinking to
I know that this list has been over the issues of using ROWID to get the
count. Nevertheless, I was thinking, if you never do any deletes the
last ROWID should contain the count. It's too bad you can't do an offset
of negative one so that it would start at the back. That should be darn
Thanks, Igor, you've inspired and saved me yet again. The subqueries you had
used for the x/yEnd did not work, but the rest did and I have that maxim
information beforehand anyway. Here's how it shook down:
cast(cast((xStart+xEnd) as double)/2/15518.5 as integer) cellX,
I can see no difference in my time measurements in changing the "order by
desc limit 1" to "max".
> >I'm wondering if the following query can be done as a single query
> >rather than running it in a (nested) loop.
> >Suppose a database with five columns; xStart, yStart, xEnd, yEnd,
peed this up,
please mention it.
omit frame pointers.
I suppose I'll dig in and make a custom JNI interface so that most of my
code is done in C, but just thought I'd ask around first... Thanks for your
time. The other Java wrappers posted seem to wrap too much or too little or
not be compatible with version 3.
Is there a place I can post some icon files made from the logo on the
main sqlite.org page?
And where would I post unofficial builds of sqlite3.exe, like the one I
did with current CVS today using VC71 and embedding the icon?
I've seen a few issues myself, but they all appeared to be related to
the memory management thing. If you're using VC, try putting this into
// and then in your main function:
_CrtSetDbgFlag ( _CRTDBG_ALLOC_MEM_DF | _CRTDBG_LEAK_CHECK_DF
For me, I have a bunch of threads writing to the database. That is the only
part I do multithreaded. (All my read queries are handled after all the data
is written.) I just use the scoped_lock operator from the Boost library at
the top of my function that does the bind and step calls. I pass a
transaction running at any given time.
> > EXPLAIN QUERY PLAN SELECT * FROM vals2d WHERE x = 1 OR y = 1;
> select * from vals2d where x=1
> union all
> select * from vals2d where y=1
Super! You've greatly helped me yet again. My query really looked like this:
SELECT x,y,val FROM vals2d WHERE x = 1 OR y = 1 ORDER BY val DESC
or your time.
Thank you for taking the time to sort out my query! (The meat is at the
> results_1 r
> INNER JOIN bounds b ON
> r.qis = b.bqis AND r.ris = b.bris
select qi, ri, drl, max(score), min(score) from ...
What values of qi, ri, and drl would you want
this query to return?
What you have to do is:
SELECT qi, ri, drl, score
WHERE score=(SELECT max(score) FROM ...)
Thank you for the instruction, although the other query
Thanks again for any help.
is allocated in blocks, but deallocated one piece at a time.
sqlite - 0 error(s), 199 warning(s)
Thank you for answering the question! Like I said in my other post, the
index creation is about 20% faster than the unique index creation in my
latest tests of an index of two INTEGERs. 20% out of several hours is
So index names are unique for the whole database? I was thinking they
> I am simply curious. This sounds like an amazing
> engineering challenge. If it is not a secret, can you
> describe what this data represents and how it will be used?
Genomics. Look up "Smith-Waterman" or "Needleman-Wunsch-Sellers" on the web.
> What is the ultimate source of this
> If I understand it right you'll definitely need a correlated subquery.
> You need one query to calculate the max() of the 257(0-256?) values.
> There must be some way to define which values should be used
> in that calculation for a specific piece (the distance). Is
> the distance geometric
> _Personally_ I think, this sounds like a task not quite
> fitting in sqlites (probably any 'standard' databases) realm.
> This is a bit off-topic in this group, but because you
> mention sub-boxes - did you ever look into more specialized
> file-formats like HDF5:
Jay Sprenkle wrote:
score(x,y) = max(score(x+n,y+n)-n*penalty) where n < drc(x+n,y+n) < 256
So at some 'sample' you have the score and drc, but not the x,y value?
The x,y values can be interpolated using some algebra and the surrounding
Uh, no. Sorry my other emails were unclear. At
John Stanton wrote:
You don't seem to need a data manipulation system like Sqlite, more a
form of high volume storage. Do you really need elaborate SQL,
journalling, ROLLBACK and assured disk storage?
Di you consider some form of hashed storage, perhaps linear hashing,
to build a compact
For your mail i think you have everything planned, but just for
curiosity, how do you plan to pass data from hardware (800 MB/s +
Database data) to a raid?? A GigEthernet has 1000 Mb/s (so, 128 MB/s),
a PCI-X based solution has 1.3 to 2.6 Gb/s (so 150-300 MB/s), and
similar for SATA and
The documentation says to put the database name on the front of the
index name, not the table name when using the create index command. I
thought it was weird myself.
Micha Bieber wrote:
I'm receiving sql errors like the following
"no such table: main.phs_matrices_1"
when trying to create
Declare it static in the class declaration, not at the function definition.
Esteban Zeller wrote:
El Sábado 20 Mayo 2006 21:40, Brannon King escribió:
Is it declared as a static function? Something like "static int
bd::analisis_ultimo(void* arg)" ? I assume it would need to be s
Is it declared as a static function? Something like "static int
bd::analisis_ultimo(void* arg)" ? I assume it would need to be static
just like all the other thread function parameters or callback
parameters in various APIs.
Esteban Zeller wrote:
I'v got the next problem:
string cola =
Dennis Cote wrote:
Where did you get the idea there are "fake" columns?
"SELECT 200" returns 200. I'd call that a fake column. What is the
proper name for it?
What I was picturing was something like this: "SELECT (BETWEEN 200 AND
500) as a, a+2" and then get 300 outputs. That would be
The benefits I'm trying to get out of sqlite are the data queries. I
collect a large, sparse 2D array from hardware. The hardware device is
giving me a few GB of data data at 200MB/s. Future hardware versions
will be four times that fast and give me terabytes of data. After I have
the data, I
t line to include '(select 300) as a'. That still didn't let me do what I
really wanted to do; use 'a' in the max function. What's the right way to do
that? And how do I simply return a range of numbers with a select statement?
It works like a champ. Thanks.
> > Suppose I have two tables, A and B. A has two columns: score,
> > location. B has two columns leftLocation, rightLocation.
> > I need the maximum score located outside all entries in
> table B. B is
> > a small table, say less than 100 entries.
Suppose I have two tables, A and B. A has two columns: score, location.
B has two columns leftLocation, rightLocation.
I need the maximum score located outside all entries in table B. B is a
small table, say less than 100 entries.
I tried this:
SELECT max(score),location FROM A, B GROUP BY
Is there some way I can create that unique index without the overhead of
uniqueness checking? I know all my triples are unique when used together: I
generated them that way. Or is there some other way I can create that index
> I'm not sure what you are getting at here. Copy memory from
> where to where? The database is much more than an array of
> record structures that you can copy data into.
> Dennis Cote
I've been studying the profile and can see my thinking was wrong. I was
thinking we would just log an
So I dropped the "not null" and it gave a ~2% increase in overall speed.
It's not much, but may count for something. It's a little strange because
that is just the opposite of MySQL. In MySQL, it shrinks the database size
and speeds up the access by declaring a column not null.
I am using the
>Also, do a large number of inserts within a transaction. (1000-100,000 or
so, not a billion ;) )
I don't see how increasing the number of inserts per transaction changes the
speed at which the sqlite3_step function executes when it is called on an
insert in the middle of the transaction. I'll
As I understand SQL, "alter table blah add primary key (blah1, blah2)"
should be how you do it. The sqlite documentation seems to say
otherwise. Actually, I cannot figure out from the documentation how to
add a primary key after the table is created and data is entered. How is
it done? Or is
Thanks. That command created the files; however,
grep sqlite3_sleep ./tsrc/*
and that's all. In other words, it's declared but never defined.
> So I downloaded the latest CVS files on my Linux box. What do I run to
> generate the c files I need for
configure or make for this to work right?
I searched the newsgroup for this information, but was unsuccessful. Is
there a link that explains it already available?
Thanks for your time.
ers%40sqlite.org/msg14954.html ) ?
Thanks for your time.
Mail list logo