[sqlite] Point a newbie in the right direction ;)

[Monte Milanuk]

Hello all.  New guy here ;)

I started working my way through a Python programming book, and got
introduced to SQLite on one of the Python mailing lists.  I have a
particular project goal in mind for when I get a little further along with
Python - making a cross-platform application for running tournaments.
SQLite seems like it would be ideal for handling all the background work
involved with the record keeping and such.

Where I need some help (or more specifically, some pointers towards books,
tutorials and such) is on how I should organize the data in SQLite - how to
split it up between tables, referencing them in queries, backups, dumps,
etc.

I've been stepping through one tutorial I found online, but it is just the
very basics of SQLite operation.  I think (maybe) I will be needing
something covering more fleshed out SQL examples - any suggestions?

Thanks,

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

Re: [sqlite] Formatted text with fts3

[Alexandre Courbot]

Never did this myself, but I think you can do what you need by writing
your own tokenizer:

http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/fts3/README.tokenizers

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

Re: [sqlite] bad index selection?

[Derek Scherger]

On Mon, Mar 9, 2009 at 12:08 AM, Igor Tandetnik  wrote:

>
> It can't. "hash" column is not part of the index.
>

Right... I should have seen that.


> > Would re-ordering our unique index to be (name, id, value, ...)
> > rather than (name, value, id, ...) allow a query with only name and
> > id to use it more
> > effectively?
>
> Yes. So would (id, name, ...)


Ok, good. I'm not sure we can do this but it's good to know.

Thanks again for your help.

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

[sqlite] Formatted text with fts3

[Paul Perry]

I've been looking into the full text search capabilities of SQLite and it
looks like exactly what I need.  Cool stuff, especially in the confines of
all the other great features of SQLite.

The question is if I could put formatted text in a text field, and not have
that show up in a full text search..  I would be fine with basic tags, and
would also be fine limiting it to only open and close, as in:

 

and not use the somewhat shorter format (as appropriate) of .  That
is, if it makes it any easier.


I have done some testing, (not surprisingly) and fts will return the text in
the tag.  Actually, I would probably be surprised if it did not, after-all
that is what it is doing.  I was wondering if there is anyway to manually
remove tags from the virtual table somehow (maybe remove some rows from
*table*_content, *table*_segdir, or *table*_segments).  Alas, I looked into
the content of those tables, and there is nothing too obvious that I could
see to be done from just peering into them.

If anybody has some ideas on this, it would be great.  I could always
provide formatting by having another table which would match up that
document, along with a formatting code and the starting and ending character
it applies to.  That does get more complicated, especially when preparing
the text.  I would really prefer having the tags intermixed with the text,
that is if I can prevent them from being in the full text search.

Any ideas would be loved.

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

Re: [sqlite] how to do fulltest with amalgamation source

[Kees Nuyt]

On Mon, 9 Mar 2009 14:54:45 -0500 (CDT), Tim Mooney
 wrote:

>
>All-
>
>If this is a FAQ, please point me at the answer.  I've looked in the FAQ,
>skimmed and grepped the archives for this list, read the
>http://www.sqlite.org/testing.html page and others and still don't have
>an answer, so I thought I would pose the question here.
>
>I've been building sqlite from source on various UNIX platforms for quite
>a long time.  I've recently converted to building from the amalgamation
>sources, since that's what the developers recommend.
>
>The old (multi-file) source distribution had a "fulltest" make target
>which allowed me to do at least some verification of the build I had
>performed on the various platforms.
>
>I can't find any test target whatsoever with the amalgamation source.  Is
>there an extra .tar.gz I can download that would add at least some
>rudimentary tests for the version compiled from the amalgamation?  

The test suite is not included in the amalgamation source.
The only way to test is with the full source tree.
As far as I know, for some tests, a special SQLite library
is built to inject errors or introduce lower limits.

I think the test suite will never be part of the
amalgamation, because it's purposes are:
- to optimize the resulting library / executable
- to simplify the build process (with default options)

Please feel free to add this to the FAQ.
http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq

>If not,
>are there any plans to add a "check" or "test" or "fulltest" target to
>the amalgamation source?
>
>Thanks,
>
>Tim
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to do fulltest with amalgamation source

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Tim Mooney wrote:
> I can't find any test target whatsoever with the amalgamation source. 

Note that fulltest also includes more code to do things like fake IO
errors and running out of memory.

> I can download that would add at least some
> rudimentary tests for the version compiled from the amalgamation? 

Since the SQLite team already extensively test their code, there are
only two things that could hit your compilation of the amalgamation.
The first is the compiler on the platform mis-optimizing code
sufficiently to break it (extremely rare) and the other is changes in
performance for your performance sensitive queries.

I'd recommend writing your own performance test based on your queries.
A single file that creates the database and runs various queries can be
fed to the shell - you can time execution to catch differences in
performance and run a diff against previous runs to catch potential
optimizer problems.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkm1mjsACgkQmOOfHg372QTINwCgiDGyRLJmSxfzi1gBpc/2BbOq
0u0AoKfQjtkZI+aEyddAhaOf3mqvpfhH
=8RjQ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] extra character when reading SQL file

[anjela patnaik]

Hello,
 
I noticed that when I .read a SQL file with insert statements, sqlite is 
inserting an \r before the \n for multiline chars. So I had to regsub out the 
\r char. Is there a way to override this extra char?
 
I'm on Windows2k
 
Thank you


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

[sqlite] how to do fulltest with amalgamation source

[Tim Mooney]

All-

If this is a FAQ, please point me at the answer.  I've looked in the FAQ,
skimmed and grepped the archives for this list, read the
http://www.sqlite.org/testing.html page and others and still don't have
an answer, so I thought I would pose the question here.

I've been building sqlite from source on various UNIX platforms for quite
a long time.  I've recently converted to building from the amalgamation
sources, since that's what the developers recommend.

The old (multi-file) source distribution had a "fulltest" make target
which allowed me to do at least some verification of the build I had
performed on the various platforms.

I can't find any test target whatsoever with the amalgamation source.  Is
there an extra .tar.gz I can download that would add at least some
rudimentary tests for the version compiled from the amalgamation?  If not,
are there any plans to add a "check" or "test" or "fulltest" target to
the amalgamation source?

Thanks,

Tim
-- 
Tim Mooney tim.moo...@ndsu.edu
Enterprise Computing & Infrastructure  701-231-1076 (Voice)
Room 242-J6, IACC Building 701-231-8541 (Fax)
North Dakota State University, Fargo, ND 58105-5164
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Transaction Rate and speed...

[Doug]

Not sure if it would work in your situation, but I got a good performance
boost in a similar situation by essentially queuing all the updates in
memory (not using SQLite), and eventually flushing thousands of queued
updates as a single transaction.  Worked great and was simple to implement,
with the caveat that some items were 'processed' but in volatile memory
longer than they might have been otherwise.

Doug


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of VF
> Sent: Sunday, March 08, 2009 3:48 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] SQLite Transaction Rate and speed...
> 
> Hi SQLite Gurus,
> 
> I am a pretty new SQLite user, and looks like (from the research I've
> made
> so far) I am facing a pretty typical problem with the product -
> performing
> millions upserts as fast as possible. The good news is - there's no
> concurrent access involved - it's pretty much a single Perl script
> which
> processes text and inserts into SQLite DB. The bad news - upsert of
> hundreds
> of thousands of rows takes hours (!).
> 
> Here's the schema of my table (it's split mod 10):
> 
> create table MAPPINGS_$idx (
> key VARCHAR(32) NOT NULL
>   , mapping VARCHAR(32) NOT NULL
>   , rank CHAR(2) NOT NULL
>   , counter INTEGER NOT NULL
>   , timeCreated DATE NOT NULL
>   , timeModified DATE NOT NULL
>   );
> CREATE UNIQUE INDEX MAP_IDX_$idx ON MAPPINGS_$idx (key, mapping);
> CREATE INDEX KEY_IDX_$idx ON MAPPINGS_$idx(key);
> 
> I am trying to do an upsert with the following logic:
> 
> UPDATE MAPPINGS_$idx
> SET counter = counter + 1
> , timeModified = CURRENT_TIMESTAMP
> WHERE
>   key = ? AND
>   mapping = ?;
> IF rowcount == 0 -- here tried rowid and other variations, too, it
> still
> fails at he IF
> BEGIN
> INSERT INTO MAPPINGS_$idx (
> key
>   , mapping
>   , rank
>   , counter
>   , timeCreated
>   , timeModified
>   ) values (
> ?
>   , ?
>   , 1
>   , 1
>   , CURRENT_TIMESTAMP
>   , CURRENT_TIMESTAMP
>   )
> END;
> 
> Unfortunately, it fails. I ended up having separate update, check the
> number
> of rows modified, and if 0 - do insert in a separate statement. It
> works,
> but painfully slow. Because of this I can't do BEGIN/COMMIT transaction
> from
> the SQL. I am using Perl DBI interface. I made sure Per's AucoCommit is
> turned off, but I am not sure it still prevents SQLite engine to do
> commit
> after each upsert. And 'pragma synchronous=off' doesn't seem to work
> either
> (or else I am using it incorrectly).
> 
> Any suggestion would be highly appreciated (ideally with some Perl
> examples).
> 
> Thanks in advance, Bobby
> 
> ___
> 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] SQLite Transaction Rate and speed...

[VF]

Hi SQLite Gurus,

I am a pretty new SQLite user, and looks like (from the research I've made
so far) I am facing a pretty typical problem with the product - performing
millions upserts as fast as possible. The good news is - there's no
concurrent access involved - it's pretty much a single Perl script which
processes text and inserts into SQLite DB. The bad news - upsert of hundreds
of thousands of rows takes hours (!). 

Here's the schema of my table (it's split mod 10):

create table MAPPINGS_$idx ( 
  key VARCHAR(32) NOT NULL 
, mapping VARCHAR(32) NOT NULL
, rank CHAR(2) NOT NULL 
, counter INTEGER NOT NULL
, timeCreated DATE NOT NULL
, timeModified DATE NOT NULL
);
CREATE UNIQUE INDEX MAP_IDX_$idx ON MAPPINGS_$idx (key, mapping);
CREATE INDEX KEY_IDX_$idx ON MAPPINGS_$idx(key);

I am trying to do an upsert with the following logic:

UPDATE MAPPINGS_$idx 
SET counter = counter + 1
, timeModified = CURRENT_TIMESTAMP
WHERE 
key = ? AND 
mapping = ?;
IF rowcount == 0 -- here tried rowid and other variations, too, it still
fails at he IF
BEGIN
INSERT INTO MAPPINGS_$idx (
  key
, mapping
, rank
, counter
, timeCreated
, timeModified
) values (
  ?
, ?
, 1
, 1
, CURRENT_TIMESTAMP
, CURRENT_TIMESTAMP
)
END;

Unfortunately, it fails. I ended up having separate update, check the number
of rows modified, and if 0 - do insert in a separate statement. It works,
but painfully slow. Because of this I can't do BEGIN/COMMIT transaction from
the SQL. I am using Perl DBI interface. I made sure Per's AucoCommit is
turned off, but I am not sure it still prevents SQLite engine to do commit
after each upsert. And 'pragma synchronous=off' doesn't seem to work either
(or else I am using it incorrectly). 

Any suggestion would be highly appreciated (ideally with some Perl
examples).

Thanks in advance, Bobby

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

Re: [sqlite] SQLite Transaction Rate and speed...

[Chris Wedgwood]

On Fri, Mar 06, 2009 at 12:27:32PM -0800, Nuzzi wrote:

> I have a project where I have to be determining if a row exists, if
> so get the data, change it, and then write it back, if not, then
> just writing the data.  I have to be able to do millions of these
> per minute.  Is that pretty much impossible with SQLite or any other
> DB?

Do you really need an SQL database for this?  I can think of various
ways of doing this that aren't that complex and don't require an SQL
database.

Also, do you need on disk persistence?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Transaction Rate and speed...

[Shane Harrelson]

Already there:  http://www.sqlite.org/faq.html#q19

On Sun, Mar 8, 2009 at 8:15 AM, Alexey Pechnikov  wrote:
> Hello!
>
> On Saturday 07 March 2009 01:59:13 Roger Binns wrote:
>> A transaction requires two syncs (ie requesting the drive write the data
>> to the metal and not return until it does).  On average each sync will
>> take a disk rotation so a 7200rpm drive maxes out at 60 transactions a
>> second.  If you require each data update to be physically on the disk
>> then this is your limit no matter what library or storage mechanism you
>> use.
>
> That's great description! Can you add this to documentation? I did know the
> limit experimentally but I didn't can to calculate this.
>
>> You can also look at alternate storage mechanisms.  For example rotating
>> drives can be paired with a battery backed controller, or solid state
>> could be used if the wear leveling and write-erase performance meet your
>> needs.
>
> Do you have experimental results of SQLite performance on SSD?
>
> Best regards.
> ___
> 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: Report constraint name(s) in error message

[Ralf Junker]

Roger Binns wrote:

>> when a named constraint is violated, the name of the constraint which 
>> actually failed is not included in the error message.
>
>There has been a ticket about this for over 3 years, and also includes a
>patch to fix it:
>
>  http://www.sqlite.org/cvstrac/tktview?tn=1648

Thanks, the ticked escaped my notice. Given that there is a patch suggestion 
already, let's hope that it will eventually make it into the SQLite core.

Ralf 

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

[sqlite] ANN: SQLite Maestro 9.3 released

[SQL Maestro Group]

Hi!

SQL Maestro Group announces the release of SQLite Maestro 9.3, a complete 
Windows GUI solution for SQLite database management. The new version is 
immediately available at

http://www.sqlmaestro.com/products/sqlite/maestro/

New features
=

1. SQL Editor: support for explicit transaction management has been 
implemented. Now you can execute queries either in autocommit mode (default 
behavior) or manage transactions manually. In the second case you have to 
issue the BEGIN TRANSACTION statement to start a transaction and explicitly 
end the transaction by COMMIT or ROLLBACK statements (it is also possible to 
use the corresponding links at the editor's navigation bar).

2. Auto-attached databases become available for remote connections.

3. Starting with this version it is possible to create referential integrity 
triggers together with a foreign key creation.

4. Data Export wizard has been significantly improved. Now you can export 
data to Microsoft Office Excel 2007, Microsoft Office Word 2007, 
OpenDocument Spreadsheed, and OpenDocument Text file formats and select the 
result file encoding (ANSI, UTF8, UTF16, UTF32, OEM, Mac). Also the wizard 
has been completely redesigned to increase the usability.

5. Visual Query Builder has been dramatically improved. Now it can produce 
INSERT, UPDATE and DELETE statements as well as the SELECT statements 
containing subqueries and/or UNIONs.

6. Trigger Editor: the Code Completion feature becomes available for the NEW 
and OLD keywords.

7. SQL Editor: starting with this version SQLite Maestro highlights the 
error line after executing an erroneous query.

8. An Italian localization is now included into the installation package.

9. Data grid: starting with this version it is possible to allow displaying 
row numbers in grids.

10. Tabbed MDI interface: a popup menu has been added to each tab.

11. Some other minor improvements and corrections.

Full press-release (with explaining screenshots) is available at:
http://www.sqlmaestro.com/news/company/5598/

Background information:

SQL Maestro Group is engaged in developing complete database admin and 
management tools for MySQL, Oracle, MS SQL Server, PostgreSQL, SQLite, DB2, 
Firebird, SQL Anywhere and MaxDB providing the highest performance, 
scalability and reliability to meet the requirements of today's database 
applications.

Sincerely yours,
The SQL Maestro Group Team
http://www.sqlmaestro.com


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

Re: [sqlite] bad index selection?

[Igor Tandetnik]

"Derek Scherger"  wrote
in message
news:e97446630903082113x1efb0173mfde6aa70909b1...@mail.gmail.com
> I'm curious though, why does 'select *' not also use the unique index
> on the
> same grounds that it could get all the values it needs from the index
> and
> avoid the table lookup?

It can't. "hash" column is not part of the index.

> Would re-ordering our unique index to be (name, id, value, ...)
> rather than (name, value, id, ...) allow a query with only name and
> id to use it more
> effectively?

Yes. So would (id, name, ...)

> I' wondering if the problem is that value is ahead of id
> in the
> index and we can't use anything past name because we don't have a
> value,

Correct. SQLite can use any prefix of the index, but as soon as you skip 
a column, the index is useless. Think about it this way. Consider an 
actual index at the end of a book - an alphabetical list of terms. You 
could use the index to quickly find all words that begin with A, or with 
AB. But it's useless if you need to find all words whose second letter 
is X.

>> The id index is much
>>> more selective that the unique index.
>>
>> You may know that, but SQLite doesn't.
>
> Fair enough. I wasn't sure how much sqlite might know about the
> distribution
> of actual data in the table and what its query optimizer/planner
> might do
> with such information.

Try running ANALYZE command. Sometimes it helps the optimizer to make 
better decisions. But in general, SQLite's optimizer is less 
sophisticated than those found in "big" databases like DB2 and Oracle. 
Hence "lite" in SQLite.

Without ANALYZE, SQlite has no information about distribution of values.

Igor Tandetnik



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