[sqlite] First Reunion The Sqlite Latino
Hello the Free Software III Conference organized by students from the Universidad Nacional de Jujuy - UNJu in Jujuy Province - Argentina - The Community SQLite - Latino . Saludos Gerardo Cabero [0] sqlite-latino.blogspot.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] On UNIQUE and PRIMARY KEY
On Sep 4, 2008, at 8:56 PM, Darren Duncan wrote: > D. Richard Hipp wrote: >> One occasionally sees SQLite schemas of the following form: >> >> CREATE TABLE meta(id LONGVARCHAR UNIQUE PRIMARY KEY, ); >> >> In other words, one sometimes finds a PRIMARY KEY and a UNIQUE >> declaration on the same column. This works fine in SQLite, but it is >> wasteful, both of disk space and of CPU time. If we ignore the >> INTEGER PRIMARY KEY exception, the "PRIMARY KEY" keyword in SQLite >> means the same thing as "UNIQUE". Both create a unique index on the >> column. So if you use them both on the same column, you get two >> identical unique indices. SQLite will dutifully maintain them both - >> requiring twice the CPU time and twice the disk space. But having a >> redundant index does not make queries run any faster. The extra >> index >> merely takes up time and space. >> >> So here is a good rule of thumb: Never use both UNIQUE and PRIMARY >> KEY on the same column in SQLite. > > Is there any reason why you can't just optimize this away at the > parser > level by ignoring the UNIQUE keyword and only make the PRIMARY KEY > index? Yes. It would result in an incompatible file format. Older versions of SQLite would be unable to read newer databases because they would be expecting to find multiple indices instead of just one. If I had discovered this problem before the file format was frozen, I could have fixed it. But it is too late now. > > (Or alternately to disallow those 2 terms appearing together?) Does > having > identical UNIQUE and PRIMARY KEY constraints lead to different > semantics > than having just the PRIMARY KEY? I suggest changing SQLite about > this. -- > Darren Duncan > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] On UNIQUE and PRIMARY KEY
D. Richard Hipp wrote: > One occasionally sees SQLite schemas of the following form: > > CREATE TABLE meta(id LONGVARCHAR UNIQUE PRIMARY KEY, ); > > In other words, one sometimes finds a PRIMARY KEY and a UNIQUE > declaration on the same column. This works fine in SQLite, but it is > wasteful, both of disk space and of CPU time. If we ignore the > INTEGER PRIMARY KEY exception, the "PRIMARY KEY" keyword in SQLite > means the same thing as "UNIQUE". Both create a unique index on the > column. So if you use them both on the same column, you get two > identical unique indices. SQLite will dutifully maintain them both - > requiring twice the CPU time and twice the disk space. But having a > redundant index does not make queries run any faster. The extra index > merely takes up time and space. > > So here is a good rule of thumb: Never use both UNIQUE and PRIMARY > KEY on the same column in SQLite. Is there any reason why you can't just optimize this away at the parser level by ignoring the UNIQUE keyword and only make the PRIMARY KEY index? (Or alternately to disallow those 2 terms appearing together?) Does having identical UNIQUE and PRIMARY KEY constraints lead to different semantics than having just the PRIMARY KEY? I suggest changing SQLite about this. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Core dump version 3.6.2
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ken wrote: > Core dump backtrace, using sqlite3 version 3.6.2... > Suse Linux, gcc 4.2.1 > > Any ideas? By far the easiest way of diagnosing is to run valgrind. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIwHbmmOOfHg372QQRApGAAJ9IrNFOKRoGPK5R1IKK3uXv1vQjtQCfTU7+ xeT9kse/CxuoSIG211R3XKE= =42kz -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Logging
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hardy, Andrew wrote: > Do I have to pass function pointers (for functions that implement the > appropriate logging) to these functions (the ones below) then my > functions get called back omn the appropriate activity? Yes, those functions are to register callbacks. There are various C tutorials on the web that show how to do callbacks. > Is this & rebuild the only way to achieve logging? SQLite is a library so you will have to rebuild your code that interfaces to SQLite, but not SQLite itself. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIwHn8mOOfHg372QQRAtsyAJ9ecvKCnIcs/mVYYcboXLu/qtV4/gCfeO/J NQHoSMUNNAYFh25TiNI1GWI= =T8Cn -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Core dump version 3.6.2
Core dump backtrace, using sqlite3 version 3.6.2... Suse Linux, gcc 4.2.1 Any ideas? Program terminated with signal 11, Segmentation fault. #0 0x2b4ead3562d2 in ?? () from /lib64/libc.so.6 (gdb) backtrace #0 0x2b4ead3562d2 in ?? () from /lib64/libc.so.6 #1 0x2b4ead357d1b in ?? () from /lib64/libc.so.6 #2 0x2b4ead357f76 in free () from /lib64/libc.so.6 #3 0x0047a722 in sqlite3ScratchFree () #4 0x0049618b in balance_nonroot () #5 0x004965ee in balance () #6 0x00496abf in sqlite3BtreeInsert () #7 0x004b032a in sqlite3VdbeExec () #8 0x004a56e8 in sqlite3_step () #9 0x00463815 in ixsqlt_stmt_fetch (sqltc=0x794e68) at ix_sqlite3.c:2085 #10 0x0040c339 in fetch_kdo (p_stmt=0x794e68, xscn_flg=1) at redo_data.c:254 #11 0x0040ec78 in _objR_Read (objd=0x794a20) at obj_rdata.c:730 #12 0x00411c98 in fileapply_do (ti=0x71d800) at decomp_fileapply_thr.pc:280 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] Nanoki & SQLite's FTS
Hello, Nanoki, a simple, elegant wiki engine implemented in Lua. http://alt.textdrive.com/nanoki/ Online demo: http://svr225.stepx.com:3388/search?q=chicago Nanoki incorporates SQLite excellent FTS module to provide full text search. Cheers, PA. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] On UNIQUE and PRIMARY KEY
One occasionally sees SQLite schemas of the following form: CREATE TABLE meta(id LONGVARCHAR UNIQUE PRIMARY KEY, ); In other words, one sometimes finds a PRIMARY KEY and a UNIQUE declaration on the same column. This works fine in SQLite, but it is wasteful, both of disk space and of CPU time. If we ignore the INTEGER PRIMARY KEY exception, the "PRIMARY KEY" keyword in SQLite means the same thing as "UNIQUE". Both create a unique index on the column. So if you use them both on the same column, you get two identical unique indices. SQLite will dutifully maintain them both - requiring twice the CPU time and twice the disk space. But having a redundant index does not make queries run any faster. The extra index merely takes up time and space. So here is a good rule of thumb: Never use both UNIQUE and PRIMARY KEY on the same column in SQLite. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query runs faster when repeating condition
Magnus Manske <[EMAIL PROTECTED]> wrote: > I've found the strangest issue today. I have a sqlite3 database that > contains the following table: > > CREATE TABLE MAL9_single ( read_name VARCHAR[32], pos1 INTEGER, seq1 > VARCHAR[64] ); > CREATE INDEX MAL9_sin_index ON MAL9_single ( pos1 ); > > From Perl, I query for all rows in that table within a certain range: > SELECT read_name,pos1,seq1 FROM MAL9_single WHERE ( pos1 >= 1 AND pos1 > <= 100 ) ; The condition could be written a bit more laconically: pos1 BETWEEN 1 AND 100 > On my table (ca. 180K entries) this takes about 10 seconds for the > above, which returns all rows. Since you are not benefitting from the index for this query, you might get better performance by not using it: +pos1 BETWEEN 1 AND 100 The unary plus operator suppresses the use of the index on the affected column, while having no effect on the value of the expression. > However, when I repeat the exact same condition with OR: > SELECT read_name,pos1,seq1 FROM MAL9_single WHERE ( pos1 >= 1 AND pos1 > <= 100 ) OR ( pos1 >= 1 AND pos1 <= 100 ) ; > > it is about 10 times faster. Having an OR also suppresses the index. > I pasted the EXPLAIN below, but I'm no wiser :-( It usually helps to start with EXPLAIN QUERY PLAN, rather than raw EXPLAIN. The former shows in compact form which tables and indexes are used. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ICU and SQlite
Hi there, i need to be able to sort results from a select statement in alphabetic order. The results however can have diacritic characters, like the german Umlaute "Ä,Ö,Ü"). They are supposed to be sorted like "AE", "OE" and "UE". I have been pointed to ICU as the solution. I got a download link to a compiled DLL of the ICU project, as I couldn?t figure out how to compile my own version, yet the DLL i got did not load right as an sqlite3 extension. Can anyone help me please? I am using the latest sqlite3 (command line version) on Windows XP SP3. Ideally, I?d like to get precompiled binaries, but any help on how to compile the ICU extension in a workable fashion would be much appreciated as well. Thanks, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing an image to the database as a BLOB
Jared Miller wrote: > > //bind blob m_pSqliteDB->BindBlob(blobHandle, 1, (void *)bmBytes, > bmSize); //calls sqlite3_bind_blob (bmBytes is the pData param) > Can you show the code you use to call sqlite3_bind_blob() as well? > Then I call StepQuery to execute it, and then I close the query. > sqlite3_bind_blob() returns SQLITE_OK when I run it, so it does not > seem to be encountering an error there. > > Something apparently gets written to the database, but it does not > seem to be correct. When I try to retrieve and display my image, it > is all black (which is how bitmaps look when there is no data). > > I think that the problem is coming from writing the BLOB to the > database, but I am not entirely sure. Just in case it is getting > written properly and I am not reading it from the database correctly, > I will show you how I pulled it from the DB. > > //prepare blob sqlite3_blob* pBlob = NULL; > sqlite3_blob_open(m_sqliteDB, "main", "tblBlob", "Data", iBlobID, > FALSE, &pBlob); > > BLOB_HANDLE hBlob = pBlob; int size = sqlite3_blob_bytes(hBlob); > //works correctly, returns 998058 BYTE* pBuffer = > g_MemMgr.AllocDataBuffer(size); > > sqlite3_blob_read(hBlob, pBuffer, size, 0); //returns SQLITE_OK > sqlite3_blob_close(hBlob); > > I then try to make a bitmap out of the bytes in pBuffer, but when I > do, it turns out all black (as I mentioned earlier). > Instead of making a bitmap, can you call memcmp() to compare the data in the two buffers at pBuffer and bmBytes? That would allow a direct comparison of the data that was written and the data that was read back. > Do I have the concept correct? I think so. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query runs faster when repeating condition
Hi, I'm new to the list, but have been an enthusiastic sqlite user for years. I've found the strangest issue today. I have a sqlite3 database that contains the following table: CREATE TABLE MAL9_single ( read_name VARCHAR[32], pos1 INTEGER, seq1 VARCHAR[64] ); CREATE INDEX MAL9_sin_index ON MAL9_single ( pos1 ); >From Perl, I query for all rows in that table within a certain range: SELECT read_name,pos1,seq1 FROM MAL9_single WHERE ( pos1 >= 1 AND pos1 <= 100 ) ; On my table (ca. 180K entries) this takes about 10 seconds for the above, which returns all rows. However, when I repeat the exact same condition with OR: SELECT read_name,pos1,seq1 FROM MAL9_single WHERE ( pos1 >= 1 AND pos1 <= 100 ) OR ( pos1 >= 1 AND pos1 <= 100 ) ; it is about 10 times faster. Adding more OR repeats doesn't speed it up further, sadly ;-) I pasted the EXPLAIN below, but I'm no wiser :-( I don't manage the system here, so I'm rather in the dark about the exact version number. Any ideas? Magnus sqlite> EXPLAIN SELECT read_name,pos1,seq1 FROM MAL9_single WHERE ( pos1 >= 1 AND pos1 <= 100 ) ; addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 0 EXPLAIN SELECT read_name,pos1,seq1 FROM MAL9_single WHERE ( pos1 >= 1 AND pos1 <= 100 ) ; 00 1 Goto 0 26000 2 OpenRead 0 69000 3 SetNumColumns 0 3 000 4 OpenRead 1 260893 0 keyinfo(1,BINARY) 00 5 SetNumColumns 1 2 000 6 Integer100 2 000 7 IsNull 2 23000 8 MakeRecord 2 1 1 db 00 9 Integer1 2 000 10IsNull 2 23000 11MakeRecord 2 1 5 db 00 12MoveGe 1 23500 13IdxGE 1 23101 14Column 1 0 500 15IsNull 5 22000 16IdxRowid 1 5 000 17MoveGe 0 0 500 18Column 0 0 600 19Column 1 0 700 20Column 0 2 800 21ResultRow 6 3 000 22Next 1 13000 23Close 0 0 000 24Close 1 0 000 25Halt 0 0 000 26Transaction0 0 000 27VerifyCookie 0 138 000 28TableLock 0 690 MAL9_single00 29Goto 0 2 000 sqlite> EXPLAIN SELECT read_name,pos1,seq1 FROM MAL9_single WHERE ( pos1 >= 1 AND pos1 <= 100 ) OR ( pos1 >= 1 AND pos1 <= 100 ); addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 0 EXPLAIN SELECT read_name,pos1,seq1 FROM MAL9_single WHERE ( pos1 >= 1 AND pos1 <= 100 ) OR ( pos1 >= 1 AND pos1 <= 100 ); 00 1 Goto 0 24000 2 OpenRead 0 69000 3 SetNumColumns 0 3 000 4 Rewind 0 22000 5 Column 0 1 100 6 Integer1 2 000 7 Lt 2 111 collseq(BINARY) 6c 8 Column 0 1 200 9 Integer100 1 000 10Le 1 172 collseq(BINARY) 64 11Column 0 1 100 12Integer1 2 000 13Lt 2 211 collseq(BINARY) 6c 14Column 0 1 200 15Integer100 1 000 16Gt 1 212 collseq(BINARY) 6c 17Column 0 0 300 18Column 0 1 400 19Column 0 2 500 20ResultRow 3 3 000 21Next 0 5 000 22Close 0 0 000 23Halt 0 0 000 24Transaction0 0 000 25VerifyCookie 0 138 000 26TableLock 0 690 MAL9_single00 27Goto 0 2 0
Re: [sqlite] SQLite DB memory fault error
are you sure that the nickname field is 100 bytes?
Why do you use strncpy(d,s,100 ) why not
strncpy(d,s, sizeof(d))
Other than that, hard to tell without seeing the data types and declarations.
Might want to post on a C programming board.
--- On Thu, 9/4/08, kogure <[EMAIL PROTECTED]> wrote:
From: kogure <[EMAIL PROTECTED]>
Subject: [sqlite] SQLite DB memory fault error
To: sqlite-users@sqlite.org
Date: Thursday, September 4, 2008, 7:16 AM
Hello everyone. I have a database with fields not required to be filled in
(the other fields are declared NOT NULL). When I have a record with the
non-required fields empty, and copied it to my structure, there is a memory
fault (core dumped) error. Here is the snippet of my code:
if (pdata) {
for (index = 0; index < p_out.r_count; index++) {
// p_ptr will contain the record (row) from the table
p_ptr = p_out.ppp_data[index];
// accessing the elements of the pointer to the record
(row)
pdata->info_id = atoi((p_ptr[0]));
strncpy(pdata->surname, (p_ptr[1]), 100);
strncpy(pdata->firstname, (p_ptr[2]), 100);
//#if 0
memcpy(pdata->nickname, (p_ptr[3]), 100); <- probable
error source (not required fields)
pdata->age = atoi((p_ptr[4])); <-
probable error source (not required fields)
//#endif
strncpy(pdata->position, (p_ptr[5]), 100);
strncpy(pdata->teamname, (p_ptr[6]), 100);
strncpy(pdata->teamclient, (p_ptr[7]), 100);
// increment pointer to move to next record
pdata++;
}
// output the contents of pdata
*all_records = pdata;
}
else {
result = MEM_ERROR;
}
Thanks for the help in advance.
--
View this message in context:
http://www.nabble.com/SQLite-DB-memory-fault-error-tp19308790p19308790.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] Broken indexes ...
Jordan Hayes wrote: > > Where would the file be? It would be "beside" the database file, i.e. in the same directory as the database file. The journal only exists while sqlite is modifying the database. When a change has been completed successfully, the journal file is deleted. > I don't see a file. I didn't write the > application, but it runs in Windows. Under Windows does it get put > somewhere "special" ...? > When I restarted the application it didn't > give me any notice that anything was wrong; Normally it wouldn't give you any indication it found a journal. When the application opened the database file, the sqlite library would see the journal file (if it existed) and use it to restore the database file to the exact state it was in before the last change (i.e. transaction) started, and then delete the journal file. If there is no journal file, the library assumes the database file is intact and simply opens it for use. If the application was making changes and hence a journal file existed at the time of the power failure, the database file would be in an inconsistent state. The journal file has the information needed to restore the state. If you, or your application, deleted this journal file (thinking it was a temporary file left over from the crash) before the sqlite library gets a chance to see it (i.e. before the database is opened again) your database file is left in the inconsistent state. Some applications perform an integrity check on database files immediately after they are opened. These applications may report the problem if a "hot" journal file is deleted, since the database may have been left in an inconsistent state by a partially complete change. An application could check for the existence of the journal file before opening the database, and report that the incomplete transaction will be rolled back as the database is opened. I doubt if many applications do this however. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Logging
I've taken a brief look at this in the source. Do I have to pass function pointers (for functions that implement the appropriate logging) to these functions (the ones below) then my functions get called back omn the appropriate activity? I think I could use an example to help me along. Can some one point me to a simple example somewhere? Is this & rebuild the only way to achieve logging? Regards, Andrew -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns Sent: 04 September 2008 10:33 To: General Discussion of SQLite Database Subject: Re: [sqlite] Logging -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hardy, Andrew wrote: > What's the most efficient way of getting a log file of the db activity > on your sql db over a time period. Can you get timings against these > acrtivities? You have to write code/callbacks that interface with the library. sqlite3_trace() will get you the text of queries while sqlite3_profile() will get you the text and timings. If you also want the bound parameters then you'll need to note them in your calls to sqlite3_prepare. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIv6s6mOOfHg372QQRAjRDAJ9tBVlHVEX5EfqN6+kMG8fyn7zdqQCfW9p5 awhgF/OzwCSSOPmQGHG2b9I= =RqBM -END PGP SIGNATURE- ___ 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] Entity Relationship Diagram Tool for SQLite
Hello, Thank you to all that responded both via the list and privately. It was pointed out that I was not clear in my original question. I am looking for a tool that can create ER diagrams from a SQLite database. Along with dia and dezign I also found SQLite Maestro at http://www.sqlmaestro.com/products/sqlite/maestro/ SQLite Maestro creates the ER diagrams from the database file directly but I have to draw the connections between the tables manually. David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite DB memory fault error
"kogure" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hello everyone. I have a database with fields not required to be > filled in (the other fields are declared NOT NULL). When I have a > record with the non-required fields empty, and copied it to my > structure, there is a memory fault (core dumped) error. Here is the > snippet of my code: I don't see a single SQLite call in your code. Which part do you feel is SQLite's fault? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite DB memory fault error
Hello everyone. I have a database with fields not required to be filled in
(the other fields are declared NOT NULL). When I have a record with the
non-required fields empty, and copied it to my structure, there is a memory
fault (core dumped) error. Here is the snippet of my code:
if (pdata) {
for (index = 0; index < p_out.r_count; index++) {
// p_ptr will contain the record (row) from the table
p_ptr = p_out.ppp_data[index];
// accessing the elements of the pointer to the record
(row)
pdata->info_id = atoi((p_ptr[0]));
strncpy(pdata->surname, (p_ptr[1]), 100);
strncpy(pdata->firstname, (p_ptr[2]), 100);
//#if 0
memcpy(pdata->nickname, (p_ptr[3]), 100); <- probable
error source (not required fields)
pdata->age = atoi((p_ptr[4])); <-
probable error source (not required fields)
//#endif
strncpy(pdata->position, (p_ptr[5]), 100);
strncpy(pdata->teamname, (p_ptr[6]), 100);
strncpy(pdata->teamclient, (p_ptr[7]), 100);
// increment pointer to move to next record
pdata++;
}
// output the contents of pdata
*all_records = pdata;
}
else {
result = MEM_ERROR;
}
Thanks for the help in advance.
--
View this message in context:
http://www.nabble.com/SQLite-DB-memory-fault-error-tp19308790p19308790.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] vertical -> horizontal data conversion
Andy, You should be looking for pivot tables. However, there is no native support for them in sqlite as far as I know. Some databases (e.g. SQL Server 2005+) know PIVOT command but not sqlite. You may want to look at http://mesh.dl.sourceforge.net/sourceforge/souptonuts/Solving_Complex_SQL_Problems.txtor construct them in the application layer as I do. Good luck, Tomas On Thu, Sep 4, 2008 at 1:09 PM, Andy Chambers <[EMAIL PROTECTED] > wrote: > Hi, > > I have a table like this > > create table clinical_data ( > group_def text, > item_def text, > value text > ); > > Assuming this example data... > > GROUP_DEF, ITEM_DEF, VALUE > --- > "MEDHIST", "BODSYS", "foo" > "MEDHIST", "TERM", "bar" > "MEDHIST", "ONSET", "baz" > > I'd like to create a view that looks like > > BODSYS, TERM, ONSET > --- > "foo", "bar", "baz" > > I've got a feeling the answer lies in outer joins but my SQL is a > little rusty. This seems like something people would need to do a > lot. Is there a name for this that I can go lookup? > > Many Thanks, > Andy > ___ > 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] Entity Relationship Diagram Tool for SQLite
On 9/4/08, Hardy, Andrew <[EMAIL PROTECTED]> wrote: > > Not sure if this is what David is getting at, iro specifically saying > sqlite, but I'd be interested in something that generates a schema > diagram from an existing sqlite db file. Or are there tools that can do > this from the text of a dump, if the sql is standard enough? > If you are using Mac OS X, try SQL Editor by the Malcolm Hardie Company. http://www.malcolmhardie.com/sqleditor/ Does all of the above, using SQLite JDBC driver. Worth every cent. > > Andrew > > > -Original Message- > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] On Behalf Of Fred Williams > Sent: 03 September 2008 19:06 > To: [EMAIL PROTECTED]; General Discussion of SQLite > Database > Subject: Re: [sqlite] Entity Relationship Diagram Tool for SQLite > > What is so unique to SQLite that it would require its very own Entity > Relationship engine? > > Relational databases are relational databases. Granted some are "more" > relational than others, but that is where the designer's mind is > required to function above a video game level. > > Fred > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of David Abrames > Sent: Wednesday, September 03, 2008 12:55 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Entity Relationship Diagram Tool for SQLite > > > Hello, > > I am looking for an Entity Relationship Diagram Tool for SQLite. Thank > you in advance. > > David Abrames > > > ___ > 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-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] vertical -> horizontal data conversion
Hi, I have a table like this create table clinical_data ( group_def text, item_def text, value text ); Assuming this example data... GROUP_DEF, ITEM_DEF, VALUE --- "MEDHIST", "BODSYS", "foo" "MEDHIST", "TERM", "bar" "MEDHIST", "ONSET", "baz" I'd like to create a view that looks like BODSYS, TERM, ONSET --- "foo", "bar", "baz" I've got a feeling the answer lies in outer joins but my SQL is a little rusty. This seems like something people would need to do a lot. Is there a name for this that I can go lookup? Many Thanks, Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Logging
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hardy, Andrew wrote: > What's the most efficient way of getting a log file of the db activity > on your sql db over a time period. Can you get timings against these > acrtivities? You have to write code/callbacks that interface with the library. sqlite3_trace() will get you the text of queries while sqlite3_profile() will get you the text and timings. If you also want the bound parameters then you'll need to note them in your calls to sqlite3_prepare. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIv6s6mOOfHg372QQRAjRDAJ9tBVlHVEX5EfqN6+kMG8fyn7zdqQCfW9p5 awhgF/OzwCSSOPmQGHG2b9I= =RqBM -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_bind_int64, sqlite3_bind_int, sqlite_uint64
Martin (OpenGeoMap) wrote: >> Neither C nor C++ define the specific sizes of different types. >> Therefore, "long long int" can be different sizes on different >> platforms, or even within different compilers for the same platform. >> Without more information, we cannot say for sure if a "long long int" >> is 64 bits or not on your platform. >> > yes, that´s the reason the define new types in QT or in the glib: > http://library.gnome.org/devel/glib/stable/glib-Basic-Types.html I thought C99 had spesified these types (i.e. with C99 it is a part of C): http://en.wikipedia.org/wiki/C99 http://en.wikipedia.org/wiki/Stdint.h C99 include file stdint.h defines these types: uint64_t, int64_t, uin32_t, int32_t and so on. C99 also specifies a boolean value 'bool': http://en.wikipedia.org/wiki/Stdbool.h I understand that these types cannot be used directly in code that can be compiled on older compilers that does not support C99. But for those older compilers you can define a compability header file that defines these values. Then you can use C99 datatypes in your code and it is supported for newer and older compilers. And I personally thinks that using explicit datatypes like uin32_t is way more descriptive than 'unsigned int' and similar for datatypes where you really need to know the length. -- Roar Bjørgum Rotvik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Logging
What's the most efficient way of getting a log file of the db activity on your sql db over a time period. Can you get timings against these acrtivities? Regards, Andrew ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Entity Relationship Diagram Tool for SQLite
Not sure if this is what David is getting at, iro specifically saying sqlite, but I'd be interested in something that generates a schema diagram from an existing sqlite db file. Or are there tools that can do this from the text of a dump, if the sql is standard enough? Andrew -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Fred Williams Sent: 03 September 2008 19:06 To: [EMAIL PROTECTED]; General Discussion of SQLite Database Subject: Re: [sqlite] Entity Relationship Diagram Tool for SQLite What is so unique to SQLite that it would require its very own Entity Relationship engine? Relational databases are relational databases. Granted some are "more" relational than others, but that is where the designer's mind is required to function above a video game level. Fred -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of David Abrames Sent: Wednesday, September 03, 2008 12:55 PM To: sqlite-users@sqlite.org Subject: [sqlite] Entity Relationship Diagram Tool for SQLite Hello, I am looking for an Entity Relationship Diagram Tool for SQLite. Thank you in advance. David Abrames ___ 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_bind_int64, sqlite3_bind_int, sqlite_uint64
> > Neither C nor C++ define the specific sizes of different types. > Therefore, "long long int" can be different sizes on different > platforms, or even within different compilers for the same platform. > Without more information, we cannot say for sure if a "long long int" > is 64 bits or not on your platform. > yes, that´s the reason the define new types in QT or in the glib: http://library.gnome.org/devel/glib/stable/glib-Basic-Types.html > However, most modern desktop systems _do_ define "long long int" to be a > 64 bit integer. If you want to find out, just look at the return value > from "sizeof(long long int)"-- if it is 8, then it is 64 bits. > > Regardless, it is extremely unlikely to be greater than 64 bits, so > using sqlite_bind_int64() (which does define an explicit 64 bit > integer) should be able to hold anything a "long long int" can hold. > The compiler should do any required conversion if "long long int" is > some other size. > >-j > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
