Manuj Bhatia wrote:
> I do not have a requirement of persistence in my current design, but I
> expect that we might extend this shared-queue solution to more areas of
> the server and will require some sort of persistence then.
> That is one of the main reasons I do not want to use IPC queues
> Devs were told to make sure they are validating their sql statements,
> but as I have seen in the code, few of them have
> Question: Do you think that instead of getting them go back throughout
> their code, it is feasible to create a function that just eliminates the ;
> and replaces it wit
The tcl interface spec says:
> The "last_insert_rowid" method returns an integer which is the ROWID of
> the most recently inserted database row.
So if I have
db eval {CREATE TABLE t(the_key INTEGER PRIMARY KEY, data TEXT)}
and I
db eval {INSERT INTO t VALUES(NULL, 'foo')}
then does
db
Let's say my app has (only) inserts followed by (only) reads.
The reads are best served by some indices. So I can create the indices
before the INSERTs, or after them.
In general, should I expect a run time perf difference between these two
options?
Eric
--
Eric A. Smith
Louis Paste
Simon Slavin wrote:
> The standard assumption about SQLite is that it's faster to do your
> INSERTs first, then create the indices. How much of a difference this
> makes depends on a lot of things.
On what things does it depend?
--
Eric A. Smith
Sendmail may be safely run set-user-id to
I have no user-defined indices in my db, and want to do a largish number
of inserts (a few billion). I COMMIT every 10 million INSERTs or so --
so if my app dies (or I want to kill it) I don't have to start over.
Row sizes are small, a couple hundred bytes across 15ish columns.
The primary
Terribly sorry to self-reply, but I have a correction:
> I'm using the Tcl API, which probably doesn't matter for this question.
Don't know what I was thinking when I typed that. I'm sharing a
connection in a single thread, mixing C API calls and Tcl API calls.
The C API calls drive the IN
Jay A. Kreibich wrote:
> > I'd really love to avoid writing a big journal file. And I'd love to
> > avoid doing a billion-row insert in one transaction.
>
> So turn journaling off.
... which implies possible corruption on app failure, right?
I want progress to be saved every once in a while
Simon Slavin wrote:
> How do you pass the handle from your C code to your Tcl code ?
I don't. I pass it from Tcl to C.
The handle in Tcl is a command name registered with the interpreter.
SQLite attaches a structure to the registration that contains the
underlying handle as used by the
Jay A. Kreibich wrote:
> Yes. Hence the "and this is the important part" comment. Most of
> the time when people are building billion-row files, they're building
> a new DB by importing a static source of data. If things go wrong,
> you just throw out the database and try again.
That's
Jay A. Kreibich wrote:
> Try getting rid of the PK definition and see how much that buys you.
> It might be worth it, or it might not.
and Simon Slavin wrote:
> We know you are doing a huge amount of writing to this database. Are
> you also reading it frequently ? If not, then it might
Eric Smith wrote:
> I'd like to show it to the forum -- is it possible to send emails with
> attachments here? It's a 60kb jpg file.
God bless the gnuplot developers, who provided an ascii output option:
time (minutes) to insert 2
Black, Michael (IS) wrote:
> Hmmma 6.5X speed diff between RAM and disk? Sounds pretty good to
> me. Not sure why you expect better.
I don't expect better. As I said, I'm "not really sure what I should
expect here, or how to find out what I should expect".
> 10,000/sec is crappy?
I have confirmed that INSERT times are roughly logarithmic in
the number of existing records after creating my specific user
indices.
But INSERT times appeared to be *linear* in the number of existing
records before I had created any user indices (and with no primary
keys or unique indices def
Jim Wilcoxson wrote:
> Insert times should be constant for the 2nd case: no primary key, no
> indexes; ie, it doesn't matter how many records are already in the
> database. I confirmed this with SQLite 3.6.18.
Definitely not constant. Looks linear to me -- you saw the plot, you
can decide
In another thread in this forum, someone says they noticed a behavior
in sqlite version 3.6.18 different (better) than what I've observed in
3.6.23.1.
Where can I find version 3.6.18 (or, more generally, any old version)
for testing?
Eric
--
Eric A. Smith
It's up. It sorta works. Tha
Richard Hipp wrote:
> When there are no indices, SQLite can put the rows into the database in
> any order it wants, and it chooses to put them in ascending order. Thus,
> each insert becomes a constant-time append. (Approximately - the truth is
> a little more complicated, but by waving our
Jay A. Kreibich wrote:
> What OS/filesystem are you using?
>
> SQL inserts should be near-constant, assuming the table does not
> have an INTEGER PRIMARY KEY with explicit values. The table's root
> B-Tree needs to re-balance every now and then, but if the inserts are
> in-order (which th
Scott Hess wrote:
> You should reduce your demonstration case to something you'd be
> willing to post the code for. Probably using synthetic data
> (preferably generated data). There's something missing in the thread
> right now, and it's unlikely to be exposed by random shots in the
> dark
Scott Hess wrote:
> You should reduce your demonstration case to something you'd be
> willing to post the code for. Probably using synthetic data
> (preferably generated data). There's something missing in the thread
> right now, and it's unlikely to be exposed by random shots in the
> dark
Black, Michael (IS) wrote:
> Though I'm not sure if there's any advantage/disadvantage to 64-bit
> binaries for sqlite3, is there?
64-bit SQLite can cache more than 4Gb of your db.
Eric
--
Eric A. Smith
Finagle's First Law: If an experiment works, something has gone wrong.
___
Peng Yu wrote:
> On Thu, Jun 24, 2010 at 5:05 AM, Simon Slavin wrote:
> >
> > On 24 Jun 2010, at 4:50am, Peng Yu wrote:
> >
> >> Is there a way to use Shebang for sqlite3 script?
> >>
> >> http://en.wikipedia.org/wiki/Shebang_%28Unix%29
> >
> > SQLite comes with a command-line tool. You can feed
>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?
> Please provide feedback - positive, negative, or indifferent - to this
> mailing list.
Minor: the link in about.html from "fopen()" to
http://man.he.net/man3/fopen is broken.
Eric
--
Eric A. Smith
Mandelbug, n.:
A bug whose underlying causes are so complex and obscure as to
make it
Matheus Ledesma wrote:
> "arm-none-linux-gnueabi-gcc -o balanca
> balanca_simula_peso_io_paralela.c -Wall -W -O2
> -Wl,-R/home/generic/CodeSourcery/Sourcery_G++_Lite/lib -lsqlite3
> -lpthread -static"
Try adding '-ldl' to your args.
Eric
--
Eric A. Smith
Where a calculator on the ENIAC is
Matheus Ledesma wrote:
> With "-ldl" after "-static" argument I receive the following error:
>
> balanca_simula_peso_io_paralela.c:632: warning: 'mseg1' may be used
> uninitialized in this function
>
> /home/generic/CodeSourcery/Sourcery_G++_Lite/bin/../arm-none-linux-gnueabi/libc/lib/libsql
Nick Shaw wrote:
> Realised I also fixed this warning before posting too:
You may not be getting a lot of responses on this because the SQLite
devs have a philosophy that, for this project, chasing down compiler
warnings is generally a waste of time.
See http://sqlite.org/testing.html#stat
Nick Shaw wrote:
> If it's safe to ignore
Note that I was careful not to say that the warnings are safe to
ignore. :) I only said that the SQLite devs may ignore them without
further evidence of problems.
I'm not an expert on the SQLite code, so wouldn't make any specific
claims about it
Subhadeep Ghosh wrote:
> I finally managed to create a wrapper around the SQLite core to support
> the creation of in-memory databases. The wrapper comprises of three
> functions - one to serialize the database, one to de-serialize a database
> and the third one to do the cleanup job.
>
>
tclsqlite.html lists an "unlock_notify" method with no other
documentation. Trying to use it gives me this:
-bash-2.05b$ tcl
% package require sqlite
3.6.23
% sqlite3 db /tmp/foo
% db unlock_notify
unlock_notify not available in this build
%
--
Eric A. Smith
The concept is interesting and well
I'm forced into a situation where I have to use an nfs server that I
think is buggy.
I can read/write files normally using fopen() on the exported
filesystem, but can't do anything useful with sqlite 3.6.23.1:
-bash-2.05b$ tclsh
% package require sqlite
3.6.23
% sqlite3 db ./foo
% db eval {pr
Richard Hipp wrote:
> sqlite3 db ./foo -vfs unix-dotfile
Works like a charm!
> That uses an alternative VFS that uses dot-file locking instead of posix
> advisory locks. The dot-file locks are someone slower and have less
> concurrency, so the use of "PRAGMA locking_mode=EXCLUSIVE" might al
Subhadeep Ghosh wrote:
> I am aware of the functionality which you mentioned. But the reason why
> I ended up coding the feature was because of a very specific requirement.
> My requirement was such that some of my client applications were running
> on disk-less systems where I was not using
Richard Hipp wrote:
> sqlite3 db ./foo -vfs unix-dotfile
>
> That uses an alternative VFS that uses dot-file locking instead of
> posix advisory locks. The dot-file locks are someone slower and have less
> concurrency, so the use of "PRAGMA locking_mode=EXCLUSIVE" might also be a
> good ide
Kavita Raghunathan wrote:
> sprintf(SqlStr, "INSERT INTO %s (AttrName, AttrEnum, AttrType,
> AttrValue, ReadWrite, Entity_id) VALUES('%s', %d, %d, '%s', %d,
> %d);", tbl_name, db[i]->attr_name, db[i]->attr_num, db[i]->attr_type,
> db[i]->attr_value, db[i]->attr_src, entity_id);
Don't do that
Simon Slavin wrote:
> BLOBs can handle any sequences of bytes without problems,
> including nulls, ETX, and sequences which be illegal if they were used to
> express Unicode characters. You can put anything you like in a BLOB.
I assume, due to the manifest typing semantics of the library, t
Simo Slavin wrote:
> (according to your earlier post)
I'm not OP. I'm Eric. OP was someone else. In this context, I don't
care about blobs or about the right way of doing anything.
> Read the documentation for memset().
I know quite well how memset works. I know character!=byte. These
smengl90 wrote:
> Hi, I want to compose a query that will use a c++ variable. For example I
> have:
>
> int i= 5;
> char * query = "SELECT * from userInfo WHERE count<'i'".
>
> The syntax does not work. How do I do that?
I think your primary problem is lack of experience in C++. I
strongly re
> I strongly recommend that you always make the child side of fork(2)
> either exit(2) or exec(2) immediately.
Sorry Nico, I never saw this response -- I appreciate it!
What do you mean, "immediately"? As I said, my child comes to life,
does some work without touching (its copy of) existing S
Calimeron wrote:
> Chinese Char. No. Chinese Char. English Def. No.English Def.
> 1 Char1 1 Def1
> 2 Char2 2 Def2
> 2 Char2 3 Def3
>
Roger Binns wrote:
> I'll bet you are actually getting exit(3) which means anything
> registered with atexit will be run. (SQLite does not register with
> atexit.)
I knew what Nico meant :) just repeated him for expositional clarity
(deeming the distinction to be unimportant for my particu
Calimeron wrote:
> I've understood that the program (I'm
> using http://www.sqlite.org/sqlite-3_6_23_1.zip) will first compile the
> entire list 30.000 x 50.000 = 1.5 BILLION lines, after which it filters
> it.
One of SQLite's primary goals in life is to fit into a tiny memory
footprint.
Calimeron wrote:
> >What do you mean, "save"?
>
> So that when you're done, you have 3 tables "Chinese," "English," "Merged"
> or the original 2, but with data from the one into the other.
>
> I don't know the terms or the procedure, but I'd like to have a new table
> that has the Chinese
W.-H. Gu wrote:
> For (1), after I wrapped inserts into a transaction, I face an issue:
> the cpu usage is too high. I think the reason is that I use prepare
> statement to insert a row at a time, it than does sqlite3_step for
> thousands times every second.
If I'm reading this right, you
Roger Binns wrote:
> I'd have no problem contributing the code to SQLite, but there isn't
> very much of it
I don't think the devs would complain about that. "This code has too
few lines!"
> and it is an open issue as to how you report the cross fork usage should
> it happen. (In my case I
Richard Hipp wrote:
> Try using:
>
> sqlite3 db ./foo -vfs unix-dotfile
When my application dies while holding a lock under unix-dotfile, all
subsequent runs always think the database is locked. I guess this is
because the lock file exists.
Is there an obvious/good way to know whether the
Griggs, Donald wrote:
> Is the percentage of the final rowcount really a criterion?
The answer to that, according to my brief exploration, is somewhere
between "yes" and "very much yes", depending on various factors.
--
Eric A. Smith
The number of UNIX installations has grown to 10, with m
Werner Smit wrote:
> My question(s)
> If I want to use the "insert or replace" to populate my database of
> around 1 million records.
> And I want to do it as fast as possible.
> What are all the tricks I can use?
Obey the first rule of optimization: don't do it unless you're
sure you n
Griggs, Donald wrote:
> I guess I was wondering if the fastest records-per-transaction value
> would depend on the page cache and be more or less independent of the
> total records to be imported.
I think the page cache is one of a great many variables.
> So, the records-per-transaction for
Black, Michael (IS) wrote:
> Does anybody know how to make the journal file go to a different
> location than the database? Apprarently it's not treated as a "temporary"
> file. Perhaps it should be??
Seems like you'd have to communicate the journal location to other
processes, meaning yo
Werner Smit wrote:
> After taking out count(*) and adding a few pragma's and saving 6000
> records rather than 500 at a time I've got it down to 34 minutes.
> If I build in on local drive it takes 28 minutes.(with chunks of 500)
Why not do an apples-to-apples test and commit the same number
Darren Duncan wrote:
> I don't have time to investigate right now, but both failing tests seem
> to be connected with concurrent access to a table by two forked processes
> (the test script forks a child, which does concurrent access).
>
> At least in the second case, the DROP TABLE and CREA
peterwinson1 wrote:
> Hello,
>
> I have a some what complex question about UPDATE. I have the following
> table
>
> table1 (KEY, COL1)
>
> 0, 1
> 1, 2
> 2, 3
> 3, 4
>
> What I would like to do is to UPDATE COL1 by subtracting the COL1 value
> where KEY = 0 from the COL1 value of the current r
Afaict sqlite doesn't support indices on subsets of rows in a table, Ю
la http://en.wikipedia.org/wiki/Partial_index -- right?
Any plans to implement that?
Are there any known hacks to implement something similar?
--
Eric A. Smith
Keeping Young #3:
Keep the juices flowing by janglin round gentl
Tim Romano wrote:
> The partial index is one very messy thing, fraught with ambiguities,
> something to avoid.
I want an index that only can be used to find rows with a particular
value or set of values. In what way is that ambiguous? Other databases
(e.g. postgres) seem to support this k
Peng Yu wrote:
> I have the following code to search for neighboring positions
> (distance <=10). But it is slow for large data set. I'm wondering what
> is the most efficient query for such a search. Note that I don't
> create an index, as I'm not sure what index to create on table A.
I haven't
Igor Tandetnik wrote:
> > How would you find a row whose column X contained value Y if the
> > "partial" index on column X specified that rows containing value Y
> > in column X should never be returned?
>
> No one suggests partial index should be capable of hiding anything. The
> idea is
Stephen Oberholtzer wrote:
> I believe what he's getting at is this:
{snip explanation}
You exactly understand what I'm going for and my use case.
Is there a better way to implement it in sql itself than what I
outlined? I.e. create my own index table that points to the proper
rows and keep it
Sorry, let me amend that:
> The schema is roughly
>
> create table records(__recno INTEGER PRIMARY KEY, fileId, data);
Forget the INTEGER PRIMARY KEY. My partial index would reference
the _rowid_. I don't permit vacuums on the database so, if I'm not
mistaken, this shouldn't be an issue.
--
Kees Nuyt wrote:
> You could add a "deleted" column with value range (0,1) and
> create an index on it if benchmarks show that makes it
> faster. As a bonus it is easier to code and maintain than a
> separate table with references and triggers.
>
> Alternatively, you can create an composite index
Simon Slavin wrote:
> http://www.sqlite.org/lang_createview.html
>
> This is the SQL standard way to reduce your view of a table to just
> certain rows. If I understand your request, this feature should provide
> exactly what you want. Appropriate indexes will be used when consulting
> any
Scott Frankel wrote:
> Hi all,
>
> How does one find the next value of a serial item? Given a simple
> table with a serial primary key, I'd like to get the next available
> integer key value. eg:
>
> CREATE TABLE foo (
> foo_id SERIAL PRIMARY KEY,
> name
My Spanish isn't perfect but it looks like you're running out of file
descriptors. I.e. you (or a library or module you are using) is creating file
descriptors using e.g. open(2) and never close(2)ing them.
Please forgive typos--I sent this from my smart phone.
On Sep 1, 2010, at 15:34, andre
The sqlite int space is 64 bits. You'd have to generate 1 billion keys
per second for ~600 years to exhaust the space. Is my math right?
Eric
> Hello,
>
> My Primary Key is an integer. In the lifetime of a product, it may
> increase to the maximum possible value of an integer. Our proces
> I currently use a single database and it works fine.
> The trouble is replication.
> Or the sharing of an ever growing database.
I think your question is more architectural than sqlite-specific.
If your user base / data set is going to be getting more serious and
if you have some time o
Lynton Grice wrote:
> Many thanks for your feedback, much appreciated ;-)
>
> But why would that happen on Solaris and not Debian?
Did you try it on real OSs & not VMs? Are you sure the host OS is
giving the same compute & disk slices to the VMs? Any other differences
in those mappings?
Are t
Gilles Ganault wrote:
> Sorry about that :-/ Apparently, gcc will compile and link in one go.
> Is a Makefile required to add the -lpthread switch, or can this be
> done on the command line?
You can do it on the command line -- just try it.
make(1) (and its typical input, a set of makefi
On Fri, Dec 17, 2010 at 4:36 AM, giuseppe500 wrote:
> There is a version of SQLite 3 for 64-bit systems?
> or, you can simply compile the source of sqlite3 at 64-bit with c++ 2008?
> thanks.
FWIW I compiled sqlite 3.6.23.1 along with its tcl hooks and have been
happily using it in a (single-threa
You might also worry about your OS's page cache affecting your result. In
modern linuxes you can say
# sync
# echo 1 > /proc/sys/vm/drop_caches
That will free the page cache.
Eric
On Wed, Dec 29, 2010 at 6:11 PM, Simon Slavin wrote:
>
> On 30 Dec 2010, at 12:02am, KimTaein wrote:
>
> > How c
Sachin Gupta wrote:
> Our application is kind of heavily multi-threaded and required using
> sqlite to be used as an in memory database for caching purposes. Being
> multi-threaded requires read-write locking mechanism, and WAL seemed
> quite the thing for us. We are expecting high rate of I
Rich Shepard wrote:
> What controls the information in /usr/lib/pkgconfig/sqlite3.pc?
>
> Here, it contains:
>
> # Package Information for pkg-config
>
> prefix=/usr
> exec_prefix=${prefix}
> libdir=/usr/lib
> includedir=${prefix}/include
>
> Name: SQLite
> Description: SQL database
Stephen Oberholtzer wrote:
> Good news, everyone! I've taught SQLite to feel love!
FINALLY. I put in that feature request like 3 years ago.
--
Eric A. Smith
Computer programs don't like being anthropomorphized.
___
sqlite-users mailing list
sqlite-us
On Wed, Jan 26, 2011 at 11:02 AM, Ian Hardingham wrote:
> Hey guys.
>
> I am under the impression that there is no concurrent access to a single
> SQLite DB. Ie if thread A is performing a query, and thread B trys to
> query, it will block until thread A is finished, no matter the query.
>
> 1.
SQLite version 3.7.4 with TEA. gcc was run like this:
gcc -DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\"
-DPACKAGE_VERSION=\"3.7.4\" -DPACKAGE_STRING=\"sqlite\ 3.7.4\"
-DPACKAGE_BUGREPORT=\"\" -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1
-DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1
Igor Tandetnik wrote:
> On 1/26/2011 6:39 PM, Eric Smith wrote:
> > busy handler not called on lock escalation
>
> This behavior is by design. See
>
> http://sqlite.org/c3ref/busy_handler.html
>
> the part that talks about a deadlock.
Understood & agreed
Ian Hardingham wrote:
> I'm using an SQLite implementation that someone else made for my
> high-level language of choice.
>
> While looking through the imp, I've just found this function, which is
> used as the callback argument to sqlite3_exec. Does this look like an
> ok useage? It seems t
Yuzem wrote:
> >
> > Test#1
> > create index tags_index on tags(tag);
> > You should have an index for any fields you query on like this.
> >
>
> Thanks Michael but I don't see any speed improvement:
> create index test on tags(tag);
> select count(distinct tag) from tags;
>
> This is much fas
Yuzem wrote:
> I thought sqlite didn't handle foreign keys correctly, I am using
> triggers to automatically delete tags when a movie gets deleted.
There's a new thing, 'PRAGMA foreign_keys=1', that causes SQLite to
enforce them. Check out the docs on that.
> Anyway, to use integers in t
Fredrik Karlsson wrote:
> package require sqlite3
> sqlite3 db :memory:
> db eval {create table a (id INTEGER);}
> db eval {insert into a values (1);}
> db eval {insert into a values (2);}
> db eval {select * from a where id in (1,3);} vals {parray vals}
> vals(*) = id
> vals(id) = 1
> set alist
http://sqlite.org/features.html
"Database For Gadgets. SQLite is popular choice for the ..."
should have an article, like
"Database For Gadgets. SQLite is a popular choice for the ..."
Eric
--
Eric A. Smith
Windows is *NOT* a virus - viruses are small and efficient.
_
On Sat, Mar 5, 2011 at 8:14 AM, BareFeetWare wrote:
> On 05/03/2011, at 1:59 AM, "J Trahair"
> wrote:
>
> > I understand about Primary keys and Unique keys attributed to their own
> field. Is there a need to have other fields indexed, for faster searching?
> Eg. a table containing your favourite
On Sat, Apr 2, 2011 at 8:01 PM, Matt Young wrote:
> sqlite> select 1 as type union select 2;
> type
> 1
> 2
> sqlite> select 'tr' as type union select 2;
> type
> 2
> tr
> sqlite>
>
> The order of the rows change when text replaces a numeric.
>
My understanding is that if you don't use an ORDER
On Thu, Apr 21, 2011 at 2:33 PM, Phoenix wrote:
> Hi,
>
> I have a database with 130 million rows. It's an RDBMS.
>
> I am thinking of using Sqlite3 as a kind of a backup, just for the
> important bits of data.
>
> Questions.
>
> 1. Is this advisable? Will Sqlite3 hold up to this volume?
>
I wro
83 matches
Mail list logo
