[sqlite] wtl list view article on codeproject
Hello, I've written a small article to show how to connect sqlite to a wtl listview on windows. http://www.codeproject.com/KB/list/alphaview.aspx I hope it will help somebody Best wishes Noël Frankinet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Something to think about: Saving Select ID Lists
One thing that I really miss form another database is the ability to reuse select statements. I haven't programmed but I believe this is sort of possible using C, this is a command language version maybe? e.g. CREATE TABLE IF NOT EXISTS Transactions (ID Integer PRIMARY KEY AutoIncrement, DataID Integer, DateTime Real, Comment Text COLLATE IUNICODE) This has lots of entries (INSERTS, DELETES, UPDATES) CREATE TABLE IF NOT EXISTS Data (ID Integer PRIMARY KEY AutoIncrement, Data1 Text, Data2 Integer, LastTransaction Real) This doesn't have many entries (comparatively) Select * FROM Transactions Where DateTime Between x and y Saving Unique DataID to 1 GetList 1 Select * From Data Using 1 Select Data2 From Data Where Data2=Z Using 1 The "Saving Unique DataID to 1" saves a list of ID's that can be used like part of an IN(1) on another table. The unique qualifier restricts the list to only 1 unique ID where it is in multiple rows. If I remember correctly the lists where saved as UsernameList1, UsernameList2 etc and cleared when disconnected logged out. Not only could you reuse process intensive ID lists, many complicated joins etc can be eliminated. Select * FROM Transactions Where DateTime Between x and y Saving Unique DataID to 1 (500 rows) Select * From Data Where Data2=Z Using 1 (150 rows) Saving ID to 2 (unique not used as the ID is unique already) Select * From AnotherTable Where Data7<>B Using 2 (25 rows returned) If you need information from the other tables you can do the join statement here with only 25 IDs. I hope this makes sense ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] what's the difference between exec and prepare-bind-step(C api)?
Deal all, I have used some SQLite application with sprintf and sqlite3_exec. It takes about 1 hour. And for test, they are changed to prepare-bind-step with same logic. After that, it takes about 2 hours. I tested it on ARM board and I don't know why. Please advise me. * Sorry. I can't support application source for company security. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing fast database rotation
Self replies sorry its kinda lame huh? Could you add a column to
your schema such as "LOG #" or so, and do all your work in the same
table. So if your data max limit is 3 you would have...
rowiddata logNum
1 x 1
2 y 1
3 z 1
4 a 2
5 b 2
Just thinking out of my finger tips.
On Sun, Jun 15, 2008 at 10:20 PM, Rich Rattanni <[EMAIL PROTECTED]> wrote:
> I am working with SQLite in an embedded environment. With synchronous
> = full, I can say large inserts are abysmal (of course I need the
> protection that full synchronous offers). Of course, as always what I
> call large may not be what you call large. Keep in mind that sqlite
> will make a journal file equal to roughly the size of the data you
> will be moving. Instead of moving the data to a backup, could you
> create a new table and start dumping data there? You know, in your
> program remember the current table (DataLogX). When it comes time to
> roll over the log "CREATE TABLE DataLog(X+1) .Just one man's
> opinion.
>
>
> On Fri, Jun 13, 2008 at 5:25 AM, Al <[EMAIL PROTECTED]> wrote:
>> Hello,
>>
>> I'm using sqlite to implement a fast logging system in an embbeded system.
>> For
>> mainly space but also performance reason, I need to rotate the databases.
>>
>> The database is queried regularly and I need to keep at least $min rows in
>> it.
>>
>> What I plan, is inside my logging loop, to do something like this.
>>
>> while(1) {
>>read_informations_from_several_sources();
>>INSERT(informations);
>>
>>if(count > max) {
>> /* I want to move all oldest rows in another database */
>> BEGIN;
>> INSERT INTO logs_backup
>>SELECT * FROM logs order by rowid limit ($max - $min);
>>
>> DELETE FROM logs WHERE rowid IN (SELECT rowid FROM logs ORDER BY rowid
>>LIMIT ($max - $min));
>> COMMIT;
>>}
>> }
>>
>> rowid is an autoincremented field.
>> I am not an sql expert, and would like to find the fastest solution to move
>> the
>> oldest rows into another database. Am I doing silly things ? Can it be
>> improved ?
>>
>> Thanks in advance.
>>
>> ___
>> 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
[sqlite] Help! Integrated Sqlite3 with my embedded system
Hi all, I am integrated Sqlite3(V3.5.7) with my embedded system. For my system doesn't support create in-memroy file, so I have the problem in opening journal file. If I want to continue my work, what should I do? Thank you! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing fast database rotation
I am working with SQLite in an embedded environment. With synchronous
= full, I can say large inserts are abysmal (of course I need the
protection that full synchronous offers). Of course, as always what I
call large may not be what you call large. Keep in mind that sqlite
will make a journal file equal to roughly the size of the data you
will be moving. Instead of moving the data to a backup, could you
create a new table and start dumping data there? You know, in your
program remember the current table (DataLogX). When it comes time to
roll over the log "CREATE TABLE DataLog(X+1) .Just one man's
opinion.
On Fri, Jun 13, 2008 at 5:25 AM, Al <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I'm using sqlite to implement a fast logging system in an embbeded system. For
> mainly space but also performance reason, I need to rotate the databases.
>
> The database is queried regularly and I need to keep at least $min rows in it.
>
> What I plan, is inside my logging loop, to do something like this.
>
> while(1) {
>read_informations_from_several_sources();
>INSERT(informations);
>
>if(count > max) {
> /* I want to move all oldest rows in another database */
> BEGIN;
> INSERT INTO logs_backup
>SELECT * FROM logs order by rowid limit ($max - $min);
>
> DELETE FROM logs WHERE rowid IN (SELECT rowid FROM logs ORDER BY rowid
>LIMIT ($max - $min));
> COMMIT;
>}
> }
>
> rowid is an autoincremented field.
> I am not an sql expert, and would like to find the fastest solution to move
> the
> oldest rows into another database. Am I doing silly things ? Can it be
> improved ?
>
> Thanks in advance.
>
> ___
> 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
[sqlite] How to import CSV data if strings NULL?
Hello
It's probably trivial but I'm having a difficult time using Python
to import a tab-delimited file into SQLite because some columns might
be empty, so the INSERT string should contain NULL instead of "NULL".
Does someone have working code handy that can parse through each line,
check each column, and if found empty, build the right INSERT?
Here's my newbie, yucckie, non-working code:
==
import csv
cr = csv.reader(open("test.tsv","rb"),dialect='excel-tab')
for row in cr:
#INSERT INTO mytable (col1,col2) VALUES (NULL,"My string")
sql = "INSERT INTO mytable (col1,col2) VALUES ("
for col in row:
if col=="":
sql = sql + "NULL"
else:
sql = sql + col
if not last col:
sql = sql + ","
else:
#remove trailing comma
==
Thank you for any tip.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_BUSY frequent error
Hello! I have an application that will try to access an sqlite database for some database operations such as selecting, deleting, inserting, and/or updating records. This application spawns some children that will also do the same transactions to the database. I have developed a locking mechanism that will lock the database file in a blocking mode to avoid sqlite_busy error. This mechanism actually works as expected. However, while the application is actually running, the database transactions returns sqlite_busy quite frequently in such a way that no transaction in all process can be processed. The database contains really a great number of records. Would this be a cause while an exclusive lock will be taken by sqlite itself? I have read something that gave me a hint that the amount of records in the database will actually let sqlite get an exclusive lock. The url is: http://www.mail-archive.com/sqlite-users@sqlite.org/msg28643.html so you guys can check out for yourselves. If anyone can help me go around this problem, please do so. Thank you and God bless! Best regards, arbalest06 -- View this message in context: http://www.nabble.com/SQLITE_BUSY-frequent-error-tp17847439p17847439.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
Re: [sqlite] SQlite and C works with "like" but not with "="
On Jun 15, 2008, at 8:55 AM, Daniel White wrote: > Cheers both of you, it seems this problem is indeed > linked with the "no such collation sequence: iunicode" > error as Dan mentioned. > > After some research, I found out that the root of > the problem is unsurmountable at present. I quote from: > http://www.mediamonkey.com/forum/viewtopic.php?p=84197 > > "It's a real shame that simple queries like "select * > from Songs where SongTitle = 'ABC'" aren't viable. > However, SQLite doesn't have good collation included, > there's absolutely no support for Unicode sorting or > case insensitive comparisons. We will try to do something > about it, but to be honest, I don't know if there's any > easy fix..." > > ...and... > > "IUNICODE is our collation that's there in order to > support Unicode sorting - which SQLite can't do internally." > > Oh well, there are one or two 'hacks' around it. The first is > to use COLLATE BINARY, or COLLATE NOCASE after the SQL query. > This appears okay on the surface, but probably ignores > unicode chars or something. It may also slow down the query (?) > > The other idea is to simply use "LIKE 'xyz'" instead of > "= 'xyz'". To my limited knowledge, apart from the case > sensivity of the latter, these don't differ in the outcome, > because there are no % signs around the former statement. > Although it would be nice, I'm not too bothered about case > sensitivity for my purposes. > > Which solution would you guys recommend? Just using 'COLLATE BINARY' is a good idea. There is a pretty good chance that memcmp() and whatever is being used for IUNICODE are the same for the '=' operator. If you don't have any non-ASCII characters in the song names, this will almost certainly work. Using COLLATE BINARY will prevent SQLite from using any index created on the song_title column (as the index will have been created using IUNICODE). Or you could copy all the data into a new table - one that uses only the default available collation sequences: CREATE TEMP TABLE my_songs AS SELECT * FROM songs; then query my_songs instead of songs. That wouldn't help you any more than using COLLATE binary explicitly in every query though. Or you could ask the vendor for the source code to the IUNICODE collation function. Using SQLite's ICU extension to try to create an equivalent collation sequence is also possible, but a bit dangerous. If the collation sequence you create turns out to be "mostly compatible" instead of "completely compatible", then you might wind up with segfaults or a corrupted database at some point in the future. Dan. > Cheers, Dan > > > >> And you are saying the statement >> >> SELECT SongTitle FROM songs WHERE SongTitle='Hexion'; >> >> doesn't return any rows? > > Correct. It's weird I know. I also tried with different > names in different fields (Artist etc.), and I get the > same problem. "like" is okay, but = doesn't work. > > Here are the files again: > http://www.skytopia.com/stuff/MMdatabase.zip (1.6 M) > http://www.skytopia.com/stuff/sqlite.cpp (1k) > > Dan > > > On Sat, 14 Jun 2008 15:54:50 +0100, Igor Tandetnik > <[EMAIL PROTECTED]> > wrote: > >> "Daniel White" <[EMAIL PROTECTED]> >> wrote in message news:[EMAIL PROTECTED] Which way is it stored in the database? Show the output of this statement: SELECT SongTitle FROM songs WHERE SongTitle like 'hexion'; >>> >>> There are 8 records of Hexion in the database, so after a printout >>> to the console with a carriage return after each value, I basically >>> get: >>> Hexion >>> Hexion >>> Hexion >>> Hexion >>> Hexion >>> Hexion >>> Hexion >>> Hexion >> >> And you are saying the statement >> >> SELECT SongTitle FROM songs WHERE SongTitle='Hexion'; >> >> doesn't return any rows? With all due respect, I find it difficult to >> believe. Would it be possible for you to email a copy of the database >> file to me at [EMAIL PROTECTED] ? Perhaps a stripped-down version, >> with just enough data to reproduce the problem. >> >> Igor Tandetnik >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > -- > www.skytopia.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
